DEV Community: Bolvrk.com

Deterministic checks for AI-written migrations

Bolvrk.com — Thu, 17 Sep 2026 15:30:44 +0000

A coding agent asked to "add a status column to orders" will do it in seconds. It will also, more often than not, write the version that fails on a table with rows in it, or the version that takes an exclusive lock and holds it for the whole rewrite. Not because the model is bad at SQL, because the dangerous form and the safe form look almost identical, and the difference only matters at production scale, which the model never sees.

-- what the agent wrote
ALTER TABLE orders ADD COLUMN status text NOT NULL;

-- what survives contact with a table that has rows
ALTER TABLE orders ADD COLUMN status text;
UPDATE orders SET status = 'open' WHERE status IS NULL;  -- in batches
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

That first statement is BV002. It is one of the oldest and best-understood migration mistakes there is, and it is exactly the kind of thing an AI reviewer will sometimes catch and sometimes wave through, depending on the prompt, the context window, and the day.

Why the reviewer cannot be another model

The obvious fix is to ask a second model to review the first model's migration. It works often enough to feel like it works. It does not work in the way a team can build a process on, for three reasons.

It is not repeatable. The same migration reviewed twice can get two verdicts. A pull-request check that is red on Tuesday and green on Wednesday for the same diff teaches people to click through it.
It cannot say what it did not check. A model that returns "looks fine" gives you no list of the properties it verified. A rule corpus is a list by construction: these 104 patterns were checked, these fired, these did not.
Its claims are not verifiable. When a rule says "this rewrites the table under an ACCESS EXCLUSIVE lock", that claim was tested against a real Postgres and the fixture is in the repository. A model's claim is a sentence.

None of this is an argument against using agents to write migrations. It is an argument about where the trust boundary goes. The agent proposes; something deterministic disposes.

What a deterministic check looks like in an agent workflow

The check has to sit where the agent can reach it, and it has to return the same shape every time so the agent can act on it without interpretation. Three seperate placements cover most setups.

Inside the agent's loop. Bolvrk runs as an MCP server. An agent that has just drafted a migration calls the check tool, gets findings back as JSON with a rule id, a severity and the fix, and revises before the human ever sees the draft. The skills library in the public repository tells the agent when to do this unprompted.
In the terminal. npx bolvrk check migration.sql, no account, no config, exit code 1 on findings. This is the same rule set the MCP server runs, so a person double-checking an agent's work sees exactly what the agent saw.
On the pull request. The GitHub Action posts one summary comment and fails the job above the severity the team chose. Whoever, or whatever, opened the PR, the gate is the same.

The point of running the same corpus in all three places is that the verdict does not change as the migration moves from the agent's draft to the terminal to CI. The agent cannot talk its way past the Action, because the Action is not listening to arguments.

The rules that matter most for generated SQL

Looking at what agents actually produce, a handful of rules do most of the work.

BV002, ADD COLUMN … NOT NULL without a default.
BV003, an index created without CONCURRENTLY, blocking writes for the whole build.
BV034, a lock taken with no lock_timeout, so one slow transaction turns into a queue behind it.
BV030, a DROP COLUMN while code that reads the column is still deployed.

Every one of these has a page that says what the rule catches, the SQL it fires on, and the safe pattern. That is deliberate: when an agent explains a finding to a person, or a person to an agent, the explanation should come from the same place and say the same thing.

What this is not

A rule corpus does not know your buisness. It cannot tell you that the backfill will take four hours on your data, or that the column you are dropping is read by a report nobody has looked at since 2024. Live-schema context narrows that gap, a read-only connection lets the rules see the real table sizes and constraints, but judgement stays with the team. The corpus removes the category of mistakes that need no judgement at all, so the judgement can go where it is needed.

If you are letting agents write migrations, the question is not whether to review them. It is whether the review is something you can rely on the same way every time. Start with npx bolvrk check on the last migration an agent wrote for you.

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

Vibe coding without being reckless

Bolvrk.com — Thu, 17 Sep 2026 15:30:43 +0000

Vibe coding, describing what you want, accepting what the model writes, running it, and iterating on the result rather than the code, is a genuinely good way to build a lot of software. Most of a product is UI, glue and business logic that is cheap to be wrong about, because being wrong shows up immediately and costs a retry. The reckless part is not the vibe. It is applying the same loop to the small set of changes where being wrong shows up later, at scale, and is not undone by a retry.

So the question is not "should I vibe code" but "which parts of this change are safe to vibe". The answer turns out to be short.

The parts you can vibe

Anything with a fast, honest feedback loop. If a mistake makes a test fail, a page render wrong, or an API return the wrong shape, you will see it within seconds and the model will fix it on the next turn. Components, handlers, transformations, most refactors, most tests. Let it drive. Read the diff the way you would read a collegue's, not the way you would audit a contractor's.

The parts you cannot

Three kinds of change have no fast feedback loop, because the failure mode does not exist in a development environment.

Schema migrations. On a dev database with forty rows, every migration is instant and every migration works. ALTER TABLE orders ADD COLUMN status text NOT NULL (BV002) succeeds on an empty table and fails on a full one. An index built without CONCURRENTLY (BV003) takes a second locally and blocks writes for twenty minutes in production. The model has seen millions of migrations and almost none of them ran against a table with a hundred million rows, so it has no reason to prefer the safe form. Neither does your test suite.
Credentials. A model that needs a database URL to make a script work will happily inline the one it found in .env, and a model that is asked to "make the migration self-contained" will do the same. The commit passes every test. It also passes the connection string into git history, where it stays. Nothing in the vibe loop flags this, because nothing in the vibe loop is looking.
Destructive data operations. DROP COLUMN (BV030), DELETE without a WHERE, a backfill that rewrites every row. These are correct in the sense that they do exactly what was asked. They are only wrong in the sense that the previous deploy is still reading the column, or that the rows were not yours to delete. That context lives in the deploy window and the business, not in the code the model can see.

What these have in common is that the person or agent making the change cannot observe the consequence before committing to it. The loop is open. Something has to close it.

Close the loop with a rule, not with vigilance

The tempting answer is "just review migrations carefully". It does not survive contact with a team that ships forty pull requests a day, half of them opened by agents, at eleven at night. Vigilance is a budget; it runs out exactly when the migration count goes up, and it definately runs out first at night.

The answer that scales is a gate that does not get tired. For the three categories above, the dangerous forms are known and enumerable, locks, rewrites, missing defaults, missing timeouts, credentials in the diff, which means they can be checked deterministically: the same input, the same verdict, every time, with a rule id and a fix attached. That is what Bolvrk is. It sits at the three places a generated change passes through, and it does not matter which one catches it.

Before the draft leaves the agent, the MCP server gives the agent a check tool, and the skills library tells it to call the tool before proposing SQL. The agent revises its own migration; you never see the first draft.
Before you commit, npx bolvrk check migration.sql in the terminal, and bolvrk secrets on the diff or on any output the agent pasted. No account, exit code 1 on findings.
Before it merges, the GitHub Action fails the job above the severity your team chose and explains each finding on the pull request. This is the one that holds when the first two were skipped.

A working rule of thumb

Vibe everything whose mistakes you would see in the next sixty seconds. Gate everything whose mistakes you would see in the next sixty days. The gate should be a program, not a person, and it should give the same answer to the agent that it gives to you.

That is the whole discipline. It costs one command per migration and one Action in the repo, and it lets you keep the speed for the ninety percent of changes that deserve it.

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

One line stops a migration from taking down production. Almost nobody adds it.

Bolvrk.com — Thu, 17 Sep 2026 15:30:42 +0000

Here is how a migration that does no work at all takes a table offline.

ALTER TABLE orders ADD COLUMN note text;

A nullable column, no default. This is a catalog change; on its own it holds its lock for a few milliseconds. It does not matter how big the table is. What matters is who else is holding a lock on the table when it asks for its own, and that is something the migration author can not see and did not think about, because the statement is so obviously harmless.

The queue

ALTER TABLE needs ACCESS EXCLUSIVE, the lock that conflicts with every other lock including the ACCESS SHARE a plain SELECT takes. So if any transaction is holding any lock on the table, the ALTER waits. A long report query. A transaction some worker opened, ran one select in, and then went to make an HTTP call. An interactive psql session someone forgot about at lunch.

Now the important part. Postgres grants locks in order. Once the ALTER is in the queue, every new request that conflicts with ACCESS EXCLUSIVE queues behind it, and everything conflicts with ACCESS EXCLUSIVE. Every read. Every write. The table is not locked by the ALTER, which has not started yet. It is locked by the ALTER's request, on behalf of a transaction that is doing nothing.

We reproduced it on Postgres 16 with a 20 million row table. Session one opens a transaction, runs one cheap select, and idles. Session two runs the ALTER. Session three runs an ordinary point read.

  pid  | state  | wait_type | wait_event |                    query
-------+--------+-----------+------------+---------------------------------------------
 10542 | active | Timeout   | PgSleep    | BEGIN; SELECT count(*) FROM orders WHERE id < 10
 10549 | active | Lock      | relation   | ALTER TABLE orders ADD COLUMN note text;
 10556 | active | Lock      | relation   | SELECT count(*) FROM orders WHERE id = 5;

  pid  |        mode         | granted |                  query
-------+---------------------+---------+------------------------------------------
 10542 | AccessShareLock     | t       | BEGIN; SELECT count(*) FROM orders WHERE
 10549 | AccessExclusiveLock | f       | ALTER TABLE orders ADD COLUMN note text;
 10556 | AccessShareLock     | f       | SELECT count(*) FROM orders WHERE id = 5

The point read at the bottom wants the same AccessShareLock the idle session already holds, and those two do not conflict with each other at all. It is waiting anyway, because the ALTER is in front of it. In our run the idle transaction held on for thirty seconds, the ALTER finished after 28.1 s, and the point read, a query that takes under a millisecond, waited 26.1 s. Multiply by every connection in the pool and that is the outage: not a slow migration, a fast one, stuck behind something irrelevant, with the whole application stuck behind it.

The one line

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;

Same scenario, same idle transaction. The ALTER waited two seconds, gave up with canceling statement due to lock timeout, and left the queue. The point read that had been queued behind it ran immediatly: 0.1 s including the round trip. Nothing was locked, nothing was changed, and the migration can be retried in a loop until it gets a gap, which on most systems is the very next attempt.

Two details that matter. lock_timeout is not statement_timeout: the latter limits how long the statement may run, which for an index build or a backfill is the wrong thing to cap, while the former only limits how long it may wait for a lock, which is exactly the dangerous part. And set it per session or per migration, not globally; the application's own queries should not be cancelled for waiting on a lock, only the schema change should.

-- a retry loop the migration runner can own
DO $$
BEGIN
  FOR i IN 1..10 LOOP
    BEGIN
      SET LOCAL lock_timeout = '2s';
      ALTER TABLE orders ADD COLUMN note text;
      RETURN;
    EXCEPTION WHEN lock_not_available THEN
      PERFORM pg_sleep(1);
    END;
  END LOOP;
  RAISE 'could not take the lock after 10 attempts';
END $$;

Why almost nobody adds it

Because the statement without it works. i have added this line to more migrations than i can count and it has never once been the wrong call, but every time, in development, in staging, and in production nine times out of ten, the version without it works too. The tenth time is the one with the idle transaction, and by then the migration has been copy-pasted from the previous nine. The guard is not a fix for a bug in the SQL. It is an admission that the SQL will run in an environment the author can not see, which is true of every migration and is the whole reason BV034 exists: it fires on exclusive-lock DDL that has no lock_timeout in scope, so the line gets added before the tenth time, not after.

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

CREATE INDEX CONCURRENTLY is not free, and half the time you do not need it

Bolvrk.com — Thu, 17 Sep 2026 15:30:41 +0000

The standard advice is "always CREATE INDEX CONCURRENTLY in production". It is good advice and it is also a little lazy, because it skips over what the concurrent form costs and when you are paying that cost for nothing. So here are numbers.

The setup

Postgres 16, one table, twenty million rows, 1.6 GB on disk. While each index builds, a second session inserts one row every fifty milliseconds and records how long the slowest insert had to wait. Laptop hardware, so the absolute times are small; the ratios are what matter.

CREATE TABLE orders (
  id bigserial PRIMARY KEY, customer_id int NOT NULL,
  status text NOT NULL DEFAULT 'open', total numeric(12,2) NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now());
INSERT INTO orders (customer_id, total)
  SELECT (random()*1000000)::int, (random()*500)::numeric(12,2)
  FROM generate_series(1, 20000000);

The result

	Build time	Worst insert wait
`CREATE INDEX`	4.4 s	4216 ms
`CREATE INDEX CONCURRENTLY`	7.3 s	123 ms

Read the second column first. The plain build holds a SHARE lock on the table for its whole duration. Reads go through, every write waits, and the slowest insert waited almost exactly as long as the build took, because it arrived just after the lock was taken and sat there until it was released. On a table where a build takes four seconds that is four seconds of every checkout, signup and webhook handler hanging. On a table where it takes four minutes it is an incident.

The concurrent build never blocked a write for more than an eighth of a second. It took two thirds longer to finish, and that is the honest price: it scans the table twice, once to build the index and once to catch the rows that changed during the first scan, and it waits at the end for every transaction that was open when it started. On a busy server it can take several times longer than the plain form. It also can not run inside a transaction block, which is why migration frameworks make you opt out of their wrapper for it. BV003 fires on the plain form for the reason in the first row.

What wrong and right look like

-- wrong on a table that has writers: blocks every insert, update and delete for the whole build
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- right: two scans, no blocked writes; must run outside a transaction block
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

-- and if the migration runner wraps every file in a transaction, tell it not to for this one:
-- Rails: disable_ddl_transaction!   Django: atomic = False   Flyway: -- flyway:executeInTransaction=false

The concurrent build finishes, on a busy table, minutes later than the plain one would have. That is time the migration job spends waiting, not time the application spends blocked, and that is the trade you want.

The failure mode nobody mentions

If a concurrent build fails part way, because of a deadlock, a unique violation, or a cancelled statement, it leaves behind an invalid index. The index exists, it is maintained on every write, it costs the same as a real one, and the planner never uses it. Nothing tells you. The next migration that tries to create the same name fails with "already exists", and the usual response is to drop it and retry, which is right, but the invalid index can sit there for weeks first, slowing every insert on the table for no benefit.

SELECT indexrelid::regclass
  FROM pg_index WHERE NOT indisvalid;   -- should return nothing

Put that query in the runbook next to every concurrent build, and drop with DROP INDEX CONCURRENTLY when it finds something, for the same reason: a plain DROP INDEX takes the exclusive lock, BV016.

When the plain form is fine

A new table. Zero rows, no writers yet, nothing to block. The plain form is faster and it works inside the migration transaction. Creating the index in the same statement as the table is the cleanest version of this.
A tiny table. A few thousand rows builds in milliseconds either way; the concurrent form's second scan and end-of-build wait cost more than the lock does.
A maintenance window with writes actually stopped. If nobody can write, the lock is free, and the plain form is simpler and can not leave an invalid index behind.
Inside a rebuild. When you are populating a new table and swapping it in, build the indexes on the new table after the bulk copy and before the swap, plain. Nothing is reading it yet.

The decision is not about the statement. It is about whether anything is writing to the table while it runs. A checker can tell you the statement is the plain form on an existing table, and it should, every time, so that the times you choose it are choices rather then accidents.

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

Adding a foreign key to a big table without locking both of them

Bolvrk.com — Thu, 17 Sep 2026 15:30:05 +0000

Foreign keys are the constraint people add late. The table already has a few million rows in it, someone notices that order_items.order_id can point at nothing, and the fix is one line.

ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES orders (id);

This statement does two things. It records the constraint, which is instant, and it validates it, which means scanning every row of order_items and looking each order_id up in orders. While it does that it holds a SHARE ROW EXCLUSIVE lock on both tables. Reads continue. Every write to either table waits. On a busy orders table a validation that takes ninety seconds is ninety seconds where nobody can place an order.

Split the two things

Postgres lets you separate recording the constraint from validating it.

-- 1. record it; new and updated rows are checked from now on, existing rows are not yet
ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;

-- 2. validate the existing rows, under a lock that lets writes through
ALTER TABLE order_items VALIDATE CONSTRAINT order_items_order_id_fkey;

Step one still takes a brief lock on both tables, but it holds it for milliseconds because there is nothing to scan. Step two does the scan under SHARE UPDATE EXCLUSIVE, which blocks other schema changes and not much else: inserts, updates and deletes all proceed. The end state is identical to the one-line version. The only difference is who waited. BV008 is the rule for the one-line version.

If validation finds a violating row, it fails, and the constraint stays in place as NOT VALID. That is a feature: new writes are already protected, and you can fix the bad rows at leisure and validate again.

The index nobody adds

Postgres creates an index for a primary key and for a unique constraint. It does not create one for the referencing side of a foreign key. So after the statements above, orders.id is indexed and order_items.order_id is not, and every DELETE FROM orders or update to orders.id has to scan order_items to check that nothing still points at the row. On a large child table a single parent delete becomes a sequential scan, and a batch of them becomes a lock queue.

CREATE INDEX CONCURRENTLY order_items_order_id_idx
  ON order_items (order_id);

BV017 flags a foreign key whose referencing columns have no index. It is one of the few rules where the fix is a statement you add rather than a statement you change, and it is also one where the checker benefits from a live schema: with a read-only connection it can see whether the index already exists rather than guessing from the file.

The same trick, twice more

The NOT VALID then VALIDATE split is not special to foreign keys. A CHECK constraint takes it in exactly the same form. And a NOT NULL constraint, which has no NOT VALID form of its own, can borrow it: add CHECK (col IS NOT NULL) NOT VALID, validate that, and from Postgres 12 onward SET NOT NULL will see the validated check and skip its own full-table scan under ACCESS EXCLUSIVE. That scan is BV011, and it is the reason the three statements are worth the extra lines.

The principle underneath all three: any constraint whose validation needs a scan should be recorded first and validated seperately, so the scan runs under the weakest lock that is correct. Postgres gives you the syntax. It just does not make it the default.

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

Why SQLite refuses your ALTER TABLE, and the twelve-step rebuild it wants instead

Bolvrk.com — Thu, 17 Sep 2026 15:30:04 +0000

Coming from Postgres, SQLite's ALTER TABLE feels broken. It is not. It is small on purpose, and the rules for what it refuses are consistent once you know them. The trouble is that the refusals happen at run time, and a migration that fails at run time fails in production too.

What ALTER TABLE can do

Rename a table. Rename a column. Add a column, with restrictions. Drop a column, since 3.35, with more restrictions. That is the complete list. There is no ALTER COLUMN, no changing a type, no adding or dropping a constraint, no adding a primary key to an existing table.

The add-column restrictions

ADD COLUMN is fast in SQLite because it does not touch existing rows: the new column is appended to the schema and existing rows read as the default. That is exactly why the restrictions exist. Anything that would require a value per existing row that SQLite can not derive from a constant is refused.

NOT NULL needs a non-null default. Without one the statement fails with Cannot add a NOT NULL column with default value NULL. On every table, including an empty one, so unlike the Postgres version this fails in development too. That is SL001.
PRIMARY KEY and UNIQUE are refused outright. A uniqueness constraint would need to be checked across all rows, and existing rows would all have the same default. SL002.
A default must be a constant. DEFAULT CURRENT_TIMESTAMP is refused, because the stored-default trick only works for a value that is the same for every row.
A STORED generated column can not be added; only VIRTUAL.

The add-column fixes

-- wrong: fails at run time, everywhere
ALTER TABLE users ADD COLUMN tier TEXT NOT NULL;
-- right: a constant default satisfies every existing row without touching it
ALTER TABLE users ADD COLUMN tier TEXT NOT NULL DEFAULT 'free';

-- wrong: refused outright
ALTER TABLE users ADD COLUMN public_id TEXT UNIQUE;
-- right: add the column, then enforce uniqueness with an index
ALTER TABLE users ADD COLUMN public_id TEXT;
CREATE UNIQUE INDEX users_public_id ON users (public_id);

-- wrong: a non-constant default is refused
ALTER TABLE users ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP;
-- right: nullable now, populate on insert from the application, or rebuild the table

Why DROP COLUMN rewrites the file

SQLite stores each row as a record with the columns in order. Dropping a column means rewriting every record without it, so DROP COLUMN is a full table rewrite, holding the write lock for the duration. On a phone that is fine. On a server with a multi-gigabyte database file behind a web app it is an outage that looks like a one-line migration. SL006 flags it for that reason. And DROP COLUMN is refused entirely when the column is indexed, part of a constraint, or referenced by a view or trigger.

The twelve-step rebuild

For everything ALTER TABLE will not do, the SQLite documentation prescribes one procedure: create the table you wanted, copy the rows into it, drop the old one, rename. Written out properly it is twelve steps, and the order matters, because most of the steps exist to keep foreign keys, indexes, triggers and views from breaking half way through.

PRAGMA foreign_keys = OFF;          -- 1. outside the transaction
BEGIN;                              -- 2
-- 3. remember indexes, triggers and views on the table
CREATE TABLE users_new (...);       -- 4. the schema you actually want
INSERT INTO users_new SELECT ...    -- 5. copy the rows
  FROM users;
DROP TABLE users;                   -- 6
ALTER TABLE users_new RENAME TO users; -- 7
-- 8. recreate indexes and triggers
-- 9. recreate views
PRAGMA foreign_key_check;           -- 10. must return no rows
COMMIT;                             -- 11
PRAGMA foreign_keys = ON;           -- 12. outside the transaction

The steps people skip are the ones that hurt. Dropping the old table before copying loses the data, SL019. Leaving foreign keys enforced during the rebuild makes step six fail or, worse, cascade. Forgetting the foreign_key_check commits a database with dangling references. And a PRAGMA foreign_keys inside the transaction is silently a no-op, so steps one and twelve have to be outside it, which is the kind of detail that is easy to get wrong and impossible to see in a diff.

Checking it

The SQLite corpus is thirty-five rules and every "SQLite refuses this" claim in it is proven against a real SQLite in the test suite. It runs locally in the free CLI with --engine=sqlite, and it is static only for now: no live schema, no hosted checks. For a migration that is going to fail at run time in every enviroment, static is enough to save the deploy.

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

What 40 agent-written migrations got wrong

Bolvrk.com — Thu, 17 Sep 2026 15:30:03 +0000

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

Result	Files
Clean	13
Flagged, warning	25
Flagged, critical	2
Flagged, note only	1

Rule	Files	What it is
BV003	10	Index created without CONCURRENTLY
BV004	4	Column type change that rewrites the table
BV005	4	Rename or drop inside the deploy window
BV011	4	SET NOT NULL scanning the table under an exclusive lock
BV008	2	Foreign key added without NOT VALID
BV013	1	TRUNCATE in a migration
BV015	1	VACUUM FULL under a full lock
BV014	1	Unbounded UPDATE
BV016	1	DROP INDEX without CONCURRENTLY

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.

How a database password ends up in git history, and why rotating it is not enough

Bolvrk.com — Thu, 17 Sep 2026 15:29:28 +0000

Nobody sets out to commit a password. It happens because a migration file is, from the point of view of the person writing it, just SQL, and SQL sometimes needs to know things that are secret.

The four ways in

A settings row. The application reads its configuration from a table, the migration seeds the table, and one of the values is a connection string to another service. INSERT INTO settings VALUES ('warehouse_dsn', 'postgres://etl:Sup3rSecret@wh.internal/dw'). This is the one that spreads furthest, because every service that loads settings now has the credential too.
A role. The migration creates a database user for a new service and gives it a password, in plaintext, in the file. CREATE ROLE reporting LOGIN PASSWORD 'hunter2'. Postgres would have accepted a SCRAM verifier in the same position.
A vendor key. An API key seeded into a table, or set as a column default, so the app "just works" after deploy. Stripe, AWS, GitHub and Slack all give their keys a recognisable prefix precisely so that this can be caught.
A helpful agent. Ask a coding agent to make a script self-contained, or to make a migration runnable without setup, and it will do the obvious thing: read the value out of .env and write it into the file. It is not being careless. It is doing exactly what was asked, and the request did not say "except the secrets" because nobody thought it needed to.

What happens next

The commit lands. The pull request diff shows the value to everyone with read access to the repository, and to every integration that reads pull requests. CI prints the file, so the value is in build logs. If log_statement covers DDL, the database server writes the statement to its own log. And git history is permanent: reverting the commit adds a second commit and leaves the first one exactly where it was, readable by anyone who clones the repository, for as long as the repository exists.

This is why rotating the credential is necessary but not sufficient. Rotation closes the door for the value that leaked. It does nothing about the fact that the file, the process and the reviewer all let a plaintext secret through, and the same path is open for the next one. Rewriting history to remove the value is possible and painful, and every fork and every developer's local clone still has it.

How to do each one right

-- wrong: a live credential in a committed file
INSERT INTO settings (key, value)
  VALUES ('warehouse_dsn', 'postgres://etl:Sup3rSecret@wh.internal/dw');
-- right: seed the key with a placeholder; the value comes from the environment at runtime
INSERT INTO settings (key, value) VALUES ('warehouse_dsn', NULL);

-- wrong: the password is now in git history and in the server log
CREATE ROLE reporting LOGIN PASSWORD 'hunter2';
-- right: commit the SCRAM verifier, which is useless without the password, or set the password
-- out of band after the role exists
CREATE ROLE reporting LOGIN PASSWORD 'SCRAM-SHA-256$4096:...$...:...';

-- wrong: a vendor key as a default
ALTER TABLE integrations ALTER COLUMN api_key SET DEFAULT 'sk_live_51H...';
-- right: no default; the application injects the key from its secret store per environment
ALTER TABLE integrations ALTER COLUMN api_key DROP DEFAULT;

The rule is the same in all three cases: a migration may create the place a secret lives, and it must never contain the secret. The value belongs in the environment, the secret manager, or a one-off command a person runs, none of which get committed.

Why the reviewer does not catch it

Because it looks like data. A DSN in an INSERT is a string like any other string in the file, and a reviewer reading a 200-line migration for logic errors is not pattern-matching on sk_live_. Secret scanning at the platform level helps, but it runs after the push, which is after the leak. The check has to happen on the diff, before the commit, and it has to be a program, because a person's attention is the thing that was already exhausted.

What the rules look for

BC003 fires when a string literal parses as a libpq connection string with a real password in it, and stays quiet on placeholders and empty passwords. BC004 matches the published key formats, not entropy, so a uuid or a bcrypt hash does not trip it. BC001 catches CREATE ROLE ... PASSWORD with a plaintext value and is silent on a SCRAM verifier. All three run in npx bolvrk check on migration files, and the first two also run on any file or on piped output with bolvrk secrets, which is the one to put in front of an agent's output before it is commited:

git diff --cached | npx bolvrk secrets -

The point of scanning the diff rather than the repository is that the diff is the moment the secret is still only on one machine. Everything after that is cleanup.

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

Expand, migrate, contract: dropping a column without breaking the deploy that is still running

Bolvrk.com — Thu, 17 Sep 2026 15:29:27 +0000

Here is a migration that is completely correct and takes production down anyway.

ALTER TABLE users DROP COLUMN legacy_ref;

The column is unused. The code that read it was deleted in the same pull request. The tests pass. And for the thirty to ninety seconds between the migration running and the last old pod being replaced, the previous version of the application is still running, still has legacy_ref in its model, and every query it makes that mentions the column fails with column "legacy_ref" does not exist. If the ORM selects every column by name, which most do, that is every query on the table.

The migration was not wrong. The timing was. The schema moved before the code did, and there was a window where they disagreed.

The deploy window

Every rolling deploy has one. Migrations usually run first, as a job before the new version starts, and the old version keeps serving until the rollout completes. During that window the database is at version N+1 and some of the application is still at version N. Anything the old code needs that the new schema removed is a live error for as long as the window lasts, and rollbacks make it worse: rolling back the code without rolling back the schema puts the whole fleet on version N against a schema it does not understand.

Adding things is safe across the window, because old code ignores columns it does not know about. Removing or renaming things is not, because old code can not ignore a column it expects. That asymmetry is the whole pattern.

Expand, migrate, contract

The fix is to never remove anything in the same release that stops using it. Three phases, at least two releases.

Expand. Add the new column, table or constraint. Old code ignores it, new code can start writing to it. If it replaces something, write to both for now.
Migrate. Backfill the new thing from the old, in batches. Switch reads to the new thing once the backfill is complete. Ship this and let it soak: every pod is now on code that no longer needs the old column.
Contract. In a later release, drop the old column. Nothing running reads it anymore, so the window is harmless.

A rename is the same problem wearing a different hat. RENAME COLUMN name TO full_name removes name from the point of view of old code, instantly. The safe version is add full_name, copy, switch, drop name later. It takes two releases instead of one statement, and it is the only version that does not depend on the deploy being fast.

The same change, both ways

-- wrong: one release, and old pods fail on every query that names the column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- right, release 1 (expand): add the new column; nothing reads it yet
ALTER TABLE users ADD COLUMN full_name text;

-- release 1 (migrate): backfill in batches, then deploy code that writes both and reads full_name
UPDATE users SET full_name = name WHERE full_name IS NULL AND id BETWEEN 1 AND 50000;

-- release 2 (contract): only once no running version reads name
ALTER TABLE users DROP COLUMN name;

The drop in the first paragraph of this post follows the same shape: it is a contract step, and the question is only whether the migrate step, the deploy that stopped reading legacy_ref, has fully rolled out. If it has, the drop is safe. If you are not sure, it is not.

What "later" means

Later means after you are sure no version of the code that references the old column can be running or rolled back to. For most teams that is the next release, or the one after. The contract step is also the one that gets forgotten, wich is how tables grow a graveyard of columns named legacy_ something. i put it in the same ticket as the expand step, with a date on it.

What a checker can and cannot know here

BV030 fires on a DROP COLUMN and BV005 on a rename or drop, and both are warnings rather than criticals for an honest reason: the statement itself is fine. What makes it dangerous is whether code that still reads the column can be running when it executes, and that is a property of your deploy, not of the SQL. A deterministic rule can tell you "this is a contract step, make sure the migrate step already shipped". It can not tell you whether it did. That is the review question the rule is there to force, and it is a much better use of a reviewer's attention than checking syntax.

Where live-schema context helps is the other direction: with a read-only connection the checker can see that the column you are about to drop is still the target of an index, a view or a foreign key, which is the class of contract-step mistake that shows up as an error rather than a window.

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

Why ADD COLUMN NOT NULL fails on a full table, and the migration that does not

Bolvrk.com — Thu, 17 Sep 2026 15:29:26 +0000

This one works on your laptop and fails in production, which is the worst kind of migration there is.

ALTER TABLE orders ADD COLUMN status text NOT NULL;
-- ERROR:  column "status" of relation "orders" contains null values

On a dev database the table is empty, so there are no rows to violate the constraint and the statement succeeds. On the real table every existing row would need a value for the new column, none is given, and Postgres refuses. The migration stops half way through the deploy, the framework marks it as failed, and someone gets paged to work out whether the earlier statements in the same file were applied or rolled back.

What the server is actually doing

ADD COLUMN takes an ACCESS EXCLUSIVE lock on the table. That is the strongest lock Postgres has: it blocks every read and every write until the statement finishes. For a plain nullable column that is fine, because adding a nullable column with no default is a catalog change. No row is touched, the lock is held for a few milliseconds, and nobody notices.

NOT NULL changes that. A NOT NULL column has to hold a value in every row, so Postgres has to know what the value is. With a default it can use the default. Without one there is nothing to use, and the only honest answer is the error above. The check is not a scan in the usual sense, it is Postgres declining to invent data.

What changed in Postgres 11

Before Postgres 11, adding a column with a default rewrote the whole table to write the default into every row, under that same exclusive lock. On a large table that was minutes of downtime, so the folk wisdom became "never add a column with a default". Postgres 11 fixed it: a constant default is now stored in the catalog and returned on read for rows that predate the column, so ADD COLUMN status text NOT NULL DEFAULT 'open' is instant on any table size.

The word that matters is constant. DEFAULT now() or DEFAULT gen_random_uuid() is volatile, every row needs its own value, and Postgres still rewrites the table. Same syntax, completely different cost, and the only way to tell them apart is to know the rule.

The migration that works

When there is a sensible constant default, use it and you are done, on any modern Postgres. When there is not, which is the normal case for a column that must be backfilled from other data, do it in three steps.

-- 1. add the column nullable; a catalog change, milliseconds
ALTER TABLE orders ADD COLUMN status text;

-- 2. backfill in batches, outside the migration transaction
UPDATE orders SET status = 'open'
 WHERE status IS NULL AND id BETWEEN 1 AND 50000;
-- ... repeat by id range until no rows remain

-- 3. add the constraint without a full-table scan under lock
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

Step three looks like ceremony, and it is there for a reason. A bare SET NOT NULL scans the whole table to prove no null exists, and it does that scan while holding ACCESS EXCLUSIVE. On a big table that is the outage everyone was trying to avoid in step one. A CHECK ... NOT VALID constraint is added instantly, VALIDATE CONSTRAINT does its scan under a much weaker lock that lets reads and writes continue, and from Postgres 12 onward SET NOT NULL notices the validated check constraint and skips its own scan. Then the helper constraint can go.

The two rules that catch it

The first statement in this post is BV002, and the bare SET NOT NULL is BV011. Both are in the free CLI, which is worth knowing because both are exactly the kind of mistake that looks fine in review. The SQL is syntactically perfect. It is the table it will run against that makes it wrong, and a reviewer can not see the table from the diff.

If you use SQLite instead, the rule is stricter and the failure is earlier: SQLite refuses a NOT NULL column without a non-null default outright, in every enviroment including the empty one. That is SL001.

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