Dates: big-bang cutover — drain to zero, coordinated flip, verification #120

New Issue

2026-09-24T23:00:10Z

cameron commented

2026-09-24 23:00:10 +00:00

Parent

ImageApi #113 — SPEC: true-instant date contract (ADR-0001).

What to build

The big-bang cutover: the moment the whole system switches to true instants, and the verification that proves it. Operator-executed with the agent preparing checklists and verifying. All three ImageApi blockers (#117/#118/#119) are merged; the server deploy is mixed-row-safe by construction (#119), so the "big bang" is really the client flip — the server can land ahead of the drain.

Test-server validation findings (2026-09-27, branch deploy, ~5.2k-row corpus)

Evidence from running #118+#119 against a copy of the prod schema on real media:

The drain floor is not zero. Edited/exported files (Snapseed, AdobeLightroom, -edited.jpg, merged photos, FPV exports) lose their date tags; exiftool confirms empty Date/Time Original + Create Date on a sample. The drain correctly refuses fs_time over a real capture date, so those rows are the permanent date_utc IS NULL remainder: 14/1,313 (2.1%) and 18/3,859 (0.5%) on the two test libraries. "0 remaining" is unreachable by design; the end state is a stable floor (more_remain=false, unresolved constant across ≥3 ticks).
day_changed fired as predicted: 38 rows on Phone Backup's final tick (late-evening walls crossing the display-zone midnight). reels_invalidated: 0 was correct — no pregenerated day-span reels existed yet.
Mixed-rows read state verified live: converted rows read date_utc; unconverted wall-rows read through wall_in_zone (row-by-row spot check vs metadata); /memories tiles "more realistic" per operator; the uniform 4h (summer) / 5h (winter) divergence between memories and FileViewer's MetadataView is the frozen legacy column, expected until the FV flip.
server_zone() = None hazard, real: the test container's /etc/localtime is a copy, not a symlink, so zone-name probes fail while the process-local tz still resolves. Set TZ=America/New_York in the service env (primary guard) — the code fallback (commit 615941a) is DST-correct at the wall but only as defense-in-depth. Prod must pass the preflight check below.

Production sequence

Preflight — server resolves server_zone(): TZ=America/New_York in the service env (or a real /etc/localtime symlink + /etc/timezone); exiftool on PATH; ffmpeg/ffmpeg on PATH; auth.db copied to a timestamped backup; master ≥ 7f34802 (contains #118, #119, the wall-offset fallback fix).
Filename audit (read-only, #117) — run the filename-class audit against the live library; operator sign-off on the proposed dates (or pattern fix + re-run).
Deploy master to prod — migrations auto-run (nullable columns, additive). Reads switch to the mixed-row-safe contract immediately; unconverted rows serve via the per-source fallback conventions. No client-visible break: the old clients (FV MetadataView, Apollo) still read the frozen legacy date_taken, byte-identical.
Drain — the #118 drain runs per watcher tick (500 rows/library/tick). Expect ~45 min for 21k rows. Watch: utc_backfill log lines (converted/skipped_missing/unresolved/day_changed), GET /libraries date_backfill_remaining, and the imageserver_date_backfill_remaining gauge.
Floor audit — SQL the remainder: SELECT library_id, rel_path, date_taken_source FROM image_exif WHERE date_utc IS NULL; Spot-check a sample with exiftool -DateTimeOriginal -CreateDate <abs-path>; every row should show empty date tags (edited/exported files). Any row that has tags in the file is a real bug — stop and chase.
Coordinated client flip — in one window: Apollo #37 (web UI, branch feature/photo-chat-imageapi-sync) and FileViewer-React #33 (mobile, main @ 978961c or later). Both flip to the new-contract triple and delete their offset-shift/re-anchor helpers.
Reels — the nightly pregen regenerates day-span reels in the owner's display zone; day_changed invalidations during the drain already removed shifted reels. Verify: today's day-reel month_day equals the day the /memories tiles show; pregen zone = prefs display_tz = the tiles' zone.
Verification battery — photo-for-photo web/mobile agreement on the same photo; a video's date matching a same-instant photo; timeline scrubber/track agreement; a Japan-zone photo showing its camera wall clock while grouping under the display-zone day; a manual-date round-trip; insight prompt date == UI date (video + travel-zone photo); MetadataView (now on the new fields) agreeing with memories tiles to the hour.
Rollback drill — flip one client back to the legacy field: values must be byte-identical to pre-cutover (legacy columns were never touched); re-flip forward. Server-side rollback = revert to pre-#119 master; the new triple is inert data on that build.
Week of observation — no date-related operator reports; the spec's user stories 1–24 verifiable in daily use.

Acceptance criteria

Preflight passed: TZ set, exiftool + ffmpeg on PATH, DB backup taken, master ≥ 7f34802.
Audit utility run against the live library; operator sign-off recorded (or pattern fixes applied and re-run).
Drain reaches its stable floor: more_remain=false with unresolved constant across ≥3 consecutive ticks; GET /libraries date_backfill_remaining flat.
Floor audit: every remaining date_utc IS NULL row verified tag-free by exiftool (sample ≥ 10 or 100%, whichever is smaller); zero rows with in-file tags.
All three consumers (server reads, web UI, mobile) on the new contract in one window; no sustained mixed-convention window after the client deploys.
Photo-for-photo date agreement verified between web UI and mobile app (including a travel-zone photo and a video).
No offset-shift call sites remain in either consumer repo (search returns only the deletion diffs).
Insight prompt date equals the UI date for the same photo, including a video and a Japan-zone photo.
Today's day-reel month_day matches the /memories day tiles; pregen zone == display_tz pref.
Rollback verified: a client on the legacy field sees byte-identical values; re-flip forward works.
After a week of normal use, no date-related reports from the operator (the spec's user stories 1–24 all verifiable in daily use).

Blocked by

ImageApi #117 (audit utility) — merged (PR #123)
ImageApi #118 (backfill drain) — merged (PR #124)
ImageApi #119 (reads + insight context on the new contract) — merged (PR #125)
ImageApi #152 (/photos date filters on the true-instant contract) — open
Apollo #37 (web UI flip + re-anchor deletion) — open, cross-repo
FileViewer-React #33 (viewer flip) — open, cross-repo

## Parent ImageApi #113 — SPEC: true-instant date contract (ADR-0001). ## What to build The big-bang cutover: the moment the whole system switches to true instants, and the verification that proves it. Operator-executed with the agent preparing checklists and verifying. All three ImageApi blockers (#117/#118/#119) are merged; the server deploy is mixed-row-safe by construction (#119), so the "big bang" is really the **client** flip — the server can land ahead of the drain. ## Test-server validation findings (2026-09-27, branch deploy, ~5.2k-row corpus) Evidence from running #118+#119 against a copy of the prod schema on real media: - **The drain floor is not zero.** Edited/exported files (Snapseed, AdobeLightroom, `-edited.jpg`, merged photos, FPV exports) lose their date tags; exiftool confirms empty `Date/Time Original` + `Create Date` on a sample. The drain correctly refuses `fs_time` over a real capture date, so those rows are the permanent `date_utc IS NULL` remainder: **14/1,313 (2.1%) and 18/3,859 (0.5%)** on the two test libraries. "0 remaining" is unreachable by design; the end state is a **stable floor** (`more_remain=false`, `unresolved` constant across ≥3 ticks). - **`day_changed` fired as predicted**: 38 rows on Phone Backup's final tick (late-evening walls crossing the display-zone midnight). `reels_invalidated: 0` was correct — no pregenerated day-span reels existed yet. - **Mixed-rows read state verified live**: converted rows read `date_utc`; unconverted wall-rows read through `wall_in_zone` (row-by-row spot check vs metadata); `/memories` tiles "more realistic" per operator; the uniform 4h (summer) / 5h (winter) divergence between memories and FileViewer's MetadataView is the frozen legacy column, expected until the FV flip. - **`server_zone() = None` hazard, real**: the test container's `/etc/localtime` is a copy, not a symlink, so zone-name probes fail while the process-local tz still resolves. Set `TZ=America/New_York` in the service env (primary guard) — the code fallback (commit `615941a`) is DST-correct at the wall but only as defense-in-depth. Prod must pass the preflight check below. ## Production sequence 1. **Preflight** — server resolves `server_zone()`: `TZ=America/New_York` in the service env (or a real `/etc/localtime` symlink + `/etc/timezone`); `exiftool` on PATH; ffmpeg/ffmpeg on PATH; `auth.db` copied to a timestamped backup; master ≥ `7f34802` (contains #118, #119, the wall-offset fallback fix). 2. **Filename audit (read-only, #117)** — run the filename-class audit against the live library; operator sign-off on the proposed dates (or pattern fix + re-run). 3. **Deploy master to prod** — migrations auto-run (nullable columns, additive). Reads switch to the mixed-row-safe contract immediately; unconverted rows serve via the per-source fallback conventions. No client-visible break: the old clients (FV MetadataView, Apollo) still read the frozen legacy `date_taken`, byte-identical. 4. **Drain** — the #118 drain runs per watcher tick (500 rows/library/tick). Expect ~45 min for 21k rows. Watch: `utc_backfill` log lines (converted/skipped_missing/unresolved/day_changed), `GET /libraries` `date_backfill_remaining`, and the `imageserver_date_backfill_remaining` gauge. 5. **Floor audit** — SQL the remainder: `SELECT library_id, rel_path, date_taken_source FROM image_exif WHERE date_utc IS NULL;` Spot-check a sample with `exiftool -DateTimeOriginal -CreateDate <abs-path>`; every row should show empty date tags (edited/exported files). Any row that has tags in the file is a real bug — stop and chase. 6. **Coordinated client flip** — in one window: Apollo #37 (web UI, branch `feature/photo-chat-imageapi-sync`) and FileViewer-React #33 (mobile, `main` @ `978961c` or later). Both flip to the new-contract triple and delete their offset-shift/re-anchor helpers. 7. **Reels** — the nightly pregen regenerates day-span reels in the owner's display zone; `day_changed` invalidations during the drain already removed shifted reels. Verify: today's day-reel `month_day` equals the day the `/memories` tiles show; pregen zone = prefs `display_tz` = the tiles' zone. 8. **Verification battery** — photo-for-photo web/mobile agreement on the same photo; a video's date matching a same-instant photo; timeline scrubber/track agreement; a Japan-zone photo showing its camera wall clock while grouping under the display-zone day; a manual-date round-trip; insight prompt date == UI date (video + travel-zone photo); MetadataView (now on the new fields) agreeing with memories tiles to the hour. 9. **Rollback drill** — flip one client back to the legacy field: values must be byte-identical to pre-cutover (legacy columns were never touched); re-flip forward. Server-side rollback = revert to pre-#119 master; the new triple is inert data on that build. 10. **Week of observation** — no date-related operator reports; the spec's user stories 1–24 verifiable in daily use. ## Acceptance criteria - [ ] Preflight passed: `TZ` set, exiftool + ffmpeg on PATH, DB backup taken, master ≥ `7f34802`. - [x] Audit utility run against the live library; operator sign-off recorded (or pattern fixes applied and re-run). - [x] Drain reaches its **stable floor**: `more_remain=false` with `unresolved` constant across ≥3 consecutive ticks; `GET /libraries` `date_backfill_remaining` flat. - [x] Floor audit: every remaining `date_utc IS NULL` row verified tag-free by exiftool (sample ≥ 10 or 100%, whichever is smaller); zero rows with in-file tags. - [x] All three consumers (server reads, web UI, mobile) on the new contract in one window; no sustained mixed-convention window after the client deploys. - [x] Photo-for-photo date agreement verified between web UI and mobile app (including a travel-zone photo and a video). - [ ] No offset-shift call sites remain in either consumer repo (search returns only the deletion diffs). - [ ] Insight prompt date equals the UI date for the same photo, including a video and a Japan-zone photo. - [ ] Today's day-reel `month_day` matches the `/memories` day tiles; pregen zone == `display_tz` pref. - [ ] Rollback verified: a client on the legacy field sees byte-identical values; re-flip forward works. - [ ] After a week of normal use, no date-related reports from the operator (the spec's user stories 1–24 all verifiable in daily use). ## Blocked by - ImageApi #117 (audit utility) — **merged** (PR #123) - ImageApi #118 (backfill drain) — **merged** (PR #124) - ImageApi #119 (reads + insight context on the new contract) — **merged** (PR #125) - ImageApi #152 (`/photos` date filters on the true-instant contract) — open - Apollo #37 (web UI flip + re-anchor deletion) — open, cross-repo - FileViewer-React #33 (viewer flip) — open, cross-repo

cameron commented

2026-09-24 23:01:21 +00:00

External blockers: Apollo #37 (Apps/Apollo#37) and FileViewer-React #33 (Apps/FileViewer-React#33). Server-side blockers: #117, #118, #119. This is the coordination ticket — run it last, after the audit sign-off.

External blockers: Apollo #37 (https://git.crkc.xyz/Apps/Apollo/issues/37) and FileViewer-React #33 (https://git.crkc.xyz/Apps/FileViewer-React/issues/33). Server-side blockers: #117, #118, #119. This is the coordination ticket — run it last, after the audit sign-off.

cameron referenced this issue

2026-09-25 19:30:41 +00:00

feat(dates): true-instant date contract — container dates, fs branch, manual edit (#114, #115) #121

cameron added a new dependency 2026-09-25 20:10:56 +00:00

#119 Dates: reads + insight context on the new contract (mixed-row safe)

cameron added a new dependency 2026-09-25 20:11:23 +00:00

#117 Dates: pre-cutover audit utility for the filename date class

cameron added a new dependency 2026-09-25 20:11:23 +00:00

#118 Dates: the backfill drain — convert mixed-convention rows, progress surface, day-boundary invalidation

cameron referenced this issue

2026-09-25 20:11:56 +00:00

SPEC: true-instant date contract — one clock for the system #113

cameron removed a dependency 2026-09-25 20:12:44 +00:00

#118 Dates: the backfill drain — convert mixed-convention rows, progress surface, day-boundary invalidation

cameron added a new dependency 2026-09-25 20:12:45 +00:00

#118 Dates: the backfill drain — convert mixed-convention rows, progress surface, day-boundary invalidation

cameron referenced this issue

2026-09-26 13:28:45 +00:00

feat(dates): backfill drain converts mixed-convention rows to true instants (#118) #124

cameron referenced this issue

2026-09-26 13:29:02 +00:00

Dates: the backfill drain — convert mixed-convention rows, progress surface, day-boundary invalidation #118

cameron referenced this issue

2026-09-27 02:48:09 +00:00

Dates: reads + insight context on the new contract (mixed-row safe) #119

cameron referenced this issue from a commit

2026-09-29 18:45:59 +00:00

Dates: serve the display wall clock (`wall_clock`) to clients

cameron referenced this issue from a commit

2026-09-29 18:45:59 +00:00

dev/e2e: wall_clock regression check + deterministic fixture dates

cameron referenced this issue

2026-09-29 18:46:03 +00:00

feat(dates): serve wall_clock + true_instant to clients (#113, #120) #146

cameron referenced this issue from a commit

2026-09-29 21:59:11 +00:00

Dates: serve the display wall clock (`wall_clock`) to clients

cameron referenced this issue from a commit

2026-09-29 21:59:11 +00:00

dev/e2e: wall_clock regression check + deterministic fixture dates

cameron referenced this issue from a commit

2026-09-29 22:01:24 +00:00

Merge pull request 'feat(dates): serve wall_clock + true_instant to clients (#113, #120)' (#146) from feature/wall-clock-dates into master

cameron commented

2026-09-30 00:41:30 +00:00

Progress (2026-09-29)

Server read surface extended — #146, merged and deployed to the test env. Beyond the triple, clients now get two server-computed fields:

wall_clock — naive ISO wall (2018-09-29T14:00:00) from date_view::row_display_wall, on /image/metadata (+ the date / date-clear / GPS mutation responses), /memories items and /photos/gps-summary. Clients render it verbatim.
true_instant — on /image/metadata, from row_true_instant (else filename instant, else earliest fs time). The anchor for SMS / location windows.

Why server-side rather than consumers computing instant + offset from the triple: the stable floor found above (0.5–2% of rows never get date_utc) would force every consumer to carry the per-source legacy conventions forever. With these fields the consumers do no zone math, and "insight prompt date == UI date" holds by construction (same function).

Consumer flips

FileViewer-React #33 → FVR PR #35: viewer date line, memories tiles, map callout and the SMS sheet read wall_clock; the SMS window anchors on true_instant; zero legacy-field formatting left. Under operator testing against the test env; merges in the coordinated window.
Apollo #37 should read wall_clock as well — deleting its re-anchor helpers then needs no replacement logic, and photo-for-photo web/mobile agreement (step 8) follows by construction.

New finding for the verification battery (spec user story 3): the mobile "View messages" window was anchored on the legacy date_taken (a wall stored as UTC for EXIF rows) while SMS-API dates are true instants — the window was off by the photo's UTC offset, 4–5 h even for photos taken at home. Fixed by true_instant (FVR #35). SMS-API itself verified correct: importers store backup epochs as UTC, the for-timestamp endpoint windows on the instant, and 40/40 sampled stored rows equal their source epochs exactly.

Automated coverage for step 8: dev/e2e/e2e.sh check (#145 harness + #146) asserts, for 97 synthetic fixtures, the expected wall_clock on metadata / memories / gps-summary and the expected true_instant — travel-zone photos (Tokyo / Paris / LA via GPS), no-GPS, filename-class, container video, mtime-only, byte-identical duplicate. 97/97 before and after the tracing rebase (#148). The e2e env pins TZ=America/New_York, mirroring the step-1 preflight.

🤖 Generated with Claude Code

## Progress (2026-09-29) **Server read surface extended — #146, merged and deployed to the test env.** Beyond the triple, clients now get two server-computed fields: - `wall_clock` — naive ISO wall (`2018-09-29T14:00:00`) from `date_view::row_display_wall`, on `/image/metadata` (+ the date / date-clear / GPS mutation responses), `/memories` items and `/photos/gps-summary`. Clients render it verbatim. - `true_instant` — on `/image/metadata`, from `row_true_instant` (else filename instant, else earliest fs time). The anchor for SMS / location windows. Why server-side rather than consumers computing instant + offset from the triple: the stable floor found above (0.5–2% of rows never get `date_utc`) would force every consumer to carry the per-source legacy conventions forever. With these fields the consumers do **no** zone math, and "insight prompt date == UI date" holds by construction (same function). **Consumer flips** - FileViewer-React #33 → FVR PR #35: viewer date line, memories tiles, map callout and the SMS sheet read `wall_clock`; the SMS window anchors on `true_instant`; zero legacy-field formatting left. Under operator testing against the test env; merges in the coordinated window. - **Apollo #37 should read `wall_clock` as well** — deleting its re-anchor helpers then needs no replacement logic, and photo-for-photo web/mobile agreement (step 8) follows by construction. **New finding for the verification battery (spec user story 3):** the mobile "View messages" window was anchored on the legacy `date_taken` (a wall stored as UTC for EXIF rows) while SMS-API dates are true instants — the window was off by the photo's UTC offset, 4–5 h even for photos taken at home. Fixed by `true_instant` (FVR #35). SMS-API itself verified correct: importers store backup epochs as UTC, the for-timestamp endpoint windows on the instant, and 40/40 sampled stored rows equal their source epochs exactly. **Automated coverage for step 8:** `dev/e2e/e2e.sh check` (#145 harness + #146) asserts, for 97 synthetic fixtures, the expected `wall_clock` on metadata / memories / gps-summary and the expected `true_instant` — travel-zone photos (Tokyo / Paris / LA via GPS), no-GPS, filename-class, container video, mtime-only, byte-identical duplicate. 97/97 before and after the tracing rebase (#148). The e2e env pins `TZ=America/New_York`, mirroring the step-1 preflight. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

cameron referenced this issue

2026-09-30 00:41:33 +00:00

Dates: /photos date_from/date_to filters on the true-instant contract #152

cameron added the wayfinder:task label 2026-09-30 00:47:21 +00:00

cameron added a new dependency 2026-09-30 00:51:51 +00:00

#152 Dates: /photos date_from/date_to filters on the true-instant contract

cameron added a new dependency 2026-09-30 00:51:52 +00:00

Apps/Apollo#37 - Dates: consume the true-instant contract, delete re-anchoring (big-bang flip)

cameron added a new dependency 2026-09-30 00:51:52 +00:00

Apps/FileViewer-React#33 - Dates: consume the true-instant contract in the viewer (big-bang flip)

cameron referenced this issue from a commit

2026-10-11 01:15:47 +00:00

feat(dates): true_instant + wall_clock on /photos/exif (#113)

cameron referenced this issue

2026-10-11 01:42:38 +00:00

feat(dates): true_instant + wall_clock on /photos/exif (#113) #161

cameron referenced this issue

2026-10-11 02:55:05 +00:00

Dates: exiftool GPS hemisphere refs ignored ("West" != "W") — western photos resolve to UTC+8 #163

cameron commented

2026-10-17 03:38:32 +00:00

Prod sign-off confirmed by the owner (2026-10-16). Post-cutover date bugs found along the way are fixed: #163 (exiftool GPS hemisphere refs, PR #165), #160 (filename year gate + re-resolve migration, PR #176) and #152 (calendar-day filters, PR #178 + FileViewer-React #47). Every consumer is on the new contract: FileViewer-React #33, Apollo #37.