'm 12. My referral system slept for 3 days — here's the SQL trigger that woke it up

Quick recap if you're new: I'm Harun, 12 years old, no laptop. I build KODA — an AI coding mentor — entirely on a POCO C55 Android phone. Vanilla JS + Supabase + Groq + Netlify.

This week I opened my database expecting a growth story. I found a mystery instead.

8 users. Zero referrals. Zero ambassadors. The "Invite a friend, unlock Gold Mode" loop I was so proud of? It had never fired. Not once.

No error messages. No red banners. Just... silence. Silent failures are the worst failures.

My client code only attached a referral if the account was less than 10 minutes old when the user logged in:

if (ref !== me.id && ageMs < 10 * 60 * 1000) {
  await db.from('profiles').update({ referred_by: ref })
    .eq('id', me.id).is('referred_by', null);
}

But my signup flow says: "check your inbox, tap the confirmation link, THEN sign in."

Humans don't do that in 10 minutes. They confirm their email hours later. By then ageMs

is huge and the referral dies silently. Every single time.

Fix: a 48-hour window.

if (ref !== me.id && ageMs < 48 * 60 * 60 * 1000) { ... }

Here's the embarrassing part. The client wrote referred_by

on the NEW user... and that was it. Nothing anywhere incremented the referrer's referral_count

or flipped is_ambassador = true

.

And it can't be done from the client anyway — Row Level Security (good security!) blocks users from editing other people's rows.

Fix: move the logic into the database. A security definer

trigger that powers up the referrer automatically:

create or replace function public.bump_referrer()
returns trigger language plpgsql security definer
set search_path = public as $$
begin
  if new.referred_by is not null
     and new.referred_by <> new.id
     and (old is null or old.referred_by is null) then
    update public.profiles
    set referral_count = coalesce(referral_count, 0) + 1,
        is_ambassador = true
    where id = new.referred_by;
  end if;
  return new;
end; $$;

drop trigger if exists trg_bump_referrer on public.profiles;
create trigger trg_bump_referrer
after insert or update of referred_by on public.profiles
for each row execute function public.bump_referrer();

Now when a referral lands, the database itself grants the Gold. No client code, no RLS fights. When RLS says "no," move the logic to the database.

One deploy = one bundle. This bundle (v9) shipped with:

print(f'{bugs=} {count=}')

— yes, on Python 3.14, the current "pi version."Why batch? Because every deploy costs attention and risk on a phone. Ship in bundles, test once, celebrate once.

If you're a senior dev: roast my trigger in the comments. I read every single word.

Built with ❤️ by Harun (age 12) on a POCO C55.