Generating Compliant Sequential Invoice Numbers

The moment you discover that two invoices share a number — or that an auditor found a gap between INV-2026-000041 and INV-2026-000043 — you learn that invoice numbering is a concurrency problem dressed as a formatting problem. Many tax jurisdictions (most of the EU, much of Latin America, and others) legally require invoice numbers to be sequential and gap-free per issuing legal entity, which quietly rules out the database tools engineers reach for first. This page sits under Invoicing & Credit Notes and covers exactly how to produce compliant numbers: why a Postgres SEQUENCE is the wrong tool, how to serialize finalization with row locks or advisory locks, how to handle year and prefix resets, and how to make finalization idempotent so a retry never burns a number. You reach for this when finalization is about to go concurrent and “gap-free” stops being free.

Trade-offs

Three mechanisms produce gap-free numbers and one trap does not. A Postgres SEQUENCE is lock-free precisely because it leaks on rollback; the counter row and advisory lock serialize per entity; an external coordinator only earns its network hop when numbering spans databases. The map ranks them.

There are three viable mechanisms and one trap. The trap is the bare SEQUENCE; the three real options differ in how they serialize concurrent finalizers and how they behave under contention.

For a single Postgres-backed billing system the counter-row approach is the default: it is one table, it is transactional, and a rollback genuinely un-assigns the number because the increment lives in the same transaction. Advisory locks are the better fit when you want to avoid a hot row or coordinate numbering that is not naturally tied to a single table row.

What “contention cost” actually buys you

The word “serializes” in the table sounds expensive, but put a number on it before you panic. The critical section is a single indexed UPDATE on a one-row-per-entity table plus the surrounding finalization writes — typically under a millisecond of held-lock time on warm rows. Even at 500 concurrent finalizations for the same legal_entity_id, the queue drains in well under a second, and finalizations for different entities never touch the same lock, so throughput scales with the number of distinct issuers rather than total volume. The mistake is to treat this serialization as a system-wide bottleneck; it is per-entity, and almost no real tenant finalizes hundreds of invoices for one legal entity in the same tick. If yours does — say a marketplace batch-closing a billing period — the fix is to hold the transaction open for the shortest possible span, not to abandon gap-freeness.

Why the external coordinator rarely earns its keep

An external single-writer (a Redis INCR, a Durable Object, a dedicated numbering service) looks attractive because it removes the counter from the transactional hot path. The problem is that it removes it from the transaction entirely. Redis INCR is not gap-free under your failure model: if the coordinator hands you 42 and the finalization then rolls back, 42 is gone unless you build a compensating “return the number” path, which is exactly the rollback semantics Postgres gave you for free. A coordinator only pays off when the number genuinely must span multiple databases — sharded tenants whose invoices for one legal entity live in different physical stores — and even then you inherit the coordinator’s own availability and durability as a hard dependency of finalizing any invoice. Reach for it last, and only with a documented reason.

Step-by-Step Implementation

The five steps scope the counter per entity and year, increment it under a lock inside the finalization transaction, handle the year reset via upsert, and make retries idempotent. The critical property is that the increment and the status flip commit together. The diagram shows two finalizers serializing on the counter.

1. Create a per-entity, per-year counter table

Scope the counter by both the legal entity (each issuer has its own series) and the period (so you can reset annually where the jurisdiction expects INV-2026-... to restart at 1).

CREATE TABLE invoice_sequences (
    legal_entity_id UUID    NOT NULL,
    document_type   TEXT    NOT NULL,   -- 'invoice' | 'credit_note'
    period_year     INT     NOT NULL,
    prefix          TEXT    NOT NULL,   -- e.g. 'INV'
    last_value      BIGINT  NOT NULL DEFAULT 0,
    PRIMARY KEY (legal_entity_id, document_type, period_year)
);

2. Increment under a lock inside the finalization transaction

The whole point is that the increment and the invoice’s status change commit together. With the counter row, a single UPDATE ... RETURNING takes the row lock and hands you the next value atomically.

-- runs inside the same transaction that finalizes the invoice
UPDATE invoice_sequences
   SET last_value = last_value + 1
 WHERE legal_entity_id = $1
   AND document_type   = 'invoice'
   AND period_year     = $2
RETURNING prefix, period_year, last_value;

Format the result into the human-facing number. Zero-pad so numbers sort lexically and look uniform:

def format_number(prefix: str, year: int, value: int) -> str:
    return f"{prefix}-{year}-{value:06d}"   # INV-2026-000042

3. Or serialize with a transactional advisory lock

If you prefer not to contend on a counter row — or your “next value” lives across a couple of tables — take a transaction-scoped advisory lock keyed by a hash of the entity and year. It auto-releases at commit or rollback, so there is no cleanup and no risk of a stuck lock.

One caveat deserves a hard look: pg_advisory_xact_lock takes a single 64-bit key (or two 32-bit keys), so your stable_hash(entity_id, "invoice", year) must map the tuple into that space without practical collision risk. Two different entity-year pairs that hash to the same key will block each other pointlessly — correctness is preserved (you never lose gap-freeness) but you have manufactured contention between unrelated issuers. Use a strong hash such as the low 63 bits of a SHA-256 over a delimited byte string, keep the delimiter unambiguous so ("e1","invoice",2026) and ("e","1invoice",2026) cannot collide, and treat the key derivation as a stable contract you never change once numbers are live. Because advisory locks share one namespace across the whole database, also reserve a key prefix or a distinct classid for invoice numbering so it cannot collide with an unrelated advisory lock elsewhere in the application.

def next_number_with_advisory_lock(entity_id: str, year: int) -> str:
    lock_key = stable_hash(entity_id, "invoice", year)  # 64-bit int
    with db.transaction():
        # ✅ blocks only other finalizers for THIS entity+year
        db.execute("SELECT pg_advisory_xact_lock(%s)", [lock_key])
        current = db.fetchval(
            "SELECT last_value FROM invoice_sequences "
            "WHERE legal_entity_id=%s AND document_type='invoice' AND period_year=%s",
            [entity_id, year],
        )
        nxt = (current or 0) + 1
        upsert_sequence(entity_id, year, nxt)
        return format_number("INV", year, nxt)

4. Handle the year and prefix reset

At the year boundary you typically want the series to restart. Because the counter is keyed by period_year, the first finalization in a new year simply finds no row and inserts one starting at 1 — no special-case code, just an upsert. Decide your reset policy explicitly: some jurisdictions want a continuous lifetime series (never reset), others expect a per-year series. Encode it in the key you choose.

def ensure_sequence_row(entity_id: str, year: int, prefix: str) -> None:
    # idempotent: creates the new-year series on first use, no-op thereafter
    db.execute(
        "INSERT INTO invoice_sequences "
        "(legal_entity_id, document_type, period_year, prefix, last_value) "
        "VALUES (%s, 'invoice', %s, %s, 0) ON CONFLICT DO NOTHING",
        [entity_id, year, prefix],
    )

5. Make finalization idempotent

A finalization call can be retried — a timeout, a redelivered queue message, a double click. The retry must return the number already assigned, not consume a new one. Guard on the invoice’s own state: if it is already finalized, hand back its stored number without touching the counter.

The subtle part is ordering. The load_for_update(invoice_id) row lock must be taken before you read invoice_number, otherwise two concurrent retries can both observe a null number and both proceed to increment — one document, two numbers, and a gap where the discarded number should have been. Because the SELECT ... FOR UPDATE on the invoice row serializes the two callers on that specific invoice_id, the second waits, re-reads the now-populated invoice_number, and returns early. Note this is a different lock from the counter lock: the invoice-row lock deduplicates retries of the same document, while the counter lock serializes distinct documents competing for the next value. You need both, and they nest cleanly because they are always taken in the same order (invoice row first, counter second), which is also what keeps you free of deadlocks.

If your finalization is driven by an at-least-once queue, pair this state guard with the message’s own idempotency_key so a redelivery short-circuits before it even opens a transaction; the in-transaction check then remains the last line of defense rather than the only one.

def finalize(invoice_id: str) -> str:
    with db.transaction():
        inv = load_for_update(invoice_id)
        if inv.invoice_number is not None:
            return inv.invoice_number          # ⚠️ retry — reuse, never re-number

        ensure_sequence_row(inv.legal_entity_id, now().year, "INV")
        number = increment_and_format(inv.legal_entity_id, now().year)
        set_finalized(invoice_id, number)      # status + number commit together
        return number

Verification & Testing

The two tests that matter are the concurrency run and the idempotent retry, backed by a standing gap-detection query in production. The panel lists them before the code.

The non-negotiable test is concurrency: fire many finalizations at once and assert the numbers form a contiguous, duplicate-free run. A reconciliation query then proves gap-freeness against live data.

For the concurrency test to mean anything, the workers must genuinely overlap in the critical section — an in-process thread pool against SQLite serialized by a global lock proves nothing. Run it against real Postgres with 32 or more connections, and if your ORM defaults to READ COMMITTED, confirm the row-lock path still serializes correctly under that level (it does for SELECT ... FOR UPDATE, but a naive read-then-write without the lock silently passes at low concurrency and fails at high). It is worth adding a second variant that deliberately rolls back half the finalizations mid-run and asserts the survivors are still gap-free: this is the test that catches a stray nextval() or an out-of-transaction assignment, because those are the code paths that only leak when a transaction aborts. A green run with zero rollbacks can hide exactly the bug the law cares about.

The standing production query should not run only on demand. Wire it to an alerting job per legal entity — a single returned row is a compliance incident, not a warning — and scope it to a rolling window (for example the trailing 90 days plus the current period) so it stays cheap on large invoices tables while still covering the range an auditor is likely to sample.

def test_no_gaps_under_concurrency():
    drafts = [create_draft(ENTITY) for _ in range(500)]
    numbers = run_in_parallel(finalize, drafts, workers=32)
    seq = sorted(int(n.split("-")[-1]) for n in numbers)
    assert len(set(seq)) == 500                       # no duplicates
    assert seq == list(range(1, 501))                 # gap-free 1..500

def test_finalize_is_idempotent():
    d = create_draft(ENTITY)
    first = finalize(d)
    second = finalize(d)                              # retry
    assert first == second                            # same number, no burn

Run a standing reconciliation query in production to catch any gap a bug might introduce:

-- any row where the next number isn't exactly +1 reveals a gap
SELECT invoice_number
FROM (
  SELECT invoice_number,
         (regexp_replace(invoice_number, '\D', '', 'g'))::BIGINT AS n,
         LAG((regexp_replace(invoice_number, '\D', '', 'g'))::BIGINT)
           OVER (PARTITION BY legal_entity_id ORDER BY finalized_at) AS prev_n
  FROM invoices
  WHERE status = 'finalized' AND legal_entity_id = $1 AND finalized_at >= $2
) g
WHERE prev_n IS NOT NULL AND n <> prev_n + 1;          -- must return zero rows

Gotchas & Production Pitfalls

Nearly every numbering pitfall is a way of accidentally reintroducing a gap or a duplicate: reaching for nextval(), assigning the number outside the transaction, sharing one counter, racing the year boundary, or non-idempotent retries. The map groups them.

• Reaching for nextval(). A Postgres sequence is lock-free precisely because it does not roll back; a failed finalization leaves a permanent hole. It is the single most common cause of “gap-free” violations. Use a counter row or advisory lock instead.
• Assigning the number outside the finalization transaction. If you grab the next number, then commit the invoice in a separate transaction, a crash in between burns a number with no document attached. Keep the increment and the status flip in one transaction.
• Sharing one global counter across legal entities. Numbering must be per issuing entity. A single global counter both leaks one entity’s volume to another and usually violates the requirement that each entity’s series be independent and gap-free.
• Forgetting the year-boundary race. The first two finalizations after midnight on January 1 can race to create the new-year row. Use INSERT ... ON CONFLICT DO NOTHING to make row creation idempotent, then increment.
• Non-idempotent retries. A redelivered finalization message that re-increments the counter creates a gap (the first number is now attached to nothing useful) or a duplicate. Always check whether the invoice already carries a number before assigning one.
• Mixing credit notes into the invoice series. Credit notes need their own gap-free series. Sharing the invoice counter makes both series non-contiguous from each authority’s point of view; key the counter by document_type.

Frequently Asked Questions

Why can invoice numbers not simply use a database sequence? Because sequences leave gaps on rollback, and many jurisdictions require the numbering to be gapless. The allocation has to be tied to the transaction that issues the invoice.

Should numbering restart each year? It is common and acceptable in most jurisdictions, usually with the year in the prefix. Whatever the scheme, it must be documented and applied consistently, since changing it mid-year raises questions.

Do credit notes share the invoice sequence? No. They generally need their own gapless sequence, and interleaving them breaks the gapless property of both.

What happens if an invoice is issued in error? It is cancelled with a credit note rather than deleted, and its number remains used. A missing number is exactly what an audit looks for.

Related

• Invoicing & Credit Notes
• Revenue Recognition (ASC 606 / IFRS 15)
• Building an Immutable Double-Entry Billing Ledger in PostgreSQL
• Building Idempotent Webhook Handlers in Node.js
• Tax, Compliance & Revenue Recognition