types: `isUniqueViolationError` claims SQLite's UNBACKED-conflict-target error — a superstring collision on "UNIQUE constraint", the exact inversion the predicate's own warning names

[os-zhuang]

Found while implementing #8567 (measuring the unbacked-conflict-target condition across dialects). Filed unassigned; not fixed there — the fix moves a shared predicate's verdicts, which is its own blast radius.

The defect

isUniqueViolationError (packages/types/src/unique-violation.ts) answers true for the error SQLite raises when an ON CONFLICT target has no backing unique index — i.e. for the exact condition its own docstring warns it must never claim:

⚠️ It must stay a separate predicate: isUniqueViolationError answers the OPPOSITE condition (a unique index exists and the row violated it), and merging them would report a working constraint as a missing one.

Measured on real driver errors (not fixtures), knex 3.3.0, @objectstack/types built from a3b1d264f:

===== sqlite (better-sqlite3) =====
UNBACKED-target error:
 msg = "insert into `xtalk_plain` (`email`, `id`, `title`) values ('a@b.com', '1', 'x')
 on conflict (`email`) do update set `title` = excluded.`title`
 - ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint"
 isUnbackedConflictTargetError = true
 isUniqueViolationError = true <== WRONG, should be false

===== pg (PostgreSQL 16.13) =====
UNBACKED-target error:
 msg = "insert into "xtalk_plain" (...) values ($1, $2, $3)
 on conflict ("email") do update set "title" = excluded."title"
 - there is no unique or exclusion constraint matching the ON CONFLICT specification"
 isUnbackedConflictTargetError = true
 isUniqueViolationError = false <== correct

Cause

The vocabulary's message limb is

message: /unique constraint|unique violation|duplicate key|duplicate entry/i

and SQLite's sentence for the missing-index condition ends ...any PRIMARY KEY or UNIQUE constraint. The words UNIQUE constraint sit adjacent inside it, so the unique constraint limb matches a sentence that says the constraint is absent.

Postgres escapes only by luck of word order: its unique or exclusion constraint is not adjacent, so the same limb misses it. That asymmetry is the tell — the limb is matching a word pair, not a condition.

This is the same bug class relation-sub-object.ts already documents for a different phrase pair: "Postgres' missing-COLUMN spelling contains a legal missing-TABLE phrase as a substring. Three packages had each repaired that superstring hole separately." Same shape, different vocabulary, and the one that has a named module was fixed centrally.

Reachability today: latent, not live — and worth fixing anyway

SqlDriver.upsert is the only site in driver-sql that compiles a caller-supplied conflict target (grep -n "onConflict(" returns exactly one hit), and #8445's recognition runs first in that catch, so the raw error is enveloped before any unique-violation consumer sees it. The remote face does the same (#8413). So no user-visible 409-instead-of-400 is reachable through the upsert path as it stands.

Two reasons that is not a close:

1. A comment in sql-driver.ts states the opposite as fact and reasons from it. At the upsert catch:

   // It is not an autonumber collision — `collidingAutoNumberReservations`
   // asks `isUniqueViolationError`, which is false for this error, so the
   // reservation probe would query the sequences table for nothing and
   // then rethrow the raw error anyway.
   

   It is true for this error, measured above. The ordering is therefore load-bearing in a way the comment says it is not: move the recognition after the retry branch and the reservation probe really does run. (drivers(sql): the unbacked-conflict-target refusal is SQLite-only — Postgres and MySQL still answer the raw driver error #8567 corrects the comment in place; it does not touch the predicate.)

2. Six other packages consume the predicate — rest-server.ts (the 409 UNIQUE_VIOLATION mapping), objectql/engine.ts, rest/import-runner.ts, service-messaging, metadata-protocol/partial-index-probe.ts. Any of them reached by a raw unbacked-target error gets told a value collided when nothing was ever compared, and 409 is a status an SDK will not retry.

What a fix has to be careful about

Narrowing the unique constraint limb changes verdicts of a predicate whose whole point (#6250) was that four divergent copies disagreed. The limb is inherited verbatim from the REST branch it replaced and covers both SQLite's UNIQUE constraint failed: t.c and Postgres' violates unique constraint "...", so it cannot simply be deleted. A negative-lookahead on the missing-index sentence, or requiring the limb to match a violation phrasing (constraint failed, violates), are both plausible — this needs the same measured-per-dialect treatment #8567 gave the other predicate, including MySQL's Duplicate entry path, before anything is changed.

packages/types/src/unbacked-conflict-target.test.ts (landing with #8567) already pins the current behaviour in both directions and names this issue, so whichever way the fix goes, the pin points at itself rather than going quietly green.

Related: #8567 (where this was measured), #6250 / #6543 (the predicate's consolidation), #6615 (the same superstring bug class, already fixed once for a different phrase pair), #8445 / #8413 (the two faces whose ordering currently masks it).

[hotlong]

Triage: lands in packages/types/src/unique-violation.ts → pm:queue + domain:cli (the types package is the cli lane's), type Bug — the predicate answers true for the exact condition its own docstring forbids it to claim (declared ≠ enforced, measured on real driver errors on two dialects).

Rationale for queue rather than the decision box: the fix direction is implementation judgment (negative lookahead vs violation-phrasing requirement), to be settled by the same measured-per-dialect method #8567 used — no product-semantics fork, no public-contract change. The predicate is internal shared infrastructure; its consumers' behaviour moving from wrong to right is the point of the card.

Dispatch notes for the cli seat:

• Serial constraint (hard): drivers(sql): the unbacked-conflict-target refusal is SQLite-only — Postgres and MySQL still answer the raw driver error #8567 is in flight and lands packages/types/src/unbacked-conflict-target.test.ts (which pins this card's current wrong behaviour and names this issue) plus the sql-driver.ts comment correction. Land this card after drivers(sql): the unbacked-conflict-target refusal is SQLite-only — Postgres and MySQL still answer the raw driver error #8567's PR merges, and expect to flip that pin's direction rather than delete it.
• The fix must be measured per dialect including MySQL's Duplicate entry path before the limb is narrowed — the card's own caution, adopted as a dispatch condition.
• Verify the six consumer packages' behaviour on the unbacked-target error after the change (rest-server.ts 409 mapping, objectql/engine.ts, rest/import-runner.ts, service-messaging, metadata-protocol/partial-index-probe.ts).

Size/model suggestion: M, opus — verdict change on a consolidated shared predicate (#6250 lineage), careful but not contract-shaped.

---

Generated by Claude Code

[qq9340100]

Claim: PM loop round 1
Session: session_01NaS1PAHJcPfAA2acnV53Tn
Branch: claude/issue-8590-unique-violation-superstring
Worktree: objectstack-issue-8590
Domain: domain:cli
File surface: packages/types/src/unique-violation.ts, packages/types/src/unbacked-conflict-target.test.ts (+ new tests in packages/types/src/) (stop on breach; explain in the report)
Container & model: M, mode:subagent, model: opus
Serial constraints cleared: the declared hard serial is discharged. Triage made this card serial behind in-flight #8567; verified on origin/main @ 3508678 that #8567 has landed — packages/types/src/unbacked-conflict-target.test.ts exists on main and carries its [#8567] ⚠️ separation from isUniqueViolationError describe block, with the pin at :206 asserting the current (wrong) true verdict and naming this issue. packages/types carries no in-flight claim. This seat's other two dispatches this round are #8275 (service-package + package DELETE arm) and #8328 (packages/mcp) — neither edits packages/types.

Dispatched under the maintainer's direct channel, verbatim instruction: 「identity ＋ services ＋ cli 优先处理 bug」.

⚠️ Cross-card awareness declared: this predicate's consumers include packages/rest/src/rest-server.ts (the 409 UNIQUE_VIOLATION mapping), and #8275 is in flight this round editing packages/rest/src/package-routes.ts. Different files, and this card must not edit packages/rest — verify consumer behaviour by reading and by test, not by editing. Any need to change a consumer package is a breach: stop and report.

---

Generated by Claude Code