[finding] a failed insert permanently burns its autonumber (TK-0001 → fail → TK-0003) — is a gapless series in scope at all?

[baozhoutao]

Recording an observation for the platform to rule on — this may well be an accepted trade-off, but it is currently neither documented nor decided anywhere I could find.

An insert that fails for a reason unrelated to the autonumber still consumes the number that row had reserved. Measured on both dialects, same shape:

sqlite:   first=TK-0001  → insert fails (duplicate `slug`)  → next=TK-0003
postgres: first=TK-0001  → insert fails (duplicate `slug`)  → next=TK-0003

TK-0002 exists in no row and will never be issued. Any rejected write — a unique violation on another field, a validation rule, a beforeInsert hook throwing — leaves a permanent hole in the business identifier.

This is not multi-tenant-specific (a failed insert in tenant A does not disturb tenant B — verified), and it is the ordinary behaviour of database-native sequences. But ObjectStack's autonumber is not a native sequence: it is a counter this driver owns and updates itself, and the driver's own docs frame the value as a business identifier —

packages/drivers/driver-sql/src/sql-driver.ts:4523 — "an autonumber is an immutable business identifier"

For a surrogate key, gaps are free. For a business identifier — a contract number, an invoice number, an order number — gaps are sometimes a compliance problem, and are frequently the first thing a customer's finance team asks about.

The decision

Three coherent answers, and the platform should pick one rather than leave it implicit:

1. Gaps are fine and guaranteed-unique-only is the contract — then say so in the autonumber docs, so nobody promises a customer a gapless series.
2. Gaps are worth avoiding on the cheap path — reserve the number only after the row is known to be insertable, or return it on a clean rejection.
3. Gapless is a supported option — an opt-in mode that pays the concurrency cost (serialized issuance) for objects that need it.

Related: #8269 is the pathological version of the same mechanism — there the counter advances and the whole batch fails, so a fresh tenant's numbering can start at TK-0004.

Environment

Probed via SqlDriver directly against postgres:16 and file-backed sqlite, branch claude/multi-org-service-testing-a75937, 2026-08-12. Probe files were temporary and have been removed; the two lines above are the complete result.

[os-zhuang]

Evidence for the findings round from the drivers seat. ⛔ Not a claim, no label changed, no grade — and ⛔ not an answer to the decision, which is a contract-shape question for the maintainer, not a lane call.

This card is well-posed and it is not a duplicate of #8269 — it is the same mechanism arriving from a second, independent direction, which is itself worth recording. Three things I can add, all verified on origin/main @ 289d04a.

① The "Related" line went stale about four minutes before this card was filed

"#8269 is the pathological version of the same mechanism — there the counter advances and the whole batch fails, so a fresh tenant's numbering can start at TK-0004."

#8269 merged at 02:53:05Z as ecb39ea (fix(driver-sql): run speculative sequence statements under a savepoint); this card was filed 02:57:11Z. The pathological version is gone: the cold cross-tenant race no longer produces failed attempts at all, so it no longer burns numbers. What survives is exactly what this card describes — a genuinely rejected write burning the number it reserved — which is the ordinary case, not the pathological one. The decision this card asks for is now the whole of the remaining question rather than a subset of it.

② The citation has drifted — the quote is real, the line is not

sql-driver.ts:4523 is now probe.suffix,. The sentence "an autonumber is an immutable business identifier" is live at sql-driver.ts:4754. (main moves fast enough that any line number in a card outlives its accuracy by hours — worth pinning by phrase.)

③ The mechanism, measured rather than inferred

The counter advance is not merely "not rolled back" — it is committed by a different transaction than the caller's insert. getNextSequenceValue runs runner.transaction(...) over parentTrx ?? this.knex, so with no parent transaction the reservation commits independently and the caller's later failure cannot reach it. That is why option 2 ("reserve only after the row is known to be insertable") is a transaction-ordering change rather than a local tweak — and it now has to compose with the savepoint structure #8269 just introduced at both speculative sites.

For the record on scope: I explicitly excluded this question from #8269's dispatch and told that dev to file separately if it judged the gap behaviour defective. It declined to file, reasoning the parent card already described it — so this card is the first place the question stands on its own. That was the right outcome; it just took the reporter, not the implementer, to get it there.

⛔ I am not proposing an option. Note only that all three of the listed answers change either documented contract or accepted behaviour, which puts this outside anything a lane seat should settle.

---

Generated by Claude Code

[hotlong]

Findings-round triage: promoted finding → needs-user-decision (stays domain:drivers; driver-sql is outside the memory/mongodb investment freeze).

Rationale: the premise is fresh and verified (drivers-seat comment above re-checked all three facts on origin/main @ 289d04a — #8269's merge removed the pathological branch, so this card now carries the WHOLE remaining question, not a subset). All three listed answers change documented contract or accepted behaviour, which places the choice outside the delegated-adjudication lane by its own boundary test — and the drivers seat has independently said the same. This is a decision waiting to be made; parking it as a held finding would misstate its state.

Four-prism block (decision aid, not a substitute for the ruling):

1. Platform long-term coherence — leaving the gap behaviour implicit invites someone to promise a gapless series later; any of the three options closes that, option 1 with the least new surface. Option 2 is a transaction-ordering change that must now compose with driver-sql: first concurrent autonumber insert from two tenants fails on Postgres (25P02) — the sequence-row race handler runs inside an already-aborted transaction #8269's savepoint structure at both speculative sites — real complexity for a partial guarantee (it narrows gaps, it cannot eliminate them: a post-reservation crash still burns a number).
2. Measured business pull — zero today: this arrived from a seat's probe, not a user; no deployment has reported a gap complaint. Finance-grade gapless series (invoice/contract numbering) is a plausible future ask but is not being asked now.
3. AI-agent error-resistance — an explicitly documented "unique, monotonic, NOT gapless" contract is the shape an AI app-author cannot mis-promise; today's undocumented behaviour is exactly the ambiguity that gets confidently restated wrong.
4. Startup scope discipline — option 3 (gapless mode) is capability expansion with permanent concurrency-cost obligations; declare-and-maintain loses to document-and-defer with no pull.

Recommendation: option 1 now — one docs paragraph on the autonumber contract (unique + monotonic per scope, gaps possible on any rejected write) — with option 3 recorded as the restart condition ("first customer with a compliance-grade gapless requirement"), and option 2 rejected on complexity-vs-benefit. Prisms 2/3/4 point the same way; prism 1 tolerates it. Not self-adjudicated: options 1 vs 2 are a genuine contract-stance fork, and two seats have already deferred it to you.

A one-line ruling (1 / 2 / 3) unblocks: on 1, this becomes a docs-only card for the drivers lane; on 2/3, it needs a design card first.

---

Generated by Claude Code

[hotlong]

Maintainer ruling — Option 1: document the contract (unique + monotonic, gaps possible)

Ruled in a live PM session, 2026-08-13, accepting the triage seat's four-prism recommendation in full (maintainer verbatim: 「接受你的全部建议」). Recorded by the triage seat as a ruling record.

Ruling: option 1. The autonumber contract is stated explicitly in the docs: unique and monotonic per scope; NOT gapless — any rejected write may consume the number it reserved. That makes today's behaviour a decided property instead of an undocumented default, and stops anyone promising a customer a gapless series.

• ⛔ Option 2 rejected (reserve-after-insertable): it is a transaction-ordering change that must now compose with the savepoint structure at both speculative sites, and it only narrows gaps — a post-reservation crash still burns a number. Complexity for a partial guarantee.
• Option 3 recorded as the restart condition, not built: an opt-in gapless mode returns to the inbox when the first customer with a compliance-grade gapless requirement (invoice / contract numbering) appears. Until then it is capability expansion with permanent concurrency-cost obligations and zero pull.

Prism basis: zero measured pull (found by a seat probe, no user report); documenting is the smallest coherent close; an explicitly documented "unique, monotonic, not gapless" contract is precisely the shape an AI app-author cannot mis-promise; declare-and-maintain loses to document-and-defer with no pull.

Scope for the drivers lane — docs-only:

• State the contract where an author looks for autonumber behaviour, and name the cause plainly (a rejected write — unique violation on another field, a validation rule, a throwing beforeInsert — consumes its reserved number).
• Anchor by phrase, not line number: the existing "an autonumber is an immutable business identifier" sentence in the SQL driver is the natural neighbour, and the card notes line numbers there drift within hours.
• ⛔ No behaviour change, ⛔ no reservation-ordering work, ⛔ do not touch the savepoint structure.

State: needs-user-decision → pm:queue. Blocked-by: none (checked live — the pathological sibling merged 2026-08-13 and its removal is what left this as the whole remaining question).

---

Generated by Claude Code