supabase_schema.sql

-- Create public.users table to track credits and associate with auth.users
create table public.users (
  id uuid references auth.users on delete cascade not null primary key,
  email text,
  credits integer default 2 not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Turn on Row Level Security (RLS)
alter table public.users enable row level security;

-- Policy so users can view (only) their own row.
-- Deliberately NO update policy: RLS cannot restrict columns, so a
-- self-update policy would let users set their own credits via PostgREST.
-- All writes to this table go through the backend's service role key.
create policy "Users can view own profile" on public.users
  for select using (auth.uid() = id);

-- Function to handle new user creation
create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.users (id, email, credits)
  values (new.id, new.email, 2);
  return new;
end;
$$ language plpgsql security definer;

-- Trigger to call the function right after the auth.users insert
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- Function to safely increment user credits (Stripe Webhook)
create or replace function public.increment_credit(user_uuid uuid, amount int) 
returns int as $$
declare
  current_credits int;
begin
  update public.users
  set credits = credits + amount
  where id = user_uuid
  returning credits into current_credits;

  return current_credits;
end;
$$ language plpgsql security definer;