yellowjacket/.planning/plans/completed/002-data-lifecycle-architecture.md

# 002 — Data lifecycle architecture

**Status:** completed (first tranche); follow-ups tracked below
**Branch:** main
**Created:** 2026-07-26

## Problem

An audit of asset and row cleanup found five leaks, four of which shared
one root cause: **deletion logic was hand-written per call site and lived
far from the thing being deleted.** `RemoveLibrary` knew about ten tables
because someone enumerated them once; migration 32 added an eleventh and
nothing noticed. Files written by `explore` had no cleanup counterpart
anywhere. A function that evicted expired cache rows was written and
never called.

Findings, in severity order:

1. **`RemoveLibrary` was broken for any scanned library.** `tagging_items`
   holds `FOREIGN KEY(library_id) REFERENCES libraries(id)` with no
   `ON DELETE` clause and was never cleared, so `DELETE FROM libraries`
   failed with `FOREIGN KEY constraint failed (787)` and rolled back the
   whole removal. Every scanned library has `tagging_items` rows (the
   scan upserts one per album folder), so this fired on essentially every
   real removal. `RemoveLibrary` had zero test coverage.
2. **Artist images were never deleted by anything.** No `os.Remove` in
   `explore`, no `DELETE FROM artist_images` in the codebase. Unbounded
   in the number of artists ever browsed in Explore, most of whom are not
   in the library.
3. **Cover art size variants leaked on removal.** Only the base
   `cover_art.file_path` was unlinked; the `_sm/_md/_lg` files beside it
   are derived filenames, not rows, so three files per cover survived.
4. **`http_cache` was never pruned.** `Cache.Evict()` existed with no
   callers. Reads filter on `expires_at`, so expired rows were inert but
   accumulated for the life of the install.
5. **Cover-art proxy cache was never pruned.** No eviction, no size cap.

## Approach

Rather than patch five holes, classify the data so the *class* of bug
becomes hard to write. Everything persisted falls on two axes —
regenerability and cost of regeneration — which collapse to four kinds:

| Kind | Regenerable? | Deletion policy |
|---|---|---|
| **Owned** — projection of the user's files | Yes, by rescan | Follows the files |
| **Authored** — user-created, no other copy | **No** | Explicit user action only |
| **Derived** — computed from owned | Yes, cheaply | Free; must never block owned deletion |
| **Cache** — network or dump sourced | Yes, expensively | TTL/age eviction, never cascade |

The classification is not just vocabulary — it produces the right fix for
each finding. Finding 1 is derived data acting as a referential parent of
owned data, which the taxonomy makes categorically illegal. Finding 2 is
cache data that never needed owner-linked cleanup at all; it wants age
eviction. Finding 3 is derived data that must be swept against a live set
rather than tracked individually.

A Go interface was considered and rejected: the only polymorphic consumer
is the janitor, the substrates have nothing in common (SQL rows, an FTS
virtual table, a view, three directories of JPEGs, a 900 MB index), and
provenance is a static fact better enforced by package boundaries than by
methods an implementation may lie about. A declarative catalog gets the
same benefit for a tenth of the cost.

## What shipped

**`backend/datamap`** — the catalog. Every table, view, and asset
directory declared with its `Kind`, its `Lifetime` (`cascade`, `set-null`,
`swept`, `retained`), and a note explaining the classification. Plain data
with no service dependencies, so tests can assert it against a live
schema. FTS5 shadow tables resolve to their parent.

Tests that give it teeth (`backend/datamap/datamap_test.go`):

- `TestCatalogCoversSchema` — every table in `sqlite_master` is claimed by
  exactly one entry. **A new table fails the build until somebody states
  what it is and how it dies.**
- `TestCatalogHasNoStaleEntries` — the reverse, catching drift.
- `TestNoActionForeignKeysAreDeclaredSwept` — a `NO ACTION` foreign key
  blocks its parent's deletion, so its table must declare `swept`. This is
  the exact shape of finding 1, now caught at CI time.
- `TestLifetimesMatchSchema` — declared cascade/set-null must match what
  SQLite actually enforces.
- `TestAuthoredCascadesAreDeliberate` — authored data is unrecoverable, so
  a cascade onto it needs an explicit exemption.

**`backend/maintenance`** — the janitor. A registry of named jobs with
per-job minimum intervals, run at startup-idle and on a 6h tick. Policies
follow the taxonomy: derived data sweeps against a live set, cache data
ages out. Registered in one place (`app.go: startJanitor`) so the full set
of janitorial work is a single visible list.

Jobs: `http-cache-evict` (6h), `covers-sweep` (24h, live set from
`cover_art` expanded via `CoverArtFileSet`), `artist-images-sweep` (24h,
keeps art for library artists indefinitely, evicts browsed-artist art
after 90d), `cover-art-proxy-sweep` (24h, 30d age eviction).

The covers sweep refuses to act on an empty live set — that means the
query failed to see the table, not that every cover is garbage.

**Leak tests** (`backend/library/leak_test.go`) — driven by the catalog
rather than a hardcoded list, so new tables are covered the moment they
are catalogued:

- `TestRemoveLibraryLeavesNoOwnedOrDerivedRows` — removing the only
  library leaves no owned or derived rows, except those in
  `staleTolerated` with a written reason.
- `TestRemoveLibraryPreservesAuthoredData` — authored data survives.
- `TestSweptTablesAreActuallySwept` — a table declaring `swept` that
  nothing sweeps is caught.

All three were verified to fail when the finding-1 fix is reverted.

**Fixes** — `tagging_items` cleared inside the removal transaction
(`crud.go` step 17); `CoverArtFileSet` expands originals to variants and
the legacy `_thumb` name; `Cache.Evict` logic moved into a registered job.

**Incidental:** `Library.emit` — `runtime.EventsEmit` calls `log.Fatalf`
on a context without a Wails runtime, which killed the test binary and
made the whole package untestable. All ten emits in the package now route
through a nil-safe helper. This also removes a real crash risk for
background workers that outlive their context.

## Follow-ups

**`audio_files` is a mixed-kind table.** `play_count`, `last_played`, and
`tag_status` are *authored* data living in an *owned* table. Orphan
cleanup treats the whole row as regenerable, which is why renaming a file
destroys its play count — the row is deleted and re-imported fresh. This
is the strongest argument for splitting authored per-track state into its
own table keyed by something more stable than a path. Related: an
audio-stream content hash (excluding tag blocks, so it survives
retagging) would let a rename be recognised as the same file. Deliberately
out of scope here; it is a schema change plus a rename-detection pass, not
a cleanup fix.

**Cascade adoption.** Fourteen of nineteen foreign keys are `NO ACTION`.
Converting them to `CASCADE` would delete a lot of hand-written orphan
sweeps, but SQLite cannot add `ON DELETE` via `ALTER TABLE` — each needs
the 12-step table rebuild. Note the ordering constraint: cascades delete
rows silently, so any code that collects file paths *before* deleting rows
(as `RemoveLibrary` does for cover art) breaks under cascade. Mark-and-
sweep must land first; the two compose, cascade plus path-collection does
not.

**Consolidate the ten orphan sweeps.** `DELETE ... WHERE id NOT IN (...)`
appears ten times across `crud.go`, `dbsync.go`, `smartplaylist.go`, and
`database.go`. One shared `sweepOrphans(tx)` would shrink the surface where
a new table can be forgotten. Worth doing opportunistically rather than as
a big-bang refactor.

**Storage settings pane.** The catalog knows every table and directory and
its kind; the janitor already computes bytes freed. A settings pane showing
per-kind disk usage with "clear cache" and "rebuild derived data" buttons
is now mostly a UI job.