Constant MIGRATION_027 

const MIGRATION_027: &str = r#"
ALTER TABLE items ADD COLUMN container_id TEXT GENERATED ALWAYS AS (
    CASE item_type
        WHEN 'Episode' THEN COALESCE(season_id, series_id, parent_id)
        WHEN 'Season'  THEN COALESCE(series_id, parent_id)
        WHEN 'Audio'   THEN COALESCE(album_id, parent_id)
        ELSE parent_id
    END
) VIRTUAL;

CREATE INDEX IF NOT EXISTS idx_items_container ON items(container_id, sort_name, name);

INSERT OR IGNORE INTO items (id, server_id, library_id, name, item_type, is_folder, series_id, series_name)
SELECT season_id, server_id, MAX(library_id), COALESCE(MAX(season_name), 'Season'), 'Season', 1,
       MAX(series_id), MAX(series_name)
FROM items
WHERE item_type = 'Episode' AND season_id IS NOT NULL
GROUP BY season_id;

INSERT OR IGNORE INTO items (id, server_id, library_id, name, item_type, is_folder)
SELECT series_id, server_id, MAX(library_id), COALESCE(MAX(series_name), 'Series'), 'Series', 1
FROM items
WHERE item_type IN ('Episode', 'Season') AND series_id IS NOT NULL
GROUP BY series_id;

INSERT OR IGNORE INTO items (id, server_id, library_id, name, item_type, is_folder, album_artist)
SELECT album_id, server_id, MAX(library_id), COALESCE(MAX(album_name), 'Album'), 'MusicAlbum', 1,
       MAX(album_artist)
FROM items
WHERE item_type = 'Audio' AND album_id IS NOT NULL
GROUP BY album_id;
"#;

One canonical “which container lists this item” link, plus an index that serves a listing in display order.

Jellyfin’s ParentId is the storage parent, not the logical one: in a series without season folders an episode’s ParentId is the series while its SeasonId names a virtual season, and a cached episode may arrive without its season row at all. So listings matched children on four columns at once (parent_id, album_id, season_id, series_id). That was slow — the OR defeated the planner into walking the whole table — and wrong: every episode carries its series id, so a series answered with its seasons and all their episodes.

container_id resolves the logical container once, by rule: an episode belongs to its season (else its series, else its parent), a season to its series, a track to its album, anything else to its parent. It is a VIRTUAL generated column, so every write path — cache, downloads, catalog crawl — is covered without touching any of them, and it cannot drift from the columns it is computed from. The index covers the listing’s ORDER BY sort_name, name (sort_name is usually NULL in the cache).

The placeholders keep offline navigation intact: an episode whose season or series row was never cached used to surface directly under the series through the series_id match. Now it lists under its season, so the season (and series, and a track’s album) must exist. They are built from the names the child rows already carry, with synced_at NULL — they only show when a download makes them available, and a real row from the server replaces them wholesale (save_to_cache upserts every field).

TRACES: UR-002, UR-007 | DR-013