Postmortem: Postgres timestamptz microseconds vs JavaScript Date milliseconds bug

A scheduled YouTube post sat in scheduled status for 35 minutes: it published nothing, recorded no error anywhere, and refused to retry. Disabling composer enforcement — the team's first troubleshooting move — did not help. Four compounding defects, all shipped and fixed together in PR #258 (commit 0a0191c0), turned out to be responsible.

Postgres's timestamptz type stores microsecond precision. A JavaScript Date object holds only millisecond precision. Publish jobs carried their scheduledAt value as an ISO string, so the worker reconstructed a JavaScript Date from it and matched the target row by composite primary key using exact equality on scheduled_at — comparing a millisecond-precision value like …03.981 against the microsecond-precision value actually stored, …03.981164. Those two values are never equal, so the row was invisible to that query.

Only one row in production actually carried sub-millisecond digits, which is why the bug hid for as long as it did before surfacing. The fix matches on a 1-millisecond half-open window (scheduleInstantWindow, in the shared schedule-precision module) instead of exact equality — precision-proof, while still narrow enough to prune to a single database partition — and was applied to all three processors that rebuild a target instant from a job payload: publish, the dead-letter queue, and reconcile.

The invisibility cascaded. The worker's attempt to park the row as failed was an UPDATE keyed on that same mismatched timestamp, so it matched zero rows — status stayed scheduled with a null error_log, even though the worker's own log line claimed the row had been parked as content_locked. A second, independent defect meant that a row left scheduled while still carrying its claim marker (queued_at) was invisible to the poller that claims due targets FOREVER, since that poller only considers rows where queued_at is null — with no error raised anywhere to say why.

Because the row was never actually parked into a retryable status, retrying it was structurally impossible: the retry endpoint accepts only failed or rate_limited targets, and this row read scheduled, so every retry attempt answered 409 not_retryable. That is also the reason turning composer enforcement off changed nothing — nothing was re-running the publish call at all, because nothing considered this row eligible to run.

The precision-window match described above was the root-cause fix. Alongside it: a new recovery sweep re-claims any row left scheduled with a stale claim marker after a 30-minute grace period (chosen because publish is itself idempotent, so re-claiming a job that is actually still running safely no-ops rather than double-publishing); the retry endpoint's error surfaces the real underlying state instead of a bare 409; and — a related, separately-discovered defect in the same investigation — YouTube's server-required title field was being derived only client-side in the console, which silently broke automation clients that scheduled a title-less post through the API directly. That derivation now happens server-side, so every client agrees on what YouTube requires.

The UI reported "Publishing", the worker's own log line reported "parking content_locked", and the database row said scheduled with a null error. The database was the one telling the truth — both of the other signals were reporting an action that had never actually persisted. When a system's log and its database state disagree, trust the row.