time handling: scheduled_at stored verbatim in mixed formats alongside naive-UTC bookkeeping timestamps - UI and queries read as out of sync

[ajianaz]

Evidence (one real schedules table, 2026-09-23)

scheduled_at values stored verbatim from client input, three different formats side by side:

2026-08-09T06:30:23+07:00
2026-08-09T23:00:00.000Z
2026-08-12T04:00:00+07:00
2026-09-15T03:05:00Z
2026-09-23T05:09:46+00:00

Bookkeeping columns on the same rows use a fourth format (naive UTC, no T, no zone):

published_at = 2026-08-09 08:27:30
created_at / updated_at = datetime('now')  ->  "YYYY-MM-DD HH:MM:SS"

Store::create_schedule binds input.scheduled_at as-is (store.rs INSERT), while updated_at/published_at are written with datetime('now'). The due-check query normalizes at READ time (#107 - that part works), but every other surface (list API, web UI columns, any new query that compares scheduled_at to datetime('now') raw) sees mixed formats with no timezone labels.

User-visible impact

• Web UI shows a 13:00 slot next to a 05:13 created_at and they look 8h apart; one is +07:00, the other naive UTC. Owner read this as "jam penjadwalan vs jam tersimpan tidak sinkron".
• Sorting by the scheduled_at string orders +07:00 rows by their local clock time, not by absolute instant.
• The API accepts any well-formed ISO timestamp without validation (#pitfall seen Aug 29: a UTC value suffixed +07:00 was silently stored at the wrong slot). A Z value, +00:00, +07:00, and .000Z all coexist in prod today.

Proposed fixes

1. Canonical storage: normalize scheduled_at to UTC RFC 3339 (YYYY-MM-DDTHH:MM:SSZ) at INSERT/PATCH; one-time migration for existing rows. Optionally keep the client's original in scheduled_at_raw for display fidelity.
2. Validate: reject timestamps without an explicit offset (400), so the silent wrong-slot class of bugs dies.
3. UI: render all times in one timezone (browser-local or a workspace TZ setting) with the offset shown; never raw-mix stored strings.
4. Sweep: grep for any remaining raw comparison of scheduled_at against datetime('now') outside the bug: Scheduler never publishes due schedules — timezone mismatch in get_due_schedules query #107-normalized query (e.g. any new bundle/retry queries copying the old pattern would silently regress).