1 files changed, 63 insertions, 0 deletions
diff --git a/makima/migrations/20250110000007_create_auth_trigger.sql b/makima/migrations/20250110000007_create_auth_trigger.sql
new file mode 100644
index 0000000..3b3fec9
--- /dev/null
+++ b/makima/migrations/20250110000007_create_auth_trigger.sql
@@ -0,0 +1,63 @@
+-- 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';