A Postgres Migration Safety Checker Agents Cannot Hand-Wave Past

Database migrations are one of the few places where a short pull request can create a long outage. A non-concurrent index on a busy table, a rewrite ALTER … TYPE, a volatile column default, or an unbounded backfill disguised as a deploy migration will not announce itself politely in staging if staging does not look like production.

We already had engineering rules for this: expand / migrate / contract sequencing, prefer nullable columns over rewrite DDL, run heavy backfills as restartable commands rather than deploy-time SQL. Rules in a document are not enough when humans are tired and AI agents can generate plausible Doctrine migrations in seconds.

So we built a deterministic migration safety checker and made it part of the merge path.

The problem with “we’ll be careful”

Careful review assumes the reviewer sees the hazard. Migration hazards are easy to miss:

• The dangerous SQL might live in a private helper method, not in the obvious up() body.
• A type clause can hide a default behind punctuation the reviewer’s eyes skip.
• A WHERE clause that looks bounding may not actually limit the update.
• A rename or drop may be intentional and still deserve an explicit human acknowledgement.

Agents make the gap worse. They are excellent at producing migrations that look locally consistent with an entity change and terrible at inferring lock behavior on a live PostgreSQL 15 database. Asking an agent to “follow the migration docs” without a machine check is asking it to simulate a DBA.

What we built

The checker is a first-class application module, not a one-off CI script. It analyzes Doctrine migration classes, discovers SQL through the PHP syntax tree, parses that SQL with a real PostgreSQL grammar, and classifies findings into errors and warnings.

Errors always fail continuous integration. These are the operations we treat as unsafe to ship as ordinary deploy migrations: table-rewrite patterns, non-concurrent index creation on existing tables, volatile defaults that rewrite rows, unbounded data changes, and similar lock or rewrite hazards.

Warnings are for operations that may be intentional but must not be silent: renames, drops, truncates, expand-and-contract packed into one migration, and other size-sensitive patterns. On a pull request, warnings fail until a different human reviewer acknowledges them. The author (or a bot) cannot self-approve the warning path. That distinction matters. Some migrations should ship after judgment. None of them should ship after a shrug.

The checker also posts a sticky migration report on the pull request, including the useful negative result: no migrations in the diff. Absence of news is still signal when reviewers are scanning for database risk.

Closing the escape hatches

The first version found the obvious cases. Review findings closed the paths that would have let unsafe SQL hide:

• Discover SQL from the PHP AST instead of brittle token scanning, so valid PHP forms cannot hide statements.
• Scan helpers reachable from up(), not only the migration class’s top-level calls.
• Treat tautological predicates as unbounded data changes.
• Keep schema-qualified names distinct so two tables do not collapse into one finding or one miss.
• Ignore comments and string literals when classifying structure.
• Fail when a migration file disappears from a branch in a way that would look like “no migration change.”

Each of those sounds pedantic until you remember the failure mode: a green check on a migration that never actually got analyzed.

Local parity with CI

A gate that only exists in GitHub Actions trains people to push and pray. We wired the same checker into local Make targets and the file-scoped quality command agents already use. Branch mode is strict, matching CI. Explicit-file mode can print warnings while you iterate, then you switch to branch mode before review.

Agent documentation was updated in the same change set. The preferred workflow is not “remember fourteen migration rules.” It is: load the migrations pattern, load the migration-safety checklist, run the checker, read the suggestions. When the quality gate fails, the failure text points back at the tool that explains the rule.

What we are not claiming

This checker does not prove a migration is fast on production data. It does not replace expand / migrate / contract judgment. It does not measure table size or invent a risk score from telemetry. It answers a narrower, more reliable question: does this SQL match patterns we have already decided are dangerous or acknowledgement-worthy?

That narrower question is the one continuous integration can enforce every time.

Verification

We treated the checker like production software: unit coverage on the analyzer and rules, infection-style pressure on the changed logic, and intentional probe pull requests that introduced unsafe SQL, warning-only SQL, and safe create-table SQL to confirm CI failed or passed the way we expected before reverting the probes.

The operational test is simple. If an agent or a tired human can merge a rewrite migration without the checker complaining, the system is wrong. If the complaint is noisy but accurate, the system is doing its job.

Lessons

1. Write migration policy as code. Documents educate. Gates enforce.
2. Parse the database dialect you deploy. Regex approximations will miss the migration that hurts you.
3. Separate “never” from “not silently.” Errors and acknowledged warnings are different social objects.
4. Assume SQL will hide. Helpers, formatting, and comments are part of the attack surface on your own process.
5. Give agents the same local command CI runs. Otherwise they optimize for a green push, not a safe merge.

If your team is letting coding agents touch schema, a migration safety checker is not optional polish. It is how you keep “move fast” from meaning “lock the table.”