andrew
68cb007e4c
Moves all ~30 pipeline scripts, configs, and the vendored sldl binary into this repo (source /opt/sldl left untouched). Removes all docker exec/docker compose dependencies now that beets and sldl run in-process/as a subprocess of this container instead of via soulbeet/on-demand sldl containers. Replaces hardcoded host paths, Navidrome credentials, and Spotify credential sourcing with env-var-driven paths and shared credential loaders. Adds Dockerfile, entrypoint.sh, requirements.txt, docker-compose.snippet.yml, and the initial app DB schema.
133 lines
3.6 KiB
SQL
133 lines
3.6 KiB
SQL
-- alembic app database — initial schema.
|
|
-- Applied once at startup by app/db.py if schema_version < 1. Plain SQL
|
|
-- migrations, not the `alembic` Python migration tool (name collision
|
|
-- avoided deliberately — see docs/ARCHITECTURE.md).
|
|
|
|
CREATE TABLE IF NOT EXISTS schema_version (
|
|
version INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS playlists (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT UNIQUE NOT NULL,
|
|
spotify_url TEXT NOT NULL,
|
|
active BOOLEAN NOT NULL DEFAULT 1,
|
|
no_m3u BOOLEAN NOT NULL DEFAULT 0,
|
|
cron_expr TEXT,
|
|
notes TEXT,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS secrets (
|
|
id INTEGER PRIMARY KEY,
|
|
scope TEXT NOT NULL,
|
|
key TEXT NOT NULL,
|
|
value_encrypted BLOB NOT NULL,
|
|
updated_at REAL NOT NULL,
|
|
UNIQUE(scope, key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS scheduled_jobs (
|
|
id INTEGER PRIMARY KEY,
|
|
job_key TEXT UNIQUE NOT NULL,
|
|
job_type TEXT NOT NULL,
|
|
playlist_id INTEGER REFERENCES playlists(id),
|
|
cron_expr TEXT NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT 1
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS job_runs (
|
|
id INTEGER PRIMARY KEY,
|
|
job_key TEXT NOT NULL,
|
|
started_at REAL NOT NULL,
|
|
finished_at REAL,
|
|
status TEXT NOT NULL,
|
|
exit_code INTEGER,
|
|
summary TEXT,
|
|
log_path TEXT,
|
|
triggered_by TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS manual_fix_audit (
|
|
id INTEGER PRIMARY KEY,
|
|
beets_item_id INTEGER NOT NULL,
|
|
file_path TEXT NOT NULL,
|
|
field TEXT NOT NULL,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
changed_by TEXT NOT NULL,
|
|
changed_at REAL NOT NULL,
|
|
source TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS manual_imports (
|
|
id INTEGER PRIMARY KEY,
|
|
source_path TEXT NOT NULL,
|
|
playlist_name TEXT,
|
|
imported_by TEXT NOT NULL,
|
|
imported_at REAL NOT NULL,
|
|
status TEXT NOT NULL,
|
|
job_run_id INTEGER REFERENCES job_runs(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS dedup_runs (
|
|
id INTEGER PRIMARY KEY,
|
|
started_at REAL NOT NULL,
|
|
finished_at REAL,
|
|
mode TEXT NOT NULL,
|
|
groups_found INTEGER,
|
|
kept INTEGER,
|
|
deleted INTEGER,
|
|
log_path TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS dedup_candidates (
|
|
id INTEGER PRIMARY KEY,
|
|
dedup_run_id INTEGER NOT NULL REFERENCES dedup_runs(id),
|
|
pass_name TEXT NOT NULL,
|
|
keep_path TEXT NOT NULL,
|
|
delete_path TEXT NOT NULL,
|
|
delete_size_bytes INTEGER,
|
|
confirmed BOOLEAN NOT NULL DEFAULT 0,
|
|
confirmed_by TEXT,
|
|
confirmed_at REAL,
|
|
applied BOOLEAN NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS genre_runs (
|
|
id INTEGER PRIMARY KEY,
|
|
started_at REAL,
|
|
finished_at REAL,
|
|
mode TEXT,
|
|
written INTEGER,
|
|
unchanged INTEGER,
|
|
no_match INTEGER,
|
|
no_meta INTEGER,
|
|
log_path TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS genre_candidates (
|
|
id INTEGER PRIMARY KEY,
|
|
genre_run_id INTEGER NOT NULL REFERENCES genre_runs(id),
|
|
artist TEXT,
|
|
title TEXT,
|
|
old_genre TEXT,
|
|
new_genre TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY,
|
|
oidc_sub TEXT UNIQUE NOT NULL,
|
|
email TEXT,
|
|
name TEXT,
|
|
last_login_at REAL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS app_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT
|
|
);
|
|
|
|
INSERT INTO schema_version (version) SELECT 1 WHERE NOT EXISTS (SELECT 1 FROM schema_version);
|