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
- 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_lorefor the engine and version, the migration tool, deploy order and any online-migration rules. - 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.
- Check locks and rewrites for the engine in use (checklist below). On PostgreSQL
remember the lock queue: an
ALTER TABLEwaiting for its ACCESS EXCLUSIVE lock behind a long query blocks every query after it — DDL on a busy table needs a shortlock_timeout(with retry) so it fails fast instead of queueing. - Check reversibility. A working
downthat 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. - 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. - 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-reviewskill). - 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-performanceskill), unless that query becomes unbounded on a table that grows with user input (then it is blocking underperformance-review); naming and style. Only blocking and should-fix justifyRECOMMEND CHANGES. - Record each finding with
record_ac_evidence(evidence_type: manual_note: statement, hazard, safe form), then let thereview-ticketverdict carry the result.
Checklist
- Indexes: PostgreSQL
CREATE INDEX CONCURRENTLY(not inside a transaction — the tool must disable its wrapper; a failed build leaves an INVALID index to drop); MySQL InnoDB online DDL (ALGORITHM=INPLACE, LOCK=NONE) or a tool such as gh-ost for large tables;DROP INDEX CONCURRENTLYlikewise. - Columns: adding a nullable column or one with a constant default is metadata-only
(PostgreSQL 11+); a volatile default (
now(),random()), a type change or a stored generated column rewrites the table; renames are expand/contract (add, dual-write, backfill, switch reads, drop later), never a singleRENAMEunder live code. - Constraints: foreign keys and CHECKs added
NOT VALIDthenVALIDATE CONSTRAINTin a separate step;SET NOT NULLon a big PostgreSQL table preceded by a validatedCHECK (col IS NOT NULL)(PostgreSQL 12+ then skips the full scan); a unique constraint built from a concurrently created unique index (USING INDEX). - Destructive changes: phased across deploys (stop writing → stop reading → drop); the ORM told to ignore a column before it is dropped.
- Backfills: batched by key range with a bounded batch size, idempotent, restartable, not in the DDL transaction, not in a request path.
- SQLite:
ALTER TABLEsupports only rename table, add column, rename column (3.25+) and drop column (3.35+); anything else is a rebuild (create new, copy, swap) in one transaction with foreign keys handled. - Hygiene: no application models or callbacks inside migrations (they drift); the schema snapshot or lock file regenerated and committed; migration ids ordered and unique.
Rules
- Simulate the live rollout; the migration runs on production data under load.
- Data loss, long locks on written tables, and deploy-order breaks always block.
- Do not ask for phasing or tooling the table size and traffic do not need — a new, empty table has no lock hazard.
- You review; you do not patch. Findings go into evidence, the verdict goes through
review-ticket.
