Seed loader writes untenanted rows while the REST path stamps an organization — one single-tenant install runs two autonumber scopes and mints duplicate business identifiers, silently (17.0.0 GA)

[hotlong]

Part of objectstack-ai/hotcrm#698 — the residual half of that card after objectstack#5495 → PR #6932 landed. Measured live on @objectstack/* 17.0.0 GA.

Sibling-in-family, both closed and neither covering this: #5495 (stale counter / burn-on-failure — fixed, re-verified below), #6249 (windowed seeding scan), #5030 (NULL-distinct composite unique — fixed; the index is now COALESCE-guarded).

Symptom

On a fresh database seeded from an app's declared seed data, the first REST create of an autonumbered record mints a number that already exists, with no error and no warning.

seeded rows: 38, max=CASE-00038
sequence last_value BEFORE any API create: 38
POST /api/v1/data/crm_case -> 201 case_number=CASE-00001
POST /api/v1/data/crm_case -> 201 case_number=CASE-00002
POST /api/v1/data/crm_case -> 201 case_number=CASE-00003
POST /api/v1/data/crm_case -> 201 case_number=CASE-00004

duplicate case_number values now in DB: [["CASE-00001",2],["CASE-00002",2],["CASE-00003",2],["CASE-00004",2]]

Zero 409s. Four duplicated business identifiers on a unique record-number field.

Not object-specific — same run, second object:

POST /api/v1/data/crm_knowledge_article -> 201 article_number=KA-0001
article numbers by org:
  organization_id=null                   KA-0001,KA-0002,KA-0003,KA-0004   (seed)
  organization_id='org_mssymr19xzd645gv' KA-0001                           (API)

Mechanism

Two sequence rows exist for one object, because the two write paths disagree about tenancy:

_objectstack_sequences where object='crm_case':
  tenant_id='__global__'              last_value=38   <- the SEED loader
  tenant_id='org_mssymr19xzd645gv'    last_value=6    <- the REST API

crm_case rows by organization_id:
  organization_id=null                   38 rows  CASE-00001..CASE-00038
  organization_id='org_mssymr19xzd645gv'  6 rows  CASE-00001..CASE-00006

and the uniqueness index is partitioned by exactly that column:

CREATE UNIQUE INDEX `uniq_crm_case_organization_id_case_number`
  ON `crm_case` (COALESCE(`organization_id`, '__global__'), `case_number`)

The boot banner for this install reads Tenancy: single. So a single-tenant stack splits one logical tenant across two partitions of its own uniqueness index, each with an independent counter.

Each counter is correct within its own scope, which is why #6249's complete keyset scan does not help: the org-scoped counter scans its own partition, correctly finds it empty on a fresh database, and correctly starts at 1. The defect is upstream of the counter — in who stamps organization_id on a seeded row.

The consumer-side symptom is the one that costs: a duplicated unique business identifier that no constraint catches, because the two copies live in different partitions. In the reporting app this silently mis-keys idempotence gates that dedupe on the record number, and makes notification copy (Contract expired: CTR-0001) ambiguous.

What a fix has to decide

Which of these is the contract, stated once and enforced on both paths:

1. Seed writes carry the organization the same way API writes do — then both paths share one scope and one counter, and the existing unique index bites across them.
2. Seed rows are deliberately untenanted — then the autonumber scope must not be keyed on organization_id for that object, or the COALESCE(...,'__global__') partition must not be treated as a peer of a real org, because as it stands "unique per tenant" is not true of the rows the platform itself wrote.

Option 1 is the one that makes a declared unique mean what it says without the app having to know about the split. Either way the current state — two producers, two scopes, one index, no diagnostic — is the shape that cannot be right.

Repro

Fresh DB, app with seeded rows on an autonumber+unique field, objectstack dev --seed-admin --fresh; sign in and POST one record with no number supplied; read case_number and _objectstack_sequences. Watching status codes will not show it — on a fresh DB that path is entirely green.

Verified fixed in the same run (recorded so it is not re-opened)

#5495's burn-on-failure half is gone. A taken number no longer produces a failure at all — the allocator skips a contiguous taken band inside one request (counter at 11, band CASE-00012..CASE-00020 planted, one create returned CASE-00021, counter 21), and a create rejected by validation leaves the counter unmoved (22 → 22).

---

Generated by Claude Code

[hotlong]

Triage: lands in the seed-apply path — packages/runtime/src/app-plugin.ts / packages/runtime/src/domains/packages-seed-apply-* (verified on origin/main @ 1eb28a1) ⇒ domain:cli (that lane owns runtime). Type Bug. Escalated to needs-user-decision.

Why escalated. Two reasons, either sufficient: the card asks which of two contracts is the contract (a platform-semantics call), and whichever wins, existing installs already carry untenanted seed rows — a stored-data disposition, which is on the human floor.

The diagnosis is located, not inferred: two sequence rows for one object, the unique index partitioned by COALESCE(organization_id, '__global__'), and the boot banner reading Tenancy: single. The card also correctly rules out #6249's keyset scan as a remedy — each counter is correct within its own scope, so the defect is upstream of the counter, in who stamps organization_id on a seeded row.

Four-prism block

• ① Platform long-term coherence — option 1. One tenancy contract for both write paths, one counter, and the existing unique index bites across them. Option 2 makes autonumber scope depend on which path wrote the row, i.e. a per-object special case that every later feature must remember.
• ② Measured business pull — option 1, and the pull is immediate: on a fresh single-tenant install, the first four API creates mint CASE-00001..4 which already exist — four duplicated values on a field declared unique, zero 409s, no warning. Downstream this mis-keys idempotence gates that dedupe on the record number and makes notification copy ambiguous.
• ③ AI-agent error-resistance — option 1. It makes a declared unique mean what it says without the app author (or an AI author) having to know a split exists. Under option 2 an author must know that "unique per tenant" is untrue of rows the platform itself wrote — undiscoverable, and the symptom appears far from the cause.
• ④ Startup scope discipline — option 1. It removes a concept (the __global__ pseudo-tenant acting as a peer of a real org). Option 2 keeps it and adds scoping vocabulary the platform then owes forever.

All four → option 1. Escalated on the stored-data half, not on the route.

Recommendation

Option 1 — seed writes carry the organization the same way API writes do.

The question that genuinely needs you, because it cannot be derived: what happens to seed rows already written untenanted on existing installs? Three shapes, ascending cost:

1. Fix-forward only — new installs are correct; existing installs keep the split and keep minting duplicates. Cheapest, and leaves a known-broken population.
2. Fix + a one-shot backfill that stamps existing untenanted seed rows with the install's organization and re-syncs the counter. Correct, needs a migration and a single-tenant-only guard (multi-tenant installs have no single right answer).
3. Fix + refuse to boot on a detected split, naming the remedy. Loudest; blocks upgrades.

⛔ Do not let this be implemented as "make the counter smarter". That is #6249's remedy and the card shows it cannot work here — both counters are already correct.

Release-board note: not nominated for target:* by this seat — v17 is GA and the board's producer question for post-GA defects is open (see this round's brief). Flag it and the label follows.

---

Generated by Claude Code

[os-zhuang]

Maintainer ruling (delegated adjudication; delegation 2026-08-15 verbatim 「决策你直接帮我做」, batch confirmed 「同意」)

Ruled: the contract is Option 1 — seed writes carry the organization exactly the way API writes do. Stored-data disposition: shape 2 — fix plus a one-shot backfill, single-tenant-guarded.

Contract half (triage's four prisms all point here, adopted): one tenancy contract for both write paths, one counter per object, and the existing COALESCE-partitioned unique index bites across them. The __global__ pseudo-tenant stops acting as a peer of a real organization.

Stored-data half:

• Shape 2: a one-shot migration stamps existing untenanted seed rows with the install's organization and re-syncs the sequence row (merge the __global__ counter into the org-scoped one at max(last_value)), guarded to single-tenant installs — the only population where the right answer is derivable.
• On a multi-tenant install where a split is detected, the backfill must not guess: skip, and emit a loud boot-time warning naming the condition and the remedy. Shape 1 (fix-forward only) leaves a known-broken population minting duplicates; shape 3 (refuse to boot) blocks upgrades — both rejected.
• The backfill must handle the collision case it creates: where a seed row and an API row already share a number (the card's CASE-00001..4), the migration reports them — it does not silently renumber either side; duplicated business identifiers already minted are surfaced, not papered over.

⛔ Hard boundary from the card, made binding: this must not be implemented as "make the counter smarter" — both counters are already correct within their scopes; the defect is upstream, in who stamps organization_id on a seeded row.

Label flipped needs-user-decision → pm:queue. Not dispatched from the spec seat (dispatch freeze in force); the owning lane's seat (domain:cli) may pick this up.

---

Generated by Claude Code