64 lines
2.8 KiB
SQL
64 lines
2.8 KiB
SQL
-- M5a: Lidarr integration foundation. Two tables:
|
|
--
|
|
-- lidarr_config — singleton (CHECK id=1) holding the operator's Lidarr
|
|
-- connection. enabled=false is the unconfigured state.
|
|
--
|
|
-- lidarr_requests — per-request lifecycle row created by users at
|
|
-- /discover, transitioned by admin at /admin/requests, and matched
|
|
-- back to library tracks by the reconciler worker. Three matched_*_id
|
|
-- FKs (one per kind) instead of polymorphic — clean SQL, ON DELETE
|
|
-- SET NULL preserves audit even if the matched track is later removed.
|
|
|
|
CREATE TABLE lidarr_config (
|
|
id smallint PRIMARY KEY DEFAULT 1 CHECK (id = 1),
|
|
enabled boolean NOT NULL DEFAULT false,
|
|
base_url text,
|
|
api_key text,
|
|
default_quality_profile_id int,
|
|
default_root_folder_path text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
INSERT INTO lidarr_config (id, enabled) VALUES (1, false);
|
|
|
|
CREATE TYPE lidarr_request_status AS ENUM (
|
|
'pending', 'approved', 'rejected', 'completed', 'failed'
|
|
);
|
|
CREATE TYPE lidarr_request_kind AS ENUM ('artist', 'album', 'track');
|
|
|
|
CREATE TABLE lidarr_requests (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
status lidarr_request_status NOT NULL DEFAULT 'pending',
|
|
kind lidarr_request_kind NOT NULL,
|
|
|
|
lidarr_artist_mbid text NOT NULL,
|
|
lidarr_album_mbid text,
|
|
lidarr_track_mbid text,
|
|
artist_name text NOT NULL,
|
|
album_title text,
|
|
track_title text,
|
|
|
|
quality_profile_id int,
|
|
root_folder_path text,
|
|
|
|
decided_at timestamptz,
|
|
decided_by uuid REFERENCES users(id) ON DELETE SET NULL,
|
|
notes text,
|
|
|
|
completed_at timestamptz,
|
|
matched_track_id uuid REFERENCES tracks(id) ON DELETE SET NULL,
|
|
matched_album_id uuid REFERENCES albums(id) ON DELETE SET NULL,
|
|
matched_artist_id uuid REFERENCES artists(id) ON DELETE SET NULL,
|
|
|
|
requested_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX lidarr_requests_user_id_idx ON lidarr_requests (user_id);
|
|
CREATE INDEX lidarr_requests_status_idx ON lidarr_requests (status);
|
|
CREATE INDEX lidarr_requests_artist_mbid_idx ON lidarr_requests (lidarr_artist_mbid);
|
|
CREATE INDEX lidarr_requests_album_mbid_idx ON lidarr_requests (lidarr_album_mbid)
|
|
WHERE lidarr_album_mbid IS NOT NULL;
|