ci(db): gate long-running migrations with a production-scale duration check

[Soju06]

Problem

The existing migration checks (Alembic policy/drift, PostgreSQL upgrade-from-empty, lock serialization) all run against empty or tiny databases, so a revision that is instant in CI can block startup for many minutes at production data volumes. 20260722_000000_backfill_request_log_useragent_families passed every gate and then ran for ~10 minutes over a ~3.2M-row request_logs on a production deployment, during which the app serves nothing (see #1470 for the runtime-side proposal).

Proposal

A nightly (or migrations-path-triggered) CI job that:

1. Seeds a PostgreSQL service container with a synthetic production-scale fixture (e.g. 3–5M request_logs rows with realistic column distributions; generation script checked in).
2. Runs python -m app.db.migrate upgrade across the revisions added since the last stable tag and records per-revision wall time.
3. Fails (or requires a long-migration label plus a release-notes callout) when any single revision exceeds a threshold (suggest 30s), so "this release needs a maintenance window" is visible at merge/release time instead of during the rollout.

Complements #1470: that issue removes the startup-blocking property for data backfills; this gate catches whatever remains blocking (schema rewrites, index builds without CONCURRENTLY, unexpected table scans).

Notes

• Threshold and fixture size should be config in one place so they can track production growth.
• Per-revision timing output belongs in the job summary for quick triage.

[Soju06]

Status audit (2026-07-27) of open PRs carrying new Alembic revisions, as a baseline for what this gate needs to catch — no open PR implements the gate itself yet.

Effectively instant at production scale (guarded, schema-only DDL on PostgreSQL):

• fix(usage): confirm workspace-less paid to free plan downgrades #1491 — new empty account_plan_downgrade_observations table.
• fix(proxy): preserve fresh bridge full resends #1487 / fix(proxy): preserve fresh bridge full resends #1486 — nullable TEXT column on http_bridge_sessions. Note both PRs ship the identical revision id 20260725_000000_add_http_bridge_pending_tool_calls; whichever lands second must drop or re-id its copy.
• fix(proxy): purge stale hard codex_session mappings pinned to a durably unavailable owner #1417 — nullable DateTime on sticky_sessions. Its down_revision points at 20260720_000000_add_request_log_conversation_id, skipping the 20260722 backfill and 20260724 rollup revisions, so it forks a second head — the Migration checks on that PR are already red.
• fix(http-bridge): stabilize silent and clean-close recovery #1394 / fix(http-bridge): unstarve the stuck-gate watchdog on silent upstreams (follow-up to #1394) #1410 — new http_bridge_retry_circuits table plus merge revisions; fix(http-bridge): stabilize silent and clean-close recovery #1394's 20260726_000000_repair_request_usage_rollups_after_merge re-invokes the guarded rollup upgrade, which only creates empty tables and a watermark (no raw request_logs scan).
• feat(proxy): subagent prompt-cache TTL and session lifecycle #1309 — nullable columns on settings tables; down_revision is stale at 20260717_000000_optimize_dashboard_hot_path_indexes and will fork a second head on rebase.

Would trip this gate (the #1470 anti-pattern):

• fix(db): persist security lineage markers #1183 (draft) — 20260722_000000_add_security_lineage_persistence runs row-by-row Python loops (fetchall() then per-row INSERT/UPDATE round trips) over sticky_sessions and http_bridge_sessions inside the startup migration, and on SQLite rebuilds usage_history via batch_alter_table with a drop/recreate of the idx_usage_window_account_* expression indexes. The row sets are filtered, but the loop is unbatched, non-resumable, and emits no progress.
• fix(proxy): reconcile continuity stack integration #1442 (draft) — same lineage family, with multiple fetchall()-and-loop backfill passes across seven revisions.

Both drafts should be reworked to batched, filtered, idempotent set-based statements (or a post-start data phase per #1470) before leaving draft. Everything else in flight passes the proposed 30s-per-revision threshold trivially.