Migration gate runbook

System: Console Alembic migration gate + Raptor Alembic migration gate (Woodpecker step migration-round-trip in .woodpecker/migration-round-trip.yaml) Owner: sre-agent / operator Last incident: 2026-05-11 (gate broken from day one — missing kris@ seed row, missing raptor_app role; fixed in PR #1654 and PR #1656) Last reviewed: 2026-09-13 Enforcement status: ⚠️ ADVISORY — NOT merge-blocking. See Enforcement status below.

2026-09-07 correction (migrations-grants sweep item 3): everything below was written about the GHA migration-gate job in ci-pr.yml (PR #1623). That job was retired along with the rest of GHA during the Woodpecker cutover and never re-ported — for roughly 4 months this entire runbook described a gate that did not run anywhere. It has been re-ported as the migration-round-trip step in .woodpecker/migration-round-trip.yaml, delegating the actual round-trip logic to scripts/ci/migration_round_trip.sh (runnable locally against a throwaway Postgres, independent of any CI platform). Every mention of ci-pr.yml / the GHA migration-gate job below refers to file/job names that no longer exist — read it as describing .woodpecker/migration-round-trip.yaml and scripts/ci/migration_round_trip.sh instead. The underlying mechanics (two-phase Console seed, single-head assertion, downgrade -1 round-trip) are unchanged and this file's recovery procedures still apply.

Enforcement status (read this before relying on this gate)

2026-09-13 correction. The 2026-09-07 change that added .woodpecker/migration-round-trip.yaml described it — in its commit message, in its own file header, and in the CI coverage table in docs/ops/runbooks/console-migrations.md — as landing "as a hard PR-blocking check." That claim was wrong as shipped, and is corrected here.

What is actually true today:

• .woodpecker/migration-round-trip.yaml is a standalone Woodpecker pipeline file. Woodpecker reports one GitHub commit-status context per pipeline file, named ci/woodpecker/pr/<pipeline-filename> — so this gate reports as ci/woodpecker/pr/migration-round-trip.
• develop's branch protection requires exactly two contexts:

ci/woodpecker/pr/ci-pr ci/woodpecker/pr/pii-scan

Verify at any time (read-only):

gh api repos/raxx-app/TradeMasterAPI/branches/develop/protection \ --jq '.required_status_checks.contexts'

• ci/woodpecker/pr/migration-round-trip is not in that list. A PR whose migration round-trip goes red can therefore still be merged into develop. This gate is advisory until branch protection is updated.

Contrast: the migration-grant lint IS enforced

The same sweep's item 1 added a migration-grant-lint step inside .woodpecker/ci-pr.yaml. Because it is a step in an already-required pipeline, its failure fails the ci/woodpecker/pr/ci-pr context, which is required — so that one is genuinely merge-blocking (on PRs matching its path filter: backend_v2/alembic/versions/**, backend_v2/db/migrations/**, and the lint scripts themselves). This is the practical difference between a step in ci-pr.yaml and a standalone pipeline file, and it is the reason the round-trip gate's own claim did not hold.

Why this was not fixed in the same change

Editing develop's required status checks affects every PR in the repo (a red required check blocks all merges, including unrelated ones, and the first run of a newly-required context can block PRs that never trigger it). That is an operator decision, not something a migration sweep should change as a side effect. The record is corrected; the setting is untouched.

Follow-up — TODO (operator action required)

To make this gate genuinely merge-blocking, add its context to develop branch protection:

ci/woodpecker/pr/migration-round-trip

Do this only after confirming the pipeline has reported at least one green run on a real PR (so the context name is observed rather than assumed), and note that a path-filtered pipeline may report no status at all on PRs that do not touch migrations — confirm how that interacts with a required check before flipping it, or the whole repo can wedge.

This is a pre-existing, repo-wide pattern, not unique to this gate. .woodpecker/migration-collision-check.yaml's own header comment declares itself a "REQUIRED STATUS CHECK" with context ci/woodpecker/migration-collision-check/check; neither that string nor its real context (ci/woodpecker/pr/migration-collision-check) is in develop's required list either. The Wave A/B branch-protection swap planned in docs/architecture/woodpecker-migration-table-2026-07-04.md §7 was evidently never executed as written. Any fix should cover the whole set in one deliberate pass rather than one pipeline at a time.

What the migration gate does

The migration-round-trip Woodpecker step (.woodpecker/migration-round-trip.yaml, re-ported from the retired GHA migration-gate job in ci-pr.yml, PR #1623, closes issue #1552) runs automatically on every PR that touches:

• console/migrations/** (Console Alembic tree)
• backend_v2/alembic/versions/** (Raptor Alembic tree)

It spins up an ephemeral postgres:15 service container and runs a full round-trip on both migration trees:

upgrade head → assert single head → downgrade -1 → upgrade head

Each tree gets its own ephemeral database (console_ci, raptor_ci) so there is no schema bleed.

What it catches before merge:

The gate runs against an ephemeral database with no vault credentials. This bypasses the RDS IAM restriction that prevents CREATE ROLE WITH PASSWORD on Heroku Standard-0 (feedback: heroku_pg_rds_password_gotcha.md).

Seed requirements

The ephemeral CI database starts empty. Two categories of "environment prerequisites" that exist in prod but not in a fresh Postgres must be supplied by the gate itself before running alembic upgrade head.

1. Console: kris@moosequest.net admin row (required by migration 0016)

Migration: console/migrations/versions/0016_raxx_platform_owners_group.py

Why it is required: Migration 0016 seeds the raxx-platform-owners RBAC group and enrolls the operator (kris@moosequest.net) as its first member. Step 3 of upgrade() executes SELECT id FROM admins WHERE email = :email. If the row is absent it raises:

RuntimeError: 0016 migration: operator 'kris@moosequest.net' not found in the admins table.

This is intentional on prod — the operator account must exist before the platform-owners group is seeded. In CI there is no prior data, so the gate supplies a stub row. Heroku review apps are the same situation (a fresh empty database per PR), and console/scripts/migrate.sh supplies the row for them via console/scripts/seed_review_app_operator.py when REVIEW_APP=true (see console-review-apps.md Failure mode H). 0016 itself is never relaxed.

How the gate supplies it (two-phase upgrade, PR #1654):

1. Console — alembic upgrade to 0015 (pre-seed) — brings the schema up to the migration that creates the admins table.
2. Console — seed kris@ admin row (required by 0016) — inserts the stub row:

INSERT INTO admins (id, email, display_name, created_at, updated_at, disabled_at)
VALUES (
  '00000000-0000-0000-0000-000000000001',
  'kris@moosequest.net',
  'CI Seed — do not use in prod',
  '2026-01-01T00:00:00',
  '2026-01-01T00:00:00',
  NULL
)
ON CONFLICT (email) DO NOTHING;

3. Console — alembic upgrade head — completes from 0016 onward.

Why ON CONFLICT DO NOTHING: The round-trip downgrade -1 / upgrade head must not fail on the second upgrade head. Migration 0016's downgrade() only removes rbac_user_groups membership — the admins row is untouched — so the seed is still present for the second upgrade.

Columns seeded: id, email, display_name, created_at, updated_at, disabled_at. The admins table has no password columns (schema from migration 0001). The stub UUID and timestamps are CI-only and are never used for authentication.

2. Raptor: raptor_app Postgres role (required by migration 0002+)

Migration: backend_v2/alembic/versions/0002_paper_orders.py (and all subsequent Raptor migrations that add tables)

Why it is required: Migration 0002 executes:

GRANT SELECT, INSERT, UPDATE, DELETE ON paper_orders TO raptor_app;
GRANT USAGE ON SEQUENCE paper_orders_id_seq TO raptor_app;

On prod, the raptor_app role is provisioned during the Postgres Standard-0 setup (RM-9), outside of Alembic. The CI ephemeral database has no such provisioning. Without the role, upgrade head fails with:

psycopg2.errors.UndefinedObject: role "raptor_app" does not exist

How the gate supplies it (PR #1656):

DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'raptor_app') THEN
    CREATE ROLE raptor_app NOLOGIN;
  END IF;
END $$;

This runs before alembic upgrade head on the Raptor tree. The role has no login privilege and no initial permissions — the GRANT statements inside each migration attach the needed permissions.

Rule for future data-dependent migrations

Any migration that queries an existing table for lookup data OR executes a GRANT to a named role is a seed-dependent migration. The gate must be updated in the same PR as the migration. The pattern:

1. Add a new CI step that runs alembic upgrade <N-1> (up to the migration before the one with the dependency).
2. Insert or create the required data/role idempotently (ON CONFLICT DO NOTHING / IF NOT EXISTS).
3. Continue with alembic upgrade head.

Every seed INSERT must use ON CONFLICT DO NOTHING. Every role creation must use the IF NOT EXISTS guard (or the DO $$ BEGIN IF NOT EXISTS ... END $$ block for Postgres < 9.6 syntax, though CI uses Postgres 15). This ensures the gate remains idempotent across re-runs and round-trips.

How to tell it is broken

• migration-gate job in GitHub Actions shows FAILED for a PR touching console/migrations/** or backend_v2/alembic/versions/**.
• The failing step name is one of:
• Console — alembic upgrade to 0015 (pre-seed) — pre-seed step failed
• Console — seed kris@ admin row (required by 0016) — psql seed failed
• Console — alembic upgrade head — a migration errored
• Console — assert single head — multiple heads detected
• Console — downgrade -1 (reversibility check) — downgrade errored
• Raptor — create raptor_app role (required by 0002+) — role creation failed
• Raptor — alembic upgrade head — a Raptor migration errored
• Raptor — assert single head — multiple Raptor heads detected

How to diagnose (in order)

1. Open the failing CI run. Navigate to the migration-gate job and expand the failing step. The Alembic stack trace or psql error is the primary signal.

2. Classify the error.

3. For "Multiple heads" errors: Run locally to identify the conflicting revisions:

```bash # Console cd console && alembic -c migrations/alembic.ini heads

# Raptor cd backend_v2 && alembic heads ```

Each output line marked (head) is a branch tip. Find the two migrations that share the same down_revision value.

4. For seed row / role errors: Confirm the CI workflow has the corresponding seed step before alembic upgrade head. If it is missing, the fix is a new seed step in ci-pr.yml (see "Recovery steps" below).

Known failure modes

Failure mode A: Missing admin seed row

Symptom: Console — alembic upgrade head fails with:

RuntimeError: 0016 migration: operator 'kris@moosequest.net' not found in the admins table.

Cause: A new migration was added that runs before or alongside 0016 and depends on the same seed, OR the seed step was removed or reordered in a ci-pr.yml change.

Fix:

Verify the seed step is present and runs after alembic upgrade 0015 but before alembic upgrade head. If the seed step exists but the error still fires, the cause is likely a new migration that executes before 0016 and operates on data that requires a different seed row — diagnose by reading the new migration's upgrade() body.

If a completely new migration introduces its own seed requirement, add the corresponding seed step following the pattern established by PR #1654.

Verification: Re-run the migration-gate job. Console — seed kris@ admin row (required by 0016) must exit 0, followed by Console — alembic upgrade head exiting 0.

Failure mode B: Missing Postgres role

Symptom: Raptor — alembic upgrade head fails with:

psycopg2.errors.UndefinedObject: role "raptor_app" does not exist
LINE 1: GRANT SELECT, INSERT, UPDATE, DELETE ON <table> TO raptor_app

Cause: A new Raptor migration GRANTs to raptor_app (or another named role) that the gate's role-creation step does not cover. Most commonly this happens if a new Raptor migration GRANTs to a role other than raptor_app.

Fix:

Add a role-creation step to the Raptor section of the migration-gate job in ci-pr.yml, modeled on the existing raptor_app step:

DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '<new_role_name>') THEN
    CREATE ROLE <new_role_name> NOLOGIN;
  END IF;
END $$;

Place this step before Raptor — alembic upgrade head.

Verification: Re-run the migration-gate job. The new role-creation step must exit 0, and Raptor — alembic upgrade head must exit 0 after it.

Failure mode C: Duplicate revision (multiple heads)

Symptom: Console — assert single head or Raptor — assert single head fails with output like:

Console head count: 2
ERROR: Console alembic has 2 heads — expected exactly 1.

Or alembic upgrade head itself may error:

DETAIL: Multiple heads are present; please specify the target revision.

Cause: Two migrations were merged into main that both set down_revision to the same parent revision. This is the duplicate-revision collision pattern. Historical examples:

• PR #1548, #1550: 0022, 0024 both set down_revision = "0021" / "0022" respectively — caused by concurrent feature branches merging without rebasing.
• PR #1778, #1781 (0038 chain): 0037_promote_trace_mode_transitions.py and 0038_promote_demo_paper_fill.py both claimed revision "0038". Fixed by renumbering:
• PR #1781 (2225e2f2): renamed 0037_promote_trace_mode_transitions.py → 0038_promote_trace_mode_transitions.py, updated revision = "0038".
• PR #1778 (fd175f8e): renamed 0038_promote_demo_paper_fill.py → 0040_promote_demo_paper_fill.py, updated revision = "0040" and down_revision = "0039", producing the correct linear chain: ...0037 → 0038 (trace_mode) → 0039 (auth_backup_codes) → 0040 (demo_paper_fill).

Fix:

Identify the two colliding migrations. Choose the one that was merged later (higher PR number) and renumber it to the next available revision ID:

1. Rename the file: XXXX_old_name.py → YYYY_old_name.py where YYYY is the next unused number.
2. Update revision = "YYYY" inside the file.
3. Update down_revision = "ZZZZ" to point to the correct predecessor.
4. Verify the chain is linear: alembic -c console/migrations/alembic.ini heads should return exactly one (head).

The fix can be committed directly to a PR branch. The migration-gate job on that PR will validate the chain.

Verification: Console — assert single head or Raptor — assert single head must report head count: 1.

Failure mode D: Postgres-incompatible index predicate

Symptom: alembic upgrade head fails with:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidColumnDefinition)
ERROR:  cannot use a non-immutable function in index expression

Cause: A migration creates a partial index with a WHERE clause that uses a non-IMMUTABLE function (e.g., CURRENT_TIMESTAMP, now()). Postgres requires index predicates to be IMMUTABLE. This was the failure mode in PR #1551.

Fix: Change the index predicate to use only IMMUTABLE expressions (literal values, IMMUTABLE-marked functions). If a time-bounded partial index is genuinely needed, use a fixed literal timestamp or restructure the index.

Verification: alembic upgrade head must exit 0. Confirm the index exists with the correct predicate:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = '<table>';

Failure mode F: DuplicateTable — two migrations both create the same table

Symptom: Raptor — alembic upgrade head fails with:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable)
ERROR:  relation '<table_name>' already exists

Cause: Two migrations in the linear chain both call op.create_table('<table_name>', ...). Because both run in sequence on a fresh Postgres database (as in CI), the second create fails. This typically happens when a later migration is written with the incorrect assumption that the earlier migration only exists in a different schema chain (e.g., a SQLite-only legacy chain) and does not apply to the Postgres Alembic chain.

First observed: paper_orders duplicate between 0002_paper_orders.py and 0022_mbt_paper_tables.py (fixed in PR that resolves the Settings 6-tab rebuild gate failure, 2026-05-31).

Fix:

The later migration must explicitly drop the earlier table before re-creating it. Determine which schema is authoritative post-migration:

1. If the later migration replaces the earlier table with a different schema (different columns/purpose), add op.drop_index(...) + op.drop_table(...) calls at the top of the later migration's upgrade(), before the op.create_table(...) call. Add the inverse (recreate the earlier schema) to the later migration's downgrade() so the chain remains consistent if the operator rolls back.

2. If the later migration is redundant (exactly the same schema), delete the duplicate op.create_table block and replace it with a comment explaining the earlier migration already created the table.

Pattern for case 1 (schema replacement):

def upgrade() -> None:
    # Drop earlier table first — <earlier_migration> created this with a
    # different schema. This migration replaces it.
    op.drop_index("<earlier_index>", table_name="<table_name>")
    op.drop_table("<table_name>")
    # ... now create the new version
    op.create_table("<table_name>", ...)

def downgrade() -> None:
    # Drop new version
    op.drop_table("<table_name>")
    # Restore earlier schema so the chain is consistent
    op.create_table("<table_name>", ...)  # mirror of earlier migration's schema

Verification: alembic upgrade head must exit 0 on a fresh Postgres. Confirm the final table schema matches the later migration's intent:

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = '<table_name>' ORDER BY ordinal_position;

Failure mode E: Broken down_revision pointer

Symptom: alembic upgrade head fails with:

alembic.util.exc.CommandError: Can't locate revision identified by '<hash>'

Cause: A migration's down_revision references a revision ID that does not exist in the chain. This happens when a migration file is deleted, renamed without updating the successor's down_revision, or when a PR adds a migration that references a revision that has not merged yet.

Fix: Update the down_revision in the orphaned migration to point to the correct predecessor revision ID. Run alembic history to see the full chain and identify the gap.

Verification: alembic history --verbose must show a contiguous chain with no gaps.

First failure / next step decision tree

migration-gate fails
│
├── Step: "Console — alembic upgrade to 0015 (pre-seed)"
│   └── Cause: schema error before 0015 — look at migrations 0001–0015
│       → Feature-dev owner of the failing migration must fix it.
│
├── Step: "Console — seed kris@ admin row (required by 0016)"
│   └── Cause: psql syntax error or admins table missing
│       → Check that 0015 actually created the admins table (gate step ordering)
│       → If admins table is missing: upgrade-to-0015 step may have silently exited 0 on an error
│       → Escalate to operator if the admins schema has been modified in a recent migration
│
├── Step: "Console — alembic upgrade head"
│   ├── RuntimeError: operator not found in admins table
│   │   → A new migration requires a seed row that the gate does not supply.
│   │   → SRE adds seed step to ci-pr.yml. Must ship in the same PR as the migration.
│   ├── psycopg2.errors.UndefinedObject (role)
│   │   → A new migration GRANTs to a role the gate does not create.
│   │   → SRE adds role-creation step to ci-pr.yml. Must ship in the same PR as the migration.
│   ├── Other psycopg2 error
│   │   → Migration-specific Postgres incompatibility — feature-dev fixes the migration.
│   └── alembic.util.exc.CommandError: Can't locate revision
│       → Broken down_revision pointer — feature-dev fixes the chain.
│
├── Step: "Console — assert single head" / "Raptor — assert single head"
│   └── head count > 1
│       → Duplicate revision collision. Identify and renumber the later migration.
│       → Feature-dev (whichever PR introduced the collision) fixes it.
│       → SRE verifies the fix resolves the gate before approving.
│
├── Step: "Console — downgrade -1" / "Raptor — downgrade -1"
│   └── Downgrade errored
│       → The migration's downgrade() function has a bug.
│       → Feature-dev fixes the downgrade() path. All migrations must be reversible.
│
├── Step: "Raptor — create raptor_app role (required by 0002+)"
│   └── DO block errored — likely psql connectivity issue or syntax
│       → Check the service container is healthy (rare; usually a transient CI issue)
│       → Re-run the job once. If it fails a second time, file a type:reliability issue.
│
└── Step: "Raptor — alembic upgrade head"
    ├── psycopg2.errors.UndefinedObject (role)
    │   → New migration GRANTs to a role the gate does not create → SRE adds role step
    ├── psycopg2.errors.DuplicateTable
    │   → Two migrations both create the same table (see Failure mode F)
    │   → grep for op.create_table('<table>') across all versions files
    │   → Later migration must drop the earlier table before re-creating (or skip if redundant)
    └── Other error
        → Feature-dev fixes the migration.

Escalation

When SRE handles it directly

• Missing seed row: SRE adds the seed step to ci-pr.yml in the same PR as the migration.
• Missing role: SRE adds the role-creation step to ci-pr.yml in the same PR as the migration.
• Duplicate revision (multiple heads): SRE coordinates with feature-dev to renumber; SRE validates the fix.
• Transient CI infrastructure failure (Postgres service container fails to start): re-run the job; file a reliability issue if it recurs.

When to escalate to feature-dev

• The migration's upgrade() or downgrade() body contains a bug (Postgres-incompatible expression, wrong SQL, broken FK ordering).
• A broken down_revision pointer — requires the migration author to confirm the intended predecessor.
• A new seed requirement that was not communicated before merge.

When to escalate to operator

• The gate itself is being bypassed or disabled in ci-pr.yml without a documented reason.
• A migration failure in prod (not CI) — the prod migration was never gated, implying the migration touches schema that the gate does not cover.
• Any migration that touches payment, audit, or RBAC schema and fails — treat as SEV-2 until confirmed non-data-loss.
• The gate is failing consistently across all PRs despite no migration changes (CI infrastructure issue).

Contact: operator (kris@moosequest.net) via Slack #ops or direct message (D0AJ7K184TV).

Related references