`bootstrapSystemCapabilities` reconciles an arbitrary row when a curated capability name exists in more than one organization — `find(..., limit: 1)` has no ORDER BY, so the platform's own row can be left unseeded

[os-zhuang]

Measured while implementing #8323 (PR #8461). This PR unmasks the defect; it does not cause it — see "Provenance" below, which also says why restoring the global unique is not the remedy.

The mechanism

packages/plugins/plugin-security/src/bootstrap-system-capabilities.ts upserts each capability by name:

const existing = await tryFind(ql, 'sys_capability', { name: def.name }, 1);
const row = existing[0];

tryFind runs under SYSTEM_CTX = { isSystem: true }, i.e. across organizations, and the query is limit: 1 with no ORDER BY. When two rows share a name — the platform's own (organization_id NULL) and one an admin authored inside an organization — the row this returns is arbitrary, and SQL guarantees nothing about which. It can differ between boots on the same database.

For a curated name the seeder then takes the update branch and writes the platform's label/description onto whichever row it got.

Measured

Against the seeder's own in-memory ql harness, which models isSystem faithfully (filters on where only, ignoring organization). Direction predicted before each run; all three matched.

#	case	result
1	DERIVED name (approve_invoice) + org-authored row	✅ safe — skippedAuthored: 1, org label preserved. The derivedNames.has(name) && row.managed_by !== 'platform' guard fires
2	CURATED name, platform row seeded first, org row added after	⚠️ safe in the harness ONLY — see the caveat below
3	CURATED name (manage_users), org row present, platform row not yet	⛔ platformRowExists: false — the platform's own row is never seeded, and the org row's ORG CUSTOM LABEL is overwritten with Manage Users

⚠️ The caveat on row 2 — please do not read this card as "2 of 3 safe"

Row 2 passes only because the fake returns rows in insertion order. The real query is find(..., { where: { name }, limit: 1 }) with no ORDER BY, and SQL provides no ordering guarantee there. On a real database, once two rows share a name the seeder's choice is arbitrary and may flip between boots — so row 2 is not a safe case, it is row 3 with an unobserved coin flip. Insertion order is a property of the test double, not a property of the system.

The concrete failure

platformRowExists: false. Two harms, the second worse:

1. An organization's authored capability row is silently overwritten with the platform's copy at boot.
2. A single tenant's row suppresses the platform's own definition installation-wide — the curated row is never created, in any bucket.

Scope of what was measured — verbatim, and please keep it prominent

I measured seeding behaviour only. I did not measure whether a missing platform capability row changes any authorization decision.

My reasoning that this is not a privilege issue — stated as my claim with its basis, not as a finding — is that sys_capability rows are definitions, while grants live in permission sets (systemPermissions) and requirements in requiredPermissions, both of which reference a capability by name rather than by row. That is why I expect a missing row to be a metadata/UX defect rather than an authz one. I did not test it, and I would rather the next person measure it than inherit my confidence.

Open question, explicitly unresolved: does a never-seeded curated capability break any downstream assumption? Candidates worth checking: validateCapabilityReferences (the authoring lint warns when requiredPermissions names a capability "registered nowhere"), Setup surfaces that list capabilities, and anything that resolves a capability row rather than the bare string.

Blast radius

Exactly the 8 curated names in PLATFORM_CAPABILITIES (packages/spec/src/security/capabilities.ts):

manage_users · manage_org_users · manage_metadata · manage_platform_settings · setup.access · setup.write · studio.access · manage_sharing

Derived names are guarded (row 1). Reaching it requires an org admin to author a sys_capability row whose name exactly matches one of those 8 — which is a supported action (managedBy: 'config'; ADR-0066 D1: the platform DEFINES, admins EXTEND in Setup).

sys_user_preference is unaffected — nothing seeds preferences by name across organizations.

Provenance — unmasked, not caused, and ⛔ not a reason to restore the global unique

The nondeterministic limit: 1 lookup predates #8461 and is unchanged by it. What #8461 changed is only that the two-row state became reachable: sys_capability.name was an installation-wide unique index, so an organization could not author a row named manage_users at all (it got 409) — the constraint made the ambiguity unreachable rather than correct.

⛔ Restoring the global unique is not the fix. That index is precisely the release-blocking cross-tenant existence oracle #8323 reports (an organization could read 409-vs-201 to enumerate values it cannot see, on the isolated posture sold as a hard wall). Reverting it to hide this defect would reopen a security hole to paper over a metadata one.

Direction

No contested intent here — the lookup is simply under-specified. Either or both:

• make the platform lookup deterministic (order the query, or select by identity rather than by name alone), and/or
• scope the platform seeder's lookup to managed_by = 'platform' (and/or organization_id IS NULL), so it reconciles the row it owns and never an organization's.

The second matches the intent already expressed one branch down, where the derived half deliberately refuses to touch a row it does not own (#5876). The curated half simply never had the equivalent guard, because it never needed one.

Filed unassigned per the PM's sequencing. Not fixed in #8461 — deliberately, to keep a security release blocker narrow. Measured by session session_012WMpuAfA2KSdDjGF6tm1bH.

---

Generated by Claude Code

[os-zhuang]

Claiming this card.

• Session: session_012WMpuAfA2KSdDjGF6tm1bH
• Branch: claude/issue-8470-capability-seed-lookup

Note for whoever reads this later: the session ID above is identical to the one recorded at the bottom of the issue body as the measuring session for #8461. That is the session URL this container reports, not evidence that the filer and I are the same actor — treat the branch name as the disambiguator for this claim.

---

Generated by Claude Code

---

Generated by Claude Code

[os-zhuang]

{
 "issue": 8470,
 "status": "done",
 "branch": "claude/issue-8470-capability-seed-lookup",
 "pr": "https://github.com/objectstack-ai/objectstack/pull/8537",
 "premise_still_valid": true,
 "summary": "Scoped the CURATED half of bootstrapSystemCapabilities to `{ name, managed_by: 'platform', organization_id: null }` — the row the platform owns. The DERIVED half is untouched (R3). Route selected by measurement, and the measurement falsifies the alternative rather than ranking it: the lookup was ALREADY deterministic, because #4363's pagination tie-breaker appends `ORDER BY id` to any paged read of a driver-managed table and `limit: 1` counts as paged on SqlDriver and MongoDBDriver alike (only findOne opts out via singleRowLookup). Measured on better-sqlite3: the returned row tracks `id` ascending and ignores physical insert order entirely, so 'add an ORDER BY' would change which wrong row is picked, nothing more — and the defect is MORE persistent than the card states (stable per installation, so a boot that reconciles the wrong row never self-heals, rather than flipping between boots). The two predicates together also make the result set a provable singleton, since the post-#8461 unique key is (COALESCE(organization_id, …), name) — which retires the ordering question instead of answering it. Added `blockedCurated` (counter + warn) so the one collision the fix newly DECLINES to resolve by overwriting — a non-platform row holding a curated name in the platform bucket, i.e. a Setup-authored row on a single-organization deployment — is not silent, since tryInsert swallows the engine's unique-constraint refusal.",
 "tests": "pnpm --filter @objectstack/plugin-security test → 54 files, 1068 passed. typecheck clean. ABLATION (fix committed first, prediction written before the run, lookup reverted to `{ name }`): predicted 5 RED / 2 new pins GREEN / derived control GREEN / all pre-existing GREEN; measured `5 failed | 38 passed (43)` — exactly the predicted five, no others, no divergence. Real-driver harness (SqlDriver + better-sqlite3, fixture copying sys-capability.object.ts incl. `unique: 'organization'`), later deleted: two-row state reachable via this call site; `{ name }` + limit 1 returned the smaller-id row under BOTH insert orders (org-first→cap_org, platform-first→cap_org; ids swapped→answer follows the id, not the order); scoped predicate returned the platform row under both orders and zero rows when only an org row exists; `organization_id: null` really compiles to IS NULL; the platform's global insert is NOT blocked by an org row of the same name; a second platform-bucket row IS refused. Gates all exit 0 (union of lint.yml + scripts/pm/dispatch-gates.mjs): nul-bytes, cross-package-test-inputs, docs-audit-scope, test-source-alias, type-source-resolution, query-options-erasure, type-check-coverage, i18n, engine-double-contract, empty-changeset, check-changeset-no-major.mjs. No shrink-only ledger grown. #8246 dist trap bit once on the first run; those numbers were discarded and every later measurement followed a `^...` closure build.",
 "open_questions": [],
 "out_of_scope_findings": [
 "filed as #8535: `sys_capability.active` is read by nothing, yet the Deactivate action's confirmation dialog tells the admin that grants and requirements stop resolving — a false promise on a security surface (unlabeled, PM triage)",
 "filed as #8536: the DERIVED half also leaves the platform bucket unseeded when an organization holds the name — the #5876 guard skips instead of inserting; same shape as this card's harm 2, on the half R3 excluded (unlabeled, PM triage)"
 ]
}

§4 open question — answered, and the filer's expectation holds. A never-seeded curated capability affects no authorization decision. Measured on origin/main at 30f1b7488d: the requiredPermissions AND-gate compares string sets (getSystemPermissions() unions permissionSets[].systemPermissions; normalizeRequiredPermissions reads the resource's declared strings) and loads no row; validateCapabilityReferences — the candidate this card named — resolves against PLATFORM_CAPABILITY_NAMES, stack declarations and seed-data records, never the table, so a curated name with no row does not warn; and a repo-wide search for row reads of the object (find/findOne/count/aggregate against 'sys_capability', plus every consumer of its platform-object-names entry) finds exactly two production call sites, both seeders. The harm is a registry/Setup-listing defect, not an authz one — so no widening was needed and the card's severity stands.

---

Generated by Claude Code

---

Generated by Claude Code