Stop free AI credits from leaking in Supabase and Next.js apps

An app that gives every new account free AI credits tends to leak them in four places.

None of these needs a clever attacker. A loop in the browser console is enough for the second one. This post closes all four with one route handler and one SQL migration. It works for a plain Next.js app on Supabase, and for a Lovable project, since Lovable apps run on Supabase too and the SQL goes into the same SQL editor.

lib/email-key.mjs normalise the address into a claim key db/schema.sql claim table, credits, daily counter, triggers app/api/generate/handler.mjs the route logic, testable without Next app/api/generate/route.js wires it to Supabase test/ node --test suites Enter fullscreen mode Exit fullscreen mode 1. One key per inbox Gmail ignores dots in the local part, drops everything after a +, and treats googlemail.com as gmail.com. So the claim key collapses those. Every other domain keeps its dots and tags, because plenty of mail servers treat them as separate mailboxes.

// lib/email-key.mjs const GMAIL = new Set ([ " gmail.com ", " googlemail.com " ]); export function emailKey ( raw ) { const email = String ( raw?? "" ). trim (). toLowerCase (); const at = email. lastIndexOf ( " @ " ); if ( at < 1 || at === email. length - 1 ) return null; let local = email. slice ( 0, at ); let domain = email. slice ( at + 1 ); if ( GMAIL. has ( domain )) { local = local. split ( " + " )[ 0 ]. replaceAll ( ". ", "" ); domain = " gmail.com "; } return local? ` ${ local } @ ${ domain } `: null; } Enter fullscreen mode Exit fullscreen mode The stored email stays exactly as typed. Only the claim key is normalised.

Run this in the Supabase SQL editor. It does four things:

-- db/schema.sql create or replace function public. email_key ( raw text ) returns text language sql immutable as $$ with p as ( select lower ( trim ( raw )) as e ), s as ( select split_part ( e, '@', 1 ) as local, substr ( e, length ( split_part ( e, '@', 1 )) + 2 ) as domain from p where e like '_%@_%' ) select case when domain in ( 'gmail.com', 'googlemail.com' ) then nullif ( replace ( split_part ( local, '+', 1 ), '.', '' ), '' ) || '@gmail.com' else local || '@' || domain end from s $$; create table if not exists public. trial_claims ( email_key text primary key, user_id uuid not null unique, claimed_at timestamptz not null default now () ); create table if not exists public. credits ( user_id uuid primary key, balance integer not null default 0 check ( balance >= 0 ) ); create table if not exists public. usage_daily ( user_id uuid not null, day date not null default current_date, calls integer not null default 0, primary key ( user_id, day ) ); -- Runs when Auth flips email_confirmed_at from null to a timestamp. create or replace function public. grant_trial_on_confirm () returns trigger language plpgsql security definer set search_path = public as $$ declare k text: = public. email_key ( new. email ); begin if k is null then return new; end if; insert into public. trial_claims ( email_key, user_id ) values ( k, new. id ) on conflict do nothing; if found then insert into public. credits ( user_id, balance ) values ( new. id, 50 ) on conflict ( user_id ) do nothing; end if; return new; end $$; drop trigger if exists grant_trial_on_confirm on auth. users; create trigger grant_trial_on_confirm after update of email_confirmed_at on auth. users for each row when ( old. email_confirmed_at is null and new. email_confirmed_at is not null ) execute function public. grant_trial_on_confirm (); -- Google sign in arrives already confirmed, so it never hits the update path. drop trigger if exists grant_trial_on_confirmed_insert on auth. users; create trigger grant_trial_on_confirmed_insert after insert on auth. users for each row when ( new. email_confirmed_at is not null ) execute function public. grant_trial_on_confirm (); -- One call: under the daily cap and has a credit, or nothing changes. -- The user id comes from the caller's JWT, never from the request body. create or replace function public. spend_credit ( daily_cap integer ) returns integer language plpgsql security definer set search_path = public as $$ declare uid uuid: = auth. uid (); used integer; left_over integer; begin if uid is null then raise exception 'not_signed_in' using errcode = 'P0001'; end if; insert into public. usage_daily as u ( user_id, day, calls ) values ( uid, current_date, 1 ) on conflict ( user_id, day ) do update set calls = u. calls + 1 where u. calls < daily_cap returning calls into used; if used is null then return - 1; -- daily cap reached end if; update public. credits set balance = balance - 1 where user_id = uid and balance > 0 returning balance into left_over; if left_over is null then raise exception 'no_credits' using errcode = 'P0001'; end if; return left_over; end $$; revoke all on function public. spend_credit ( integer ) from public, anon; grant execute on function public. spend_credit ( integer ) to authenticated; alter table public. trial_claims enable row level security; alter table public. credits enable row level security; alter table public. usage_daily enable row level security; create policy "read own credits" on public. credits for select to authenticated using ( user_id = auth. uid ()); Enter fullscreen mode Exit fullscreen mode A few details worth calling out.

on conflict do nothing followed by if found is what makes the grant safe under load. Twenty confirmations of the same inbox at once still produce exactly one credits row, and the test below runs that case.

The daily counter uses on conflict... do update... where u.calls < daily_cap. When the cap is reached the where fails, no row comes back, and the function returns -1 without touching the balance.

If the counter goes up but the balance is empty, the function raises. Postgres rolls the whole call back, so a failed request doesn't eat a slot from the daily cap.

The three tables have row level security on with no write policies, so the browser can read its own balance and nothing else. All writes go through the triggers and spend_credit.

The logic lives in a small factory so it can be tested without booting Next. It checks the session first, refuses unconfirmed accounts, validates the prompt, spends a credit, and only then calls the model. The order matters: every early return happens before any quota is touched.

// app/api/generate/route.js import { createClient } from " @/utils/supabase/server "; import { makeHandler } from "./handler.mjs "; import { callModel } from " @/lib/model "; export const POST = makeHandler ({ async getUser () { const supabase = await createClient (); const { data, error } = await supabase. auth. getUser (); return error? null: data. user; }, async spendCredit ( userId, dailyCap ) { const supabase = await createClient (); // spend_credit reads the user id from the session JWT. const { data, error } = await supabase. rpc ( " spend_credit ", { daily_cap: dailyCap, }); if ( error ) throw new Error ( error. message ); return data; }, callModel, }); Enter fullscreen mode Exit fullscreen mode Because spend_credit runs with the caller's JWT, the RPC goes through the user's own session. No service role key is needed in this route, which is one less secret that could end up in a client bundle.

Two suites. The handler suite uses fakes and runs anywhere. The database suite runs against a local Postgres when DATABASE_URL is set and skips otherwise. Locally it needs a tiny stand in for what Supabase already ships: an auth.users table, auth.uid(), and the anon and authenticated roles.

-- Minimal stand-in for what Supabase already provides. Not part of the article's migration. create schema if not exists auth; create table if not exists auth. users ( id uuid primary key default gen_random_uuid (), email text, email_confirmed_at timestamptz ); create or replace function auth. uid () returns uuid language sql stable as $$ select nullif ( current_setting ( 'request.jwt.claim.sub', true ), '' ):: uuid $$; do $$ begin if not exists ( select from pg_roles where rolname = 'anon' ) then create role anon; end if; if not exists ( select from pg_roles where rolname = 'authenticated' ) then create role authenticated; end if; end $$; Enter fullscreen mode Exit fullscreen mode The anon and authenticated roles need a superuser to create, so on a plain local Postgres make them once by hand before the first run.

// test/handler.test.mjs import { test } from " node:test "; import assert from " node:assert/strict "; import { makeHandler } from "../app/api/generate/handler.mjs "; const req = ( body ) => new Request ( " http://x/api/generate ", { method: " POST ", body: JSON. stringify ( body ) }); function setup ({ user, balance = 2, cap = 3 }) { const calls = { model: 0, used: 0 }; const POST = makeHandler ({ dailyCap: cap, getUser: async () => user, spendCredit: async ( _id, dailyCap ) => { if ( calls. used >= dailyCap ) return - 1; if ( balance === 0 ) throw new Error ( " no_credits " ); calls. used ++; return -- balance; }, callModel: async ( p ) => { calls. model ++; return `echo: ${ p } `; }, }); return { POST, calls }; } const confirmed = { id: " u1 ", email_confirmed_at: " 2026-10-20T10:00:00Z " }; test ( " no session: 401 and the model is never called ", async () => { const { POST, calls } = setup ({ user: null }); assert. equal (( await POST ( req ({ prompt: " hi " }))). status, 401 ); assert. equal ( calls. model, 0 ); }); test ( " unconfirmed email: 403 ", async () => { const { POST, calls } = setup ({ user: { id: " u1 ", email_confirmed_at: null } }); assert. equal (( await POST ( req ({ prompt: " hi " }))). status, 403 ); assert. equal ( calls. model, 0 ); }); test ( " spends credits, then 402 when they run out ", async () => { const { POST, calls } = setup ({ user: confirmed, balance: 2, cap: 10 }); const a = await POST ( req ({ prompt: " hi " })); assert. equal ( a. status, 200 ); assert. equal (( await a. json ()). credits_left, 1 ); assert. equal (( await POST ( req ({ prompt: " hi " }))). status, 200 ); assert. equal (( await POST ( req ({ prompt: " hi " }))). status, 402 ); assert. equal ( calls. model, 2 ); }); test ( " daily cap: 429 even with credits left ", async () => { const { POST, calls } = setup ({ user: confirmed, balance: 100, cap: 2 }); await POST ( req ({ prompt: " a " })); await POST ( req ({ prompt: " b " })); assert. equal (( await POST ( req ({ prompt: " c " }))). status, 429 ); assert. equal ( calls. model, 2 ); }); test ( " empty or oversized prompt: 400 before any credit is spent ", async () => { const { POST, calls } = setup ({ user: confirmed }); assert. equal (( await POST ( req ({ prompt: " " }))). status, 400 ); assert. equal (( await POST ( req ({ prompt: " x ". repeat ( 4001 ) }))). status, 400 ); assert. equal ( calls. used, 0 ); }); Enter fullscreen mode Exit fullscreen mode // test/db.test.mjs import { test, before, after } from " node:test "; import assert from " node:assert/strict "; import { readFileSync } from " node:fs "; import pg from " pg "; import { emailKey } from "../lib/email-key.mjs "; const url = process. env. DATABASE_URL; const pool = url? new pg. Pool ({ connectionString: url }): null; const opts = { skip:! url && " set DATABASE_URL to run " }; before ( async () => { if (! pool ) return; await pool. query ( " drop schema if exists auth cascade; drop table if exists public.trial_claims, public.credits, public.usage_daily cascade " ); await pool. query ( readFileSync ( new URL ( "./supabase-stub.sql ", import. meta. url ), " utf8 " )); await pool. query ( readFileSync ( new URL ( "../db/schema.sql ", import. meta. url ), " utf8 " )); }); after (() => pool?. end ()); async function signUp ( email ) { const { rows } = await pool. query ( " insert into auth.users (email) values ($1) returning id ", [ email ]); return rows [ 0 ]. id; } const confirm = ( id ) => pool. query ( " update auth.users set email_confirmed_at = now() where id = $1 ", [ id ]); const balance = async ( id ) => ( await pool. query ( " select balance from public.credits where user_id = $1 ", [ id ])). rows [ 0 ]?. balance?? null; async function spendAs ( id, cap ) { const c = await pool. connect (); try { await c. query ( " select set_config('request.jwt.claim.sub', $1, false) ", [ id ]); return ( await c. query ( " select public.spend_credit($1) as left ", [ cap ])). rows [ 0 ]. left; } finally { await c. query ( " reset all " ); c. release (); } } test ( " SQL email_key matches the JS one ", opts, async () => { for ( const e of [ " Jane.Doe+2@GoogleMail.com ", " Jane.Doe+a@Example.com ", " nope ", " +x@gmail.com " ]) { const { rows } = await pool. query ( " select public.email_key($1) k ", [ e ]); assert. equal ( rows [ 0 ]. k, emailKey ( e ), e ); } }); test ( " no credits until the email is confirmed ", opts, async () => { const id = await signUp ( " first.person@gmail.com " ); assert. equal ( await balance ( id ), null ); await confirm ( id ); assert. equal ( await balance ( id ), 50 ); await confirm ( id ); // second update must not grant again assert. equal ( await balance ( id ), 50 ); }); test ( " a second Gmail spelling gets an account but no credits ", opts, async () => { const id = await signUp ( " FirstPerson+again@googlemail.com " ); await confirm ( id ); assert. equal ( await balance ( id ), null ); }); test ( " Google sign in (confirmed on insert) gets credits once per inbox ", opts, async () => { const ins = " insert into auth.users (email, email_confirmed_at) values ($1, now()) returning id "; const a = ( await pool. query ( ins, [ " oauth.person@gmail.com " ])). rows [ 0 ]. id; const b = ( await pool. query ( ins, [ " oauthperson+2@gmail.com " ])). rows [ 0 ]. id; assert. equal ( await balance ( a ), 50 ); assert. equal ( await balance ( b ), null ); }); test ( " daily cap and balance are enforced in one call ", opts, async () => { const id = await signUp ( " capped@example.com " ); await confirm ( id ); assert. equal ( await spendAs ( id, 2 ), 49 ); assert. equal ( await spendAs ( id, 2 ), 48 ); assert. equal ( await spendAs ( id, 2 ), - 1 ); assert. equal ( await balance ( id ), 48 ); }); test ( " out of credits raises and does not count the call ", opts, async () => { const id = await signUp ( " empty@example.com " ); await confirm ( id ); await pool. query ( " update public.credits set balance = 0 where user_id = $1 ", [ id ]); await assert. rejects ( spendAs ( id, 5 ), /no_credits/ ); const { rows } = await pool. query ( " select calls from public.usage_daily where user_id = $1 ", [ id ]); assert. equal ( rows. length, 0 ); }); test ( " no session: spend_credit refuses ", opts, async () => { await assert. rejects ( pool. query ( " select public.spend_credit(5) " ), /not_signed_in/ ); }); test ( " 20 parallel confirmations of one inbox grant credits once ", opts, async () => { const ids = []; for ( let i = 0; i < 20; i ++ ) ids. push ( await signUp ( `race.case+ ${ i } @gmail.com` )); await Promise. all ( ids. map (( id ) => confirm ( id ))); const { rows } = await pool. query ( " select count(*)::int n from public.credits where user_id = any($1::uuid[]) ", [ ids ]); assert. equal ( rows [ 0 ]. n, 1 ); }); test ( " 50 parallel spends against 10 credits never go below zero ", opts, async () => { const id = await signUp ( " burst@example.com " ); await confirm ( id ); await pool. query ( " update public.credits set balance = 10 where user_id = $1 ", [ id ]); const results = await Promise. allSettled ( Array. from ({ length: 50 }, () => spendAs ( id, 1000 ))); assert. equal ( results. filter (( r ) => r. status === " fulfilled " ). length, 10 ); assert. equal ( await balance ( id ), 0 ); }); Enter fullscreen mode Exit fullscreen mode // test/email-key.test.mjs import { test } from " node:test "; import assert from " node:assert/strict "; import { emailKey } from "../lib/email-key.mjs "; test ( " gmail dots, plus tags and googlemail collapse to one key ", () => { for ( const e of [ " jane.doe@gmail.com ", " JaneDoe+2@Gmail.com ", " j.a.n.e.doe+x@googlemail.com " ]) { assert. equal ( emailKey ( e ), " janedoe@gmail.com " ); } }); test ( " other domains keep dots and plus tags ", () => { assert. equal ( emailKey ( " Jane.Doe+a@Example.com " ), " jane.doe+a@example.com " ); }); test ( " junk returns null ", () => { for ( const e of [ "", " nope ", " @gmail.com ", " a@ ", " +x@gmail.com ", null ]) { assert. equal ( emailKey ( e ), null ); } }); Enter fullscreen mode Exit fullscreen mode With "type": "module" in package.json and pg installed, on Node 20.19 and PostgreSQL 17.11:

$ DATABASE_URL=postgres://trial:trial@localhost/trial node --test --test-reporter=spec ✔ SQL email_key matches the JS one ✔ no credits until the email is confirmed ✔ a second Gmail spelling gets an account but no credits ✔ Google sign in (confirmed on insert) gets credits once per inbox ✔ daily cap and balance are enforced in one call ✔ out of credits raises and does not count the call ✔ no session: spend_credit refuses ✔ 20 parallel confirmations of one inbox grant credits once ✔ 50 parallel spends against 10 credits never go below zero ✔ gmail dots, plus tags and googlemail collapse to one key ✔ other domains keep dots and plus tags ✔ junk returns null ✔ no session: 401 and the model is never called ✔ unconfirmed email: 403 ✔ spends credits, then 402 when they run out ✔ daily cap: 429 even with credits left ✔ empty or oversized prompt: 400 before any credit is spent ℹ tests 17 ℹ pass 17 ℹ fail 0 ℹ skipped 0 Enter fullscreen mode Exit fullscreen mode Without DATABASE_URL the eight handler and normaliser tests pass and the nine database tests are skipped.

A unique key per inbox stops the same Gmail address from coming back in a new spelling. It does nothing about someone who owns ten separate Google accounts, or a script that rotates through fresh domains. For that you need signals from outside the email string: the IP the signup came from, whether the domain was registered last week, whether a phone number belongs to a reserved test range. The daily cap also limits damage per account, not per person, so pairing it with a per IP limit on the signup form is a sensible next step.

I built Orisift for the parts this doesn't cover; there's a free tier. orisift.com