One trial per inbox in Supabase, enforced by Postgres

If your Supabase app gives new accounts free credits, someone will eventually sign up as jane.doe@gmail.com, spend the credits, then come back as janedoe+2@gmail.com, then j.a.n.e.doe@googlemail.com. Supabase Auth sees three different strings and creates three users. Gmail delivers all three to the same inbox.

You don't have to block those signups. The person may simply have forgotten they had an account. What you want is narrower: the second account works, it just doesn't get a second batch of credits.

This post does that entirely inside Postgres, in one migration. No edge function, no extra service. Everything below ran against PostgreSQL 17 with a small stand in for the auth schema, and the test files are listed at the end.

The plan

1. Turn each email into a claim key, so the three addresses above all become janedoe@gmail.com.

2. Store one row per key in trial_claims, with a unique index on the key.

3. In a trigger on auth.users, try to insert the claim. If the insert lands, grant credits. If it hits the index, grant nothing.

4. Separately, a Before User Created hook in SQL that refuses signups from domains on a throwaway list.

The trigger and the hook do different jobs. The trigger never refuses anyone; it only decides whether credits come with the account. The hook runs earlier and can refuse.

Step 1: the claim key

create or replace function public.email_claim_key(email text)
returns text
language plpgsql
immutable
set search_path = ''
as $$
declare
  addr   text := lower(trim(email));
  at_pos int;
  local  text;
  domain text;
begin
  if addr is null or addr = '' then
    return null;
  end if;
  at_pos := length(addr) - strpos(reverse(addr), '@') + 1;
  if strpos(addr, '@') = 0 then
    return null;
  end if;
  local  := left(addr, at_pos - 1);
  domain := substr(addr, at_pos + 1);

  if domain = 'googlemail.com' then
    domain := 'gmail.com';
  end if;

  -- drop a +tag on every domain
  local := split_part(local, '+', 1);

  -- Gmail ignores dots in the local part
  if domain = 'gmail.com' then
    local := replace(local, '.', '');
  end if;

  if local = '' then
    return null;
  end if;
  return local || '@' || domain;
end;
$$;

The rules, and why each one is there:

Lowercase and trim. Supabase lowercases emails already, but the key should not depend on that.

googlemail.com becomes gmail.com. They are the same mailbox. Google still accepts mail for both.

Drop everything after the first +, on every domain. Gmail, Outlook, iCloud, Fastmail, Proton and most company mail servers treat name+anything as name. A few servers don't, but someone who really has two inboxes that differ only by a +tag is rare, and the cost of a false match here is one account without trial credits, not a lost signup.

Remove dots only for gmail.com. Gmail ignores dots in the local part. Outlook and most others do not, so jane.doe@outlook.com and janedoe@outlook.com stay different keys.

Return null when there's nothing to key on. No @, an empty string, or a local part that was only a tag. Phone signups also land here because email is null. A null key means no claim and no credits, which you can change if phone signups should get a trial.

The function finds the last @ rather than the first, since quoted local parts can legally contain one. It's marked immutable so you can index on it later if you want to look up existing users by key.

Step 2: claims and credits

create table public.trial_claims (
  id         bigint generated always as identity primary key,
  claim_key  text not null,
  user_id    uuid references auth.users (id) on delete set null,
  created_at timestamptz not null default now()
);

create unique index trial_claims_claim_key_idx on public.trial_claims (claim_key);

create table public.credits (
  user_id    uuid primary key references auth.users (id) on delete cascade,
  balance    integer not null default 0 check (balance >= 0),
  updated_at timestamptz not null default now()
);

alter table public.trial_claims enable row level security;
alter table public.credits enable row level security;

create policy "read own credits" on public.credits
  for select to authenticated
  using ((select auth.uid()) = user_id);

Two things in here are deliberate.

The unique index is what enforces the rule. Application code that checks "does a claim exist?" and then inserts has a gap between the check and the insert. Two signups arriving together both see nothing and both get credits. The index closes that gap: the second insert waits for the first transaction and then conflicts. I tested exactly that with two sessions, below.

on delete set null, not cascade. My first version used cascade, and the test showed the obvious hole: delete the account, sign up again with the same address, get fresh credits. With set null the claim outlives the account. The key stays taken and the new user starts at zero.

RLS is on for both tables. Users can read their own balance. Nobody reads trial_claims from the client; the trigger writes it as the function owner.

Step 3: grant on first claim only

create or replace function public.grant_trial_credits()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  key     text := public.email_claim_key(new.email);
  claimed bigint;
begin
  if key is not null then
    insert into public.trial_claims (claim_key, user_id)
    values (key, new.id)
    on conflict (claim_key) do nothing
    returning id into claimed;
  end if;

  insert into public.credits (user_id, balance)
  values (new.id, case when claimed is not null then 100 else 0 end);

  return new;
end;
$$;

create trigger on_auth_user_created_grant_credits
  after insert on auth.users
  for each row execute function public.grant_trial_credits();

on conflict (claim_key) do nothing returning id is the whole trick. When the insert lands, returning hands back the new id. When it conflicts, nothing is returned and claimed stays null. One statement, no separate lookup.

The function is security definer because Supabase Auth inserts into auth.users as supabase_auth_admin, which has no rights on your public tables. set search_path = '' plus fully qualified names keeps the definer function from resolving anything unexpected.

Every user still gets a credits row, at zero when the key was taken. Your app code can then always read one row and never has to handle a missing balance.

Step 4: refuse throwaway domains in the hook

A claim key doesn't help with addresses at disposable inbox providers, because each one is a fresh, unrelated address. That check belongs before the user exists, in a Postgres Before User Created hook.

create table public.disposable_domains (
  domain text primary key check (domain = lower(domain))
);

alter table public.disposable_domains enable row level security;

create or replace function public.hook_before_user_created(event jsonb)
returns jsonb
language plpgsql
stable
set search_path = ''
as $$
declare
  email  text := lower(trim(event -> 'user' ->> 'email'));
  email_domain text;
begin
  if email is null or strpos(email, '@') = 0 then
    return '{}'::jsonb;
  end if;

  email_domain := split_part(email, '@', -1);

  if exists (
    select 1
    from public.disposable_domains d
    where email_domain = d.domain
       or email_domain like '%.' || d.domain
  ) then
    return jsonb_build_object(
      'error', jsonb_build_object(
        'http_code', 400,
        'message', 'Please sign up with a permanent email address.'
      )
    );
  end if;

  return '{}'::jsonb;
end;
$$;

grant usage on schema public to supabase_auth_admin;
grant execute on function public.hook_before_user_created(jsonb) to supabase_auth_admin;
grant select on table public.disposable_domains to supabase_auth_admin;
create policy "auth admin reads domains" on public.disposable_domains
  as permissive for select to supabase_auth_admin using (true);
revoke execute on function public.hook_before_user_created(jsonb) from authenticated, anon, public;

Seed the table with whatever list you trust. There are maintained open source lists on GitHub with a few thousand domains; load one with \copy public.disposable_domains from 'domains.txt' and refresh it from time to time.

The like '%.' || d.domain line catches subdomains such as mx.yopmail.com without matching notmailinator.com, because it requires the dot.

The return shape is what Supabase reads. An empty object lets the signup through. An object with error.http_code and error.message stops it, and the message reaches your client as the error on signUp. Then enable it in the dashboard under Authentication, Hooks, Before User Created, choosing Postgres and public.hook_before_user_created.

The grants at the end matter. Supabase calls the hook as supabase_auth_admin, so that role needs execute on the function and select on the domain table (with an RLS policy, since RLS is on). Revoking execute from anon and authenticated keeps the function off your public API.

In a Next.js app

Nothing changes on the client. A normal supabase.auth.signUp either errors with the hook message or succeeds, and then you read the balance:

const { data: credits } = await supabase
  .from("credits")
  .select("balance")
  .single();

if (credits?.balance === 0) {
  // show "your trial was already used on this email" instead of an empty dashboard
}

Telling the person why they have zero credits saves you a support email.

What I tested

I ran all of this against PostgreSQL 17.11 with a stub auth schema (an auth.users table, auth.uid() and the three Supabase roles). Inserts into auth.users ran as supabase_auth_admin, the same role Supabase Auth uses. 28 checks passed, plus a two session race:

1. Jane.Doe+trial2@Gmail.com, j.a.n.e.d.o.e@googlemail.com and janedoe@gmail.com all produce janedoe@gmail.com.

2. jane.doe+x@outlook.com becomes jane.doe@outlook.com, dots kept.

3. Empty, null, no @, and +onlytag@gmail.com give a null key.

4. Five email signups across two people: the first for each person gets 100 credits, every repeat gets 0, and all six users (one phone only) are still created.

5. A direct duplicate insert into trial_claims raises unique_violation.

6. Deleting an account keeps its claim, and signing up again with the same address gets 0.

7. A signed in user sees one row in credits and none in trial_claims.

8. The hook refuses mailinator.com, refuses Someone@MX.Yopmail.com, allows gmail.com, allows notmailinator.com, and allows a phone signup.

9. anon cannot execute the hook function.

10. Race: one session inserts race.case@gmail.com and holds its transaction for two seconds; a second session inserts racecase+b@googlemail.com meanwhile. The second waits on the index, then gets 0. Final balances: 100 and 0.

Where this stops

This keys on the mailbox, so it stops the cheap repeats: dots, tags, the googlemail spelling, delete and return. It does nothing about someone with ten separate Gmail accounts, and a domain list only knows the domains on it. If you need more than that, card checks at signup or phone confirmation are the next steps, each with its own friction.

Also decide what happens when a user changes their email. The trigger only runs on insert, which is usually what you want: the trial is tied to the address they signed up with.

I built Orisift (https://orisift.com/x?m=post&c=hashnode-supabase-hook&to=%2F), which does this key plus a throwaway domain check in one call.