In April, Yan, the controller at a six-hundred-person industrial equipment manufacturer, exported four thousand purchase orders for an audit. The auditor scrolled to the middle of the sheet and stopped. PO-2026-0147 was there. PO-2026-0149 was there. 0148 was not.
She checked the recycle bin, the deleted filter, the archived view. Then she sent me the spreadsheet with one line: "Either somebody deleted a purchase order, or the system invented a number and threw it away. Which one do I tell the auditor?"
The answer is the second one. It is worse than a bug, because it is the design, and because nobody in the room had ever been told.
The number is not in the row
Every data table the platform creates gets two system columns for free. An id, a thirty-two character string, and a seq, an integer described in our own documentation as the order in which records were added.
The number a customer actually reads — the one printed on the purchase order — is neither of those. It is a separate field with its own PostgreSQL sequence object behind it, named after the application, the table, and the field. On insert, the platform takes the next value from that sequence, runs an expression over it, and stores the result as a string.
So the number is not a property of the record. It is a token the record was allowed to keep. That distinction sounds academic until the first time you need to explain a missing one.
nextval does not roll back
Our documentation says it in a single sentence, buried under the settings table: sequences in PostgreSQL are non-transactional, so numbering may not be continuous when a transaction rolls back, because a value taken from the sequence is never returned.
Read that as a specification rather than a caveat and you get the whole of 0148. A clerk opened a purchase order, mistyped a supplier code, hit save, and the platform pulled 0148 before the validation rule rejected the form. She fixed the code and saved again, and got 0149. The transaction rolled back. The number did not. There is no row, no audit entry, no trace of the attempt, and a permanent hole in a document series a customer will reconcile against a supplier's ledger for the next seven years.
We already have the vocabulary for this. A voided invoice keeps its number and is stamped void; the gap is explained by the record. We do the opposite. We erase the number and keep nothing, so the only evidence that anything happened is the absence of evidence.
Two settings that have to agree, and nothing that checks
Numbering has exactly two settings. An expression that builds the string, and a reset period: never, daily, monthly, or yearly, each snapping the counter back to one at midnight.
The date inside the number is written by the expression. The reset is a dropdown. Nothing validates that the two describe the same period, and nothing warns you when they do not.
Choose monthly reset with a year-only prefix and the first purchase order of February asks for 2026-00001, which January already spent. The uniqueness check — which does run, at insert — refuses the save. The first person to submit a document on the first of the month gets an error about a duplicate number they never typed, and the person they call for help has to reason about a sequence object they cannot see.
Midnight is its own small hazard: the reset fires at 00:00, and the expression reads the server clock when it executes. A save straddling the boundary can carry yesterday's date with today's counter. Rare, unglamorous, impossible to reproduce on request.
The immutable field that is not immutable
The number is stored as a string of up to a hundred and twenty-eight characters. Our documentation states, correctly, that it is computed only on insert and cannot be modified. Two paragraphs later it explains that if you need to modify it, you should call the table update function from a script.
Both sentences are true. The number is immutable to the person who owns the document and mutable to anyone who can write a script. Our own field-conversion reference is more honest still: the number is computed automatically, stored as a string, and automation or scripts may modify it.
Which means the one field in the entire platform that leaves the building — printed, scanned, faxed, quoted over the phone, keyed into another company's ERP — is protected by a sentence in a document rather than by a permission. Every other field on that form can be corrected and the correction stays inside. This one has copies that we cannot reach. When a migration script rewrites it to fix a prefix, we are not editing data. We are editing the past.
It also sorts like text
Because the stored type is a string, sorting follows string rules. One, ten, one hundred, one hundred and two, two. Yan's export was ordered that way, which made a few real gaps look like a pattern of tampering.
The fix is a function field that casts the number to a numeric type and sorts on that instead. Most teams add it after their first audit. Nobody adds it before, because in a demo the list has eleven rows and everything looks fine.
The backup that does not contain the counter
When you export a table's data, you get rows. The sequence lives in a separate object with its own name and its own last value, and a table-level dump does not carry it.
Our migration guide knows this. It includes a step titled reset sequence value, a single UPDATE against the numbering field's sequence, to be run after the rows are restored. It is one line in a runbook executed at two in the morning by an engineer with forty other lines to get through. Skip it and the counter starts from wherever the target happened to be, and the platform begins issuing numbers that already exist on paper.
The uniqueness check turns most of those into failed saves rather than duplicates, which is a small mercy and a poor recovery plan. On a date-prefixed scheme crossing a year boundary, it can do something worse: hand out a number that is genuinely new, correctly formatted, and attached to the wrong document.
Why we built it this way
Because a counter is fast and a ledger is a table with a lock.
Taking the next value from a sequence is constant time and never contends. Allocating from a ledger row — scoped by document type and period, with a unique index held until commit — contends at exactly the moment every customer submits at once, which is Monday morning.
So we optimized the path everyone uses and moved the cost to the one afternoon a year when someone asks a question we cannot answer. Gaps are cheap for us and expensive for the customer, and that asymmetry never appeared in a metric, because nobody measures numbers that were never issued.
The uncomfortable conclusion
The fix is not research. A number table with scope, period, value, record id, and state. Allocation inside the same transaction as the record. A unique index doing the work instead of a check-then-write. Reset by inserting a new period row rather than mutating a counter at midnight. Gaps written down as void, with a reason and a person. And immutability enforced by a permission, not by a paragraph.
Every one of those makes a create slower and a failure louder. The current design makes both invisible, and invisible is what a demo rewards.
Yan's auditor accepted the explanation, eventually. What she did not accept, and what I have not been able to argue with since, is that the number on the document is a promise — to a customer, an auditor, a tax authority — and we built it as a counter with a format string attached.
Fourteen hundred records before 0148 were fine. That is not the point. The point is that we shipped an identifier that leaves the building without ever deciding who owns it, and then wrote a sentence in the documentation admitting it might not be there.
Top comments (0)