A migration that would have failed only in production

A schema migration that passes in development and every test suite, then fails on the production database in the middle of the deployment, is one of the more expensive ways to lose a release window. You find out with the change half applied, on a live system, with customers connected.

This one was caught before it got that far. It came out of a change that looked routine: move every primary key in the database to a single time-ordered UUID format, so keys sort by creation, indexes stay well-behaved and nothing has to parse a prefix to know what it is looking at.

Two separate things nearly went wrong in that work. One was a version gap between the development and production databases. The other was that three of the forty columns were not keys at all, and the test suite, not the diff review, is what said so.

What was actually going on

The version gap. The database engine has a built-in generator for this ID format, but only from a major version newer than the one production runs. Development was on the newer version. Production was two majors behind. Had the built-in function been used, every local test would have passed and the migration would have run clean in development. It would have failed on the production box at the moment of deployment, during a schema change, on a live database.

The column audit. The forty columns did not split into “convert” and “do not convert”. They split four ways. Twenty-two were server-minted identifiers, the straightforward case. Some were composite keys, which follow their parents: a join row keyed on two foreign keys converts if and only if both of the things it points at converted. Two held credentials wearing an identifier’s name, a short code a child types off a screen and a token that goes out in a link; both were left alone and given a proper identifier beside them. And three were never identifiers.

The three were all named “id” or similar, and the name was a convention rather than a fact:

• a handle on a response object describing an AI-generated quiz candidate, with no row behind it, that exists for a few seconds inside one response;
• a field on a ledger row recording who decided something, which is usually a person and sometimes the literal word for an automatic decision, so it holds a display string;
• the audit log’s column saying which entity an event concerned, which holds a row identifier for most events and a pairing code or an invite token for others.

The last one stays as text. The honest description of it is “a reference to whatever this event was about”, which no schema can enforce.

What we changed

The built-in generator was not used. The ID generator was written in application code, following the specification directly, including the counter method that keeps IDs monotonic within a single millisecond rather than only within a second. It was exercised over twenty thousand sequential generations and twenty thousand across threads, because a monotonicity claim nobody has tested under concurrency is not yet a claim.

The three non-keys were reverted to what they were. The JSON serialisation breakage that the change caused in several places at once was fixed once, at the database engine’s own serialiser, rather than at each call site. When the same bug appears in five files, the bug is that the change happens in five files.

What it did not fix

Weeks later, SQL was written that minted random UUIDs, the old unordered kind, into a schema where everything else was time-ordered. Nothing complained. Both are UUIDs, with the same column type, the same length and the same representation, and no constraint was violated. The only symptom is that those rows do not sort correctly by key beside the others, which stays invisible until it matters.

Two values that share a representation will not be told apart by a type system. A standard held at the value level needs a check that actually runs, such as a constraint or a test that reads back a sample, or it lasts as long as the attention of whoever wrote it down.

The version gap and the three mislabelled columns were caught before the change was deployed.

What to ask your own team or supplier

• Which version of the database runs in development, and which in production? Has anyone listed the features that exist in one and not the other?
• When a migration is reviewed, is it rehearsed against a copy of production, or only against a development database?
• For each column called “id”, can someone say in one sentence whether it names a row, grants access to something, or points at different things depending on another column?
• Where a convention is held only by habit, such as every key being the same kind of UUID, what check fails when somebody breaks it?

Where this ends up

Working out which identifiers are yours to change and which are somebody else’s handle is the first pass of most enterprise modernisation work, and it decides whether a migration is additive or a rewrite.