driver-sql: on SQLite the builtin audit columns DEFAULT to zone-naive `CURRENT_TIMESTAMP` while a declared `Field.datetime` NOW() default in the SAME table gets the canonical ISO-8601 form

[os-zhuang]

Found while landing #11224 (the MySQL precision half of the same helper). Not fixed there: that card is about the UPDATE door's stamp on MySQL, this one is about the SQLite column DEFAULT, and fixing it changes DDL — a different verification surface.

The mismatch

packages/drivers/driver-sql/src/sql-driver.ts:

• createAuditTimestampColumn's non-MySQL branch is table.timestamp(name).defaultTo(this.knex.fn.now()). On SQLite knex.fn.now() compiles to an unqualified CURRENT_TIMESTAMP, which SQLite renders as a zone-naive, space-separated, second-precision string.
• defaultDatetimeSql's SQLite branch — the one a declared Field.datetime with defaultValue: 'NOW()' gets — is (strftime('%Y-%m-%dT%H:%M:%fZ','now')), the canonical zone-explicit ISO-8601 form with millis.

So one table carries two different default shapes for the same conceptual value.

Measured (better-sqlite3, in-memory, via the driver's own initObjects)

Object declared as { id: text, title: string, when: datetime defaultValue 'NOW()' }. Emitted DDL:

CREATE TABLE `probe_t` (
 `id` varchar(255),
 `created_at` datetime default CURRENT_TIMESTAMP, -- builtin audit
 `updated_at` datetime default CURRENT_TIMESTAMP, -- builtin audit
 `title` varchar(255),
 `when` datetime default (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), -- declared field
 primary key (`id`)
)

An insert that lets the DEFAULTs fire (insert into probe_t (id, title) values ('x','y')) stores:

created_at "2026-08-23 11:03:23" <- zone-naive, space-separated, no millis
updated_at "2026-08-23 11:03:23" <- same
when "2026-08-23T11:03:23.611Z" <- canonical

Why this is the shape the driver elsewhere calls a defect

updatedAtStamp()'s own docblock says exactly what is wrong with that shape, about a form it was already fixed away from:

The previous …replace('T',' ').replace('Z','') wrote a zone-NAIVE, space-separated string that Date.parse reads as LOCAL time, silently shifting the instant by the host offset on a non-UTC runtime (the objectos freshness-probe miss).

A row whose created_at came from this DEFAULT carries that exact string. It is also the pre-canonical storage form that backfillCanonicalDatetimes and repairCanonicalDatetimes exist to converge — reachable here through a path that is writing it today, not through legacy data.

Reachability — narrow, but real, and stated honestly

On the driver's own insert paths stampInsertTimestamps fills both audit columns explicitly with new Date().toISOString(), so the DEFAULT does not fire and the stored value is canonical. The DEFAULT is what a write outside the driver hits:

• raw SQL / knex(...) inserts (including test fixtures and seed scripts),
• a hand-written migration or another process writing the same file,
• any future driver path that inserts without going through stampInsertTimestamps.

Whether that population is worth DDL churn is a triage call, not something this card decides.

Corroborating signal already in the repo

Two live-matrix suites build "a hand-written migration that got the schema right" and spell the SQLite audit default as strftime('%Y-%m-%dT%H:%M:%fZ','now'), each with a docblock claiming those columns "take the SAME physical types createAuditTimestampColumn produces":

• packages/drivers/driver-sql/src/sql-driver-11176-bulk-and-merge-updated-at.test.ts
• packages/drivers/driver-sql/src/sql-driver-timestamps-without-ddl.test.ts

On MySQL that claim holds (datetime(3) default current_timestamp(3) both sides). On SQLite it does not: the fixtures model a more correct table than the driver itself creates. So the fixtures and the driver disagree, and only the fixtures are asserted — which is why nothing has failed.

Suggested shape (not decided here)

Give createAuditTimestampColumn's SQLite branch the same canonical expression defaultDatetimeSql already produces, so one table cannot carry two default shapes. Whoever takes it owns the DDL-change surface: existing SQLite databases keep their old DEFAULT until the column is rebuilt, so the card has to say what happens to them (leave them, or converge them the way backfillCanonicalDatetimes converges stored values) rather than only changing what new tables get.

⚠️ Note for triage: backfillCanonicalDatetimes and sqliteCanonicalDatetimeSql are the restart conditions of the pm:on-hold card #6009, so a fix that reaches for them collides with that hold and should be sequenced against it.

---

Generated by Claude Code

[claude]

Triage: lands in packages/drivers/driver-sql (createAuditTimestampColumn SQLite DDL default); pm:queue, domain:engine, Bug. Note: sibling #11224 (MySQL half) closed today — same helper, re-check current main first; DDL change ⇒ verification surface includes migration/DDL fixtures, not just stamp reads.

---

Generated by Claude Code

[os-zhuang]

Claim — engine seat, session session_01RfyXxZ2WPjcjhuXpiQQc3y, branch claude/issue-11321-sqlite-audit-default-canonical. pm:queue → pm:dispatched in one label write, read back.

The part of this card that decides its size

The mismatch itself is a two-line change. What makes this card non-trivial is that it changes emitted DDL, and the card says so up front — which is why it was filed rather than repaired in passing while #11224 was in flight. The right call then, and the reason it needs its own verification surface now.

The question the dispatch order puts first, before any edit: what does schema-drift comparison do about tables that already exist with the old default? Changing createAuditTimestampColumn changes what the driver declares for every SQLite table from here on, while every already-created table on disk keeps default CURRENT_TIMESTAMP. If the drift comparator reads column defaults, this fix could make every existing SQLite deployment report drift on created_at/updated_at — turning a narrow correctness fix into a broad false alarm. That has to be measured, not assumed, and if it is real then the shape of the fix changes.

Region

createAuditTimestampColumn is at ~12452 with call sites at 8020/8021 and 8461/8462. Dispatched alongside #11324, whose region is introspectForeignKeys at ~13152 — different method, ~700 lines away, no shared symbol. The clearly-stated-region carve-out applies, so the two run in parallel; each order names the other's region as off-limits.

sql-driver.ts currently has no open PR occupant (#11270 merged 13:17:43Z; all 7 open PRs are elsewhere), so the only serialization in play is between this seat's own two devs.

H17

Both pm:on-hold neighbours on this file scope their restart conditions to symbols this card does not touch — #8740 to insertOnlyUpsertColumns, #6009 to sqliteCanonicalDatetimeSql / backfillCanonicalDatetimes. ⚠️ #6009 is the closest call on the file, because this card is about SQLite datetime spellings and #6009 is about a SQLite datetime misread: they are adjacent in subject but disjoint in code — this one changes a column DEFAULT, #6009 concerns the shared canonical read expression and the backfill's SET side. The dev is instructed to measure the symbol counts over its own diff with positive controls rather than assert this, and a touch-notice follows once a PR number exists.

Duplicate probe, both legs positive-controlled: repo-scoped PR search returns no open PR for this number, and git ls-remote origin 'refs/heads/*issue-11321*' is empty while the *issue-* pattern returns 100 live branches.

---

Generated by Claude Code