Listings matched children on four columns at once (parent_id, album_id,
season_id, series_id) because Jellyfin's ParentId is the storage parent,
not the logical one. The OR defeated the planner into a full scan, and it
was wrong: every episode carries its series id, so a series listed all its
episodes beside its seasons (on both the browse and Downloads surfaces).
- Migration 027 adds items.container_id, a VIRTUAL generated column
(episode -> season/series/parent, season -> series, track -> album,
else parent) indexed with (sort_name, name), so a listing is one
ordered index range and every write path is covered untouched.
- Containers never cached (an episode that arrived via Next Up) get
placeholders named from the child's own fields, in the migration and
on every cache write, so offline navigation stays series -> season.
- The six queries that built the set of every downloaded item in a CTE
(get_item, latest, recently played, search, favourites, by-person,
Downloads) now check availability per row with one shared predicate.
- PRAGMA optimize at open gives the planner statistics.
Benchmark (~110k items, desktop): series listing ~80 ms -> <1 ms;
migration 027 upgrades that database in ~0.1 s (0.2 s on the Fairphone).
Tests first: a series listing its episodes, and the Downloads series
drill, both failed before the change.
A series page took about a second to show its seasons on a phone, every
visit, although they were cached. Three things stacked up:
- One SQLite connection behind one mutex served the whole app, so every
read queued behind every write. The database now has one owner: a
writer thread for writes and a pool of read-only WAL connections for
reads. synchronous = NORMAL and a busy timeout on every connection.
- The listing query built the set of every available item in the
database before filtering to the parent (~80 ms on a desktop for a
100k-item cache), then fetched user data one row at a time. It now
checks availability per row, uses the hierarchy indexes (1.5 ms on
the same benchmark) and batches the user-data lookup.
- A cache read that missed the 100 ms fast path was set aside until the
server answered. It is now raced against the server; whichever answers
first with content wins.
On the Fairphone, Frasier's season and episode lists now come from
cache in 34-133 ms (was 600-1030 ms waiting on the server).
Fixes found on the way, each with a test that failed first:
- sync_queue_mutation could return another mutation's row id: the id
came from a second trip to the shared connection. insert() reads it in
the same job.
- save_to_cache switched foreign keys off on the shared connection
across its awaits, so concurrent writes ran unchecked. The toggle now
lives inside one writer job, and a page is one transaction instead of
one commit per row.
Also: thumbnail LRU touches no longer block the lookup; unused
tokio-rusqlite dropped. Design and invariants in
docs/architecture/08-database-design.md (Connection ownership, Listing
query shape) and 03-data-flow.md.
dtourolle