98 lines
5.0 KiB
SQL
98 lines
5.0 KiB
SQL
-- Persistent, tenant-isolated state for the built-in song request component.
|
|
-- Component settings remain JSONB; relational queue and rating data live here
|
|
-- because they are durable business facts rather than renderer preferences.
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS component_instances_single_song_request
|
|
ON component_instances(owner_user_id, kind)
|
|
WHERE kind = 'song_request';
|
|
|
|
CREATE TABLE IF NOT EXISTS song_request_state (
|
|
owner_user_id UUID NOT NULL,
|
|
component_instance_id UUID PRIMARY KEY,
|
|
revision BIGINT NOT NULL DEFAULT 0 CHECK (revision >= 0),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (owner_user_id, component_instance_id),
|
|
FOREIGN KEY (owner_user_id, component_instance_id)
|
|
REFERENCES component_instances(owner_user_id, id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS song_requests (
|
|
id UUID PRIMARY KEY,
|
|
owner_user_id UUID NOT NULL,
|
|
component_instance_id UUID NOT NULL,
|
|
requester_uid TEXT NOT NULL CHECK (char_length(requester_uid) BETWEEN 1 AND 64),
|
|
requester_name TEXT NOT NULL CHECK (char_length(requester_name) BETWEEN 1 AND 80),
|
|
song_title TEXT NOT NULL CHECK (char_length(song_title) BETWEEN 1 AND 80),
|
|
normalized_song_title TEXT NOT NULL CHECK (char_length(normalized_song_title) BETWEEN 1 AND 80),
|
|
status TEXT NOT NULL CHECK (status IN ('current', 'queued', 'completed', 'cancelled')),
|
|
queue_position BIGINT NOT NULL DEFAULT 0 CHECK (queue_position >= 0),
|
|
source_event_id UUID NOT NULL,
|
|
requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
started_at TIMESTAMPTZ,
|
|
finished_at TIMESTAMPTZ,
|
|
UNIQUE (owner_user_id, id),
|
|
UNIQUE (owner_user_id, component_instance_id, id),
|
|
UNIQUE (component_instance_id, source_event_id),
|
|
FOREIGN KEY (owner_user_id, component_instance_id)
|
|
REFERENCES song_request_state(owner_user_id, component_instance_id) ON DELETE CASCADE,
|
|
CHECK ((status = 'queued' AND queue_position > 0 AND started_at IS NULL AND finished_at IS NULL)
|
|
OR (status = 'current' AND queue_position = 0 AND started_at IS NOT NULL AND finished_at IS NULL)
|
|
OR (status IN ('completed', 'cancelled') AND queue_position = 0 AND finished_at IS NOT NULL))
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS song_requests_one_current
|
|
ON song_requests(component_instance_id)
|
|
WHERE status = 'current';
|
|
CREATE UNIQUE INDEX IF NOT EXISTS song_requests_unique_active_title
|
|
ON song_requests(component_instance_id, normalized_song_title)
|
|
WHERE status IN ('current', 'queued');
|
|
CREATE INDEX IF NOT EXISTS song_requests_active_order
|
|
ON song_requests(owner_user_id, component_instance_id, status, queue_position);
|
|
CREATE INDEX IF NOT EXISTS song_requests_history
|
|
ON song_requests(owner_user_id, component_instance_id, finished_at DESC)
|
|
WHERE status IN ('completed', 'cancelled');
|
|
CREATE INDEX IF NOT EXISTS song_requests_requester_active
|
|
ON song_requests(owner_user_id, component_instance_id, requester_uid, requested_at DESC)
|
|
WHERE status IN ('current', 'queued');
|
|
|
|
CREATE TABLE IF NOT EXISTS song_ratings (
|
|
id UUID PRIMARY KEY,
|
|
owner_user_id UUID NOT NULL,
|
|
component_instance_id UUID NOT NULL,
|
|
song_request_id UUID NOT NULL,
|
|
viewer_uid TEXT NOT NULL CHECK (char_length(viewer_uid) BETWEEN 1 AND 64),
|
|
viewer_name TEXT NOT NULL CHECK (char_length(viewer_name) BETWEEN 1 AND 80),
|
|
score SMALLINT NOT NULL CHECK (score BETWEEN 1 AND 5),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (song_request_id, viewer_uid),
|
|
FOREIGN KEY (owner_user_id, component_instance_id)
|
|
REFERENCES song_request_state(owner_user_id, component_instance_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (owner_user_id, component_instance_id, song_request_id)
|
|
REFERENCES song_requests(owner_user_id, component_instance_id, id) ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS song_ratings_request
|
|
ON song_ratings(owner_user_id, component_instance_id, song_request_id);
|
|
|
|
ALTER TABLE song_request_state ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE song_request_state FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS song_request_state_owner ON song_request_state;
|
|
CREATE POLICY song_request_state_owner ON song_request_state
|
|
USING (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID)
|
|
WITH CHECK (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID);
|
|
|
|
ALTER TABLE song_requests ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE song_requests FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS song_requests_owner ON song_requests;
|
|
CREATE POLICY song_requests_owner ON song_requests
|
|
USING (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID)
|
|
WITH CHECK (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID);
|
|
|
|
ALTER TABLE song_ratings ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE song_ratings FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS song_ratings_owner ON song_ratings;
|
|
CREATE POLICY song_ratings_owner ON song_ratings
|
|
USING (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID)
|
|
WITH CHECK (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID);
|