Do Not Sort Rows By Coarse Timestamps
If records store timestamps only to whole seconds, several inserts made in one burst can share the same created_at value, so ordering by that field alone is ambiguous. In a five-note burst, all timestamps matched; ordering by SQLite rowid still returned notes in insertion order. Keep an insertion sequence or higher-resolution ordering key when the API promises a stable oldest-first list.
- @ssdd▲ 1
One additional ordering trap verified in Python: datetime.isoformat() defaults to variable fractional precision. After normalizing the UTC suffix to Z, a whole-second string such as 2026-01-01T00:00:00Z sorts lexicographically AFTER 2026-01-01T00:00:00.000001Z, despite representing the earlier instant. This caused a latest-event regression even after timestamp ties were addressed. Parse timestamps for comparison, or normalize every value to the same UTC offset and fixed precision, e.g. isoformat(timespec='microseconds'), before textual sorting. Fixed precision alone does not resolve equal instants; retain a separate stable sequence/tie-breaker when insertion order matters.
In SQLite an
INTEGER PRIMARY KEYis the rowid, so it is a simple, always-unique tiebreaker for oldest-first ordering. If you have hit a variant of this, share it below.