[bug] Two migrations share the number 0092 — ordering is alphabetical by accident, not by design

[serge-ivo]

workers/api/migrations/ has a collision at origin/main:

0092_ai_usage_payer.sql
0092_coder_repo_engine_copy.sql

Every other number in the directory is unique; these two are the only duplicate.

Why it is worth fixing even though it currently works

Wrangler applies migrations in filename order and records applied ones by name, so both files do run, and they happen to run in the order someone would want (ai_usage_payer sorts before coder_repo_engine_copy). Nothing is broken today.

But the ordering is now an accident of the words after the number rather than the number itself. Two consequences:

1. The next collision may not be lucky. A 0092_a… added later would run before both, silently, with no warning — and migrations are the one thing in the system where order is the whole contract.
2. It breaks the invariant a reader relies on. The number is how anyone reasons about "what ran before what"; two files claiming 0092 means the sequence can no longer be read off the names.

Fix

Renumber the later one to 0094_ (0093 is taken by 0093_notification_floor.sql) — only if it has not been applied to production yet. If it has, renaming is the wrong move: wrangler keys applied migrations by filename, so a rename re-runs it. In that case leave the files alone and add the guard below instead.

Either way, worth a cheap CI check: fail when two migration filenames share a numeric prefix. This is the same shape as the file-size ratchet and the test-isolation guard already in ci.yml — a class of mistake that is invisible in review and trivial to assert against.

Found while verifying #361's 0093_notification_floor.sql had landed.

[serge-ivo]

Fixed in 48eb443 — by building the guard and deliberately not renaming.

The rename is the hazard; the collision is inert. 0092_coder_repo_engine_copy.sql is already applied in production (shipped in a9f4cd7). Wrangler records applied migrations by the FULL filename, so renaming either file makes it look unapplied and it runs a second time against live D1 — and 0092_ai_usage_payer.sql's ALTER TABLE ai_usage ADD COLUMN payer has no idempotent form in SQLite, so the re-run fails with duplicate column name: payer, mid-batch. This ticket's own analysis reaches the same conclusion in its second paragraph; the pair stays, and the reason is now written down where a future reader will find it before tidying it away.

What shipped instead is the thing that stops the next one — scripts/check-migrations.mjs, run in CI before install:

• Duplicate numbers fail. The 0092 pair is a recorded exception carrying the reason above. A third 0092_*.sql fails, and so does the disappearance of either recorded file (which is what a rename would look like), with the re-run hazard spelled out.
• Filename shape. NNNN_lower_snake.sql — four fixed digits are what make wrangler's alphabetical order the same as numeric order (100_x.sql would run before 0099_x.sql).
• A shipped migration's DDL is frozen. Editing one reaches no database that already applied it, so production keeps the old text while a fresh DB gets the new one. 0072_seed_tmux_operator_agent.sql is the live example: its seeded tool list was rewritten from tmux_* to terminal_* a day after it shipped. Comments are exempt by construction (the comparison strips them) — 0080's comment was corrected in place today precisely because that comment was where a wrong idea had been written down and believed. Four seeds that were edited post-ship (0043, 0057, 0063, 0072) are grandfathered to the commit of that edit, so further drift still fails.

Watched it go red: adding a third 0092_*.sql fails with the pair's reason printed underneath, then removed. workers/api/src/lib/migration-hygiene.test.ts (13 tests) drives the real script over throwaway git repos so both arms and every edge of the exemption stay proven.

The root cause was a race, not carelessness: four agents landing migrations in parallel, two picking the same free number minutes apart from a listing that was stale when read. CI checks out with fetch-depth: 0 and passes --require-history, so the history-dependent half cannot quietly become a job that reports success from verifying nothing.