Squash the migration history into one baseline so it replays from an empty database #877

Summary

We can't build a MaveDB database from scratch by running our migrations: alembic upgrade head fails on an empty database, and some migrations don't apply all-or-nothing. That blocks CI from testing migrations, blocks the new test setup, and makes the RLS rollout risky. This issue replaces the whole migration history up to the 2026.3.0 release with one starting migration built from production's schema, and adds rules and a CI check so the history stays replayable.

RT and 754 are branch names, explained in the glossary on #808.

What's broken

Why this matters for RLS: the policy rollout needs upgrades that apply fully or not at all. A half-applied migration can leave a table with RLS enabled and no policy, and Postgres then hides every row.

Plan

Replace every revision up to the head prod runs after 2026.3.0 (c5e1a9d4b7f2, unless a 2026.3.x patch adds a migration first) with one migration that keeps that revision ID. Prod and staging need no restamp. Do the work on the 2026.4 branch now; merge it once 2026.3.0 has been stable in prod for a few days, since the squash removes Alembic downgrades across that release. The rollback for 2026.3.0 is the pre-deploy RDS snapshot either way.

Build the baseline

1. Confirm prod and staging report the squash revision in alembic_version.
2. Take a schema-only dump of prod:

   pg_dump "host=<rds endpoint> dbname=mavedb user=postgres sslmode=require" --schema-only --no-owner --no-privileges -f prod-schema-<revision>.sql

   Ownership and grants stay with the bootstrap script (Split database roles into owner, migrate, worker and api #827).
3. Replace the head revision's file with a migration that has the same revision ID, down_revision = None, the dump as literal SQL, and a downgrade() that raises.
4. Seed the fixed vocabulary an empty database needs, from a data-only dump of prod: controlled_keywords, licenses, and the generic mondo_terms row. Add any other table the replay smoke test (acceptance criteria) shows the API can't run without.
5. Delete every older revision. Git keeps them.

Retire alembic/manual_migrations/

6. Move migrate_mapped_variants_to_allele_substrate.py to src/mavedb/scripts/. The RT backfill (feat: Retroactive backfill — migrate MappedVariant data and run reverse translation for existing score sets #747) and the 2026.3.0 runbook still run it, and backfill_campaign.py and audit_allele_identifiers.py reference its path. Update those references.
7. Delete the rest of the directory, including its README.

Re-parent in-flight branches

8. Re-parent the first migration on each open branch onto the baseline: feature/estelle/721/supersedingCalibration (off a7f3c2e9b104) and feature/estelle/795/premappedVRS (off c4b18d0f7a92).

Keep it replayable

9. Add these rules for new migrations:
   • No imports from mavedb.*. Write views and functions as literal SQL.
   • No network calls, ORM sessions or commits.
   • Nothing that can't run inside a transaction, such as CREATE INDEX CONCURRENTLY or autocommit_block(). For a large index, build it by hand before the deploy, and have the migration create the same index without CONCURRENTLY and with IF NOT EXISTS. In prod that does nothing; on an empty database it builds the index.
10. Add a CI job that runs alembic upgrade head on an empty database for every PR, and a check that fails on the forbidden patterns in migration code. The check ignores docstrings, which describe the build-by-hand step.

Document it

11. In a new alembic/README, describe the squash: at each cycle boundary, once the last release is stable in prod, squash up to its head revision and keep that revision ID. Keep a log there of each squash's revision, release and date.
12. Add a release checklist to mavedb-deployment as docs/releasing.md, linked from its README, with the squash as a cycle-boundary step.
13. In .github/instructions/database.instructions.md, replace the Manual Migrations section, which tells authors to import scripts into upgrade(), with the rules from step 9. Remove the manual_migrations reference in .github/instructions/copilot-instructions.md.
14. In mavedb-deployment's docs/database-dumps.md, note that a dump loads against a checkout of the tag it was made from.

Acceptance criteria

• alembic upgrade head on an empty database produces a schema that matches a schema-only dump of prod.
• On a freshly replayed database, the API starts and can create and publish a score set.
• alembic/versions/ holds only the baseline and migrations added after it.
• alembic/manual_migrations/ is gone, and the allele substrate backfill runs from src/mavedb/scripts/.
• CI fails when a migration imports from mavedb.*, uses autocommit_block() or uses CONCURRENTLY outside a docstring.
• alembic/README describes the squash and logs this one.
• database.instructions.md, copilot-instructions.md and database-dumps.md match the new procedure.
• mavedb-deployment's docs/releasing.md includes the squash step.