A column named id is a naming convention, not a type
Three columns broke when every identifier in the database was converted to one uniform format, and each of them broke because the word “id” in its name was describing a convention rather than a thing.
The change itself was straightforward on paper. Forty primary key columns, various shapes, some strings minted by the application with a prefix, some integers. All of them to become a single time-ordered UUID type, natively stored, so that keys sort by creation, indexes stay well-behaved and nothing anywhere has to parse a prefix to know what it is looking at.
The forty columns did not split two ways into “convert” and “do not convert”. They split four ways, and working out which was which took longer than the migration.
The four categories
Server-minted identifiers. Twenty-two of them. These are the straightforward case: the server creates the row, the server chooses the key, nothing outside the system depends on the format. These became the native type.
Composite keys. These follow their parents. A join row keyed on two foreign keys converts if and only if both of the things it points at converted, and it is not an independent decision.
Credentials wearing an identifier’s name. Two columns held strings that grant something rather than name something — a short code a child types off a screen, and a token that goes out in a link. Both were excluded and given a proper identifier alongside them instead. That distinction deserves its own treatment and I have written about it separately; the only point here is that it is a category, not an exception.
Things that were never identifiers at all. Three columns, all named in a way that says otherwise, all caught by tests after the conversion was already written.
The three that were not identifiers
The first was an id field on a response object describing an AI-generated quiz candidate. It looks
like a key. It reads like a key. It has no row behind it — it is a handle the client uses to refer
to one item in a list within a single response, and it exists for a few seconds. Converting it to a
database identifier type is not just unnecessary, it is a lie about the value’s lifetime, and the
first thing anyone reading the code would do is try to look it up.
The second was a field on a ledger row recording who decided something. Most of the time that is a person. Sometimes it is the literal word describing an automatic decision, because a quiz marks itself and pays on submission with no human in the loop. So the column holds a display string, one of whose values is not a name at all. It looked like a foreign key because it sat next to several real ones.
The third is the interesting one. The audit log has a column recording which entity a logged event concerned. For most events that is a row identifier. For some events it is a pairing code or an invite token, because an event about an invite has no better handle than the token itself. So the column holds identifiers and credentials and presumably other things later. It stays text, and the honest description of it is “a reference to whatever this event was about”, which is not a type any schema can enforce.
Any column whose value’s meaning depends on another column’s value is not a typed column, and attempting to type it will either fail or force you to lie. The polymorphic reference is a real pattern with real costs, and the cost is exactly this: you cannot constrain it, so you have to document it.
All three were caught by the test suite rather than by review. That is worth saying plainly, because I had read the diff.
The version gap that would have passed locally and failed in production
There was a second, unrelated trap in the same piece of work, and it is the one I would warn about hardest.
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 running the newer version. Production was two majors behind.
Had I used the built-in function, every local test would have passed, the migration would have run clean in development, and it would have failed on the production box at the moment of deployment, during a schema change, on a live database.
So the generator is hand-rolled in application code instead, following the specification directly, including the counter method that keeps ids monotonic within a single millisecond rather than merely within a second. Verified over twenty thousand sequential generations and twenty thousand generated across threads, because a monotonicity claim that has not been tested under concurrency is not a claim.
A development environment running a newer version of the same engine than production is a green test that lies about the exact class of problem it exists to catch. The mitigation is dull and absolute: know both version numbers, and treat any use of a feature introduced between them as a production-only failure that your tests are structurally unable to see.
The one that got through anyway
Weeks later I found myself writing SQL that minted random UUIDs — the old, non-ordered kind — into a schema where everything else was the time-ordered kind.
Nothing complained. Both are UUIDs. Same column type, same length, same representation, valid insert, no constraint violated. The only symptom is that those rows do not sort correctly by key alongside the others, which is invisible until it matters.
When two things share a representation, no type system will tell you they have diverged. That is not a defect in type systems, it is their boundary, and the practical consequence is that anything you have standardised at the value level rather than the type level needs a check that runs — a constraint, a test that reads back a sample, something. A convention nothing enforces lasts exactly as long as the attention of whoever wrote it down.
The last fix was smaller and worth copying. Serialising these values into JSON broke in several places at once. Rather than patching each call site, it was fixed once at the database engine’s own serialiser, which is the only place that has to know. When the same conversion bug appears in five files, the bug is that the conversion happens in five files.