How to Build a Affiliate Tracking App with Lovable

1Create an affiliate tracking schema in Supabase.
2
3Tables:
4- affiliates: id, user_id (references auth.users, unique), code (text unique, generated 8-char alphanumeric), name, email, website_url, commission_rate (decimal, e.g. 0.10 for 10%), status ('pending' | 'active' | 'suspended'), payout_method (text), payout_details (jsonb, encrypted), created_at
5- affiliate_clicks: id, affiliate_id (references affiliates), ip_hash (text), user_agent_hash (text), landing_url (text), referrer_url (text), visitor_id (text, uuid stored in visitor's localStorage), created_at
6- affiliate_conversions: id, affiliate_id (references affiliates), click_id (references affiliate_clicks, nullable), order_id (text unique), order_amount (decimal), commission_amount (decimal), status ('pending' | 'approved' | 'paid'), payout_id (text), created_at
7- affiliate_payouts: id, affiliate_id (references affiliates), total_amount (decimal), commission_ids (uuid array), status ('processing' | 'completed' | 'failed'), payout_reference (text), processed_at, created_at
8
9RLS:
10- affiliates: users can SELECT and UPDATE their own row
11- affiliate_clicks and affiliate_conversions: affiliates SELECT their own rows (affiliate_id = sub-select of their affiliate row)
12- affiliate_payouts: affiliates SELECT their own
13- All INSERT via service role (Edge Functions only)
14
15Add indexes: affiliate_clicks(affiliate_id, created_at DESC), affiliate_clicks(ip_hash, affiliate_id, created_at), affiliate_conversions(affiliate_id, status), affiliate_conversions(order_id).

1// supabase/functions/track-click/index.ts
2import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
3import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
4
5const corsHeaders = {
6  'Access-Control-Allow-Origin': '*',
7  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
8  'Content-Type': 'application/json',
9}
10
11async function hashString(input: string): Promise<string> {
12  const data = new TextEncoder().encode(input)
13  const hash = await crypto.subtle.digest('SHA-256', data)
14  return Array.from(new Uint8Array(hash)).map(b => b.toString(16).padStart(2, '0')).join('').slice(0, 16)
15}
16
17serve(async (req: Request) => {
18  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })
19
20  try {
21    const supabase = createClient(
22      Deno.env.get('SUPABASE_URL') ?? '',
23      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
24    )
25
26    const { ref_code, landing_url, referrer_url, visitor_id } = await req.json()
27
28    if (!ref_code) return new Response(JSON.stringify({ tracked: false }), { headers: corsHeaders })
29
30    const { data: affiliate } = await supabase
31      .from('affiliates')
32      .select('id')
33      .eq('code', ref_code)
34      .eq('status', 'active')
35      .single()
36
37    if (!affiliate) return new Response(JSON.stringify({ tracked: false }), { headers: corsHeaders })
38
39    const ip = req.headers.get('CF-Connecting-IP') ?? req.headers.get('X-Forwarded-For') ?? 'unknown'
40    const ua = req.headers.get('User-Agent') ?? ''
41    const ip_hash = await hashString(ip + affiliate.id)
42    const user_agent_hash = await hashString(ua)
43
44    // Dedup: check for existing click from same IP for this affiliate in last 24h
45    const since = new Date(Date.now() - 86_400_000).toISOString()
46    const { count } = await supabase
47      .from('affiliate_clicks')
48      .select('*', { count: 'exact', head: true })
49      .eq('affiliate_id', affiliate.id)
50      .eq('ip_hash', ip_hash)
51      .gte('created_at', since)
52
53    if ((count ?? 0) > 0) {
54      return new Response(JSON.stringify({ tracked: false, reason: 'duplicate' }), { headers: corsHeaders })
55    }
56
57    const { data: click } = await supabase
58      .from('affiliate_clicks')
59      .insert({ affiliate_id: affiliate.id, ip_hash, user_agent_hash, landing_url, referrer_url, visitor_id })
60      .select('id')
61      .single()
62
63    return new Response(JSON.stringify({ tracked: true, click_id: click?.id }), { headers: corsHeaders })
64  } catch (err) {
65    return new Response(JSON.stringify({ error: 'Internal error' }), { status: 500, headers: corsHeaders })
66  }
67})

1Create a Supabase Edge Function at supabase/functions/track-conversion/index.ts.
2
3The function accepts POST with body: { order_id: string, order_amount: number, visitor_id: string }.
4
5Logic:
61. Check if a conversion for this order_id already exists in affiliate_conversions. If so, return { duplicate: true } to prevent double commission on retries.
72. Look up the most recent affiliate_clicks row WHERE visitor_id = p_visitor_id AND created_at > now() - interval '30 days', ordered by created_at DESC, LIMIT 1.
83. If no click found, return { attributed: false } — organic sale, no commission.
94. Fetch the affiliate's commission_rate from the affiliates table.
105. Calculate commission_amount = order_amount * commission_rate, rounded to 2 decimal places.
116. INSERT into affiliate_conversions: affiliate_id, click_id, order_id, order_amount, commission_amount, status='pending'.
127. Return { attributed: true, affiliate_id, commission_amount }.
13
14This function is called from your existing checkout completion webhook or server action. It uses the visitor_id that the client stores in localStorage and sends at checkout.

1Build an affiliate dashboard at src/pages/AffiliateDashboard.tsx.
2
3Requirements:
4- Header Card: affiliate's unique referral link displayed with a Copy button. Show affiliate status Badge.
5- Four stat Cards: Total Clicks (30 days), Conversions (30 days), Conversion Rate (conversions/clicks as percentage), Pending Earnings (sum of commission_amount WHERE status='pending')
6- Recharts LineChart below stats: two lines, clicks and conversions per day for the last 30 days, with dual Y-axes. X-axis = date, formatted as 'Jan 15'.
7- Conversions DataTable: columns Date, Order Amount (currency), Commission (currency), Status Badge (pending=yellow, approved=green, paid=blue). Paginated, 20 rows.
8- A shareable link section: show the referral URL and allow the affiliate to add a custom UTM campaign parameter to it.
9
10All data fetches are scoped to the currently authenticated affiliate's rows via RLS. Use Promise.all for parallel fetches of stats.

1Build an admin page at src/pages/AffiliateAdmin.tsx. Protect with role='admin' check.
2
3Two Tabs: 'Pending Approvals' and 'Affiliates'.
4
5Pending Approvals tab:
6- DataTable of all affiliate_conversions WHERE status='pending'
7- Columns: Affiliate Name, Order ID, Order Amount, Commission Amount, Date, Approve Button, Reject Button
8- Bulk select checkboxes. 'Approve Selected' Button updates status='approved' for all checked rows.
9- Filter by affiliate Select and date range.
10
11Affiliates tab:
12- DataTable of all affiliates with columns: Name, Email, Code, Commission Rate, Status Badge, Total Earned (sum approved+paid commissions), Pending Balance (sum pending commissions), Actions
13- 'Process Payout' Button per affiliate: opens a Dialog showing all approved commissions as line items, total payout amount, and a payout reference Input. Submitting inserts into affiliate_payouts and updates all included commission_ids status to 'paid'.
14- 'Edit Commission Rate' inline — clicking the rate opens an editable Input that saves on blur.
15- Add Affiliate Button opens a Sheet with registration form.