driver-sql: on MySQL the UPDATE door's `updated_at` stamp truncates to whole seconds against a `DATETIME(3)` column — a row updated in its first second reads `updated_at` EARLIER than `created_at`

[os-zhuang]

Found while choosing the stamp expression for #11176's upsert path. Not fixed there: that card is about whether updated_at advances, this one is about the fidelity of the value it advances to, and fixing it changes the SQL every update() on MySQL emits.

The mismatch

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

• createAuditTimestampColumn builds the audit columns on MySQL as DATETIME(3) defaulted with this.knex.fn.now(3), and its docblock says why in as many words: "CURRENT_TIMESTAMP has to carry matching precision for a DATETIME(3) default, hence now(3)" (MySQL: Field.datetime maps to TIMESTAMP (range ends 2038-01-19) and binds a Date in the process-local timezone #3942).
• updatedAtStamp() — the value every UPDATE door writes into that same column — returns a bare this.knex.fn.now(), which compiles to an unqualified CURRENT_TIMESTAMP. MySQL truncates that to whole seconds.

So the column is created with millisecond precision on purpose and then written at second precision.

Measured, live MySQL 8.0.46

Against the exact schema createAuditTimestampColumn produces:

AFTER INSERT (both columns from the DEFAULT now(3))
 created_at 2026-08-23 11:59:33.357 updated_at 2026-08-23 11:59:33.357

AFTER an update() stamp — CURRENT_TIMESTAMP, what updatedAtStamp() emits
 created_at 2026-08-23 11:59:33.357 updated_at 2026-08-23 11:59:33.000
 -> updated_at is EARLIER than created_at, by 357 ms

AFTER the precision-matched form CURRENT_TIMESTAMP(3)
 created_at 2026-08-23 11:59:33.357 updated_at 2026-08-23 11:59:33.361 ok

Consequences

1. "Last modified" can precede "created". Any consumer comparing the two — an audit answer, a "modified since creation?" badge, a data-quality check — reads a row that was modified as if it were not.
2. Delta / incremental sync can SKIP a row. A cursor held at millisecond precision (updated_at > cursor) misses every row whose stamp was truncated back below the cursor. That is the same silent-wrong-answer family as driver-sql: updated_at is never refreshed on a deployment that skips boot schema sync — tablesWithTimestamps is also only filled by DDL #11067 and driver-sql: updateMany() never stamps updated_at, and upsert()'s merge branch does not advance it on Postgres/MySQL — on every deployment, DDL or not #11176, reached by a different mechanism.
3. Two updates in the same second are indistinguishable, so an ordering by updated_at is unstable exactly where it matters most.

Postgres is unaffected: CURRENT_TIMESTAMP there is transaction_timestamp() at microsecond precision, and the column is timestamptz. SQLite is unaffected: updatedAtStamp() returns a full ISO-8601 string with millis there.

This is already half-known in the repo — sql-driver-timestamps-without-ddl.test.ts documents the truncation in its BACKDATED_MS docblock as a reason to backdate rather than sleep, but records it as a testing hazard rather than as a defect in the stored value.

Suggested shape (not decided here)

Give updatedAtStamp() the same precision the column default carries — this.knex.fn.now(3) on MySQL, unchanged elsewhere. #11176's upsertUpdatedAtStamp() already does exactly this for the upsert door and carries the reasoning at the site; the two would then collapse into one helper. Deliberately left as two by that PR so the UPDATE door's emitted SQL was not changed by a card that had not measured it.

Note the ordering constraint if both land: whoever converges them owns re-running the live MySQL cells of the driver-sql matrix, since the emitted SQL for every update() on MySQL changes.

---

Generated by Claude Code

[os-zhuang]

Triage: pm:queue, domain:engine, Bug — measured on live MySQL 8.0.46: updated_at written at second precision into a DATETIME(3) column whose DEFAULT is now(3); "modified before created" rows and ms-cursor delta-sync skips are user-reachable consequences. Fix shape inherited from #11176's upsertUpdatedAtStamp() (precision-matched now(3) on MySQL, unchanged elsewhere); converging the two helpers is in scope. Constraints: ① serial/fold with the sql-driver.ts family — #11223 and any in-flight #11176 work touch the same file; the engine seat answers fold-or-serial across the family with the five gates at claim time; ② whoever lands it owns re-running the live MySQL cells of the driver-sql matrix (emitted SQL for every update() on MySQL changes), per the card's own ordering note.

---

Generated by Claude Code