Timezone lost on datetime DB round-trip (naive vs aware mismatch)

Summary

Datetime values lose their timezone when they round-trip through the database, leaving stored values naive while fresh inputs are timezone-aware. Comparing or subtracting one of each raises TypeError: can't compare offset-naive and offset-aware datetimes, and naive storage is ambiguous (UTC is assumed but not enforced).

Root cause

• The app produces aware datetimes: utcnow() in app/models/mixins.py returns datetime.now(timezone.utc), and API request bodies with an offset are parsed by Pydantic as aware.
• Model datetime fields are declared as plain datetime, which SQLModel/SQLAlchemy maps to DateTime() — not DateTime(timezone=True):
  • SQLite (tests + demo) has no timezone type and returns values naive.
  • PostgreSQL (production, ADR-0033): a plain DateTime() column is timestamp without time zone and also discards the offset.

So any value read back from the DB is naive.

Symptom

Invisible within a single code path; it bites when a fresh input meets a stored value, e.g. updating a Shot:

create Shot with shot_at = 14:32+00:00   (aware) -> stored (naive)
PUT shot_end = 14:00+00:00               (aware, from request)
validator compares request shot_end (aware) vs db shot_at (naive) -> TypeError

This was worked around locally with an _as_utc shim in validate_temporal_fields (app/services/shot_service.py), but that is a patch, not a fix.

Affected fields

• Shot.shot_at / shot_end
• Dataset.temporal_start / temporal_end
• Activity.started_at / ended_at
• created_at / updated_at everywhere (via TimestampMixin)

Why the suite doesn't catch it

JSON-LD tests assert with value.isoformat().startswith("2024-03-15T14:00:00"). A naive datetime's isoformat() simply omits the +00:00 suffix, so startswith still matches and the tz loss passes silently. The crash only surfaces when two datetimes from different sources are compared.

There is also a correctness angle beyond the crash: a naive value is ambiguous. We assume UTC (and _as_utc encodes that assumption), but nothing enforces it — a future non-UTC input would be silently mis-stored.

Proposed fix

Make the persistence boundary timezone-explicit and consistent:

1. Declare datetime columns as timezone-aware, e.g. sa_column=Column(DateTime(timezone=True)), so PostgreSQL preserves the offset. (SQLite still won't, so step 2 matters regardless.)
2. Normalise to aware-UTC in one place on the way in and out — a shared helper in the base service, or Pydantic field validators on the models — rather than ad-hoc per call site.
3. Remove the per-validator _as_utc shim once the boundary guarantees aware-UTC.

Low-risk on the greenfield DB (no migrations), but it touches several models and the service layer, so it warrants its own scoped change.

Acceptance criteria

• Datetimes read back from the DB are timezone-aware (UTC).
• Mixing a stored datetime with a fresh input in a comparison/subtraction no longer raises.
• _as_utc shim removed.
• A regression test that round-trips a datetime through the DB and asserts it is aware.

Migrated from the internal tracker, where it was #15, opened 2026-06-24.

Start by inspecting the datetime fields in the affected models, app/models/mixins.py, and validate_temporal_fields in app/services/shot_service.py. Run the existing JSON-LD tests, then add a regression test that round-trips a datetime through the database and verifies it remains UTC-aware; done means comparisons no longer raise, the _as_utc shim is removed, and all listed fields meet the acceptance criteria.