Gapless invoice numbering in PostgreSQL without a sequence

A PostgreSQL sequence cannot give gapless invoice numbers: aborted transactions, crashes and INSERT ... ON CONFLICT all consume values. Keep one counter row per tenant and series and increment it with UPDATE ... RETURNING in the issuing transaction. The row lock queues issuers, numbers follow commit order, and a rollback returns the number.

CREATE TABLE number_counters (
  tenant_id uuid   NOT NULL,
  series    text   NOT NULL,
  last_seq  bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (tenant_id, series)
);
-- documents, reduced to the columns this pattern needs
CREATE TABLE documents (
  id              uuid        PRIMARY KEY,
  tenant_id       uuid        NOT NULL,
  series          text        NOT NULL,
  seq_no          bigint      NOT NULL,
  idempotency_key text        NOT NULL,
  issued_at       timestamptz NOT NULL DEFAULT clock_timestamp(),
  UNIQUE (tenant_id, series, seq_no),
  UNIQUE (tenant_id, idempotency_key)
);

-- once, when the series is created
INSERT INTO number_counters (tenant_id, series) VALUES ($1, $2);

-- per issue, READ COMMITTED (the default)
BEGIN;
UPDATE number_counters SET last_seq = last_seq + 1
WHERE tenant_id = $1 AND series = $2
RETURNING last_seq;          -- zero rows: no such series, ROLLBACK
INSERT INTO documents (id, tenant_id, series, seq_no, idempotency_key)
VALUES ($3, $1, $2, $4, $5); -- $4 is the last_seq returned above
COMMIT;                      -- on any error, including 23505: ROLLBACK

#Why does it work?

• The UPDATE holds its row lock until commit or rollback. The next issuer of that series waits, then increments the committed value. A later failure rolls the increment back, so the number is free again.
• Under READ COMMITTED the waiter re-reads the committed row and continues. Under REPEATABLE READ it fails with SQLSTATE 40001 and the transaction must be retried.
• A retried request hits UNIQUE (tenant_id, idempotency_key) and fails with 23505: roll back and read the stored document. A sequence would burn a number on that retry, because nextval is never rolled back and ON CONFLICT calls it before detecting the conflict.
• now() is the transaction start, so a waiter could carry an earlier date than the number before it. The clock_timestamp() default runs at insert, after the lock, so numbers and dates rise together.

#When should you not use it?

• One issuer at a time per series: keep the transaction short, with no PDF rendering or gateway calls before commit.
• Whether a series must be gapless is a jurisdiction question. Ask the accountant and make it a per-series setting; where gaps are allowed, a sequence avoids the lock.