254 lines
8.7 KiB
SQL
254 lines
8.7 KiB
SQL
-- Custom types
|
|
CREATE TYPE MEDIA_TYPE AS ENUM (
|
|
'movie', 'series', 'episode', 'music', 'album',
|
|
'audiobook', 'podcast', 'photo', 'other'
|
|
);
|
|
|
|
CREATE TYPE MEDIA_STATUS AS ENUM (
|
|
'unavailable', 'searching', 'downloading', 'importing',
|
|
'available', 'upgrading', 'failed'
|
|
);
|
|
|
|
CREATE TYPE QUEUE_STATUS AS ENUM (
|
|
'pending', 'downloading', 'imported', 'failed',
|
|
'blacklisted', 'cancelled'
|
|
);
|
|
|
|
-- Quality profiles
|
|
CREATE TABLE quality_profiles (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
media_types MEDIA_TYPE[] NOT NULL,
|
|
cutoff_quality JSONB NOT NULL,
|
|
allowed_qualities JSONB NOT NULL DEFAULT '[]',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Root folders
|
|
CREATE TABLE root_folders (
|
|
id SERIAL PRIMARY KEY,
|
|
path TEXT NOT NULL UNIQUE,
|
|
media_type MEDIA_TYPE NOT NULL,
|
|
free_space BIGINT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Tags
|
|
CREATE TABLE tags (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
color TEXT DEFAULT '#6366f1'
|
|
);
|
|
|
|
-- Scheduled tasks
|
|
CREATE TABLE scheduled_tasks (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
cron_expr TEXT NOT NULL,
|
|
last_run_at TIMESTAMPTZ,
|
|
next_run_at TIMESTAMPTZ,
|
|
enabled BOOLEAN DEFAULT true,
|
|
retention_days INTEGER DEFAULT 7
|
|
);
|
|
|
|
-- Indexers
|
|
CREATE TABLE indexers (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
implementation TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
api_key TEXT,
|
|
categories JSONB DEFAULT '[]',
|
|
settings JSONB DEFAULT '{}',
|
|
enabled BOOLEAN DEFAULT true,
|
|
priority INTEGER DEFAULT 0,
|
|
last_success_at TIMESTAMPTZ,
|
|
failure_count INTEGER DEFAULT 0,
|
|
disabled_until TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Download clients
|
|
CREATE TABLE download_clients (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
implementation TEXT NOT NULL,
|
|
host TEXT NOT NULL,
|
|
port INTEGER NOT NULL,
|
|
username TEXT,
|
|
password TEXT,
|
|
settings JSONB DEFAULT '{}',
|
|
enabled BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Unified media table (partitioned by type)
|
|
CREATE TABLE media (
|
|
id BIGSERIAL,
|
|
media_type MEDIA_TYPE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
sort_title TEXT NOT NULL,
|
|
original_title TEXT,
|
|
overview TEXT,
|
|
year INTEGER,
|
|
status MEDIA_STATUS NOT NULL DEFAULT 'unavailable',
|
|
monitored BOOLEAN NOT NULL DEFAULT false,
|
|
external_ids JSONB NOT NULL DEFAULT '{}',
|
|
metadata JSONB NOT NULL DEFAULT '{}',
|
|
images JSONB NOT NULL DEFAULT '[]',
|
|
quality_profile_id INTEGER REFERENCES quality_profiles(id),
|
|
root_folder_id INTEGER REFERENCES root_folders(id),
|
|
current_quality JSONB,
|
|
desired_quality JSONB,
|
|
added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_search_at TIMESTAMPTZ,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
PRIMARY KEY (id, media_type)
|
|
) PARTITION BY LIST (media_type);
|
|
|
|
CREATE TABLE media_movie PARTITION OF media FOR VALUES IN ('movie');
|
|
CREATE TABLE media_series PARTITION OF media FOR VALUES IN ('series');
|
|
CREATE TABLE media_episode PARTITION OF media FOR VALUES IN ('episode');
|
|
CREATE TABLE media_music PARTITION OF media FOR VALUES IN ('music');
|
|
CREATE TABLE media_album PARTITION OF media FOR VALUES IN ('album');
|
|
CREATE TABLE media_audiobook PARTITION OF media FOR VALUES IN ('audiobook');
|
|
CREATE TABLE media_podcast PARTITION OF media FOR VALUES IN ('podcast');
|
|
CREATE TABLE media_photo PARTITION OF media FOR VALUES IN ('photo');
|
|
CREATE TABLE media_other PARTITION OF media FOR VALUES IN ('other');
|
|
|
|
-- Media indexes
|
|
CREATE INDEX idx_media_title ON media USING gin (to_tsvector('english', coalesce(title, '')));
|
|
CREATE INDEX idx_media_monitored ON media (monitored) WHERE monitored = true;
|
|
CREATE INDEX idx_media_status ON media (status, media_type);
|
|
CREATE INDEX idx_media_external_ids ON media USING gin (external_ids);
|
|
|
|
-- Media relations (series->episodes, album->tracks)
|
|
CREATE TABLE media_relations (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
parent_id BIGINT NOT NULL,
|
|
child_id BIGINT NOT NULL,
|
|
relation TEXT NOT NULL,
|
|
position INTEGER,
|
|
season INTEGER,
|
|
UNIQUE(parent_id, child_id, relation)
|
|
);
|
|
|
|
-- Media tags
|
|
CREATE TABLE media_tags (
|
|
media_id BIGINT NOT NULL,
|
|
media_type MEDIA_TYPE NOT NULL,
|
|
tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (media_id, media_type, tag_id)
|
|
);
|
|
|
|
-- Unified file tracking
|
|
CREATE TABLE media_files (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
media_id BIGINT NOT NULL,
|
|
media_type MEDIA_TYPE NOT NULL,
|
|
path TEXT NOT NULL,
|
|
original_path TEXT,
|
|
file_name TEXT NOT NULL,
|
|
file_size BIGINT NOT NULL DEFAULT 0,
|
|
quality JSONB NOT NULL DEFAULT '{}',
|
|
codec TEXT,
|
|
resolution TEXT,
|
|
source TEXT,
|
|
is_hardlinked BOOLEAN DEFAULT false,
|
|
checksum TEXT,
|
|
transcode_status TEXT DEFAULT 'none',
|
|
transcode_preset TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
UNIQUE(media_id, media_type, path)
|
|
);
|
|
|
|
CREATE INDEX idx_media_files_media ON media_files (media_id, media_type);
|
|
CREATE INDEX idx_media_files_transcode ON media_files (transcode_status) WHERE transcode_status != 'done';
|
|
|
|
-- Download queue
|
|
CREATE TABLE download_queue (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
media_id BIGINT NOT NULL,
|
|
media_type MEDIA_TYPE NOT NULL,
|
|
release_title TEXT NOT NULL,
|
|
release_url TEXT,
|
|
indexer TEXT NOT NULL,
|
|
download_client TEXT NOT NULL,
|
|
quality JSONB NOT NULL DEFAULT '{}',
|
|
size BIGINT,
|
|
protocol TEXT NOT NULL DEFAULT 'torrent',
|
|
status QUEUE_STATUS NOT NULL DEFAULT 'pending',
|
|
progress REAL DEFAULT 0,
|
|
error_message TEXT,
|
|
batch_id UUID,
|
|
priority INTEGER DEFAULT 0,
|
|
retry_count INTEGER DEFAULT 0,
|
|
max_retries INTEGER DEFAULT 3,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_queue_status ON download_queue (status, priority DESC);
|
|
CREATE INDEX idx_queue_batch ON download_queue (batch_id) WHERE batch_id IS NOT NULL;
|
|
CREATE INDEX idx_queue_media ON download_queue (media_id, media_type);
|
|
|
|
-- Blocklist
|
|
CREATE TABLE blocklist (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
release_title TEXT NOT NULL,
|
|
source_title TEXT,
|
|
quality JSONB DEFAULT '{}',
|
|
indexer TEXT,
|
|
protocol TEXT DEFAULT 'torrent',
|
|
torrent_hash TEXT,
|
|
size BIGINT,
|
|
message TEXT,
|
|
media_id BIGINT,
|
|
media_type MEDIA_TYPE,
|
|
block_reason TEXT DEFAULT 'manual',
|
|
auto_expires_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_blocklist_media ON blocklist (media_id, media_type);
|
|
CREATE INDEX idx_blocklist_expires ON blocklist (auto_expires_at) WHERE auto_expires_at IS NOT NULL;
|
|
|
|
-- Task execution log
|
|
CREATE TABLE task_executions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
task_id INTEGER NOT NULL REFERENCES scheduled_tasks(id) ON DELETE CASCADE,
|
|
status TEXT NOT NULL DEFAULT 'running',
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ended_at TIMESTAMPTZ,
|
|
duration_ms INTEGER,
|
|
result JSONB,
|
|
error TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_task_exec_task ON task_executions (task_id, started_at DESC);
|
|
|
|
-- Download history (partitioned for auto-cleanup)
|
|
CREATE TABLE download_history (
|
|
id BIGSERIAL,
|
|
media_id BIGINT NOT NULL,
|
|
media_type MEDIA_TYPE NOT NULL,
|
|
action TEXT NOT NULL,
|
|
release_title TEXT,
|
|
quality JSONB DEFAULT '{}',
|
|
indexer TEXT,
|
|
client TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (id, created_at)
|
|
) PARTITION BY RANGE (created_at);
|
|
|
|
CREATE TABLE download_history_current PARTITION OF download_history
|
|
FOR VALUES FROM (CURRENT_DATE - INTERVAL '90 days') TO (MAXVALUE);
|