Referral Reward System: Technischer Leitfaden zur Gestaltung effektiver Empfehlungsprogramme für SaaS

-- Tabelle 1: Invite-Codes (von Empfehlenden generiert)
CREATE TABLE invite_codes (
  id TEXT PRIMARY KEY,
  code TEXT NOT NULL UNIQUE, -- z. B. 'FRIEND-ABC123'
  creator_id TEXT NOT NULL, -- Der Nutzer, der diesen Code erstellt hat
  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 oder Prozentpunkten
  is_active INTEGER NOT NULL DEFAULT 1,
  expires_at INTEGER, -- Optionaler Ablaufzeitpunkt
  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);

-- Tabelle 2: Redemption-Einträge
CREATE TABLE invite_redemptions (
  id TEXT PRIMARY KEY,
  invite_code_id TEXT NOT NULL,
  referrer_id TEXT NOT NULL, -- Die Person, die den Code geteilt hat
  referred_user_id TEXT NOT NULL, -- Der neue Nutzer, der den Code verwendet hat
  reward_referrer INTEGER NOT NULL DEFAULT 0, -- Cents für den Empfehlenden
  reward_referred INTEGER NOT NULL DEFAULT 0, -- Cents für den Empfänger
  status TEXT NOT NULL DEFAULT 'pending' CHECK (
    status IN ('pending', 'completed', 'expired', 'fraudulent')
  ),
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  completed_at INTEGER, -- Zeitpunkt, wenn Bedingungen erfüllt wurden (z. B. Empfänger zahlt)
  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);

-- Tabelle 3: Credit-Ledger (für Credit-basierte Belohnungen)
CREATE TABLE credit_ledger (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  amount_cents INTEGER NOT NULL, -- Positiv für Credit, negativ für Ausgaben
  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, -- Verknüpft mit invite_redemptions oder Rechnung
  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: Empfehlungsstatistiken pro Nutzer
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"

// Sichere, nutzerfreundliche Invite-Code-Generierung
export function generateInviteCode(length: number = 8): string {
  // Zeichensatz vermeidet mehrdeutige Zeichen
  const chars = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789" // Kein 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()

    // Eindeutigkeit sicherstellen (Kollisionswahrscheinlichkeit ist vernachlässigbar, aber trotzdem prüfen)
    const existing = await env.DB.prepare(
      "SELECT id FROM invite_codes WHERE code = ?"
    ).bind(code).first()

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

    // Standardbelohnung: 500 Cents (5,00 $) Account-Credit für beide Seiten
    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">Freunde empfehlen, Guthaben verdienen</h2>
      <p className="text-gray-600 mb-4">
        Teilen Sie Ihren Invite-Link und verdienen Sie 5 $ für jeden Freund, der sich anmeldet
      </p>

      <button
        onClick={() => mutation.mutate()}
        className="bg-blue-600 text-white px-6 py-2 rounded-lg"
        disabled={mutation.isPending}
      >
        {mutation.isPending ? "Wird generiert..." : "Empfehlungslink abrufen"}
      </button>

      {shareUrl && (
        <div className="mt-4">
          <label className="block text-sm font-medium mb-1">
            Ihr Empfehlungslink
          </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"
            >
              Kopieren
            </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 }) => {
    // Invite-Code validieren
    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" }
    }

    // Selbst-Empfehlung verhindern
    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()

    // Redemption-Eintrag erstellen (ausstehend bis Bedingung erfüllt)
    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, // Empfehlender erhält die Belohnung
      invite.reward_value, // Empfänger erhält die Belohnung
      now
    ).run()

    // Nutzungszähler des Codes erhöhen
    await env.DB.prepare(
      `UPDATE invite_codes SET use_count = use_count + 1 WHERE id = ?`
    ).bind(invite.id).run()

    // Credit sofort an den empfohlenen Nutzer vergeben (Anmeldebonus)
    await awardCredit(newUserId, invite.reward_value, "referral_signup_bonus", redemptionId)

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

// src/lib/invite/rewards.ts
// Ausgelöst, wenn der empfohlene Nutzer seine erste Zahlung abschließt
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 }

    // Credit an den Empfehlenden vergeben
    await awardCredit(
      pendingRedemption.referrer_id,
      pendingRedemption.reward_referrer,
      "referral_reward",
      pendingRedemption.id
    )

    // Als abgeschlossen markieren
    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 // Unzureichendes Guthaben

  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([
      // Prüfung 1: Erkennung gleicher IP
      env.DB.prepare(
        `SELECT COUNT(*) as count FROM users
         WHERE ip_address = ? AND id != ?`
      ).bind(ipAddress, refereeId).first(),

      // Prüfung 2: Empfehlungsgeschwindigkeit (wie viele in den letzten 24h)
      env.DB.prepare(
        `SELECT COUNT(*) as count FROM invite_redemptions
         WHERE referrer_id = ? AND created_at > unixepoch() - 86400`
      ).bind(referrerId).first(),

      // Prüfung 3: Gleicher Device-Fingerprint (falls verfügbar)
      // Implementierung abhängig vom Fingerprinting-Ansatz
    ])

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

    if (ipCheck.count > 3) {
      // Zur Überprüfung markieren – gleiche IP wie zu viele verschiedene Nutzer
      await flagForReview(referrerId, "referral_fraud_same_ip")
      return { valid: false, reason: "suspicious_activity" }
    }

    if (velocityCheck.count > 10) {
      // Mehr als 10 Empfehlungen in 24 Stunden ist für die meisten SaaS verdächtig
      await flagForReview(referrerId, "referral_fraud_high_velocity")
      return { valid: false, reason: "rate_limited" }
    }

    return { valid: true }
  }
)

-- Dashboard-Abfragen zur Überwachung der Programmgesundheit

-- 1. Empfehlungs-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-Empfehlende
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. Vergleich Retention: Empfohlen vs. Organisch
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