What 40 agent-written migrations got wrong

A small experiment. We gave a current coding agent the role of "helpful assistant on a Postgres web app" and asked it, in forty separate requests, for the migrations a team writes in a normal quarter: add a column, add an index, add a foreign key, change a type, rename, drop, backfill, truncate, vacuum. No hints about safety, no linter in the loop, first draft only. Then we ran the eighteen rules the free CLI bundles over the forty files.

The numbers

What it got right

This is the part worth reading before the part about what it got wrong. Every time the agent was asked for a required column, it added a default: ADD COLUMN status text NOT NULL DEFAULT
'open', ADD COLUMN email_verified boolean NOT NULL DEFAULT false. That is the correct modern form, it is instant on Postgres 11 and later, and BV002 never fired once. The single most famous migration mistake did not appear. Models have read a lot of blog posts about that one.

It also wrote a correct USING clause on every type change, used string_to_array sensibly for the tags split, and wrote a clean CREATE TABLE with a foreign key when asked for a new table. The SQL was never malformed. Not one of the forty files had a syntax error.

What it got wrong, and why it is the same mistake ten times

Ten of the forty files create an index, and all ten do it the plain way. Not one CONCURRENTLY. On a dev database the plain form is faster and simpler, and the agent has no reason to prefer the form that only matters when the table has fifty million rows and writes going to it. This is the shape of most of the flagged files: the agent picks the form that is locally best, and the form that is locally best is the one that blocks production.

The four SET NOT NULL statements are the same story. The agent even did the right thing first, backfilling with an UPDATE before setting the constraint, and then set the constraint the way that scans the whole table under an exclusive lock, because that is the short way to write it. The two foreign keys were added without NOT VALID, so they validate every existing row while holding locks on both tables. The pattern is consistent enough that you could predict which files would be flagged from the request alone.

The two criticals were a TRUNCATE and a VACUUM FULL, both of which were literally what was asked for. That is a fair point in the agent's defence and it is also the point: the request was reckless, the agent did not push back, and the only thing standing between the request and production was whether anyone was checking.

The fixes, side by side

Every flagged file has a one or two line correction. The four that account for twenty of the twenty-seven:

-- BV003: the agent wrote            ->  the fix
CREATE INDEX idx ON orders (customer_id);
CREATE INDEX CONCURRENTLY idx ON orders (customer_id);

-- BV011: backfilled, then
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- fix: prove it under a weak lock first, then the SET NOT NULL is a catalog change (PG12+)
ALTER TABLE users ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_nn;

-- BV004: a type change rewrites the table under ACCESS EXCLUSIVE
ALTER TABLE orders ALTER COLUMN total TYPE bigint;
-- fix: new column, batched copy, swap, drop (expand / migrate / contract)
ALTER TABLE orders ADD COLUMN total_new bigint;
UPDATE orders SET total_new = total WHERE total_new IS NULL AND id BETWEEN 1 AND 50000;  -- repeat
-- ... switch the application, then rename and drop in a later release

-- BV008: validates every row while locking both tables
ALTER TABLE payments ADD CONSTRAINT payments_order_fk FOREIGN KEY (order_id) REFERENCES orders (id);
-- fix: record now, validate under a lock that lets writes through
ALTER TABLE payments ADD CONSTRAINT payments_order_fk FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
ALTER TABLE payments VALIDATE CONSTRAINT payments_order_fk;

The honest caveats

• One agent, one run, forty prompts i wrote. A different model or a different day would move the numbers. We do not think it would change the shape.
• The free rule set is the outage core, eighteen rules. The full corpus would have flagged more, and some warnings here are advisory by design: a rename is only dangerous during a deploy window, and the rule says so.
• We asked for first drafts. An agent that is told to review its own migration, or that has a checker as a tool, does better. That is the whole argument for giving it one.

Thirteen of forty clean is not a verdict on the agent. It is a measurment of what "looks right" means when the thing that makes a migration wrong is a table the author will never see. The fix is not a better prompt. It is the same check, run the same way, on every file, before it merges.

Originally published at bolvrk.com. Bolvrk is a deterministic checker for database migrations and committed credentials: free CLI, GitHub App and Action.