2026-09-08

2026-09-08 — sprint/artifact numbering needs a database-authoritative allocator, not a file counter

Status: research/prompt for a future session. Written during a multi-worktree consolidation (/consolidate-worktrees, session log this repo doesn't track directly — see the commits citing "2026-09-08 multi-worktree consolidation" for the concrete instances). Not a Gate 1 doc; this is the entry point for whoever picks the actual fix up. Operator directive, verbatim in substance: "the database is the master. The UUID is the master. ID."

What was found, concretely

Consolidating 9 scattered worktrees of this repo surfaced three real sprint-number collisions in one pass: two independent docs each claiming 4.54, 4.60, and 4.61 across different branches. One of the colliding docs had already been renumbered once before (4.56 → 4.60) the same day it was drafted, and its own header note already names the failure class from docs/POSTMORTEMS.md: bug-id-allocated-twice-across-branches — "a number allocated from a listing the allocator holds a stale view of." This is not a new failure mode; it is a known, named, recurring one that has never been fixed at the root.

The two allocators that exist today, and why both fail under concurrency:

1. `docs/reference/sprint-number-registry.txt` — a single line, a flat-file monotonic counter tools-artifact-scaffold reads and increments. Every worktree has its own copy. Two sessions in two worktrees read the same number, both increment their own local copy, both commit — collision, discovered only later (if at all) at merge time. 2. `sprint_identity` (corpus_sourcecode database, migration crates/operations/control-plane/migrations/0016_story_harness_language.sql and later amendments) — genuinely collision-proof by design: UNIQUE (number) WHERE is_primary, an append-only admit trigger (sprint_identity_admit, forbids changing number/slug after creation), require a work_product(kind='sprint', revision=1) row to exist first. But nothing writes to it. Checked directly: SELECT number, slug FROM sprint_identity returns exactly 2 rows (4.63, 5.1) against 60+ real sprint files. story-new, compile-story, tools-artifact-scaffold — none of them create a work_product/sprint_identity row. The one table built to solve this problem is almost completely unused.

Why file-based numbering cannot be fixed by itself

Any git-tracked flat-file counter has the same structural problem regardless of how carefully it's incremented: two worktrees/branches/sessions working concurrently each see a consistent LOCAL view and each believe their read was the latest. Only a system with a real transactional uniqueness constraint — i.e., the database, via INSERT ... UNIQUE or equivalent — can make two concurrent claims on the same number fail loud instead of silently both succeeding until someone notices at merge time (which is what happened here, three separate times, in a single consolidation pass).

What the fix needs to do

1. Build the missing writer. A sprint-new-equivalent (or an extension to story-new) that, before writing the markdown file, does the full birth sequence: repo_artifact (or reuses the doc's own eventual repo_artifact row) → work_product(kind='sprint', revision=1) → sprint_identity(number, slug, artifact_id). The INSERT on sprint_identity is the actual collision check — if it fails (UNIQUE (number) WHERE is_primary), the number was already taken and the tool refuses and picks/reports the next free one, transactionally, no race window. 2. Make the number itself derived, not chosen. Per the operator's own framing and precedent already established in sprint 4.65's Gate 1.5 round (this repo, docs/userstories/sprint-4.65-*.md, "per-product sequential numbering" decision): the UUID (sprint_identity.artifact_id or equivalent) is the real identity; the human-facing number is presentation, ideally derived by MAX(number) + 1 computed inside the same transaction that inserts the new row, not read-then-write from a file two sessions can race on. 3. Retire (or clearly demote) `sprint-number-registry.txt` once the DB writer exists — keep it only as a cached/rendered view of the DB's current max, regenerated, never the source of truth, matching this repo's own established pattern for docs/reference/ lexicon.tsv and docs/reference/story-template.tsv (both explicitly "renderings of rows," per their own headers). 4. Backfill history. Once the writer exists, register every already-existing sprint doc's number into sprint_identity (a one-time migration script, not a manual sweep), so the uniqueness constraint actually has something to check new claims against. Until this runs, sprint_identity's near-emptiness means it can't catch a collision against the 60+ numbers that already exist only as files.

What this is NOT asking for

Not a redesign of the sprint-doc format, not a change to how Gate 1 planning works, not a new numbering scheme (decimal sprint numbers stay exactly as they are). Purely: move the allocation of the next number from "read a file, hope nobody else read it first" to "ask the database, which cannot lie about whether a number is taken."

Pointers for whoever picks this up

sprint_identity's actual schema and trigger.

sequence for work_item, though it doesn't touch sprint_identity/work_product at all today; B-092 through B-097 this session already hardened this function's other bugs — read those bug records first, this function has a track record of untested first-use surprises).

failure mode this fixes, with (presumably) prior incidents to read for context on why it was named in the first place.

the three concrete collisions found this pass, if a regression test wants real fixture data.

All writing