Skip to content

SQLite Database Reference

The project stores its operational state in a SQLite database that lives under sqlite_db/. All schemas are created and maintained by SQLiteConnector; no manual migrations are required. The connector will automatically create the database directory, initialize the tables, and keep library metadata in sync as Emby changes over time.

Library metadata tables

library_items

Central catalogue of every Movie and Episode imported from Emby. Each row contains:

  • Identity: item_id (primary key), item_type, item_name.
  • Series context: series_id, series_name, season_number, episode_number for episodic content.
  • Runtime and chronology: runtime_ticks (with generated seconds/minutes columns), premiere_date, and date_created (when the item was added to Emby - its DateCreated, stored since Oct 2026, so NULL for items removed before then; used by Freshness).
  • Descriptive metadata: overview, community_rating, production_year.
  • File attributes: file_path, container, video_codec, resolution_width, resolution_height.
  • Timestamps: last_updated auto-populates whenever the row is refreshed.

Indexes (idx_library_series, idx_library_type, idx_series_season_ep, idx_library_name, idx_library_year) keep lookups responsive for common filtering patterns.

item_genres

A many-to-many bridge that stores the genre tags attached to each library item. Genres sourced directly from Emby retain their numeric IDs; otherwise a deterministic CRC32-derived ID is generated so joins remain stable across runs.

item_tags

Stores free-form tag labels (e.g., “Kids”, “4K”) associated with a library item. Tags can originate from Emby’s TagItems collection or the fallback Tags array. A composite primary key (item_id, tag_id) prevents duplicates.

item_provider_ids

Normalises the ProviderIds payload emitted by Emby. Each row captures a provider namespace (TMDB, IMDB, TVDB, etc.) alongside the provider-specific identifier so you can map items back to external datasets. Records are replaced on every ingest. Provider names are stored lowercase (Emby mixes IMDB and Imdb), so join on e.g. provider = 'imdb'.

Removed items

Items that disappear from Emby are not deleted: library_items.removed_at is set instead (and cleared if the item comes back), so watch history for those titles keeps its runtime, genres and provider IDs. Filter with removed_at IS NULL for the current library.

Note: Emby item IDs are not stable across a server rebuild - the server was rebuilt between Sep 2025 and Jul 2026 and every item got a new ID. Use provider IDs (IMDb) to link the same title across ID changes. User IDs changed at the same time.

TMDB reference tables

SQLiteConnector._INIT_create_tmdb_schemas() provisions two lookup tables:

  • tmdb_movie_genres
  • tmdb_tv_genres

Populate them with SQLiteConnector.ingest_tmdb_movie_tv_genres(...) so item_genres can map Emby names to canonical TMDB IDs. When a genre label is missing from the TMDB response, the connector falls back to the deterministic hash strategy described above.

TMDB title details

Content metadata for recommendations (ContentKNN), filled incrementally by SQLiteConnector.ingest_tmdb_title_details(TMDB.fetch_title_details, TMDB.find_by_imdb_id), which the daily refresh runs.

  • tmdb_title_details: one row per (media_type, tmdb_id), with media_type being movie or tv.
  • Columns: imdb_id (TMDB's), title, overview, original_language, collection (movie franchise).
  • JSON list columns: genres, keywords and people ({id, name, role}, role being cast/director/writer/creator).
  • found = 0 records a TMDB 404, so a dead ID isn't retried every day.
  • fetched_at drives refreshes.
  • tmdb_imdb_lookups: IMDB ID -> (media_type, tmdb_id) from TMDB's /find, with a NULL tmdb_id when nothing was found.
  • Used for shows that only appear through their episodes: removed from the library, or their Emby series ID changed on the rebuild.
  • One episode IMDB ID is looked up per series name.

Which titles get fetched: - Every library movie and series with a TMDB provider ID (removed ones included, since they're in watch history), plus every show found through a lookup. - Titles without details go first, and details older than 90 days are refetched.

Titles beyond the library

So the chat-bot and the daily picks' "to request" list can recommend what the server doesn't have, SQLiteConnector.ingest_tmdb_discovery_details(...) stores details of titles beyond the library in the same tmdb_title_details table: - Every title TMDB recommends for a library title ("people who liked this also liked"). Only the library's recommendations count, not these titles' own, so the set doesn't keep growing. - Popular IMDB titles: DISCOVERY_MIN_VOTES (25,000) votes or more, from PreProcess.get_popular_imdb_ids(). Each is looked up once, in tmdb_discovery_lookups (same columns as tmdb_imdb_lookups). - About 12,000 titles in Oct 2026. Details older than 180 days are refetched.

get_tmdb_title_details() returns an in_library column to tell them apart, and PreProcess.get_title_content() only returns the library's unless asked (discovery=True), so the daily picks' model and the evaluation are unaffected.

TMDB has a few junk entries that claim a real title's IMDB ID, such as a "movie" with a series' ID. TMDBConnector.find_by_imdb_id() takes the entry with the most votes. get_title_content() also leaves out any title beyond the library whose TMDB kind (movie or tv) contradicts IMDB's. In Oct 2026 that was 10 of about 8,600 lookups.

The daily refresh fetches at most 3,000 lookups and 3,000 titles per run (DISCOVERY_PER_RUN, about 25 minutes at TMDB's ~0.25 s per request). To fill everything at once (about 1.5 hours the first time), run PYTHONPATH=src python scripts/sqlite/tmdb_discovery_fill.py outside the refresh: it writes the live database, and it refuses to start while the refresh's working copy exists. - The first run is ~600 requests (~3 min); after that, only new titles.

Read the details with get_tmdb_title_details(), or PreProcess.get_title_content() for model input. recommendations (JSON list of "movie:<id>" / "tv:<id>") is TMDB's "people who liked this also liked" list; rows fetched before it existed are refetched once.

Emby user item states

user_item_states: Emby's own per-user state for every movie, series and episode that is played, a favourite, or partly watched. It's a snapshot that ingest_user_item_states(Emby.get_all_emby_users, Emby.get_user_item_states) replaces on each refresh, all-or-nothing. - Columns: user_id, item_id, item_type, played, play_count, last_played, is_favorite, position_ticks. - Coverage: unlike Playback Reporting (~90 days), this covers everything ever marked or finished on the current server. - Caveat (Oct 2026): every played item has play_count 0 and no last_played. The flags came through the server rebuild without their counts.

Jellyseerr requests

jellyseerr_requests: one row per request, upserted by ingest_jellyseerr_requests(JellyseerrConnector().iter_requests) when JELLYSEERR_URL and JELLYSEERR_API_KEY are set. Requests deleted in Jellyseerr stay here. - Columns: request_id, media_type, tmdb_id, status, media_status, requested_at, updated_at, requester_id, requester_name, requester_emby_id. - Linking: PreProcess.get_user_requests() links each request to a watch-history user (by Emby user ID, else by name, then through aliases) and to an IMDB title (through TMDB details).

Recommendation log

recommendation_log: every pick the nightly job showed, one row per (generated_at, user_id, list, rank). log_recommendations(picks, model) appends a run, and rows are never rewritten. ml.outcomes reads it to see which picks were watched or requested (see ML: pick outcomes). - Columns: generated_at, user_id, list (fresh / back_catalogue), rank, t_const, item_id (the Emby item put in the playlist), score, similarity, because_you_watched, model. - Purpose: comparing it with later watch history gives a real-world hit rate, alongside the offline evaluation in ml.evaluate.

Watch history tables

The watch-history pipeline converts raw Emby playback events into progressively richer aggregates.

watch_hist_raw_events

Append-only record of Emby’s playback reporting API. Playback Reporting only retains a limited window (~90 days as of Oct 2026), so this table is the only copy of older history and must never be rebuilt from scratch. A unique index on (user_id, item_id, date, time, duration) makes re-fetching overlapping windows safe (duration is included because the plugin can log a 0s stub and the real play at the same second). Each row stores the recorded date and time (normalised to Australia/Melbourne time for pre-August 2025 entries), the user_id, user_name, item_id, item_name, item_type, playback duration, and optional remote_address metadata.

watch_hist_agg_sessions

Aggregates raw events into contiguous sessions. Key fields include:

  • session_start_timestamp / session_end_timestamp
  • session_span_minutes and total_seconds_watched
  • session_count (number of underlying raw events)
  • completion_ratio (capped at 1.0 once runtime metadata is available)
  • outcome classification (sampled, abandoned, partial, completed)

Uniqueness is enforced per (user_id, item_id, session_start_timestamp) and each row links back to library_items via item_id.

watch_hist_user_item_stats

Derived statistics summarising a user’s relationship with a specific item. Generated columns (e.g., total_minutes_watched, days_between_first_last) and counters (completed_sessions, rewatch_count, adherence_score) are recalculated every time _INIT_POPULATE_watch_hist_user_item_stats() runs. The table is keyed by user_id + item_id and also links to library_items for runtime-aware metrics.

Watch history processing pipeline

Run the following steps—typically in this order—to refresh watch history:

  1. Library ingest (ingest_all_library_items): Populate library_items, item_genres, item_tags, and item_provider_ids. Provide EmbyConnector.iter_all_items() and optionally EmbyConnector.get_item_metadata() so the connector can fetch series-level genres for episodes.
  2. Raw event sync (ingest_watch_hist_raw_events): Fetches available playback history via EmbyConnector.get_all_watch_hist(), normalises timestamps, and inserts only events not already stored. (_INIT_POPULATE_watch_hist_raw_events drops the table first - only use it for a brand new database.)
  3. Session aggregation (_INIT_POPULATE_watch_hist_agg_sessions): Groups raw events into sessions. Tunable parameters include session_segment_minutes, completion thresholds, and minimum seconds for the sampled outcome.
  4. User-item statistics (_INIT_POPULATE_watch_hist_user_item_stats): Summarises aggregate engagement for each (user, item) pair, calculating adherence scores and outcome counts.
  5. Runtime-aware completion update (update_completion_ratios): Rewrites completion_ratio for sessions when actual runtime data is available in library_items.

Running the steps nightly (or after large library changes) keeps the downstream analytics model aligned with the latest viewing behaviour.

Index summary

Besides the library indexes mentioned earlier, the connector maintains:

  • idx_watch_hist_raw_user_time on watch_hist_raw_events(user_id, date, time DESC)
  • idx_watch_hist_agg_sessions on watch_hist_agg_sessions(user_id, session_end_timestamp DESC)
  • idx_watch_hist_agg_item on watch_hist_agg_sessions(item_id)
  • idx_watch_hist_user_item_stats on watch_hist_user_item_stats(user_id, adherence_score DESC)
  • idx_genres_item on item_genres(item_id)
  • idx_tags_item on item_tags(item_id)
  • idx_provider_provider on item_provider_ids(provider)

These indexes are recreated automatically with CREATE INDEX IF NOT EXISTS, so repeated pipeline runs are safe.