The step 6 hook works, but I'd change how it finds the duplicate before relying on it. Wrapping auth.users.email in public.normalise_email() means Postgres can't use the email index, so every signup scans the whole users table. Supabase gives Postgres hooks 2 seconds, so past some user count the check starts timing out and blocking real signups along with the repeats.
It also has a race. Two signups for Your.Name+a and Your.Name+b sent at the same moment both run the exists query before either user row is committed, and both get through.
A table you own fixes both. Give public.signup_keys the normalised email as its primary key, and have the hook run insert ... on conflict do nothing and refuse when no row went in. The primary key is the index, and a second insert of the same key waits for the first and then conflicts. The trade is that a key can stay behind if account creation fails after the hook, so someone needs a way to clear it. It's your trial_claims idea from step 4, moved one step earlier.
The step 6 hook works, but I'd change how it finds the duplicate before relying on it. Wrapping auth.users.email in public.normalise_email() means Postgres can't use the email index, so every signup scans the whole users table. Supabase gives Postgres hooks 2 seconds, so past some user count the check starts timing out and blocking real signups along with the repeats.
It also has a race. Two signups for Your.Name+a and Your.Name+b sent at the same moment both run the exists query before either user row is committed, and both get through.
A table you own fixes both. Give public.signup_keys the normalised email as its primary key, and have the hook run insert ... on conflict do nothing and refuse when no row went in. The primary key is the index, and a second insert of the same key waits for the first and then conflicts. The trade is that a key can stay behind if account creation fails after the hook, so someone needs a way to clear it. It's your trial_claims idea from step 4, moved one step earlier.