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

#ai #webdev #supabase #beginners

I'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.

The scoreboard that made me suspicious

username	referred_by	is_ambassador
me (founder)	NULL	false
my brother	NULL	false
my 2 friends	NULL	false
4 strangers	NULL	false

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.

Detective work: Bug #1 — the 10-minute trap

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) { ... }

Detective work: Bug #2 — nobody ever rewarded the referrer

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.

And because I batch deploys now...

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

The 48-hour referral fix (client)
The SQL trigger (server-side — zero deploys, just the SQL editor)
An "Install KODA" home-screen button so the app lives on phones like a real app
Sensei Brain v2 — my AI now ends every lesson with a Codewars kata challenge. Codewars ranks kata from 8 kyu (white belt) to 1 kyu (master belt), and you solve them with TDD test cases right in the browser — perfect homework for my users. The Sensei also teaches fresh tricks like the Python docs' self-debugging f-string: 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.

Lessons from the plateau

Read your own database like a story. The column of NULLs was the plot twist.
Silent failure is still failure. No error + empty table = check your assumptions, not your syntax.
Triggers beat client code for cross-user logic.
Batch your deploys. Your nerves will thank you.

What's next

Test the loop end-to-end with a fresh account and watch my own row turn Gold
Paper wireframes before the next UI redesign (a dev-tools team told me to try Balsamiq — I'll report back)
Turn 8 users into 80

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.