279 lines
9.3 KiB
PL/PgSQL
279 lines
9.3 KiB
PL/PgSQL
-- ============================================================
|
|
-- 0001_init.sql — Devodytyji schema, RLS, storage, seed helpers
|
|
-- Run in the Supabase SQL editor or via `supabase db push`.
|
|
-- ============================================================
|
|
|
|
-- ---------- profiles ----------
|
|
create table public.profiles (
|
|
id uuid primary key references auth.users (id) on delete cascade,
|
|
full_name text,
|
|
avatar_url text,
|
|
is_admin boolean not null default false,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Auto-create a profile for every new auth user
|
|
create or replace function public.handle_new_user()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
begin
|
|
insert into public.profiles (id, full_name, avatar_url)
|
|
values (
|
|
new.id,
|
|
coalesce(
|
|
new.raw_user_meta_data->>'full_name',
|
|
new.raw_user_meta_data->>'name',
|
|
split_part(coalesce(new.email, ''), '@', 1)
|
|
),
|
|
new.raw_user_meta_data->>'avatar_url'
|
|
)
|
|
on conflict (id) do nothing;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
create trigger on_auth_user_created
|
|
after insert on auth.users
|
|
for each row execute function public.handle_new_user();
|
|
|
|
-- Helper to promote a user to admin by email (run from dashboard/psql)
|
|
create or replace function public.promote_user_to_admin(target_email text)
|
|
returns void
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
begin
|
|
update public.profiles p
|
|
set is_admin = true
|
|
from auth.users u
|
|
where u.id = p.id
|
|
and lower(u.email) = lower(target_email);
|
|
if not found then
|
|
raise exception 'No user found with email %', target_email;
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
-- ---------- events ----------
|
|
create table public.events (
|
|
id uuid primary key default gen_random_uuid(),
|
|
slug text unique not null,
|
|
title jsonb not null,
|
|
description jsonb not null default '{"ua":"","en":""}'::jsonb,
|
|
cover_image_url text,
|
|
starts_at timestamptz not null,
|
|
ends_at timestamptz not null,
|
|
venue_name text,
|
|
status text not null default 'draft' check (status in ('draft', 'published')),
|
|
created_by uuid references public.profiles (id) on delete set null,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index events_slug_idx on public.events (slug);
|
|
create index events_starts_at_idx on public.events (starts_at);
|
|
create index events_status_idx on public.events (status);
|
|
|
|
-- ---------- event_reviews ----------
|
|
create table public.event_reviews (
|
|
id uuid primary key default gen_random_uuid(),
|
|
event_id uuid not null references public.events (id) on delete cascade,
|
|
user_id uuid not null references public.profiles (id) on delete cascade,
|
|
rating int not null check (rating between 1 and 5),
|
|
comment text,
|
|
created_at timestamptz not null default now(),
|
|
unique (event_id, user_id)
|
|
);
|
|
|
|
create index event_reviews_event_id_idx on public.event_reviews (event_id);
|
|
|
|
-- ---------- venue_reviews ----------
|
|
create table public.venue_reviews (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null references public.profiles (id) on delete cascade,
|
|
rating int not null check (rating between 1 and 5),
|
|
comment text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- ---------- contact_messages ----------
|
|
create table public.contact_messages (
|
|
id uuid primary key default gen_random_uuid(),
|
|
name text not null,
|
|
email text not null,
|
|
message text not null,
|
|
read boolean not null default false,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index contact_messages_read_idx on public.contact_messages (read);
|
|
|
|
-- ---------- updated_at trigger ----------
|
|
create or replace function public.set_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
new.updated_at = now();
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
create trigger profiles_set_updated_at
|
|
before update on public.profiles
|
|
for each row execute function public.set_updated_at();
|
|
|
|
create trigger events_set_updated_at
|
|
before update on public.events
|
|
for each row execute function public.set_updated_at();
|
|
|
|
-- ---------- contact rate-limit helper (security definer so anon can use it) ----------
|
|
create or replace function public.recent_contact_messages(p_email text, p_within interval default interval '10 minutes')
|
|
returns bigint
|
|
language sql
|
|
security definer
|
|
set search_path = public
|
|
stable
|
|
as $$
|
|
select count(*)::bigint
|
|
from public.contact_messages
|
|
where email = p_email
|
|
and created_at > now() - p_within;
|
|
$$;
|
|
|
|
grant execute on function public.recent_contact_messages(text, interval) to anon, authenticated;
|
|
|
|
-- ============================================================
|
|
-- Row Level Security
|
|
-- ============================================================
|
|
|
|
alter table public.profiles enable row level security;
|
|
alter table public.events enable row level security;
|
|
alter table public.event_reviews enable row level security;
|
|
alter table public.venue_reviews enable row level security;
|
|
alter table public.contact_messages enable row level security;
|
|
|
|
-- profiles: public read, self edit
|
|
create policy "profiles_select_public" on public.profiles
|
|
for select using (true);
|
|
|
|
create policy "profiles_update_self" on public.profiles
|
|
for update using (auth.uid() = id);
|
|
|
|
-- events: published visible to all; admins full CRUD
|
|
create policy "events_select_published" on public.events
|
|
for select using (status = 'published');
|
|
|
|
create policy "events_select_admin" on public.events
|
|
for select using (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
create policy "events_insert_admin" on public.events
|
|
for insert with check (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
create policy "events_update_admin" on public.events
|
|
for update using (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
create policy "events_delete_admin" on public.events
|
|
for delete using (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
-- event_reviews: public read, authenticated insert, owner update/delete, admin delete
|
|
create policy "event_reviews_select_public" on public.event_reviews
|
|
for select using (true);
|
|
|
|
create policy "event_reviews_insert_auth" on public.event_reviews
|
|
for insert with check (auth.uid() = user_id);
|
|
|
|
create policy "event_reviews_update_owner" on public.event_reviews
|
|
for update using (auth.uid() = user_id);
|
|
|
|
create policy "event_reviews_delete_owner" on public.event_reviews
|
|
for delete using (auth.uid() = user_id);
|
|
|
|
create policy "event_reviews_delete_admin" on public.event_reviews
|
|
for delete using (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
-- venue_reviews: public read, authenticated insert, owner update/delete, admin delete
|
|
create policy "venue_reviews_select_public" on public.venue_reviews
|
|
for select using (true);
|
|
|
|
create policy "venue_reviews_insert_auth" on public.venue_reviews
|
|
for insert with check (auth.uid() = user_id);
|
|
|
|
create policy "venue_reviews_update_owner" on public.venue_reviews
|
|
for update using (auth.uid() = user_id);
|
|
|
|
create policy "venue_reviews_delete_owner" on public.venue_reviews
|
|
for delete using (auth.uid() = user_id);
|
|
|
|
create policy "venue_reviews_delete_admin" on public.venue_reviews
|
|
for delete using (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
-- contact_messages: anon insert, admin read/update/delete
|
|
create policy "contact_messages_insert_anon" on public.contact_messages
|
|
for insert with check (true);
|
|
|
|
create policy "contact_messages_select_admin" on public.contact_messages
|
|
for select using (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
create policy "contact_messages_update_admin" on public.contact_messages
|
|
for update using (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
create policy "contact_messages_delete_admin" on public.contact_messages
|
|
for delete using (
|
|
exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Storage: event-covers bucket (public read, admin write)
|
|
-- ============================================================
|
|
insert into storage.buckets (id, name, public)
|
|
values ('event-covers', 'event-covers', true)
|
|
on conflict (id) do nothing;
|
|
|
|
create policy "event_covers_select_public" on storage.objects
|
|
for select using (bucket_id = 'event-covers');
|
|
|
|
create policy "event_covers_insert_admin" on storage.objects
|
|
for insert with check (
|
|
bucket_id = 'event-covers'
|
|
and exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
create policy "event_covers_update_admin" on storage.objects
|
|
for update using (
|
|
bucket_id = 'event-covers'
|
|
and exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
create policy "event_covers_delete_admin" on storage.objects
|
|
for delete using (
|
|
bucket_id = 'event-covers'
|
|
and exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Auth providers (Google + GitHub) must be enabled in the
|
|
-- Supabase dashboard: Authentication → Providers → Google/GitHub.
|
|
-- ============================================================
|