The Document Number Is Reserved Before the PDF Exists

The hard part of document numbering is not incrementing an integer.

It is deciding what happens when the integer is reserved, rendering starts, and the render fails.

PostgreSQL sequences are built for speed. They are not built for legal numbering. A sequence advances even if the surrounding transaction rolls back. For most applications that is fine. For invoices and board records, a gap is not invisible. If number 41 exists and number 43 exists, someone will ask what happened to 42.

I needed a numbering system that could do three things at once: serialize per entity, type, and year; let different sequences run in parallel; and preserve a reason when a number is skipped.

That became the two-phase allocator in src/documents/numbering.ts.

Phase one reserves the next number. The allocator locks the tuple (entity_id, document_type, year) with pg_advisory_xact_lock, checks for the oldest unclaimed gap, and only then advances the high-water mark. It writes the reservation to pending_allocations with a TTL.

Phase two either finalizes the reservation against a document row or releases it into sequence_gaps.

The core reservation shape is small:

await acquireAllocatorLock(c, entityId, documentType, year);

let reservedNumber = await tryReclaimOldestGap(c, entityId, documentType, year);
if (reservedNumber === null) {
  reservedNumber = await computeNextHighWater(c, entityId, documentType, year);
}

await c.query(
  `INSERT INTO pending_allocations
     (id, entity_id, document_type, year, reserved_number,
      reserved_by_api_key_id, reserved_at, expires_at, metadata)
   VALUES ($1, $2, $3, $4, $5, $6, now(),
           now() + ($7::text)::interval,
           $8::jsonb)`,
  [allocationId, entityId, documentType, year, reservedNumber, apiKeyId, ttl, metadata],
);

The advisory lock boundary matters. FZE invoices for 2026 serialize against other FZE invoices for 2026. LLC board resolutions do not wait on them. A document type has its own counter space because letterhead #1 and compliance letter #1 can both be real first documents in their own family.

The part I got wrong early was the SQL shape for reclaiming a gap.

The first version used an update with a subquery and a limit against the same table. PostgreSQL flattened the plan in a way that ignored the limit and updated every eligible row. That is the kind of bug that looks impossible until you inspect the affected rows. The fixed version uses a CTE target so the candidate set is materialized before the update runs.

const res = await c.query(
  `WITH target AS (
     SELECT id FROM sequence_gaps
      WHERE entity_id = $2 AND document_type = $3 AND year = $4
        AND reclaimed_at IS NULL
      ORDER BY reaped_at ASC
      LIMIT 1
      FOR UPDATE SKIP LOCKED
   )
   UPDATE sequence_gaps
      SET reclaim_allocation_id = $1, reclaimed_at = now()
    WHERE id IN (SELECT id FROM target)
    RETURNING id, gap_number`,
  [null, entityId, documentType, year],
);

That one CTE is the difference between reclaiming one documented gap and mutating the whole backlog.

The reaper is the other half of the design. A reservation can expire before it is attached to a document. Maybe Puppeteer failed. Maybe the sidecar was down. Maybe storage rejected the upload. The service cannot just delete the reservation and pretend the number never happened. It moves the number to sequence_gaps with reason = 'reaper_swept'.

There are also explicit reasons: abandoned reservation, admin documented gap, manual void. That vocabulary is important because auditors do not need a philosophical explanation. They need a row that says why the number did not produce a document.

What surprised me was how much of the design was about not being too clever. I could have hidden this behind one nextDocumentNumber() helper and let failures be retried. That would make the happy path smaller and the audit story weaker. The split between pending allocation and sequence gap is more verbose, but the data tells the truth.

The same pattern shows up in the tests. The reaper-race gate is not a decorative concurrency test. It exists because the allocator is only correct under pressure: concurrent reservations, expired rows, reclaimed gaps, and failed finalization must all preserve one property. No two documents get the same number in the same sequence, and no missing number lacks a reason.

The document number is reserved before the PDF exists because rendering is fallible. The audit trail has to survive that fallibility.