Referral rewards people actually open

The first version of the Zenthos Gym referral programme worked perfectly and nobody noticed it.

Bring three friends onto a monthly plan and you get seven free days. When the third friend paid, the days were quietly added to your membership. Technically correct. But members never saw the moment they earned it, so they never talked about it, and a referral programme nobody talks about does not bring in referrals.

So I turned the reward into a moment: a gift that waits in the app until the member shakes their phone to unwrap it. This is how it is built underneath.

Rewards are earned when money moves

A referral only counts when the invited friend pays for a plan that qualifies. That check happens inside the same database function that confirms a payment, so there is no separate job that could miss one or count one twice:

if pl.counts_for_referral and p.referred_by is not null then
  select * into ref from referrals where referred_id = p.id;
  if ref.id is not null and ref.qualified_at is null then
    update referrals set qualified_at = now() where id = ref.id;

qualified_at is null makes it a one-time event. If the same payment were somehow confirmed twice, the second pass would find the referral already qualified and do nothing.

The days wait in a gift

When the member reaches the gym’s target, the free days are not added. They are put in a gift:

if qualified_count >= s.referral_target then
  -- the free days wait in a gift the member opens by shaking their phone
  insert into referral_gifts (user_id, days) values (ref.referrer_id, s.referral_reward_days);
  update referrals set rewarded_at = now()
   where referrer_id = ref.referrer_id and qualified_at is not null and rewarded_at is null;

Both numbers, how many friends and how many days, come from the gym’s own settings, so each gym can run its own offer. Marking those referrals as rewarded in the same transaction means they can never be counted towards a second gift.

The in-between moments matter too

A member who is one friend away should know it. So every qualifying payment that does not complete the set still sends a notification, built from real names and numbers:

'That is ' || qualified_count || ' of ' || s.referral_target || '. ' ||
short_by || ' more and you get ' || s.referral_reward_days || ' free days.'

“Tolu joined on your link. That is 2 of 3. 1 more and you get 7 free days” does more for the next referral than any banner.

Opening it, exactly once

When the member opens the app with a gift waiting, a sheet slides up asking them to shake their phone, with a “tap to open” fallback for anyone who would rather not. Opening it calls one function:

update referral_gifts set opened_at = now()
 where id = p_gift and user_id = auth.uid() and opened_at is null
returning * into gift;
if gift.id is null then return 0; end if;
perform public.grant_days(gift.user_id, gift.days);
return gift.days;

Three conditions sit in that one where: it must be this gift, it must belong to the person asking, and it must still be unopened. A double tap, a second shake or a replayed request all find opened_at already set, update nothing and grant nothing. Only the first one returns the days.

Members can read their own gifts, but there is no permission to change them directly. The only way to open one is through this function.

What I took from it

• A reward nobody sees is a reward nobody shares. Design the moment, not just the rule.
• Count rewards where money is confirmed, in the same transaction. That is the only place the truth lives.
• Make every grant conditional on its own state. is null checks turn retries into no-ops.
• Put the business’s numbers in settings. The same code runs a different offer for every gym.
• Write notifications with real names and real progress. People respond to “2 of 3”, not “keep going”.