bug(storage): enforce UTC normalization for all persisted SQLite timestamps

Bug

AO intends persisted timestamps to be comparable and timezone-stable, but the SQLite storage layer does not consistently normalize every time.Time before writing it. PR #3900 fixes the confirmed production failure for sessions.activity_last_at; this issue tracks the broader follow-up audit so all persisted timestamp columns have the same UTC-only invariant.

Source: follow-up from review of PR #3900 and #3899 | Reported by: @chauhan via AO session | Analyzed against: d820a03c6aa6da192f6758096bc396c3640201e6 (main) and PR head 40bda959fc021c482e59a6b067a3d19dafa8e773 Priority: P2 — significant storage correctness risk with known affected class; current confirmed production blocker is handled separately by PR #3900. Confidence: Medium — the exact activity_last_at failure is confirmed; broader risk comes from code inspection showing mixed UTC-normalized and pass-through timestamp write helpers.

Reproduction

1. Write a time.Time with a non-UTC location into a SQLite TIMESTAMP column through modernc.org/sqlite.
2. Compare that column directly in SQL against a UTC timestamp, or order/page rows by that timestamp.
3. Observe that mixed textual renderings can compare lexicographically rather than as canonical instants.

The confirmed concrete reproduction is covered by #3899: sessions.activity_last_at stored as a local-zone string can make activity_last_at <= stopped_at reject a valid agent-switch source stop.

Root Cause

Some stores already normalize timestamps to UTC before persistence, for example:

• backend/internal/storage/sqlite/store/usage_store.go uses .UTC() / timeOrNow for usage timestamps.
• backend/internal/storage/sqlite/store/telemetry_store.go normalizes telemetry event times.
• backend/internal/storage/sqlite/store/agent_model_catalog_store.go normalizes model catalog fetch times.

But other storage helpers still pass caller-supplied timestamps through as-is, including examples in:

• backend/internal/storage/sqlite/store/session_store.go — generic updatedAt, nullable firstSignalAt, pinnedAt, and related session helper timestamps.
• backend/internal/storage/sqlite/store/project_store.go — project registration/archive timestamps and workspace repo registration timestamps.
• backend/internal/storage/sqlite/store/agent_switching_store.go — switch/native-session timestamps such as RequestedAt, UpdatedAt, CreatedAt, LastUsedAt, acknowledgements, and transition timestamps.
• backend/internal/storage/sqlite/store/conversation_store.go and session_interface_transition_store.go — many now/at parameters are stored directly.

Because SQLite timestamp comparisons and ordering can depend on stored representation, mixed local-zone and UTC renderings can break fences, pagination, ordering, CHECK constraints, and CDC/event ordering when values are compared directly in SQL.

Fix

Add a small, shared UTC normalization boundary for persisted timestamps and apply it consistently across SQLite stores:

• Normalize non-zero time.Time values with .UTC() before passing them to sqlc params.
• Normalize nullable timestamp helpers (sql.NullTime) and pointer helpers the same way.
• Preserve zero-time/null semantics.
• Add targeted regression tests for representative columns that are compared or ordered in SQL.
• Avoid changing already-shipped migrations; use new migrations only if existing non-UTC persisted data needs cleanup beyond activity_last_at.

This should be done separately from PR #3900, which should remain the focused production fix for activity_last_at.

Impact

• Prevents future string-order timestamp bugs similar to #3899.
• Makes persisted timestamps stable across host timezones.
• Reduces hidden differences between lifecycle, usage, telemetry, PR, conversation, project, and session storage paths.

Related

• #3899 — confirmed production bug caused by local-zone sessions.activity_last_at.
• PR #3900 — focused fix for activity_last_at producers, store normalization, and existing malformed rows.