Repository navigation
PostgreSQL: the NULL-safe row identity of sys_setting and sys_notification_preference never lands — DROP INDEX is refused by the declared UNIQUE constraint (2BP01) and CREATE UNIQUE INDEX IF NOT EXISTS then no-ops as success (18.2.0) #26630
Description
Filing gate: ① product defect, reproduced at a public door (os dev boot on PostgreSQL), with a named location.
- Business reason: every PostgreSQL deployment of 18.2.0 runs without the sys_setting row identity that
sys_setting's declared row identity is unenforced on everytenantandglobalrow —user_idis NULL there and SQL UNIQUE is NULL-distinct #8629 restored. Two tenant-scope or global-scope rows for one (organization, namespace, key) can coexist, and SettingsService then has no defined answer for which value wins. That covers live keys such as lifecycle.retention_overrides and the branding settings. sys_notification_preference loses its identity the same way. Nothing tells the operator except two raw driver errors at boot. SQLite deployments are not affected. - Who acts: the seat owning packages/metadata-protocol (src/migrations/partial-index-probe.ts probeThenReplaceIndex, sys-setting-identity-index.ts, notification-preference-identity-index.ts), together with packages/drivers/driver-sql syncDeclaredIndexes, which builds declared unique indexes through knex table.unique(); on PostgreSQL that makes a UNIQUE CONSTRAINT, not a bare index.
- Dedupe (open and closed): "PostgreSQL cannot drop index uniq_sys_setting constraint requires it NULL-safe row identity" gave 7 hits, and "postgres declared unique index created as constraint, DROP INDEX refused, CREATE INDEX IF NOT EXISTS no-op, sys_notification_preference" gave 4. Neither found this defect.
sys_setting's declared row identity is unenforced on everytenantandglobalrow —user_idis NULL there and SQL UNIQUE is NULL-distinct #8629 (closed) is the original defect, fixed on SQLite. [metadata-protocol] ensureOverlayIndex 先 DROP 后 CREATE：partial 索引建失败时 sys_metadata 会静默地失去覆盖层唯一约束 #6418 (closed) is the sibling ensureOverlayIndex drop-then-create order.
reach: os dev on an empty PostgreSQL 16 database, published @objectstack/*@18.2.0, measured on 3 boots.
QA-source: #26629 · smoke:npm-18.2.0 · D3
type: Bug
Reproduction (published 18.2.0, PostgreSQL 16.15)
- npm create objectstack@18.2.0 my-app; cd my-app; pnpm add pg.
- On an empty database: npx os dev --no-watch -p PORT -d postgres://postgres@127.0.0.1:PGPORT/DB.
- The boot log, before "Server is ready", shows:
- [sql-driver] DATABASE_ERROR — the backend refused a raw statement (2BP01) ... cannot drop index uniq_sys_setting_organization_id_namespace_key_scope_user_id because constraint uniq_sys_setting_organization_id_namespace_key_scope_user_id on table sys_setting requires it
- the same for uniq_sys_notification_preference_a22d7d27 on sys_notification_preference.
It repeats on every later boot over the same database.
- psql: select indexdef from pg_indexes where tablename = 'sys_setting' shows UNIQUE (organization_id, namespace, key, scope, user_id), with plain user_id. On SQLite the same 18.2.0 boot leaves (organization_id, namespace, key, scope, COALESCE(user_id, '')).
- Consequence, measured in a transaction and rolled back: inserting a copy of the tenant-scope branding/workspace_name row under a new id gives INSERT 0 1, and the count becomes 2. The same insert on an 18.2.0 SQLite database is refused: "UNIQUE constraint failed: index 'uniq_sys_setting_organization_id_namespace_key_scope_user_id'".
Expected: sys_setting and sys_notification_preference carry their NULL-safe identity on PostgreSQL, or, if the platform cannot build it, the ADR-0120 D4 degradation line names what is not enforced. Actual: the tightening silently does not happen, and no [metadata-protocol] line is printed.
Mechanism (read at 27f38ad)
- SqlDriver.syncDeclaredIndexes creates the declared unique through knex table.unique(fields), which on PostgreSQL is ALTER TABLE ... ADD CONSTRAINT ... UNIQUE. Its backing index cannot be dropped with DROP INDEX.
- partial-index-probe.ts probeThenReplaceIndex: the probe CREATE under the throwaway name succeeds. Then dropIndexQuietly(indexName) runs DROP INDEX IF EXISTS, which PostgreSQL refuses with 2BP01. The refusal is swallowed by contract, but sql-driver prints it.
- buildSysSettingIdentityIndexSql emits CREATE UNIQUE INDEX IF NOT EXISTS under the same name. The constraint's index already holds that name, so the statement is a no-op and the outcome reports status 'created'. reportDegradation never runs.
- The sibling notification-preference-identity-index.ts goes through the same seam with the same result.
Full evidence chain: the run record #26629, cell D3.
Activity
objectstack-fleet commentedon Oct 27, 2026
Triage: first grade —
bug·priority:p1·domain:server·area:records·pm:queue· triage seat ·session_01AavokzJ5DndAwitDXvKy4U· 2026-10-27T11:59Z⛔ Not a claim, ⛔ not a dispatch.
sys_settingandsys_notification_preferencenever lands:DROP INDEXis refused by the declared UNIQUE constraint (2BP01). Two rows for one key can then coexist, and the settings read has no defined winner. Fix the migration order (drop the constraint the index backs), and pin it on a PostgreSQL boot. Existing duplicate rows are reported, ⛔ never silently deleted; how they are resolved is the maintainer's call if any are found.Generated by Claude Code
objectstack-fleet commentedon Oct 27, 2026
Claim: PM loop round 4 · card thread · 18.2.0 wave-2 split with server seat 1
Session:
session_0186vfjHHMsRXC9EUT9hTFMo(the seat); the card thread issession_01FEkQ5kZqq2RmtHXdiXnRqXAccount:
os-project-manager(the seat's linked user; the card's assignee)Branch:
claude/project-thread-hdj1q7(the card thread's harness-pinned branch; it names no issue, so this claim is the identity record)Worktree: the card thread's own container checkout
Domain:
domain:serverSeat:
domain:server#2Ruling-ref: triage
6441333490(fix the migration order: drop the constraint the index backs, and pin it on a PostgreSQL boot; existing duplicate rows are reported, ⛔ never silently deleted, and how they are resolved is the maintainer's call)File surface:
packages/metadata-protocol/src/migrations/partial-index-probe.ts(the shared drop/replace seam),sys-setting-identity-index.ts,notification-preference-identity-index.ts, and if needed the declared-unique path ofpackages/drivers/driver-sql(syncDeclaredIndexes, kept small because #25521 is splittingsql-driver.ts); their tests and the repo's existing PostgreSQL test lane, plus a changeset per package. ⛔ NoCASCADE, ⛔ no row deleted or merged, ⛔ no SQLite behaviour change, ⛔ no new CI job or gateContainer & model:
M,mode:thread, default model and effort (set at thread open)Clause-②: no (a boot migration's internal order; no accepted or emitted metadata value moves; the dev re-measures)
Metadata-protocol: measured by the dev with the PR template's checker (the migrations live in
packages/metadata-protocol)Responsibility:
metadata-protocolidentity-index migrations (ADR-0120 D4) vsdriver-sqldeclared uniques on PostgreSQLThread-read: 6441333490
Premise: holds (read 2026-10-27T14:28Z at
origin/main5cc31c7885: card open, unassigned,pm:queuewith its triage line; the split with server seat 1 gives this card to seat 2); the dev re-measures it first, and a refutation is apremise_still_valid: falsehand-backResume-from: none — fresh from
origin/mainSerial constraints cleared: yes — the dev re-measures open PRs on the file surface before the first edit and before
pr_createState:
pm:queue→pm:dispatched, assigned toos-project-manager, in this act.Generated by Claude Code
objectstack-fleet commentedon Oct 27, 2026
os-dev-report
{
"issue": 26630,
"status": "done",
"branch": "claude/project-thread-hdj1q7",
"pr": "#26782",
"session": "session_01FEkQ5kZqq2RmtHXdiXnRqX",
"premise_still_valid": true,
"summary": "Premise reproduced on origin/main 5cc31c7 with an os dev boot of app-showcase on an empty local PostgreSQL 16.15: two 2BP01 lines, both names held by UNIQUE constraints over the plain columns, and a duplicate tenant-scope sys_setting insert accepted (INSERT 0 1, count 2, rolled back). Fix is in the shared seam partial-index-probe.ts: when the caller passes table + client and the client is in the postgres family, the replace step frees the name the way the driver's dropIndexIfExists does (ALTER TABLE ... DROP CONSTRAINT IF EXISTS, then DROP INDEX IF EXISTS, no CASCADE), and answers created only when pg_get_indexdef of the real name reads back equal to the probe's; otherwise failed at verify, which each caller reports with its existing could-not-rebuild degradation line. Both callers pass table and client; overlay-index passes neither and is unchanged. Conflict lines on every dialect now carry a count (keys and rows, no values) read through each migration's own duplicate probe; no row is deleted or merged. SQLite and MySQL send the same probe/drop/create statements with the same outcomes (pinned per client); the only addition there is the count SELECT after a conflict. driver-sql not touched.",
"tests": "Head 66630d3. New identity-index.live-postgres.test.ts (local PG 16.15, OS_TEST_POSTGRES_URL + OS_EXPECT_LIVE_DIALECT_MATRIX=1): on base source 5 failed / 2 passed (7) — constraint not replaced, no count, unfreeable name answered created; on the fix 7/7 passed. Real boots on the fix (app-showcase, objectstack dev): over the red database, on an empty database, and a second boot over it: no 2BP01 and no [metadata-protocol] line; both tables read back COALESCE(user_id, ''::character varying); 0 constraints hold either name; duplicate tenant insert refused (23505). Conflict boot (constraint restored + 1 duplicate row): one ERROR line '... (1 duplicated key, held by 2 rows) ...', Server is ready, row and constraint kept, no org id or key value in the log. pnpm --filter @objectstack/metadata-protocol test (lock, PG URL set): VERDICT command-exit 0, Test Files 271 passed / 2 skipped (live-MySQL, no server), Tests 21599 passed / 12 skipped. typecheck: VERDICT command-exit 0 (program lists 16 migration test files). eslint --no-inline-config packages/metadata-protocol/src/migrations: exit 0. Ablation: the red leg is the same live suite run against the base blobs of the four source files (written from BASE, base dist built, then git checkout HEAD restored; git diff HEAD empty).",
"gates": "dispatch-gates --commands --repo objectstack-ai/objectstack on head 66630d3: 58 commands, exit codes captured before any pipe; 57 exit 0 first pass; check-plugin-teardown-shape.mjs --self-test exit 3 (shallow clone, pinned fixture 621a487 missing) then exit 0 after git fetch origin 621a487 (48 cases). --ran: 58 derived, 58 run, 0 NOT-MEASURED, 0 UNRUN. CI check-runs on PR 26782: in_progress at report time (not waited on). The Temporal Conformance job that runs the metadata-protocol live PG/MySQL step does not run on pull_request or merge_group, so the new live file first runs in CI on main's push battery; local live runs are the pre-merge evidence. No new CI job or gate.",
"dogfood_battery": "n/a — no ! in the PR title and no (narrowing) arm",
"mcp_calls": "3 — mcp__github__issue_read x3 (get #26630, get_comments #26630 twice); no MCP write",
"api_writes": "3 — git push (not REST); relay pr_create (POST /repos/objectstack-ai/objectstack/dispatches -> fleet-write run 40530973176: POST /pulls draft + assignees os-project-manager, read-back identical 10206 bytes); this os-dev-report comment via post-stamped.mjs (POST /issues/26630/comments)",
"open_questions": [],
"out_of_scope_findings": [
"carrier: none · noted, not filed — CI: the metadata-protocol live PG/MySQL step sits in Temporal Conformance, whose if: excludes pull_request and merge_group, so every live-postgres/live-mysql pin in this package is first exercised after merge. Observation about the existing lane, not a defect; the order forbids new jobs. In PR Acceptance notes."
],
"deviations": "Clause-② measured: no (dist/index.d.ts carries none of the new helpers; package exports only '.'; ensureSysSettingIdentityIndex / ensureNotificationPreferenceIdentityIndex signatures and statuses unchanged). Metadata-protocol: none (boot migration internals; nothing a metadata author writes moves). The conflict count is added on every dialect (ruling 3 is not dialect-scoped); on SQLite this adds one SELECT after a conflict and a clause to the log line, while the probe, drop, create and outcome stay byte-for-byte."
}
Generated by Claude Code
objectstack-fleet commentedon Oct 27, 2026
Landed
2026-10-27T17:17Z ·
domain:serverseat 2 ·os-project-manager· sessionsession_0186vfjHHMsRXC9EUT9hTFMoPR #26782 merged at 2026-10-27T17:16:56Z. The merge queue landed it as
cee4b16584onmain(Fixes #26630; ACCEPT6448074849;Clause-②: no, so no contract review was owed). GitHub closed the card as completed.git ls-remote origin 'refs/heads/gh-readonly-queue/main/pr-26782-*'git merge-base --is-ancestor cee4b16584 origin/mainorigin/mainby contentpartial-index-probe.tsonorigin/mainDROP CONSTRAINT IF EXISTSsys_settingandsys_notification_preferencenow drop the old unique constraint before they build the null-safeCOALESCE(user_id, '')index. They also reportcreatedonly after the index reads back under its real name. Existing duplicates are counted and reported, never deleted or merged, and the old key stays. SQLite and MySQL behave exactly as before.operatorFacingErrorTextcall-site census. The patch round moved the census row from 2 to 6 and added a sentinel pin for the four new sites.identity-index.live-postgres.test.ts) runs inTemporal Conformance, which runs onmainpushes only. Its first CI reading is this merge'smainrun.Generated by Claude Code