Jess Hallsworth
6a557bcdd9
The application the design describes: Flask + SQLite, Plex for library data, Tautulli for watch history, report-only. Structure follows the design's seams. providers/ splits MediaProvider from HistoryProvider, because on this network library data and watch data live on different machines and Jellyfin later will have no Tautulli equivalent. scoring.py implements the reclaim score twice - as a SQL expression for the live grid (weights change on every slider drag, so storing it would mean rewriting thousands of rows per drag) and in Python for CSV export and tests, with a property test over 500 generated rows asserting the two agree. rules.py compiles saved views to parameterized SQL through a field/operator whitelist; nothing user-supplied is ever interpolated. Three properties are enforced by test rather than asserted in prose: - Ingest is idempotent. Three consecutive full scans leave every count and every byte total unchanged. A scanner that double-counts produces a report that looks plausible and is wrong. - Keep marks survive Plex reassigning every rating key in the library. They are keyed on content GUID, scoped per library so the Movies and 4K Movies copies of the same film mark independently. - Every config variable the app reads is declared in docker-compose.yml, so a variable set in Portainer can never silently do nothing. Also found and fixed while verifying against a fake Plex+Tautulli pair: executescript() commits the pending transaction, so migrations needed their BEGIN/COMMIT inside the script; replaceChildren() renders null as the literal text "null"; a hash-only URL change does not reload the document, so deep links needed a hashchange listener; and SQLite ROUND rounds half away from zero where Python rounds half to even. 73 tests, no live server required. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01GVbG48GAXfCZatcmX123Ra
245 lines
9.5 KiB
SQL
245 lines
9.5 KiB
SQL
-- MediaShelf initial schema. See docs/design.md §5.
|
|
|
|
CREATE TABLE provider (
|
|
id INTEGER PRIMARY KEY,
|
|
kind TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
base_url TEXT NOT NULL,
|
|
server_id TEXT,
|
|
version TEXT,
|
|
last_scan_id INTEGER,
|
|
created_at INTEGER NOT NULL,
|
|
UNIQUE (kind, base_url)
|
|
);
|
|
|
|
CREATE TABLE library (
|
|
id INTEGER PRIMARY KEY,
|
|
provider_id INTEGER NOT NULL REFERENCES provider(id),
|
|
provider_key TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
kind TEXT NOT NULL, -- 'movie' | 'show'
|
|
locations TEXT, -- JSON array
|
|
keep_all INTEGER NOT NULL DEFAULT 0,
|
|
scanned_at INTEGER,
|
|
UNIQUE (provider_id, provider_key)
|
|
);
|
|
|
|
CREATE TABLE media_item (
|
|
id INTEGER PRIMARY KEY,
|
|
provider_id INTEGER NOT NULL REFERENCES provider(id),
|
|
library_id INTEGER NOT NULL REFERENCES library(id),
|
|
provider_item_id TEXT NOT NULL,
|
|
kind TEXT NOT NULL, -- 'movie' | 'show' | 'season'
|
|
guid TEXT,
|
|
show_guid TEXT, -- seasons: the parent show's guid
|
|
title TEXT NOT NULL,
|
|
sort_title TEXT,
|
|
year INTEGER,
|
|
parent_id INTEGER REFERENCES media_item(id),
|
|
season_number INTEGER,
|
|
added_at INTEGER,
|
|
updated_at INTEGER,
|
|
episode_count INTEGER NOT NULL DEFAULT 0,
|
|
size_bytes INTEGER NOT NULL DEFAULT 0,
|
|
duration_ms INTEGER NOT NULL DEFAULT 0,
|
|
part_count INTEGER NOT NULL DEFAULT 0,
|
|
primary_path TEXT,
|
|
resolution TEXT,
|
|
video_codec TEXT,
|
|
watch_count INTEGER NOT NULL DEFAULT 0,
|
|
partial_count INTEGER NOT NULL DEFAULT 0,
|
|
abandoned_count INTEGER NOT NULL DEFAULT 0,
|
|
last_watched_at INTEGER,
|
|
last_touched_at INTEGER,
|
|
first_watched_at INTEGER,
|
|
distinct_watcher_count INTEGER NOT NULL DEFAULT 0,
|
|
avg_percent_complete REAL,
|
|
pre_history INTEGER NOT NULL DEFAULT 0,
|
|
kept INTEGER NOT NULL DEFAULT 0,
|
|
kept_via TEXT,
|
|
kept_mark_id INTEGER,
|
|
provider_view_count INTEGER NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'present',
|
|
first_seen_scan_id INTEGER,
|
|
last_seen_scan_id INTEGER,
|
|
UNIQUE (provider_id, provider_item_id)
|
|
);
|
|
|
|
CREATE TABLE episode (
|
|
id INTEGER PRIMARY KEY,
|
|
season_item_id INTEGER NOT NULL REFERENCES media_item(id) ON DELETE CASCADE,
|
|
provider_item_id TEXT NOT NULL,
|
|
episode_number INTEGER,
|
|
title TEXT,
|
|
added_at INTEGER,
|
|
duration_ms INTEGER NOT NULL DEFAULT 0,
|
|
size_bytes INTEGER NOT NULL DEFAULT 0,
|
|
part_count INTEGER NOT NULL DEFAULT 0,
|
|
watch_count INTEGER NOT NULL DEFAULT 0,
|
|
partial_count INTEGER NOT NULL DEFAULT 0,
|
|
abandoned_count INTEGER NOT NULL DEFAULT 0,
|
|
last_watched_at INTEGER,
|
|
last_touched_at INTEGER,
|
|
status TEXT NOT NULL DEFAULT 'present',
|
|
last_seen_scan_id INTEGER,
|
|
UNIQUE (provider_item_id)
|
|
);
|
|
|
|
CREATE TABLE media_part (
|
|
id INTEGER PRIMARY KEY,
|
|
media_item_id INTEGER REFERENCES media_item(id) ON DELETE CASCADE,
|
|
episode_id INTEGER REFERENCES episode(id) ON DELETE CASCADE,
|
|
provider_part_id TEXT,
|
|
file_path TEXT NOT NULL,
|
|
size_bytes INTEGER NOT NULL DEFAULT 0,
|
|
container TEXT,
|
|
resolution TEXT,
|
|
video_codec TEXT,
|
|
audio_codec TEXT,
|
|
bitrate INTEGER,
|
|
CHECK ((media_item_id IS NULL) != (episode_id IS NULL))
|
|
);
|
|
|
|
CREATE TABLE watch_event (
|
|
id INTEGER PRIMARY KEY,
|
|
provider_id INTEGER NOT NULL REFERENCES provider(id),
|
|
source TEXT NOT NULL, -- 'tautulli' | 'plex'
|
|
source_row_id TEXT NOT NULL,
|
|
reference_id TEXT,
|
|
provider_item_id TEXT NOT NULL,
|
|
account_id TEXT,
|
|
viewed_at INTEGER NOT NULL,
|
|
stopped_at INTEGER,
|
|
play_duration_s INTEGER,
|
|
paused_counter_s INTEGER,
|
|
percent_complete INTEGER,
|
|
watched_status REAL,
|
|
disposition TEXT NOT NULL, -- completed | partial | abandoned
|
|
session_id TEXT,
|
|
media_type TEXT,
|
|
platform TEXT,
|
|
UNIQUE (provider_id, source, source_row_id)
|
|
);
|
|
|
|
CREATE TABLE account (
|
|
id INTEGER PRIMARY KEY,
|
|
provider_id INTEGER NOT NULL REFERENCES provider(id),
|
|
account_id TEXT NOT NULL,
|
|
name TEXT,
|
|
friendly_name TEXT,
|
|
UNIQUE (provider_id, account_id)
|
|
);
|
|
|
|
CREATE TABLE history_coverage (
|
|
id INTEGER PRIMARY KEY,
|
|
provider_id INTEGER NOT NULL REFERENCES provider(id),
|
|
source TEXT NOT NULL,
|
|
earliest_event_at INTEGER,
|
|
latest_event_at INTEGER,
|
|
event_count INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL,
|
|
UNIQUE (provider_id, source)
|
|
);
|
|
|
|
-- Keep marks: human judgements the score must not override (§6.6).
|
|
-- Keyed on CONTENT identity, never on provider_item_id.
|
|
CREATE TABLE keep_mark (
|
|
id INTEGER PRIMARY KEY,
|
|
scope TEXT NOT NULL, -- 'show' | 'season' | 'movie'
|
|
mode TEXT NOT NULL, -- 'keep' | 'exclude'
|
|
library_id INTEGER NOT NULL REFERENCES library(id),
|
|
guid TEXT NOT NULL,
|
|
season_number INTEGER,
|
|
provider_item_id TEXT,
|
|
label TEXT NOT NULL,
|
|
note TEXT,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
last_matched_scan_id INTEGER,
|
|
UNIQUE (scope, library_id, guid, season_number)
|
|
);
|
|
|
|
CREATE TABLE scan (
|
|
id INTEGER PRIMARY KEY,
|
|
provider_id INTEGER REFERENCES provider(id),
|
|
mode TEXT NOT NULL, -- 'full' | 'incremental'
|
|
trigger TEXT NOT NULL, -- 'manual' | 'schedule' | 'startup'
|
|
status TEXT NOT NULL, -- 'running' | 'succeeded' | 'failed'
|
|
history_source TEXT,
|
|
started_at INTEGER NOT NULL,
|
|
finished_at INTEGER,
|
|
progress TEXT,
|
|
items_seen INTEGER NOT NULL DEFAULT 0,
|
|
items_added INTEGER NOT NULL DEFAULT 0,
|
|
items_updated INTEGER NOT NULL DEFAULT 0,
|
|
items_missing INTEGER NOT NULL DEFAULT 0,
|
|
events_added INTEGER NOT NULL DEFAULT 0,
|
|
warning_count INTEGER NOT NULL DEFAULT 0,
|
|
warnings TEXT,
|
|
error TEXT
|
|
);
|
|
|
|
CREATE TABLE scan_lock (
|
|
id INTEGER PRIMARY KEY CHECK (id = 1),
|
|
scan_id INTEGER,
|
|
holder TEXT,
|
|
acquired_at INTEGER
|
|
);
|
|
|
|
CREATE TABLE saved_view (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
rules TEXT NOT NULL,
|
|
sort TEXT,
|
|
columns TEXT,
|
|
weights TEXT,
|
|
builtin INTEGER NOT NULL DEFAULT 0,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE setting (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX ix_item_library ON media_item(library_id, kind, status);
|
|
CREATE INDEX ix_item_added ON media_item(added_at);
|
|
CREATE INDEX ix_item_lastwatch ON media_item(last_watched_at);
|
|
CREATE INDEX ix_item_size ON media_item(size_bytes);
|
|
CREATE INDEX ix_item_watchcount ON media_item(watch_count);
|
|
CREATE INDEX ix_item_parent ON media_item(parent_id);
|
|
CREATE INDEX ix_item_guid ON media_item(guid);
|
|
CREATE INDEX ix_item_showguid ON media_item(show_guid);
|
|
CREATE INDEX ix_item_kept ON media_item(kept, library_id);
|
|
CREATE INDEX ix_episode_season ON episode(season_item_id);
|
|
CREATE INDEX ix_part_item ON media_part(media_item_id);
|
|
CREATE INDEX ix_part_episode ON media_part(episode_id);
|
|
CREATE INDEX ix_event_item ON watch_event(provider_item_id);
|
|
CREATE INDEX ix_event_viewed ON watch_event(viewed_at);
|
|
CREATE INDEX ix_event_disp ON watch_event(provider_item_id, disposition);
|
|
CREATE INDEX ix_event_account ON watch_event(account_id, viewed_at);
|
|
CREATE INDEX ix_keep_lookup ON keep_mark(library_id, guid, season_number);
|
|
|
|
-- Full-text search over titles, kept in sync by triggers.
|
|
CREATE VIRTUAL TABLE media_item_fts USING fts5(
|
|
title, sort_title, content='media_item', content_rowid='id'
|
|
);
|
|
|
|
CREATE TRIGGER media_item_ai AFTER INSERT ON media_item BEGIN
|
|
INSERT INTO media_item_fts(rowid, title, sort_title)
|
|
VALUES (new.id, new.title, new.sort_title);
|
|
END;
|
|
|
|
CREATE TRIGGER media_item_ad AFTER DELETE ON media_item BEGIN
|
|
INSERT INTO media_item_fts(media_item_fts, rowid, title, sort_title)
|
|
VALUES ('delete', old.id, old.title, old.sort_title);
|
|
END;
|
|
|
|
CREATE TRIGGER media_item_au AFTER UPDATE OF title, sort_title ON media_item BEGIN
|
|
INSERT INTO media_item_fts(media_item_fts, rowid, title, sort_title)
|
|
VALUES ('delete', old.id, old.title, old.sort_title);
|
|
INSERT INTO media_item_fts(rowid, title, sort_title)
|
|
VALUES (new.id, new.title, new.sort_title);
|
|
END;
|