The Migration That Crash-Looped Prod: Why Every Migration Must Be Idempotent

On June 1st, our control plane — api.moleculesai.app, the service that orchestrates every tenant agent in the Molecule fleet — went into a crash loop. It would boot, run its database migrations, hit an error, and exit. The control plane supervisor would restart it. It would boot, run its migrations, hit the same error, and exit again. The API never came up long enough to serve a request.

The change that triggered it was almost insultingly small: a single new row in a price catalog so we could offer a new model. The row was correct. The SQL was valid. It had passed CI. And it still took production down — not because of what it did, but because of an assumption baked into how our migration runner replays history.

This is a write-up of that incident: what the runner actually does, why a perfectly good INSERT became a fatal one, the one-line fix, and the systemic guard we added so the whole class of bug can’t recur.

How the migration runner actually behaves

Most engineers carry a mental model of migrations as a forward-only ledger: there’s a schema_migrations table, the runner checks the highest applied version, and it runs only what’s newer. Apply once, never again.

Our control-plane runner does not work that way. On every boot it walks the full *.up.sql sequence from the beginning and re-applies all of it. There’s no “skip already-applied” gate. This isn’t an accident nobody noticed — it’s a known property of the runner, and it has an upside: a migration file is the single source of truth for the state it asserts, and a fresh database and a long-lived one converge to the same place by replaying the same statements.

But it carries a hard contract, and the contract is the whole point of this post:

If the runner re-applies every migration on every boot, then every migration statement must be safe to run more than once. Idempotency isn’t a nice-to-have. It’s a precondition for the process to start at all.

A schema change like CREATE TABLE IF NOT EXISTS or ALTER TABLE ... ADD COLUMN IF NOT EXISTS satisfies that contract naturally — the IF NOT EXISTS is doing the idempotency work. The trap is data seeding. A bare INSERT is idempotent exactly once. Run it a second time against a table with a unique constraint and it doesn’t no-op — it raises a duplicate-key error and aborts the transaction.

The trigger

The migration in question, 045_minimax_m3_price_catalog.up.sql, seeded a price-catalog row for a newly offered model (shipped in cp#428). The llm_price_catalog table has a unique key on (provider, model, effective_from). The seed was a plain INSERT.

Trace what happens:

1. The migration ships. The control plane redeploys.
2. First boot after deploy: the runner replays the sequence, reaches 045, the row doesn’t exist yet, the INSERT succeeds. The catalog now has the row.
3. Next boot — any restart, deploy, scale event, or health-check bounce: the runner replays the sequence again, reaches 045, and the row already exists. The INSERT violates the unique constraint. The migration transaction fails. The runner returns an error. The process exits non-zero.
4. The supervisor restarts the process. It boots, replays migrations, hits 045, dup-keys, exits. Go to 3.

That’s the crash loop. The migration “worked” exactly once and then became a permanent boot-blocker. There was nothing wrong with the data — the bug was the mismatch between a re-applying runner and a write-once statement.

This is also a good reminder that CI green is not prod green for this class of change. The test database is fresh: 045 runs against an empty table, succeeds, and the suite passes. The failure mode only exists on the second application, which a from-scratch CI run never performs. The contract was invisible to the one environment we trusted most.

The hotfix

The immediate fix was a one-line change to the statement, shipped as cp#430: make the seed an upsert instead of a blind insert.

INSERT ... ON CONFLICT (provider, model, effective_from) DO NOTHING

Now the semantics match the runner. On a fresh database the row gets created. On every subsequent boot the conflict clause turns the re-application into a no-op instead of an error. The migration asserts “this row should exist” rather than “create this row, and fail loudly if it’s already there.” Same outcome on a clean DB, safe outcome on a dirty one.

A note on why DO NOTHING and not DO UPDATE here. For a price-catalog row keyed on effective_from, the existing row is the truth of record for that effective date — we don’t want a redeploy to silently rewrite a price that’s already in effect. DO NOTHING preserves operator and historical state across replays. That distinction matters, and we’d learned it the hard way once before.

We had seen this exact shape before

This wasn’t the first time the re-applying runner bit us. Months earlier, cp#261 fixed migration 028, which seeded runtime image pins with a bare INSERT. On every control-plane startup, 028 re-ran and overwrote newer pin promotions — including a fresh runtime digest an operator had just promoted. The fix was the same family: ON CONFLICT DO NOTHING, so fresh databases still get the bootstrap rows but operator promotions survive deploys.

The 028 case didn’t crash-loop — the table had no constraint that turned the re-run into an error — so instead of a loud failure it was a quiet data-clobber. The 045 case had the constraint, so the same root cause surfaced as a crash. Same bug, two different symptoms, depending entirely on whether a unique key happened to convert “silently wrong” into “loudly dead.”

That is the dangerous thing about a known runner behavior with a soft contract: each new seed migration is a fresh chance to forget the contract, and whether you find out depends on luck.

The systemic fix: a test that applies every migration twice

Hotfixing 045 got prod back up. But a hotfix on one file doesn’t stop the next engineer from writing the next bare INSERT. The real fix is to make the contract enforceable, not memorized.

So we added an apply-twice guard, shipped in cp#429: a test that spins up a database, runs the full migration sequence, and then runs the entire sequence a second time against the same database. If any statement isn’t idempotent — a bare INSERT that dup-keys, an un-guarded CREATE that collides, an ALTER that fails the second time — the second pass fails and the test goes red.

This is deliberately a faithful simulation of what the runner does in production, not an approximation. The production failure mode was “apply twice,” so the test is literally “apply twice.” It moves the discovery from a prod crash loop to a CI failure on the PR that introduces the bad statement — which is the only place a from-scratch test suite was structurally blind to the problem.

Two things made this guard worth the effort over just “remember to use ON CONFLICT”:

• It’s mechanical. Reviewers don’t have to spot a missing conflict clause by eye on every data migration. The test catches the whole class — duplicate inserts, non-idempotent DDL, anything that breaks on the second pass.
• It documents the contract by executing it. A new engineer reading the migration directory won’t necessarily know the runner re-applies everything. A failing apply-twice test, with a clear message, tells them exactly what invariant they violated and why.

The lesson

The narrow lesson is easy: in a system where migrations are replayed, never write a bare INSERT. Seed data with ON CONFLICT DO NOTHING (or DO UPDATE when you genuinely want the seed to win, which for historical/operator-owned rows you usually don’t).

The broader lesson is the one worth carrying to other systems. We had a component with an unusual but legitimate behavior — replay-everything-on-boot — and a contract that behavior imposed on every author downstream of it. That contract lived in people’s heads and in a couple of scar-tissue memories (the 028 clobber, then the 045 crash). Tribal knowledge is not an invariant. It’s an invariant waiting to be violated by the next person who didn’t get the memo.

The fix that actually closes the issue isn’t the one-line ON CONFLICT — that just patches one file. It’s the test that makes the runner’s contract executable, so the system tells you when you’ve broken it instead of waiting for production to. When a component’s correctness depends on every future contributor remembering an implicit rule, the highest-leverage move is to stop relying on memory and let the build fail.

We chose a re-applying migration runner on purpose, and we still think the convergence property is worth it. But “the runner re-applies everything” and “every migration is idempotent” are not two facts — they are one fact with two halves, and you don’t get to ship only the first half. cp#430 brought prod back. cp#429 is what keeps it back.