Why document numbers do not come from a database sequence
A sequence is the obvious way to number invoices, and it is wrong. Here is what a gap costs you, and what we do instead.
Every database has a sequence. It is fast, it is safe under concurrency, and it never hands the same number to two callers. It is also the wrong tool for an invoice number.
The problem is the gap
A sequence increments outside your transaction. If the transaction rolls back — a validation failure, a credit-limit block, a user pressing escape — the number is gone. The next invoice is INV-1043 and there is no INV-1042. Nothing is broken, technically.
Then the auditor asks where INV-1042 went, and "the database skipped it" is not an answer anyone finds satisfying. In several jurisdictions it is not an acceptable answer at all.
What we do instead
Telos keeps a document_counter table keyed on prefix, year, and branch, and allocates from it inside the same transaction that writes the document.
- On Postgres:
SELECT ... FOR UPDATEon the counter row. - On SQLCipher:
BEGIN IMMEDIATE, plus a unique constraint on(prefix, year, branch, sequence)as the backstop.
If the transaction rolls back, the counter rolls back with it. The number is never consumed by a document that does not exist.
The cost
This serialises document creation per counter. Two clerks raising an invoice at the same instant queue for a moment. That is a real cost and we accept it, because the alternative is a gap you cannot explain.
It also means the counter is per prefix, year, and branch — so two branches do not contend with each other, and each branch series reads continuously on its own.