driver-sql: SQLite introspectUniqueConstraints flags a single-column PRIMARY KEY as unique while PG/MySQL never do — and SQLite disagrees with itself by key type

Found while implementing #11202 (which unified the three arms on single-column-uniqueness semantics). This is the residual cross-dialect divergence in the same method, left deliberately untouched there because it is a semantic question rather than a mechanical one — the flag it produces is not false, only inconsistent.

What

SqlDriver.introspectUniqueConstraints reaches its answer from a different catalog per dialect, and the two catalogs disagree about whether a PRIMARY KEY is a unique constraint:

Postgres / MySQL filter on CONSTRAINT_TYPE = 'UNIQUE', which excludes primary keys outright. A primary key is never reported through this method on either dialect.
SQLite iterates PRAGMA index_list and keys only on idx.unique === 1, never on origin. SQLite materialises a non-INTEGER primary key as a unique auto-index, so the primary-key column is reported.

Measured on embedded better-sqlite3 (2026-08-24):

create table t_text (id varchar(64) primary key, email varchar(64) unique)
  PRAGMA index_list(t_text) ->
    { name: 'sqlite_autoindex_t_text_2', unique: 1, origin: 'u'  }   -- the UNIQUE(email)
    { name: 'sqlite_autoindex_t_text_1', unique: 1, origin: 'pk' }   -- the PRIMARY KEY(id)

create table t_int (id integer primary key, note varchar(64))
  PRAGMA index_list(t_int) -> []

So introspectSchema sets isUnique: true on t_text.id, and the same table introspected through Postgres or MySQL does not.

The second half: SQLite disagrees with itself

t_int shows the divergence is not even stable within SQLite. An INTEGER PRIMARY KEY is a rowid alias, so SQLite creates no auto-index and index_list is empty — the column is not flagged. A varchar primary key is flagged. The same logical schema therefore produces different isUnique flags depending only on the key's declared type.

Why it matters

The same consumer argument as #11202: the flag reaches introspectedSchemaToObjects in @objectstack/objectql, which turns it into a drafted field's unique: true, and from there into cross-dialect snapshot comparison and the ADR-0015 federated-object draft. A drafted object gains or loses a unique: true on its key column according to which dialect it was drafted from, and according to whether that key is an INTEGER.

Note the direction: unlike #11202 this flag is not lying — a primary-key column really is unique. What is wrong is that three dialects (four, counting SQLite's two cases) answer differently.

Decision needed

Should a PRIMARY KEY column carry isUnique?

A — never, matching the PG/MySQL arms: filter the SQLite arm on origin !== 'pk'. Primary-key membership is already reported separately and losslessly through primaryKeys / IntrospectedColumn.primaryKey, so nothing is lost, and isUnique becomes purely "unique constraint that is not the key". Mechanical: origin is already in the index_list rows the arm reads.
B — always, matching SQLite's varchar case: report a single-column primary key as unique on all three dialects. Truthful in the strict sense (a PK is unique), and arguably more useful to a consumer asking "can I address a row by this column alone" — but it means adding a primary-key read to the PG and MySQL arms, and it makes isUnique partly redundant with primaryKey.

A is the smaller change and keeps the two flags non-overlapping; B is defensible if any consumer wants "uniquely addressable" as one question. Either way the SQLite self-inconsistency between INTEGER and varchar keys should go.

Filed unassigned, per the finding rule. Not fixed in #11202's PR: the correct form is not pinned by existing evidence, so it fails the bounded in-place exemption's second condition.

Generated by Claude Code

Triage: cross-driver introspection-conformance family → domain:spec, type Bug, pm:queue — same lane and precedent as #11202 ("introspectUniqueConstraints answers a different question per dialect"), which landed today narrowing the producer to single-column uniqueness. This card is the next cell of that family: SQLite counting a single-column PRIMARY KEY as unique where PG/MySQL never do, and disagreeing with itself by key type.

⚠️ Premise check before dispatch: re-measure on the post-#11202 merged ref — that landing touched exactly this producer and may have absorbed or reshaped this cell (the finding #11655, filed against #11202's own doc, shows the surface moved today). If the inconsistency survives, the ruled convention from #11202 (what "unique" means to the introspection contract) decides which face is wrong — inherit it with its reason rather than re-opening the question.

Generated by Claude Code

Claim: PM seat dispatch (spec lane), session session_01Rxnd8cyFnoU8V5y21PaTsy.

Branch: claude/issue-11654-sqlite-pk-unique
File surface: packages/drivers/driver-sql/src/sql-driver.ts — the SQLite arm of introspectUniqueConstraints (PRAGMA index_list iteration; filter on origin) — plus its introspection tests and a changeset. ⛔ Not touching the canonical-datetime functions, the backfill SET side, or the upsert region (see H17 below).
Premise check first (triage fence 5395287447): re-measure on the post-driver-sql: introspectUniqueConstraints answers a different question per dialect — SQLite reports only single-column unique indexes, PG/MySQL report every member of composite constraints #11202 merged ref (packages/drivers at/after PR fix(driver-sql): introspectUniqueConstraints reports single-column uniqueness on all three dialects (#11202) #11657) — that landing touched this exact producer; if the cell was absorbed, stop with evidence (a documented zero-implementation stop is a good deliverable).
Fix direction: inherit driver-sql: introspectUniqueConstraints answers a different question per dialect — SQLite reports only single-column unique indexes, PG/MySQL report every member of composite constraints #11202's landed convention with its reason — isUnique means a declared single-column UNIQUE constraint, and PK membership is reported losslessly via primaryKeys/primaryKey — which prescribes option A (SQLite arm filters origin !== 'pk'), killing the INTEGER-vs-varchar self-inconsistency in the same stroke. ⛔ Do not re-open the A/B question; a contrary measurement = stop and report the fork.
Clause-②: no on the declaration limb (driver introspection behaviour, not packages/spec parse contract); no path limb either (diff stays in packages/drivers). Dispatch tier: opus.
H17 declarations (both holds name sql-driver.ts as a trigger file): drivers(sql): 共享 canonical 修复表达式把「裸数字文本」当儒略日读,backfill 会把这个误读写进盘里(观察项) #6009 — restart condition 2 is about sqliteCanonicalDatetimeSql/backfillCanonicalDatetimes, which this diff does not touch; drivers(sql): upsert's empty-merge-set fallback is merge-ALL, which re-admits every insert-only column — currently unreachable, but it is the wrong shape for "nothing to merge" #8740 — restart is about insertOnlyUpsertColumns/the upsert region, likewise untouched. Both intersections declared here; patch-scoped H17 notices (measured over changed lines per those cards' own recorded discipline) land on both when the PR lands.

Generated by Claude Code

fix(driver-sql): a PRIMARY KEY is not a unique constraint on the SQLi…

os-dev-report

{
  "issue": 11654,
  "status": "done",
  "branch": "claude/issue-11654-sqlite-pk-unique",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/11827",
  "premise_still_valid": true,
  "summary": "The cell was NOT absorbed by #11202/PR #11657 — re-measured at merge base dce6a019f through the driver on embedded better-sqlite3, and every reading the card gives reproduces: PRAGMA index_list(t_text) returns the origin 'u' row for UNIQUE(email) plus an origin 'pk' row for the varchar PRIMARY KEY, introspectUniqueConstraints(t_text) answered ['email','id'] (the PK column flagged) and introspectSchema set t_text.id.isUnique=true, while t_int (INTEGER PRIMARY KEY, a rowid alias) had an empty index_list and answered [] — the same logical schema giving a different isUnique from the declared type of its key alone. Implemented option A as prescribed, inheriting #11202's convention with its reason (isUnique means a DECLARED single-column UNIQUE constraint; PK membership is already reported losslessly via primaryKeys / IntrospectedColumn.primaryKey): the SQLite arm now skips origin 'pk' index rows, killing the cross-dialect divergence and the INTEGER-vs-varchar self-inconsistency in one stroke. The filter is on the INDEX's origin, never on whether the COLUMN is in the key — a key column separately carrying its own unique index really does have a declared single-column UNIQUE and stays flagged; that distinction is pinned because it fails under the wrong sibling implementation. Two shapes the card does not name were measured and changed the test design: a WITHOUT ROWID key still materialises a pk-origin auto-index and carried the identical defect (covered, pinned), and a composite PK was already dropped but only incidentally by #11202's width filter (pinned so this change is not credited with it). Diff is 3 files, +293/-1, entirely inside the introspection arm plus the two TSDoc contract sentences in the same file and a patch changeset. H17 fence verified clean: neither sqliteCanonicalDatetimeSql / backfillCanonicalDatetimes (#6009) nor insertOnlyUpsertColumns / the upsert region (#8740) appears anywhere in the diff — those symbols sit at lines 4007-9630 and 6529/7023 while the three hunks are at 3701, 14775 and 14887. PR #11827 is DRAFT, not flipped ready, auto-merge not armed.",
  "tests": "All readings at head SHA 66d9634df (working tree clean at commit time, so the tested tree IS the committed tree). REVERSE VERIFICATION, pinned first against unfixed source as dispatched — RED leg, full @objectstack/driver-sql suite with the new pins and the unmodified arm: 'Test Files 1 failed | 126 passed | 8 skipped (135)', 'Tests 4 failed | 1972 passed | 112 skipped (2088)', the four failures quoting the gates' own assertions: 'expected [ email, id ] to not include id' (PK flagged), 'expected true to be falsy' (the isUnique fold), and 'expected [ email, id ] to deeply equal [ email ]' twice (the INTEGER-vs-varchar agreement pin and the WITHOUT ROWID pin). GREEN leg after the origin filter: 'Test Files 127 passed | 8 skipped (135)', 'Tests 1976 passed | 112 skipped (2088)'. Exactly 4 tests moved, 1972 to 1976 passed, nothing else — and the four pins that must hold in BOTH directions (non-vacuity, the primaryKeys face, the composite key, the key-column-with-its-own-index case) were already green in the RED leg, which is what makes the red meaningful rather than a broken fixture. Fixtures prove the absence is real before asserting it: a repeated key and a repeated email are both REJECTED by the server. PER-PACKAGE: @objectstack/driver-sql test 127 files passed / 8 skipped, 1976 passed / 112 skipped; @objectstack/driver-sql typecheck exit 0 with 'tsc --noEmit' echoed (not a zero-match silent pass). CONSUMER CHECK: @objectstack/objectql src/util.test.ts — 1 file, 22 tests passed; that is the suite over introspectedSchemaToObjects, the flag's one real consumer, and its own fixture already models a key column as primaryKey:true with NO isUnique, so the consumer's test data had always encoded option A and no fixture triage was needed. GATES: node scripts/pm/dispatch-gates.mjs --repo objectstack-ai/objectstack run from the worktree AFTER the final commit; derivation line quoted verbatim: 'dispatch-gates: gate list derived from the tree of objectstack-ai/objectstack at commit 66d9634df' and 'change set derived from git — 3 path(s) vs merge base dce6a019f of origin/main and HEAD'. It named 14 path-derived families plus 6 convention-triggered (adds a test file); ALL 20 RUN, ALL exit 0, each exit code captured before any pipe. Verdict lines quoted from the gates themselves: 'check-driver-conformance: OK — 45 covered cell(s), 0 in the DEBT ledger, 0 exempt.', 'check-engine-double-contract: OK — 401 pinned, 133 in the DEBT ledger, 2 exempt.', 'OK: 16 package(s) read outside themselves, all declared', 'check-nul-bytes: OK (scanned 6593 text file(s) ... no raw ASCII control bytes).', 'check-where-matcher: 0 silently-wrong and 0 unjudged matcher(s) ... none new.', 'check-changeset-no-major: This diff introduces no major bump.'. TWO DECLARED NARROWINGS (declared, not silently skipped; CI runs the full farm regardless): (1) check:type-check-debt --re-measure not run locally — it re-runs tsc per LEDGER ENTRY and needs the whole workspace closure built; its population was read from the gate's own model rather than guessed: driver-sql carries no test-typecheck-debt.json and appears in NONE of the gate's DEBT / TEST_DEBT / EXEMPT / UNCHECKED_SOURCE_DEBT / PHANTOM_PIN_DEBT literals (only in its header's history prose as debt already paid), the structural half check:type-check-coverage passed, and the package's own tsc is green. (2) Repo-wide pnpm lint narrowed to the changed files, with all three evidence pieces so it is a measurement not a skip: population read from eslint's OWN config via ESLint#isPathIgnored / calculateConfigForFile (of 3 changed paths exactly 2 are in the linted population at 6 and 5 rules; the changeset .md is not linted at all), count from --format json (2 files linted, 0 errors, 0 warnings, exit 0), and the invariance claim from eslint.config.mjs's own text that this repo 'never enables type-aware linting (no parserOptions.project, no typed @typescript-eslint rules) for ANY file' — with no cross-file type information in play this diff cannot move the verdict on any untouched file. The container was saturated when measured (load 4.41 on 4 cores, three waiters queued behind an active build), where a repo-wide scan reliably meets the foreground cap and yields nothing. No ablation was performed: the reverse verification here is the pin-first RED/GREEN pair above, run against real source rather than a mutation, so there is no mutation-on-disk or rebuild leg to report. All heavy commands went through scripts/pm/os-verify-lock.sh; dependency closure built first with pnpm --filter '@objectstack/driver-sql^...' build (VERDICT command-exit 0).",
  "open_questions": [],
  "out_of_scope_findings": [
    "filed as #11826: objectql's consumer-side copy of the IntrospectedColumn.isUnique TSDoc in packages/objectql/src/util.ts under-specifies the contract now that PRIMARY KEYs are excluded — that declaration states in its own text that it 'must not drift from' the producer's sentence, and #11655 was filed and fixed for exactly this drift one round ago. Documentation only, no behaviour depends on it. Filed unassigned with the 'finding' label (matching #11655), NOT fixed in this PR: it fails the bounded in-place exemption's fourth condition, since packages/objectql/src pulls in gate families this diff otherwise never matches (check:durability-log-level, check-engine-split-ratio — the two PR #11657 measured when it amended that same file under a PM authorization), which is a new verification surface rather than a free ride."
  ]
}

Generated by Claude Code

Review (PM seat, session session_01Rxnd8cyFnoU8V5y21PaTsy): ACCEPT on PR #11827 at head 66d9634df.

Premise re-measured at the merge base as fenced: the cell was NOT absorbed by driver-sql: introspectUniqueConstraints answers a different question per dialect — SQLite reports only single-column unique indexes, PG/MySQL report every member of composite constraints #11202/PR fix(driver-sql): introspectUniqueConstraints reports single-column uniqueness on all three dialects (#11202) #11657 — every card reading reproduced through the driver on embedded better-sqlite3 (pk-origin auto-index row, ['email','id'] answer, the INTEGER-vs-varchar split).
The fix is exactly the inherited convention: the SQLite arm skips origin: 'pk' INDEX rows — a filter on the index's origin, never on column key-membership, and the t_pk_and_idx pin (key column with its OWN declared unique index stays flagged) is the assertion that fails under the wrong sibling implementation. Two shapes the card never named were measured and pinned: WITHOUT ROWID keys carried the identical defect (now covered), and composite keys were already dropped by driver-sql: introspectUniqueConstraints answers a different question per dialect — SQLite reports only single-column unique indexes, PG/MySQL report every member of composite constraints #11202's width filter (pinned so this change is not credited with it, and so it stays dropped if that filter is reworked).
Reverse verification is airtight: pins first, RED leg = exactly 4 failures quoting the assertions, GREEN leg = exactly those 4 flip (1972→1976), with the both-direction pins (non-vacuity, primaryKeys face, composite, own-index) green on BOTH legs. Consumer check: introspectedSchemaToObjects's suite green, and its fixture had always encoded option A.
Scope: 3 files, +293/−1, inside the declared surface; H17 fence verified over the patch (the drivers(sql): 共享 canonical 修复表达式把「裸数字文本」当儒略日读,backfill 会把这个误读写进盘里(观察项) #6009 datetime functions and drivers(sql): upsert's empty-merge-set fallback is merge-ALL, which re-admits every insert-only column — currently unreachable, but it is the wrong shape for "nothing to merge" #8740 upsert region appear nowhere in the diff — hunks at :3701/:14775/:14887 vs their :4007-9630/:6529/:7023). Patch-scoped H17 notices land on drivers(sql): 共享 canonical 修复表达式把「裸数字文本」当儒略日读,backfill 会把这个误读写进盘里(观察项) #6009/drivers(sql): upsert's empty-merge-set fallback is merge-ALL, which re-admits every insert-only column — currently unreachable, but it is the wrong shape for "nothing to merge" #8740 at merge, per the claim. 20 derived gate families all green locally; two declared narrowings with population-from-the-gate's-own-model evidence. Out-of-scope TSDoc drift correctly filed separately as [finding] objectql's consumer copy of IntrospectedColumn.isUnique under-specifies it after #11654 excluded PRIMARY KEYs #11826 (the [finding] objectql's derived IntrospectedColumn.isUnique doc still says "has a unique constraint" after #11202 narrowed the producer to single-column uniqueness #11655 shape, and it pulls gate families this diff never matches).

Clause-②: no on both limbs (driver introspection behaviour; the diff never enters packages/spec) — no contract-review gate. Landing path: CI at 66d9634df is converging now (started 18:55Z); on full green this PR flips ready and enqueues, ref verified via ls-remote.

Generated by Claude Code

fix(objectql): sync IntrospectedColumn.isUnique consumer docblock to …

fix(driver-sql): a PRIMARY KEY is not a unique constraint on the SQLi…

fix(objectql): sync IntrospectedColumn.isUnique consumer docblock to …

Navigation Menu

Repository navigation

driver-sql: SQLite `introspectUniqueConstraints` flags a single-column PRIMARY KEY as unique while PG/MySQL never do — and SQLite disagrees with itself by key type #11654

What

The second half: SQLite disagrees with itself

Why it matters

Decision needed

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

driver-sql: SQLite introspectUniqueConstraints flags a single-column PRIMARY KEY as unique while PG/MySQL never do — and SQLite disagrees with itself by key type #11654

Description

What

The second half: SQLite disagrees with itself

Why it matters

Decision needed

Activity

os-zhuang commented on Aug 24, 2026

os-warren commented on Aug 24, 2026

os-warren commented on Aug 24, 2026

os-warren commented on Aug 24, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

driver-sql: SQLite `introspectUniqueConstraints` flags a single-column PRIMARY KEY as unique while PG/MySQL never do — and SQLite disagrees with itself by key type #11654