instance_runtime_tasks.updated_at is written in two text formats, so ORDER BY updated_at DESC ranks a 00:30 runner task above the 22:00 escalation and the LIMIT drops the wrong rows

[serge-ivo]

instance_runtime_tasks.updated_at is written in two mutually-unsortable text formats, so every ORDER BY updated_at DESC … LIMIT over the board mirror ranks a 00:30 runner task above an escalation raised at 22:00 the same day.

Found in the D1 sweep prompted by #438 (no test in this repo executes SQL). This is the #587 shape one level down: not two queries disagreeing, but two writers disagreeing about the byte format of the column a reader sorts on.

Verified — the column has two writers with two formats

datetime('now') → SQLite's YYYY-MM-DD HH:MM:SS (space at index 10):

• workers/api/src/lib/work-card.ts:22-23 — upsertWorkCard, the ONE upsert every board domain goes through:

  INSERT INTO instance_runtime_tasks (id, instance_id, user_id, type, status, payload, created_at, updated_at)
   VALUES (?1, ?2, ?3, ?4, ?5, ?6, datetime('now'), datetime('now'))

  Callers: lib/coding-board.ts:91 (coding-session cards), lib/pipeline-board.ts:54 (pipeline runs), lib/coding-authority.ts:355, lib/repo-policy-act.ts:241 and :275, workflows/coding-session.ts:255.
• workers/api/src/lib/work-card.ts:63 — closeWorkCards: updated_at = datetime('now')
• workers/api/src/lib/work-card.ts:93 — setWorkCardProgress: updated_at = datetime('now')
• workers/api/src/workflows/agent-loop.ts:312-313 — the escalation card, i.e. "a supervised agent needs a decision":

  VALUES (?1, ?2, ?3, 'escalation', 'needs_human', ?4, datetime('now'), datetime('now'))

• workers/api/src/lib/loop-drivers.ts:268-269 — the delegation card, same shape.
• workers/api/src/workflows/job-apply.ts:141, routes/instances-apply.ts:246, routes/instances-browse.ts:183 — updated_at = datetime('now') on failure.

ISO-8601 with T/Z (index 10 is T):

• workers/api/src/routes/instances-runtime.ts:250-262 — mirrorRuntimeTask binds ?8 from
  taskTimestamp(task.updatedAt ?? task.updated_at ?? createdAt), and taskTimestamp is
  workers/api/src/routes/instances-runtime.ts:155-159:

  export function taskTimestamp(value: unknown): string {
  	return typeof value === "string" && value.trim()
  		? value
  		: new Date().toISOString();
  }

  The runner-supplied value is ISO too — packages/browser-runner/src/runner.ts:180,195,235,243,253,260,474,1079,1140,1153,1172 all assign task.updatedAt = new Date().toISOString().
• workers/api/src/routes/instances-tasks.ts:242-245 — SET status = 'running', updated_at = ?1 bound with new Date().toISOString().
• workers/api/src/routes/instances-apply.ts:104 and routes/instances-browse.ts:108 — the single-flight claim rows bind nowIso.

Verified — SQLite orders these by bytes, so the two formats never interleave

D1/SQLite compares TEXT with BINARY collation. ' ' is 0x20, 'T' is 0x54, so for any two rows with the same date every ISO row sorts above every datetime('now') row regardless of the time of day:

$ sqlite3 :memory: "create table t(u text);
  insert into t values('2026-08-16T01:00:00.000Z'),('2026-08-16 23:00:00'),('2026-08-16T02:00:00.000Z'),('2026-08-16 09:00:00');
  select u from t order by u desc;"
2026-08-16T02:00:00.000Z
2026-08-16T01:00:00.000Z
2026-08-16 23:00:00
2026-08-16 09:00:00

Verified — three readers sort on it, and each pairs the sort with a LIMIT

The sort is not cosmetic: it decides which rows are returned at all.

1. workers/api/src/routes/instances-runtime.ts:281-286 — mirroredRuntimeTasks:

   SELECT payload FROM instance_runtime_tasks
    WHERE instance_id = ?1 AND user_id = ?2 AND hidden = 0
    ORDER BY updated_at DESC
    LIMIT ?3

   Default limit = 200 (:279).
2. workers/api/src/lib/instance-work.ts:220-224 — recentWorkForInstances, one branch per subordinate:

   SELECT instance_id, id, type, status, payload, updated_at
     FROM instance_runtime_tasks
    WHERE instance_id = ?${p} AND user_id = ?1 AND hidden = 0
    ORDER BY updated_at DESC LIMIT ?2

   clampPer(perInstance, 8) at :216 — 8 rows per instance.
3. workers/api/src/lib/board.ts:295 calls mirroredRuntimeTasks(env, instanceId, userId, BOARD_TASK_LIMIT) with BOARD_TASK_LIMIT = 1000 (lib/board.ts:127).

User-visible impact

• The supervision surface is the sharp one. lib/connectors/supervision.ts:358 calls
  recentWorkForInstances(ctx.env, userId, ids, limit) — this is what a supervisor agent reads to
  answer "what have my subordinates been doing". Only 8 cards come back per subordinate, and any
  subordinate with more than 8 non-hidden cards gets its 8 chosen by this ordering. A coding-session
  card, a pipeline card, a delegation card and an escalation card are all datetime('now')
  writers, so they lose to every runner-mirrored task stamped anywhere earlier the same day. The
  card that says "a supervised agent needs a decision" (workflows/agent-loop.ts:312) is exactly
  the one that drops off.
• GET /v1/instances/:id/tasks (routes/instances-tasks.ts:81 and :121) returns
  mirroredRuntimeTasks(...) at the 200 default with no pagination and no truncation flag, so
  the console has no way to know rows were dropped or that the drop was arbitrary. (The board path
  does report truncated — lib/board.ts:415 — but only at 1000.)

Not claimed / inferred

• lib/board.ts:415 re-sorts the assembled items in JS with Date.parse(item.updatedAt) off the
  payload, and the payload's updatedAt is mixed the same way (work-card.ts:62 and :92 write
  json_set(payload, '$.updatedAt', datetime('now')) while runner payloads are ISO). V8 parses
  "YYYY-MM-DD HH:MM:SS" as local time and the Workers runtime is UTC, so this JS sort is probably
  correct today. Not verified, and not part of the claim above — the SQL ordering is broken
  independently of it, and the payload mixing is worth fixing for the same reason.
• I have not measured a production row count, so I cannot say how often the 200/1000 caps bind. The
  8-per-instance supervision cap binds at nine cards.

The repo already knows this hazard exists

workers/api/src/lib/error-log.ts:93 carries a private one-liner for exactly this conversion:

const sqlTime = (ms: number) => new Date(ms).toISOString().slice(0, 19).replace("T", " ");

grep -rn "sqlTime" workers/api/src → 2 hits, both in error-log.ts. lib/admin.ts:116-118 (sinceTs), routes/admin-instance-detail.ts:166-168 (thirtyDaysAgo) and lib/external-usage.ts:170-173 each re-derive the same three lines locally. Four independent copies of the fix, none of them shared, and instance_runtime_tasks got none of them.

Suggested shape of a fix (not prescriptive)

Pick one format for the column, convert the other writers, and pin it with a test that inserts through both writer paths and asserts the SQL ordering — which is the class of test #438 says does not exist yet. A shared sqlTime/nowSql helper in lib/ would give the four existing copies somewhere to collapse into.

Refs #438.

[serge-ivo]

Fixed in e33cd5e79758dbd46b0e46065ae8ada540bf38f5 (main), together with #657 — the shared cause this issue named.

What was done

instance_runtime_tasks.created_at / updated_at now store one format: datetime('now')'s own YYYY-MM-DD HH:MM:SS. The three ISO writers were converted at the boundary, and migration 0128_normalize_timestamp_text.sql backfills the rows already in the table (applied to production D1 at 22:58:48 UTC, 6 commands, ✅).

Write-site denominator — 12 statements across 8 files:

already datetime('now') (8)	converted (4)
lib/work-card.ts:22 upsert, :63 close, :93 progress	routes/instances-runtime.ts:246-247 (mirrorRuntimeTask)
workflows/agent-loop.ts:312 escalation	routes/instances-tasks.ts:243 (ticket claim)
lib/loop-drivers.ts:268 delegation	routes/instances-apply.ts:107 (single-flight claim)
workflows/job-apply.ts:143, routes/instances-apply.ts:247, routes/instances-browse.ts:184	routes/instances-browse.ts:108 (single-flight claim)

instance_runtime_task_events.created_at was converted with them. It was not broken — one writer, uniformly ISO — but that writer now emits the column format, and leaving the old rows behind is what would have made it mixed.

The census is re-derived from source by sql-time-writers.test.ts and a thirteenth statement fails it, so the guard is the denominator rather than "a helper exists" (the #591 shape you flagged).

The coupling that was nearly missed

The apply and browse single-flight claims compare updated_at > ?5 against a bound cutoff, not datetime('now'). Converting the mirror writer without converting the cutoff would have made every live datetime('now') card read as stale — two concurrent applications, which is exactly what single-flight exists to prevent. Both moved together, with a test that runs the claim SQL read out of instances-apply.ts and asserts a live card still blocks with the new cutoff and does not with an ISO one.

Watched it fail

workers/api/src/lib/sql-time-writers.test.ts, executed against a real in-memory SQLite carrying all 128 migrations (lib/d1-sqlite.ts), foreign keys on:

• before the fix: a runner task stamped 2026-08-15T00:00:01.000Z came back ahead of an escalation raised at 2026-08-15 22:38:19 — expected 'runner-task' to be 'escalation'. 5 of 10 assertions red.
• after: 11/11 green. The test asserts both rows carry today's date first, so a clock rollover fails loudly rather than passing for the wrong reason.

The shared helper

workers/api/src/lib/sql-time.ts. SqlTime is branded, so a plain string cannot be passed where the format is required and the only producers are sqlTime / toSqlTime. Six private copies existed, not four — lib/runtime-response.ts:22 (d1Timestamp) and routes/coding-repos.ts:139 (sqlNow) as well as the four you cited. All six now delegate.

What I did NOT do, and why

• lib/board.ts:415's JS re-sort on the payload's updatedAt — left as-is, as you scoped it. The payload deliberately keeps the runner's ISO: it is what the console renders, and a browser reads new Date("2026-08-15 22:38:19") as local time. Column format and payload format are separate decisions; a test pins both.
• routes/admin.ts:318 — a seventh copy of the conversion, correct but uncollapsed. That file belongs to another agent's live lane (A cancelled instance's webhook triggers and inbound event deliveries still fire — the cron sweep was gated, the pump and the webhook route were not, and the console still has no surface to stop them #649/Admin "BYOK spend" is structurally $0.00 — loadAdminUsage never selects payer, and every charged figure downstream is computed from it in JS #647) so I left it. It is a tidy-up, not a defect.
• No third broken slice. Swept independently rather than taken on trust: mcp_auth_codes.expires_at is INTEGER; agent_loop_runs.last_alive_at/waiting_until and coding_sessions.last_activity_at/driver_at are INTEGER ms; mcp_credentials.expires_at, github_installations.token_expires_at and users.subscription_expires_at are compared only in JS; agent_connection_deliveries.next_attempt_at is ISO on both sides (dueDeliveries binds now.toISOString()), so it is internally consistent. Your two slices were the only ones.

Verified

pnpm typecheck (11 projects), pnpm vitest run (9,432 tests), biome, and every scripts/ gate in ci.yml. CI run 31913408990 green including Playwright chromium+webkit; deploy 31913409010 green.

Not verified live: the ordering through an authenticated GET /v1/instances/:id/tasks — no session credential is available to this agent. What is confirmed in production is the deploy and the migration's execution against the real D1.