bvandeusen
71a9bd8dee
- Composite PK (user_id, track_id) already serves WHERE user_id queries;
the secondary (user_id, created_at DESC) index just amplified writes.
- ListQuarantineForUser now selects only the joined fields the SPA card
actually renders (~10 columns) rather than embedding three full structs
(~35 columns); halves wire/DB bandwidth before consumers exist.
- max(q.created_at)::timestamptz cast emits pgtype.Timestamptz instead of
interface{} so handlers can read latest_at without a type-assert.
49 lines
2.2 KiB
SQL
49 lines
2.2 KiB
SQL
-- M5b: per-user track quarantines + admin action audit log.
|
|
--
|
|
-- lidarr_quarantine — one row per (user, track) complaint. PK matches
|
|
-- the general_likes pattern. Re-flagging the same track upserts. Deleted
|
|
-- on user resolution (un-hide), admin Resolve, or any of the deletes.
|
|
--
|
|
-- lidarr_quarantine_actions — audit log of admin destructive actions.
|
|
-- Snapshot text columns let the log stay readable after the underlying
|
|
-- track/album rows are gone.
|
|
|
|
CREATE TYPE lidarr_quarantine_reason AS ENUM (
|
|
'bad_rip', 'wrong_file', 'wrong_tags', 'duplicate', 'other'
|
|
);
|
|
|
|
CREATE TABLE lidarr_quarantine (
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
track_id uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
|
|
reason lidarr_quarantine_reason NOT NULL,
|
|
notes text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (user_id, track_id)
|
|
);
|
|
|
|
-- Only the track_id index is non-redundant: the composite PK
|
|
-- (user_id, track_id) already serves WHERE user_id = $1 lookups, so a
|
|
-- separate (user_id, created_at DESC) index would just amplify writes
|
|
-- without paying for itself at household-scale row counts.
|
|
CREATE INDEX lidarr_quarantine_track_idx ON lidarr_quarantine (track_id);
|
|
|
|
CREATE TYPE lidarr_quarantine_action_kind AS ENUM (
|
|
'resolved', 'deleted_file', 'deleted_via_lidarr'
|
|
);
|
|
|
|
CREATE TABLE lidarr_quarantine_actions (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
track_id uuid NOT NULL,
|
|
track_title text NOT NULL,
|
|
artist_name text NOT NULL,
|
|
album_title text,
|
|
action lidarr_quarantine_action_kind NOT NULL,
|
|
admin_id uuid REFERENCES users(id) ON DELETE SET NULL,
|
|
lidarr_album_mbid text,
|
|
affected_users int NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX lidarr_quarantine_actions_track_idx ON lidarr_quarantine_actions (track_id);
|
|
CREATE INDEX lidarr_quarantine_actions_created_idx ON lidarr_quarantine_actions (created_at DESC);
|