How to Build a Loyalty Program with Lovable

1Create a loyalty program database schema in Supabase.
2
3Tables:
4- profiles: extend with columns tier (text default 'Bronze'), lifetime_points (int default 0), updated_at
5- rewards: id, name, description, point_cost (int), stock (int), image_url, is_active (bool default true), created_at
6- point_transactions: id, user_id (references auth.users), type ('earn' | 'redeem'), points (int, positive for earn, negative for redeem), description, reference_id (text, optional order/reward id), created_at
7- redemptions: id, user_id, reward_id (references rewards), points_spent (int), status ('pending' | 'fulfilled' | 'cancelled'), created_at
8
9Create a Postgres trigger function update_tier_after_earn() that fires AFTER INSERT on point_transactions WHERE NEW.type = 'earn':
101. SELECT SUM(points) FROM point_transactions WHERE user_id = NEW.user_id AND type = 'earn' INTO lifetime_pts
112. UPDATE profiles SET lifetime_points = lifetime_pts, tier = CASE WHEN lifetime_pts >= 2000 THEN 'Gold' WHEN lifetime_pts >= 500 THEN 'Silver' ELSE 'Bronze' END WHERE id = NEW.user_id
12
13RLS policies:
14- point_transactions: users SELECT/INSERT their own rows (user_id = auth.uid())
15- rewards: public SELECT, admin-only INSERT/UPDATE/DELETE
16- redemptions: users SELECT their own, service role INSERT
17- profiles: users SELECT/UPDATE their own row

1// supabase/functions/redeem-reward/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
11serve(async (req: Request) => {
12  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })
13
14  try {
15    const supabase = createClient(
16      Deno.env.get('SUPABASE_URL') ?? '',
17      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
18    )
19
20    const { reward_id, user_id } = await req.json()
21    if (!reward_id || !user_id) {
22      return new Response(JSON.stringify({ error: 'reward_id and user_id required' }), { status: 400, headers: corsHeaders })
23    }
24
25    // Run atomic redemption via RPC (Postgres function with transaction)
26    const { data, error } = await supabase.rpc('atomic_redeem_reward', {
27      p_reward_id: reward_id,
28      p_user_id: user_id,
29    })
30
31    if (error) {
32      return new Response(JSON.stringify({ error: error.message }), { status: 400, headers: corsHeaders })
33    }
34
35    return new Response(JSON.stringify({ success: true, redemption: data }), { headers: corsHeaders })
36  } catch (err) {
37    return new Response(JSON.stringify({ error: 'Internal error' }), { status: 500, headers: corsHeaders })
38  }
39})

1Build a customer loyalty dashboard at src/pages/Dashboard.tsx.
2
3Layout:
4- Top row: three stat Cards side by side
5  - Card 1: 'Your Points' — show current balance (SUM of all transactions for auth user), large number
6  - Card 2: 'Your Tier' — show tier as a colored Badge (Bronze=amber, Silver=gray, Gold=yellow) with a Crown icon for Gold
7  - Card 3: 'Lifetime Earned' — show total earn-type points
8- Below stats: a Progress bar showing points toward next tier. Label: 'X more points to Silver' (or Gold). Calculate from lifetime_points in profiles.
9- Below progress: a Card containing a DataTable of point_transactions ordered by created_at DESC. Columns: Date (relative), Description, Points (green for earn, red for redeem), Running Balance. Show 20 rows with pagination.
10- Use Recharts AreaChart showing points earned per week for the last 8 weeks. Fetch grouped data from point_transactions WHERE type = 'earn'.
11
12All data fetches use the Supabase client with the authenticated user's session. The balance SUM and the profile tier come from separate queries that run in parallel with Promise.all.

1Build a rewards catalog page at src/pages/Rewards.tsx.
2
3Requirements:
4- Fetch all rewards WHERE is_active = true, ordered by point_cost ASC
5- Display as a responsive grid of Cards (3 columns desktop, 2 tablet, 1 mobile)
6- Each Card shows: reward image (AspectRatio), name, description, point_cost as a Badge with Coins icon, stock remaining (show 'Low stock' warning Badge if stock < 5), and a 'Redeem' Button
7- The Redeem Button is disabled if the customer's current balance < point_cost. Show a Tooltip explaining why it is disabled.
8- Clicking Redeem opens an AlertDialog: 'Redeem [reward name] for [X] points?' with Confirm and Cancel buttons
9- On confirm, call the redeem-reward Edge Function via supabase.functions.invoke('redeem-reward', { body: { reward_id, user_id } })
10- Show a success Toast ('Reward redeemed! Check your email for details.') or error Toast on completion
11- After successful redemption, invalidate and refetch the customer's point balance

1Build an admin page at src/pages/Admin.tsx. Protect it so only users with role='admin' in profiles can access it (check role on mount, redirect to / if not admin).
2
3Two Tabs: 'Rewards' and 'Customers'.
4
5Rewards tab:
6- DataTable of all rewards with columns: Name, Point Cost, Stock, Active Toggle (Switch component), Edit Button
7- 'Add Reward' Button opens a Sheet with a form: name (Input), description (Textarea), point_cost (Input number), stock (Input number), image_url (Input), is_active (Switch). Submit inserts via supabase.from('rewards').insert()
8- Clicking Edit opens the same Sheet pre-filled for updating
9
10Customers tab:
11- DataTable fetching profiles joined with point balance SUM. Columns: Email (from auth.users), Tier Badge, Balance, Lifetime Points, Joined Date
12- Input to manually add points: select customer, enter points and description, click 'Add Points' to INSERT into point_transactions with type='earn' using service role (call an Edge Function add-points that validates admin role server-side)