SaaS Referral System: Tracking, Reward Logic, Fraud Prevention, and Analytics

A well-designed referral program is the highest-CAC-efficiency growth channel in SaaS. Dropbox grew 3900% with referrals. But the engineering behind it is deceptively complex: attributing conversions correctly, fulfilling rewards without double-paying, and blocking the inevitable fraud attempts. This post covers the complete implementation: database schema, referral link generation, conversion tracking, Stripe credit rewards, fraud rules, and funnel analytics.

System Design

Referrer generates link: /signup?ref=abc123
         │
         ▼
Visitor clicks → cookie set (30-day window) + referral_click recorded
         │
         ▼
Visitor signs up → referral_conversion created (status: pending)
         │
         ▼
Fraud check passes + qualifying event (paid, activated) →
  referral_conversion.status = 'approved'
  reward issued (Stripe credit to referrer + optionally to referee)

---

1. Database Schema

-- Referral codes: one per user (or custom vanity codes)
CREATE TABLE referral_codes (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id   UUID        NOT NULL REFERENCES accounts(id),
  code        TEXT        NOT NULL UNIQUE,
  is_active   BOOLEAN     NOT NULL DEFAULT true,
  
  -- Limits
  max_uses    INTEGER,    -- NULL = unlimited
  uses_count  INTEGER     NOT NULL DEFAULT 0,
  
  -- Custom reward override (NULL = use program defaults)
  referrer_reward_cents  INTEGER,
  referee_reward_cents   INTEGER,
  
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  INDEX idx_referral_codes_user (user_id)
);

-- Track each click on a referral link
CREATE TABLE referral_clicks (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  code_id     UUID        NOT NULL REFERENCES referral_codes(id),
  session_id  TEXT        NOT NULL,   -- Anonymous session identifier
  ip_hash     TEXT,                   -- SHA-256 of IP (for fraud, not stored raw)
  user_agent  TEXT,
  landing_url TEXT,
  clicked_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  INDEX idx_referral_clicks_code (code_id, clicked_at DESC),
  INDEX idx_referral_clicks_session (session_id)
);

-- Conversion: when a referred user signs up + qualifies
CREATE TABLE referral_conversions (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  code_id         UUID        NOT NULL REFERENCES referral_codes(id),
  referrer_id     UUID        NOT NULL REFERENCES users(id),
  referee_id      UUID        NOT NULL REFERENCES users(id),
  
  status          TEXT        NOT NULL DEFAULT 'pending'
                              CHECK (status IN ('pending', 'approved', 'paid', 'rejected', 'expired')),
  
  -- Qualifying event that triggers approval
  qualifying_event TEXT,      -- 'first_payment', 'plan_upgrade', 'activation'
  qualifying_event_at TIMESTAMPTZ,
  
  -- Fraud signals
  fraud_score     INTEGER     NOT NULL DEFAULT 0,  -- 0-100
  fraud_flags     TEXT[]      NOT NULL DEFAULT '{}',
  
  -- Rewards
  referrer_reward_cents   INTEGER NOT NULL DEFAULT 0,
  referee_reward_cents    INTEGER NOT NULL DEFAULT 0,
  referrer_reward_issued  BOOLEAN NOT NULL DEFAULT false,
  referee_reward_issued   BOOLEAN NOT NULL DEFAULT false,
  
  -- Stripe credit IDs for idempotency
  referrer_credit_id  TEXT UNIQUE,
  referee_credit_id   TEXT UNIQUE,
  
  approved_at     TIMESTAMPTZ,
  paid_at         TIMESTAMPTZ,
  rejected_at     TIMESTAMPTZ,
  rejection_reason TEXT,
  
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  UNIQUE (referee_id),     -- One conversion per referee
  INDEX idx_conversions_referrer (referrer_id, status),
  INDEX idx_conversions_pending (status, created_at) WHERE status = 'pending'
);

---

2. Referral Code Generation and Link Tracking

// src/services/referral/codes.ts
import { nanoid } from 'nanoid';
import { db } from '../../lib/db';

const CODE_LENGTH = 8; // 8 chars = ~281 trillion combinations

export async function getOrCreateReferralCode(userId: string, tenantId: string): Promise<string> {
  const existing = await db.referralCode.findFirst({
    where: { userId, isActive: true },
    select: { code: true },
  });

  if (existing) return existing.code;

  // Retry on collision (nanoid collisions are extremely rare but possible)
  for (let attempt = 0; attempt < 5; attempt++) {
    const code = nanoid(CODE_LENGTH).toLowerCase();

    try {
      await db.referralCode.create({
        data: { userId, tenantId, code },
      });
      return code;
    } catch (err: any) {
      if (err.code !== 'P2002') throw err; // Retry only on unique constraint
    }
  }

  throw new Error('Failed to generate unique referral code after 5 attempts');
}

export function buildReferralUrl(code: string, baseUrl: string): string {
  return `${baseUrl}/signup?ref=${code}`;
}

// Track a click when someone visits a referral link
export async function trackReferralClick(
  code: string,
  sessionId: string,
  ip: string,
  userAgent: string,
  landingUrl: string
): Promise<void> {
  const referralCode = await db.referralCode.findUnique({
    where: { code, isActive: true },
  });

  if (!referralCode) return; // Invalid or inactive code — ignore silently

  // Hash IP for fraud signals (don't store raw IPs)
  const ipHash = require('crypto')
    .createHash('sha256')
    .update(ip + process.env.IP_HASH_SALT)
    .digest('hex')
    .slice(0, 16); // First 16 chars is sufficient

  await db.referralClick.create({
    data: {
      codeId: referralCode.id,
      sessionId,
      ipHash,
      userAgent,
      landingUrl,
    },
  });
}

---

3. Conversion Attribution

// src/services/referral/attribution.ts
import { db } from '../../lib/db';

// Called at signup — attribute referral from cookie/query param
export async function attributeReferral(
  refereeId: string,
  referralCode: string,
  sessionId: string
): Promise<void> {
  const code = await db.referralCode.findUnique({
    where: { code: referralCode, isActive: true },
    include: { user: true },
  });

  if (!code) return;

  // Can't refer yourself
  if (code.userId === refereeId) return;

  // Check max uses
  if (code.maxUses && code.usesCount >= code.maxUses) return;

  // Get fraud score for this signup
  const fraudScore = await computeFraudScore(refereeId, code, sessionId);
  const fraudFlags = await getFraudFlags(refereeId, code, sessionId);

  // Determine rewards
  const referrerReward = code.referrerRewardCents ?? PROGRAM_DEFAULTS.referrerRewardCents;
  const refereeReward = code.refereeRewardCents ?? PROGRAM_DEFAULTS.refereeRewardCents;

  await db.$transaction([
    db.referralConversion.create({
      data: {
        codeId: code.id,
        referrerId: code.userId,
        refereeId,
        fraudScore,
        fraudFlags,
        referrerRewardCents: referrerReward,
        refereeRewardCents: refereeReward,
      },
    }),
    db.referralCode.update({
      where: { id: code.id },
      data: { usesCount: { increment: 1 } },
    }),
  ]);
}

const PROGRAM_DEFAULTS = {
  referrerRewardCents: 2000,  // $20 credit
  refereeRewardCents: 1000,   // $10 credit
  qualifyingEvent: 'first_payment',
  fraudScoreThreshold: 60,    // Reject if score >= 60
};

---

4. Fraud Prevention

// src/services/referral/fraud.ts
import { db } from '../../lib/db';
import crypto from 'crypto';

export async function computeFraudScore(
  refereeId: string,
  code: ReferralCode & { user: User },
  sessionId: string
): Promise<number> {
  let score = 0;
  const flags: string[] = [];

  const [referee, clicksFromSession, recentConversionsFromReferrer] =
    await Promise.all([
      db.user.findUnique({
        where: { id: refereeId },
        select: { createdAt: true, email: true },
      }),
      db.referralClick.count({
        where: { sessionId, codeId: code.id },
      }),
      db.referralConversion.count({
        where: {
          referrerId: code.userId,
          createdAt: { gte: new Date(Date.now() - 7 * 86_400_000) },
        },
      }),
    ]);

  if (!referee) return 100; // Can't find referee — reject

  // Signal: same session clicked and signed up (same browser)
  // This is actually GOOD — score stays low

  // Signal: multiple conversions from same referrer in 7 days
  if (recentConversionsFromReferrer > 10) {
    score += 20;
    flags.push('high_volume_referrer');
  }

  // Signal: referrer and referee share email domain (potential self-referral)
  const referrerDomain = code.user.email.split('@')[1];
  const refereeDomain = referee.email.split('@')[1];
  if (referrerDomain === refereeDomain && !CORPORATE_DOMAIN_EXCEPTIONS.has(referrerDomain)) {
    score += 25;
    flags.push('same_email_domain');
  }

  // Signal: disposable email address
  if (DISPOSABLE_EMAIL_DOMAINS.has(refereeDomain)) {
    score += 40;
    flags.push('disposable_email');
  }

  // Signal: account created within 5 minutes of click (bot-like speed)
  const lastClick = await db.referralClick.findFirst({
    where: { sessionId, codeId: code.id },
    orderBy: { clickedAt: 'desc' },
  });

  if (lastClick) {
    const signupDelay = referee.createdAt.getTime() - lastClick.clickedAt.getTime();
    if (signupDelay < 60_000) { // < 1 minute
      score += 30;
      flags.push('instant_signup');
    }
  }

  // Update flags in DB
  await db.referralConversion.updateMany({
    where: { refereeId, status: 'pending' },
    data: { fraudFlags: flags },
  });

  return Math.min(100, score);
}

const DISPOSABLE_EMAIL_DOMAINS = new Set([
  'mailinator.com', 'guerrillamail.com', 'tempmail.com',
  'throwaway.email', 'yopmail.com', '10minutemail.com',
]);

const CORPORATE_DOMAIN_EXCEPTIONS = new Set([
  'gmail.com', 'yahoo.com', 'outlook.com', 'hotmail.com',
]);

---

5. Reward Fulfillment with Stripe

// src/services/referral/rewards.ts
import { stripe } from '../../lib/stripe';
import { db } from '../../lib/db';

// Called when qualifying event occurs (e.g., first payment succeeded)
export async function approveAndRewardReferral(
  refereeId: string,
  qualifyingEvent: string
): Promise<void> {
  const conversion = await db.referralConversion.findUnique({
    where: { refereeId },
    include: {
      referrer: { include: { account: { include: { subscription: true } } } },
      referee: { include: { account: { include: { subscription: true } } } },
    },
  });

  if (!conversion || conversion.status !== 'pending') return;

  // Reject high-fraud conversions
  if (conversion.fraudScore >= PROGRAM_DEFAULTS.fraudScoreThreshold) {
    await db.referralConversion.update({
      where: { id: conversion.id },
      data: {
        status: 'rejected',
        rejectedAt: new Date(),
        rejectionReason: `Fraud score too high: ${conversion.fraudScore}`,
      },
    });
    return;
  }

  // Approve and issue rewards
  await db.referralConversion.update({
    where: { id: conversion.id },
    data: {
      status: 'approved',
      approvedAt: new Date(),
      qualifyingEvent,
      qualifyingEventAt: new Date(),
    },
  });

  // Issue Stripe credits (idempotent via unique credit ID)
  await Promise.all([
    issueStripeCredit(
      conversion.referrer.account.subscription?.stripeCustomerId,
      conversion.referrerRewardCents,
      conversion.id,
      'referrer',
      `Referral reward: ${conversion.referee.email} signed up`
    ),
    issueStripeCredit(
      conversion.referee.account.subscription?.stripeCustomerId,
      conversion.refereeRewardCents,
      conversion.id,
      'referee',
      'Welcome credit from referral program'
    ),
  ]);

  await db.referralConversion.update({
    where: { id: conversion.id },
    data: {
      status: 'paid',
      paidAt: new Date(),
      referrerRewardIssued: true,
      refereeRewardIssued: true,
    },
  });
}

async function issueStripeCredit(
  stripeCustomerId: string | null | undefined,
  amountCents: number,
  conversionId: string,
  role: 'referrer' | 'referee',
  description: string
): Promise<void> {
  if (!stripeCustomerId || amountCents <= 0) return;

  // Idempotency key prevents duplicate credits on retry
  await stripe.customers.createBalanceTransaction(stripeCustomerId, {
    amount: -amountCents, // Negative = credit
    currency: 'usd',
    description,
    metadata: { conversionId, role },
  });
}

---

6. Referral Analytics

-- Referral funnel: clicks → signups → paid conversions
SELECT
  DATE_TRUNC('week', rc.clicked_at) AS week,
  COUNT(DISTINCT rc.id) AS total_clicks,
  COUNT(DISTINCT conv.id) AS signups,
  COUNT(DISTINCT conv.id) FILTER (WHERE conv.status IN ('approved', 'paid')) AS conversions,
  COUNT(DISTINCT conv.id) FILTER (WHERE conv.status = 'paid') AS paid_conversions,
  ROUND(
    100.0 * COUNT(DISTINCT conv.id) FILTER (WHERE conv.status = 'paid') /
    NULLIF(COUNT(DISTINCT rc.id), 0), 1
  ) AS click_to_paid_pct,
  SUM(conv.referrer_reward_cents) FILTER (WHERE conv.status = 'paid') / 100.0 AS rewards_paid_usd
FROM referral_clicks rc
LEFT JOIN referral_conversions conv ON rc.code_id = conv.code_id
  AND conv.created_at >= rc.clicked_at
  AND conv.created_at <= rc.clicked_at + INTERVAL '30 days'
WHERE rc.clicked_at >= NOW() - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1 DESC;

-- Top referrers by paid conversions
SELECT
  u.email AS referrer_email,
  COUNT(*) FILTER (WHERE conv.status = 'paid') AS paid_referrals,
  SUM(conv.referrer_reward_cents) FILTER (WHERE conv.status = 'paid') / 100.0 AS total_rewards_usd,
  AVG(conv.fraud_score) AS avg_fraud_score
FROM referral_codes rc
JOIN users u ON rc.user_id = u.id
JOIN referral_conversions conv ON rc.id = conv.code_id
GROUP BY u.id, u.email
HAVING COUNT(*) FILTER (WHERE conv.status = 'paid') > 0
ORDER BY paid_referrals DESC
LIMIT 20;

---

Cost Reference

Benchmark: A well-implemented referral program typically produces 15–30% of new signups at 20–40% of paid acquisition CAC.

---

See Also

• SaaS Trial Conversion: Onboarding Sequences and Feature Gates
• SaaS Email Sequences: Transactional System and Drip Campaigns
• Stripe Webhook Handling: Signature Verification and Idempotency
• SaaS Metrics Benchmarks: ARR, Churn, NRR, and CAC
• Product Analytics Engineering: Tracking, Funnels, and Retention

---

Working With Viprasol

Building a referral program that needs to handle attribution correctly, prevent fraud, and reward referrers reliably via Stripe? We design and implement end-to-end referral systems — from link generation and conversion tracking to fraud scoring and automated reward fulfillment — integrated with your existing billing infrastructure.

Talk to our team → | Explore our SaaS engineering services →