149 lines
5.4 KiB
PL/PgSQL
149 lines
5.4 KiB
PL/PgSQL
-- Profiles (extends auth.users)
|
|
create table public.profiles (
|
|
id uuid references auth.users on delete cascade primary key,
|
|
username text unique not null,
|
|
avatar_url text,
|
|
bio text,
|
|
is_premium boolean default false,
|
|
stripe_customer_id text,
|
|
free_listens_count integer default 0,
|
|
created_at timestamptz default now()
|
|
);
|
|
|
|
alter table public.profiles enable row level security;
|
|
|
|
create policy "Profiles are viewable by everyone" on public.profiles
|
|
for select using (true);
|
|
|
|
create policy "Users can update own profile" on public.profiles
|
|
for update using (auth.uid() = id);
|
|
|
|
-- Auto-create profile on signup
|
|
create or replace function public.handle_new_user()
|
|
returns trigger as $$
|
|
begin
|
|
insert into public.profiles (id, username)
|
|
values (new.id, coalesce(new.raw_user_meta_data->>'username', split_part(new.email, '@', 1)));
|
|
return new;
|
|
end;
|
|
$$ language plpgsql security definer;
|
|
|
|
create trigger on_auth_user_created
|
|
after insert on auth.users
|
|
for each row execute function public.handle_new_user();
|
|
|
|
-- Podcasts
|
|
create table public.podcasts (
|
|
id uuid default gen_random_uuid() primary key,
|
|
creator_id uuid references public.profiles(id) on delete cascade not null,
|
|
title text not null,
|
|
description text default '',
|
|
audio_url text not null,
|
|
duration_seconds integer default 0,
|
|
cover_url text,
|
|
plays_count integer default 0,
|
|
created_at timestamptz default now()
|
|
);
|
|
|
|
alter table public.podcasts enable row level security;
|
|
|
|
create policy "Podcasts are viewable by everyone" on public.podcasts
|
|
for select using (true);
|
|
|
|
create policy "Users can insert own podcasts" on public.podcasts
|
|
for insert with check (auth.uid() = creator_id);
|
|
|
|
create policy "Users can update own podcasts" on public.podcasts
|
|
for update using (auth.uid() = creator_id);
|
|
|
|
create policy "Users can delete own podcasts" on public.podcasts
|
|
for delete using (auth.uid() = creator_id);
|
|
|
|
-- Tags
|
|
create table public.tags (
|
|
id uuid default gen_random_uuid() primary key,
|
|
name text unique not null,
|
|
slug text unique not null
|
|
);
|
|
|
|
alter table public.tags enable row level security;
|
|
create policy "Tags are viewable by everyone" on public.tags for select using (true);
|
|
|
|
create table public.podcast_tags (
|
|
podcast_id uuid references public.podcasts(id) on delete cascade,
|
|
tag_id uuid references public.tags(id) on delete cascade,
|
|
primary key (podcast_id, tag_id)
|
|
);
|
|
|
|
alter table public.podcast_tags enable row level security;
|
|
create policy "Podcast tags are viewable by everyone" on public.podcast_tags for select using (true);
|
|
create policy "Creators can manage podcast tags" on public.podcast_tags
|
|
for all using (
|
|
exists (select 1 from public.podcasts where id = podcast_id and creator_id = auth.uid())
|
|
);
|
|
|
|
-- Likes
|
|
create table public.likes (
|
|
user_id uuid references public.profiles(id) on delete cascade,
|
|
podcast_id uuid references public.podcasts(id) on delete cascade,
|
|
created_at timestamptz default now(),
|
|
primary key (user_id, podcast_id)
|
|
);
|
|
|
|
alter table public.likes enable row level security;
|
|
create policy "Likes are viewable by everyone" on public.likes for select using (true);
|
|
create policy "Users can manage own likes" on public.likes
|
|
for all using (auth.uid() = user_id);
|
|
|
|
-- Comments
|
|
create table public.comments (
|
|
id uuid default gen_random_uuid() primary key,
|
|
user_id uuid references public.profiles(id) on delete cascade not null,
|
|
podcast_id uuid references public.podcasts(id) on delete cascade not null,
|
|
content text not null,
|
|
created_at timestamptz default now()
|
|
);
|
|
|
|
alter table public.comments enable row level security;
|
|
create policy "Comments are viewable by everyone" on public.comments for select using (true);
|
|
create policy "Users can insert own comments" on public.comments
|
|
for insert with check (auth.uid() = user_id);
|
|
create policy "Users can delete own comments" on public.comments
|
|
for delete using (auth.uid() = user_id);
|
|
|
|
-- Follows
|
|
create table public.follows (
|
|
follower_id uuid references public.profiles(id) on delete cascade,
|
|
following_id uuid references public.profiles(id) on delete cascade,
|
|
created_at timestamptz default now(),
|
|
primary key (follower_id, following_id)
|
|
);
|
|
|
|
alter table public.follows enable row level security;
|
|
create policy "Follows are viewable by everyone" on public.follows for select using (true);
|
|
create policy "Users can manage own follows" on public.follows
|
|
for all using (auth.uid() = follower_id);
|
|
|
|
-- Listen history (for freemium tracking)
|
|
create table public.listen_history (
|
|
id uuid default gen_random_uuid() primary key,
|
|
user_id uuid references public.profiles(id) on delete cascade not null,
|
|
podcast_id uuid references public.podcasts(id) on delete cascade not null,
|
|
listened_at timestamptz default now()
|
|
);
|
|
|
|
alter table public.listen_history enable row level security;
|
|
create policy "Users can view own history" on public.listen_history
|
|
for select using (auth.uid() = user_id);
|
|
create policy "Users can insert own history" on public.listen_history
|
|
for insert with check (auth.uid() = user_id);
|
|
|
|
-- Indexes
|
|
create index idx_podcasts_creator on public.podcasts(creator_id);
|
|
create index idx_podcasts_created on public.podcasts(created_at desc);
|
|
create index idx_podcasts_plays on public.podcasts(plays_count desc);
|
|
create index idx_comments_podcast on public.comments(podcast_id);
|
|
create index idx_likes_podcast on public.likes(podcast_id);
|
|
create index idx_follows_following on public.follows(following_id);
|
|
create index idx_listen_history_user on public.listen_history(user_id);
|