mufassall

Cheat sheet · Business processes

Gapless document numbering

14 min · Intermediate · mufassall.dtwin.uz/en/l/document-numbering/

← Open the lesson

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
Final scene

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_api

Mermaid: 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)
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Missing numbers0/yr0/yr
Numbers used twice0/yr0/yr
Finalizes per series900/s780/s
p99 finalize wait4 ms18 ms

Steps

  1. 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.

  2. Problem The register has holes

    At the end of the year the register is exported for the tax auditor.

  3. Problem MAX()+1 gives the same number twice

    A developer replaces the sequence: the next number is the highest one plus one.

  4. Fix A counter row per series and year

    One row per series and year holds the last number used.

  5. Fix A number only when the document is issued

    Drafts no longer get a number: they have an internal id.

  6. Problem Everyone waits for one row

    At month end the load test runs 64 parallel finalizes on one series.

  7. Fix Take the number last, do the heavy work outside

    Validation and totals move before BEGIN.

  8. Problem An issued invoice is deleted

    Invoice 000517 went to the wrong buyer.

  9. Fix Cancel, never delete

    An issued document is cancelled: status cancelled, the time, the reason and a link to the document that replaces it.

  10. Fix A series per branch

    The company opens a branch office.

  11. Fix Numbering starts again on 1 January

    Every series starts at 000001 in a new year.

  12. 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.