Make the migration history replayable from an empty database

[bencap]

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 collapses the old migration history into one starting migration built from production's schema, keeps every recent migration as it is, 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

Migration	Problem
22e2d92d602e	Opens an ORM session, calls PubMed and bioRxiv, and commits in the middle of the upgrade. It imports today's mavedb.lib and models, so it breaks as they change
398067c53257	Uses autocommit_block(), which commits everything before it
b8f2c5a1d3e4 (RT, not yet shipped)	Imports its view definition from mavedb.models, so replaying it builds today's view, not the one it shipped with
alembic/manual_migrations/ (9 scripts)	One-off data scripts run by hand; not runnable as they are

Why this matters for RLS: the policy rollout needs upgrades that apply fully or not at all. A half-applied batch leaves tables with RLS forced on and no policy, and Postgres then hides every row.

Plan

Keep everything since the current head of main, 4dbf24ed1857, and replace everything before it with one migration. Every in-flight branch (release-2026.3.0, RT, 754 and the feature branches off them) builds on 4dbf24ed1857, so none of them change.

Build the new starting migration

1. Confirm prod and staging report 4dbf24ed1857 in alembic_version.
2. Take a schema-only dump of prod with --no-owner --no-privileges. Ownership and grants stay with the bootstrap script (Role topology and idempotent bootstrap script #827).
3. Replace 4dbf24ed1857's file with a migration that has the same revision ID, down_revision = None, and the dump as literal SQL. Because the ID is unchanged, prod and staging don't need restamping.
4. Add the reference data an empty database needs. controlled_keywords is seeded by migrations today; check for other lookup tables.
5. Delete the older revisions and alembic/manual_migrations/. Git keeps them.

Fix the two unshipped migrations that break the rules

6. Rewrite b8f2c5a1d3e4, on the RT branch, with its view definition as literal SQL.
7. Rewrite 15c40c367731, fix(score-sets): index functional classification variant fk to unblock score set deletion #874's index migration, to the non-concurrent form below, and build its index by hand in prod before 2026.3.0 deploys.

Keep it replayable

8. 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.
9. Add a CI job that runs alembic upgrade head on an empty database for every PR.

Document it

10. In alembic/README, describe the squash: at each cycle boundary, squash up to the head revision of the release before last, keeping its revision ID. Keep a log there of each squash's revision, release and date.
11. Add a release checklist to mavedb-deployment as docs/releasing.md, linked from its README, with the squash as a cycle-boundary step.
12. In .github/instructions/database.instructions.md, remove the Manual Migrations section, which tells authors to import scripts into upgrade(), and add the rules from step 8.
13. 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 4dbf24ed1857 on an empty database matches a schema-only dump of prod.
• alembic upgrade head on an empty database succeeds on main and on each shipping branch.
• The older revisions and alembic/manual_migrations/ are gone.
• CI fails when a migration imports from mavedb.*, uses autocommit_block() or uses CONCURRENTLY.
• alembic/README describes the squash and logs this one.
• database.instructions.md and database-dumps.md match the new procedure.
• mavedb-deployment's docs/releasing.md includes the squash step.