Decision — Profile Creation via Postgres Trigger

---

What

User profiles are created by a Postgres trigger, not an API route.

When a user signs up (via any auth provider), Supabase inserts a row into auth.users. A trigger on that table automatically calls public.create_profile_for_new_user(), which inserts a corresponding row into public.profiles.

CREATE FUNCTION public.create_profile_for_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, email, created_at)
  VALUES (NEW.id, NEW.email, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.create_profile_for_new_user();

The original plan included a POST /api/auth/profile route for this purpose. That route was dropped.

---

Why the trigger approach

Works for all auth providers automatically. An API route would need to be called explicitly after every sign-up — and separately wired up for every auth provider (email/password, OAuth, magic link, SSO). Forgetting to handle a new provider means users can authenticate but have no profile. The trigger fires unconditionally on every auth.users insert, regardless of provider.

Cannot fail silently after auth succeeds. With an API route, auth could succeed but the profile creation API call could fail (network error, bug, deployment issue), leaving the user in a broken state. The trigger executes in the same database transaction as the auth.users insert — if profile creation fails, the whole operation fails cleanly before the user is considered signed up.

No app-level orchestration. No route handler, no useEffect, no post-sign-up redirect-then-create flow. The database handles it.

Simpler codebase. One less API route. One less thing that can drift or be forgotten when adding a new auth provider.

---

The public. prefix requirement

Trigger functions execute in the schema context of the table that fired the trigger. Since this trigger is on auth.users, unqualified table references inside the function resolve to the auth schema — not public.

Writing INSERT INTO profiles inside the trigger function would look for auth.profiles, which does not exist. The table must be referenced as public.profiles (fully qualified) or the insert will fail.

This is a subtle Postgres behavior that is easy to miss and produces confusing errors. Always use the full public. prefix in trigger functions that reference tables outside the triggering schema.

---

Profile schema

The profiles table lives in packages/core. It contains:

• id — same UUID as auth.users.id (not auto-generated by the profiles table)
• email — copied from auth.users.email at creation time
• createdAt — timestamp set by the trigger

RLS is enabled on profiles. The canonical user-owns-rows policy applies.