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, the eight ui_* design toggles, 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), the instance default for the design toggles (default_ui_toggles — a comma-separated list of ui_* keys; absent/NULL means "never configured" and keeps the old per-browser localStorage fallback, an empty string means "explicitly all off") 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.
library_aliases	Every name a library folder legitimately answers to, so "already downloaded" survives two providers naming the same show differently. folder TEXT PRIMARY KEY (lower-cased folder name), tmdb_id TEXT, media_type TEXT, aliases TEXT (JSON array of titles), resolved_at REAL. Written by web/library_aliases.py's background resolver, never inside a request; a row with an empty aliases array records a confirmed miss and is retried after 30 days. Persisted rather than cached — which names a folder answers to is a fact about the folder, not something with a TTL.
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). A cached entry now also carries a titles list (localized title, original-language title and TMDB's alternative titles) — the details call appends alternative_titles to its append_to_response, so the alias resolver costs no extra request.
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). The eight ui_* design toggles follow the same three-level cascade: user_ui_prefs ui_* (account) → app_settings default_ui_toggles (instance default) → the browser's localStorage mirror.
• 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).

Layout

The persistence layer is the package mediaforge/web/db/, not a single module. It used to be one 6939-line db.py — the slowest file in the repository to search, the most likely to produce a merge conflict, and the one place where a mistake takes the whole app down. None of that was caused by the code being complicated, only by all of it living in one file.

The split is by domain and is a pure move: every function is what it was, in a file named after the table family it touches.

Module	Owns
_core	Connection handling, WAL pragmas, the instance lock, secret encryption
users	Accounts, roles, SSO identities
queue	The download queue
paths	Custom download paths
language_groups	The fallback-group table (the vocabulary lives in web/language_groups.py)
autosync	Auto-Sync jobs
history	Per-episode download history and its retention prune
stats	Aggregates over queue, history and library
favourites, seerr	Favourites, hidden Seerr requests
library	Scan cache and the ignored-media list
settings	app_settings, encrypted values, change listeners
notifications, push, ui_prefs	Per-user preference tables
caches, browse_cache	TMDB/provider/browse result caches
calendar	Calendar watcher tables
upscale, encoding	The two post-processing queues
misc	Watch/reading progress, bookmarks, uptime heartbeats, dev infos

Import from mediaforge.web.db, never from a submodule. db/__init__.py re-exports all ~280 public names, so every existing from ..db import x keeps working; which file a function lives in is an internal detail, and moving one between domains must not become a breaking change.

The dependency graph between the submodules is a DAG — _core at the bottom, then ui_prefs → settings → users, push → notifications, encoding → upscale — so the import order in __init__.py is stable and there are no lazy imports working around a cycle. tests/test_db_package.py asserts the DAG, the completeness of the re-exports, and that no submodule creeps back past ~1200 lines.

Trap worth knowing: db/language_groups.py and web/language_groups.py share a name and are one dot apart. Inside the package, from .language_groups import ... imports itself; the module that owns the group prefix and chain resolution is ..language_groups. Two functions were carrying exactly that mistake after the split and failed on first call.

Schema migrations

Since the introduction of web/dbmigrate.py, schema changes are versioned. A schema_migrations table records (version, name, applied_at, app_version, baselined).

• Migrations are registered with a permanent number via the @migration(n, "name") decorator in dbmigrate.py and run once at startup, before any init_*_db(). That ordering is load-bearing: run_pending() tells "fresh database" from "existing database" by looking for tables the pre-migration code created, so calling it after those functions makes a brand-new database look old and every migration gets marked applied without executing.
• Each migration runs in its own transaction. A failure leaves every earlier migration committed and the failing one fully undone, so a fixed release resumes from exactly that point. A failed migration is logged, not fatal.
• Databases that predate the engine are baselined at BASELINE_VERSION (1) — the schema the init_*_db() path produces. Migrations above the baseline always execute, because they create tables no init_*_db() function knows about.
• Migration bodies must never commit() (the engine owns the transaction) and must never import from db.py at module level (circular import).

Snapshots

dbmigrate.snapshot() writes a self-contained copy to ~/.mediaforge/db_snapshots/ using SQLite's online backup API, not shutil.copy. In WAL mode part of the committed state lives in the -wal file, so a plain file copy produces a database that is valid but silently missing the newest transactions.

One snapshot is taken automatically before any pending migration and before any restore; automatic ones are pruned to the ten most recent, manual ones never. verify_snapshot() opens a snapshot read-only and runs integrity_check + foreign_key_check + row counts; restore_snapshot() refuses to run unless that passes, removes the live -wal/-shm sidecars (a leftover WAL would replay transactions the snapshot deliberately lacks) and requires a restart afterwards.

Snapshot ids arrive from an HTTP route and are therefore untrusted: _snapshot_path() rejects anything containing a separator or .. and confirms the resolved path's parent is the snapshot directory.

Tables added by the migration engine

Table	Migration	Purpose
schema_migrations	—	Version bookkeeping
user_groups, user_group_members	2	Permission sets + library scoping (see Authentication)
download_rules	3	Rule engine (web/rules.py)
language_profiles, title_language_profile	4	Per-title language profiles (web/langprofiles.py)
maintenance_windows	5	Time-of-day worker limits (web/maintenance.py)
worker_heartbeats	6	Worker liveness (web/worker_registry.py)
api_keys	7	Scoped API keys — only the hash is stored

user_group_members has no foreign key to users, for the same reason user_ui_prefs has none (the no-auth pseudo-user id 0). delete_user() therefore purges it explicitly — SQLite reuses user ids by value, and inheriting a deleted account's permissions is the worst variant of that bug.

title_language_profile declares ON DELETE CASCADE but the cascade never fires, since PRAGMA foreign_keys is off on every connection; langprofiles.delete_profile() deletes the bindings itself.

The audit database

The audit log is a separate database file, ~/.mediaforge/audit.db (web/audit.py) — see Audit Log for why. It is append-only at the database level (BEFORE UPDATE/BEFORE DELETE triggers), hash-chained per row, and written by a background thread from a bounded queue so an audit write can never slow down or fail the request it records. Retention pruning opens an explicit gate row the delete trigger checks, and closes it in the same transaction.