# How to Create a Loyalty Program App with Lovable

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

## TL;DR

To create a loyalty program app, track points as transactions, move customers through tiers automatically, and redeem rewards in one atomic step so points cannot be spent twice. In Lovable this runs on Supabase: a points ledger, a tier trigger, a reward catalog and a customer balance screen.

## Build or use a loyalty app platform?

Use a loyalty platform (built into Shopify or Square) for standard points-per-purchase. Build your own when rewards depend on your own data (visits, referrals, subscriptions), when you want it inside your app, or when platform fees grow with customers.

## 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.

### How long does it take to build a loyalty app in Lovable?

A working MVP takes a few hours with the steps below. A production version with POS or Shopify sync, fraud rules and admin reports usually takes a few weeks.

---

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