supabase_trigger.sql

-- This script creates a trigger that automatically syncs the user's auth ID
-- to your public.users table after a new user signs up.
-- It is idempotent and can be run multiple times safely.

-- 0. Drop existing trigger and function if they exist
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
DROP FUNCTION IF EXISTS public.handle_new_user();

-- 1. Create the function
-- This function will be triggered after a new user is inserted into auth.users.
-- It takes the newly created user's ID and email, finds the corresponding
-- record in public.users based on the email, and updates the auth_user_id.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  -- Update the auth_user_id in the public.users table
  update public.users
  set auth_user_id = new.id
  where email = new.email;
  return new;
end;
$$;

-- 2. Create the trigger
-- This trigger will fire the handle_new_user function every time
-- a new record is added to the auth.users table.
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();