supabase_schema.sql

-- Enable UUID extension
create extension if not exists "pgcrypto";

-- ROLES
create table if not exists public.roles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  role text not null check (role in ('admin', 'user')) default 'user',
  created_at timestamptz default now()
);
alter table public.roles enable row level security;

-- PROFILES
create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  prn_number text unique,
  email text,
  full_name text,
  avatar_url text,
  website text,
  bio text,
  updated_at timestamptz
);
alter table public.profiles enable row level security;

-- POLICIES

-- Profiles
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);
create policy "Admins can update any profile" on profiles for update using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can delete any profile" on profiles for delete using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);

-- Roles
create policy "Roles viewable by everyone" on roles for select using (true);
create policy "Admins can insert roles" on roles for insert with check (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can update roles" on roles for update using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can delete roles" on roles for delete using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);

-- HANDLERS

create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.roles (user_id, role)
  values (new.id, 'user');
  
  insert into public.profiles (id, full_name, avatar_url, prn_number, email)
  values (
    new.id, 
    new.raw_user_meta_data->>'full_name', 
    new.raw_user_meta_data->>'avatar_url',
    new.raw_user_meta_data->>'prn_number',
    new.email
  );
  
  return new;
exception
  when others then
    return new;
end;
$$ language plpgsql security definer;

-- Trigger
drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();