How do I fix "Database error saving new user" on Supabase signup?

When someone signs up, Supabase Auth inserts a row into auth.users. Any trigger on that table runs inside the same transaction. So if your profile trigger throws, the whole signup is rolled back and the user only sees a generic "Database error saving new user".

According to Supabase's troubleshooting guide, the common causes are:

• A trigger function with an error. For example, the profiles table doesn't exist, a column was renamed, a NOT NULL column gets no value, or a unique constraint (often username) is violated.
• A constraint you added to auth.users that new rows don't meet.
• Broken permissions on the auth schema, sometimes left behind by migration tools.

AI builders often generate the trigger and the table in separate steps. Then a later edit renames a column and the trigger quietly breaks.

1. Read the real error. In the dashboard, open Logs → Auth and find the failed signup, then check Logs → Postgres around the same time. You'll see something like relation "profiles" does not exist or null value in column "username" violates not-null constraint, together with the function name and line.

2. List the triggers on auth.users:

select tgname, tgfoid::regprocedure as function
from pg_trigger
where tgrelid = 'auth.users'::regclass and not tgisinternal;

3. Look at the function:

select pg_get_functiondef('public.handle_new_user'::regproc);

4. Rewrite it defensively. This follows the pattern in Supabase's user-management guide. Adjust the column names to match your table:

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, full_name, avatar_url)
  values (
    new.id,
    coalesce(new.raw_user_meta_data ->> 'full_name', ''),
    new.raw_user_meta_data ->> 'avatar_url'
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

What each part does:

• security definer runs the function as its owner, so RLS on profiles doesn't block the insert.
• set search_path = '' plus public.profiles means it always finds the right table.
• coalesce(...) gives NOT NULL columns a value even when signup metadata is missing. This matters for OAuth users, who often don't send the fields your form sends.
• on conflict (id) do nothing makes retries harmless.

5. Make sure the trigger exists exactly once:

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

6. If you see converting NULL to string is unsupported, users were inserted with raw SQL rather than through the Auth API. Supabase's fix is to set that column to an empty string where it's NULL, for example update auth.users set confirmation_token = '' where confirmation_token is null;. Then stop inserting users with SQL.

7. Test a signup with email/password and one with your OAuth provider, from a private window on the live site.

Paste this into Lovable, Bolt, Cursor, Claude, ChatGPT, Replit, v0 or whichever tool you build with. Replace the bracketed parts first.

Signup on our app fails with "Database error saving new user". The Postgres log says: [paste the exact error line here].
Please fix only the database trigger:
1. Show me the current trigger(s) on auth.users and the handle_new_user function.
2. Rewrite handle_new_user as security definer with set search_path = '', fully qualified table names (public.profiles), coalesce defaults for every NOT NULL column, and on conflict (id) do nothing.
3. Make sure exactly one trigger calls it after insert on auth.users.
Give me the SQL as a single migration. Don't disable RLS, don't drop tables and don't change the signup UI.

This error tends to come back whenever a migration touches profiles, and nothing in your app will tell you. Signups just stop.

Heedza checks on a schedule that your live signup page loads and shows its email field, and it emails you a screenshot and a fix prompt when it doesn't. It doesn't create test accounts in your database, so a broken trigger behind a working form won't trip it. After any schema change, also do one real signup with a throwaway address. The free scan shows what Heedza checks on your live site.