The database

The single most important idea in the schema.

A refresh replaces a source's catalogue wholesale: delete every row, insert the new ones. Every primary key therefore changes, and anything keyed by primary key is destroyed.

So each row carries a stable key — the provider's own identity for that item — and everything that must outlive a refresh is keyed by it: favourites, resume points, guide entries, cached metadata. It is why favourites are a separate table rather than a boolean column, and the join is done in SQL so a twenty-thousand-row list is not re-mapped every time one is toggled.

Table	Holds	Note
sources	Configured playlists and accounts	No credentials. Those are encrypted in DataStore
channels	Every playable item: live, film and series rows	Replaced wholesale on refresh
profiles	Who is watching	Owns favourites and resume points, and nothing else
favorites	Favourites	Keyed by profile + source + stable key
resume_positions	Where you stopped, and enough to list it as history	Keyed by profile + stable key. Denormalised on purpose — see below
programmes	Guide entries	Source-agnostic: adding XMLTV later needs no migration
title_metadata	Cached film and series information	Keyed by cleaned title and kind
channel_logos	The optional logo reference index	A cache of a public catalogue, not user data
category_overrides	Local renames and hides	Keyed by the provider's own title

resume_positions carries the title, artwork, duration and — when the row is an episode — its season and episode numbers. That looks like duplication of the channel row, and a join would seem tidier.

It would also be wrong. An episode is never a row in the channel table. An episode's identity is its stream URL; episodes are fetched from the panel per series and held for a session. A history list that joined would show films and silently drop every episode — which is most of what anyone actually resumes.

Rows written before those columns existed keep resuming correctly and are excluded from the history list by their empty title: a tile with no name and no artwork is worse than one row of history missing.

Two tables gained a profileId in their primary key, not merely as a column: favorites is keyed by profile, source and stable key, and resume_positions by profile and stable key. Two people can therefore hold the same favourite and stop at different points in the same film, which is the entire feature.

The foreign key to profiles cascades on delete, and that is doing real work. Guest data does not outlive its session because the database enforces it, not because every screen remembers to help: deleting the guest row takes its favourites and its resume points with it, atomically, from one statement.

Above the database, the active profile is a StateFlow with a synchronous value. Every profile-scoped read needs the id — a browse query, a favourite toggle, a resume point written from the player — and a suspending lookup in each of those would be a database round trip per call. Held in memory, it is a field read.

The id that matches no row

Before anybody has chosen, the active id is 0. Ids are generated from 1, so that value matches nothing: every profile-scoped read comes back empty and every write lands nowhere.

That is not a hole to be guarded at each call site — it is the correct behaviour for a moment when the app does not yet know whose data it would be touching, and it means no screen needs a special case for "no profile". The chooser then stands in front of the app so the question does not stay open.

Cached film and series information is keyed by the cleaned search title plus the kind, rather than by channel id.

By id, a refresh would reassign every id and throw the whole cache away for no reason. By title alone, "Fargo" the film and "Fargo" the series would be one record, and whichever tab you opened first would answer for the other.

Negative answers are cached too. "The service was asked and had nothing" is an answer, and re-requesting it on every visit is the most wasteful thing a cache can do.

Eleven versions so far, every one with a real migration. Destructive migration is deliberately not enabled — silently dropping a user's configured sources on a schema change would be a data-loss bug, and an acceptance criterion requires version mismatches to be handled explicitly rather than by discarding state.

The schema JSON is exported on every build and committed. Room validates the live database against it at open time, so a migration that does not produce exactly what the entities declare fails loudly at launch rather than subtly later.

Version	Change
1 → 2	Resume positions
2 → 3	Programmes, with its two indices
4 → 5	Film metadata cache
5 → 6	Category overrides
6 → 7	Metadata reworked to cover series as well as films
7 → 8	History columns on resume positions
8 → 9	Channel logo index
9 → 10	The two missing indices — see the browse defects
10 → 11	Profiles, and re-keying favourites and resume positions onto them

Version 10 is pure index creation: no data moves and no columns change, so it cannot lose anything. It costs a moment on the first launch after upgrading, once, against a saving on every emission thereafter.

Version 11 is the opposite kind, and the most careful one written so far. SQLite cannot add a column to a primary key, so both tables are rebuilt: create the new shape, copy every row across, drop the old, rename. The copy is where the user's data is, and it is a single INSERT … SELECT that assigns every existing row to profile 1.

Profile 1 is created by the same migration and named Default, because everything on the device at that moment belongs to whoever has been using the app. An upgrade that offered a fresh empty profile instead would look exactly like having lost every favourite.