Spaces:
Sleeping
Sleeping
File size: 2,295 Bytes
6fc3143 |
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 |
-- 1. Ensure the table exists and has the right columns
create table if not exists public.users (
id uuid references auth.users on delete cascade not null primary key,
email text,
full_name text,
avatar_url text,
credits integer default 5,
created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
-- Add columns if they are missing (idempotent)
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'full_name') THEN
ALTER TABLE public.users ADD COLUMN full_name text;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'avatar_url') THEN
ALTER TABLE public.users ADD COLUMN avatar_url text;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'credits') THEN
ALTER TABLE public.users ADD COLUMN credits integer default 5;
END IF;
END $$;
-- 2. Create a Secure RPC Function to Handle Login
-- This function runs with 'SECURITY DEFINER' privileges, meaning it bypasses RLS.
-- This allows us to safely insert/update the user record without needing the Service Role Key in the frontend.
create or replace function public.handle_user_login(
user_email text,
user_full_name text,
user_avatar_url text
)
returns void
language plpgsql
security definer -- CRITICAL: Runs as database owner
as $$
declare
current_user_id uuid;
begin
-- Get the ID of the currently authenticated user invoking this function
current_user_id := auth.uid();
if current_user_id is null then
raise exception 'Not authenticated';
end if;
-- Insert or Update the user record
insert into public.users (id, email, full_name, avatar_url, credits)
values (
current_user_id,
user_email,
user_full_name,
user_avatar_url,
5 -- Default credits for new users
)
on conflict (id) do update set
email = excluded.email,
full_name = excluded.full_name,
avatar_url = excluded.avatar_url,
-- We DO NOT reset credits on login, only update profile info
credits = public.users.credits;
end;
$$;
-- 3. Grant permission to call this function
grant execute on function public.handle_user_login to authenticated;
|