driver-sql: SQLite introspection reports only the FIRST column of a composite primary key (`pk === 1` vs SQLite's 1,2,3… numbering)

[os-warren]

Found while fixing the introspection seam for #10676 (packages/services/service-datasource). The defect is upstream of that seam, in driver-sql, so it is filed separately rather than widened into that PR.

What

SqlDriver.introspectPrimaryKeys (packages/drivers/driver-sql/src/sql-driver.ts, SQLite arm) collects primary-key columns with:

const result = await this.knex.raw(`PRAGMA table_info(${safeTableName})`);
for (const row of result) {
 if (row.pk === 1) {
 primaryKeys.push(row.name);
 }
}

PRAGMA table_info does not report pk as a boolean. It reports the column's 1-based position within the primary key — 0 for "not part of the key", 1 for the first key column, 2 for the second, and so on. Filtering on pk === 1 therefore keeps only the first member of a composite key and silently drops the rest.

Measured

Live in-memory SQLite at 368e7a06f, table created as t.primary(['order_id', 'line_no']):

PRAGMA table_info(order_lines):

name	pk
order_id	1
line_no	2
sku	0

SqlDriver.introspectSchema() for that table:

{
 "name": "order_lines",
 "primaryKeys": ["order_id"],
 "columns": [
 { "name": "order_id", "isPrimary": true },
 { "name": "line_no", "isPrimary": false },
 { "name": "sku", "isPrimary": false }
 ]
}

line_no is a declared member of the primary key and is reported as not being one. Note that both output signals are wrong together, and for the same reason: introspectSchema derives col.isPrimary from primaryKeys (if (primaryKeys.includes(col.name)) col.isPrimary = true), so a consumer cannot recover the missing member by cross-checking the two.

Why it matters

Every consumer of SQLite introspection sees a composite-keyed table as single-keyed:

• the federated-object codegen and the persisted external_catalog (ADR-0015) record a partial addressing/upsert key;
• schema-drift comparison against a declared composite key reads as drift on the missing member.

SqliteWasmDriver extends SqlDriver and inherits this arm, so it is affected identically.

Fix direction

row.pk > 0 rather than row.pk === 1, and — since pk is an ordinal — push in pk order so primaryKeys reflects the declared key order rather than column order. Worth a pin over a real composite-keyed table; the current suite has none.

The Postgres and MySQL arms read from pg_index / KEY_COLUMN_USAGE and do not have this specific bug, though neither orders its result by key position either (ORDER BY is absent in both) — worth confirming in the same pass.

Not measured here

Only the SQLite arm was executed (better-sqlite3, in-memory). The Postgres and MySQL arms were read, not run — no server was reachable from this container.

Filed unassigned as a finding awaiting first-touch grading. Discovered from #10676.

[huangyiirene]

Triage: lands in packages/drivers/driver-sql (introspectPrimaryKeys, SQLite arm) ⇒ domain:engine, type Bug, queued (pm:queue).

Grading rationale: measured, mechanical, and self-contained — PRAGMA table_info's pk is a 1-based ordinal, pk === 1 keeps only the first member of a composite key, and both output signals (primaryKeys, isPrimary) are wrong together so no consumer can cross-check its way out. Fix direction is in the card and is not a judgement call: row.pk > 0, push in pk-ordinal order, pin over a real composite-keyed table (the suite has none today); confirm the PG/MySQL arms' missing ORDER BY in the same pass. SqliteWasmDriver inherits the fix for free.

Notes for the engine seat:

• Hot-file: sql-driver.ts also carries queued A compare-and-set where on a by-id update is silently inert — the extra predicate keys never reach the driver, and SqlHttpOutbox.redeliver's status guard is one of them #11009 (by-id predicate refusal, different function), queued driver-sql (PG): JSON-field values are bound without JSON.stringify — non-empty arrays and bare strings 500, empty array silently stored as {} #10995 (PG JSON binding), and parked driver-sql: introspectSchema() returns only { tables } — the spec IntrospectedSchema contract's dialect and (required) introspectedAt are never emitted #10998 (same seam, blocked on External object-draft drops the introspected primary key (isPrimary vs primaryKey seam) #10676). Fold-or-serial is the seat's five-gate call — these are different defect shapes in one file, so serial with region-level surface declarations is the likely answer, not a fold.
• Downstream consumers (federated codegen / external_catalog / drift comparison) need no separate fix, but the drift-comparison case makes a good second assertion in the pin.

---

Generated by Claude Code