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

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:

An organization's authored capability row is silently overwritten with the platform's copy at boot.
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

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

{
  "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

Supersedes the earlier report comment on this card (review rounds added commits; findings and verification numbers below are from the final head 1abef9fd27).

{
  "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. DERIVED half untouched (R3). The measurement that selected the route also falsifies the alternative: 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 (only findOne opts out). Measured on better-sqlite3: the returned row tracks `id` ascending and ignores physical insert order, so 'add an ORDER BY' would only change which wrong row is picked — and the defect is MORE persistent than the card states, being stable per installation rather than flipping between boots, so a wrong choice never self-heals. The two predicates also make the result set a provable singleton under the post-#8461 key (COALESCE(organization_id, …), name), which retires the ordering question rather than answering it. Added `blockedCurated` (counter + warn) for the one collision the fix now DECLINES to resolve by overwriting; the warning reports the provenance it READ off the blocking row and splits three ways (no row visible / row with no managed_by / row with a value) rather than asserting authorship it cannot observe. It reports the distinction and deliberately does not act on it — adopting or backfilling a stamp are maintainer calls.",
  "tests": "At final head 1abef9fd27: `pnpm --filter @objectstack/plugin-security test` → 54 files, 1072 passed; typecheck EXIT=0. ABLATION (fix committed first, prediction written before each run): predicted 7 RED / 3 GREEN controls; measured `7 failed | 16 passed (23)` — exactly the predicted seven. The three-way diagnostic split ablated separately: `1 failed | 23 passed`, exactly the new pin. Real-driver harness (SqlDriver + better-sqlite3, fixture copying sys-capability.object.ts incl. `unique: 'organization'`), since deleted: two-row state reachable via this call site; `{ name }` + limit 1 returned the smaller-id row under BOTH insert orders and followed the id when ids were swapped; scoped predicate returned the platform row under both orders and zero 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. `pnpm check:type-check-debt` (package script, --self-test AND --re-measure) EXIT=0: 33 ledger entries re-measured, 1969 raw errors, none above recorded, surplus none. Union all EXIT=0 at the same head: nul-bytes, cross-package-test-inputs, docs-audit-scope, test-source-alias, type-source-resolution, query-options-erasure, type-check-coverage, engine-double-contract, empty-changeset, i18n, check-changeset-no-major.mjs. Ledger numbers identical to baseline (engine doubles 201/133/2; query-options-erasure 67 unswept; type-check 64/77, 13 DEBT). CI at head: ESLint (which carries check:engine-double-contract) green.",
  "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",
    "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",
    "filed as #8550: a green gate union proves nothing about the head it is reported against — nothing ties a gate run to a commit, so a union run mid-round goes stale silently when review adds commits (this cost a red CI lap on PR #8537; sibling of #8545, different root cause)"
  ]
}

§4 open question — answered. A never-seeded curated capability affects no authorization decision. The requiredPermissions AND-gate compares string sets (getSystemPermissions() unions permissionSets[].systemPermissions; normalizeRequiredPermissions reads the resource's declared strings) and loads no row; validateCapabilityReferences resolves against PLATFORM_CAPABILITY_NAMES, stack declarations and seed-data records, never the table; and a repo-wide search for row reads of sys_capability finds exactly two production call sites, both seeders. Registry/Setup-listing defect, not an authz one — no widening needed.

Reachability matrix for the platform bucket (PM review Q1/Q2): managed_by: 'platform' → matched and reconciled. admin → reachable and ordinary (organization_id auto-stamping lives in the enterprise @objectstack/organizations runtime, which is also what activates every walled posture, so a deployment without it puts every Setup-authored row in the NULL bucket). package → reachable via name promotion (setup.write, manage_sharing were both curated after the fact). Absent → not engine-reachable: managed_by is required: true with defaultValue: 'admin', resolved before the beforeInsert hooks. Caveat stated rather than hidden: this clone is shallow (157 commits), so pre-boundary seeder revisions could not be inspected.

Generated by Claude Code

Landed — 5c04b2a9 on origin/main (PR #8537, squash-merged, 19:03:28Z). Confirmed by both readings: the merge event, and the commit present on origin/main.

The curated half now looks up { name, managed_by: 'platform', organization_id: null } — the row the platform owns. The DERIVED half is untouched.

This card's stated mechanism was wrong, in the worse direction

The card expects a choice that "can differ between boots on the same database". It does not. #4363's pagination tie-breaker already appends ORDER BY id to any paged read of a driver-managed table, and limit: 1 counts as paged on both SqlDriver and MongoDBDriver (only findOne opts out, via singleRowLookup). Measured on better-sqlite3, the returned row tracks id ascending and ignores physical insert order entirely.

Two consequences, and the second is why this matters:

Ordering was never the missing property. It was already there. Adding an explicit orderBy would change which wrong row is chosen, not that a wrong row can be chosen. Ownership was missing, not determinism.
The defect is more persistent than the card says. Being stable makes it permanent: an installation that reconciles the wrong row keeps reconciling it on every subsequent boot, rather than self-healing on some later one.

How that surfaced is worth recording: the dev predicted rows 1–2 of its table would differ by insertion order, measured that they didn't, and chased the failed prediction instead of filing it as noise. That is where #4363 came from.

Why the predicate is a conjunction

managed_by: 'platform' AND organization_id: null are the two facts that jointly define the platform's own row, and together they make the result set a provable singleton — the post-#8461 unique key is (COALESCE(organization_id, …), name), so the NULL-organization bucket admits at most one row per name. limit: 1 over a set of size ≤ 1 cannot be arbitrary. That retires the ordering question rather than answering it.

Neither predicate alone suffices: managed_by alone loses the singleton if a platform-marked row ever sits inside an organization; organization_id alone cannot tell the platform's row from an admin's on a single-organization deployment.

⛔ The installation-wide unique index was not restored and must not be — it is #8323's cross-tenant existence oracle, closed by #8461.

The open question the card asked: answered, and the filer's expectation held

does a never-seeded curated capability break any downstream assumption?

No authorization decision is affected. The requiredPermissions AND-gate compares string sets; validateCapabilityReferences resolves against the PLATFORM_CAPABILITY_NAMES spec constant and never reads the table; a repo-wide search for row reads of sys_capability finds exactly two production call sites, both seeders, neither on an authorization path. So the harm is a registry/Setup-listing defect, as the filer expected — and the filer was right to ask the next person to measure rather than inherit that.

What the fix declines to do, and says so

A curated name can be held in the platform bucket by a row this pass does not match. The seeder now declines rather than overwriting, counts it (blockedCurated) and warns — because tryInsert swallows the engine's refusal, so declining silently would look exactly like a clean boot with a capability missing installation-wide.

The warning reports the provenance it read, and splits three ways: no blocking row visible at all / a row with no managed_by / a row with a value. Collapsing the first two would have repeated this branch's own defect one level down — the insert was refused, so a follow-up read finding nothing is a genuinely interesting state, not an ordinary unstamped row.

Reachability was measured, not assumed: admin is ordinary (the organization_id stamper lives in the enterprise organizations runtime, which is also what activates every walled posture — so on single posture every Setup-authored row lands in the NULL bucket); package is reachable via name promotion, with setup.write and manage_sharing both curated after the fact; an absent managed_by is not engine-reachable (required: true, defaultValue: 'admin', defaults applied before beforeInsert).

It deliberately does not act on the distinction: adopting a differently-stamped row would reverse #5876's "not provably ours → leave it alone", and backfilling a stamp is a data migration. Both are maintainer calls. Filed as #8552.

Residue

[Decision] When a curated capability name is already held in the platform bucket, should the seeder keep declining — or adopt, backfill, or refuse the collision at authoring time? #8552 — the policy decision above (needs-user-decision).
sys_capability.active is read by nothing — the Deactivate action tells the admin grants stop resolving, and they do not #8535 — sys_capability.active is read by nothing while Deactivate tells the admin grants stop resolving.
The DERIVED half of bootstrapSystemCapabilities also leaves the platform bucket unseeded when an organization holds the name — the #5876 guard skips instead of inserting #8536 — the DERIVED half has the same unseeded-bucket shape, on the half this card's ruling excluded.
A green gate union proves nothing about the head it is reported against — nothing ties the run to a commit, so a mid-round union goes stale silently #8550 — a green gate union proves nothing about the head it is reported against.
finding: check:engine-double-contract counts declaration sites, so a behaviourally-distinct engine double built by Object.assign over an existing one is not counted #8553 — the engine-double detector counts declaration sites, so a double built by override is not counted.

Generated by Claude Code

fix(security): the curated capability seeder reconciles the row the p…

fix(security): the derived capability seeder owns its row by the same…

docs(plugin-security): re-anchor the dead tracker citations to the co…

Navigation Menu

Repository navigation

`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 #8470

The mechanism

Measured

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

The concrete failure

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

Blast radius

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

Direction

This card's stated mechanism was wrong, in the worse direction

Why the predicate is a conjunction

The open question the card asked: answered, and the filer's expectation held

What the fix declines to do, and says so

Residue

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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 #8470

Description

The mechanism

Measured

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

The concrete failure

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

Blast radius

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

Direction

Activity

os-zhuang commented on Aug 13, 2026

os-zhuang commented on Aug 13, 2026

os-zhuang commented on Aug 13, 2026

os-zhuang commented on Aug 13, 2026

This card's stated mechanism was wrong, in the worse direction

Why the predicate is a conjunction

The open question the card asked: answered, and the filer's expectation held

What the fix declines to do, and says so

Residue

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

`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 #8470