migration-review

Migration review lens

A schema change is the one part of a deploy a redeploy cannot undo. For every migration ask three questions: what happens on the live database while it runs (locks, rewrites, duration); what happens mid-rollout, when old and new code both run against one schema; and how do we get back. A finding is a concrete defect: a named statement, the lock or data loss it causes, and the safe form.

Steps

1. Inventory every schema and data change: migration files, ORM model or schema changes that generate migrations (read the generated SQL — Prisma and some ORMs turn a rename into drop + add), seed and backfill scripts, queries that assume the new shape. Call search_lore for the engine and version, the migration tool, deploy order and any online-migration rules.
2. Simulate the rollout (expand → migrate → contract). Old code keeps running against the new schema until rollout completes; new code may run against the old schema if the migration runs after deploy. Every combination must work: a dropped or renamed column old code still reads breaks the deploy; a NOT NULL column without a default breaks old inserts; an ORM that selects every mapped column fails on a dropped one.
3. Check locks and rewrites for the engine in use (checklist below). On PostgreSQL remember the lock queue: an ALTER TABLE waiting for its ACCESS EXCLUSIVE lock behind a long query blocks every query after it — DDL on a busy table needs a short lock_timeout (with retry) so it fails fast instead of queueing.
4. Check reversibility. A working down that does not destroy data, or a documented forward-fix. Destructive steps (drop column/table, delete rows, narrowing a type) are in a later deploy than the code change that stopped using them, and data is copied or archived first.
5. Check data integrity. Constraints match the model's validations (NOT NULL, unique, foreign keys with a deliberate ON DELETE); backfills are idempotent, batched, resumable and outside the schema transaction; enum/status additions are additive; the migration works on a table that already has rows, including rows that violate the new constraint.
6. Check the tests. If an AC depends on existing data surviving (a rename, a type change, a backfill), a test applies the migration to a database seeded with old-shape rows and asserts the data afterwards — an empty-database migration run proves only that the SQL parses (the test-quality-review skill).
7. Rate each finding. Blocking: data loss; a table-rewriting or long-locking statement on a table the app writes during deploy; a deploy-order break; an irreversible destructive step without a preceding phase; a constraint that fails on existing rows. Should-fix: a missing constraint the model assumes; an unbatched backfill on a large table. Note: a missing index for a new query (the sql-query-performance skill), unless that query becomes unbounded on a table that grows with user input (then it is blocking under performance-review); naming and style. Only blocking and should-fix justify RECOMMEND CHANGES.
8. Record each finding with record_ac_evidence (evidence_type: manual_note: statement, hazard, safe form), then let the review-ticket verdict carry the result.