Apps/ImageApi
1
0 Fork 0
Code Issues 8 Pull Requests Releases Wiki Activity

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

New Issue
Closed
opened 2026-09-24 22:56:07 +00:00 by cameron · 1 comment
cameron commented 2026-09-24 22:56:07 +00:00
Owner
Copy link
Copy source

Parent

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

What to build

The backfill drain that converts the existing mixed-convention rows to true instants, following the exact shape of the repo's existing per-tick drains: runs on every watcher tick, capped per tick, idempotent, library-online-gated, errors don't burn the cap, and it batches external-tool calls (one subprocess per batch, as the existing date drain does). It selects rows where the new instant column is still null, runs the re-based waterfall (all four sources: EXIF, container, filename class, filesystem time), and writes the new columns — it never touches the legacy columns.

It also owns the two operational surfaces: a progress count (rows remaining to convert) exposed where the operator already looks at status, and day-boundary invalidation — when a converted instant lands on a different display-zone calendar day than the row's legacy value implied, that day's derived day data (daily summaries, pre-generated reels) is marked stale for the existing nightly regeneration, and rows whose day is unchanged invalidate nothing.

Videos are in scope: they drain through the same waterfall (container branch) in the same pass.

Acceptance criteria

  • Seeded mixed-convention rows (one per source: exif, exiftool/container, filename, fs_time) convert to the correct true instants with correct offsets and audit values; the legacy columns are untouched.
  • A second pass over fully-converted rows changes nothing (idempotent) and the progress count reads 0.
  • The per-tick cap holds (a 14k-row backlog drains over ticks, not in one tick); errors on individual rows don't consume the cap.
  • A library whose root is unavailable is skipped this tick and picked up next tick (online gate), with no partial conversion of its rows.
  • Progress surface reports rows remaining; it decrements correctly across ticks and reaches 0 on the fixture.
  • A row whose converted day differs from its legacy day (display zone) marks exactly that day's derived day data stale; rows whose day is unchanged mark nothing; a row straddling UTC midnight but not the display-zone midnight marks nothing.
  • Stale-marked derived day data regenerates on the existing nightly cadence (no new scheduler needed).
  • The drain and the batch external-tool call run at the existing drain seam (in-memory SQLite + fixture files); no new test infrastructure.

Blocked by

  • ImageApi #115 (container/fs/manual branches)
  • ImageApi #116 (filename class conventions + priority)
## Parent ImageApi #113 — SPEC: true-instant date contract (ADR-0001). ## What to build The backfill drain that converts the existing mixed-convention rows to true instants, following the exact shape of the repo's existing per-tick drains: runs on every watcher tick, capped per tick, idempotent, library-online-gated, errors don't burn the cap, and it batches external-tool calls (one subprocess per batch, as the existing date drain does). It selects rows where the new instant column is still null, runs the re-based waterfall (all four sources: EXIF, container, filename class, filesystem time), and writes the new columns — it never touches the legacy columns. It also owns the two operational surfaces: a progress count (rows remaining to convert) exposed where the operator already looks at status, and day-boundary invalidation — when a converted instant lands on a different display-zone calendar day than the row's legacy value implied, that day's derived day data (daily summaries, pre-generated reels) is marked stale for the existing nightly regeneration, and rows whose day is unchanged invalidate nothing. Videos are in scope: they drain through the same waterfall (container branch) in the same pass. ## Acceptance criteria - [ ] Seeded mixed-convention rows (one per source: exif, exiftool/container, filename, fs_time) convert to the correct true instants with correct offsets and audit values; the legacy columns are untouched. - [ ] A second pass over fully-converted rows changes nothing (idempotent) and the progress count reads 0. - [ ] The per-tick cap holds (a 14k-row backlog drains over ticks, not in one tick); errors on individual rows don't consume the cap. - [ ] A library whose root is unavailable is skipped this tick and picked up next tick (online gate), with no partial conversion of its rows. - [ ] Progress surface reports rows remaining; it decrements correctly across ticks and reaches 0 on the fixture. - [ ] A row whose converted day differs from its legacy day (display zone) marks exactly that day's derived day data stale; rows whose day is unchanged mark nothing; a row straddling UTC midnight but not the display-zone midnight marks nothing. - [ ] Stale-marked derived day data regenerates on the existing nightly cadence (no new scheduler needed). - [ ] The drain and the batch external-tool call run at the existing drain seam (in-memory SQLite + fixture files); no new test infrastructure. ## Blocked by - ImageApi #115 (container/fs/manual branches) - ImageApi #116 (filename class conventions + priority)
cameron self-assigned this 2026-09-25 22:59:40 +00:00
cameron commented 2026-09-26 13:29:02 +00:00
Author
Owner
Copy link
Copy source

Resolved by PR #124 (feature/date-backfill-drain).

What landed

  • backfill::backfill_utc_dates runs at the existing date-drain seam, right after backfill_missing_date_taken, inside the per-library online gate.
    • It selects date_utc IS NULL AND date_taken IS NOT NULL, backed by the new partial index idx_image_exif_utc_backfill.
    • It runs resolve_dates_batch(paths, {}): one exiftool subprocess per batch, the same call shape as the #117 audit.
    • It writes only the new-contract triple. The legacy pair is untouched.
    • The cap is DATE_BACKFILL_MAX_PER_TICK, shared with the legacy drain.
  • Progress:
    • Prometheus imageserver_date_backfill_remaining, refreshed every tick.
    • date_backfill_remaining on each GET /libraries entry, a live count of date_utc IS NULL.
  • Day-boundary invalidation: the day is compared in the display zone (user_ai_prefs.display_tz) between the legacy value, rendered as an instant the way the legacy /memories SQL did, and date_utc. When the day differs, the drain deletes and unlinks the day-span precomputed_reels rows for this library and all that cover either month-day. The nightly pre-gen regenerates them; no new scheduler.

Decisions made during implementation

  • Daily conversation summaries are out of scope for invalidation. They are SMS-derived (daily_summary_job.rs groups SmsMessage by date) and don't depend on photo dates.
  • Legacy manual rows convert through camera_tz::manual_date_resolution, the same path a manual edit uses today. They are never re-resolved from the file.
  • No downgrades: a row whose legacy date came from EXIF or exiftool is not written if it would now resolve to fs_time. This covers exiftool being missing or failing on that file.
  • Cursor: a per-library resume cursor is carried across ticks and wraps at the end of the table. Unconvertible rows (missing file, no faithful resolution) never spend the cap and can't stall the rows behind them.

Heads-up for #119 / #120

  • Reads, including the reel selector and /memories, still use legacy date_taken. Until #119 lands, a regenerated day reel selects the same media, and a deleted day reel for today stays absent until the next nightly run.
  • Pre-gen keys its day on the fixed tz_offset_minutes. The drain uses the IANA display zone. These agree except around DST or when the prefs disagree. #119/#120 should move pre-gen onto display_tz.
  • The pre-gen fallback reels::fixed_tz_offset stores seconds rather than minutes (local_minus_utc()). The drain tolerates this, but it's a latent bug.
Resolved by PR #124 (`feature/date-backfill-drain`). **What landed** - `backfill::backfill_utc_dates` runs at the existing date-drain seam, right after `backfill_missing_date_taken`, inside the per-library online gate. - It selects `date_utc IS NULL AND date_taken IS NOT NULL`, backed by the new partial index `idx_image_exif_utc_backfill`. - It runs `resolve_dates_batch(paths, {})`: one exiftool subprocess per batch, the same call shape as the #117 audit. - It writes only the new-contract triple. The legacy pair is untouched. - The cap is `DATE_BACKFILL_MAX_PER_TICK`, shared with the legacy drain. - Progress: - Prometheus `imageserver_date_backfill_remaining`, refreshed every tick. - `date_backfill_remaining` on each `GET /libraries` entry, a live count of `date_utc IS NULL`. - Day-boundary invalidation: the day is compared in the display zone (`user_ai_prefs.display_tz`) between the legacy value, rendered as an instant the way the legacy `/memories` SQL did, and `date_utc`. When the day differs, the drain deletes and unlinks the `day`-span `precomputed_reels` rows for this library and `all` that cover either month-day. The nightly pre-gen regenerates them; no new scheduler. **Decisions made during implementation** - **Daily conversation summaries are out of scope for invalidation.** They are SMS-derived (`daily_summary_job.rs` groups `SmsMessage` by date) and don't depend on photo dates. - **Legacy `manual` rows** convert through `camera_tz::manual_date_resolution`, the same path a manual edit uses today. They are never re-resolved from the file. - **No downgrades:** a row whose legacy date came from EXIF or exiftool is not written if it would now resolve to `fs_time`. This covers exiftool being missing or failing on that file. - **Cursor:** a per-library resume cursor is carried across ticks and wraps at the end of the table. Unconvertible rows (missing file, no faithful resolution) never spend the cap and can't stall the rows behind them. **Heads-up for #119 / #120** - Reads, including the reel selector and `/memories`, still use legacy `date_taken`. Until #119 lands, a regenerated day reel selects the same media, and a deleted day reel for *today* stays absent until the next nightly run. - Pre-gen keys its day on the fixed `tz_offset_minutes`. The drain uses the IANA display zone. These agree except around DST or when the prefs disagree. #119/#120 should move pre-gen onto `display_tz`. - The pre-gen fallback `reels::fixed_tz_offset` stores seconds rather than minutes (`local_minus_utc()`). The drain tolerates this, but it's a latent bug.
cameron added the wayfinder:task label 2026-09-30 00:47:19 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Labels
Clear labels
needs-info
Waiting on reporter for more information
 needs-triage
Maintainer needs to evaluate this issue
 ready-for-agent
Fully specified, ready for an AFK agent
 ready-for-human
Requires human implementation
 wayfinder:grilling
Wayfinder child ticket: grilling
 wayfinder:map
Wayfinder map: the single issue holding Notes / Decisions-so-far / Fog
 wayfinder:prototype
Wayfinder child ticket: prototype
 wayfinder:research
Wayfinder child ticket: research
 wayfinder:task
Wayfinder child ticket: task
 wontfix
Will not be actioned
No labels wayfinder:task
Milestone
No items
No Milestone
Assignees
Clear assignees
cameron
No Assignees cameron
1 Participants
Notifications
Due Date
No due date set.
Blocks
#120 Dates: big-bang cutover — drain to zero, coordinated flip, verification
Apps/ImageApi
Depends on
#115 Dates: container (video) dates, filesystem branch, manual edit — true instants
Apps/ImageApi
#116 Dates: filename date class — per-pattern conventions + filename-over-EXIF priority
Apps/ImageApi
Reference: Apps/ImageApi#118