You've seen the headlines by now. An agent in Cursor wiped a company's production database, backups and all, in about nine seconds. Replit's agent nuked another company's prod. Same shape every time: the agent was sure of itself, the SQL was valid, and nobody was in the loop to say wait.
Those are the loud failures. The fix for them is boring and you already know it. Don't hand an agent write access to prod. Read-only by default, propose instead of apply, keep a human on the button.
But there's a quieter version that a permissions policy won't catch, and that's the one I want to talk about. Your agent is probably doing it right now. It looks completely fine in the diff.
Ask an agent to make an email column unique. It writes:
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
Correct SQL. Does exactly what you asked. It sails through review because there's nothing to see. Then on a users
table with any real size, it grabs an ACCESS EXCLUSIVE
lock and scans every row to build the unique index, and for the whole length of that scan nothing else can read or write the table. The API starts timing out. The connection pool fills. Now you're in an incident over a one-line migration that everybody approved.
The agent didn't do anything a decent junior engineer wouldn't have done. That's the trap. The danger isn't the SQL, it's the lock the SQL takes, and you can't see a lock by reading a statement. You'd have to know Postgres locking cold: which DDL grabs which lock, and for how long, and what it shuts out while it holds. And you'll still miss one at 2am.
I got tired of missing them. So I measured one.
I ran the same schema change two ways against a real Postgres 18. Fifty million rows, twenty connections doing ordinary traffic. The unsafe version was a plain SET NOT NULL
, which also scans under ACCESS EXCLUSIVE
. The safe version was the NOT VALID
then VALIDATE
dance that moves the scan under a gentler lock.
The unsafe path held its exclusive lock for 2.2 seconds with all twenty connections stacked up in the lock queue behind it. p99 for the whole workload hit 2,028 ms. The safe path: 3 milliseconds of exclusive lock, nothing queued, p99 of 0.57 ms. Same change, same end state in the schema. Something like a 3,500x difference in what it did to everyone else on the way there.
The traces and the repro script are in the repo if you want to run it yourself. Don't fixate on the exact numbers, they move with your hardware. The thing to take away is that "correct SQL" and "safe migration" are two different claims, and the gap between them is where outages live.
If the lock is the thing you can't see by reading, you want something that can read locks sitting between the agent and the DDL.
So that's what I built. MigrationPilot parses a migration with libpg-query, the actual Postgres parser (the same C library Postgres itself uses, not a wall of regexes), works out the lock every statement takes, and runs it against 112 rules for the known footguns. There's a CLI, a GitHub Action, and for this problem in particular, an MCP server.
The tool that matters here is check_before_apply
. It's a pass/fail gate the agent calls before it writes or runs any DDL, and it gives the same verdict your CI would, because it reads the same config. In Claude Code a PreToolUse
hook wires it up and blocks the call when it fails, so the agent literally can't write the bad migration to a file or hand it to a runner. It fails open on purpose. If the check can't run for some reason the call goes through with a note, because a guardrail that jams your workflow every time it hiccups is one you'll rip out by Friday.
Here's what comes back on that ADD CONSTRAINT
from earlier:
✗ [MP027] CRITICAL
Adding a UNIQUE constraint scans the whole table under ACCESS EXCLUSIVE.
Create the index concurrently first, then attach it:
CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique_idx;
Not "this looks risky." It names the lock, says why it hurts, and hands back SQL that does the same job without the outage. The agent takes that and tries again, and this time its own suggestion passes the check. Which it didn't, at first. I had to fix the tool so its own advice stopped tripping its own rules. A little embarrassing.
It's static analysis. With no database connection it knows the lock a statement takes but not how big your table is or how busy it gets, so it'll say "this holds an exclusive lock," not "this takes 40 minutes." Give it --database-url
and it reads your table sizes and query stats to sharpen the call. That's read-only against the catalog, and it's free. Nothing here sits behind a paywall. It only sees the SQL you show it, so if your framework renders templates or runs DDL from app code, point it at the rendered output. And the bundled parser speaks Postgres 17 grammar for now, so a couple of brand-new PG18-only syntax forms don't parse yet.
There's a benchmark in the repo too. Fifty-six labelled migrations scored against Squawk and pgfence, corpus and exact commands included, and it lists the hazards that nothing catches, mine included. I'd rather show you where it's thin than pretend it's airtight.
npx migrationpilot analyze migration.sql
No install, no account, exits non-zero on a critical finding so it slots straight into CI. Wiring up an agent? The MCP server is npx migrationpilot-mcp
.
Agents are going to keep writing migrations, and honestly that's fine. They're good at the SQL part. They're just bad at knowing which statement locks the table. So are most of us. Give them something that isn't.
Repo and docs: https://github.com/mickelsamuel/migrationpilot