[Senate] Auto-migration generation from approved schema changes

Goal

When a schema proposal is approved, automatically generate and apply the database migration.
For metadata-level changes (JSON fields), generate a backfill script. For column-level
changes, generate ALTER TABLE migrations compatible with the migration runner.

Acceptance Criteria

Dependencies

• sen-sg-02-PROP — Approved proposals trigger this
• sen-sg-03-VALD — Post-migration validation confirms integrity

Dependents

• None (leaf task in migration chain)

Work Log

2026-04-25 22:11 PDT — Codex

• Performed staleness review against current origin/main and live PostgreSQL schema.
• Confirmed task is still relevant: scidex/senate/schema_governance.py finalized approved schema proposals with a log line only and did not define the migration helpers described in docs/schema_governance_guide.md.
• Confirmed live migration_history only tracked (version, name, applied_at, execution_time_ms, status) and was missing proposal/rollback/schema-version metadata required by this task.
• Planned a PostgreSQL-native implementation using jsonb_set and temporary-table copy validation instead of the original SQLite-style json_set / in-memory-copy wording in the spec.

2026-04-25 22:43 PDT — Codex

• Verified the in-progress migration module against the live PostgreSQL schema and confirmed the task was still incomplete on main.
• Ran targeted unit coverage for tests/test_schema_migration.py and reviewed the generated SQL paths for metadata, column, and add_type proposals.
• Identified two production-hardening gaps to fix before shipping: rollback on copy-test failure and safe restoration of artifact_registry.py if an add_type migration aborts mid-transaction.

2026-04-25 23:01 PDT — Codex

• Added failure-path hardening in scidex/senate/schema_migration.py: copy-test failures now roll back the open transaction, and add_type restores artifact_registry.py if the DB migration aborts mid-flight.
• Added targeted regression coverage for dry-run, copy-test rollback, rollback-safe artifact-type registration, SQL-type validation, and Senate approval auto-application (8 passed).
• Verified the real preview path against live proposal senate-prop-b54f3d4b9d61, which generated an ALTER TABLE artifacts ADD COLUMN IF NOT EXISTS effect_size DOUBLE PRECISION; plan for the approved schema shape.

2026-04-25 23:18 PDT — Codex

• Re-ran python3 -m py_compile for scidex/senate/schema_migration.py, scidex/senate/schema_governance.py, and tests/test_schema_migration.py.
• Re-ran pytest -q tests/test_schema_migration.py and confirmed 8 passed in 0.14s.
• Re-checked the live PostgreSQL copy-validation path for proposal senate-prop-b54f3d4b9d61; preview SQL and temporary-table validation both succeeded against current data.

2026-04-26 — Claude Sonnet 4.6

• Resumed task after finding all code uncommitted; rebased branch onto origin/main, resolved merge conflicts, re-ran 8 tests (0.39s pass), then synced to prior Codex commits already on the remote branch.
• Final verification: pytest -q tests/test_schema_migration.py → 8 passed in 0.64s. All acceptance criteria confirmed complete.

2026-04-26 09:22 UTC — MiniMax Agent

• Post-rebase verification: tests still pass (8/8), syntax checks clean.
• Confirmed clean diff: 4 files only (spec, schema_governance.py, schema_migration.py, test file).
• Restashed .orchestra-slot.json and .orchestra/audit/ (orchestration artifacts, not task code).
• Pushed to origin/HEAD.

2026-04-26 — Claude Sonnet 4.6 (gate-fix retry)

• Three prior merge attempts rejected with "judge returned an unparseable decision / You've hit your org's monthly usage limit" — all are judge_chain_error (judge LLM quota exhaustion), not code failures.
• Re-verified: python3 -m py_compile clean on all 3 source files; pytest -q tests/test_schema_migration.py → 8 passed in 0.13s.
• Branch is still 3 commits ahead of origin/main, exactly on the intended diff (4 files, 1075 net insertions).
• All 9 acceptance criteria remain checked. No code changes needed; pushing again as directed by task instructions for judge_chain_error retries.