Database

Database

🌐 English · Deutsch

SQLite database at ~/.mediaforge/mediaforge.db — WAL mode, synchronous=NORMAL (crash-safe under WAL, no fsync per commit), 16 MB page cache, 30 s busy timeout, connections are cached per Flask request. A PID lock (~/.mediaforge/mediaforge.pid) warns when two instances use the same DB.

Everything lives in src/mediaforge/web/db.py — schema, migrations and the access helpers. The tables below are the complete set (28); per table this page names the purpose plus the columns you actually need to know (identity, references, surprises). For the full column list read the CREATE TABLE statement in db.py, it is the only place that cannot go stale.

Accounts & preferences

Table	Content
users	Accounts. id PK, username UNIQUE. role is CHECK-constrained to admin / user / kids (the restricted home mode). auth_method (local/oidc), sso_issuer + sso_subject (unique index, NULL for local accounts), language (en/de, seeded from default_ui_language). SSO accounts store an empty password_hash.
user_notification_prefs	Notification settings per user, as (user_id, key) → value. Declares FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE — which never fires, see Migrations & the foreign-key trap.
user_ui_prefs	Appearance and per-person layout, also (user_id, key) → value: theme pack, dark/light, accent, library view/page size, home-feed filters and layout, eBook reader settings. Deliberately no FK to users (the no-auth pseudo-user has id 0 and no row). Keys are whitelisted in db.USER_UI_PREF_KEYS with a validator each, extendable by modules via register_ui_pref_key().
push_subscriptions	Web Push endpoints. endpoint UNIQUE, user_id nullable, plus the auth/p256dh keys. No FK either.
seerr_hidden	Seerr requests a user dismissed. PK (user_id, seerr_request_id), keeps title/poster so the "hidden" list renders without asking Seerr.
app_settings	Key-value store of all instance settings — key PK, value TEXT. Also the home of the telemetry keys (telemetry_install_id, telemetry_consent_given, telemetry_consent_at, telemetry_enabled_keys, telemetry_device_secret), the module store (module_store_extra_urls, module_store_allow_unverified), the active theme pack (theme_pack_active) and every module's own settings under the namespace module:<module_id>:<key> (see thirdparties/registry.py, module_setting_key()). Sensitive values are encrypted, see below.

Downloads

Table	Content
download_queue	Download jobs. id PK, episodes (JSON list), total_episodes, language, provider, username, status CHECK queued/running/completed/partial/failed/cancelled, current_episode/current_url, errors (JSON). Retrofitted columns worth knowing: position (manual queue order), custom_path_id (target path), source (manual/sync), captcha_url, hidden (excluded from the queue UI but kept for statistics), average_speed_mbps + total_size_mb, format_id and source_provider (Direct-Link jobs only — the yt-dlp format selector and the recognised embed host), upscale flag, and replace_paths (JSON {episode_url: [old file paths]} for a language upgrade: the worker deletes the listed files once the better version has landed).
download_history	Persistent per-episode log, independent of the queue: queue_id (loose back-reference, no FK), title, season/episode, language/provider, source, username, target_path, size_mb, avg_speed_mbps, duration_sec, status (completed/failed/cancelled/skipped), error (error or skip reason), started_at/finished_at (UTC).
custom_paths	Named additional download targets. default_sites (comma list of sites this path is preselected for) and media_kinds (comma list of media-kind slugs — video, book, comic; manga/music exist as slugs but have no scanner yet). Existing rows migrate to video, so a folder that also holds eBooks has to be ticked once in Settings.
language_groups	Named language priority groups for auto-sync upgrades: languages (JSON list, best first) and delete_replaced (remove the superseded file after an upgrade).
autosync_jobs	Sync jobs: series_url, language, provider, custom_path_id, enabled, on_hold, added_by, path_unavailable_action, episode_filter (JSON season/episode selection), movie_custom_path_id (separate path for movies/specials), filter_dirty (silent baseline recompute after a filter change), group_name (optional manual group), cover_url (poster cached for the job card), counters (episodes_found, local_episodes_found, last_new_count) and error/retry state (last_error, retry_count).
favourites	Series bookmarks. Identity is UNIQUE(series_url, added_by) — the same series can be bookmarked once per user. Carries media_type, provider and language alongside title and poster.

Processing queues

Table	Content
upscale_queue	Anime4K jobs. queue_item_id points back at the download_queue row that produced the job (NULL for manual ones), files is a JSON list for multi-file entries (total_files/current_file_idx), plus status, progress_pct, source, position.
encoding_queue	FFmpeg re-encode jobs, same shape as upscale_queue plus upscale_after: the download also asked for upscaling, so instead of queueing both jobs side by side the encoding worker hands the finished file over to the upscaler.

Both queues touch the same files, so they must never run on one file at the same time. The claim helpers (claim_next_upscale_queued() / claim_next_encoding_queued(), each on its own connection with BEGIN IMMEDIATE) enforce that by skipping candidates whose paths are busy in the other queue — with one deliberate asymmetry:

• an upscale is held back by a queued or running encode (encoding comes first in the chain Download → Encoding → Upscaling),
• an encode is held back only by a running upscale.

If both sides waited on the other's queued rows, an item sitting in both queues would block itself forever. Note that they skip ahead rather than stall on the head of the queue.

Library & media

Table	Content
library_cache	One row per scan target: path_key PK ("default" for the global download path, otherwise the custom_paths.id as a string), data (JSON payload), scanned_at, is_scanning. The payload is a dict, not a list: titles or lang_folders (the video side — lang_folders holds {name, titles} when language separation is on, and titles is NULL then), plus books/books_version and comics/comics_version for the book and comic scanners, plus label, custom_path_id and media_kinds. Read it via lib_iter_cached_titles(); iterating the entry itself yields key strings.
media_ignored	Missing-media slots the user chose to ignore. PK (folder, slot) where folder is the lower-cased series folder and slot is "S1E3", a whole season "S2", or the sentinel "__all__" for the entire series. Ignored slots are subtracted before the statistics decide a series is incomplete.
mediascan_cache	Plex/Jellyfin inventory (TMDB/IMDB/TVDB IDs, title, media type, updated_at).
watch_progress	Playback position for the browser player. Identity is UNIQUE(file_path, username), not the file alone; username is a TEXT column (not a user id) because the no-auth install has no users row to point at — '' is the shared bucket. watched flips at ≥ 95 %.
reading_progress	Same idea for the eBook reader, but keyed on UNIQUE(book_key, username) — the book, not the file: one book routinely exists as EPUB, MOBI and PDF at once (web/books/identity.py). Stores location (CFI/page), percent, finished.
reading_bookmarks	Reader bookmarks, UNIQUE(book_key, username, location), with kind (e.g. epub), label and percent.

Caches

Table	Content
tmdb_cache	TMDB lookups: cache_key PK, data_json, cached_at (24 h TTL, hourly cleanup).
provider_cache	Same shape, but namespaced — PK (namespace, cache_key) — so several providers (Crunchyroll, Fernsehserien.de, …) share one table for their availability/pill lookups without colliding. Persistent so those survive a restart.
browse_cache	Browse lists (new/popular titles): cache_key PK, data_json, cached_at.
calendar_media	Calendar watcher — one row per TMDB title: tmdb_id UNIQUE, bilingual title (title/title_en), poster_path, last_updated.
calendar_episodes	Calendar watcher — dated episodes/releases, media_id REFERENCES calendar_media(id) ON DELETE CASCADE (declared, but see the FK note below), UNIQUE(media_id, season, episode), season/episode NULL for movies, bilingual name/name_en, air_date (YYYY-MM-DD), still_path.

Monitoring & Dev Infos

Table	Content
uptime_heartbeats	One row per probe: source, ts (unix seconds), status, response_ms, http_status, message; indexed on (source, ts).
devinfo_posts	Cached posts from the remote devInfo feed. id is the server's uid (a UUID), not its numeric id. For type="release" posts the release notes (release_tag, release_name, release_notes, release_url, release_published_at) are cached with the post, so the changelog still renders when the devInfo server is unreachable — and nothing here ever talks to GitHub directly.
devinfo_read	Read state, keyed by the same post id. Deliberately its own table: every poll round replaces devinfo_posts wholesale (DELETE + reinsert), so a "read" flag stored as a column there would be wiped every five minutes.

Deliberately not tables

Do not go looking for these in the schema:

• SyncPlay rooms — purely in memory: web/syncplay_rooms.py keeps _rooms and _token_index as module-level dicts behind an RLock. Rooms and their playback state are gone on restart by design. (The module touches the DB only to look up a file's origin in download_history for telemetry.)
• Theme packs — files on disk under the themes directory; only the active one is persisted, as the app_settings key theme_pack_active (instance default) and the user_ui_prefs key theme_pack (per-user override).
• Home feed & home panels — registries, not storage: home_feed.py / home_panels.py keep module-level dicts that modules extend via register_home_feed_source() / register_home_panel(). The admin defaults are app_settings keys (home_rows_order, home_rows_hidden, home_cards_per_row, home_source_order, home_default_sources_off/_types_off), the per-user overrides are the user_ui_prefs keys home_feed_filters and home_feed_layout.

Migrations & the foreign-key trap

Migrations run automatically at startup inside the init_*() functions — there is no migration framework and no downgrade path. Almost everything is an ALTER TABLE … ADD COLUMN wrapped in try/except (a "duplicate column" error means the column is already there). New columns are also listed in the CREATE TABLE statement, so a freshly created table has the same shape as a migrated one.

Three things need more than an ADD COLUMN, because SQLite cannot alter a constraint:

• download_queue — rebuilt once to widen the status CHECK constraint with partial; the retrofitted columns are re-added afterwards.
• users, twice — once to widen the role CHECK, once again to add kids to it. The rebuild copies into a users_new_kids table first, verifies the row count, and only then drops the original; a crash halfway leaves a stray table and nothing else. If it fails, the kids role is simply unavailable and the accounts are untouched.
• watch_progress — the legacy table had a UNIQUE on file_path alone. It is renamed to watch_progress_legacy, recreated per-user, and all existing rows are copied into the shared '' user.

PRAGMA foreign_keys is OFF on every connection (_configure_connection()), deliberately. The two declared ON DELETE CASCADE constraints — user_notification_prefs → users and calendar_episodes → calendar_media — therefore never fire. Turning it on globally breaks no-auth mode: the users table is only created when auth is enabled, while no-auth requests run as pseudo-user id 0 and still save notification prefs, which would fail with "no such table: main.users" or a FK violation. Enabling it would first require dropping the FK on user_notification_prefs (another table rebuild).

The practical consequence: per-user cleanup is explicit. delete_user() deletes from user_notification_prefs, push_subscriptions and seerr_hidden before removing the account, and calls clear_user_ui_prefs() afterwards. This is not belt-and-braces — SQLite reuses user ids by value, so without it the next account created with that id inherits the deleted user's notification prefs, push endpoints and hidden requests. Any new per-user table must be added to that list.

Encrypted settings

Sensitive app_settings values are stored Fernet-encrypted (prefix enc:); the key is derived from the Flask secret (~/.mediaforge/.flask_secret). The core list is db.SENSITIVE_KEYS:

external_api_key, seerr_api_key, oidc_client_secret, cineinfo_tmdb_api_key, mediaplayer_apikey, mediascan_jf_apikey, notif_telegram_bot_token, notif_pushover_app_token, notif_discord_webhook_url, notif_ntfy_auth_token, notif_ntfy_password, pushover_user_key, crunchyroll_email, crunchyroll_password, crunchyroll_session_key, opensubtitles_api_key, opensubtitles_password, comicvine_api_key, home_kids_pin, telemetry_device_secret.

Existing plaintext values for these are encrypted once at startup.

Modules extend the list at runtime with register_sensitive_keys(keys) instead of waiting for a core release: every extra_settings field declared as type="secret" is registered automatically, and a module can name further keys via MODULE_SENSITIVE_SETTINGS. The call also encrypts anything of those already stored in plaintext and returns how many values it converted. Registration is one-way and cumulative — a key never becomes non-sensitive again, and get_setting() decrypts anything carrying the enc: prefix regardless of registration, so an uninstalled module's leftover value stays readable.

Backup note: Always back up mediaforge.db and .flask_secret together — without the secret the encrypted settings cannot be recovered.

Access patterns

• get_setting(key, default) / set_setting(key, value) / delete_setting(key) — central settings API (encrypts/decrypts transparently); get_json_setting() / set_json_setting() for JSON values.
• Queue claiming (claim_next_queued) uses a dedicated connection with an atomic UPDATE … WHERE status='queued' so two workers can never grab the same job; the upscale/encoding claims add the mutual exclusion described above.
• get_encoding_ffmpeg_opts() translates the encoding settings into ready-to-use FFmpeg arguments (incl. the expert flag parser).