Two Migrations Claimed the Same Sequence Number

The short version

Two feature branches each independently created a migration numbered 0035 on the same day. Both are already applied to the production DB as idempotent SQL, and there was no harm. What is dangerous is not the harm but the latent mine left behind: when a fresh database or a staging environment is run through in numeric order, which of the two executes first can vary by environment. We resolved it by renaming the later one to 0039.

What it looks like

Under supabase/migrations/, two files sat side by side with the same number.

0034_slot_model.sql
0035_company_apply_fields.sql
0035_must_change_password.sql   ← duplicate
0036_relax_term_months_min.sql

0035_company_apply_fields.sql only adds extra application-form fields (representative_email / billing_email) to the companies table, and 0035_must_change_password.sql only adds a flag column for the first-login password change to the profiles table. Both are idempotent ALTER TABLE ... ADD COLUMN IF NOT EXISTS SQL. Both were already applied to the production DB with no effect on operation.

Why

Looking at the two commits’ creation times: 19:01 and 19:42 — 41 minutes apart on the same day.

56c5dec  19:01  feat(apply): expand the application form ... (0035_company_apply_fields.sql)
dd64894  19:42  feat(portal): add the first-login password change flow (0035_must_change_password.sql)

And reading the comment in 0035_company_apply_fields.sql shows the number was not a guess but a decision made while watching for collisions.

--  ⚠️ Number: the latest on trunk (feat/zero-touch-automation) is 0034_slot_model.
--    0031 (receipts) / 0032 (role split) / 0033 (impact adjustment) / 0034 (slot model)
--    are already numbered from their own feature branches, so 0035 is taken to avoid a collision.

At the time, four migrations from 0031 to 0034 had been numbered from separate feature branches, and 0035 was the “next free number” chosen in light of them. The awareness of avoiding a collision was there, and it did avoid four of them.

It collided on the fifth anyway. The reason is simple: supabase/migrations/ manages its sequence by hand (plain integers 0001, 0002, …), and the only material for deciding “what is the next number” is the local file listing your own branch knows about. At the moment the 19:01 commit judged “trunk’s latest is 0034”, the 19:42 commit had not been pushed and existed nowhere. The other branch was independently judging “next is 0035” in exactly the same way, and neither had any means of knowing the other existed.

Fixing it

Without changing the contents, we renamed the later 0035_must_change_password.sql to 0039, which does not collide with the 0037 and 0038 in flight at the time.

0035_must_change_password.sql → 0039_must_change_password.sql

Not a byte of the file’s contents changed. Both are additive-only SQL with IF NOT EXISTS, so renumbering causes no re-application and does not affect the production DB’s state. It was a matter of re-establishing the sequence’s meaning as “the order in which this will be applied to new environments in future” rather than “the order in which it was actually applied”.

Preventing a repeat

What this incident shows is that “being alert to collisions” alone cannot protect hand numbering across parallel branches. The 19:01 commit genuinely was alert, and chose its number knowing the preceding four. It still failed, because the material for the judgment is always “the local latest state visible from your own branch”, and another branch numbering at the same moment is invisible to everyone until it is decided whose push lands first.

The sources behind this article contain no record of adding a mechanism to detect number collisions automatically. This one was resolved by renaming, but as long as the same structure remains, the same thing can happen the next time two branches number at the same moment.