Cheat sheet · Business processes
Gapless document numbering
The problem
In many countries issued invoices must carry continuous numbers: per series and per year, no number skipped and none used twice, and an auditor checks the register. A database sequence leaves gaps, MAX()+1 gives duplicates. A counter row taken in the finalize transaction gives both guarantees, at the price of making writers wait in line, so the lesson also measures that price and keeps it small.
What you learn
- Why database sequences leave gaps by design, and why MAX()+1 hands the same number to two documents
- A counter row per series and year, taken with UPDATE ... RETURNING in the same transaction as the document, and what its row lock costs in throughput
- Take the number at finalize, not at draft; keep the locked part of the transaction short
- Cancel instead of delete, a series per branch, and a year rollover in the company's legal time zone
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_clerk(["Accountant"])
n_api["Invoice service"]
n_db[("Invoices DB")]
n_export("Register export")
n_auditor>"Tax auditor"]
n_branch(["Branch office"])
n_clerk -->|"call"| n_api
n_api --> n_db
n_export --> n_db
n_export -->|"async"| n_auditor
n_branch -->|"call"| n_apiMermaid: request sequence
sequenceDiagram
participant n_api as Invoice service
participant n_db as Invoices DB
participant n_export as Register export
participant n_auditor as Tax auditor
Note over n_api,n_db: Every invoice gets the next number
n_api->>n_db: INSERT INTO invoices (id, number, status, buyer_id)
n_db-->>n_api: INSERT 0 1 number = INV-2026-000481
Note over n_export,n_auditor: The register has holes
n_export->>n_auditor: register 2026, series INV: 000481 000482 000484 000485 000490 ...
Note over n_api,n_db: MAX()+1 gives the same number twice
n_api->>n_db: number = SELECT max(no) + 1 FROM invoices WHERE series = 'INV'
Note over n_api,n_db: A counter row per series and year
n_api->>n_db: UPDATE number_counters
n_db-->>n_api: UPDATE 1 last_no = 482
Note over n_api,n_db: A number only when the document is issued
n_api->>n_db: BEGIN#59; -- POST /invoices/9f2c/finalize
Note over n_api,n_db: Everyone waits for one row
n_api->>n_db: load test: 64 parallel finalizes, one series
Note over n_api,n_db: Take the number last, do the heavy work outside
n_api->>n_db: finalize, reordered
Note over n_export,n_auditor: An issued invoice is deleted
n_export->>n_auditor: DELETE FROM invoices WHERE number = 'INV-2026-000517'#59; -- wrong buyer
Note over n_api,n_db: Cancel, never delete
n_api->>n_db: UPDATE invoices
Note over n_api,n_db: A series per branch
n_api->>n_db: SELECT series, year, last_no FROM number_counters#59;
n_db-->>n_api: HQ #124; 2026 #124; 517
Note over n_api,n_db: Numbering starts again on 1 January
n_api->>n_db: INSERT INTO number_counters (series, year, last_no)D2
direction: right
n_clerk: "Accountant" {
shape: rectangle
}
n_api: "Invoice service" {
shape: rectangle
}
n_db: "Invoices DB" {
shape: cylinder
}
n_export: "Register export" {
shape: rectangle
style.multiple: true
}
n_auditor: "Tax auditor" {
shape: cloud
}
n_branch: "Branch office" {
shape: rectangle
}
n_clerk -> n_api: "call"
n_api -> n_db
n_export -> n_db
n_export -> n_auditor: "async"
n_branch -> n_api: "call"PlantUML: request sequence
@startuml
participant "Invoice service" as n_api
database "Invoices DB" as n_db
participant "Register export" as n_export
boundary "Tax auditor" as n_auditor
note over n_api, n_db : Every invoice gets the next number
n_api -> n_db : INSERT INTO invoices (id, number, status, buyer_id)
n_db --> n_api : INSERT 0 1 number = INV-2026-000481
note over n_export, n_auditor : The register has holes
n_export ->> n_auditor : register 2026, series INV: 000481 000482 000484 000485 000490 ...
note over n_api, n_db : MAX()+1 gives the same number twice
n_api -> n_db : number = SELECT max(no) + 1 FROM invoices WHERE series = 'INV'
note over n_api, n_db : A counter row per series and year
n_api -> n_db : UPDATE number_counters
n_db --> n_api : UPDATE 1 last_no = 482
note over n_api, n_db : A number only when the document is issued
n_api -> n_db : BEGIN; -- POST /invoices/9f2c/finalize
note over n_api, n_db : Everyone waits for one row
n_api -> n_db : load test: 64 parallel finalizes, one series
note over n_api, n_db : Take the number last, do the heavy work outside
n_api -> n_db : finalize, reordered
note over n_export, n_auditor : An issued invoice is deleted
n_export ->> n_auditor : DELETE FROM invoices WHERE number = 'INV-2026-000517'; -- wrong buyer
note over n_api, n_db : Cancel, never delete
n_api -> n_db : UPDATE invoices
note over n_api, n_db : A series per branch
n_api -> n_db : SELECT series, year, last_no FROM number_counters;
n_db --> n_api : HQ | 2026 | 517
note over n_api, n_db : Numbering starts again on 1 January
n_api -> n_db : INSERT INTO number_counters (series, year, last_no)
@endumlPlantUML: components
@startuml
left to right direction
agent "Accountant" as n_clerk
component "Invoice service" as n_api
database "Invoices DB" as n_db
collections "Register export" as n_export
cloud "Tax auditor" as n_auditor
agent "Branch office" as n_branch
n_clerk --> n_api : call
n_api --> n_db
n_export --> n_db
n_export --> n_auditor : async
n_branch --> n_api : call
@endumlRecap
Six rules for numbers the law requires to be continuous.
1) Do not use a database sequence for them: it skips numbers by design. 2) Do not use MAX()+1: two writers read the same maximum. 3) Keep a counter row per series and year and take the number with UPDATE ... RETURNING in the same transaction as the document: a rollback reuses it. 4) Take the number at finalize, never at draft. 5) The row lock serializes writers: take the number last, keep heavy work outside, split by series where the rules allow. 6) Cancel instead of deleting, and roll the year over in the legal time zone with rows created in advance.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Missing numbers | 0/yr | 0/yr |
| Numbers used twice | 0/yr | 0/yr |
| Finalizes per series | 900/s | 780/s |
| p99 finalize wait | 4 ms | 18 ms |
Steps
- Case Every invoice gets the next number
An accountant creates an invoice and the service gives it a number like
INV-2026-000481: series, year, and a running number. - Problem The register has holes
At the end of the year the register is exported for the tax auditor.
- Problem MAX()+1 gives the same number twice
A developer replaces the sequence: the next number is the highest one plus one.
- Fix A counter row per series and year
One row per series and year holds the last number used.
- Fix A number only when the document is issued
Drafts no longer get a number: they have an internal id.
- Problem Everyone waits for one row
At month end the load test runs 64 parallel finalizes on one series.
- Fix Take the number last, do the heavy work outside
Validation and totals move before
BEGIN. - Problem An issued invoice is deleted
Invoice 000517 went to the wrong buyer.
- Fix Cancel, never delete
An issued document is cancelled: status
cancelled, the time, the reason and a link to the document that replaces it. - Fix A series per branch
The company opens a branch office.
- Fix Numbering starts again on 1 January
Every series starts at 000001 in a new year.
- Recap Recap
Six rules for numbers the law requires to be continuous.
Terms
- Gapless numbering
- Document numbers in each series and year follow one another with nothing skipped and nothing repeated. Often a legal requirement that an auditor checks against the register.
- Number series
- A separate run of numbers with its own prefix and year, for example one per branch. Each series has its own counter, so series never wait for each other.
- Counter row
- A row holding a series' last number, incremented with UPDATE ... RETURNING in the same transaction as the document. A rollback gives the number back; the row lock makes writers take turns.
- Sequence gap
- A value a database sequence skipped: after a rollback, a cache or a crash. Sequences never go back, on purpose, for speed, so they cannot provide gapless numbers.