Engineering6 min read
Why gapless invoice numbering is harder than it looks
Two people issuing an invoice in the same second is the whole problem. A look at how sequential numbering is actually enforced, and why an auto-increment column isn't enough.
By myInvoice.ae Team
Tax authorities, the FTA among them, expect invoice numbers to run in an unbroken series. No duplicates, no gaps. It sounds like the easiest requirement in the whole compliance list — increment a counter — and it is the one most systems quietly get wrong.
Where the gaps come from
A database auto-increment column looks like a counter, but it isn't a sequence you control. Roll back a transaction and the value is consumed anyway: the row is never written, the number is never used, and you have a gap you cannot explain to an auditor. Reserve the number in application code instead, and two requests arriving together will read the same value and issue the same number twice.
The fix is a lock, not a cleverer counter
The number has to be handed out under a lock held for the duration of the write. In myInvoice.ae each organisation has one sequence record per document type, and issuing a number selects that row FOR UPDATE — a genuine row-level lock. The second request waits, reads the incremented value, and gets the next number. No duplicates, and no consumed-but-unused values.
- One sequence per organisation, per document type — invoices, quotations, credit notes and bills each run their own series.
- The number is issued inside the same transaction that writes the document.
- Cancelling a document keeps its number: the record is retained rather than destroyed, so the series stays continuous.
Compliance features are rarely hard to describe. They are hard to guarantee under concurrency.