AI Code Review for Database Migrations: The Checklist Before a Schema Change Ships

Most pull requests can be wrong without hurting anyone. A migration cannot. A schema change ships to production exactly once, runs against data the author never saw, and the rollback is frequently a second migration that nobody has written yet. That makes migrations the highest-leverage place to point an AI code reviewer, and also the place where a diff-only reviewer is most likely to nod a disaster through.

This post is a checklist for what an AI code reviewer should actually verify on a migration PR, written so you can turn it into review instructions for whichever tool you run. Where PURA does something specific, we say so; most of it applies to any reviewer, human or model.

Why migrations defeat the default review

A typical migration diff is tiny: ten lines of DDL, or a generated file from an ORM. The risk is almost entirely outside those lines. Whether ALTER TABLE ... ADD COLUMN ... NOT NULL is fine or a multi-minute table rewrite depends on the row count, the database engine and the version. Whether dropping a column is safe depends on which application code still reads it, which is in a different directory and possibly a different repository. Whether an index creation locks writes depends on a keyword the generated SQL may have left out.

A reviewer that reads only the diff has no way to answer any of those questions, so it tends to produce the worst kind of review: a confident “looks good”. We wrote about this failure mode in general in why diff-only reviewers miss real bugs. Migrations are the sharpest version of it.

The checklist

1. Is the change expand-first?

The safe pattern for almost every schema change is expand, migrate, contract: add the new column or table, deploy code that writes both, backfill, switch reads, and only then drop the old thing in a later PR. A reviewer should flag any single PR that both adds and removes, or that renames a column in place, because a rename is a drop plus an add with no window for the old code to keep working during a rolling deploy.

2. Does any live code still depend on what is being removed?

For a DROP COLUMN, DROP TABLE or a type change, the reviewer needs to search the repository for readers and writers of that column: ORM models, raw SQL strings, serializers, reporting queries, and the cheeky analytics job in a folder nobody owns. This is the single highest-value check and the one that requires repository-wide context rather than the diff. In PURA the review agent has the whole repository available, so a review instruction such as “for any dropped or retyped column, list every reference to it outside the migration” is a one-line rule.

3. Will it lock, and for how long?

The engine-specific questions a reviewer should ask explicitly: on PostgreSQL, is every index created CONCURRENTLY, is a new constraint added as NOT VALID and validated separately, and does a new NOT NULL column carry a constant default that modern versions can apply without a rewrite? On MySQL, is the operation one the engine can perform online, and has the author said so? The reviewer cannot know your table sizes, but it can insist that the PR description states the expected size of the affected tables and that the migration is written for the large case.

4. Is the backfill separated from the schema change?

A migration that alters the schema and then runs UPDATE across every row in the same transaction is the classic way to hold a lock through a deploy window. The reviewer should flag data backfills inside schema migrations and ask for batched, resumable backfills that run outside the migration runner.

5. Does the down migration actually reverse the up?

Generated down migrations are often wrong in quiet ways: they drop a table the up migration only altered, or they recreate a column without its default or index. A reviewer should read the down migration as carefully as the up, and should also say plainly when a change is irreversible, so the team decides that on purpose rather than discovering it during an incident.

6. Is the ordering safe across services?

If two services share the database, or a consumer reads a replica, the migration may be correct for this repository and still break a neighbour. The reviewer should check whether the PR names the other consumers and the deploy order. It cannot verify the other repositories from inside this one, which is a limitation worth stating in the review rather than glossing over.

7. Are the mechanical details right?

• Migration timestamp or version does not collide with one merged since the branch.
• Column types match the ORM model and the application's expectations (nullable, precision, timezone awareness).
• Foreign keys reference existing columns with matching types and indexes.
• Default values are expressed in SQL, not evaluated once at migration time.
• The migration file is idempotent or guarded, if your runner can re-execute on retry.

Turning this into review instructions

Checklists in a wiki are not read at 17:55 on a Friday. The point of an AI reviewer is to apply them on every PR without anyone remembering. Three configuration decisions make that work.

Route migrations to a stronger model. Migration PRs are rare and high-stakes, which is the ideal profile for spending more per review. In PURA this is a path-based rule in the agent's review skill: anything under db/migrations or matching *.sql gets the premium model and the full checklist, while the rest of the PR is reviewed as usual. Our guide to budgeting by provider and model covers the mechanics.

Give the reviewer the context it needs, in writing. Put the engine and version, the expand-and-contract policy, and the names of the large tables into the project review instructions. A reviewer that knows you run PostgreSQL 16 and that events has two billion rows can say “this will rewrite events” instead of “consider the impact on large tables”. How to encode team conventions is covered in custom rules that actually work.

Decide what blocks. Most migration findings should be advisory; a few should not. A dropped column with live references, a non-concurrent index on a named large table, or a backfill inside a schema migration are reasonable candidates for a blocking finding, with a logged override. The trade-offs are in gating vs advisory review.

What the reviewer still cannot do

Honesty about limits matters more here than anywhere else. An AI reviewer cannot see production row counts, replication lag, or the other repositories that share your database. It cannot run the migration against a copy of production, which remains the single most reliable test. And it will occasionally be confidently wrong about an engine-specific locking rule, because those rules change between versions. Treat its review as a structured second pair of eyes that never forgets the checklist, not as the gate that replaces a staging run.

What it does change is the baseline. Every migration PR gets the same seven questions asked, in full, with the repository open, before a human spends their attention on it. For the one change type where a missed question costs an outage, that is worth more than any other place you could point a reviewer.