bookmarkedAt is wrong, breaks ft viz Rhythm/Daily Arc/Weekly Pulse charts

[beefkatsu]

Bug

Three related bugs around bookmark timestamps. They all stem from bookmarkedAt being incorrect.

1. bookmarkedAt doesn't match reality

bookmarkedAt is derived from entry.sortIndex and decoded as a Twitter snowflake in graphql-bookmarks.js:258. But the result contradicts postedAt:

postedAt:     Wed Apr 08 06:30:15 +0000 2026
bookmarkedAt: 2024-11-27T22:27:15.063Z
syncedAt:     2026-04-08T13:19:23.366Z

The tweet was posted and synced today, but bookmarkedAt claims I bookmarked it 17 months ago. You can't bookmark a future tweet.

The sortIndex numerically corresponds to bookmark order (sorting by it gives correct order — verified), but its snowflake decode is not a real timestamp.

2. ft viz Rhythm / Daily Arc / Weekly Pulse charts produce garbage labels

These all bucket by bookmarkedAt. The Twitter date string parsing in bookmarks-viz.js (lines 100-135) tries to handle both ISO and Twitter legacy formats, but ends up producing labels like:

00 000Z   ▏  2
00 001Z   ▎  3
00 002Z   ▏  1

These should be hour-of-day labels (00:00, 01:00, etc.).

3. MIN(posted_at) / MAX(posted_at) is alphabetical, not chronological

bookmarks-db.js:523 does SELECT MIN(posted_at), MAX(posted_at) on the raw Twitter date string "Wed Apr 08 06:30:15 +0000 2026". SQLite sorts these alphabetically, so the "earliest" and "latest" are wrong (related to #11 but more fundamental than just year display).

Root cause

Twitter's sortIndex field for bookmark entries is not a snowflake encoding bookmark creation time. It's an opaque sort key. The decoder just happens to produce a plausible-looking date.

Twitter's bookmarks API does not expose a real "when did you bookmark this" timestamp.

Fix suggestions

• Drop the bookmarkedAt snowflake decode. Either store sortIndex as a numeric sort key, or use syncedAt as a fallback for "first time we saw this in your bookmarks".
• Remove or rebuild the Rhythm/Daily Arc/Weekly Pulse charts since they have no real data to plot.
• Parse posted_at to ISO before storing (or compute a parallel posted_at_iso column) so MIN/MAX works.

Version

v1.3.2, GraphQL session sync, macOS.

[MrDwarf7]

Related #69

[afar1]

Fixed on main across several commits:

• 315b5b8 — stopped fabricating bookmark dates from the sortIndex snowflake decode. bookmarkedAt is no longer derived from the opaque sort key.
• a483c07 / c7045d1 — normalized viz month buckets and updated viz SQL queries to support the new ISO bookmarked_at format, fixing the Rhythm / Daily Arc / Weekly Pulse chart garbage labels.
• The MIN/MAX alphabetical sort issue was replaced with a proper chronologicalDateRange() parser that handles both Twitter legacy and ISO date formats numerically.

All three sub-bugs you identified are addressed. Thank you @beefkatsu for the extremely thorough write-up — the root cause analysis on sortIndex not being a real snowflake timestamp was exactly right, and it directly shaped the fix. Great bug report. 🙏