A draft invoice must not have a number, and a sequence cannot give you a gapless one
In several jurisdictions an invoice series has to be unbroken. Not “mostly sequential” and not “sorted by date” — unbroken, so that a tax officer looking at the books can see that nothing was removed. If number forty-one does not exist, the business is explaining itself.
The obvious implementation is a database sequence. It is atomic, it is fast, it is one line in a migration, and it is wrong for this, because a sequence is explicitly not transactional. If the transaction that drew number forty-one rolls back, forty-one is gone. The sequence has already advanced and it does not go back. That behaviour is correct and deliberate — it is what makes a sequence fast under concurrency — and it is exactly the property that disqualifies it here.
The second instinct is to take the maximum existing number and add one. That is a read, a decision and a write, which produces duplicates the first time two people issue an invoice in the same second.
The counter that is a row
What was used instead is a counter table with one row per tenant per series per financial year, incremented by a single statement that inserts the row if it is missing, increments it if it is not, and returns the new value in the same round trip.
INSERT INTO counters (tenant, series, period, value)
VALUES (:t, :s, :p, 1)
ON CONFLICT (tenant, series, period)
DO UPDATE SET value = counters.value + 1
RETURNING value
That is one statement, so it is atomic. It runs inside the caller’s transaction, so if the caller rolls back, the increment rolls back with it and the number is available again. It is per tenant, so one customer’s activity never advances another customer’s series. And it is per series, so orders, dispatches, invoices, credit notes and receipts each count independently.
There is a cost and it is worth stating plainly: every issue of a document in a given tenant and series contends on one row. Two concurrent issues serialise. For this workload that is correct — gaplessness is serialisation, and any implementation that avoids the contention has necessarily given up the guarantee. If the throughput ever matters more than the guarantee, the honest move is to say so out loud and switch to a sequence, not to invent a scheme that quietly has holes.
The number is minted at issue, not at creation
The second half of the problem is timing, and it is the half people get wrong.
An invoice starts life as a draft. It can be edited, corrected, and abandoned. If the number is allocated when the draft row is created, every abandoned draft is a permanent hole in the series — and drafts get abandoned constantly, because that is what a draft is for.
So the number column is nullable. A draft invoice has no number. The number is minted at the moment of issue, in the same transaction that computes and persists the tax lines and sets the issue date, and from that moment the record is immutable: an attempt to edit an issued invoice, or to issue it again, returns a conflict.
That gives the column a clean meaning. A number on the record means the document exists in the world. No number means it does not yet. There is no third state where a document is half-issued, and no reconciliation needed between a status field and a number field that can disagree.
The same shape applies to the other series. Dispatch numbers are minted when a dispatch is created, because a dispatch record is not a draft — it exists because something is being shipped. Receipt numbers are minted at the moment a payment is recorded, for the same reason. Order numbers are minted at creation. The rule is not “always mint late”; it is mint at the first moment the record is real, and know which moment that is for each document type.
The unique index has to say the same thing
A unique index on the number column would reject every draft after the first, because they are all null — depending on the database, either immediately or never, and both are surprises. And a plain unique index also collides with soft deletion: delete an invoice, create another, and the number comes back.
The index used is partial, and its condition is the interesting part:
UNIQUE (tenant, number) WHERE number IS NOT NULL AND deleted_at IS NULL
Two conditions, two different reasons. The null condition says drafts do not participate. The deleted condition says a soft-deleted row does not hold its number hostage.
That second condition deserves scrutiny rather than acceptance, because it means a soft-deleted issued invoice frees its number for reuse, and reusing an issued invoice number is a worse problem than a gap. The resolution in this system is that issued invoices are voided rather than deleted — voiding is a status change, the row stays active, and the number stays taken. The partial index supports the soft-delete convention that every other table uses without weakening the series, because nothing ever soft-deletes an issued document.
That is a real dependency between a schema decision and a service-layer rule, and it is exactly the kind of thing that gets broken later by someone adding a delete path for tidiness. It belongs in a comment on the constraint, where a person inspecting the table will see it.
What the prefix quietly assumes
The numbers carry a financial-year segment. Financial years do not start in January in every country, so the start month is a tenant setting rather than a constant, and it sits alongside the tax regime and rounding rules on the same settings record.
That is easy to get right when the module is built and easy to get wrong when a different module needs to know which year a document belongs to. The counter’s period key and the report’s year grouping must derive from the same setting, or the invoice numbered for one year appears in the totals for another. This is the same failure as a timezone that is stored and never read: a setting that exists and is consulted by one caller out of three is a setting that guarantees disagreement.
Rules
Do not use a database sequence for anything a regulator counts. Sequences advance outside your transaction. That is a feature, and it is incompatible with gapless.
Allocate a document number at the first moment the document is real, and never before. Nullable until then. The presence of the number then means something on its own, with no status field to cross-check.
Make the uniqueness index partial, and write down what each condition in it depends on. Excluding soft-deleted rows is safe only if nothing ever soft-deletes an issued document, and that is a service-layer promise sitting under a schema-layer guarantee.
Freeze the record when the number is minted. Editing an issued document is a credit note, not an update.
The limit worth admitting: this design serialises issues within a series and I have not needed it to do otherwise. If a tenant ever issues invoices faster than one row lock allows, the answer is a conversation about whether they actually need gapless numbering, not a cleverer counter.