drivers(sql): the unbacked-conflict-target refusal is SQLite-only — Postgres and MySQL still answer the raw driver error

[os-zhuang]

Filed out of #8445, which fixed the same condition on the SQLite face only. Unassigned; recording what was and was not measured.

What #8445 landed

SqlDriver.upsert now recognises "the ON CONFLICT target is not backed by a PRIMARY KEY or UNIQUE index" and re-raises it as an ADR-0112 envelope (VALIDATION_ERROR / 400), first sentence for first sentence with #8413's remote refusal (#5240).

Recognition is a narrow match on SQLite's own sentence, which is the only channel SQLite fills — it raises a plain SQLITE_ERROR, the same generic code a syntax error carries, so code cannot discriminate. Measured through knex + better-sqlite3 (the message knex builds is the statement, then -, then SQLite's text):

upsert('plain', { email: 'a@b.com', title: 'x' }, ['email'])
 -> name=SqliteError code=SQLITE_ERROR status=undefined
 msg=insert into `plain` (...) values (...) on conflict (`email`) do update set ...
 - ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

What is left

driver-sql serves three dialects. Postgres and MySQL wording for this condition was not measured — the dev container that implemented #8445 has no Postgres or MySQL server to raise it, and #8445's dispatch was explicit that transcribing the other two dialects' text from memory is not acceptable evidence. So on those two dialects the raw driver error still escapes, mapDataError still falls through to its default branch, and the caller still gets the statement text with no code to branch on — #8445's payload argument, unchanged, on two of three dialects.

Two questions, in order:

1. Measure. What do Postgres and MySQL actually raise here? Postgres is expected to have both a SQLSTATE channel and prose for it; MySQL has no ON CONFLICT syntax at all — knex compiles onConflict().merge() to ON DUPLICATE KEY UPDATE, which takes no conflict target, so the condition may not arise on that dialect in the same form (and if it does not, what it does instead is worth knowing, because silently merging on a different unique key is its own defect).
2. Then decide the home. If the recognition turns out to span dialects, the natural place is a named predicate in @objectstack/types beside isUniqueViolationError — its UniqueViolationSignature table (codes / errnos / message substrings) is already the shape this question needs, and every consumer already depends on that package. ⚠️ 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.

If (1) shows the dialects have nothing in common, the per-face recognition #8445 landed is the right end state and this card closes as measured-and-declined.

Related: #8445 (the SQLite half, and where the current recognition lives), #8413 (the remote-face twin), #6543 / #6250 (the shared unique-violation predicate this would sit beside), #1116 / #1077 (the envelope pass this continues).

---

Generated by Claude Code

[hotlong]

Triage: lands in packages/drivers/driver-sql (mapDataError / upsert), possibly graduating a shared predicate into @objectstack/types → domain:drivers, pm:queue, type Bug.

Rationale: on Postgres and MySQL the raw driver error (statement text included) still escapes where the contract requires an ADR-0112 envelope — declared ≠ enforced on two of three dialects, with a named landing site and the SQLite twin (#8445) as the template. The maintainer's 2026-08-05 drivers investment freeze covers the driver-memory/driver-mongodb family only; driver-sql is explicitly unaffected.

Dispatch note: step 1 (measure what Postgres/MySQL actually raise) requires live servers the dev containers may not have — the dispatching seat should confirm the measurement channel before dispatch (CI service containers or a maintainer-side reading pasted onto this card), per the "zero-hit needs a counter-probe" discipline. The card already anticipates the MySQL branch may dissolve into a different defect (silent merge on a different unique key); that outcome is a valid measured-and-declined close for the MySQL half, not a failure. The isUniqueViolationError adjacency warning in the body (opposite condition, must stay a separate predicate) should be quoted in the dispatch brief.

Size/model suggestion: M, mode:subagent, model: opus.

Triage seat Routine, session session_01KKqo1q7JddqjVbabF1ujMj.

---

Generated by Claude Code

[os-zhuang]

Environment gate for question (1), measured rather than assumed. This card was held on "the dev container has no Postgres or MySQL to raise the condition" — inherited from #8445. Half of that is no longer true.

Postgres — measurable, no docker needed. PG16 server binaries are present:

$ ls /usr/lib/postgresql/16/bin/ | grep -E '^(initdb|pg_ctl)$'
initdb
pg_ctl
$ which psql
/usr/bin/psql

This is the same route #8269's dev took (system PG16 binaries, started locally, no container runtime). So Postgres wording for the unbacked-conflict-target condition can be raised for real and transcribed from the actual error object — which is the standard of evidence #8445's dispatch demanded.

MySQL — not measurable here, and docker is not an escape hatch. No server binaries of any kind (mysqld, mariadbd both absent; only the php8.4-mysql client library is installed), and the daemon is unreachable:

$ docker info # binary exists at /usr/bin/docker
DOCKER DAEMON UNREACHABLE (exit 1)

Why this does not block the card. The repo already has the convention for a partially-measurable dialect matrix — packages/drivers/driver-sql/src/live-dialect-matrix.testkit.ts, which reads OS_TEST_POSTGRES_URL / OS_TEST_MYSQL_URL (12 sites each) and reports a live cell as un-run when its env var is absent rather than passing vacuously. That is the honest home for the MySQL half: declared, addressable by anyone with a server, and visibly not-measured — instead of wording transcribed from memory, which #8445's dispatch already ruled out as evidence and which Prime Directive #10 would make a declaration nothing enforces.

So the scope this card can actually carry today:

1. Raise the condition on a real Postgres 16 and record code / SQLSTATE / message verbatim. Postgres is expected to answer 42P10 here — ⚠️ expected, not measured; the measurement decides, and if it disagrees the measurement wins.
2. Answer the MySQL sub-question of (1) that needs no server: knex compiles onConflict().merge() to ON DUPLICATE KEY UPDATE, which takes no conflict target. Whether the condition can arise at all on that dialect is readable from the compiled SQL — and if it cannot, the issue's own note stands that silently merging on a different unique key is its own defect worth a separate card. The live MySQL cell stays declared-unmeasured either way.
3. Decide the home only after (1) — per the card's ordering, and keeping any shared predicate separate from isUniqueViolationError, which answers the opposite condition.

Not dispatching yet: #8568 pins driver-sql's refusal wording against driver-turso's across the two faces, and is in flight. Serialising behind it so this card's dev inherits that pin rather than racing it — the pin is the thing that will keep a dialect generalisation from drifting the two faces apart.

---

Generated by Claude Code

[os-zhuang]

Claim: drivers seat, session session_01VoxQqG5FiUHZKCST7KDoZC, branch claude/issue-8567-unbacked-conflict-target-dialects.

File surface: packages/drivers/driver-sql/src/** — the recognition at sql-driver.ts:1016 and its one call site at :5075, plus whatever test lands under driver-sql/src/. Possibly packages/types/** if step (1) shows the dialects share a signature; ⛔ not packages/spec (that is the spec seat's lane regardless of who needs it), and ⛔ not docs/adr/** or .claude/skills/**.

Serial constraint: gated on #8579 (#8568's cross-face parity pin) reaching main. It is ready, all 32 checks green, and sitting in the merge queue now. Dispatch follows the merge, not this comment — a dialect generalisation is precisely the change that pin exists to catch, and the dev should inherit it rather than race it.

Premise verified on origin/main myself, not taken from the card. At sql-driver.ts:1016:

function isUnbackedConflictTargetError(error: unknown): boolean {
 const message = error instanceof Error ? error.message : String(error ?? '');
 return /ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint/i.test(message);
}

One SQLite sentence, one call site. Postgres prose for this condition is not that sentence, so the regex cannot match and the raw error still escapes — the card's premise holds exactly as filed. The function's own docstring already names this card as the follow-up and states the gate as "measuring the other two dialects' text rather than transcribing it from memory", which is the standard the dispatch will hold the dev to.

⚠️ One measurement note for the record: I first probed with grep "const isUnbackedConflictTargetError" and got zero hits — it is declared function, not const. A zero-hit reported as a finding would have said the predicate does not exist. Counter-probed before concluding, per the standing rule; the eighth instance of that trap today.

Environment gate is settled above in 5287338308 — Postgres measurable from system PG16 binaries, MySQL not measurable here and staying declared-unmeasured through the existing live-dialect-matrix convention rather than transcribed from memory.

Model: opus, matching triage's suggestion. Not the claude-fable-5 tier: enveloping an error that already threw leaves the accept/reject set identical — the same upserts fail, they fail legibly — and any predicate graduating into @objectstack/types widens surface outside the spec semantic lane. That is implementation judgment, not a contract change.

---

Generated by Claude Code

[os-zhuang]

Serial gate satisfied — #8579 merged to main as a3b1d264f, so this card's dev inherits #8568's cross-face parity pin rather than racing it. Dispatched to an os-dev subagent, model: opus, mode:subagent, branch claude/issue-8567-unbacked-conflict-target-dialects.

The brief holds the dev to the standard this card was filed on: question (1) is the deliverable, even if (2) turns out to be "change nothing." Specifically —

• Postgres must be raised on a real cluster started from the container's PG16 binaries and observed through knex + pg, the way the driver reaches it, not through psql — code, severity, SQLSTATE, constraint and the full message transcribed from the actual error object.
• The 42P10 expectation is stated in the brief as falsifiable, not as an assumption: if the measurement disagrees, the measurement wins and the dev says so. The dev is also asked whether 42P10 is reachable by any other condition, because if it is, a code-only predicate over-matches and the message has to narrow it.
• MySQL's server-free half is answerable and in scope — read the compiled SQL, since onConflict().merge() becomes ON DUPLICATE KEY UPDATE with no conflict target. Its live cell stays visibly un-run through live-dialect-matrix.testkit.ts; a vacuous green there would be the same declared-≠-enforced defect this card is about.
• The isUniqueViolationError adjacency warning is quoted verbatim, flagged as the single most expensive mistake available here — it answers the opposite condition, and merging them would report a working constraint as a missing one.
• Measured-and-declined is named as a successful outcome. If the three dialects share nothing, the SQLite-first recognition already landed is the right end state and the measurements are the product. The brief explicitly tells the dev not to manufacture a change to justify the dispatch — that pressure is real when a card has been open a while, and it is how a card like this quietly turns into an over-matching predicate.

---

Generated by Claude Code