A creator referral program sounds like a link and a percentage. Once you build one, it turns into four questions: who gets credit for a signup, which payments earn a commission, how do you avoid counting the same payment twice, and what happens when a payment is refunded after you've recorded it?
We're building MotionLab, an AI-personalized stretching app with a Next.js web app on Vercel, an Android app, and Supabase underneath. Subscriptions come from Stripe on the web and Google Play on Android, and creators who share MotionLab earn a commission on subscriptions they bring in. This post is the code behind that: a landing route that stashes the referral, a Postgres function that records it once, a commissions table keyed so each payment lands once, and the jobs that undo commissions after refunds.
Stack: Next.js App Router, @supabase/supabase-js, PL/pgSQL, the stripe SDK and googleapis. Snippets are trimmed from the real code; // ... (or -- ... in SQL) marks cuts. Commission rates, prices and window lengths are left out on purpose: those are business terms.
1. The link: /c/{slug}
Each creator row has a unique slug, and their link is motion-lab.io/c/{slug}: a server component that redirects home if the slug is unknown or inactive:
// src/app/(marketing)/c/[slug]/page.tsx
export async function generateMetadata({ params }: { params: Promise<{ slug: string }> }): Promise<Metadata> {
const { slug } = await params;
const creator = await getCreator(slug);
const title = creator ? `${creator.displayName} recommends MotionLab` : "MotionLab";
return {
title,
// ...
robots: { index: false, follow: true }, // referral landing pages aren't indexed
};
}
export default async function CreatorLandingPage({ params }: { params: Promise<{ slug: string }> }) {
const { slug } = await params;
const creator = await getCreator(slug);
if (!creator) redirect("/");
return <CreatorLandingClient slug={creator.slug} displayName={creator.displayName} />;
}
Creator pages share most of their copy, so they're noindex via the robots field of generateMetadata. The client half stashes the slug in a cookie and localStorage until the visitor signs up, and builds a Play Store link that carries the slug in the referrer parameter:
// CreatorLandingClient.tsx
// The Android Install Referrer API reads this back as: utm_source=motionlab_creator&utm_content={slug}
function playUrl(slug: string): string {
const referrer = encodeURIComponent(`utm_source=motionlab_creator&utm_content=${slug}`);
return `${PLAY_BASE}&referrer=${referrer}`;
}
useEffect(() => {
// ...
document.cookie = `ml_ref=${encodeURIComponent(slug)}; path=/; max-age=${maxAge}; SameSite=Lax`;
try { localStorage.setItem("ml_ref", slug); } catch {}
}, [slug]);
On Android, the Play Install Referrer API returns that string after install; per the migration's comments, the app then calls the same database function as the web, with the user's own JWT.
2. Recording attribution once, inside Postgres
The rules: the first creator to refer a user keeps them, creators can't refer themselves, and two concurrent requests can't both win. Rather than spread those checks across a web route and the Android app, they live in one security definer function with a small string contract:
-- supabase/migrations/0002_referrals.sql
-- Returns: 'set' | 'already' | 'self' | 'invalid' | 'no_profile'
create or replace function public.set_referral_attribution(
p_user_id uuid, p_creator_slug text, p_source text
) returns text
language plpgsql security definer set search_path = public as $$
declare
v_creator public.creators%rowtype;
v_uid uuid;
-- ...
begin
if auth.uid() is not null then
v_uid := auth.uid(); -- signed-in client: p_user_id is ignored
elsif coalesce(auth.role(), '') = 'service_role' then
v_uid := p_user_id; -- our server route, which verified the user
else
return 'invalid';
end if;
select * into v_creator from public.creators where slug = p_creator_slug and active;
if not found then return 'invalid'; end if;
if v_creator.user_id is not null and v_creator.user_id = v_uid then
return 'self';
end if;
select * into v_profile from public.user_profiles where id = v_uid;
if not found then return 'no_profile'; end if; -- caller may retry later
if v_profile.referred_by_creator_id is not null then return 'already'; end if;
update public.user_profiles
set referred_by_creator_id = v_creator.id,
referred_at = now(),
referral_source = p_source
where id = v_uid
and referred_by_creator_id is null; -- first-touch lock (race-safe)
return 'set';
end $$;
The select checks give readable answers. The where ... referred_by_creator_id is null on the update is what enforces first-touch: if two calls race past the select, only one update matches. The set search_path matters too; the Postgres docs on writing SECURITY DEFINER functions safely explain how a definer function without a fixed search path can be tricked into using an object the caller created.
Two more things matter for a definer function in an exposed schema. Postgres grants EXECUTE to PUBLIC on every new function, so a grant ... to authenticated alone doesn't keep anyone out; you have to revoke it. And a function that accepts a user id should only trust that argument from a trusted caller. Our first version fell back to p_user_id whenever auth.uid() was null, which is also true for an anonymous request. A later migration fixed both:
revoke execute on function public.set_referral_attribution(uuid, text, text) from public, anon;
grant execute on function public.set_referral_attribution(uuid, text, text) to authenticated, service_role;
On the web, a route handler verifies the caller's Supabase access token and passes the verified user id along:
// src/app/api/referral/attribute/route.ts
const supabase = createServerClient();
const { data: { user }, error } = await supabase.auth.getUser(token);
if (error || !user) return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
// ...
const { data, error: rpcError } = await supabase.rpc("set_referral_attribution", {
p_user_id: user.id,
p_creator_slug: slug,
p_source: "web",
});
// ...
return NextResponse.json({ result: data }); // 'set' | 'already' | 'self' | 'invalid' | 'no_profile'
The browser helper runs from onboarding once a user exists, and is safe to call repeatedly. It clears the stash on any definitive answer and keeps it when the request failed or the profile isn't there yet:
// src/lib/referral/client.ts
const res = await fetch("/api/referral/attribute", { /* ... Bearer token, { slug } */ });
if (!res.ok) return; // keep the stash for a later retry
const { result } = (await res.json()) as { result?: string };
if (result && result !== "no_profile") clearRef();
no_profile means the user's user_profiles row doesn't exist yet. Returning a status instead of failing lets the client retry on a later mount rather than lose the referral.
The tables creators, referral_commissions and referral_payouts have row-level security enabled and no policies. Per Supabase's RLS guide, that leaves nothing readable through the API with the publishable key, while the service role our server code uses bypasses RLS. The same guide warns that policies don't remove grants, so audit both.
3. One commission row per payment
One unique constraint does most of the work:
create table if not exists public.referral_commissions (
id uuid primary key default gen_random_uuid(),
creator_id uuid not null references public.creators(id) on delete cascade,
user_id uuid not null references auth.users(id) on delete cascade,
source text not null, -- 'stripe' | 'play'
external_payment_id text not null, -- stripe invoice id / play order id
commission_cents int not null,
status text not null default 'pending', -- pending | paid | reversed
-- ...
unique (source, external_payment_id) -- idempotency
);
All inserts go through one helper that turns a duplicate into a no-op:
// src/lib/referral/commissions.ts
await supabase
.from("referral_commissions")
.upsert({ status: "pending", ...row }, { onConflict: "source,external_payment_id", ignoreDuplicates: true });
With ignoreDuplicates, supabase-js's upsert acts like Postgres's INSERT ... ON CONFLICT DO NOTHING. Every caller here can repeat: Stripe's webhook docs say endpoints "might occasionally receive the same event more than once", and the Play side re-runs on a schedule on purpose. With the payment id as the key, doing the same work twice can't create a second commission.
Stripe is the easy store. The webhook's invoice.payment_succeeded handler, which also sees renewals, calls:
export async function recordStripeCommission(supabase: DB, uid: string, invoice: Stripe.Invoice) {
const amountPaid = invoice.amount_paid ?? 0;
if (amountPaid <= 0) return; // skip $0 / fully-discounted / trial invoices
if (!invoice.id) return;
const creator = await creatorForUser(supabase, uid);
if (!creator) return;
await insertCommission(supabase, {
// ...
source: "stripe",
external_payment_id: invoice.id,
gross_amount_cents: amountPaid,
commission_cents: commissionCents(amountPaid, creator.commissionPercent),
});
}
The amount is what Stripe says was paid, and the key is the invoice id.
4. Google Play: order ids and a daily catch-up
Play is harder. Our server only hears about a renewal when the app syncs or a notification arrives, and the code comment is blunt that the v2 API "does not return the localized paid amount cleanly", so Play commissions use an estimated gross from a configured price map. (Stripe never uses that map.)
The Play key is the order id. Each renewal gets a new one, so one row per order id means one row per payment:
export async function recordPlayCommissionForUser(supabase: DB, uid: string, purchaseToken: string, interval: "month" | "year") {
const creator = await creatorForUser(supabase, uid);
if (!creator) return; // avoid hitting the Play API for unattributed users
const publisher = playPublisher();
const { data } = await publisher.purchases.subscriptionsv2.get({
packageName: PLAY_PACKAGE_NAME,
token: purchaseToken,
});
// latestOrderId is deprecated and can name a pending or declined renewal.
const latestOrderId = data.lineItems?.[0]?.latestSuccessfulOrderId;
if (!latestOrderId) return;
// ... insertCommission({ source: "play", external_payment_id: latestOrderId, ... })
}
Our first version read data.latestOrderId. Google has deprecated latestOrderId; for auto-renewing plans it's the last recurring order, "success, pending, or declined". Its replacement in the current resource, lineItems[].latestSuccessfulOrderId, only names successful orders, which is what a commission should hang on, so that's what we key on now.
It's called from our shared Play sync, which runs when the app syncs a purchase, when a Play notification arrives, and in a nightly billing reconcile (only in ACTIVE or CANCELED-not-yet-expired states, so a grace period earns nothing). A separate daily cron sweeps referred Play subscribers as a backstop:
// src/app/api/referral/reconcile-play/route.ts
export async function GET(request: Request) {
const auth = request.headers.get("authorization");
if (!process.env.CRON_SECRET || auth !== `Bearer ${process.env.CRON_SECRET}`) {
return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
}
// ... reconcileAllPlayCommissions(supabase)
}
That's the pattern from Vercel's cron docs, which also say delivery is best effort and a run can occasionally fire twice. The unique key absorbs duplicates, and since each run rechecks the current order id instead of "new since last time", a missed run is caught by the next one.
5. Undoing commissions
In this code path, refunds and chargebacks don't cut access. They reverse the commission, and only while it's still pending. The Stripe webhook resolves the invoice id from charge.refunded or charge.dispute.created, then:
export async function reverseStripeCommissionByInvoiceId(supabase: DB, invoiceId: string) {
await supabase
.from("referral_commissions")
.update({ status: "reversed", reversed_at: new Date().toISOString() })
.eq("source", "stripe")
.eq("external_payment_id", invoiceId)
.eq("status", "pending");
}
Paid commissions are left for manual handling rather than silently clawed back.
For Play, the cron asks the Voided Purchases API for voided orders and reverses matching pending commissions. Two details on that page matter. The default type=0 returns only in-app purchases; subscriptions need type=1. Our first version passed only the package name and never saw a subscription refund; it now passes type: 1 and follows nextPageToken. And the API covers only the past 30 days, so a job that stops for a while can miss voids for good.
6. The holding period and payouts
Refunds can arrive days after a payment, so a commission isn't payable right away. A view sums commissions that are pending, not reversed and older than a holding window (redacted here):
create or replace view public.referral_payable as
select c.creator_id, cr.slug, cr.display_name, c.currency,
count(*) as commission_count, sum(c.commission_cents) as payable_cents
from public.referral_commissions c
join public.creators cr on cr.id = c.creator_id
where c.status = 'pending'
and c.reversed_at is null
and c.created_at < now() - interval '...'
group by -- ...
Views in Postgres run with their owner's rights by default, so they skip the RLS on the tables underneath. Supabase's RLS guide covers this. A view in public that joins creator rows is readable through the API if the client roles have select on it, so ours runs as the caller and only the service role can read it:
alter view public.referral_payable set (security_invoker = true);
revoke all on public.referral_payable from public, anon, authenticated;
Settling a creator is an owner-only route: select the exact rows payable now, stamp them paid with a YYYY-MM batch label, and write a referral_payouts audit row. Updating by the selected ids means a commission that ages into the window mid-request isn't swept into a batch total it wasn't counted in.
What we'd keep, and what we'd change
- Put attribution rules in one database function that every client calls. The race-safe part is a
WHEREclause. Revoke the defaultEXECUTE, and only trust a user-id argument from the service role. - Make views over private tables
security_invoker, or keep client roles off them. - Key money rows on the store's payment id and make every writer ignore duplicates, so webhooks, syncs and crons can overlap.
- Reverse only what hasn't been paid, and hold commissions long enough for refunds to land.
- On Play, key on
latestSuccessfulOrderId, ask the Voided Purchases API for subscriptions explicitly, and treat the estimated amount as an estimate.
First-touch is a product choice as much as a technical one: easy to reason about and not gameable by re-clicking a link, but it never credits a second creator who actually closed the sale. The creator side of the program is on our partnerships page.
How does your referral code handle attribution and refunds: first-touch or last-touch, and what do you key commissions on? Tell us in the comments.
This post was drafted with AI assistance and reviewed by the MotionLab team.
Top comments (1)
Nice touch returning 'self' from the function instead of failing quietly. One gap worth closing: that check only catches a creator using their own account. A creator can sign up a second account with another email through their own link and subscribe, and the commission turns into a discount. Since you already hold commissions until refunds can land, you could flag them before payout when the referred Stripe payment's card fingerprint matches a card the creator has paid with, or when the referred email normalises to the creator's (Gmail dots and plus tags stripped). I'd flag for review rather than block, because partners and coworkers do share cards.