Alembic migrations

Purpose

Every table a service owns is born in a migration file, applied once per environment before the new code starts, and the running code refuses to boot against a database that is not at the revision it was written for. The invariant that makes it trustworthy is that the migration chain is the only writer of schema: no create_all() outside pytest, no hand-patcher at boot, no DDL typed into a live database, and exactly one head at every commit, so alembic upgrade head means one thing on every machine.

This foundation fixes, for every backend recipe, how a migration file is named and shaped, what it may and may not do, how the boot check and the deploy hook use it, and how a second service (Prima) keeps its own chain from ever touching the first one's.

When to use

Include it from every kind: backend recipe whose Data model section names a table, a column, a constraint or an index, and from _shared/fastapi-project-layout (S4), whose boot sequence calls the head check. Read it before writing alembic revision, before adding a value to a Postgres enum, before touching a table the baseline created, and before answering "why does the container crash-loop after the deploy". Trigger phrases: "add a column", "new table", "the enum needs a value", "alembic heads shows two", "stamp or upgrade", "migrate before or after the swap", "which database is this pointed at".

Do not use it for data backfills that belong to a job (those are _shared/scheduler-and-background-work, S12), for the test schema (pytest builds it from the models, see _shared/test-layout, S9), or for a client site on the Cloudflare stack, which has no Alembic: its D1 schema is EmDash's and is covered by _shared/astro-site-skeleton (S3).

Stack assumptions

Two chains exist and are measured here. They share the tool and the rules; they share nothing else.

Chain	Repo (branch, commit)	Version table	Revisions	Head
Labs	alchemy_labs (d1a-token-source, fd8558f; the backend tree is unchanged from feat/service-desk b1089b6)	alembic_version	91 files under backend/migrations/versions/, one base, one head	subscription_cancel_otp_001
Prima (D3: the reference for the PrimaAPI modules)	prima (feature/per-model-pricing, cd2c248)	prima_alembic_version	5 files under migrations/versions/, linear	prima_sparks_buckets_001

Versions, measured 2026-09-11: the Labs backend virtualenv resolves alembic 1.16.5 and SQLAlchemy 2.0.43 on Python 3.13.7; Prima's resolves alembic 1.14.0 and SQLAlchemy 2.0.36. CI runs the Labs chain on postgres:15 (.github/workflows/ci-cd.yml, job migration-check); the local test Postgres is postgres:14 (docker-compose.test.yml, service postgres-test, host port 5434); Prima's compose file runs postgres:16-alpine on 5433 (docker-compose.yml line 16). What is measured is narrower than that list: both chains build from empty on the local postgres:14 (2026-09-11, Acceptance checks 4 and 8); the CI job on postgres:15 has failed at dependency install on every recorded run of the workflow, six runs from 2026-06-01 to 2026-09-09 (Gotchas); no run of either chain on postgres:16 is recorded here.

Foundations assumed: S4 for where core/database.py and core/config.py sit (declared in depends_on, the way S5 and S7 declare it; S4 includes this recipe by reference for its boot check and does not list it back, so the graph stays acyclic); S9 for the scratch databases the acceptance checks use (S9 depends on this recipe, so it is not listed here). _shared/workspace-scoping (S6) owns the tenant column rules a migration implements; this recipe only says how the column arrives.

Environment names and fail behaviour:

• Labs: DATABASE_URL wins when set; otherwise DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD are assembled into it (core/config.py lines 357 to 371, DB_DSN aliased to DATABASE_URL). migrations/env.py line 92 overrides alembic.ini's sqlalchemy.url with settings.SYNC_DATABASE_URL, so the ini file's %(DB_USER)s placeholders (line 36) are never read. Missing values fall back to the config defaults (a localhost host on 5432 and a fixed database name, core/config.py lines 219 to 221), which is the wrong database rather than no database; the acceptance checks below always export the URL explicitly, and an upgrade typed with the variable unset on a developer laptop runs against whatever local database carries that name.
• Labs, one revision only: WEBAUTHN_RP_ID_BACKFILL. 20260803_0001_webauthn_rp_id.py refuses to run when the relying-party id it would backfill resolves to a development placeholder (localhost, 127.0.0.1, ::1, example.com, test; the set at line 67, the resolution at lines 70 to 108) unless the operator names the value explicitly. Measured 2026-09-11 on an empty scratch database: without the variable the chain stops at that revision with RuntimeError: Refusing to backfill webauthn_credentials.rp_id with 'localhost'; with WEBAUTHN_RP_ID_BACKFILL=localhost it completes. The rendering harness sets it (tooling/render/labs-local.sh line 105), the two per-seam test folders document it in their docstrings, and CI's migration-check job does not set it (see Gotchas).
• Prima: PRIMA_DATABASE_URL, read through prima.config.load_settings() and nothing else. alembic.ini deliberately carries no sqlalchemy.url (its own comment, lines 5 to 9), so a migration cannot be pointed at a different database than the application by editing an ini file, and it cannot run at all with the variable unset.

Data model

The chain's own state is one row per head in the version table; everything else in this section is the shape of a revision file.

Where files live and what they are called. Labs: backend/alembic.ini sets script_location = migrations (line 5), version_locations = %(here)s/migrations/versions (27), timezone = UTC (14) and truncate_slug_length = 40 (17), with a file_template (8) that would stamp YYYYMMDD_HHMM_<rev>_<slug>. The template is what alembic revision would emit; the chain as measured carries three generations of names, and only the last is the rule for new files:

Generation	Example file	Revision id	At fd8558f
Alembic default with the ini template (two from 2025-11-20, two merge revisions from 2026-05-04 and 2026-08-02)	20251120_1857_425a04794b53_update_onboarding_steps_add_form_.py	12-hex hash	4 files, legacy
Dated slug, a date and a letter or a bare date (2025-11 to 2026-05), plus the three undated 2026-08 files (baseline_prod_schema_001.py, deferred_fk_phantom_tables_001.py, folded_pending_migs_001.py)	20260408b_add_workspace_id_to_outreach.py	assorted strings	48 files (45 dated, 3 undated), legacy
Dated, numbered, semantic (the rule from 2026-06-09; two earlier files carry an HHMM stamp in the counter slot, 20251121_0815_manual_project_tables.py with the suffix-less id manual_project_tables and 20260512_0227_review_original_snapshot.py)	20260911_0002_subscription_cancel_otp.py	subscription_cancel_otp_001	39 files, the rule

The rule: the file is YYYYMMDD_NNNN_<slug>.py (the date the revision was written, a four-digit counter that restarts each day, a snake-case slug of at most 40 characters); the revision id is a string that reads as the slug plus a three-digit suffix (desk_estimate_001, leadsource_booking_001), never a hash, so alembic history, the boot log and an incident message name the change and not a checksum. Prima follows the same rule from its third file on (20260803_0001_auth_seam.py is prima_auth_001); its first two, 0001_prima_meter.py and 0002_prima_credits.py, predate the rule and carry prima_meter_0001 and prima_credits_0002. Prima's ids all start with prima_; four Labs ids do too (prima_key_sync_001, prima_credit_grants_001, prima_magic_link_tpl_001, prima_flex_removal_001, the Labs half of the Prima seams and their retirement), which is one reason the guard under Security & tenancy checks tables and not id prefixes.

The alembic.ini a new service starts from (Labs lines 1 to 12 and Prima's at cd2c248, less what each carries for its own history): script_location = migrations, prepend_sys_path = ., version_path_separator = os, timezone = UTC, truncate_slug_length = 40, the logger sections, and no sqlalchemy.url (Prima's comment at lines 5 to 9: the URL comes from the settings loader in env.py, so an ini edit cannot point a migration at another database). No file_template: revision files are written by hand in the shape above, and a generator run used as a draft (Application logic step 2) is renamed and re-id'd before it is committed. Labs' file_template (line 8) and its sqlalchemy.url placeholder (line 36, never read) are the reference's history, not the rule.

The file body. The rule for a new file, with 20260911_0002_subscription_cancel_otp.py (lines 1 to 11) as the model: a module docstring whose first line is the change in one sentence, then Revision ID:, Revises:, Create Date: (the Alembic header), then one paragraph on why the change exists and one sentence that says where the test schema comes from (Tests use Base.metadata.create_all() (models/commerce_models.py); this migration is for the live prod/staging schema., lines 8 to 10). Then revision, down_revision, branch_labels = None, depends_on = None, an upgrade() and a real downgrade(). Measured 2026-09-11 on the twelve Labs revisions dated 2026-09-01 or later: three carry the test-schema sentence (20260907_0001_leadsource_add_booking.py, 20260911_0001_workspace_documents.py, 20260911_0002_subscription_cancel_otp.py); the other nine put the header last in the docstring and say nothing about the test schema (20260904_0001_service_desk_core.py lines 1 to 14 is typical). The shape is the rule from here on, not a description of the tree.

What a revision may contain, in order of preference:

1. op.create_table(...) with every column typed, nullable stated on every column, server_default for anything the running old code will insert without naming (sa.text("gen_random_uuid()") for UUID primary keys, sa.func.now() for created_at, "0" for counters), indexes created by name in the same revision (ix_subscription_cancel_otps_email, lines 35 to 37 of the file above), including partial unique indexes, which the op API expresses as op.create_index(..., unique=True, postgresql_where=sa.text(...)) (20260905_0001_desk_domain_login.py lines 181 to 187, the WHERE verified_at IS NOT NULL AND disabled_at IS NULL index that tests/test_desk_domain_login_schema.py pins).
2. op.add_column(...) as nullable, or with a server_default, so the old container that is still serving during the migration keeps inserting (20260802_0002_surface_grants.py lines 76 to 79: magic_links.surface arrives nullable=False only because it carries server_default='labs').
3. A nullable column, a backfill UPDATE, then op.alter_column(..., nullable=False) in that order inside one revision when the column must end up NOT NULL over existing rows (20260803_0001_webauthn_rp_id.py lines 128 to 136).
4. A string column plus sa.CheckConstraint("<col> IN (...)", name="ck_<table>_<col>") for an enumerated value set (user_surface_grants.surface, ck_usg_surface, surface_grants lines 65 to 67; four CHECKs in 20260808_0001_sparks_billing.py; three in 20260904_0001_service_desk_core.py). Measured 2026-09-11: no revision dated after 20260710_0001 declares sa.Enum, postgresql.ENUM or CREATE TYPE; the 54 Postgres enum types in the schema at head are all baseline-era. Adding a value to one of those is op.execute("ALTER TYPE <name> ADD VALUE IF NOT EXISTS '<value>'") in a revision that inserts no row using the value (20260907_0001_leadsource_add_booking.py, whose docstring records why that is safe inside Alembic's transaction on the Postgres versions in use); its downgrade() is a documented no-op because Postgres cannot drop an enum value.
5. op.execute(...) for what the op API cannot express: the enum ADD VALUE in rule 4, a backfill UPDATE (20260905_0001_desk_domain_login.py line 230, that file's only op.execute), row-level security policies (20260504d_workspace_rls_policies.py, six op.execute calls). Raw DDL loses the downgrade() symmetry the op calls give for free, so it is the last choice, not the first.
6. The _phantom_guard helpers (add_column_if_missing, create_index_if_missing, create_foreign_key_if_missing and three more, backend/migrations/_phantom_guard.py) only in a revision that alters a table the baseline created in its final shape; the module's docstring says they are not a general escape hatch, and a revision on a table born in the chain uses the plain op calls so a wrong assumption fails loudly.

What is NOT in a revision: a sa.Enum(...) declaration (rule 4); an ondelete='CASCADE' on any Prima table (Security & tenancy); a raw COMMIT (one 2025-11-20 Labs file does it and it is the reason the chain is not atomic, see Gotchas); an import of app_fastapi or a service module (a revision that imports the application runs application code against a database mid-DDL; the one revision that imports core.config does so inside a function, as a guarded fallback, line 80 of the WebAuthn file); a data migration whose result depends on the environment it runs in without refusing when that environment is unset (the WebAuthn file is the pattern).

The naming convention the metadata carries (core/database.py lines 21 to 30): ix_<column label>, uq_<table>_<column>, ck_<table>_<name>, fk_<table>_<column>_<referred table>, pk_<table>. The convention reaches a revision because migrations/env.py passes target_metadata to context.configure, and Alembic applies it to op.create_table and the constraint operations. It does not treat every name the same way, and this is where a hand-written revision goes wrong (measured 2026-09-12 with SQLAlchemy 2.0.43 and Alembic 1.16.5, the Labs pins): a template that carries %(constraint_name)s (only ck here) prefixes an explicit short name, so sa.CheckConstraint(..., name="status_valid") on outbox_events is stored as ck_outbox_events_status_valid; a template without it (uq, pk, fk, ix) applies only to an unnamed constraint, so sa.UniqueConstraint("note_id") is stored as uq_notes_note_id while sa.UniqueConstraint("note_id", name="note_id") is stored as note_id, unprefixed. The rule for a revision: give a CHECK its short name and give nothing else a name (name=None), or write the full composed name wrapped so the convention leaves it alone (op.f("uq_notes_note_id") in a revision, sqlalchemy.sql.naming.conv(...) in a model). The stored names are then the ones a later op.drop_constraint passes without inspecting the database; S11's Gotcha records the CHECK half from the outbox tables. Prima names its CHECKs ck_prima_<table>_<what> (ck_prima_api_keys_scopes_nonempty, ck_prima_revocation_cursor_singleton, 20260803_0001_auth_seam.py lines 172 and 215) and its foreign keys fk_prima_<table>_<target> (165 to 200).

Prima's chain, in numbers (measured 2026-09-11 by running it on an empty scratch database): 5 revisions, 12 tables including prima_alembic_version, 5 foreign keys all ON DELETE RESTRICT, 0 with CASCADE, 50 CHECK constraints named ck_*. Every scalar character column carries a token CHECK, ck_<table>_<col>_token: the metered tables through prima/content_free.py (token_check, token_check_name, lines 221 to 227), which migrations/env.py runs by importing prima.models (lines 44 to 48); the identity tables on AuthBase, which that check does not govern, through _token_checks() in 20260803_0001_auth_seam.py (lines 64 to 76). That is how "content-free" is a property of the schema and not of the code that writes to it.

Labs' chain, in numbers (same method, same day): 91 revisions, 201 tables at head including alembic_version, 54 enum types. The base, baseline_prod_schema_001.py (4165 lines, down_revision = None), creates the roughly 100 tables that create_all() had made over the years and no migration had ever recorded, as verbatim pg_dump --schema-only bodies of the staging database taken 2026-08-02; deferred_fk_phantom_tables_001 adds their foreign keys at the end of the chain once every referenced table exists; folded_pending_migs_001 carries what the retired boot-time hand-patcher used to do. Three merge revisions exist (0aad13a9c18d, two parents, 2026-05-04; 422b303029b6, three parents, and flex_removal_converge_001, two, both from the 2026-08-02 convergence); a new one is the last resort, not the first response (Prima's chain test says why: two branches written blind of each other have an order, and a merge revision hides it).

API surface

No HTTP route. The contract is the command line and the boot sequence:

• alembic heads prints one line per head; the CI gate and the two chain tests assert exactly one. alembic current prints the row in the version table, and after any failed upgrade it is the first command to run (Gotchas). alembic history lists the chain by revision id, which is why ids are semantic.
• alembic upgrade head is issued by exactly one caller per environment: scripts/migrate.sh before the container tag is swapped (Deploy & ops), by hand from a one-off container when a migration shipped without it, by the rendering harness and the per-seam test folders against a scratch database, and by CI against a throwaway postgres:15. It is never in the image CMD (the script's own header, lines 24 to 30, on N replicas racing the same DDL).
• alembic downgrade -1 is real on both chains: CI runs upgrade head, downgrade -1, upgrade head (ci-cd.yml lines 301 to 303) on every push to the listed branches, so the newest revision's downgrade() is exercised once before it can ship.
• alembic stamp <rev> is not a routine command on either chain. The wiki records one use on 2026-08-02, when the staging database (built by create_all() and hand DDL, version table empty) was stamped at the baseline and then upgraded; ADR-0029 records why the same trick could not be applied to the production database of the day and what replaced it (Gotchas). A new service that follows this recipe from its first table never needs stamp.
• core.database.verify_schema_at_head() (Labs, lines 250 to 282): reads the code's heads through ScriptDirectory and the database's alembic_version rows, raises RuntimeError when the table is missing (Database has no alembic_version table) or when the two sets differ (Database is not at Alembic head. Current revision(s): {...}; code expects: {...}). init_db() (lines 289 to 331) calls it on every boot that is not pytest and re-raises, so the lifespan fails and the process exits. Under pytest init_db() calls Base.metadata.create_all() at most once per process instead (S9 says why).
• Prima has no boot-time head check at cd2c248; its guard runs on the migration side (Security & tenancy). The chain tests are the gate.

Application logic

How a schema change ships, in commit order:

1. Change the model in models/<area>_model.py (Labs) or prima/models/ and prima/auth/models.py (Prima). Register it: Labs' models/__init__.py imports every model class so Base.metadata is complete for the pytest create_all() path (its comments name three tables that were missing from it and failed hermetic tests with relation does not exist: scheduled_messages, the service-desk tables, user_surface_grants); Prima's migrations/env.py imports prima.models (line 48, which also runs the content-free schema check) and prima.auth.models (60) and lists both metadatas in target_metadata (62).
2. Write the revision by hand, in the shape from Data model. Do not run alembic revision --autogenerate on Labs and commit what it prints: measured 2026-09-11, the metadata that migrations/env.py's import list (lines 28 to 79) registers has 216 tables, the migrated database has 200 plus the version table, and the difference is 21 tables the running app never imports (files, file_versions, ml_predictions, user_psychographics and 17 more) plus 5 tables the chain creates and no model describes (brand_essence_drafts, knowledge_embeddings, platform_activity_buckets, primalayered_job_budgets, primalayered_spend_policy); autogenerate proposes creating the 21 and dropping the 5. make revision m='...' on Prima runs autogenerate against two small metadatas and is usable as a draft; its output still gets the checklist in script.py.mako applied by hand.
3. Chain it: down_revision is the current head and nothing else. Run alembic heads before committing; one line.
4. Prove it locally on a scratch database (S9): alembic upgrade head from empty, alembic downgrade -1, alembic upgrade head, then the model-shape test for the change (Tests).
5. Commit the model, the revision and the test together, with the revision id in the commit message.
6. Ship: scripts/migrate.sh runs alembic upgrade head under a Postgres session advisory lock (id 8110731, lines 57 to 64) before the new image is tagged live; the new container boots, verify_schema_at_head() passes, the log line Database schema verified at Alembic head (line 282) appears once per process.
7. Roll back, if needed, as schema plus image, in that order of execution. The old image refuses to boot against the newer schema for the same reason the new one refused against the older one, and it cannot undo that schema either: the revision file that holds the downgrade() exists only in the new image, and Alembic resolves the version-table row against the versions/ directory it is run from. Measured 2026-09-11 on a scratch database at subscription_cancel_otp_001 with a copy of the fd8558f tree that lacks 20260911_0002_subscription_cancel_otp.py: alembic current, alembic downgrade workspace_documents_001 and alembic upgrade head all stop with FAILED: Can't locate revision identified by 'subscription_cancel_otp_001' before touching the database (Acceptance check 5). So the rollback is alembic downgrade <previous head> from a one-off container of the new image, the same shape as the migrate step in Deploy & ops; alembic current from that container prints the previous head; then the tag swap back to the old image, whose boot check now finds the set it expects. The wiki names the downgrade and not the image (tooling/deployment.md, "Promoting to prod on the estate", verified 2026-09-05); S13 Deploy & ops names the retag.

Prima adds a step 0, in two halves. First, the chain owns its version table: migrations/env.py passes version_table=VERSION_TABLE to context.configure in both the offline and the online branch (lines 73 and 111), and the constant lives in prima/schema_guard.py (line 28, prima_alembic_version) so the guard and the runner cannot disagree; a service built from this recipe names its own table the same way (<service>_alembic_version) in one constant, because a chain left on Alembic's default alembic_version is what the guard treats as a Labs database (schema_guard.py line 67). Second, migrations/env.py inspects the live table list before configuring the context and raises NotPrimaDatabase if any of nine Labs marker tables or a bare alembic_version is present (line 86, prima/schema_guard.py; the nine are users, workspaces, workspace_users, workspace_settings, user_surface_grants, mcp_audit_logs, prima_wallets, prima_ledger, prima_user_profiles, lines 43 to 53, chosen because Prima will never create them). The marker set is chosen per service from tables it never creates and is not a constant to copy: a service that carries the S5 and S6 tables (users, workspaces, workspace_users, user_surface_grants) must not list them, or the guard refuses the service's own database after its first upgrade head (the 2026-09-12 rebuild copied the list and its alembic current raised the guard's error until the set was narrowed); then it calls connection.rollback() (106) before context.begin_transaction(), because under SQLAlchemy 2.0 the inspection opened an implicit transaction that Alembic would otherwise adopt and never commit (the comment at lines 88 to 105: "without it every migration silently does nothing", found when alembic current returned nothing after a green upgrade head). The connection.commit() at line 118 is belt and braces for the same reason.

Invariants

• Exactly one head and exactly one base at every commit. Labs: tests/v2/test_m1_alembic_chain.py (7 tests, static, no database; test_exactly_one_head, test_exactly_one_base, plus self-proving guards that build a broken in-memory map and assert the same checks go red) and the CI migration-check job (test -z "$(alembic heads | sed -n 2p)", line 300). Prima: tests/test_migration_chain.py (8 tests, parsed textually so they run without configuration or imports). Both passed 2026-09-11.
• The database at boot is at the code's head, or the process does not serve. Measured 2026-09-11 on the scratch database: after alembic downgrade -1, verify_schema_at_head() raises Database is not at Alembic head. Current revision(s): {'workspace_documents_001'}; code expects: {'subscription_cancel_otp_001'}; after upgrade head it logs the verified line.
• The chain builds from empty. Labs: 91 upgrades in 4 seconds on the local postgres:14 with the one variable set; Prima: 5 upgrades. A revision that assumes a table the chain did not create fails here first.
• Every revision downgrades one step and upgrades again (the local round trip in Acceptance checks; CI once its job is repaired, Gotchas).
• No sa.Enum in a revision after 2026-07-10; string plus CHECK for a value set; ADD VALUE IF NOT EXISTS for a legacy enum, in a revision that does not use the value.
• No ON DELETE CASCADE in Prima. tests/test_migration_chain.py::test_no_migration_creates_a_cascade walks the AST of a revision for ondelete="CASCADE" and for the word inside a sa.text or op.execute literal, so a comment that discusses the defect passes and a statement that recreates it fails.
• A revision that needs a value from its environment refuses to run rather than guess (the WebAuthn file). A revision that needs nothing from its environment imports nothing from the application.
• prima_alembic_version and alembic_version never coexist in one database by Prima's doing: the guard refuses the Labs table set before the first statement.
• The metadata the tests build (create_all()) and the schema the chain builds are documented as different sets (Gotchas), and a new revision's docstring says which one the change was tested against (three of the twelve September 2026 revisions do; Data model).

Frontend

No page renders a migration. The surfaces a person reads are:

CLI (operator, from the app image or a checkout)
  alembic heads          one line, the head id
  alembic current        the row in the version table, "(head)" when caught up
  alembic history        the chain by semantic id, newest first
boot log (every process)
  "Database schema verified at Alembic head: {'<id>'}"      core/database.py line 282
  "Database schema verification failed: <RuntimeError>"     line 329, then the process exits
CI (GitHub Actions, job "Database Migration Check")
  one head, upgrade head, downgrade -1, upgrade head on postgres:15

The minimum admin surface this recipe asks for is the boot log line above; there is no page in Labs at fd8558f that shows the running revision. A monitoring-ops row that lists each app's alembic current next to its .deployed_commit is planned and does not exist; the nearest existing pattern is the status surface S12 names for scheduler jobs.

Security & tenancy

• The migration runs as the application's own database role with the application's own URL, resolved by the application's own settings object (settings.SYNC_DATABASE_URL on Labs, load_settings().database_url on Prima). Neither chain reads a URL from an ini file; neither prints one. Alembic's own logging prints revision ids and messages, not the DSN; the acceptance checks below still filter output for the password field out of habit.
• Fail direction is closed on both chains: the app does not serve when behind (Labs), the migration does not run against the wrong database (Prima), the one environment-dependent backfill does not run without its value (Labs). Nothing in this recipe fails open.
• Prima's guard (prima/schema_guard.py): LABS_MARKER_TABLES is nine table names chosen because Prima will never create them under any design (users, workspaces, workspace_users, workspace_settings, user_surface_grants, mcp_audit_logs, and the three tables of the older in-Labs Prima copilot, prima_wallets, prima_ledger, prima_user_profiles); a bare alembic_version with no prima_alembic_version beside it is decisive on its own. The function is pure (a list of names in, an exception or nothing out) so tests/test_schema_guard.py (7 functions, 15 cases) covers it without a database. Measured 2026-09-11: pointed at the Labs scratch database, alembic upgrade head on Prima stopped with NotPrimaDatabase: Refusing to run Prima's migrations against this database. Labs tables are present: mcp_audit_logs, prima_ledger, prima_user_profiles, prima_wallets, user_surface_grants, users, workspace_settings, workspace_users, workspaces and executed no statement.
• Tenancy is a schema property: the tenant column is workspace_id (Labs) or tenant_id (Prima, the same UUID mirrored as a value, never a foreign key into Labs; prima/db.py lines 8 to 14), and the revision that adds it says how existing rows are treated: NOT NULL with a backfill or a server default when the value is known (magic_links.surface above), nullable when it is not and every read path treats NULL as legacy and unverified (20260903_0002_projects_workspace_id.py, whose docstring says why a guessed tenant is worse than none; S6 owns the rule). Prima's script.py.mako checklist pins two more: tenant_id keeps its original workspaces.id value because _row_hash hashes it first, and created_at on the meter stays a naive timestamp because it is inside the same hash; tests/test_migration_chain.py::test_created_at_is_not_timezone_aware_in_the_ledger guards the second.
• ON DELETE CASCADE is a tenancy defect on an append-only table: the in-Labs primaapi_usage_events.tenant_id cascades to workspaces.id (20260802_0001_primaapi_v1.py lines 56 and 82), so deleting a workspace deletes its ledger. D3 (2026-09-11) names that copy legacy; Prima's rule is RESTRICT everywhere, measured as 0 cascading foreign keys at head, and the AST test keeps it so for the two files it lists (Gotchas for the three it does not).
• Credentials at rest: none in a revision. The magic-link template seed (20260803_0004_seed_prima_magic_link_template.py) writes copy, not keys; the ad-token encryption revision (20260610_0002_encrypt_ad_oauth_tokens.py) reads the key from settings at run time and stores ciphertext.
• Concurrency: scripts/migrate.sh serialises concurrent upgrade head runs with pg_advisory_lock(8110731); a second run waits and finds nothing to do. Alembic itself has no lock.
• Attacker-controlled input: none reaches a migration. A revision never reads a request, a file upload or a queue.

Tests

The S9 file names and what each pins, at fd8558f and cd2c248:

• backend/tests/v2/test_m1_alembic_chain.py: one head, one base, every down_revision resolves, the self-proving red cases. Static; runs in 0.4 seconds; pytest tests/v2/test_m1_alembic_chain.py -p no:cacheprovider --no-cov printed 7 passed on 2026-09-11.
• backend/tests/test_desk_domain_login_schema.py: the migration-shape half reads 20260905_0001_desk_domain_login.py as text and asserts the seven additive columns and the partial unique index are declared; the behaviour half uses db_session. The pattern for any recipe whose invariant lives in a constraint: read the revision file, assert the constraint's name and predicate, then prove the constraint bites with an IntegrityError on the transactional session.
• backend/tests/test_webauthn_multi_rp.py::test_migration_backfills_the_relying_party (line 598): loads the WebAuthn revision as a module, sets WEBAUTHN_RP_ID_BACKFILL to a real relying party in os.environ (line 569) and runs its upgrade() (587), so it proves the backfill with the value set. The refusal path (_NOT_A_REAL_RP, _backfill_rp_id(), lines 67 to 108) has no unit test at fd8558f; Acceptance check 3 is the only place it is exercised. test_surface_rp_id.py tests the surface config alias and does not touch the migration.
• prima/tests/test_migration_chain.py (8 cases) and prima/tests/test_schema_guard.py (7 functions, 15 cases; one is parametrised over the nine marker tables, line 37): the chain shape and the guard, 23 passed in 0.03s together on 2026-09-11. Hermetic.
• prima/tests/test_credits_live.py, test_credit_grant_live.py, test_key_sync_live.py, tests/auth/test_repository_live.py: the CHECK constraints and the unique indexes against the migrated schema, opt-in through PRIMA_TEST_DATABASE_URL; their engine fixture skips when prima_credit_accounts is absent, because "building the tables from the ORM would make these constraint tests check the ORM against itself" (test_credits_live.py lines 51 to 53). 79 tests; 77 passed on the scratch database on 2026-09-11 and 2 failed for a reason recorded in S9's Gotchas (a hard-coded date, not a schema defect).
• The migration round trip itself: alembic upgrade head from empty, alembic downgrade -1, alembic upgrade head, on a scratch database on the S9 Postgres. CI is written to do this on postgres:15 and does not reach it (Gotchas); a recipe author does it locally before the commit. Known-answer vectors do not apply to DDL; the "vector" for a migration is the table count and the head id after the run, and both are in Acceptance checks.
• Playwright: not applicable. A migration has no user-facing state of its own.

Deploy & ops

Config keys per app heredoc (S13 owns the heredoc; these are the names it must carry): Labs DATABASE_URL (or the five DB_* parts; on the estate the parts are what the env file carries, and a script that expects the URL resolves it to empty, per the wiki), WEBAUTHN_RP_ID_BACKFILL set to the real relying-party host for the one revision that needs it (only matters on a database that has never run webauthn_rp_id_001; a database already past it ignores the variable), which no heredoc renders at alchemycloud b3d34da (S13 records the absence as a dated deviation on 2026-09-11; the estate's databases are past the revision, a fresh host's first migrate.sh is where it bites); Prima PRIMA_DATABASE_URL.

Migration order, per environment: build the image, run scripts/migrate.sh from that image against the environment's env file (a one-off container: docker run --rm --network host --env-file <env> -w /app/backend <image> sh /app/scripts/migrate.sh), then tag and restart. Never restart the old container between the migration and the swap: its own head check fails against the newer schema. Never put the migration in the image CMD. The wiring per platform is listed in the script's header (a release command, a pre-deploy command, a Kubernetes pre-rollout Job, a compose one-shot migrate service the app depends_on with service_completed_successfully).

Staging follows the same order. The older advice "never alembic upgrade head on staging, patch drift with idempotent DDL" (wiki tooling/deployment.md, the schema-drift section) is retired: since commit 99709a9 the boot check makes the opposite true, and the wiki records a 15-minute staging outage on 2026-08-25 from shipping a revision without applying it (/health answered nothing internally, 502 publicly, the container in a crash loop). The recovery is the one-off container above, then docker compose up -d --force-recreate.

Scheduler jobs: none. A migration runs once; anything that runs repeatedly is a job (S12).

Argus checks: no check reads the version table (argus, fix/collapse-host-blindness, e42e467, opened 2026-09-11; the word alembic appears only in a runbook regex). The boot refusal is the check: a process that is behind never answers /health, so the existing liveness probe on the app is what pages.

Rollback: schema first, then image, and the schema step runs from the new image. alembic downgrade <previous head> is issued from a one-off container of the image that shipped the revision, the same shape as the migrate step (docker run --rm --network host --env-file <env> -w /app/backend <new image> python -m alembic downgrade <previous head>), because only that image's migrations/versions/ carries the revision and its downgrade(). The old image cannot run downgrade, upgrade head or even current against a database that is ahead of its chain: each stops with FAILED: Can't locate revision identified by '<new head>' before any statement (measured 2026-09-11, Application logic step 7 and Acceptance check 5). Verify with alembic current from the same container, then retag to the old image and docker compose up -d; verify_schema_at_head() in the old image now finds the set it expects. A revision whose downgrade() is a no-op (an enum ADD VALUE) leaves the value in place, which the old code ignores. This supersedes the rollback sentence in S4's Deploy & ops ("re-point the image tag and re-run migrate.sh only when the migration is additive"): under the boot check an old image re-pointed at a newer schema crash-loops whether the revision was additive or not, because verify_schema_at_head() (core/database.py lines 273 to 280) compares sets, not directions, and migrate.sh re-run from that image fails on the same unknown revision instead of helping. The downgrade comes first, from the new image.

git archive for the shipped tree, never rsync of a working directory: the wiki records Alembic tripping over macOS ._* AppleDouble files copied into migrations/versions/ (SyntaxError: source code string cannot contain null bytes).

Gotchas

• 2026-09-11 (measured, fd8558f): the Labs chain is not one transaction. 20251120_1857_425a04794b53_update_onboarding_steps_add_form_.py lines 30 to 34 issue a raw COMMIT around its enum ADD VALUE, so everything after it in a single upgrade head run commits revision by revision. Observed: an upgrade head that stops at webauthn_rp_id_001 leaves the database at primaapi_v1_001 with 177 tables, not empty. After any failure, run alembic current before deciding anything; do not assume the run rolled back.
• 2026-09-11 (measured, fd8558f): CI's migration-check job is red before it reaches Alembic, and would be red after. Its Install dependencies step runs pip install -r requirements.txt from the repo root (.github/workflows/ci-cd.yml lines 281 to 283) where no such file exists (requirements.txt is under backend/; the backend-tests job in the same file sets working-directory: ./backend for exactly that reason, lines 66 to 72). Every run of the workflow that gh run list returns (six, 2026-06-01 to 2026-09-09) concludes failure, and in each the Database Migration Check job fails at Install dependencies. Behind that, the Test migrations step exports only the five DB_* variables (286 to 291) and no WEBAUTHN_RP_ID_BACKFILL, so on an empty database the chain would stop at webauthn_rp_id_001 with the refusal quoted under Stack assumptions. Two fixes, both in the job: the working-directory, then the env: line. Until both land, the local round trip in Acceptance checks is the gate that is known to be green.
• 2026-09-11 (measured, fd8558f): the chain and the models disagree, in both directions, at the table level. The running app's metadata has 196 tables and the migrated database has 200: aurum_signals exists as a model and not in the chain (its drop revision ran; ADR-0029 called it a phantom on 2026-08-07 and it is still in the tree); brand_essence_drafts, conversation_history, knowledge_embeddings, platform_activity_buckets and system_errors exist in the chain and have no model. migrations/env.py's import list adds 20 modules the app never imports, which is why autogenerate is unusable as a source of truth (Application logic step 2). ADR-0029 measured the same disagreement at 946 statements including constraints and enum labels on 2026-08-07 and owes a corrective revision; nothing in this recipe changes either side.
• 2026-09-11 (measured, fd8558f): the wiki's S8 row says "Postgres enum ADD VALUE in its own transaction" (operations/prima-modules-inventory.md §1.1). The code does not do that: the eleven ADD VALUE IF NOT EXISTS statements in the chain run inside Alembic's transaction (the 2025-11-20 file is the one exception, and it commits by hand for the opposite reason), and 20260907_0001_leadsource_add_booking.py records the actual rule in its docstring: safe inside the transaction as long as the same transaction does not insert the new value. The code wins; write it the 2026-09-07 way.
• 2026-09-11 (measured, fd8558f): the wiki's S8 row also says "never upgrade head on staging". Retired by commit 99709a9 (the boot check) and contradicted by the wiki's own 2026-08-25 correction two sections later. The code wins: staging is migrated the same way as production, before the swap.
• 2026-09-11 (measured, fd8558f): backend/migrations/versions/ holds 92 files, of which 20260121_add_tracking_site_id_to_projects.sql is not a revision and Alembic ignores it; alembic history prints 91 rows. modules/AUTHORING.md says 92 revision files in the harness paragraph; the number to quote is 91 revisions. The 40-odd .sql and run_*.py files one level up in backend/migrations/ are the pre-Alembic era and are run by nothing.
• 2026-09-11 (measured, fd8558f): migrations/env.py lines 19 to 27 say the import list must cover every module in backend/models/; seven modules are absent (prima_key_sync, prima_signup_intent_model, primalayered_spend_models, service_desk_model, sparks_billing, user_login_email_model, user_surface_grant_model), six of them reached anyway through models/__init__.py, one (primalayered_spend_models) not at all. Their tables exist in the chain, so nothing is broken; autogenerate would propose dropping the two primalayered_* tables, which is one more reason for step 2.
• 2026-09-11 (measured, cd2c248): Prima's test_no_migration_creates_a_cascade is parametrised over 0001_prima_meter.py and 0002_prima_credits.py only; the three later revisions (prima_auth_001, prima_key_sync_001, prima_sparks_buckets_001) are not in the list. They declare four RESTRICT foreign keys and no cascade (measured at the database: 0), so the invariant holds and the test does not yet say so. Add the file name to the list when adding a revision.
• 2026-09-11 (measured, cd2c248): migrations/env.py's docstring still says "the versions/ directory is empty and that is correct" and describes a Labs two-head fork that was converged on 2026-08-02; the code below the docstring is current, the prose above it is from the first commit. Read the guard and the rollback, not the paragraph.
• 2026-09-11 (measured, fd8558f): the alembic.ini file_template would name a new file 20260911_1432_<hash>_<slug> and give it a hash id; the rule in Data model is the YYYYMMDD_NNNN_<slug> name with a semantic id. Every non-merge revision since 2026-06-09 was written by hand in that shape; the one generator-shaped file after that date is the 2026-08-02 merge revision 20260802_1724_422b303029b6_converge_the_three_parallel_heads_from_.py, which kept the template's name and hash id. Write the file, do not generate it, or rename and re-id what the generator prints.
• 2026-08-25 (wiki, tooling/deployment.md): the staging crash loop from a shipped-but-unapplied revision, recorded above. The lesson generalises: the boot check turns "forgot to migrate" from a silent drift into an outage, on purpose, and the deploy path has to carry the migration step where a person cannot skip it.
• 2026-08-07 and 2026-08-08 (wiki, ADR-0029): a production database that was never under Alembic has no correct stamp point; reconciling it meant diffing against a reference and stamping head after the residual diff was empty, and the first reference chosen (an empty database taken to head) would have dropped nine live columns because the chain does not reproduce what create_all() had added over the years. The live staging schema was the correct reference. None of this applies to a service that follows this recipe from its first table; it applies to any service that adopts Alembic late.
• 2026-08-02 (wiki, tooling/deployment.md): "stamp first, then upgrade" on a database that already looks like the baseline; running upgrade first dies on the first CREATE TABLE that already exists. Also the origin of the merge revision 422b303029b6 and the baseline file.
• 2026-09-11 (measured, cd2c248): Prima's Makefile migrate and revision targets source .env into the shell before calling Alembic; alembic upgrade head typed directly in the directory with no PRIMA_DATABASE_URL in the environment fails at load_settings() naming the variable. That is the design (Stack assumptions), not a defect; a recipe that tells the reader to "run alembic" on Prima tells them to run make migrate.

Reference implementation

Paths are relative to each repo's root; commits as in measured_on and also_measured_on.

• alchemy_labs (d1a-token-source, fd8558f; backend files unchanged from feat/service-desk b1089b6):
  • backend/alembic.ini: script_location (5), file_template (8), timezone = UTC (14), truncate_slug_length = 40 (17), version_locations (27), the placeholder sqlalchemy.url (36).
  • backend/migrations/env.py: the comment on the import rule (19 to 27), the model import list (28 to 79), target_metadata = Base.metadata (89), the URL override from settings (92), compare_type and compare_server_default (113, 114, 138, 139), NullPool (131).
  • backend/migrations/_phantom_guard.py: column_exists, index_exists, constraint_exists and the six *_if_missing and *_if_exists helpers, with the docstring that limits their use.
  • backend/migrations/versions/: baseline_prod_schema_001.py (the base), deferred_fk_phantom_tables_001.py, folded_pending_migs_001.py, 20260802_1724_422b303029b6_converge_the_three_parallel_heads_from_.py (three parents), 20260803_0001_webauthn_rp_id.py (the refusing backfill, 67 to 108 and 119 to 139), 20260802_0002_surface_grants.py (string plus CHECK, 53 to 79), 20260907_0001_leadsource_add_booking.py (enum ADD VALUE), 20260905_0001_desk_domain_login.py (partial unique index through op.create_index, 181 to 187; the backfill op.execute, 230), 20260504d_workspace_rls_policies.py (six op.execute calls), 20260911_0002_subscription_cancel_otp.py (a complete small table, the shape to copy), 20251120_1857_425a04794b53_update_onboarding_steps_add_form_.py lines 30 to 34 (the raw COMMIT).
  • backend/core/database.py: naming convention (21 to 30), _run_pending_migrations_RETIRED (69), _running_under_pytest (233 to 237), _alembic_script_directory (240 to 247), verify_schema_at_head (250 to 282), init_db (289 to 331).
  • backend/core/config.py: DB_DSN aliased to DATABASE_URL (357), SYNC_DATABASE_URL (367 to 371).
  • backend/models/__init__.py: the registration list and its three explanatory comments.
  • scripts/migrate.sh: the header (1 to 32), the advisory lock and the single alembic upgrade head (47 to 77).
  • .github/workflows/ci-cd.yml: the migration-check job (253 to 303; the install step 281 to 283, the env: block 286 to 291), and the backend-tests job's working-directory comment (66 to 72) for contrast.
  • backend/tests/v2/test_m1_alembic_chain.py, backend/tests/test_desk_domain_login_schema.py, backend/tests/test_webauthn_multi_rp.py (566 to 598).
• prima (feature/per-model-pricing, cd2c248):
  • alembic.ini (no URL, lines 5 to 9), migrations/env.py (imports 48 and 60, two metadatas 62, version_table=VERSION_TABLE 73 and 111, guard 86, rollback 88 to 106, commit 118), migrations/script.py.mako (the checklist), prima/schema_guard.py (VERSION_TABLE 28, LABS_VERSION_TABLE 56, LABS_MARKER_TABLES, assert_not_labs_database, the bare-version-table test 67), prima/content_free.py (token_check, token_check_name, 221 to 227), prima/db.py lines 8 to 14 (mirrored values, not foreign keys), Makefile (migrate, revision), docker-compose.yml line 16 (Postgres 16 on 5433).
  • migrations/versions/0001_prima_meter.py, 0002_prima_credits.py, 20260803_0001_auth_seam.py (_token_checks, 64 to 76; the RESTRICT keys, 165 to 200), 20260804_0001_key_sync_seq.py, 20260808_0001_two_bucket_sparks.py.
  • tests/test_migration_chain.py, tests/test_schema_guard.py, tests/test_credits_live.py lines 51 to 53.
• argus (fix/collapse-host-blindness, e42e467): no reader of either version table.
• Wiki philosophers_stone (d4f5b95): tooling/deployment.md ("Alembic IS the schema source on v3", "Promoting to prod on the estate", the 2026-08-25 correction box), operations/decisions/0029-prod-schema-reconcile-diff-and-stamp.md, operations/prima-modules-inventory.md §1.1 row S8 and §2.

Deviations of the references from this recipe, all dated 2026-09-11: the chain is not atomic (the 2025-11-20 COMMIT); CI's migration job fails at dependency install and lacks the backfill variable; nine of the twelve September 2026 revisions omit the test-schema sentence; the WebAuthn refusal path has no unit test; the models and the chain differ by one phantom model and five model-less tables; env.py's import list is incomplete and over-complete at once; Prima's cascade test covers two of five files; Prima's env.py docstring is stale; the in-Labs PrimaAPI copy cascades on tenant_id (legacy under D3); the ini file_template does not produce the file name the rule asks for; Prima has no boot-time head check.

Acceptance checks

Run without the author at the commits named above, against a scratch database on the S9 test Postgres (127.0.0.1:5434, role test_user, database created for the run and dropped after). LABS is the alchemy_labs checkout, PRIMA the prima checkout, PY the Labs backend interpreter. Expected observations are exact.

1. cd $LABS/backend && $PY -m alembic heads prints one line, subscription_cancel_otp_001 (head). ls migrations/versions/*.py | wc -l prints 91; ls migrations/versions/*.sql | wc -l prints 1.
2. cd $LABS/backend && $PY -m pytest tests/v2/test_m1_alembic_chain.py -p no:cacheprovider --no-cov -q ends 7 passed.
3. Create s8_chain_scratch on the test Postgres. With DATABASE_URL pointing at it (SYNC_DATABASE_URL is a property derived from it, core/config.py lines 367 to 371, not a variable the config reads), ENVIRONMENT=development and WEBAUTHN_RP_ID_BACKFILL unset: $PY -m alembic upgrade head 2>&1 | grep -c "Running upgrade" prints 68 (67 revisions applied; the 68th line, Running upgrade primaapi_v1_001 -> webauthn_rp_id_001, is the revision that raises) and the run ends with RuntimeError: Refusing to backfill webauthn_credentials.rp_id with 'localhost'; $PY -m alembic current prints primaapi_v1_001 (branchpoint). Export the URL before anything else: with it unset the same command runs against the config default on the laptop's own Postgres (Stack assumptions).
4. Same database, WEBAUTHN_RP_ID_BACKFILL=localhost exported: upgrade head prints Running upgrade 24 more times (the refused revision's line appears in both runs, so 68 plus 24 covers the 91 distinct revisions; a single run from empty with the variable set prints 91) and alembic current prints subscription_cancel_otp_001 (head). SELECT count(*) FROM information_schema.tables WHERE table_schema='public' returns 201; SELECT count(*) FROM pg_type WHERE typtype='e' returns 54. From empty with the variable set, the whole run completes in under 10 seconds on a laptop (4 s measured).
5. Rollback order first, with the database at head: copy $LABS/backend (without .venv) to a scratch directory and delete migrations/versions/20260911_0002_subscription_cancel_otp.py from the copy (the old image); from the copy, $PY -m alembic heads prints workspace_documents_001 (head), and $PY -m alembic current, $PY -m alembic downgrade workspace_documents_001 and $PY -m alembic upgrade head each end FAILED: Can't locate revision identified by 'subscription_cancel_otp_001'. Back in $LABS/backend (the new image), $PY -m alembic downgrade -1 prints one Running downgrade subscription_cancel_otp_001 -> workspace_documents_001; from the copy, $PY -m alembic current now prints workspace_documents_001 (head) and the logging one-liner below prints the verified line once. Then, in $LABS/backend, $PY -c "from core.database import verify_schema_at_head; verify_schema_at_head()" raises RuntimeError: Database is not at Alembic head. Current revision(s): {'workspace_documents_001'}; code expects: {'subscription_cancel_otp_001'}; $PY -m alembic upgrade head then the same one-liner exits 0 with no RuntimeError (one config warning about EMAIL_TRANSPORT falling back to Resend prints to stderr in ENVIRONMENT=development, and no verified line, because -c configures no logging handler); $PY -c "import logging; logging.basicConfig(level=logging.INFO); from core.database import verify_schema_at_head; verify_schema_at_head()" 2>&1 | grep -c "verified at Alembic head" prints 1.
6. cd $LABS/backend && grep -lE "sa\.Enum\(|postgresql\.ENUM\(|CREATE TYPE" migrations/versions/2026{07,08,09}*.py | grep -v 20260710_0001 prints nothing. grep -c "ADD VALUE IF NOT EXISTS" migrations/versions/20260907_0001_leadsource_add_booking.py prints 1.
7. cd $PRIMA && .venv/bin/python -m pytest tests/test_migration_chain.py tests/test_schema_guard.py -p no:cacheprovider ends 23 passed.
8. Create s8_prima_scratch on the same Postgres. With the twelve PRIMA_* variables from tests/conftest.py's VALID_ENV exported and PRIMA_DATABASE_URL pointing at it: .venv/bin/alembic upgrade head prints five Running upgrade lines ending prima_key_sync_001 -> prima_sparks_buckets_001; .venv/bin/alembic current prints prima_sparks_buckets_001 (head). SELECT count(*) FROM information_schema.referential_constraints WHERE delete_rule='CASCADE' returns 0; ... WHERE delete_rule='RESTRICT' returns 5; SELECT count(*) FROM information_schema.table_constraints WHERE constraint_type='CHECK' AND constraint_name LIKE 'ck_%' returns 50; SELECT to_regclass('alembic_version') returns NULL and SELECT to_regclass('prima_alembic_version') does not.
9. Guard: PRIMA_DATABASE_URL pointed at s8_chain_scratch from check 4, .venv/bin/alembic upgrade head exits non-zero with prima.schema_guard.NotPrimaDatabase: Refusing to run Prima's migrations against this database. followed by Labs tables are present: and nine names; alembic_version in that database still holds subscription_cancel_otp_001 and no prima_alembic_version table was created.
10. cd $PRIMA && grep -c "connection.rollback()" migrations/env.py prints 1; grep -cE "^sqlalchemy.url" alembic.ini prints 0.
11. cd $LABS && sed -n 286,291p .github/workflows/ci-cd.yml | grep -c WEBAUTHN prints 0 and sed -n 275,283p .github/workflows/ci-cd.yml | grep -c working-directory prints 0 (the two CI gaps in Gotchas; each prints 1 once the job is fixed and this recipe's version is bumped with the date).
12. Drop both scratch databases. npm run check in this repo prints 0 problems.