Audit DateTimeOffset-vs-TEXT comparison sites for missing UTC normalization (convention-only invariant after #1403/#1421)

[Chris0Jeky]

Seam

Repositories that compare a caller-supplied DateTimeOffset against a SQLite-stored TEXT timestamp column rely on the caller passing UTC. PR #1421 (issue #1403) fixed the four known boundary sites and hardened them with .ToUniversalTime() at the bound, after its review empirically confirmed EF's SQLite mapping preserves the source offset — a non-UTC bound compares as an offset-preserving string, not as an instant.

Gap

LlmQueueRepository.GetStuckProcessingNonCaptureAsync (the proven parameterized pattern #1421 mirrored) — and potentially other query sites — pass the caller's DateTimeOffset through without normalizing to UTC. Today every caller passes UTC, so behavior is correct; but the invariant is enforced by convention only. A future caller passing a local-offset value would silently compare by offset-preserved string and mis-select rows (the exact failure class PR #1421's review caught and fixed at its own sites, severity HIGH there).

Suggested direction

Sweep backend/src/Taskdeck.Infrastructure/Repositories/ for every raw-SQL or interpolated query that binds a DateTimeOffset parameter compared against a stored TEXT timestamp; either add .ToUniversalTime() at the bind site (cheap, local, matches #1421's pattern) or centralize via a small helper. Add one regression test per fixed site with a non-UTC-offset bound (the DeleteOldEntriesAsync_WithNonUtcCutoff_ComparesByInstant_NotOffsetPreservedString test from PR #1421 is the template).

Provenance

Seeded from PR #1421's implementation round (issue #1403), overnight run 2026-07-18 — the worker's residual note flagged the convention-only invariant; tracked per review policy (no silent drops).

[Chris0Jeky]

Scope update from PR #1421 (issue #1403): AuditLogRepository's timestamp seams are now fully covered there — the four adjudicated #1403 sites (CountByDateAsync, DeleteOldEntriesAsync, OAuth DeleteExpiredAsync/TryConsumeAtomicAsync) plus, per lens-2 review, BuildSqliteQueryAsync (the QueryAsync path fed user-supplied query.From/query.To), all with .ToUniversalTime() normalization and instant-semantics regression tests (TimestampBoundaryComparisonTests).

Remaining sweep scope for this issue: LlmQueueRepository (e.g. GetStuckProcessingNonCaptureAsync relies on callers passing UtcNow rather than normalizing) and the repo-wide audit of any other seam comparing a caller-supplied DateTimeOffset against a stored TEXT column without UTC normalization. Key facts for the sweep, established empirically on #1421: EF's SQLite DateTimeOffset mapping preserves the source offset (does NOT convert to UTC), and stores with trailing fraction zeros trimmed (dot dropped at zero fraction).

[Chris0Jeky]

Taking bounded slice TD-1422-stuck-processing-bound: normalize the GetStuckProcessingNonCaptureAsync SQLite cutoff to UTC and add one non-UTC instant-semantics regression. I will not change stored rows, the inequality, ordering, or other timestamp sites.

[Chris0Jeky]

Released the bounded UTC-bound normalization slice in PR #2646 at exact head 6a700c5e1cd2338d2a4c1581fab59ac1c23ff993. The new non-UTC SQLite regression failed against the old implementation and passes after the fix; the full LlmQueueRepositoryIntegrationTests class passes 43/43. Two coordinator reviews are clean; hosted automatic Codex review is unavailable at the current account limit, so this remains unmerged.

[Chris0Jeky]

Coordinator note (2026-09-05, D-12 sweep): the Codex session's PR on this issue was reviewed by one fresh-context Opus 5 reviewer (record on the PR) and merged with a merge commit. The PR body used Refs, so this issue stays open; what merged and what remains, from the review:

• PR #2646 merged as 980432c1c: GetStuckProcessingNonCaptureAsync normalizes its cutoff with ToUniversalTime() before binding in both branches, with a +02:00 SQLite regression. Production impact today is nil (the sole caller passes UTC). Still unnormalized, from the review, for the audit this issue is: OutboundWebhookDeliveryRepository.cs:31 and :92; LlmUsageRecordRepository.cs:130, 141, 146, 151, 167, 176, 185 (the caller-supplied {now} at 136 is also written into CreatedAt/UpdatedAt, so a non-UTC caller there would corrupt the +00:00 storage invariant); the five caller-supplied UpdatedAt = {expectedUpdatedAt} equality claims in LlmQueueRepository.cs:619, 657, 682, 716, 736. LOWs: the test name ..._ComparesByInstant is broader than what it proves; the comment is thinner than the sibling #1403 sites.