tenant-scoped objects get GLOBAL unique indexes: 409-vs-201 enumerates other tenants' values, and a user's preferences silently stop persisting in their second org

[baozhoutao]

On a deployment running OS_TENANCY_POSTURE=isolated, reads are tenant-scoped but field-level unique: true materializes a global unique index. The two disagree, and that gap is both an information leak and a live data-plane failure.

1. The oracle: 409 vs 201 enumerates values you cannot see

Same user, two organizations they belong to (甲 / 乙), sys_capability:

as 甲: POST /data/sys_capability {name:"probe_cap_xtenant"} → 201 (stamped organization_id = 甲)
as 乙: GET /data/sys_capability?filter=["name","=","probe_cap_xtenant"] → total 0 ← invisible
as 乙: POST /data/sys_capability {name:"probe_cap_xtenant"} → 409 UNIQUE_VIOLATION ← but it collides
as 乙: POST /data/sys_capability {name:"probe_cap_only_in_b"} → 201 ← control

A tenant can therefore decide, for any value, whether some other tenant already holds it — while being unable to read the row. It also leaks platform-reserved rows: POST {name:"manage_metadata"} returns 409 in an org whose visible capability count is 0.

2. Not one object — the same on ordinary tenant data

sys_user_preference, unique on (user_id, key), same user in both orgs:

as 乙: POST {user_id:<self>, key:"probe.uniq.xtenant"} → 201
as 甲: GET ?filter=["key","=","probe.uniq.xtenant"] → total 0
as 甲: POST {user_id:<self>, key:"probe.uniq.xtenant"} → 409 UNIQUE_VIOLATION

Any tenant-scoped object with a unique field inherits this. For customer-authored objects the enumerable values are the ones people actually mark unique — national ID, phone, email, customer code. Under isolated, the posture sold as "legal-entity / sovereignty isolation — the hard wall" (packages/spec/src/security/tenancy-posture.ts:15), that is exactly the wall this crosses.

3. It is already breaking the platform's own feature

The console's own preference rows collide across organizations:

zhangsan in 甲: keys = ["ui.recent"]
zhangsan in 乙: keys = ["probe.uniq.xtenant"] ← no ui.recent
zhangsan in 乙: POST {key:"ui.recent"} → 409 UNIQUE_VIOLATION

So a user who belongs to two organizations can never persist ui.recent (or any preference key they already used elsewhere) in the second one. It fails silently, because the client swallows it by design — packages/data-objectstack/src/userState.ts:

save() resolves without throwing, so UI mutations never surface a toast.
[…] Swallow — provider falls back to localStorage as source of truth.

Net effect: "recent items" and similar preferences quietly never persist server-side in a user's second workspace, with no error anywhere.

Cause

uniqueIndexesFromFields (packages/drivers/driver-sql/src/schema-drift.ts:921) only puts the tenant column in the index when the field opted in:

const scoped =
 isOrganizationScopedUnique(field.unique) && tenantField != null && tenantField !== name;
const columns = scoped ? [tenantField, name] : [name];

unique: true (and 'global') → single-column global index. Only unique: 'organization' (ADR-0120 D1) → (organization_id, field). The machinery for the correct shape exists and is used for tenanted autonumber — sql-driver.ts:4318 documents that index as (COALESCE(organization_id,'__global__'), field) — but ordinary unique fields do not get it, and the platform's own objects (sys_capability, sys_user_preference) are declared with the plain form.

The decision

1. Default should follow the posture. Under group/isolated, a bare unique: true on a tenant-scoped object arguably means "unique within the tenant". Making the default posture-aware fixes every existing object at once, at the cost of an index migration.
2. At minimum, fix the platform's own objects — sys_user_preference (user_id, key) and sys_capability (name) should be unique: 'organization'. This alone clears §3 and most of §2's blast radius on system tables.
3. Whatever is decided, the 409 must stop being an oracle for values in other tenants — a collision against an invisible row should read as an ordinary refusal, not a distinguishable "taken" signal.

I did not assume which of these you want, hence one issue rather than a patch.

Environment

objectos-ee-deploy (Caddy → app → postgres:16, NODE_ENV=production, OS_TENANCY_POSTURE=isolated) on http://localhost:8080, observed 2026-08-13, via the REST API with real sessions of a user who is a member of both organizations. Probe rows created during this test were deleted afterwards.

Related: #8093 (the same sys_user_preference write path, surfacing a spurious warning toast).

[hotlong]

Triage: needs-user-decision + domain:metadata + target:v17 (release-board class ①: hit today on the published isolated posture — measured live cross-tenant 409 oracle on system tables, plus a two-org user's preferences silently never persisting). Removed the self-made domain:access-security label (not in the lane table — triage is the sole domain:* producer) and priority:p1 (the p1–p5 ladder is retired; urgency is carried by the release board and the Bug tie-break). Type Bug.

Prior art that reshapes the options — read before ruling: #3696 led to ADR-0120 D1's explicit vocabulary (unique: 'organization'); #4986 closed the table-level declared-index instance; and #5082 (pm:on-hold, target:v18) already parks the D2 ruling that a bare unique: true converts to 'global' and the bare spelling is later hard-refused. Option 1 in this card (posture-aware default for bare true) would therefore contradict a parked maintainer ruling — not adjudicable at triage, and re-opening it belongs on #5082, not here.

Four-prong assessment:

• Platform long-term coherence: option 2 — declare unique: 'organization' on the platform's own objects (sys_user_preference (user_id, key) in packages/platform-objects/src/identity/sys-user-preference.object.ts; sys_capability.name in packages/plugins/plugin-security/src/objects/sys-capability.object.ts) — uses the D1 vocabulary exactly as designed and stays consistent with parked D2. Option 1 re-litigates D2.
• Measured business pull: the console's own preference feature is broken for every multi-org user, silently (the client swallows by design); the sys_capability oracle leaks platform-reserved rows into orgs with zero visible capabilities.
• AI-agent error-resistance: with option 2 the declarations match the posture story; the v18 D2 hard-refusal is what ultimately forces every author to choose explicitly — that, not a posture-aware default, closes the class.
• Startup scope discipline: option 2 touches two object declarations plus existing drift machinery (the (tenant, field) composite shape already exists for tenanted autonumber); no new surface.

Recommendation: option 2 now; defer the default-semantics question to the #5082 v18 train; accept the §1 oracle on customer-authored bare-unique objects as the documented cost of 'global' uniqueness until v18 (a global unique constraint cannot avoid disclosing collisions; the durable fix is authors declaring 'organization', which v18's hard-refusal will force). The system-table oracle (§1's sys_capability probe, §3's preference breakage) disappears with option 2's per-org indexes.

Dispatch note if approved: the landing spans platform-objects (this lane) and plugin-security (domain:identity) — contract-first split or a triage-designated cross-domain single PR with full file-surface declaration in the claim. Also verify at dispatch why the table-level {fields:['user_id','key'], unique: true} on sys_user_preference still materialized globally despite #4986's fix (that card closed the normalizeDeclaredIndex tenant-column gap — either the fix requires the explicit 'organization' spelling, or there is a regression; the dev should measure, not assume). Related: #8093 (same write path, different symptom). 本评论来自分诊座位 Routine。

---

Generated by Claude Code

[baozhoutao]

Supporting evidence that the platform already treats this risk as real: the local-install path has an ADR-0120 D5e posture gate (packages/cloud-connection/src/marketplace-install-local-plugin.ts:594) that BLOCKS installing a package carrying installation-wide unique indexes under an isolated posture, returning 409 GLOBAL_UNIQUE_CONFIRMATION_REQUIRED and demanding an explicit --confirm-global-uniques ceremony per index.

So a third-party package may not introduce a cross-tenant global unique without a human confirming each one, while the platform's own sys_capability / sys_user_preference ship exactly that shape with no ceremony and no record. Same hazard, opposite treatment — which is an argument for fixing the platform objects rather than documenting the oracle away.