drivers(turso): the remote face drops unique entirely — a declared-unique column takes duplicates, and an upsert on conflictKeys dies with a raw SqliteError #8413

Filed unassigned from the #7099 dispatch (PR #8412) — found while reading RemoteTransport.upsert, out of that card's scope, not fixed there. Measured on origin/main @ 75fd301.

What was measured

One object, email declared { type: 'string', unique: true }, initialized through TursoDriver on each face in turn.

REMOTE — the DDL the transport actually issues:

CREATE TABLE "crm_contact" ("id" TEXT PRIMARY KEY, "created_at" TEXT DEFAULT (datetime('now')),
  "updated_at" TEXT DEFAULT (datetime('now')), "email" TEXT, "title" TEXT)

No UNIQUE constraint, and no companion unique index — sqlite_master has nothing else for the table. grep -n 'unique|UNIQUE' packages/drivers/driver-turso/src/remote-transport.ts returns zero hits across the whole 3059-line file, so this is not a fixture artifact: the remote DDL builder has no notion of the key at all.

Consequence 1 — declared uniqueness is not enforced on this face:

REMOTE duplicate on a declared-unique column: ACCEPTED — duplicate landed
LOCAL  duplicate on a declared-unique column: REJECTED — UNIQUE constraint failed: crm_contact.email

Same driver, same object definition, opposite answers.

Consequence 2 — upsert with conflictKeys cannot work on remote, and fails un-enveloped:

REMOTE upsert(obj, data, ['email'])
  -> THREW name=SqliteError code=SQLITE_ERROR status=undefined
     msg=ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

RemoteTransport.upsert builds ON CONFLICT("email") DO UPDATE, and SQLite requires the conflict target to be backed by a PRIMARY KEY or UNIQUE index. Since the DDL never creates one, every conflictKeys upsert on a business key raises a raw driver error — SQLITE_ERROR, no status, so it is not an ADR-0112 envelope either.

Why this is worth a card

This is Prime Directive #10's shape exactly — a capability the metadata declares that the runtime does not deliver — and #6203's "a fix that lands on one face is two answers", on the same driver that has already paid for that lesson twice (#5903 for $not NULL-safety, #5769 for $-operator keys). Uniqueness is the load-bearing kind: a remote deployment that believes its unique declarations is accumulating duplicates silently, and no read reports it.

It also narrows a neighbouring card's framing: #7099 is titled for the "id or conflictKeys" upsert leg, but on remote only the id half is reachable today — the conflictKeys half throws before it can merge or insert. (PR #8412 covers the leg as specified; this is the reason its coverage is effectively the id half.)

Not proposing the disposition

Whether this is "implement unique in the remote DDL", "refuse conflictKeys on remote with a proper NOT_IMPLEMENTED envelope until it is", or both, is a triage call — the two consequences above may well warrant different answers, and the second is cheap while the first touches schema sync and existing tables. Note also that no test in packages/drivers/driver-turso exercises conflictKeys at all today (grep conflictKeys src/*.test.ts is empty), so whichever way it goes, it starts uncovered.

Related: #7099 / PR #8412 (the leg this was found under), #6944 (the refusal gate), #6203, #5903, #5769.

Generated by Claude Code