File size: 3,005 Bytes
41e1749 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | -- Phase 5: profiles table + signup trigger
-- Apply in Supabase SQL Editor
-- Profiles table
create table if not exists public.profiles (
id uuid primary key references auth.users(id) on delete cascade,
display_name text,
avatar_url text,
auth_provider text not null default 'anonymous',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
alter table public.profiles enable row level security;
-- Users can read and update their own profile
create policy "profiles_select_own"
on public.profiles for select
using (auth.uid() = id);
create policy "profiles_update_own"
on public.profiles for update
using (auth.uid() = id);
create policy "profiles_insert_own"
on public.profiles for insert
with check (auth.uid() = id);
-- Auto-create profile on signup (anonymous or Google)
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
provider text;
name text;
avatar text;
begin
provider := coalesce(
new.raw_app_meta_data->>'provider',
(select identity_data->>'provider' from auth.identities where user_id = new.id limit 1),
'anonymous'
);
if new.is_anonymous then
provider := 'anonymous';
end if;
name := coalesce(
new.raw_user_meta_data->>'full_name',
new.raw_user_meta_data->>'name',
case when new.is_anonymous then 'ضيف' else new.email end
);
avatar := coalesce(
new.raw_user_meta_data->>'avatar_url',
new.raw_user_meta_data->>'picture'
);
insert into public.profiles (id, display_name, avatar_url, auth_provider)
values (new.id, name, avatar, provider)
on conflict (id) do update set
display_name = excluded.display_name,
avatar_url = excluded.avatar_url,
auth_provider = excluded.auth_provider,
updated_at = now();
return new;
end;
$$;
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 function public.handle_new_user();
-- Update profile when user links Google (identity change)
create or replace function public.handle_user_updated()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
provider text;
begin
if old.is_anonymous = true and new.is_anonymous = false then
provider := coalesce(new.raw_app_meta_data->>'provider', 'google');
update public.profiles
set
auth_provider = provider,
display_name = coalesce(new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'name', display_name),
avatar_url = coalesce(new.raw_user_meta_data->>'avatar_url', new.raw_user_meta_data->>'picture', avatar_url),
updated_at = now()
where id = new.id;
end if;
return new;
end;
$$;
drop trigger if exists on_auth_user_updated on auth.users;
create trigger on_auth_user_updated
after update on auth.users
for each row execute function public.handle_user_updated();
|