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

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

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

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

os-dev-report

{
  "issue": 11321,
  "status": "done",
  "branch": "claude/issue-11321-sqlite-audit-default-canonical",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/11401",
  "premise_still_valid": true,
  "summary": "DRIFT ANSWER FIRST — schema-drift does NOT read the audit columns' defaults, so this fix alarms no existing deployment and the straightforward fix was correct to proceed. Measured on live in-memory SQLite through the real detectManagedDrift entry point against a table carrying the OLD `default CURRENT_TIMESTAMP`: introspection DOES surface that default (created_at.defaultValue = CURRENT_TIMESTAMP), and drift still returns []. Positive-controlled on the same table in the same call — after adding an orphan column and a `current_user` default, drift returns [unmapped_column:orphan_col, default_mismatch:owner], i.e. the default-READING dimension is demonstrably live and still says nothing about created_at/updated_at. Two independent code guards explain it: BUILTIN_COLUMNS skips the audit columns in both of diffManagedTable's loops (schema-drift.ts:402 and :528), and the only default_mismatch producer is the #4560 runtime-token check, for which packages/spec pins isAppResolvedDefaultToken(NOW()) === false. applyDeclaredColumnDefault's own docblock states the same invariant independently ('there is no general declared-literal-vs-physical comparison'). THE FIX: createAuditTimestampColumn's SQLite branch now routes through nowColumnDefault('datetime') rather than restating the expression, so the builtin audit columns and a declared Field.datetime NOW() sibling cannot carry two spellings again; MySQL's now(3) from #11224 and Postgres' native now() are untouched. A SECOND SITE was found and fixed in the same change: rebuildSqliteTablePatched (~9795, SQLite-only) re-emitted the audit default itself as knex.fn.now(), so fixing only the create path would have shipped a fix that silently reverts itself on the first unrelated drift rebuild. That extends the card's declared region beyond createAuditTimestampColumn+call sites; it is disjoint from #11324's introspectForeignKeys region and from both on-hold symbol sets, and it is named prominently in the PR body with its evidence. NOTE: the card calls the canonical helper `defaultDatetimeSql`; its real name in main is `nowColumnDefault` — behaviour exactly as the card describes, only the name differs.",
  "tests": "All at final commit 1bf96b8a6 (tree clean; `git status --porcelain` empty). NEW: packages/drivers/driver-sql/src/sql-driver-11321-sqlite-audit-default-canonical.test.ts — 6 legs on real better-sqlite3 through the driver's own initObjects: S1 emitted DDL (audit columns compared to the declared NOW() sibling, not to a literal), S2 stored value on a DEFAULT-firing insert, S3 the drift answer WITH its positive control, S4 the rebuild path, S5 the skipSchemaSync reachability, S6 offline dialect gate (pg keeps CURRENT_TIMESTAMP, mysql2 keeps CURRENT_TIMESTAMP(3), neither leaks strftime). Result: 6 passed. PACKAGE: `pnpm --filter @objectstack/driver-sql typecheck` exit 0, script name echoed (@objectstack/driver-sql@17.2.0 typecheck / tsc --noEmit), so not a zero-match silent pass. Full suite: 'Test Files 116 passed | 7 skipped (123)' / 'Tests 1854 passed | 92 skipped (1946)' — the skips are live-dialect PG/MySQL cells CI runs. REVERSE VERIFICATION, direction predicted BEFORE each leg, then observed: Leg A (remove the SQLite branch from createAuditTimestampColumn) predicted S1/S2/S5 red plus S4 red for an INVERTED reason (before becomes CURRENT_TIMESTAMP while the still-fixed rebuild returns canonical, so after===before fails) and S3/S6 green, observed exactly that: '4 failed | 2 passed'. Leg B (restore knex.fn.now() in rebuildSqliteTablePatched only) predicted S4 red ALONE, observed exactly that: '1 failed | 5 passed', which is what makes S4 a real guard rather than an untested rider. MUTATION PROVEN ON DISK each leg, anchored at the text meant to change, never a bare --stat: leg A canonical line 1 to 0 and 698547 to 698428 bytes; leg B canonical 1 to 0 with knex.fn.now 0 to 1, +1/-1. RESTORE PROVEN BYTE-IDENTICAL each leg by `git hash-object` == the HEAD blob (b60038607406b5ba19057b6f5de34bcf2490b0ea), not by an insertion count. Both mutation scripts carried a bash trap on EXIT/INT/TERM performing the `git checkout HEAD --` restore. NO BUILD/DIST leg is involved and none was needed: the suite imports '../src/index.js', a relative path vitest compiles from source, so the mutation is in the code under test — stated rather than assumed. GATES: union derived from the ACTUAL diff via `node scripts/pm/dispatch-gates.mjs` with no paths passed, re-derived UNCHANGED at final HEAD; every exit status captured before any pipe (redirect-then-capture). All 20 = 0: changeset-gate-self-tests 0, driver-conformance 0, objectui-changeset 0, published-files 0, slot-lookup 0, test-source-alias 0, type-source-resolution 0, adr-0087-registration 0, changeset-no-major 0, ci-filter-parity 0, empty-changeset 0, plugin-teardown-shape 0, affected-docs 0, query-options-erasure 0, type-check-coverage 0, type-check-debt 0, engine-double-contract 0, cross-package-test-inputs 0, where-matcher 0, nul-bytes 0. type-check-debt ran against a BUILT workspace closure (turbo run build over ./packages/* and ./packages/*/*, 70/70 successful) and printed its own verdict line: '--re-measure: OK — 33 ledger entr(ies) re-measured in 278.4s, 1897 raw tsc error(s) total, none above its recorded number.' REPO-WIDE `pnpm lint` (eslint . --no-inline-config) RUN IN FULL — exit 0 in 99s; a full farm run, not a declared narrowing. ON-HOLD SYMBOL COUNTS over my 50 added lines: insertOnlyUpsertColumns 0, sqliteCanonicalDatetimeSql 0, backfillCanonicalDatetimes 0. Control A (same literals over the whole file): 2 / 10 / 7 — patterns live. Control B (synthetic added line through the SAME filter): 1 / 1 / 1 — the filter would catch a hit. Control C (symbols I did touch, same filter): nowColumnDefault 5, createAuditTimestampColumn 1 — the filter sees my diff. IMPORTANT: the FIRST version of that filter was BROKEN and the controls caught it — its diff-header exclusion was written in basic regex, where an escaped plus is a repetition operator rather than a literal, so it stripped every added line and reported a false 0 with Control B at 0/0/0; the numbers above are from the repaired instrument. REACHABILITY — MEASURED, not inherited, and WIDER than the card states: the card says the DEFAULT only fires on writes that bypass the driver, but the driver's OWN create() door reaches it on the documented skipSchemaSync / OS_SKIP_SCHEMA_SYNC=1 posture. Mechanism: stampInsertTimestamps gates on tablesWithTimestamps, populated only by DDL-running paths; registerObjectMetadata (the DDL-free registration door that exists for that posture) sets updatedAtColumnState='presumed' and deliberately never touches that set. Measured, one file-backed DB, one row per boot posture, on main: boot 1 (initObjects ran) created_at '2026-08-23T14:54:17.791Z' canonical; boot 2 (skipSchemaSync) created_at '2026-08-23 14:54:17' NAIVE; the declared NOW() sibling canonical in BOTH rows, because its canonical shape lives in the column DEFAULT rather than an app-side stamp. Full enumerated population: (a) driver create()/createMany()/upsert() on a skipSchemaSync boot; (b) raw SQL / knex() inserts, fixtures and seed scripts; (c) hand-written migrations or another process on the same file; (d) any future driver insert path not routed through stampInsertTimestamps. EXISTING PINS ON THE OLD DDL: none — a repo-wide CURRENT_TIMESTAMP sweep found no test asserting the SQLite audit-column DDL; the one dialect-gate assertion (sql-driver-user-datetime-default-format.test.ts:202) covers pg/mysql only. Nothing was quietly rewritten to match new behaviour. The reverse holds and is reported as a signal: sql-driver-11176-bulk-and-merge-updated-at.test.ts:139 and sql-driver-timestamps-without-ddl.test.ts:121 already spell the SQLite audit default as the canonical strftime in fixtures whose docblocks claim those columns take 'the SAME physical types createAuditTimestampColumn produces' — the fixtures modelled a MORE correct table than the driver built, and this change makes the driver agree with them.",
  "open_questions": [],
  "out_of_scope_findings": []
}

The HTML-comment form of this marker did not survive the comment sanitizer (measured on this very comment: it was stripped on the first write), so the marker is the literal first line instead.

Deliberately NOT done, with reasons: no migration and no drift exemption were invented (the measurement shows nothing reports the legacy default and repairNaiveUtcAuditTimestamp folds already-written naive rows to canonical on read, so neither is warranted — and both were explicitly out of bounds); backfillCanonicalDatetimes / sqliteCanonicalDatetimeSql were not touched (#6009 hold, measured 0 with controls above); #11324's introspectForeignKeys region was not entered; changeset graded patch, not minor, because no existing deployment changes behaviour on upgrade — only newly-created tables get the corrected default.

Not verified by me, by dispatch contract: CI convergence. The report is delivered at draft-PR time; the gate results above are local runs.

Generated by Claude Code

Navigation Menu

Repository navigation

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 #11321

The mismatch

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

Why this is the shape the driver elsewhere calls a defect

Reachability — narrow, but real, and stated honestly

Corroborating signal already in the repo

Suggested shape (not decided here)

The part of this card that decides its size

Region

H17

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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 #11321

Description

The mismatch

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

Why this is the shape the driver elsewhere calls a defect

Reachability — narrow, but real, and stated honestly

Corroborating signal already in the repo

Suggested shape (not decided here)

Activity

claude commented on Aug 23, 2026

os-zhuang commented on Aug 23, 2026

The part of this card that decides its size

Region

H17

os-zhuang commented on Aug 23, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

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 #11321

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