How to Design an Effective Referral Reward System: A Complete Technical Guide for SaaS

For a $29/month SaaS:
  - Referrer reward: $7.25 - $14.50 in credit (1-2 weeks free)
  - Referee reward: $7.25 - $14.50 in credit

For a $99/month SaaS:
  - Referrer reward: $25 - $50 in credit
  - Referee reward: $25 - $50 in credit

-- Table 1: Invite codes (generated by referrers)
CREATE TABLE invite_codes (
  id TEXT PRIMARY KEY,
  code TEXT NOT NULL UNIQUE, -- e.g., 'FRIEND-ABC123'
  creator_id TEXT NOT NULL, -- The user who created this code
  max_uses INTEGER DEFAULT 10,
  use_count INTEGER NOT NULL DEFAULT 0,
  reward_type TEXT NOT NULL DEFAULT 'credit' CHECK (
    reward_type IN ('credit', 'discount_percent', 'discount_fixed', 'month_free')
  ),
  reward_value INTEGER NOT NULL DEFAULT 500, -- In cents or percentage points
  is_active INTEGER NOT NULL DEFAULT 1,
  expires_at INTEGER, -- Optional expiration
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  FOREIGN KEY (creator_id) REFERENCES users(id)
);

CREATE INDEX idx_invite_codes_code ON invite_codes(code);
CREATE INDEX idx_invite_codes_creator ON invite_codes(creator_id);

-- Table 2: Redemption records
CREATE TABLE invite_redemptions (
  id TEXT PRIMARY KEY,
  invite_code_id TEXT NOT NULL,
  referrer_id TEXT NOT NULL, -- The person who shared the code
  referred_user_id TEXT NOT NULL, -- The new user who used the code
  reward_referrer INTEGER NOT NULL DEFAULT 0, -- Cents awarded to referrer
  reward_referred INTEGER NOT NULL DEFAULT 0, -- Cents awarded to referee
  status TEXT NOT NULL DEFAULT 'pending' CHECK (
    status IN ('pending', 'completed', 'expired', 'fraudulent')
  ),
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  completed_at INTEGER, -- When conditions were met (e.g., referee pays)
  FOREIGN KEY (invite_code_id) REFERENCES invite_codes(id),
  FOREIGN KEY (referrer_id) REFERENCES users(id),
  FOREIGN KEY (referred_user_id) REFERENCES users(id)
);

CREATE INDEX idx_invite_redemptions_referrer ON invite_redemptions(referrer_id);
CREATE INDEX idx_invite_redemptions_referred ON invite_redemptions(referred_user_id);

-- Table 3: Credit ledger (for credit-based rewards)
CREATE TABLE credit_ledger (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  amount_cents INTEGER NOT NULL, -- Positive for credit, negative for spend
  balance_after_cents INTEGER NOT NULL,
  reason TEXT NOT NULL CHECK (
    reason IN (
      'referral_reward', 'referral_signup_bonus',
      'credit_purchase', 'subscription_payment',
      'admin_adjustment', 'expired'
    )
  ),
  reference_id TEXT, -- Links to invite_redemptions or invoice
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_credit_ledger_user ON credit_ledger(user_id);

-- View: Referral statistics per user
CREATE VIEW referral_stats AS
SELECT
  ic.creator_id as user_id,
  COUNT(DISTINCT ir.id) as total_referrals,
  COUNT(DISTINCT CASE WHEN ir.status = 'completed' THEN ir.id END) as completed_referrals,
  COALESCE(SUM(CASE WHEN ir.status = 'completed' THEN ir.reward_referrer END), 0) as total_rewards_earned,
  ROUND(AVG(CASE WHEN ir.status = 'completed' THEN ir.reward_referrer END), 0) as avg_reward_per_referral
FROM invite_codes ic
LEFT JOIN invite_redemptions ir ON ir.invite_code_id = ic.id
GROUP BY ic.creator_id;

// src/lib/invite/codes.ts
import { createServerFn } from "@tanstack/react-start"

// Secure, human-friendly invite code generation
export function generateInviteCode(length: number = 8): string {
  // Use a character set that avoids ambiguous characters
  const chars = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789" // No I, O, 0, 1
  const prefix = "FRIEND-"
  const random = Array.from({ length }, () =>
    chars[Math.floor(Math.random() * chars.length)]
  ).join("")
  return `${prefix}${random}`
}

export const createInviteCode = createServerFn({ method: "POST" }).handler(
  async (_, { request }) => {
    const userId = await getUserId(request)
    const code = generateInviteCode()

    // Ensure uniqueness (collision probability is negligible but check anyway)
    const existing = await env.DB.prepare(
      "SELECT id FROM invite_codes WHERE code = ?"
    ).bind(code).first()

    if (existing) {
      return createInviteCode(_, { request }) // Retry
    }

    // Default reward: 500 cents ($5.00) account credit for both parties
    await env.DB.prepare(
      `INSERT INTO invite_codes (id, code, creator_id, reward_value)
       VALUES (?, ?, ?, 500)`
    ).bind(crypto.randomUUID(), code, userId).run()

    return { code, shareUrl: `https://tanstackship.com/invite/${code}` }
  }
)

// src/components/invite/ShareInvite.tsx
import { useMutation } from "@tanstack/react-query"
import { createInviteCode } from "../../lib/invite/codes"

export function ShareInvite() {
  const mutation = useMutation({
    mutationFn: () => createInviteCode(),
  })

  const shareUrl = mutation.data?.shareUrl ?? ""

  return (
    <div className="p-6 border rounded-lg">
      <h2 className="text-xl font-bold mb-4">Refer a Friend, Earn Credit</h2>
      <p className="text-gray-600 mb-4">
        Share your invite link and earn $5 for every friend who signs up
      </p>

      <button
        onClick={() => mutation.mutate()}
        className="bg-blue-600 text-white px-6 py-2 rounded-lg"
        disabled={mutation.isPending}
      >
        {mutation.isPending ? "Generating..." : "Get Your Referral Link"}
      </button>

      {shareUrl && (
        <div className="mt-4">
          <label className="block text-sm font-medium mb-1">
            Your referral link
          </label>
          <div className="flex gap-2">
            <input
              type="text"
              value={shareUrl}
              readOnly
              className="flex-1 px-3 py-2 border rounded"
            />
            <button
              onClick={() => navigator.clipboard.writeText(shareUrl)}
              className="px-4 py-2 bg-gray-100 rounded hover:bg-gray-200"
            >
              Copy
            </button>
          </div>
        </div>
      )}
    </div>
  )
}

// src/lib/invite/redeem.ts
import { createServerFn } from "@tanstack/react-start"

export const redeemInviteCode = createServerFn({ method: "POST" }).handler(
  async ({ code, newUserId }: { code: string; newUserId: string }) => {
    // Validate the invite code
    const invite = await env.DB.prepare(
      `SELECT ic.*, u.email as creator_email
       FROM invite_codes ic
       JOIN users u ON u.id = ic.creator_id
       WHERE ic.code = ? AND ic.is_active = 1
       AND (ic.expires_at IS NULL OR ic.expires_at > unixepoch())
       AND ic.use_count < ic.max_uses`
    ).bind(code).first()

    if (!invite) {
      return { success: false, error: "Invalid or expired invite code" }
    }

    // Prevent self-referral
    if (invite.creator_id === newUserId) {
      return { success: false, error: "You cannot use your own invite code" }
    }

    const now = Math.floor(Date.now() / 1000)
    const redemptionId = crypto.randomUUID()

    // Create redemption record (pending until condition is met)
    await env.DB.prepare(
      `INSERT INTO invite_redemptions
        (id, invite_code_id, referrer_id, referred_user_id,
         reward_referrer, reward_referred, status, created_at)
       VALUES (?, ?, ?, ?, ?, ?, 'pending', ?)`
    ).bind(
      redemptionId,
      invite.id,
      invite.creator_id,
      newUserId,
      invite.reward_value, // Referrer gets the reward
      invite.reward_value, // Referee gets the reward
      now
    ).run()

    // Increment code usage
    await env.DB.prepare(
      `UPDATE invite_codes SET use_count = use_count + 1 WHERE id = ?`
    ).bind(invite.id).run()

    // Award credit to the referred user immediately (signup bonus)
    await awardCredit(newUserId, invite.reward_value, "referral_signup_bonus", redemptionId)

    return { success: true, bonusAmount: invite.reward_value }
  }
)

// src/lib/invite/rewards.ts
// Triggered when the referred user completes their first payment
export const processReferralReward = createServerFn({ method: "POST" }).handler(
  async ({ referredUserId }: { referredUserId: string }) => {
    const pendingRedemption = await env.DB.prepare(
      `SELECT ir.*, ic.reward_type
       FROM invite_redemptions ir
       JOIN invite_codes ic ON ic.id = ir.invite_code_id
       WHERE ir.referred_user_id = ? AND ir.status = 'pending'`
    ).bind(referredUserId).first()

    if (!pendingRedemption) return { processed: false }

    // Award credit to the referrer
    await awardCredit(
      pendingRedemption.referrer_id,
      pendingRedemption.reward_referrer,
      "referral_reward",
      pendingRedemption.id
    )

    // Mark as completed
    await env.DB.prepare(
      `UPDATE invite_redemptions SET status = 'completed', completed_at = unixepoch()
       WHERE id = ?`
    ).bind(pendingRedemption.id).run()

    return { processed: true, amount: pendingRedemption.reward_referrer }
  }
)

// src/lib/credit/engine.ts

export async function awardCredit(
  userId: string,
  amountCents: number,
  reason: CreditLedger["reason"],
  referenceId: string
) {
  const currentBalance = await getCreditBalance(userId)
  const newBalance = currentBalance + amountCents

  await env.DB.prepare(
    `INSERT INTO credit_ledger (id, user_id, amount_cents, balance_after_cents, reason, reference_id)
     VALUES (?, ?, ?, ?, ?, ?)`
  ).bind(
    crypto.randomUUID(),
    userId,
    amountCents,
    newBalance,
    reason,
    referenceId
  ).run()

  return newBalance
}

export async function spendCredit(
  userId: string,
  amountCents: number,
  referenceId: string
): Promise<boolean> {
  const balance = await getCreditBalance(userId)
  if (balance < amountCents) return false // Insufficient credit

  const newBalance = balance - amountCents

  await env.DB.prepare(
    `INSERT INTO credit_ledger (id, user_id, amount_cents, balance_after_cents, reason, reference_id)
     VALUES (?, ?, ?, ?, 'subscription_payment', ?)`
  ).bind(
    crypto.randomUUID(),
    userId,
    -amountCents,
    newBalance,
    referenceId
  ).run()

  return true
}

export async function getCreditBalance(userId: string): Promise<number> {
  const result = await env.DB.prepare(
    `SELECT balance_after_cents
     FROM credit_ledger
     WHERE user_id = ?
     ORDER BY created_at DESC
     LIMIT 1`
  ).bind(userId).first()

  return (result?.balance_after_cents as number) ?? 0
}

// src/lib/invite/fraud-detection.ts
import { createServerFn } from "@tanstack/react-start"

export const validateReferral = createServerFn({ method: "POST" }).handler(
  async ({ referrerId, refereeId, ipAddress }: {
    referrerId: string
    refereeId: string
    ipAddress: string
  }) => {
    const checks = await Promise.all([
      // Check 1: Same IP detection
      env.DB.prepare(
        `SELECT COUNT(*) as count FROM users
         WHERE ip_address = ? AND id != ?`
      ).bind(ipAddress, refereeId).first(),

      // Check 2: Referral velocity (how many in last 24h)
      env.DB.prepare(
        `SELECT COUNT(*) as count FROM invite_redemptions
         WHERE referrer_id = ? AND created_at > unixepoch() - 86400`
      ).bind(referrerId).first(),

      // Check 3: Same device fingerprint (if available)
      // Implementation depends on your fingerprinting approach
    ])

    const [ipCheck, velocityCheck] = (checks as [{ count: number }, { count: number }])

    if (ipCheck.count > 3) {
      // Flag for review — same IP as too many different users
      await flagForReview(referrerId, "referral_fraud_same_ip")
      return { valid: false, reason: "suspicious_activity" }
    }

    if (velocityCheck.count > 10) {
      // More than 10 referrals in 24 hours is suspicious for most SaaS
      await flagForReview(referrerId, "referral_fraud_high_velocity")
      return { valid: false, reason: "rate_limited" }
    }

    return { valid: true }
  }
)

-- Dashboard queries for monitoring referral program health

-- 1. Referral funnel
SELECT
  COUNT(DISTINCT ic.creator_id) as users_with_codes,
  COUNT(DISTINCT ir.referred_user_id) as users_who_clicked,
  COUNT(DISTINCT CASE WHEN ir.status = 'completed' THEN ir.referred_user_id END) as converted_referrals,
  COUNT(DISTINCT CASE WHEN s.status = 'active' THEN ir.referred_user_id END) as retained_referrals
FROM invite_codes ic
LEFT JOIN invite_redemptions ir ON ir.invite_code_id = ic.id
LEFT JOIN subscriptions s ON s.user_id = ir.referred_user_id AND s.status = 'active'

-- 2. Top referrers
SELECT
  u.email,
  COUNT(ir.id) as referrals_sent,
  COUNT(CASE WHEN ir.status = 'completed' THEN 1 END) as referrals_completed,
  SUM(CASE WHEN ir.status = 'completed' THEN ir.reward_referrer END) / 100.0 as rewards_earned
FROM users u
JOIN invite_codes ic ON ic.creator_id = u.id
LEFT JOIN invite_redemptions ir ON ir.invite_code_id = ic.id
GROUP BY u.id
ORDER BY referrals_completed DESC
LIMIT 25

-- 3. Referral vs. organic retention comparison
SELECT
  CASE WHEN ir.id IS NOT NULL THEN 'referred' ELSE 'organic' END as acquisition_channel,
  COUNT(DISTINCT u.id) as total_users,
  COUNT(DISTINCT CASE WHEN s.status = 'active' THEN u.id END) as active_users,
  ROUND(AVG(s.mrr), 2) as avg_mrr,
  ROUND(AVG(s.created_at - u.created_at) / 86400, 0) as avg_days_to_churn_or_now
FROM users u
LEFT JOIN subscriptions s ON s.user_id = u.id
LEFT JOIN invite_redemptions ir ON ir.referred_user_id = u.id
GROUP BY acquisition_channel