`sys_setting`'s declared row identity is unenforced on every `tenant` and `global` row — `user_id` is NULL there and SQL UNIQUE is NULL-distinct

[os-zhuang]

Found while implementing #8555 (which scopes this same index per organization). Filed unassigned and deliberately separate: #8555 is a pure relaxation, this one is a tightening, and the two need different migration ceremony. Same split #6417 made against #5839, and for the same stated reason.

The fact

packages/platform-objects/src/system/sys-setting.object.ts declares the object's row identity as

{ fields: ['namespace', 'key', 'scope', 'user_id'], unique: 'organization' }

and the object's own header calls that the row identity. user_id is NULL on every row that is not scope='user' — SettingsService.set computes it as scope === 'user' ? ctx.userId ?? null : null — and SQL UNIQUE treats NULLs as mutually distinct. So the constraint is void on the tenant and global limbs, which are exactly the ones carrying organization-level and platform-level configuration.

The organization key part is NULL-safe (COALESCE(organization_id, '__global__'), ADR-0120 D3). user_id is not, and D1's vocabulary scopes the tenant column only — sys_view_definition's declaration says so in its own comment, about its own owner column:

⚠️ The KEY below is NULL-DISTINCT, which is a second gap the declaration cannot close on its own (#6417) … it scopes the tenant column only — owner would stay NULL-distinct.

Measured, live, on a real engine

Real SqlDriver, the real shipped declaration, OS_TENANCY_POSTURE=isolated. Identical before and after #8555 — this is not something that card introduced or fixed:

scope='tenant', user_id NULL, SAME organization, same (namespace, key)
 org_jia POST (lifecycle, retention_overrides, tenant, NULL) → 201
 org_jia POST the SAME AGAIN → 201 ← row identity void
 rows in table: 2

scope='global', user_id NULL
 platform POST (mail, smtp_host, global, NULL) → 201
 platform POST the SAME AGAIN → 201 ← two competing platform defaults

control — the same rows with a NON-NULL user_id
 org_jia POST (lifecycle, retention_overrides, tenant, usr_1) → 201
 org_jia POST the SAME AGAIN → 409 UNIQUE_VIOLATION

The control identifies the mechanism: it is the NULL, not the scope value.

Pinned as live assertions in packages/drivers/driver-sql/src/sql-driver-sys-setting-organization-unique.test.ts, section 4 ("the NULL-distinct user_id hole"), written to go red when this card lands so a fix must come and flip them rather than leaving a stale comment behind.

User-reachable consequence

SettingsService resolves a layer with rows.find((r) => r.key === key && r.scope === 'tenant') — a positional pick over whatever the table returns. With duplicates permitted, which value an organization gets for a tenant-scoped setting is unspecified, and a write through set() upserts against where { namespace, key, scope, user_id }, which matches an arbitrary one of them. Two rows can therefore disagree indefinitely with no way for an admin to see why the effective value is not the one they set. lifecycle.retention_overrides is a live tenant-scoped key, so this reaches real retention behaviour.

Sibling objects likely share the shape — sys_setting_audit mirrors the same three-value scope — but that is unverified and deliberately not asserted here.

Why it is not a rider on #8555

#8555 respells unique: true to 'organization'. That is a relaxation: it admits key pairs previously refused and refuses nothing that previously succeeded, so it cannot fail to apply on any existing database. Verified there against a database deliberately seeded with pre-existing duplicate tenant-scope rows — the replacement index builds and every row survives, precisely because it does not tighten user_id.

Making user_id NULL-safe is the opposite: it is a tightening that will fail to create the index on any installation that has already accumulated duplicates, which the void constraint has been permitting all along. That needs ADR-0120 D4's conflict-row disposition and a duplicate pre-flight in os migrate plan. Exactly the tradeoff #6417 recorded, verbatim and untranslated:

把键改成 NULL-safe 则相反：那是收紧，在已经存在重名活跃共享视图的库上会直接建不出索引……需要 ADR-0120 D4 的冲突行处置口径配套。这是独立的取舍，需要单独拍板。

Two routes, both with precedent

1. Runtime migration, the [metadata-core] sys_view_definition：owner/organization_id 为 NULL 的视图完全不受唯一索引约束（两条同名活跃共享视图可并存） #6417 / PR fix(metadata): sys_view_definition active-row index gets a NULL-safe key — same-name active shared views can no longer coexist (#6417) #6666 route. metadata-protocol's ensureViewDefinitionActiveIndex issues a raw-SQL CREATE UNIQUE INDEX at kernel:ready, reusing the declared index's name so syncDeclaredIndexes (which skips by name) never re-imposes the NULL-distinct form on a later boot. ensureOverlayIndex does the same with COALESCE(package_id, '') for sys_metadata. Proven twice; leaves the declaration unable to say what it means.
2. Extend the declared vocabulary so an author can mark a listed column NULL-safe. Closes the class rather than the instance, and would let the declaration state its own row identity — but it is authorable-surface design and needs its own ADR (or an ADR-0120 amendment), plus SqlDriver.createNullSafeUniqueIndex's existing MySQL functional-key fallback extended to it.

Route 2 is the ADR-0078 direction (a declaration that cannot express its own constraint is a declared-but-unenforced surface), but route 1 is what has shipped twice. Recording both rather than choosing.

Decision needed before implementation

For a duplicate-carrying installation, what happens to the extra rows — refuse to migrate and hand the operator a list, or apply a deterministic keep-one rule (newest updated_at)? Settings values are admin-authored configuration, so silently discarding one is a real data decision, not a cleanup.

Related

• sys_setting's unique key is installation-wide on a tenant-scoped object — but unlike the #8323 class it has a real argument for staying that way #8555 — the per-organization respelling of this same index (the relaxation half).
• [metadata-core] sys_view_definition：owner/organization_id 为 NULL 的视图完全不受唯一索引约束（两条同名活跃共享视图可并存） #6417 / PR fix(metadata): sys_view_definition active-row index gets a NULL-safe key — same-name active shared views can no longer coexist (#6417) #6666 — the same NULL-distinct class on sys_view_definition, fixed via route 1.
• driver-sql: 单租户栈上 organization_id 恒为 NULL,#3696 的 (tenant, col) 复合 UNIQUE 因 NULL-distinct 而完全不生效 —— 字段级 unique: true 静默零约束 #5030 — the tenant-column instance, fixed by ADR-0120 D3.
• ADR-0120 (D3 NULL-safe uniqueness, D4 tightening ceremony), ADR-0049 / ADR-0078 (declared-but-unenforced).

[hotlong]

Triage: routed needs-user-decision + domain:metadata (declaration lives in packages/platform-objects, route-1 fix would land in metadata-protocol), type Bug — declared row identity ≠ enforced on the tenant/global limbs.

Premise check on origin/main (2026-08-14): sys-setting.object.ts:191 currently declares { fields: ['namespace','key','scope','user_id'], unique: true } — the card quotes the 'organization' spelling, which is on the in-flight #8555 branch, not yet on main. The NULL-distinct user_id hole is spelling-independent (the card's own control run shows the mechanism is the NULL, not the scope value), so the premise holds either way. The pinned section-4 assertions likewise live on the #8555 branch. Serial: implementation cannot start before #8555 lands (same declaration line, same index name).

Why the human floor, not the #8468 inheritance: the settled installation-wide-unique family covers the relaxation shape (bare unique: true → per-organization). This card is the opposite move — a tightening that can fail to build the index on duplicate-carrying installations, i.e. ADR-0120 D4 ceremony plus a stored-data disposition; and route 2 is authorable-surface design (ADR-class). Both are human-floor categories, and the sibling ruling (#6417) itself recorded this half as an independent tradeoff needing its own ruling:「这是独立的取舍,需要单独拍板。」

Four-prism block:

• Platform long-term coherence — third instance of the same NULL-distinct class (driver-sql: 单租户栈上 organization_id 恒为 NULL,#3696 的 (tenant, col) 复合 UNIQUE 因 NULL-distinct 而完全不生效 —— 字段级 unique: true 静默零约束 #5030 tenant column → ADR-0120 D3; [metadata-core] sys_view_definition：owner/organization_id 为 NULL 的视图完全不受唯一索引约束（两条同名活跃共享视图可并存） #6417 owner → raw-SQL runtime patch; now user_id). Route 2 (declared NULL-safe column vocabulary) closes the class and lets a declaration state its own row identity; route 1 accretes a third out-of-band CREATE UNIQUE INDEX and widens the declared≠enforced gap.
• Measured business pull — live and measured on a real engine: duplicate tenant/global rows make the effective value of a real key (lifecycle.retention_overrides) unspecified, and set()'s upsert updates an arbitrary duplicate. Admin-visible misconfiguration with no diagnosis path; reaches real retention behaviour.
• AI-agent error-resistance — a declaration that silently does not mean what it says is the exact trap contract-first exists to prevent; on this axis route 2 (expressible, enforced constraint) beats route 1 (hidden runtime patch). For the data disposition: refuse-and-list beats keep-newest — silently discarding admin-authored configuration is silent tolerance.
• Startup scope discipline — route 1 is small and shipped twice (PR fix(metadata): sys_view_definition active-row index gets a NULL-safe key — same-name active shared views can no longer coexist (#6417) #6666 pattern, ensureOverlayIndex); route 2 is spec-surface design + ADR (or ADR-0120 amendment) + MySQL functional-key fallback work. Scope discipline says route 1 now, route 2 parked to the ADR-0120/v18 train (natural neighbour: ADR-0120 协议 18 项:D2 conversion(声明索引 unique: true → 'global')+ 裸 true 硬拒 + synonym pin 退役 —— 挂 18 列车,现在勿动工 #5082).

Recommendation: route 1 now — kernel:ready NULL-safe unique index via COALESCE(user_id, ''), reusing the declared index name so syncDeclaredIndexes never re-imposes the NULL-distinct form; duplicate disposition = refuse-to-migrate with an operator-facing list in the os migrate plan pre-flight (settings are admin-authored; keep-one is a data decision an operator should make, not a default we pick); file the route-2 vocabulary card on the v18/ADR-0120 train so the class closes there.

Two questions for the maintainer:

1. Route 1 now with route 2 parked to v18 — yes, or go straight to the vocabulary/ADR route?
2. Duplicate-row disposition on migration: refuse + operator-facing list (recommended), or deterministic keep-newest?

Not auto-adjudicable (human floor: stored-data migration shape + ADR-class route choice). Prisms 1/3 lean route 2 long-term while prism 4 leans route 1 now — genuinely split on timing, which is itself above the confidence gate.

---

Generated by Claude Code

[hotlong]

Maintainer ruling (2026-08-14, verbatim: 「同意」, approving item 3 of the six-item ruling list: 「#8629 走 Route 1＋拒绝迁移列清单?」): Route 1 now — runtime NULL-safe unique index issued at kernel:ready (precedent PR #6666); Route 2 (declaring NULL-safe uniqueness in the spec vocabulary) is deferred to v18 as the ADR-class long-term form. Migration semantics: refuse-to-migrate and list the duplicate rows for the operator — never keep-newest; silently discarding admin-authored config rows is a data-loss trade that is not worth saving one manual confirmation. Serial constraint noted at triage stands: implementation is blocked behind #8555's landing. needs-user-decision → pm:queue, target:v17 applied; Seat A (#8667).

---

Generated by Claude Code