What happened?
SQLAlchemySegmentStore corrupts non-UTC timezone-aware segment timestamps when running on SQLite, in two ways:
1. Write path: the stored instant is wrong.
SQLite's DateTime(timezone=True) does not store timezone information: SQLAlchemy's SQLite dialect discards tzinfo and persists the datetime's wall-clock fields verbatim, reading them back naive. The store's read path already assumes the stored value is UTC (it reapplies the separately stored timestamp_timezone_offset on read), but the write path persisted ensure_tz_aware(segment.timestamp) without normalizing to UTC. So a non-UTC timestamp was stored as its original zone's wall clock, then reinterpreted as UTC on read — shifting the instant by the original UTC offset.
Concretely, a segment timestamp of 2024-01-01 13:30:45-08:00 comes back as 2024-01-01 05:30:45-08:00.
2. Filter bounds: datetime comparisons decide by wall clock, not instant.
Property filters on timestamp compile through _compile_column_leaf in common/filter/sql_filter_util.py, which bound datetime values as-is. On SQLite the bind processor renders an aware datetime as wall-clock digits with the offset dropped, and the comparison against the stored text is lexical — so timestamp <= 2024-01-01T08:00+08:00 excludes a row stored at 2024-01-01T00:00Z, even though that is the very same instant.
PostgreSQL is unaffected in both cases (timestamptz stores and compares true instants); the bug is that the two backends disagree.
Expected behavior
A timezone-aware timestamp roundtrips with its instant intact (the original offset is reconstructed from timestamp_timezone_offset), and a datetime filter bound means an instant regardless of the zone it is written in — identically on SQLite and PostgreSQL.
Fix
Normalize to UTC before persisting (ensure_tz_aware(...).astimezone(UTC) in _insert_segments) and before binding datetime comparison values in sql_filter_util._compile_column_leaf. Both halves, with regression tests parametrized over UTC/-08:00/+05:30 (verified fail-before/pass-after on SQLite), are folded into #1545, which rewrites this store. The same fix for the write path originated in #1462.
This issue is scoped to the segment store only; the episode and cluster stores have the same write-path defect, tracked separately so each can be fixed in its own PR: #1558 (episode store), #1559 (cluster store).
Investigated and written by Claude (Claude Code), filed from the account of the user who commissioned the investigation.
What happened?
SQLAlchemySegmentStorecorrupts non-UTC timezone-aware segment timestamps when running on SQLite, in two ways:1. Write path: the stored instant is wrong.
SQLite's
DateTime(timezone=True)does not store timezone information: SQLAlchemy's SQLite dialect discardstzinfoand persists the datetime's wall-clock fields verbatim, reading them back naive. The store's read path already assumes the stored value is UTC (it reapplies the separately storedtimestamp_timezone_offseton read), but the write path persistedensure_tz_aware(segment.timestamp)without normalizing to UTC. So a non-UTC timestamp was stored as its original zone's wall clock, then reinterpreted as UTC on read — shifting the instant by the original UTC offset.Concretely, a segment timestamp of
2024-01-01 13:30:45-08:00comes back as2024-01-01 05:30:45-08:00.2. Filter bounds: datetime comparisons decide by wall clock, not instant.
Property filters on
timestampcompile through_compile_column_leafincommon/filter/sql_filter_util.py, which bound datetime values as-is. On SQLite the bind processor renders an aware datetime as wall-clock digits with the offset dropped, and the comparison against the stored text is lexical — sotimestamp <= 2024-01-01T08:00+08:00excludes a row stored at2024-01-01T00:00Z, even though that is the very same instant.PostgreSQL is unaffected in both cases (
timestamptzstores and compares true instants); the bug is that the two backends disagree.Expected behavior
A timezone-aware timestamp roundtrips with its instant intact (the original offset is reconstructed from
timestamp_timezone_offset), and a datetime filter bound means an instant regardless of the zone it is written in — identically on SQLite and PostgreSQL.Fix
Normalize to UTC before persisting (
ensure_tz_aware(...).astimezone(UTC)in_insert_segments) and before binding datetime comparison values insql_filter_util._compile_column_leaf. Both halves, with regression tests parametrized over UTC/-08:00/+05:30 (verified fail-before/pass-after on SQLite), are folded into #1545, which rewrites this store. The same fix for the write path originated in #1462.This issue is scoped to the segment store only; the episode and cluster stores have the same write-path defect, tracked separately so each can be fixed in its own PR: #1558 (episode store), #1559 (cluster store).
Investigated and written by Claude (Claude Code), filed from the account of the user who commissioned the investigation.