driver-sql: `updated_at` is never refreshed on a deployment that skips boot schema sync — `tablesWithTimestamps` is also only filled by DDL

[os-elon]

Found while fixing #10995 (same root cause, deliberately out of that PR's scope).

What

SqlDriver.update() refreshes updated_at only for tables in tablesWithTimestamps:

if (this.tablesWithTimestamps.has(object) && !this.keepSuppliedUpdatedAt(formatted, options)) {
 formatted.updated_at = this.isSqlite ? new Date().toISOString() : this.knex.fn.now();
}

That set is populated in exactly three places, all inside the DDL path of initObjects
(packages/drivers/driver-sql/src/sql-driver.ts): the createTable branch, the
"existing table already has an updated_at column" branch, and the rotation-shard copy.

So a deployment that manages DDL out-of-band — skipSchemaSync / OS_SKIP_SCHEMA_SYNC=1,
documented in content/docs/deployment/environment-variables.mdx as "skip the implicit
db:sync on boot; use after running migrations manually" — boots with that set empty
and never stamps updated_at on any update. Nothing else covers it: the column is created
with DEFAULT now() only (createAuditTimestampColumn), i.e. an INSERT-time default, and
no ON UPDATE clause or trigger on any dialect.

Consequence: on those deployments updated_at records the row's creation time forever.
Anything reading it as "last modified" is silently wrong — the by_user / all_preferences
list views sort on it, incremental/delta sync and cache-invalidation consumers read it, and
audit answers derived from it are wrong without being unavailable.

Why it is not fixed with #10995

#10995 makes the value-encoding registries installable without DDL
(SqlDriver.registerObjectMetadata(), called by a skipSchemaSync boot). Those registries
are derived purely from the object's DECLARED field types, so registration stays in-memory
and costs no round-trip — which is the whole reason the flag exists.

tablesWithTimestamps is different in kind: on the DDL path it is decided from the
physical columns (columnInfo() — "does this table actually have updated_at?"), and
that answer cannot be produced without touching the database. So the fix needs a decision
that PR did not want to make silently:

1. Infer from the declared shape. Every table this driver's own DDL creates gets
   created_at/updated_at unconditionally, so a managed object could join the set at
   registration time with no probe. Wrong for a hand-migrated table that genuinely lacks the
   column — an update would then write a column that does not exist (a loud failure, not a
   silent one).
2. Probe once, lazily, per table. One columnInfo() on first write to a table not yet
   classified. Correct on any physical shape, at the cost of one round-trip per table per
   process — small, but it is exactly the currency skipSchemaSync is spending.
3. Declare it. Have the out-of-band migration path record the fact, so the serving
   process is told rather than guessing.

Repro sketch

Create the table out-of-band (create table t (id text primary key, key text, value jsonb, created_at timestamptz default now(), updated_at timestamptz default now())), construct a
SqlDriver that never runs initObjects, create() a row, wait, update() it, and read
updated_at back: unchanged. The same write through a driver that ran initObjects stamps it.

Same family as #10995: driver state that the write path's correctness depends on is
installed only as a side effect of DDL.

[os-zhuang]

Triage: lands in packages/drivers/driver-sql ⇒ domain:engine, Bug, pm:queue. Rationale: on documented skipSchemaSync deployments updated_at silently records creation time forever — consumers (list-view sorts, delta sync, audit answers) are wrong without being unavailable; same family as #10995 (driver state installed only as a DDL side effect). Dispatch guidance, as PM mechanism assumptions to verify (not rulings): of the card's three directions, (1) infer-from-declared at registerObjectMetadata time is the preferred first cut — it matches how the driver's own DDL behaves for every managed table, costs zero round trips (the whole point of the flag), and its failure mode on a hand-migrated table lacking the column is loud, not silent; pairing it with (2)'s lazy one-shot columnInfo() fallback only on that loud failure would make it correct on any physical shape while keeping the steady state free. (3) is a deployment-contract change — out of scope without a ruling. Pin: repro-sketch shape from the card (out-of-band table, no initObjects, update, read back updated_at changed). Serial note: #10995 is p0 and in the same file surface — check its PR state at claim time and rebase/serialize accordingly. Size S-M.

---

Generated by Claude Code

[os-zhuang]

Claim: PM loop round 7
Session: session_01RfyXxZ2WPjcjhuXpiQQc3y
Branch: claude/issue-11067-timestamps-without-ddl
Worktree: objectstack-issue-11067
Domain: domain:engine
File surface: packages/drivers/driver-sql/src/sql-driver.ts (tablesWithTimestamps, its three DDL fill sites in initObjects, registerObjectMetadata, update()) + tests + changeset (stop on breach; explain in the report)
Container & model: S-M, mode:subagent
Clause-②: no, conditional on shipping the pair below — see the fork; shipping option 1 alone would be one and would go back for a ruling.
Serial constraints cleared, both checked at claim rather than assumed:

• sql-driver.ts is free as of 927ccbb23 — PR fix(driver-sql): introspectPrimaryKeys returns the Postgres and MySQL key in declared key order #11164 (driver-sql: the Postgres and MySQL introspectPrimaryKeys arms return the key in UNSPECIFIED row order — neither orders by key position #11101) merged and released it. test(driver-sql): pin the SORT axis' unprovisioned-injected-anchor premise (#10744) #11165 (the SORT axis' runtime premise is unpinned: neither door refuses an ORDER BY over an unprovisioned injected anchor, and nothing asserts it #10744) is queued but adds a new test file, not this one.
• Triage's serial warning is discharged: "driver-sql (PG): JSON-field values are bound without JSON.stringify — non-empty arrays and bare strings 500, empty array silently stored as {} #10995 is p0 and in the same file surface — check its PR state at claim time." driver-sql (PG): JSON-field values are bound without JSON.stringify — non-empty arrays and bare strings 500, empty array silently stored as {} #10995 is CLOSED (completed), PR fix(driver-sql,objectql): JSON-field values must not depend on DDL having run (#10995) #11070 MERGED. No conflict.

Lane dev slots in use at claim: 1 (#10729, different package).

Premise

Verified present on merged main @ 927ccbb23 — tablesWithTimestamps is filled in three places, all inside the DDL path of initObjects (the createTable branch, the "existing table already has updated_at" branch, and the rotation-shard copy), and update() stamps updated_at only for members of that set. Re-derive it yourself at those lines; the card's own reading is the thing under test.

Consequence the card names: a skipSchemaSync / OS_SKIP_SCHEMA_SYNC=1 deployment — documented behaviour, not a misconfiguration (content/docs/deployment/environment-variables.mdx: "skip the implicit db:sync on boot; use after running migrations manually") — boots with the set empty and never stamps updated_at on any update. The column carries only an INSERT-time DEFAULT now(), with no ON UPDATE clause or trigger on any dialect, so updated_at records the row's creation time forever. Consumers are wrong without being unavailable: list-view sorts, delta/incremental sync, cache invalidation, audit answers.

Direction — triage's guidance, carried as a PM mechanism assumption to verify, not a ruling

Ship the pair:

1. Infer from the declared shape at registerObjectMetadata time. Every table this driver's own DDL creates gets created_at/updated_at unconditionally, so a managed object can join the set at registration with zero round trips — which is the currency skipSchemaSync exists to save.
2. Lazy one-shot columnInfo() fallback, triggered ONLY by the loud failure from (1) on a hand-migrated table that genuinely lacks the column. That keeps the steady state free while remaining correct on any physical shape.

⛔ Option 3 (have the out-of-band migration path declare it) is OUT OF SCOPE — triage is right that it is a deployment-contract change and needs its own ruling. If you conclude it is the only correct answer, stop and report; do not open that surface here.

⛔ The fork that decides the tier — read before you write

Option 1 alone changes accept/reject behaviour: on a hand-migrated table genuinely lacking updated_at, an update() that silently succeeded (without stamping) would begin to fail loudly, because the driver would write a column that does not exist. That is a new rejection for a call that works today — clause ② — and it is not what this dispatch is authorised to ship.

The pair is what keeps it out of clause ②: (2) catches that failure and recovers, so the net observable change is "updated_at is now correctly stamped on any physical shape" with no new rejection.

So: if the lazy fallback proves impossible or unreliable — you cannot distinguish the missing-column failure from other write failures, or the recovery cannot be made safe — ⛔ STOP AND REPORT the fork. Do not ship option 1 bare to get the card closed. That path needs the contract-review tier and a maintainer ruling, and this seat will route it there.

Acceptance

The card's repro sketch, as a real pin: create the table out-of-band (create table t (id text primary key, key text, value jsonb, created_at timestamptz default now(), updated_at timestamptz default now())), construct a SqlDriver that never runs initObjects, create() a row, let time pass, update() it, read updated_at back — it must have moved. Demonstrate the failing baseline first (unchanged today), then the same pin green.

⭐ Add the hand-migrated-table-lacking-the-column leg too — that is where the pair's second half lives, and without a pin for it the fallback is untested code on the path that most needs it.

⭐ Live PG and MySQL are reachable — do not treat this as SQLite-only. This repo's Temporal Conformance (live PG + MySQL) job is a required check running pnpm --filter @objectstack/driver-sql test against postgres:16 and mysql:8.0 service containers, and the live-dialect-matrix.testkit.ts / declareDialectCell convention wires a test into it. Further: postgresql-16 is already installed in the agent container and merely stopped (pg_ctlcluster 16 main start), and mysql-server-8.0 installs from the distro archive — measured by the #11101 dev an hour ago. updated_at semantics differ per dialect (this.isSqlite ? new Date().toISOString() : this.knex.fn.now()), so a cross-dialect pin is worth the effort here.

⛔ Boundaries

1. ⛔ packages/spec off limits. ⛔ Never edit content/docs/releases/. Breaking changes ship as minor, never major.
2. ⛔ Do not skip, disable, or quarantine any test to get green. ⛔ Do not raise a ratchet's ceiling to get past it — fix the code the way the ratchet prescribes (a sibling PR hit check:query-options-erasure tonight and did exactly that).
3. ⚠️ sql-driver.ts carries an ADR-0120 anchor roster that dispatch-gates.mjs path derivation does not name — add check:adr-anchors by hand to your gate run.
4. ⚠️ A new test declaring an engine double must route update()/delete() through assertEngineUpdateDispatch/assertEngineDeleteDispatch from @objectstack/metadata-core (⛔ never from objectql — reverse edge, turbo refuses). ⚠️ That predicate was tightened tonight by 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 (8cc840198): a by-id update/delete whose where carries keys beyond id is now refused. Branch from fresh origin/main.

---

Generated by Claude Code