Supabase auth plus profiles onboarding: the trigger that creates a profile per user

Auth plus profiles: one trigger, no orphan users

Every app needs a public profile row per auth user. Doing it in application code means every signup path (password, OAuth, magic link, admin-created, invited) must remember to create it. They will not. A trigger on auth.users makes it structural.

Checkable procedure

1. Create public.profiles with id uuid primary key references auth.users(id) on delete cascade. The foreign key with cascade means deleting the auth user cleans up the profile automatically.
2. Write a trigger function that inserts into public.profiles (id, plus defaults like a display name from the user's metadata) on after insert on auth.users, for each row.
3. Attach the trigger in a migration. Test all signup paths: password, OAuth, magic link, and admin-created users. Each must produce exactly one profile row.
4. Put user-editable fields in profiles, never in auth.users metadata beyond the initial copy. App code updates profiles with RLS (auth.uid() = id); it never writes auth.users directly.
5. Backfill existing users once with a single insert-select for users missing profiles, then rely on the trigger going forward.

Ordering constraints

The profiles table and trigger must exist before the first real user signs up. On an existing project, backfill first, then add the trigger, then verify counts match.

Verification

Sign up via each method and confirm exactly one profile row appears within the same transaction. Delete a test auth user and confirm the profile cascades away.

Verified against live Supabase docs 2026-09-26: https://supabase.com/docs/guides/database/postgres/row-level-security, https://supabase.com/docs/guides/auth/managing-user-data