# Affiliate Program Playbook **Portable spec for shipping a self-serve affiliate / referral program inside a SaaS app: code generation, attribution, commission calc, payouts, dashboard, fraud detection, and creative library.** Canonical pattern drawn from Rewardful / PartnerStack / Tolt / FirstPromoter. BKE itself does not implement a full affiliate system yet; this playbook is the reference for any new app in the operator's portfolio (Kompozy, Cold Email Engine, Cold SMS, Cold DM, Cold WA/Telegram, Giveaway, Lead Scraper, AI Cold Caller) that adds one. Each portfolio app runs its own standalone affiliate system. There is **no federated affiliate identity** across apps — that's a hard portfolio rule (see memory: `moe_10_app_portfolio_strategy.md`). Cross-promotion happens at the marketing layer (a "Built by " landing page that links to each program), not at the data layer. --- ## 0. Use this spec **Inputs:** - `STRIPE_SECRET_KEY` + `STRIPE_WEBHOOK_SECRET` from the app's Stripe account - Stripe Connect platform enabled (recommended) OR a manual payout pipeline (Wise / PayPal / ACH) - Default commission tier (recommend 30% recurring for first 12 months, or 20% lifetime) - Attribution model (default: **first-click within 30 days**, locked at signup) - Refund clearance window (default: 30 days; commissions stay `pending` until the window passes) - Minimum payout threshold (default: $50) **Outputs:** - `/affiliate/signup`, `/affiliate/dashboard`, `/affiliate/assets` routes - `?ref=` URL capture middleware - Stripe `invoice.paid` + `charge.refunded` webhook handlers - Monthly payout batch job (cron) - Fraud detection ruleset (self-referral, IP clustering, click-stuffing, refund-rate) - Affiliate creative-asset library (banners, swipe copy, video clips) --- ## 1. Architecture ``` src/ ├── proxy.ts # Capture ?ref= on any public route, │ # set affiliate_ref cookie (30d), insert click row │ ├── lib/ │ ├── affiliate/ │ │ ├── code-gen.ts # 6-char alphanumeric vanity codes │ │ ├── attribution.ts # first/last/split-touchpoint resolver │ │ ├── commission.ts # gross → commission_amount math │ │ ├── fraud.ts # rule evaluator (self-ref, IP, velocity) │ │ └── payouts.ts # batch builder + Stripe Connect transfer │ └── stripe/ │ └── webhooks.ts # invoice.paid + charge.refunded handlers │ ├── app/ │ ├── affiliate/ │ │ ├── signup/page.tsx # Apply to program │ │ ├── dashboard/page.tsx # Clicks, signups, commissions, payouts │ │ └── assets/page.tsx # Creative library │ ├── admin/ │ │ └── affiliates/page.tsx # Approve, suspend, override commission % │ └── api/ │ ├── affiliate/ │ │ ├── apply/route.ts # POST signup │ │ └── stats/route.ts # GET dashboard data │ └── webhooks/ │ └── stripe/route.ts # invoice.paid → insert commission row ``` --- ## 2. Schema ```sql create table affiliates ( id uuid primary key default gen_random_uuid(), profile_id uuid not null references profiles(id) on delete cascade, code text not null unique, commission_pct numeric(5,2) not null default 30.00, payout_email text, stripe_connect_account text, status text not null default 'pending' check (status in ('pending','active','paused','banned')), created_at timestamptz default now() ); create index on affiliates (code); create table affiliate_clicks ( id uuid primary key default gen_random_uuid(), affiliate_id uuid not null references affiliates(id) on delete cascade, visitor_id text not null, landing_path text, referrer text, utm_source text, utm_campaign text, ip_hash text, country text, created_at timestamptz default now() ); create index on affiliate_clicks (visitor_id, created_at desc); create index on affiliate_clicks (affiliate_id, created_at desc); create table affiliate_attributions ( id uuid primary key default gen_random_uuid(), affiliate_id uuid not null references affiliates(id) on delete cascade, profile_id uuid not null references profiles(id) on delete cascade unique, first_click_at timestamptz not null, signup_at timestamptz not null, attribution_window_days int not null default 30, status text not null default 'active' check (status in ('active','reversed','fraud_review')) ); create table affiliate_commissions ( id uuid primary key default gen_random_uuid(), attribution_id uuid not null references affiliate_attributions(id) on delete cascade, stripe_invoice_id text not null unique, gross_amount numeric(12,2) not null, commission_amount numeric(12,2) not null, status text not null default 'pending' check (status in ('pending','cleared','payable','paid','refunded','void')), payout_at timestamptz, refunded_at timestamptz, created_at timestamptz default now() ); create index on affiliate_commissions (status, created_at); create table affiliate_payouts ( id uuid primary key default gen_random_uuid(), affiliate_id uuid not null references affiliates(id) on delete cascade, total_amount numeric(12,2) not null, stripe_transfer_id text, paid_at timestamptz default now() ); -- RLS: affiliates read their own rows; admins read all. alter table affiliates enable row level security; create policy "affiliate reads self" on affiliates for select using (profile_id = auth.uid()); ``` --- ## 3. Code generation 6-character alphanumeric, vanity-friendly. Reject confusable characters (`0OoIl1`) and a profanity list. Allow custom-vanity request on admin approval. ```ts const ALPHABET = 'ABCDEFGHJKMNPQRSTUVWXYZ23456789'; function genCode(): string { return Array.from({ length: 6 }, () => ALPHABET[Math.floor(Math.random() * ALPHABET.length)] ).join(''); } // Insert with retry on unique-violation. Cap at 5 attempts before falling back to 7 chars. ``` URL shape: `https://app.com/?ref=ABCD23` — never put the code in the path, querystring is the convention every link-shortener and analytics tool understands. --- ## 4. Click capture In `proxy.ts` (cross-ref `AUTH-ONBOARDING-PLAYBOOK §3`): 1. Read `?ref=` from the request URL on any public route. 2. Validate code exists + affiliate.status = 'active'. If not, ignore silently. 3. Set `affiliate_ref` cookie, 30-day expiry, `SameSite=Lax`, `HttpOnly=false` so client-side analytics can read it. 4. Resolve `visitor_id` from the analytics cookie (cross-ref `CUSTOMER-ANALYTICS-PLAYBOOK`). If no visitor cookie yet, mint one before the affiliate cookie. 5. Insert `affiliate_clicks` row (fire-and-forget, do not block the response). Hash the IP with a per-day salt — never store raw IPs. **Do not** insert a click row on every page navigation — only on the inbound link-click (first request carrying `?ref=`). Idempotency key: `(visitor_id, affiliate_id, hour_bucket)`. --- ## 5. Attribution **Default: first-click within 30 days, locked at signup.** Resolver runs once on `auth.users` insert via the same trigger pattern as profile auto-create: ```sql create function handle_affiliate_attribution() returns trigger language plpgsql security definer set search_path = public as $$ declare v_visitor_id text; v_click affiliate_clicks%rowtype; begin -- Pull visitor_id from new profile (set during signup form submit) select visitor_id into v_visitor_id from profiles where id = new.id; if v_visitor_id is null then return new; end if; -- First click in last 30 days wins select * into v_click from affiliate_clicks where visitor_id = v_visitor_id and created_at > now() - interval '30 days' order by created_at asc limit 1; if v_click.id is null then return new; end if; insert into affiliate_attributions (affiliate_id, profile_id, first_click_at, signup_at) values (v_click.affiliate_id, new.id, v_click.created_at, now()) on conflict (profile_id) do nothing; return new; end; $$; ``` **Configurable per app:** - `first-click` (default — rewards top-of-funnel content creators) - `last-click` (rewards bottom-of-funnel reviewers / coupon sites) - `split-by-touchpoint` (split commission across N affiliates pro-rata; needs schema extension to `affiliate_commissions.split_pct`) --- ## 6. Commission calculation Triggered by Stripe `invoice.paid` webhook: ```ts // app/api/webhooks/stripe/route.ts case 'invoice.paid': { const invoice = event.data.object; const profileId = await profileIdFromCustomer(invoice.customer); const attribution = await db .from('affiliate_attributions') .select('id, affiliate_id, affiliates(commission_pct)') .eq('profile_id', profileId) .eq('status', 'active') .maybeSingle(); if (!attribution.data) break; const gross = invoice.amount_paid / 100; const pct = attribution.data.affiliates.commission_pct; const commission = Math.round(gross * pct) / 100; await db.from('affiliate_commissions').insert({ attribution_id: attribution.data.id, stripe_invoice_id: invoice.id, gross_amount: gross, commission_amount: commission, status: 'pending', }); break; } ``` **Refund handling** — `charge.refunded` webhook: ```ts case 'charge.refunded': { const charge = event.data.object; await db.from('affiliate_commissions') .update({ status: 'refunded', refunded_at: new Date().toISOString() }) .eq('stripe_invoice_id', charge.invoice); break; } ``` **Clearance cron** (daily): move `pending` → `cleared` once `created_at + 30 days < now()` AND `refunded_at is null`. Move `cleared` → `payable` when affiliate hits payout threshold. --- ## 7. Payouts **Stripe Connect (recommended).** Affiliates onboard via Stripe Express. KYC + 1099 / W-9 / W-8BEN handled by Stripe. Monthly batch (cron, 1st of month): ```ts for (const affiliate of activeAffiliates) { const payable = await db .from('affiliate_commissions') .select('id, commission_amount') .eq('status', 'payable') .in('attribution_id', affiliate.attribution_ids); const total = sum(payable.map(p => p.commission_amount)); if (total < MIN_PAYOUT_USD) continue; const transfer = await stripe.transfers.create({ amount: Math.round(total * 100), currency: 'usd', destination: affiliate.stripe_connect_account, metadata: { affiliate_id: affiliate.id }, }); await db.from('affiliate_payouts').insert({ affiliate_id: affiliate.id, total_amount: total, stripe_transfer_id: transfer.id, }); await db.from('affiliate_commissions') .update({ status: 'paid', payout_at: new Date().toISOString() }) .in('id', payable.map(p => p.id)); } ``` **Manual fallback** for the first ~10 affiliates: pay via Wise / PayPal, mark `stripe_transfer_id = null`, log payout reference in metadata. --- ## 8. Affiliate dashboard Route: `/affiliate/dashboard`. Sections: - **Overview tiles:** clicks (30d), signups (30d), attributed MRR, pending commission, payable commission, lifetime paid - **Share links + QR codes:** primary `?ref=` link, deep links to pricing / signup, downloadable QR - **Performance table:** date range filter, per-link breakdown, EPC (earnings per click) - **Commissions ledger:** every row from `affiliate_commissions` with status badge - **Payout history:** every `affiliate_payouts` row + next scheduled payout date - **Creative library link** (see §9) All queries scoped via RLS — the dashboard cannot leak visitor_id or any cross-affiliate data. --- ## 9. Creative asset library Route: `/affiliate/assets`. Hosted in-app, version-controlled in repo or Supabase Storage `affiliate-assets` bucket. Asset types: - **Email templates** — 3-7 plain-text swipes, copy-paste with `{{REF_LINK}}` token auto-substituted to the affiliate's link - **Banner ads** — 300x250, 728x90, 160x600 with the affiliate's code burned in via on-the-fly Sharp/Canvas render - **Social swipe copy** — Twitter/X, LinkedIn, IG caption variants - **Video assets** — 30s / 60s product demo clips affiliates can re-upload to their channels - **Brand kit** — logo, color palette, do/don't usage guidelines Versioning: every asset has a `version` field; old links keep working but UI surfaces "new version available" to the affiliate. --- ## 10. Fraud detection Run nightly + on every new attribution. Auto-pause affiliate (status='paused', not 'banned' — reversible) and notify admin. | Signal | Threshold | Action | |---|---|---| | Self-referral by IP hash | attribution.ip_hash matches signup ip_hash | pause + flag attribution `fraud_review` | | Self-referral by email | affiliate.payout_email or profile.email matches attributed signup email | pause | | Self-referral by payment method | Stripe `fingerprint` matches affiliate's own card/bank | pause | | IP clustering | >10 signups from same ip_hash in 24h attributed to one affiliate | pause + bulk fraud_review | | Click-stuffing | >50% of clicks have inter-click delta <1s, OR >1000 clicks/day from one visitor_id | pause | | Refund rate | affiliate's 30-day refund rate >40% (min 10 attributed sales) | pause | | Velocity anomaly | sudden 10x WoW spike in attributed signups with <5% conversion to paid | flag for manual review | Reversal: when attribution moves to `reversed`, all linked commissions cascade to `void`. --- ## 11. Common pitfalls **❌ Don't allow affiliates to refer themselves.** Match on IP hash, email, AND Stripe payment method fingerprint. Any single one is bypassable; all three together is the floor. **❌ Don't pay commission on refunded purchases.** Wire `charge.refunded` → `affiliate_commissions.status = 'refunded'`. A 30-day clearance window between `pending` and `payable` exists precisely so refunds catch before payout. **❌ Don't use Stripe coupon codes as affiliate codes.** Two different systems. Coupons modify what the customer pays; affiliate codes modify who gets credit. Coupling them confuses the customer ("do I get a discount?") and tangles your reporting. Keep them separate. If you want a discount-as-incentive, attach a separate coupon to the affiliate's link via a second querystring param. **❌ Don't make `commission_pct` a free-text field per affiliate.** Define plan-tier defaults (e.g. 30% standard, 40% influencer tier) + an admin-override capability gated by the admin role. Free-text invites typos that pay 300% commissions. **❌ Don't pay out without identity verification.** Stripe Connect KYC handles this for US + supported countries. For manual payouts, collect W-9 (US) / W-8BEN (international) before the first dollar leaves your account. Tax liability is yours, not the affiliate's. **❌ Don't reset attribution on every Stripe webhook.** Attribution is sticky from the moment of first signup. Subsequent invoices roll up under the same `attribution_id`. Re-running attribution on every `invoice.paid` is how affiliates get silently re-attributed to whoever clicked most recently. **❌ Don't expose visitor_id ↔ affiliate_id linkage to the public API.** That linkage is an abuse vector: a competitor scraping it can poison your attribution by injecting click rows. Dashboard reads aggregate counts only; raw click rows stay admin-scoped. **❌ Don't ship a federated cross-portfolio affiliate ID.** Each app in the 10-app portfolio runs its own affiliates table per `moe_10_app_portfolio_strategy.md`. Cross-promo lives on the marketing site, not in shared infrastructure. --- ## 12. Kickoff prompt > Execute the Affiliate Program Playbook at `AFFILIATE-PROGRAM-PLAYBOOK.md` for this app. > > Inputs: > - STRIPE_SECRET_KEY + STRIPE_WEBHOOK_SECRET: [provided in .env] > - Stripe Connect platform: [enabled / not yet — start with manual payouts] > - Default commission tier: [e.g. 30% recurring 12 months] > - Attribution model: [first-click | last-click | split] > - Refund clearance window: [30 days default] > - Minimum payout threshold: [$50 default] > > Pause after schema migration + `?ref=` capture + attribution trigger ship for review. --- **Related playbooks:** - `AUTH-ONBOARDING-PLAYBOOK.md` — proxy.ts and profile-creation trigger this builds on - `CUSTOMER-ANALYTICS-PLAYBOOK.md` — visitor_id source-of-truth - `STRIPE-BILLING-PLAYBOOK.md` — invoice.paid + charge.refunded webhook plumbing - `ADMIN-DASHBOARD-PLAYBOOK.md` — admin affiliate-approval + commission-override UI