Repository Public
1import { DatabaseSync, type SQLInputValue } from "node:sqlite";2import fs from "node:fs";3import path from "node:path";4import { env } from "./env";56// One SQLite file shared by the web process and the worker (WAL mode: readers never block the writer).7const MIGRATIONS: string[] = [8 `9 CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT NOT NULL);10 CREATE TABLE sources (11 id INTEGER PRIMARY KEY, kind TEXT NOT NULL, name TEXT NOT NULL, url TEXT NOT NULL UNIQUE,12 country TEXT, enabled INTEGER NOT NULL DEFAULT 1, builtin INTEGER NOT NULL DEFAULT 0,13 last_sync_at INTEGER, last_status TEXT, last_error TEXT, stream_count INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL14 );15 CREATE TABLE channels (16 id TEXT PRIMARY KEY, name TEXT NOT NULL, alt_names TEXT NOT NULL DEFAULT '', country TEXT,17 categories TEXT NOT NULL DEFAULT '', languages TEXT NOT NULL DEFAULT '', logo TEXT, website TEXT, network TEXT,18 known INTEGER NOT NULL DEFAULT 0, active INTEGER NOT NULL DEFAULT 1, has_guide INTEGER NOT NULL DEFAULT 0,19 first_seen INTEGER NOT NULL, updated_at INTEGER NOT NULL20 );21 CREATE INDEX channels_country ON channels(country) WHERE active = 1;22 CREATE TABLE streams (23 id INTEGER PRIMARY KEY, channel_id TEXT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,24 source_id INTEGER NOT NULL REFERENCES sources(id) ON DELETE CASCADE,25 url TEXT NOT NULL, title TEXT, feed TEXT, quality TEXT, labels TEXT NOT NULL DEFAULT '',26 referrer TEXT, user_agent TEXT, first_seen INTEGER NOT NULL, last_seen INTEGER NOT NULL,27 status TEXT NOT NULL DEFAULT 'unknown', http_status INTEGER, check_error TEXT, checked_at INTEGER,28 cors INTEGER, content_type TEXT, latency_ms INTEGER, fail_count INTEGER NOT NULL DEFAULT 0,29 play_ok INTEGER, play_at INTEGER, play_error TEXT,30 UNIQUE(url)31 );32 CREATE INDEX streams_channel ON streams(channel_id);33 CREATE INDEX streams_checked ON streams(checked_at);34 CREATE TABLE categories (id TEXT PRIMARY KEY, name TEXT NOT NULL);35 CREATE TABLE countries (code TEXT PRIMARY KEY, name TEXT NOT NULL, flag TEXT);36 CREATE TABLE languages (code TEXT PRIMARY KEY, name TEXT NOT NULL);37 CREATE TABLE guides (38 channel_id TEXT NOT NULL, feed TEXT, site TEXT NOT NULL, site_id TEXT NOT NULL, site_name TEXT, lang TEXT39 );40 CREATE INDEX guides_channel ON guides(channel_id);41 CREATE TABLE epg_state (42 channel_id TEXT PRIMARY KEY, candidate INTEGER NOT NULL DEFAULT 0, site TEXT, last_try_at INTEGER, last_ok_at INTEGER, last_count INTEGER NOT NULL DEFAULT 043 );44 CREATE TABLE epg_sources (45 id INTEGER PRIMARY KEY, name TEXT NOT NULL, url TEXT NOT NULL UNIQUE, enabled INTEGER NOT NULL DEFAULT 1,46 last_sync_at INTEGER, last_status TEXT, last_error TEXT, program_count INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL47 );48 CREATE TABLE programs (49 channel_id TEXT NOT NULL, start INTEGER NOT NULL, stop INTEGER NOT NULL, title TEXT NOT NULL,50 subtitle TEXT, description TEXT, category TEXT, origin TEXT NOT NULL,51 PRIMARY KEY (channel_id, start)52 ) WITHOUT ROWID;53 CREATE INDEX programs_stop ON programs(stop);5455 CREATE TABLE lists (id INTEGER PRIMARY KEY, name TEXT NOT NULL, position INTEGER NOT NULL, created_at INTEGER NOT NULL);56 CREATE TABLE list_items (57 list_id INTEGER NOT NULL REFERENCES lists(id) ON DELETE CASCADE, channel_id TEXT NOT NULL,58 position INTEGER NOT NULL, added_at INTEGER NOT NULL, PRIMARY KEY (list_id, channel_id)59 ) WITHOUT ROWID;60 CREATE INDEX list_items_channel ON list_items(channel_id);61 CREATE TABLE hidden (channel_id TEXT PRIMARY KEY, hidden_at INTEGER NOT NULL);62 CREATE TABLE history (channel_id TEXT PRIMARY KEY, last_watched_at INTEGER NOT NULL, watch_count INTEGER NOT NULL DEFAULT 1);6364 CREATE TABLE passkeys (65 id TEXT PRIMARY KEY, public_key BLOB NOT NULL, counter INTEGER NOT NULL DEFAULT 0, transports TEXT,66 name TEXT NOT NULL, created_at INTEGER NOT NULL, last_used_at INTEGER67 );68 CREATE TABLE sessions (69 token_hash TEXT PRIMARY KEY, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, last_seen_at INTEGER NOT NULL,70 user_agent TEXT, ip TEXT, method TEXT NOT NULL71 );72 CREATE TABLE login_attempts (id INTEGER PRIMARY KEY, ip TEXT NOT NULL, at INTEGER NOT NULL, ok INTEGER NOT NULL, method TEXT NOT NULL);73 CREATE INDEX login_attempts_at ON login_attempts(at);7475 CREATE TABLE jobs (76 id INTEGER PRIMARY KEY, type TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'queued', payload TEXT,77 requested_at INTEGER NOT NULL, started_at INTEGER, finished_at INTEGER, result TEXT, error TEXT, trigger TEXT NOT NULL DEFAULT 'manual'78 );79 CREATE INDEX jobs_status ON jobs(status, id);80 CREATE TABLE logs (id INTEGER PRIMARY KEY, at INTEGER NOT NULL, level TEXT NOT NULL, scope TEXT NOT NULL, message TEXT NOT NULL, meta TEXT);81 CREATE INDEX logs_at ON logs(at);82 `,83 // 2 — films, series, personal library, playback progress.84 `85 CREATE TABLE titles (86 id INTEGER PRIMARY KEY, kind TEXT NOT NULL, origin TEXT NOT NULL, origin_id TEXT NOT NULL,87 title TEXT NOT NULL, original_title TEXT, year INTEGER, synopsis TEXT, synopsis_source TEXT, runtime_sec INTEGER,88 genres TEXT NOT NULL DEFAULT '', directors TEXT NOT NULL DEFAULT '', cast_names TEXT NOT NULL DEFAULT '',89 languages TEXT NOT NULL DEFAULT '', countries TEXT NOT NULL DEFAULT '', poster TEXT, backdrop TEXT,90 is_documentary INTEGER NOT NULL DEFAULT 0, is_animation INTEGER NOT NULL DEFAULT 0, is_short INTEGER NOT NULL DEFAULT 0,91 rights_status TEXT NOT NULL, rights_basis TEXT NOT NULL, rights_url TEXT, source_name TEXT NOT NULL, source_url TEXT,92 imdb_id TEXT, wikidata_id TEXT, library_dir INTEGER, active INTEGER NOT NULL DEFAULT 1,93 added_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, checked_at INTEGER,94 UNIQUE(origin, origin_id)95 );96 CREATE INDEX titles_kind ON titles(kind) WHERE active = 1;97 CREATE TABLE episodes (98 id INTEGER PRIMARY KEY, title_id INTEGER NOT NULL REFERENCES titles(id) ON DELETE CASCADE,99 season INTEGER NOT NULL, episode INTEGER NOT NULL, name TEXT, synopsis TEXT, air_date TEXT,100 UNIQUE(title_id, season, episode)101 );102 CREATE TABLE media (103 id INTEGER PRIMARY KEY, title_id INTEGER NOT NULL REFERENCES titles(id) ON DELETE CASCADE,104 episode_id INTEGER REFERENCES episodes(id) ON DELETE CASCADE,105 kind TEXT NOT NULL, url TEXT, path TEXT, container TEXT, vcodec TEXT, acodec TEXT, width INTEGER, height INTEGER,106 duration_sec INTEGER, size_bytes INTEGER, mtime INTEGER, playback TEXT NOT NULL DEFAULT 'direct',107 subtitles TEXT NOT NULL DEFAULT '[]', audio TEXT NOT NULL DEFAULT '[]', error TEXT, added_at INTEGER NOT NULL108 );109 CREATE INDEX media_title ON media(title_id);110 CREATE UNIQUE INDEX media_path ON media(path) WHERE path IS NOT NULL;111 CREATE TABLE progress (112 media_id INTEGER PRIMARY KEY REFERENCES media(id) ON DELETE CASCADE, position_sec REAL NOT NULL, duration_sec REAL,113 completed INTEGER NOT NULL DEFAULT 0, plays INTEGER NOT NULL DEFAULT 1, updated_at INTEGER NOT NULL114 );115 CREATE TABLE title_favs (title_id INTEGER PRIMARY KEY REFERENCES titles(id) ON DELETE CASCADE, added_at INTEGER NOT NULL);116 CREATE TABLE vod_sources (117 id INTEGER PRIMARY KEY, kind TEXT NOT NULL, name TEXT NOT NULL, ref TEXT NOT NULL, as_kind TEXT NOT NULL DEFAULT 'movie',118 rights_status TEXT, rights_note TEXT, enabled INTEGER NOT NULL DEFAULT 1, builtin INTEGER NOT NULL DEFAULT 0,119 last_sync_at INTEGER, last_status TEXT, last_error TEXT, title_count INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL,120 UNIQUE(kind, ref)121 );122 CREATE TABLE library_dirs (123 id INTEGER PRIMARY KEY, path TEXT NOT NULL UNIQUE, kind TEXT NOT NULL, label TEXT NOT NULL, enabled INTEGER NOT NULL DEFAULT 1,124 last_scan_at INTEGER, last_status TEXT, last_error TEXT, file_count INTEGER NOT NULL DEFAULT 0, new_count INTEGER NOT NULL DEFAULT 0,125 error_count INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL126 );127 `,128 // 3 — raw playlist identifier of each stream: lets a guide (XMLTV) published for a playlist find its channels.129 `130 ALTER TABLE streams ADD COLUMN tvg_id TEXT;131 CREATE INDEX streams_tvg ON streams(tvg_id) WHERE tvg_id IS NOT NULL;132 ALTER TABLE epg_sources ADD COLUMN builtin INTEGER NOT NULL DEFAULT 0;133 `,134 // 4 — personal M3U playlists for TV apps, each reachable through its own secret address.135 `136 CREATE TABLE playlists (137 id INTEGER PRIMARY KEY, name TEXT NOT NULL, token TEXT NOT NULL UNIQUE, filter TEXT NOT NULL DEFAULT '{}',138 created_at INTEGER NOT NULL, last_used_at INTEGER, last_used_by TEXT, hits INTEGER NOT NULL DEFAULT 0139 );140 `,141 // 5 — universal source engine: per-source configuration and encrypted credentials, uploaded playlists, playback priority,142 // dated rights for catalogues that rotate (ARTE).143 `144 ALTER TABLE sources ADD COLUMN config TEXT;145 ALTER TABLE sources ADD COLUMN secret TEXT;146 ALTER TABLE sources ADD COLUMN content TEXT;147 ALTER TABLE sources ADD COLUMN priority INTEGER NOT NULL DEFAULT 100;148 ALTER TABLE vod_sources ADD COLUMN config TEXT;149 ALTER TABLE vod_sources ADD COLUMN secret TEXT;150 ALTER TABLE titles ADD COLUMN expires_at INTEGER;151 CREATE INDEX titles_expires ON titles(expires_at) WHERE expires_at IS NOT NULL;152 `,153 // 6 — TMDB enrichment bookkeeping.154 `155 ALTER TABLE titles ADD COLUMN tmdb_id TEXT;156 ALTER TABLE titles ADD COLUMN tmdb_checked_at INTEGER;157 `,158 // 7 — relay engine (MediaMTX + FFmpeg): per-source authorisation, relays, playback sessions, events and samples.159 `160 ALTER TABLE sources ADD COLUMN relay TEXT;161 CREATE TABLE relays (162 id TEXT PRIMARY KEY, stream_id INTEGER NOT NULL, channel_id TEXT NOT NULL, source_id INTEGER NOT NULL,163 shared INTEGER NOT NULL DEFAULT 1, record INTEGER NOT NULL DEFAULT 0, owner TEXT,164 plan TEXT, mode TEXT, vcodec TEXT, acodec TEXT, width INTEGER, height INTEGER, probed_at INTEGER,165 state TEXT NOT NULL DEFAULT 'idle', pid INTEGER, ffmpeg_pid INTEGER, created_at INTEGER NOT NULL, started_at INTEGER, ready_at INTEGER,166 stopped_at INTEGER, restarts INTEGER NOT NULL DEFAULT 0, last_error TEXT,167 bytes_in INTEGER NOT NULL DEFAULT 0, bytes_out INTEGER NOT NULL DEFAULT 0, in_changed_at INTEGER, clients INTEGER NOT NULL DEFAULT 0168 );169 CREATE INDEX relays_state ON relays(state);170 CREATE TABLE relay_sessions (171 id TEXT PRIMARY KEY, relay_id TEXT NOT NULL, stream_id INTEGER NOT NULL, channel_id TEXT NOT NULL, auth TEXT NOT NULL,172 ip TEXT, user_agent TEXT, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, last_seen_at INTEGER NOT NULL, ended_at INTEGER,173 end_reason TEXT, bytes_out INTEGER NOT NULL DEFAULT 0, requests INTEGER NOT NULL DEFAULT 0174 );175 CREATE INDEX relay_sessions_open ON relay_sessions(ended_at, relay_id);176 CREATE TABLE relay_events (id INTEGER PRIMARY KEY, at INTEGER NOT NULL, relay_id TEXT, level TEXT NOT NULL, kind TEXT NOT NULL, message TEXT NOT NULL);177 CREATE INDEX relay_events_at ON relay_events(at);178 CREATE TABLE relay_samples (179 at INTEGER NOT NULL, relay_id TEXT NOT NULL, cpu REAL NOT NULL DEFAULT 0, rss_mb REAL NOT NULL DEFAULT 0,180 in_kbps REAL NOT NULL DEFAULT 0, out_kbps REAL NOT NULL DEFAULT 0, clients INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (at, relay_id)181 ) WITHOUT ROWID;182 `,183];184185type G = typeof globalThis & { __sptvDb?: DatabaseSync };186187function open(): DatabaseSync {188 fs.mkdirSync(path.dirname(env.dbPath), { recursive: true, mode: 0o700 });189 const db = new DatabaseSync(env.dbPath, { timeout: 15_000 });190 db.exec("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 15000;");191 const version = (db.prepare("PRAGMA user_version").get() as { user_version: number }).user_version;192 for (let i = version; i < MIGRATIONS.length; i++) {193 db.exec("BEGIN IMMEDIATE");194 try {195 // Another process may have migrated while we waited for the lock.196 const now = (db.prepare("PRAGMA user_version").get() as { user_version: number }).user_version;197 if (now <= i) { db.exec(MIGRATIONS[i]); db.exec(`PRAGMA user_version = ${i + 1}`); }198 db.exec("COMMIT");199 } catch (e) { db.exec("ROLLBACK"); throw e; }200 }201 try { fs.chmodSync(env.dbPath, 0o600); } catch { /* best effort */ }202 return db;203}204205export function db(): DatabaseSync {206 const g = globalThis as G;207 return (g.__sptvDb ??= open());208}209export function closeDb() { const g = globalThis as G; g.__sptvDb?.close(); g.__sptvDb = undefined; }210211type P = SQLInputValue;212export const all = <T = Record<string, unknown>>(sql: string, ...p: P[]) => db().prepare(sql).all(...p) as T[];213export const get = <T = Record<string, unknown>>(sql: string, ...p: P[]) => db().prepare(sql).get(...p) as T | undefined;214export const run = (sql: string, ...p: P[]) => db().prepare(sql).run(...p);215216export function tx<T>(fn: () => T): T {217 const d = db();218 d.exec("BEGIN IMMEDIATE");219 try { const r = fn(); d.exec("COMMIT"); return r; } catch (e) { d.exec("ROLLBACK"); throw e; }220}221222export const now = () => Date.now();223224// --- settings -------------------------------------------------------------------------------------------------------225export const DEFAULT_SETTINGS = {226 sync_interval_hours: "24",227 check_enabled: "1",228 check_concurrency: "6",229 check_host_delay_ms: "1500",230 check_timeout_ms: "10000",231 check_recheck_hours: "24",232 check_priority_hours: "6",233 epg_enabled: "1",234 epg_interval_hours: "12",235 epg_countries: "CA,FR",236 epg_max_channels: "250",237 epg_days: "2",238 session_days: "60",239 auto_resume: "1",240 premium_filter: "1",241 vod_interval_hours: "168",242 library_interval_minutes: "15",243 transcode_cache_gb: "30",244 sources_interval_hours: "24",245 relay_enabled: "1",246 relay_max_relays: "6",247 relay_max_transcodes: "2",248 relay_max_sessions: "12",249 relay_buffer_sec: "12",250 relay_idle_sec: "30",251 relay_out_kbps: "0",252 relay_video_kbps: "4500",253 relay_timeshift_min: "120",254 relay_reconnect_max: "8",255} as const;256export type SettingKey = keyof typeof DEFAULT_SETTINGS;257258export function getSetting(key: string): string | undefined {259 return get<{ value: string }>("SELECT value FROM settings WHERE key = ?", key)?.value ?? (DEFAULT_SETTINGS as Record<string, string>)[key];260}261export const getNum = (key: SettingKey) => Number(getSetting(key));262export function setSetting(key: string, value: string) {263 run("INSERT INTO settings(key, value) VALUES(?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", key, value);264}265266// --- logs -----------------------------------------------------------------------------------------------------------267export function log(level: "info" | "warn" | "error", scope: string, message: string, meta?: unknown) {268 const line = `${new Date().toISOString()} ${level.toUpperCase()} [${scope}] ${message}${meta ? " " + JSON.stringify(meta) : ""}`;269 (level === "error" ? console.error : console.log)(line);270 try { run("INSERT INTO logs(at, level, scope, message, meta) VALUES(?,?,?,?,?)", now(), level, scope, message.slice(0, 2000), meta ? JSON.stringify(meta).slice(0, 4000) : null); } catch { /* never let logging break the caller */ }271}272/** Bumped whenever the catalogue changes; the web process rebuilds its in-memory index when it sees a new value. */273export function bumpCatalog() { setSetting("catalog_version", String(now())); }274export function bumpTitles() { setSetting("titles_version", String(now())); }275