61 lines
1.7 KiB
SQL
61 lines
1.7 KiB
SQL
-- Playlists
|
|
CREATE TABLE playlists (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
name text NOT NULL,
|
|
description text,
|
|
cover_url text,
|
|
is_public boolean NOT NULL DEFAULT true,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
ALTER TABLE playlists ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Anyone can view public playlists"
|
|
ON playlists FOR SELECT
|
|
USING (is_public = true OR user_id = auth.uid());
|
|
|
|
CREATE POLICY "Users can manage their own playlists"
|
|
ON playlists FOR ALL
|
|
USING (user_id = auth.uid())
|
|
WITH CHECK (user_id = auth.uid());
|
|
|
|
-- Playlist items
|
|
CREATE TABLE playlist_items (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
playlist_id uuid NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
|
|
podcast_id uuid NOT NULL REFERENCES podcasts(id) ON DELETE CASCADE,
|
|
sort_order integer NOT NULL DEFAULT 0,
|
|
added_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (playlist_id, podcast_id)
|
|
);
|
|
|
|
ALTER TABLE playlist_items ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Anyone can view items of visible playlists"
|
|
ON playlist_items FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM playlists
|
|
WHERE playlists.id = playlist_items.playlist_id
|
|
AND (playlists.is_public = true OR playlists.user_id = auth.uid())
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Playlist owners can manage items"
|
|
ON playlist_items FOR ALL
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM playlists
|
|
WHERE playlists.id = playlist_items.playlist_id
|
|
AND playlists.user_id = auth.uid()
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM playlists
|
|
WHERE playlists.id = playlist_items.playlist_id
|
|
AND playlists.user_id = auth.uid()
|
|
)
|
|
);
|