How to Build Loyalty program with V0

1// Paste this prompt into V0's AI chat:
2// Build a loyalty program system. Create a Supabase schema:
3// 1. members: id (uuid PK), user_id (uuid FK to auth.users), points_balance (int DEFAULT 0), lifetime_points (int DEFAULT 0), tier (text DEFAULT 'bronze'), joined_at (timestamptz)
4// 2. point_transactions: id (uuid PK), member_id (uuid FK to members), points (int), type (text CHECK IN 'earn','redeem','expire','adjust'), description (text), reference_id (text), created_at (timestamptz)
5// 3. rewards: id (uuid PK), name (text), description (text), points_cost (int), stock (int), image_url (text), is_active (boolean DEFAULT true)
6// 4. redemptions: id (uuid PK), member_id (uuid FK to members), reward_id (uuid FK to rewards), points_spent (int), status (text DEFAULT 'pending'), created_at (timestamptz)
7// 5. tier_rules: tier (text PK), min_lifetime_points (int), multiplier (numeric DEFAULT 1.0), perks (text[])
8// Seed tier_rules: bronze (0, 1x), silver (1000, 1.25x), gold (5000, 1.5x), platinum (15000, 2x).
9// Add RLS policies. Generate SQL and TypeScript types.

1// Paste this prompt into V0's AI chat:
2// Create a loyalty dashboard at app/loyalty/page.tsx.
3// Requirements:
4// - Fetch the current user's member record with tier_rules joined
5// - Show a hero Card with: points_balance in large text, tier Badge (bronze=brown, silver=gray, gold=amber, platinum=purple), member since date
6// - Show Progress bar toward next tier with text: 'X more points to [next tier]'
7// - Show tier perks list from tier_rules.perks
8// - Below, show a grid of reward Cards: image, name, points_cost, stock remaining, 'Redeem' Button
9// - Rewards with insufficient points show the Button as disabled with 'Need X more points'
10// - Rewards with stock = 0 show 'Sold Out' Badge
11// - Add Tabs for 'Rewards' and 'History'
12// - History tab shows a Table of point_transactions: date, type Badge (earn=green, redeem=red, expire=gray), points, description

1-- Run this in Supabase SQL Editor
2CREATE OR REPLACE FUNCTION redeem_reward(
3  p_member_id uuid,
4  p_reward_id uuid
5)
6RETURNS json AS $$
7DECLARE
8  v_points_cost int;
9  v_balance int;
10  v_stock int;
11  v_redemption_id uuid;
12BEGIN
13  -- Lock and fetch reward
14  SELECT points_cost, stock INTO v_points_cost, v_stock
15  FROM rewards WHERE id = p_reward_id AND is_active = true
16  FOR UPDATE;
17
18  IF NOT FOUND THEN
19    RAISE EXCEPTION 'Reward not found or inactive';
20  END IF;
21
22  IF v_stock <= 0 THEN
23    RAISE EXCEPTION 'Reward out of stock';
24  END IF;
25
26  -- Lock and fetch member balance
27  SELECT points_balance INTO v_balance
28  FROM members WHERE id = p_member_id
29  FOR UPDATE;
30
31  IF v_balance < v_points_cost THEN
32    RAISE EXCEPTION 'Insufficient points';
33  END IF;
34
35  -- Deduct points
36  UPDATE members SET points_balance = points_balance - v_points_cost WHERE id = p_member_id;
37
38  -- Decrement stock
39  UPDATE rewards SET stock = stock - 1 WHERE id = p_reward_id;
40
41  -- Create redemption
42  INSERT INTO redemptions (member_id, reward_id, points_spent)
43  VALUES (p_member_id, p_reward_id, v_points_cost)
44  RETURNING id INTO v_redemption_id;
45
46  -- Log transaction
47  INSERT INTO point_transactions (member_id, points, type, description, reference_id)
48  VALUES (p_member_id, -v_points_cost, 'redeem', 'Reward redemption', v_redemption_id::text);
49
50  RETURN json_build_object('redemption_id', v_redemption_id, 'new_balance', v_balance - v_points_cost);
51END;
52$$ LANGUAGE plpgsql SECURITY DEFINER;

1import { NextRequest, NextResponse } from 'next/server'
2import Stripe from 'stripe'
3import { createClient } from '@supabase/supabase-js'
4
5const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!)
6const supabase = createClient(
7  process.env.SUPABASE_URL!,
8  process.env.SUPABASE_SERVICE_ROLE_KEY!
9)
10
11export async function POST(req: NextRequest) {
12  const rawBody = await req.text()
13  const sig = req.headers.get('stripe-signature')!
14
15  let event: Stripe.Event
16  try {
17    event = stripe.webhooks.constructEvent(rawBody, sig, process.env.STRIPE_WEBHOOK_SECRET!)
18  } catch {
19    return NextResponse.json({ error: 'Invalid signature' }, { status: 400 })
20  }
21
22  if (event.type === 'payment_intent.succeeded') {
23    const intent = event.data.object as Stripe.PaymentIntent
24    const userId = intent.metadata?.user_id
25    if (!userId) return NextResponse.json({ received: true })
26
27    const { data: member } = await supabase
28      .from('members')
29      .select('id, tier')
30      .eq('user_id', userId)
31      .single()
32
33    if (!member) return NextResponse.json({ received: true })
34
35    const { data: tierRule } = await supabase
36      .from('tier_rules')
37      .select('multiplier')
38      .eq('tier', member.tier)
39      .single()
40
41    const basePoints = Math.floor(intent.amount / 100)
42    const earnedPoints = Math.floor(basePoints * (tierRule?.multiplier || 1))
43
44    await supabase.rpc('earn_points', {
45      p_member_id: member.id,
46      p_points: earnedPoints,
47      p_description: `Purchase: $${(intent.amount / 100).toFixed(2)}`,
48      p_reference: intent.id,
49    })
50  }
51
52  return NextResponse.json({ received: true })
53}

1// Paste this prompt into V0's AI chat:
2// Create a reward catalog at app/loyalty/rewards/page.tsx.
3// Requirements:
4// - Fetch all active rewards from Supabase
5// - Display as a Card grid: reward image, name, description, points_cost in large text, stock Badge
6// - 'Redeem' Button on each Card. If member points < points_cost, disable and show 'Need X more points'
7// - Clicking Redeem opens an AlertDialog: 'Are you sure? This will deduct [points_cost] points from your balance.'
8// - On confirm, call a Server Action that invokes the redeem_reward RPC function
9// - Show a success Toast with 'Reward redeemed! Your new balance is X points'
10// - If error (insufficient points or out of stock), show an error Toast
11// - Add search Input and category filter Select above the grid
12// - Show the member's current balance in a sticky header Card for reference