supabase-database.sql

-- Create a table for public profiles
create table profiles (
  id uuid references auth.users not null primary key,
  role text check (role in ('webmaster', 'admin', 'cs_chair', 'pes_chair', 'ras_chair', 'wie_chair', 'sight_chair', 'sscs_chair', 'ias_chair', 'sps_chair', 'pending')) default 'pending',
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Set up Row Level Security (RLS)
alter table profiles enable row level security;

create policy "Public profiles are viewable by everyone."
  on profiles for select
  using ( true );

create policy "Users can insert their own profile."
  on profiles for insert
  with check ( auth.uid() = id );

create policy "Users can update own profile."
  on profiles for update
  using ( auth.uid() = id );

-- This trigger automatically creates a profile entry when a new user signs up via Supabase Auth.
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (id, role)
  values (new.id, 'pending'); -- Defaulting new users to pending for security
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();