-- Function to handle new user signup from Supabase Auth
-- This runs when a user is inserted into auth.users
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER -- Run with elevated privileges
SET search_path = public
AS $$
DECLARE
new_owner_id UUID;
new_group_id UUID;
user_name TEXT;
BEGIN
-- Extract display name from metadata, fallback to email
user_name := COALESCE(
NEW.raw_user_meta_data->>'full_name',
NEW.raw_user_meta_data->>'name',
split_part(NEW.email, '@', 1)
);
-- Create personal owner for this user
INSERT INTO public.owners (name, owner_type)
VALUES (user_name, 'personal')
RETURNING id INTO new_owner_id;
-- Create local user record
INSERT INTO public.users (id, email, display_name, default_owner_id)
VALUES (
NEW.id,
NEW.email,
user_name,
new_owner_id
);
-- Create default group for personal owner
INSERT INTO public.groups (owner_id, name, is_default)
VALUES (new_owner_id, 'Personal', true)
RETURNING id INTO new_group_id;
-- Add user as owner of their personal group
INSERT INTO public.group_members (group_id, user_id, role)
VALUES (new_group_id, NEW.id, 'owner');
RETURN NEW;
EXCEPTION WHEN OTHERS THEN
-- Log error but don't fail the signup
RAISE WARNING 'handle_new_user failed for %: %', NEW.id, SQLERRM;
RETURN NEW;
END;
$$;
-- NOTE: The trigger on auth.users must be created in the Supabase dashboard
-- or via supabase CLI because we can't directly access auth schema in migrations.
--
-- Run this SQL in Supabase SQL Editor to create the trigger:
--
-- CREATE TRIGGER on_auth_user_created
-- AFTER INSERT ON auth.users
-- FOR EACH ROW
-- EXECUTE FUNCTION public.handle_new_user();
COMMENT ON FUNCTION handle_new_user IS 'Creates owner, user, group, and membership when a new user signs up via Supabase Auth';
