Number Ranges, Document Numbering, and Keys

Document numbering looks trivial and is not. It is a legal requirement, a concurrency problem, and a common source of production incidents.

Why it’s a legal matter

In most jurisdictions, invoice numbers must be sequential and gapless per legal entity per fiscal year. Tax authorities use gaps as evidence of suppressed sales.

Countries with strict rules: most of the EU (VAT invoicing), India (GST invoice series), Brazil (NF-e with government authorisation per document), Italy (SdI), Bangladesh (Mushak 6.3 serial), Saudi (ZATCA), Mexico (CFDI folio), Poland (KSeF).

Consequences:

You cannot use a plain database sequence that gaps on rollback.
You cannot reuse numbers.
You cannot reorder.
You often need a separate series per legal entity, per document type, per fiscal year, and sometimes per branch or per point of sale.

Number range object model

number_range(
  object          -- 'SALES_ORDER', 'AR_INVOICE', 'JOURNAL', 'MATERIAL'
  company_id
  fiscal_year     -- nullable if year-independent
  range_code      -- '01', '02'
  from_number
  to_number
  current_number
  prefix / suffix -- 'INV-', '/2026'
  gapless         -- boolean
  external        -- number supplied by the user instead of assigned
)

Internal vs external numbering

Internal — the system assigns from the range. Used for documents.
External — the user or an interface supplies the number. Used for master data in some companies (e.g. material numbers that mirror an engineering scheme), and for migrated documents.

Typical setup: one range for internal (e.g. 4500000000–4599999999) and a separate non-overlapping range reserved for external/migrated (e.g. 9000000000–9099999999) so migration doesn’t collide with live numbering.

The concurrency problem

Two competing requirements:

Gapless requires serialising number assignment — the number is only consumed if the transaction commits.
Throughput requires not serialising.

Approaches:

1. Buffered / non-gapless (fast, gaps allowed)

Each application server grabs a block of N numbers into memory and hands them out. Fast, but gaps appear on restart or rollback.

Use for: internal documents where gaps are legally fine (sales orders, deliveries, material numbers, purchase requisitions).
SAP calls this number range buffering; it’s why sales order numbers jump.

2. Serialised, gapless (slow, correct)

Take a row lock on the number range row, increment, and hold until commit.

BEGIN;
SELECT current_number FROM number_range
 WHERE object='AR_INVOICE' AND company_id=1000 AND fiscal_year=2026
 FOR UPDATE;
UPDATE number_range SET current_number = current_number + 1 WHERE ...;
-- insert the invoice with that number
COMMIT;

This lock is a global serialisation point for that document type. Everything queues behind it.
Use only where legally required (customer invoices, credit memos, official journals).
Keep the transaction that holds the lock as short as humanly possible — assign the number last, right before commit, never at the start of a long document-build process.

3. Assign at posting, not at creation

Draft/parked documents get a temporary identifier; the legal number is assigned at the moment of posting. This is what real ERPs do, and it minimises lock time.

4. Reserve-and-void

Assign eagerly; if the transaction fails, record a voided/cancelled number rather than leaving a gap. Tax authorities accept documented voids. This is how POS systems handle it.

Anti-pattern: MAX(number) + 1. It races, it deadlocks, it double-assigns under load, and it gets slower as the table grows. It is the single most common homemade-ERP bug.

Fiscal-year-dependent ranges

Many objects restart at 1 each fiscal year:

AR invoice, company 1000, FY2025:  1000000001 – 1000999999
AR invoice, company 1000, FY2026:  1001000001 – 1001999999

Your year-end job must create next year’s ranges before the first posting of the new year. Forgetting this is a classic January 1st incident. Automate the check.

Key design in the tables

Composite natural keys

Classic ERPs use composite natural keys, not surrogate IDs:

Journal line key = (company_code, fiscal_year, document_number, line_number)
Sales item key   = (sales_document, item_number)
Stock key        = (material, plant, storage_location, batch, stock_type)

Pros: human-readable, matches the printed document, no join to explain a row. Cons: wide foreign keys, painful refactoring, migration complexity.

Modern designs often carry both: a surrogate id BIGINT primary key plus a unique constraint on the natural key. That’s the pragmatic answer if you’re building your own.

Line numbering

Items are usually numbered in tens (10, 20, 30) so lines can be inserted between them.
Line numbers are not reused within a document.
Sub-items (BOM components exploded into an order) use a hierarchy: item 10 with sub-items 11, 12, 13, or a separate parent_item column.

Client / tenant key

Classic ERPs put the tenant key (MANDT/client in SAP) as the first column of every table and every index. Modern multi-tenant systems do the same with tenant_id. See Multi-tenancy.

Prefixes and readability

Users navigate by number. Good conventions:

SO-2026-0001234    Sales order
DN-2026-0001234    Delivery note
INV-1000-26-000123 Invoice, company 1000, FY26
JV-1000-26-000045  Journal voucher

Trade-offs: legible prefixes help humans and hurt fixed-width legacy interfaces and EDI. Decide once, per document type, and never change it — changing an invoice numbering format mid-year annoys tax authorities.

Practical checklist

Every document object has an explicit number range definition, not an implicit sequence
Legally-numbered documents use gapless serialised assignment, everything else uses buffered
Number assignment happens as late as possible in the transaction
Next fiscal year’s ranges are created automatically before year end, with an alert if missing
A separate reserved range exists for data migration and for interfaces
There is a monitoring alert at 80% range consumption
Voided/cancelled numbers are recorded, not silently skipped, for gapless series
Number range config is transported/deployed but current_number is not overwritten across environments

That last one causes real incidents: deploying config from DEV that resets PROD’s current number, then re-issuing existing invoice numbers.