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

> Source: <https://dev.to/koda2026/m-12-my-referral-system-slept-for-3-days-heres-the-sql-trigger-that-woke-it-up-4375>
> Published: 2026-08-25 13:17:52+00:00

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.

| username | referred_by | referral_count | is_ambassador |
|---|---|---|---|
| me (founder) | NULL | 0 | false |
| my brother | NULL | 0 | false |
| my 2 friends | NULL | 0 | false |
| 4 strangers | NULL | 0 | 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.

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.*