Skip to content

ADR-0008: Receipt numbering under PgBouncer

  • Status: Accepted
  • Date: 2026-05-02
  • Decider(s): Theo (SA), with defaults pre-confirmed by Stefan
  • Closes: #91

Amended 2026-05-04 — counter-cap alarm dropped per Stefan's review of OQ-N-2 (unnecessary at ~50 orders/year). Format YYYY-NNNN is documented to extend gracefully past 9999 (e.g. 2026-10000) — no DB CHECK, no warning, no error. The BIGINT last_n column already carries the headroom. See Change log below for affected sections. Tracked in #94.

Context

receipts.md closes the layout, the renderer, the language rule, and the persistence model — but leaves numbering as "decision lockable in implementation; not architecturally significant." build-plan.md Phase 3 and issue #43 (C1 — receipt numbering) flag it as architecturally significant under PgBouncer:

PgBouncer constraint — receipt numbering must NOT use nextval() over a session-scoped advisory lock. C1 (#43) calls this out; design must be a row-locked counter or an INSERT ... RETURNING against a receipt_numbers(stringer_id, year, last_n) table.

ADR-0007 also assumes the one Order ↔ one receipt number invariant (re-emit reuses the original number). Without a locked numbering scheme, that invariant is undefined.

The PgBouncer constraint, restated explicitly: per project_pgbouncer_constraint.md, DATABASE_URL routes through PgBouncer at 127.0.0.1:6432 in transaction-pooling mode. That pooling mode invalidates:

  • Postgres advisory locks (session-scoped; not held across the pool's connection multiplexing).
  • SET LOCAL for the session (lost between transactions).
  • LISTEN/NOTIFY (the listening connection isn't yours for long).
  • Per-session sequences with setval/nextval semantics that depend on session continuity.

A naive CREATE SEQUENCE receipt_seq would technically work (sequences are connection-pool-safe in Postgres) but produces a single global sequence across all stringers — Stringer A's commits advance Stringer B's number. That doesn't match the receipts-page format expectation of "stringer X's 4th receipt of 2026."

Stefan's pre-baked defaults (carried in here as locked):

  • Per-stringer counter table with UPDATE ... RETURNING in the same transaction as the receipt-emission INSERT.
  • Format YYYY-NNNN per stringer per year.
  • Gap on aborted transaction = acceptable; document.

Options

Counter mechanism

  • (M-1) Per-(stringer_id, year) counter row + INSERT ... ON CONFLICT DO UPDATE SET last_n = last_n + 1 RETURNING last_n in the receipt-mint transaction. (chosen, Stefan default) PgBouncer-safe (no session state). Stringers don't contend with each other (different rows). Same-stringer concurrent mints serialize on the row lock — fine at our concurrency.
  • (M-2) Single nextval() sequence + per-stringer-per-year offset table. Two writes per mint; race-prone on the offset; doesn't actually solve the per-stringer-isolation requirement. Rejected.
  • (M-3) Application-side counter (in-process atomic). Lost on restart; broken under multi-instance scale-out (V2 is single-instance, but the multi-instance possibility shouldn't be foreclosed). Rejected.
  • (M-4) Postgres advisory lock + counter table. The advisory lock is session-scoped → fights PgBouncer. Rejected.
  • (M-5) UUID-based "number" (no monotonic counter). Defeats the human-readable-receipt-number expectation. Rejected.

Format

  • (F-1) YYYY-NNNN (chosen). Year prefix; zero-padded to ≥4 digits counter, no upper cap — the format silently extends to 5+ digits if a stringer ever exceeds 9999/year (see Format and Failure modes). Reads cleanly on the printed receipt and the email subject.
  • (F-2) NNNN (no year). Counter never resets; the receipt-page header doesn't carry the year. Loses the "year + N" clarity.
  • (F-3) YYYY-{stringer_short}-NNNN. Per-stringer prefix in the visible number. Useful for multi-stringer admin views; but the number is per-stringer already (the row), so the prefix is redundant. Defer if Stefan ever wants it.

Mint timing

  • (T-1) On strung_at set, in the same transaction. (chosen) Receipt is finalized at Strung (ADR-0002 R5); the number is minted exactly when the receipt becomes a receipt.
  • (T-2) On the Order INSERT (draft creation). Drafts that never get strung waste numbers. Worse: a draft minting 2026-0001 and then a non-draft Order minting 2026-0002 produces an out-of-order receipt history.
  • (T-3) On the first PDF render request. Couples the number to the request flow (e.g. a re-render before the first email send produces a number with no corresponding email). Rejected.

Year-boundary semantics

  • (Y-1) New row materializes for the new year on first mint of that year. (chosen) The INSERT ... ON CONFLICT DO UPDATE pattern handles it natively. Year 2027's first mint creates the (stringer_id, 2027) row with last_n = 1.
  • (Y-2) Pre-create year rows at midnight Jan 1. Adds a cron; no benefit over lazy materialization at our scale.

Gap behavior on aborted transaction

  • (G-1) Accept gaps; document. (chosen, Stefan default) A transaction that mints 2026-0007 then aborts on a downstream constraint leaves last_n = 7 but no receipt with that number. Next mint produces 2026-0008. Receipt history has a one-row gap. Acceptable: receipts are not legal-archival (per ADR-0002 §Costs we accept) and gap-free numbering is a Swiss-VAT-bookkeeping requirement that doesn't apply here (Stefan's hobby scale).
  • (G-2) Compact / re-issue gaps via a recycle table. Defeats monotonicity; a re-used number could collide with a customer's screenshot. Rejected.
  • (G-3) Reserve number only after commit. Two-phase write; needs an ID before commit anyway (the receipt PDF embeds the number). Rejected.

Decision

receipt_counters schema

receipt_counters
  stringer_id   BIGINT NOT NULL REFERENCES stringers(id)
  year          INTEGER NOT NULL CHECK (year BETWEEN 2020 AND 2200)
  last_n        BIGINT NOT NULL CHECK (last_n >= 0)
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
  PRIMARY KEY (stringer_id, year)

Indexes: the PK is the only access path. No additional indexes.

Tenancy: per-stringer-scoped via stringer_id. The chokepoint applies the predicate stringer_id = current_stringer_id automatically. Reads are therefore implicitly scoped (a stringer can never see another stringer's counter), and writes are gated by the chokepoint's write rule (stringer_id = :me).

The chokepoint only sees receipt_counters from inside the receipt-mint transaction; handlers don't read the table directly. Stefan's "what's my next receipt number" UI question is answered by SELECT max(receipt_number) FROM orders WHERE stringer_id = :me (ordered text — fine since the format is sortable), not by reading the counter directly.

Order.receipt_number column

Added to Order (per data-model.md, where receipt numbering is currently TBD):

orders.receipt_number  TEXT NULL  -- 'YYYY-NNNN'; NULL until strung_at set

Constraint: UNIQUE (stringer_id, receipt_number) WHERE receipt_number IS NOT NULL. Partial unique index; supports the per-stringer per-year invariant + tolerates the NULL state.

The format is intentionally TEXT (not (year INT, n INT)): receipts are rendered with the formatted string; storing it computed avoids reformat-drift. The pieces are recoverable via SUBSTR / SPLIT_PART if reporting ever needs them.

Mint pattern (the load-bearing SQL)

In the same transaction as the Order's strung_at set (per ADR-0007's before_flush / after_flush hooks):

WITH bumped AS (
  INSERT INTO receipt_counters (stringer_id, year, last_n)
       VALUES (:stringer_id, :year, 1)
  ON CONFLICT (stringer_id, year)
  DO UPDATE SET last_n = receipt_counters.last_n + 1,
                updated_at = now()
  RETURNING last_n
)
UPDATE orders
   SET receipt_number = (SELECT
         to_char(:year, 'FM0000') || '-' || to_char(last_n, 'FM0000')
       FROM bumped)
 WHERE id = :order_id;

Atomicity: both writes commit or both abort. The UPDATE orders references the bumped CTE → a single statement, single round-trip, single transaction.

Concurrency: two concurrent mints for the same (stringer_id, year) serialize on Postgres's row lock for the conflicting INSERT. Both transactions get a number; one gets N+1, the other N+2. PgBouncer's transaction-pool sees this as two normal transactions; row-lock semantics are connection-pool-safe (locks released at commit, which is the pool's checkpoint).

Concurrency across stringers: different rows → no contention.

Concurrency across years: new year boundary races resolve via the ON CONFLICT clause. Two transactions both inserting (stringer_id, 2027) with last_n = 1 resolve to one INSERT (winner) + one DO UPDATE (loser, becomes last_n = 2). Both commit cleanly.

Order.receipt_number lifecycle

Order state receipt_number
DRAFT NULL
ORDERED NULL
STRUNG minted ('YYYY-NNNN') — set by the mint pattern above
RETURNED / PAID unchanged (carries the STRUNG-time value)

Re-emit (per ADR-0007): does not mint a new number. The receipt_number is UNIQUE-constrained per stringer; the after_flush re-emit hook reads the existing value and renders the receipt with it.

Date-clear (per ADR-0007): clearing strung_at does not clear receipt_number. The number stays on the Order. Re-setting strung_at does not re-mint (the validator checks receipt_number IS NULL before invoking the mint pattern). This preserves the "one Order → one number forever" invariant.

If an Order needs a brand-new receipt number (a re-string), that's a new Order per ADR-0007 — which lands a fresh mint via the same pattern.

Admin override: the only way to clear receipt_number is an admin bypass-edit, audited per ADR-0007. Not exposed in normal UI.

Format

YYYY-NNNN with to_char(year, 'FM0000') and to_char(n, 'FM0000'):

  • 2026-0001 (first receipt of 2026 for this stringer)
  • 2026-0042
  • 2026-9999 (last receipt before the format extends past 4 digits)
  • 2026-10000 (graceful extension — see below)

No upper cap. The NNNN slot is documented as ≥4 digits zero-padded (not exactly 4). to_char(n, 'FM0000') emits the natural width without truncation, so once last_n crosses 9999 the rendered field becomes 5 chars (10000), then 6 (100000), etc. The receipt format silently extends; nothing breaks. There is no DB CHECK constraint on last_n, no warning, no alarm, no admin notification when the cap is approached or crossed.

This is graceful-by-design. Stefan's V1 totaled 329 client orders + 416 self orders across all years. At ~50 orders/year per stringer, 9999/year is roughly 200 years of headroom — the alarm is operational dead weight. If a stringer ever stringed 10,000 rackets in one year (cosmically unlikely at our scale), 2026-10000 is a perfectly valid receipt number and the system continues uninterrupted. Future maintainers: do not reintroduce a cap; the BIGINT last_n carries the headroom and the format spec accepts ≥4 digits, not exactly 4.

(Amended 2026-05-04 — the previous wording said "max per stringer per year" with an alarm at 9000; that alarm and cap framing are dropped per Stefan's review of OQ-N-2.)

Failure modes

Mode Behavior Mitigation
Transaction aborts after mint, before commit. last_n advance is rolled back too (it was inside the same transaction). No gap. None needed; transactional INCREMENT is correct by construction.
Transaction commits the counter advance but the Order's strung_at set was rolled back by a later constraint. Cannot happen — both writes are in the same transaction; rollback rolls back both.
Two transactions interleave on the same (stringer_id, year) row. Postgres serializes via row lock; both succeed with consecutive last_n. Inherent.
Year boundary race (two transactions both insert the new year). ON CONFLICT resolves; both succeed. Inherent.
last_n exceeds 9999. Format renders as YYYY-10000, YYYY-10001, etc. (5+ chars in the NNNN slot — to_char(n, 'FM0000') emits the natural width without truncation). Receipts continue to mint uninterrupted. None — graceful by design. No alarm, no warning, no DB CHECK. The schema does not change (BIGINT headroom; the format spec is "≥4 digits", not "exactly 4").
Manual SQL UPDATE on receipt_counters (admin tinkering). The chokepoint refuses unscoped writes; an admin can still use a direct DB session. Discipline-only. Documented; not enforced.
Restore from backup with stale last_n. The next mint may collide with an existing Order.receipt_number (the partial unique index will refuse). The restore runbook (Atlas) instructs the operator to SELECT MAX(...) FROM orders ... GROUP BY stringer_id, year and re-set receipt_counters.last_n accordingly. Documented in receipts.md follow-up.

Migration from V1

V1 used simple sequential IDs (1..329 for client orders; presumably similar for self orders). V2 has two reasonable paths:

  • (P-1) Continue from V1 (no per-year reset). Migrated V1 orders carry receipt_number = '2025-0001'..'2025-0329' (or the original year per the V1 dated row). New V2 receipts of 2026 start at 2026-0001.
  • (P-2) Backfill V2 with YYYY-NNNN derived from each V1 row's actual year. Same as P-1 if V1 dates are accurate per row. Chosen. Each migrated Order gets receipt_number set to {year_of_strung_at}-{seq_within_that_year}, where the seq is computed by the migration ETL ordered by strung_at ascending within (stringer_id, year).

The migration is the only code that ever sets receipt_number without going through the mint pattern. After migration, receipt_counters.last_n is set to the highest seq per (stringer_id, year) so the next live mint continues correctly. Vera owns the ETL; this ADR commits the format and the post-migration counter state.

Required tests (this ADR mandates them)

  1. Schema reflection. receipt_counters exists with the documented PK + checks. orders.receipt_number is TEXT, NULL by default, with the documented partial unique constraint.
  2. Mint pattern atomicity. A failing constraint downstream of the mint rolls back the last_n advance. Verified by triggering a CHECK violation on Order after the mint.
  3. Two concurrent mints, same stringer, same year, same connection-pool. Both succeed; numbers are consecutive (N and N+1); no deadlock.
  4. Two concurrent mints, different stringers. Both succeed independently; no contention.
  5. Year boundary race. Two concurrent first-mints for (stringer_id, 2027) both succeed with 2027-0001 and 2027-0002.
  6. Re-emit reuses the number. Setting strung_at, then editing pricing → Order.receipt_number unchanged; receipt PDF carries the same number.
  7. Date-clear preserves the number. Clearing strung_at does not clear receipt_number; re-setting strung_at does not re-mint.
  8. Re-string mints a new number. New Order created via the copy-last path → new mint → next sequential number for the year.
  9. Tenancy. A stringer querying receipt_counters sees only their own row (chokepoint applies). Direct SQL bypassing the chokepoint is the admin's discipline.
  10. Format. Output is exactly YYYY-NNNN with zero-padding for both fields.
  11. Migration backfill. Run the V1→V2 ETL on a fixture; assert each migrated Order has the right YYYY-NNNN derived from strung_at's year + within-year sequence; assert receipt_counters.last_n matches the highest seq per (stringer_id, year).
  12. Graceful 5-digit extension. With last_n = 9999, mint one more receipt; assert the rendered number is YYYY-10000 (5 chars in the NNNN slot, no truncation, no error). Asserts the format-extends-gracefully invariant; protects against a future maintainer adding a last_n <= 9999 CHECK or a 4-digit format guard.

Consequences

Good

  • PgBouncer-safe by construction. No advisory locks, no SET LOCAL, no LISTEN/NOTIFY. Plain INSERT-ON-CONFLICT in the request transaction.
  • Per-stringer isolation. Stringer A's numbering does not depend on or affect Stringer B's. Concurrent mints across stringers don't contend.
  • Per-year reset is automatic. No cron, no special handling at midnight. The ON CONFLICT does it.
  • Single-statement mint. One round-trip from FastAPI → Postgres for the bumped+UPDATE pair (CTE). Sub-millisecond at any realistic load.
  • Re-emit invariant lives at the schema. The partial UNIQUE index on (stringer_id, receipt_number) enforces the "one Order → one number" invariant structurally; the application layer cannot accidentally mint a duplicate.
  • Migration shape is well-defined (P-2 — derive year from strung_at per Order, sequence within year per stringer). Vera lifts it directly.
  • Failure modes are all benign — gap on abort is the only one, and it's documented + acceptable per ADR-0002.

Costs we accept

  • Gaps on aborted transactions. Stefan accepts; not a Swiss-VAT-bookkeeping issue. Documented in MR description and in receipts.md.
  • last_n is a BIGINT for headroom; the format spec is "≥4 digits" (not exactly 4). A stringer who somehow exceeds 9999 receipts in one year produces a 5+-digit NNNN slot — receipts continue to mint uninterrupted. No alarm, no cap, no DB CHECK. At our actual scale this is a never-event; documented explicitly so future maintainers don't reintroduce a cap.
  • Counter row contention for very high single-stringer concurrency. At our actual scale (a stringer mints maybe 1-5 receipts/day; concurrency is essentially zero) this is irrelevant. If V3 ever scales to many concurrent mints per stringer, the row lock becomes the bottleneck — at that point switch to a Postgres gen_random_uuid()-style ID + a separate display-number derivation. Defer.
  • Manual restore from backup needs counter-resync. One SELECT-MAX statement; documented in Atlas's restore runbook.
  • Order.receipt_number storage is denormalized (the (year, n) pieces are derivable). Stable receipts (per ADR-0002) need the formatted string to be authoritative anyway; storage cost is < 10 bytes/row.
  • Admin override to clear receipt_number is the only way to break the invariant. Audited per ADR-0007.

Open questions (Stefan-confirm — most resolved 2026-05-04)

  1. Format YYYY-NNNN vs. YYYY-{stringer_short}-NNNN — confirmed without stringer prefix. The prefix is one-line additive if multi-stringer admin views ever want it.
  2. Counter-cap alarm threshold — resolved: no alarm. Stefan deemed unnecessary at his scale (~50 orders/year). The format is documented to extend gracefully past 9999 instead. See Format, Failure modes, test 12.
  3. Migration path P-2 (derive year from strung_at) vs. P-1 (continue from 330 ignoring V1 years) — confirmed P-2 since V1 dates are present and Stefan wants per-stringer-per-year format. P-1 is one ETL change away if Stefan prefers continuity over format-purity.
  4. Counter row hard-cap. Confirmed: no DB-level cap. The format spec is "≥4 digits, no upper cap"; the BIGINT carries the headroom; a CHECK on last_n <= 9999 is explicitly not added. Future maintainers: do not reintroduce.

Change log

Date Change Reason
2026-05-02 Initial accepted version (counter-cap alarm at 9000; format described as "max 9999/year"). —
2026-05-04 Amendment — counter-cap alarm dropped; format documented as "≥4 digits, no upper cap, extends gracefully". Updated: top-of-file amendment note, format-option (F-1), Format section, Failure-modes table (counter-cap row reframed), required tests (test 12 replaced — alarm test → graceful-extension test), consequences (costs row reframed). Schema and mint pattern unchanged (BIGINT last_n already had the headroom). Stefan's resolution of OQ-N-2; tracked in #94.

Cross-references

  • Issue #43 (C1 — receipt numbering, PgBouncer-safe sequence) — this ADR closes its architectural side; Pax's implementation issue tracks the actual code.
  • ADR-0002 — receipt design that this numbering serves.
  • ADR-0007 — the one-Order-one-number invariant; mint timing at strung_at-set; re-emit reuses; re-string mints anew.
  • docs/architecture/receipts.md — receipts page; updates to drop the "decision lockable in implementation" note and link here.
  • docs/architecture/data-model.md — Order column documentation; receipt_number and receipt_counters table to be added.
  • project_pgbouncer_constraint.md — the platform constraint that drove this design.
  • Vera's migration brief (V1→V2 ETL) — lifts P-2 directly from this ADR.
  • Pax's implementation issue (Alembic migration for receipt_counters + Order.receipt_number; mint helper; integration into ADR-0007's after_flush hook) — to be opened after this ADR merges.