# How to Build a Loyalty Program with Lovable

- Tool: How to Build with Lovable
- Difficulty: Intermediate
- Compatibility: Lovable Pro or higher
- Last updated: April 2026

## TL;DR

Build a loyalty points and rewards system in Lovable where customers earn points on purchases, auto-upgrade through tiers via a Postgres trigger, redeem rewards atomically using SELECT FOR UPDATE in an Edge Function, and manage a reward catalog — all backed by Supabase with full RLS and a real-time points balance dashboard.

## Before you start

- Lovable Pro account for Edge Function generation
- Supabase project with SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY saved to Cloud tab → Secrets
- An existing users or profiles table, or willingness to create one in this build
- Basic understanding of how points-based loyalty programs work (earn on purchase, redeem for rewards)
- Optional: a products or orders table to trigger point earning from real purchases

## Step-by-step guide

### 1. Create the loyalty schema with tier trigger

Start by asking Lovable to create the complete schema. The tier trigger is the most important piece — it must fire on every new earn transaction and recalculate the customer's tier using a CASE statement on their lifetime points.

```
Create a loyalty program database schema in Supabase.

Tables:
- profiles: extend with columns tier (text default 'Bronze'), lifetime_points (int default 0), updated_at
- rewards: id, name, description, point_cost (int), stock (int), image_url, is_active (bool default true), created_at
- 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
- redemptions: id, user_id, reward_id (references rewards), points_spent (int), status ('pending' | 'fulfilled' | 'cancelled'), created_at

Create a Postgres trigger function update_tier_after_earn() that fires AFTER INSERT on point_transactions WHERE NEW.type = 'earn':
1. SELECT SUM(points) FROM point_transactions WHERE user_id = NEW.user_id AND type = 'earn' INTO lifetime_pts
2. 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

RLS policies:
- point_transactions: users SELECT/INSERT their own rows (user_id = auth.uid())
- rewards: public SELECT, admin-only INSERT/UPDATE/DELETE
- redemptions: users SELECT their own, service role INSERT
- profiles: users SELECT/UPDATE their own row
```

> Pro tip: Ask Lovable to add a partial index on point_transactions: CREATE INDEX idx_point_tx_earn ON point_transactions(user_id) WHERE type = 'earn'. The tier trigger SUM query will use this index instead of scanning all transaction types.

**Expected result:** All four tables are created. The tier trigger is attached to point_transactions. Inserting an earn row of 600 points auto-updates the profile tier to Silver. TypeScript types are generated.

### 2. Build the atomic redemption Edge Function

This is the core of your loyalty system's integrity. The Edge Function runs a Postgres transaction that locks the reward row, checks stock and customer balance, then performs both decrements atomically. Ask Lovable to generate it from this specification.

```
// supabase/functions/redeem-reward/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Content-Type': 'application/json',
}

serve(async (req: Request) => {
  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })

  try {
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
    )

    const { reward_id, user_id } = await req.json()
    if (!reward_id || !user_id) {
      return new Response(JSON.stringify({ error: 'reward_id and user_id required' }), { status: 400, headers: corsHeaders })
    }

    // Run atomic redemption via RPC (Postgres function with transaction)
    const { data, error } = await supabase.rpc('atomic_redeem_reward', {
      p_reward_id: reward_id,
      p_user_id: user_id,
    })

    if (error) {
      return new Response(JSON.stringify({ error: error.message }), { status: 400, headers: corsHeaders })
    }

    return new Response(JSON.stringify({ success: true, redemption: data }), { headers: corsHeaders })
  } catch (err) {
    return new Response(JSON.stringify({ error: 'Internal error' }), { status: 500, headers: corsHeaders })
  }
})
```

> Pro tip: The real atomic work happens in a Postgres function atomic_redeem_reward(). Ask Lovable to create it with LANGUAGE plpgsql SECURITY DEFINER. Use SELECT ... FOR UPDATE on the rewards row and RAISE EXCEPTION for insufficient balance or out-of-stock to trigger an automatic rollback.

**Expected result:** The Edge Function is deployed. Calling it with a valid reward_id and sufficient balance creates a redemption row and deducts points. Concurrent calls for the last item in stock result in exactly one success and one 'out of stock' error.

### 3. Build the customer loyalty dashboard

Ask Lovable to create the main page customers see: their current balance prominently displayed, tier status with a progress bar, and a transaction history.

```
Build a customer loyalty dashboard at src/pages/Dashboard.tsx.

Layout:
- Top row: three stat Cards side by side
  - Card 1: 'Your Points' — show current balance (SUM of all transactions for auth user), large number
  - Card 2: 'Your Tier' — show tier as a colored Badge (Bronze=amber, Silver=gray, Gold=yellow) with a Crown icon for Gold
  - Card 3: 'Lifetime Earned' — show total earn-type points
- Below stats: a Progress bar showing points toward next tier. Label: 'X more points to Silver' (or Gold). Calculate from lifetime_points in profiles.
- 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.
- Use Recharts AreaChart showing points earned per week for the last 8 weeks. Fetch grouped data from point_transactions WHERE type = 'earn'.

All 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.
```

**Expected result:** The dashboard shows the customer's balance, tier, progress bar, and transaction history. The area chart renders points earned per week. All data is scoped to the authenticated user via RLS.

### 4. Build the reward catalog

Customers need a page to browse and redeem available rewards. Ask Lovable for a card grid that shows reward details and enables redemption with a confirmation Dialog.

```
Build a rewards catalog page at src/pages/Rewards.tsx.

Requirements:
- Fetch all rewards WHERE is_active = true, ordered by point_cost ASC
- Display as a responsive grid of Cards (3 columns desktop, 2 tablet, 1 mobile)
- 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
- The Redeem Button is disabled if the customer's current balance < point_cost. Show a Tooltip explaining why it is disabled.
- Clicking Redeem opens an AlertDialog: 'Redeem [reward name] for [X] points?' with Confirm and Cancel buttons
- On confirm, call the redeem-reward Edge Function via supabase.functions.invoke('redeem-reward', { body: { reward_id, user_id } })
- Show a success Toast ('Reward redeemed! Check your email for details.') or error Toast on completion
- After successful redemption, invalidate and refetch the customer's point balance
```

**Expected result:** The rewards catalog shows all active rewards as cards. The redeem button is correctly disabled for unaffordable rewards. Confirming a redemption calls the Edge Function and updates the balance.

### 5. Build the admin reward management page

Admins need to add new rewards, adjust stock, and see customer point balances. Ask Lovable to build a protected admin area.

```
Build 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).

Two Tabs: 'Rewards' and 'Customers'.

Rewards tab:
- DataTable of all rewards with columns: Name, Point Cost, Stock, Active Toggle (Switch component), Edit Button
- '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()
- Clicking Edit opens the same Sheet pre-filled for updating

Customers tab:
- DataTable fetching profiles joined with point balance SUM. Columns: Email (from auth.users), Tier Badge, Balance, Lifetime Points, Joined Date
- 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)
```

**Expected result:** Admin page is only visible to admin-role users. Rewards can be created and edited. The customers tab shows all users with their current balances. Manual point addition works via the Edge Function.

## Complete code example

File: `supabase/functions/redeem-reward/index.ts`

```typescript
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Content-Type': 'application/json',
}

serve(async (req: Request) => {
  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })

  const supabase = createClient(
    Deno.env.get('SUPABASE_URL') ?? '',
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
  )

  try {
    const { reward_id, user_id } = await req.json()

    if (!reward_id || !user_id) {
      return new Response(
        JSON.stringify({ error: 'reward_id and user_id are required' }),
        { status: 400, headers: corsHeaders }
      )
    }

    // Atomic redemption: lock reward row, check balance, decrement stock, insert transaction
    const { data, error } = await supabase.rpc('atomic_redeem_reward', {
      p_reward_id: reward_id,
      p_user_id: user_id,
    })

    if (error) {
      const status = error.message.includes('Insufficient') || error.message.includes('stock') ? 400 : 500
      return new Response(JSON.stringify({ error: error.message }), { status, headers: corsHeaders })
    }

    return new Response(
      JSON.stringify({ success: true, redemption_id: data }),
      { status: 200, headers: corsHeaders }
    )
  } catch (err) {
    const message = err instanceof Error ? err.message : 'Internal server error'
    return new Response(JSON.stringify({ error: message }), { status: 500, headers: corsHeaders })
  }
})

/*
Postgres function for Lovable to create:

CREATE OR REPLACE FUNCTION atomic_redeem_reward(
  p_reward_id uuid,
  p_user_id uuid
) RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_reward rewards%ROWTYPE;
  v_balance int;
  v_redemption_id uuid;
BEGIN
  SELECT * INTO v_reward FROM rewards WHERE id = p_reward_id FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'Reward not found'; END IF;
  IF v_reward.stock <= 0 THEN RAISE EXCEPTION 'Out of stock'; END IF;
  SELECT COALESCE(SUM(points), 0) INTO v_balance
    FROM point_transactions WHERE user_id = p_user_id;
  IF v_balance < v_reward.point_cost THEN
    RAISE EXCEPTION 'Insufficient points: need %, have %', v_reward.point_cost, v_balance;
  END IF;
  UPDATE rewards SET stock = stock - 1 WHERE id = p_reward_id;
  INSERT INTO point_transactions (user_id, type, points, description, reference_id)
    VALUES (p_user_id, 'redeem', -v_reward.point_cost, 'Redeemed: ' || v_reward.name, p_reward_id);
  INSERT INTO redemptions (user_id, reward_id, points_spent, status)
    VALUES (p_user_id, p_reward_id, v_reward.point_cost, 'pending')
    RETURNING id INTO v_redemption_id;
  RETURN v_redemption_id;
END;
$$;
*/
```

## Common mistakes

- **Storing a mutable points balance instead of using a ledger** — A single balance column is easy to corrupt. Concurrent updates, bugs, or manual edits create balances that don't match actual transactions. Auditing is impossible. Fix: Keep point_transactions as an append-only ledger. Always calculate the balance as SUM(points) WHERE user_id = ?. The trigger can cache lifetime_points in profiles for fast tier calculation, but the authoritative balance always comes from the sum.
- **Skipping SELECT FOR UPDATE on the rewards row during redemption** — Without the row lock, two simultaneous redemption requests for the last item in stock both read stock = 1, both pass the check, and both decrement — resulting in stock = -1 and two successful redemptions for one item. Fix: The atomic_redeem_reward Postgres function must use SELECT ... FOR UPDATE on the reward row. This serializes concurrent redemptions for the same reward. All other approaches (application-level locks, checking before inserting) have race conditions.
- **Calling the redemption Edge Function directly from client-side with the anon key** — The anon key respects RLS, but the Edge Function needs service role access to perform cross-table writes atomically. More importantly, the user_id should come from the JWT, not the request body. Fix: In the Edge Function, extract user_id from the Authorization JWT using supabase.auth.getUser(token) rather than trusting the request body. This prevents one user from redeeming rewards on behalf of another by forging the user_id parameter.
- **Not indexing point_transactions for balance queries** — Every balance display runs a SUM on point_transactions filtered by user_id. Without an index, this scans the full table. With 100 customers and 1000 transactions each, queries slow noticeably. Fix: Add CREATE INDEX idx_point_tx_user ON point_transactions(user_id). For the tier trigger SUM, add the partial index idx_point_tx_earn ON point_transactions(user_id) WHERE type = 'earn' as described in Step 1.

## Best practices

- Keep the point ledger append-only. Never UPDATE or DELETE point_transactions rows. If you need to reverse a transaction, insert a compensating entry with type='correction' and a negative points value.
- Validate the customer's identity server-side in the redemption Edge Function by verifying the JWT — never trust a user_id sent in the request body.
- Use SECURITY DEFINER on the atomic_redeem_reward Postgres function so it can bypass RLS when performing the cross-table atomic writes. Keep the function logic minimal to limit the security surface.
- Cache the tier and lifetime_points in the profiles table via the trigger, but treat them as derived data. The canonical source of truth is always the point_transactions ledger.
- Add database constraints to enforce data integrity: CHECK (points > 0) on earn rows, CHECK (points < 0) on redeem rows, CHECK (stock >= 0) on rewards. Let the database reject bad data before it reaches your application.
- Show customers their tier progress proactively. A progress bar to the next tier is one of the most effective engagement tools in loyalty programs. Calculate it from (lifetime_points - current_tier_floor) / (next_tier_floor - current_tier_floor).
- Log every redemption attempt in an audit table (success and failure) with the reason. This is invaluable for debugging support tickets and detecting abuse patterns.

## Frequently asked questions

### What prevents a customer from earning the same points twice?

The reference_id column on point_transactions stores the order or event ID that triggered the earn. Before inserting a new earn transaction, check that no row with the same reference_id already exists for that user. Add a UNIQUE constraint on (user_id, reference_id) to enforce this at the database level.

### How do I integrate point earning with an actual purchase flow?

Create an Edge Function earn-points that accepts an order_id and user_id. It looks up the order total, calculates points (e.g. 1 point per dollar), and inserts a point_transactions row with type='earn' and reference_id=order_id. Call this function from your Stripe webhook handler after a successful payment_intent.succeeded event.

### Can the tier trigger cause performance problems at scale?

The trigger fires on every earn INSERT and runs a SUM query. With the partial index on (user_id) WHERE type='earn', this query is fast even with thousands of transactions per user. For very high-volume systems (millions of transactions), consider a materialized column approach where you UPDATE lifetime_points incrementally instead of recalculating the full sum each time.

### How do I handle partial redemptions or refunds?

Insert a correcting earn transaction rather than deleting the redeem transaction. If a customer returns an item they used 200 points to discount, insert a new row with type='earn', points=200, description='Refund: Order #1234'. This maintains the append-only ledger. Update the redemptions row status to 'cancelled'.

### What happens if the Edge Function fails halfway through a redemption?

The atomic_redeem_reward Postgres function runs inside a single database transaction. If any step fails — balance check, stock decrement, transaction insert — the entire transaction rolls back. The customer's balance and the reward's stock remain unchanged. No partial state is possible.

### Can I add multiple tiers beyond Bronze, Silver, Gold?

Yes. Modify the CASE statement in the tier trigger to add Platinum (5000+), Diamond (10000+), or any tiers you want. The profiles.tier column is a text field, so no migration is needed for the column itself. Update the frontend Badge colors and tier progress calculation to reflect the new thresholds.

### How do I show the loyalty widget on a separate marketing site?

Create a public API Edge Function get-loyalty-status that accepts a user token and returns their tier, balance, and progress to next tier as JSON. Your marketing site can call this endpoint with the customer's Supabase JWT to display a loyalty widget. Enable CORS in the Edge Function to allow cross-origin requests.

### Is there help available to build a more complex loyalty or rewards system?

RapidDev builds production-ready Lovable apps including loyalty systems with Stripe integration, email automation, and custom tier logic. Reach out if your requirements go beyond this guide.

---

Source: https://www.rapidevelopers.com/how-to-build-lovable/loyalty-program
© RapidDev — https://www.rapidevelopers.com/how-to-build-lovable/loyalty-program