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

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.

Step-by-Step Implementation

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.

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.

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

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

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

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