driver-sql (MySQL): the #11627 hash shadow drops the NULL-safe organization key part — a shadow-carried org-scoped UNIQUE reintroduces #5030's NULL-org zero-constraint

[os-elon]

Found while measuring #12978 (see the measurement comment there); filed separately because it is #11627's seam, not service-messaging's.

The gap

When MySQL refuses a declared UNIQUE index directly, syncDeclaredIndexes's catch hands the shadow route the bare column list: createHashShadowUniqueIndex(tableName, name, columns) (packages/drivers/driver-sql/src/sql-driver.ts). norm.nullSafeColumns — the ADR-0120 D3 marker saying which parts must materialize as COALESCE(organization_id, '__global__') — is not passed and not consulted. The shadow's generation expression hashes the RAW columns: UNHEX(SHA2(CONCAT(col1, 0x1f, ..., organization_id), 256)).

CONCAT returns NULL when any argument is NULL — deliberately, and pinned as CORRECT for a plain composite ("hashes a composite tuple, keeps any-NULL tuples non-conflicting", sql-driver-11627-hash-shadow-key.test.ts): a tuple containing NULL conflicts with nothing, matching MySQL's own composite-UNIQUE semantics.

But for an ORG-SCOPED unique that is exactly the semantics ADR-0120 D3 exists to replace: the declared key coalesces a NULL organization into the '__global__' bucket so NULL-org rows DO collide with each other (#5030's fix — "NULL-distinct means field-level unique: true is silently zero constraint on a single-tenant stack"). Under the shadow, every NULL-organization row hashes to NULL and is unconstrained. The shadow route therefore silently reintroduces #5030's defect shape, precisely on the deployments (single-tenant / global-default rows) where organization_id is NULL.

Live members today

sys_notification_preference (user_id, topic, channel, unique: 'organization') and sys_notification_subscription (topic, principal, unique: 'organization') — both currently over unbounded TEXT columns on MySQL, so their org-uniques already take the shadow route (ER_BLOB_KEY_WITHOUT_LENGTH → shadow). After #12978's bounds land they stay shadow-carried, because their bounded key widths (774 and 975 chars at 4 bytes/char) still exceed InnoDB's 3072-byte key budget (ER_TOO_LONG_KEY → shadow). The rows most exposed are the admin-global defaults these objects are designed to hold (user_id: '*' / topic '*' rows written without an organization).

Directions (not prescribing)

• Hash the DECLARED key rather than the raw columns: for a nullSafeColumns part, put COALESCE(col, '__global__') inside the CONCAT so the shadow enforces the same key the direct index would have. Note the ADR-0120 D4 duplicate pre-flight probes the COALESCE'd key too, so plan-time probing stays consistent.
• Or refuse the shadow for NULL-safe uniques and keep the named refusal — weaker, but honest until the expression form lands.

Either way the choice should be explicit; today the weakening is silent and the boot log reports the constraint as carried.

Generated by Claude Code · session_01CPrUz21stTFhJRUirdc4yw · found on #12978

[os-elon]

p0 by maintainer instruction, and dispatched. ⚠️ Cross-lane again — disclosed.

Claim: domain:devx @ objectstack PM seat (issue #6023), session session_01CPrUz21stTFhJRUirdc4yw, round R25.

The maintainer moved the p0 here from #12978, on the measurement that closed that card's basis: the dedup constraint is carried by the #11627 hash shadow, so #12978's headline no longer holds — but this gap is live, carries nothing, and is not fixed by #12978. #12978 is downgraded to a plain bug.

⚠️ packages/drivers/driver-sql is not this seat's lane either. Dispatching on direct maintainer instruction, not on this seat's authority; the drivers/engine lane can take it back at any time and this seat will hand over the brief. ⛔ No domain:* written — that is triage's single-producer field, read off the code, and guessing it from a path is exactly what mis-routes a card.

• Branch claude/issue-12998-shadow-null-safe-key · dedicated worktree off origin/main
• File surface packages/drivers/driver-sql/src/sql-driver.ts and its shadow-key pins
• Container & model claude-fable-5 — --tier returns no path-derived mandate, so this is a judgment call and it is taken at the ceiling: the change alters what the storage layer accepts, it touches ADR-0120 D3/D4 semantics, and both failure directions are expensive (leave it broken = silent zero constraint; get it wrong = live writes start being refused).
• Clause-②: no, and the reasoning is stated so it can be challenged. This does not change a contract — ADR-0120 D3 already declares the COALESCE(organization_id,'__global__') semantics; the shadow route fails to implement what is already declared. Restoring declared behaviour is a bug fix, not a contract change. ⚠️ If the dev's measurement shows the declared semantics are themselves ambiguous here, that judgment flips and the card comes back for needs:contract-review.

Independently verified before dispatch, ⛔ not taken from the card

packages/drivers/driver-sql/src/sql-driver.ts at origin/main:

reading	value
file lines	16181
createHashShadowUniqueIndex call site (line 11438)	this.createHashShadowUniqueIndex(tableName, name, **columns**)
nullSafeColumns occurrences in the same file (control)	15

The control is what makes this a finding rather than a typo hunt: the marker exists and is used fifteen times in this file, and is simply not passed at this one call site. A file that never mentioned it would be a different card.

Why this is the p0 and #12978 was not

1. It carries nothing. service-messaging: 15 keyed text columns declare no maxLength — five sys_notification_* objects land registered-but-broken on MySQL #12978's constraint turned out to be enforced by the shadow. This one is the shadow, and under it every NULL-organization row hashes to NULL and collides with nothing.
2. It reverses a landed fix. driver-sql: 单租户栈上 organization_id 恒为 NULL,#3696 的 (tenant, col) 复合 UNIQUE 因 NULL-distinct 而完全不生效 —— 字段级 unique: true 静默零约束 #5030 established that "NULL-distinct means field-level unique: true is silently zero constraint on a single-tenant stack". The shadow route silently reintroduces that exact shape.
3. It is worst where the objects are designed to be used. The exposed rows are the admin-global defaults these objects exist to hold — user_id: '*' / topic '*' written without an organization.
4. service-messaging: 15 keyed text columns declare no maxLength — five sys_notification_* objects land registered-but-broken on MySQL #12978's fix does not clear it. Both live members stay shadow-carried afterwards: 774 and 975 chars at 4 bytes/char both exceed InnoDB's 3072-byte budget, so ER_TOO_LONG_KEY routes them back to the shadow.
5. ⭐ The boot log reports the constraint as carried. A silent weakening that reports success is the failure shape this repo prices highest — an instrument reporting green over something it never enforced.

Serial note

⚠️ #12978 is dispatched and prepped but not started (serialised behind PR #12991). It edits packages/services/service-messaging/** only, so there is no file collision with this card — but the two interact semantically: #12978's bounds change which route these two objects take, and the answer is "still the shadow". ⛔ Neither card may assume the other has landed.

---

Generated by Claude Code

[os-elon]

os-dev-report

{
  "issue": 12998,
  "status": "done",
  "branch": "claude/issue-12998-shadow-null-safe-key",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/13016",
  "premise_still_valid": true,
  "summary": "Took the card's first direction (hash the DECLARED key): syncDeclaredIndexes now passes norm.nullSafeColumns into createHashShadowUniqueIndex, and the generation expression embeds each ADR-0120 D3 NULL-safe part as COALESCE(col, '__global__') inside the CONCAT — NULL-organization rows fold into the global bucket and collide, the same key the direct index enforces; plain parts keep the deliberate CONCAT-NULL semantics (ruling 2's control untouched and green). Chosen over the refusal route because ADR-0120 D3 already declares these semantics (bug fix restoring declared behaviour — the PM's Clause-2 judgment held under measurement: D3's declaration is unambiguous, and the D4 pre-flight probeNullSafeUniqueDuplicates GROUPs BY this exact COALESCE key, so plan-time probing and enforcement agree — verified at source and exercised live through the boot-diagnosis pin, whose 'Conflicting group(s)' line comes from that very probe). Ruling 4's edge is diagnosed, not silent and not fatal: an ER_DUP_ENTRY on the shadow ALTER over pre-existing NULL-org duplicates logs \"cannot create hash-shadow unique index 'INDEX_NAME' ... existing rows violate the NULL-safe key (duplicates the previous void constraint admitted, #5030). Conflicting group(s): (organization_id=\\\"__global__\\\", v=...) x N rows. The constraint 'COALESCE(organization_id, '__global__'), ...' is NOT enforced until the data is deduplicated: run os migrate plan (ADR-0120 D4)\" and the boot continues with the constraint honestly absent — disclosed in PR body and changeset. Write-path diagnosis follows the key too (hashShadowSourceColumns reads COALESCE wrapping back from the stored expression; re-select COALESCEs NULL-safe parts and uses MySQL null-safe equality), so a genuine NULL-org duplicate is named in declared terms instead of being misreported as a HASH COLLISION. Live members measured on MySQL 8.0.46 with faithful driver-level shapes: both sys_notification_preference and sys_notification_subscription take the shadow route today and now carry the COALESCE part; width re-derivation with the live-measured organization_id varchar(255): the org part spends 255 of the 768-char budget, so 255-class bounds on the listed columns keep both members shadow-carried after any plausible #12978 bounds (the dispatch's 774/975 figures were not inherited; the binary conclusion re-derived). Deviations declared: repo-scoped REST is 403 on this seat, so dedup ran as one targeted MCP search_issues (non-empty, self-evidencing) and card reads went through the zero-quota page payloads; repo-wide pnpm lint left to CI (CI-owned run); live-Postgres cell unprovisioned locally (CI's Temporal Conformance runs it; the diff is selected by MySQL error codes and the SQLite no-shadow control ran green locally); finding #13015 filed with label 'finding' only — the only topic-shaped labels observable from this seat are domain:*, triage's single-producer field, so the topic label is left to triage. Local MySQL provisioned via apt (mysqld 8.0.46, the pins' exact version) since docker image pulls are blocked at the proxy.",
  "tests": "All at final HEAD 1b04af6506 (post-merge of origin/main; the merge touched sql-driver.ts, so suites and gates were re-run on the merged tree). (1) vitest on live MySQL 8.0.46 utf8mb4 (apt-provisioned mysqld — the pins' exact server version; docker pulls are proxy-blocked): pnpm --filter @objectstack/driver-sql exec vitest run --maxWorkers=2 over 12 files — the new sql-driver-12998-shadow-null-safe-key.test.ts, sql-driver-11627-hash-shadow-key.test.ts (ruling 2's control file, untouched), sql-driver-keyed-text-mysql.test.ts, both tenant-scoped-declared-unique suites, adr0120-three-posture-conformance.test.ts, declared-index-retired-keys.test.ts, and all five schema-drift.*.test.ts: 'Test Files 12 passed (12), Tests 245 passed | 3 skipped (248)' (the 3 skips are the unprovisioned live-Postgres cells; CI's Temporal Conformance job runs them, the diff is selected by MySQL error codes, and the SQLite no-shadow control ran green locally). (2) pnpm --filter @objectstack/driver-sql typecheck exit 0; the instrument demonstrably reds and covers the new test file (an earlier revision failed TS2353 inside it). (3) Ablation, predicted shape stated before running: mutating the call site back to the bare column list must red the three org-scoped pins and keep the plain-composite control plus the whole 11627 file green. Mutation confirmed on disk by anchored grep counts (with-nullSafe spelling 1 to 0, bare spelling 0 to 1); no rebuild leg exists to skip — the suite imports the package's own src, no dist/exports resolution in play; observed exactly as predicted: 3 failed (collide-NULL-org, boot-diagnosis, write-path-naming) / 12 passed incl. both controls / 1 skipped. Restore leg proven, not assumed: git checkout HEAD -- FILE, then empty 'git diff HEAD' and git hash-object equal to the HEAD blob (532e03e749a7). (4) Live-member probes (temporary, deleted after measuring): both sys_notification_* org-uniques take the shadow route today; post-fix generation expressions read back 'unhex(sha2(concat(coalesce(organization_id,__global__),0x1f,...),256))' from information_schema, and duplicate NULL-org admin-global rows are refused. (5) Gate union at 1b04af6506, each exit captured before any pipe, all exit 0: check:changeset-gate-self-tests, check:cross-package-test-inputs, check:driver-conformance, check:objectql-double-limit, check:objectui-changeset, check:page-declaration-shape, check:pm-half-states, check:published-files, check:slot-lookup, check:test-source-alias, check:type-source-resolution, check-adr-0087-registration, check-changeset-no-major, check-ci-filter-parity, check-comment-mask-adoption, check-cross-package-test-inputs (ci.yml spelling), check-empty-changeset, check-keyed-text-bounds (newly landed on main today, matched my diff, run), check-plugin-teardown-shape, check-undeclared-dep-imports, docs-audit/check-affected-docs, docs-audit/check-drift-comment, pm/release-rehearsal-clone --self-test, check:query-options-erasure, check:type-check-coverage, check:type-check-debt (workspace closure built first, 70/70), check:engine-double-contract, check:where-matcher, check:dispatcher-error-vocabulary, check:nul-bytes. One NOT MEASURED (not red): scripts/pm/check-half-states.mjs exit 3 PREREQUISITE NOT MET — no GitHub credential on this seat (its own output: 'no reading at all'); CI's half-state-patrol owns that sweep with credentials. Derivation was re-run after each of two origin/main merges until non-stale; families were derived by the script from its own merge-base changeset, not from a hand-fed diff. Declared narrowing: repo-wide 'pnpm lint' (eslint . --no-inline-config) left to CI as the CI-owned run.",
  "mcp_calls": "5 — search_issues (dedup, non-empty so no control needed), get_label (finding label existence), issue_write (filed #13015), create_pull_request (#13016), add_issue_comment (this report)",
  "open_questions": [],
  "out_of_scope_findings": [
    "filed as #13015: drift differ reads a healthy #11627 shadow-carried UNIQUE as destructive recreate_index drift (physical key is the shadow column, never matching the declared columns), and the proposed remedy fails on the surviving generated column, ending with the constraint dropped — pre-existing, measured live, differ untouched by this diff"
  ]
}

Generated by Claude Code · session_01CPrUz21stTFhJRUirdc4yw

---

Generated by Claude Code