Feature spec
IntermediateCategory
payments-commerce
Build with AI
6-10 hours with Lovable or v0
Custom build
2-4 weeks custom dev
Running cost
$0/mo up to ~100 users · $25-100/mo at scale
Works on
Everything it takes to ship a Loyalty Program — parts, prompts, and real costs.
A loyalty program needs a tamper-proof server-side points ledger, a tier evaluation engine, and a redemption flow that generates discount codes without over-spending. With Lovable or FlutterFlow you can ship a working points system in 6-10 hours for $0/month up to 100 users. Costs rise to $25-100/mo at scale depending on Supabase plan, notification volume, and expiry scheduling.
What a Loyalty Program Feature Actually Is
A loyalty program rewards users with points for qualifying actions — purchases, reviews, referrals — and lets them redeem accumulated points for discounts or perks. The feature is deceptively complex under the hood: the ledger that tracks point movements must be append-only and tamper-proof (clients cannot insert arbitrary rows), point earning must be idempotent (a page refresh cannot award points twice), redemption must be atomic (two simultaneous clicks cannot over-spend a balance), and tier status must update in real time after earning events. The product decisions that matter most are where points are earned, how redemption maps to real-world value, and whether tiers have tangible benefits or are purely cosmetic.
What users consider table stakes in 2026
- Real-time points balance visible on every page — in the header or profile chip — that updates the instant an earning event completes
- Clear earning rules displayed at the point of action: 'Earn 10 points on every purchase' shown on the checkout page, not buried in a settings screen
- Animated points-earned toast notification immediately after a qualifying action, showing the delta ('+50 points') and the new balance
- Tiered status levels (e.g. Silver / Gold / Platinum) with a visible progress bar toward the next tier threshold
- Redemption flow with a clear minimum threshold and conversion rate: 'Redeem 500 points = $5 off' — no ambiguity about value
- Full points history with timestamps, reason labels ('Purchase #INV-042', 'Review submitted'), and expiry dates where applicable
Anatomy of the Feature
Seven components. AI tools handle the UI layer and basic Supabase wiring well on the first prompt. The earning engine guard against client-side manipulation, the redemption race condition, and the points expiry scheduler are where first builds fail.
Points balance display
UIA shadcn/ui Badge or custom header chip that shows the user's current redeemable balance. Subscribes to Supabase Realtime postgres_changes on the user's loyalty_accounts row so the number updates instantly after an earning event without a page reload.
Note: Subscribe to the full row, not just the balance column — the tier field updates in the same Edge Function call and should refresh simultaneously.
Points ledger list
UIA shadcn/ui Table or ScrollArea listing all loyalty_transactions for the user, newest first. Each row shows points_delta (positive for earning, negative for redemption or expiry), reason label, and created_at timestamp. For users with hundreds of transactions, react-window virtualises the list to avoid rendering thousands of DOM nodes.
Note: Colour-code rows: green for positive deltas, red for negative (redemption or expiry), gray for expired rows.
Tier progress bar
UIA shadcn/ui Progress component showing the user's progress toward the next tier threshold. The percentage is computed from loyalty_accounts.lifetime_points (total points ever earned, never reduced by redemptions) versus the next tier's min_lifetime_points from the tier_config table.
Note: Lifetime points and redeemable balance are separate values — redemption reduces balance but never reduces lifetime_points, so tier never regresses when a user spends points.
Earning engine
BackendA Supabase Edge Function or Next.js API route called after qualifying events (purchase completed, review submitted, referral converted). It validates the triggering event server-side (e.g. verifies the Stripe payment_intent ID), inserts a loyalty_transactions row with a positive points_delta and a reason label, and updates loyalty_accounts.balance and lifetime_points atomically in a PostgreSQL transaction.
Note: All point mutations must happen here — never from client code. A UNIQUE constraint on (user_id, reference_id) in loyalty_transactions makes earning idempotent.
Redemption flow
BackendAn Edge Function that validates the redemption request: checks balance >= minimum threshold, deducts points atomically using UPDATE loyalty_accounts SET balance = balance - :amount WHERE user_id = :uid AND balance >= :amount RETURNING id, then generates a discount code via Stripe Coupons API (stripe.coupons.create) or inserts a redemption_codes row. If the UPDATE returns zero rows, the balance was insufficient — return an error without creating a code.
Note: The conditional UPDATE is atomic at the database level. This eliminates the race condition where two simultaneous redemption requests both read a sufficient balance and both succeed.
Tier evaluation
BackendA PostgreSQL function or Edge Function step that runs after each earning event. It reads the updated lifetime_points from loyalty_accounts and compares against tier thresholds stored in the tier_config table (not hardcoded in application logic). If the user's lifetime_points cross a threshold, it updates loyalty_accounts.tier and optionally triggers a push notification.
Note: Storing tier thresholds in a tier_config table means product managers can adjust tier levels without a code deploy.
Points expiry scheduler
ServiceA Supabase pg_cron job or n8n scheduled workflow that runs nightly. It finds loyalty_transactions rows where expires_at < now() and the points have not been accounted for yet, inserts a negative transaction row (reason: 'Points expired'), and decrements loyalty_accounts.balance. The ledger remains append-only — no rows are deleted.
Note: n8n Cloud Starter (approx €20/mo) is an alternative to pg_cron if you need multi-channel expiry notifications alongside the balance deduction.
The data model
Four tables model the loyalty system: an accounts table for current balance and tier, an append-only transactions ledger, a tier configuration table, and a redemption codes table. RLS is the critical security layer — clients can read their own data but all writes go through Edge Functions using the service_role key. Paste this into the Supabase SQL editor before prompting your AI tool.
1-- One row per user: current redeemable balance and tier2create table public.loyalty_accounts (3 id uuid primary key default gen_random_uuid(),4 user_id uuid references auth.users(id) on delete cascade not null unique,5 balance integer not null default 0 check (balance >= 0),6 lifetime_points integer not null default 0 check (lifetime_points >= 0),7 tier text not null default 'Bronze',8 created_at timestamptz not null default now(),9 updated_at timestamptz not null default now()10);1112-- Append-only ledger: every point movement is a row13create table public.loyalty_transactions (14 id uuid primary key default gen_random_uuid(),15 user_id uuid references auth.users(id) on delete cascade not null,16 points_delta integer not null,17 reason text not null,18 reference_id text,19 reference_type text,20 expires_at timestamptz,21 created_at timestamptz not null default now()22);2324-- Unique constraint prevents duplicate awards for the same event25create unique index loyalty_transactions_idempotency_idx26 on public.loyalty_transactions (user_id, reference_id)27 where reference_id is not null;2829-- Tier thresholds stored in config, not hardcoded30create table public.tier_config (31 id uuid primary key default gen_random_uuid(),32 name text not null unique,33 min_lifetime_points integer not null,34 benefits jsonb not null default '[]'35);3637-- Seed default tiers38insert into public.tier_config (name, min_lifetime_points, benefits) values39 ('Bronze', 0, '["5% discount on all purchases"]'),40 ('Silver', 500, '["10% discount", "Free shipping on orders over $50"]'),41 ('Gold', 2000, '["15% discount", "Free shipping", "Priority support"]'),42 ('Platinum', 5000, '["20% discount", "Free shipping", "Dedicated account manager"]');4344-- Discount codes generated at redemption45create table public.redemption_codes (46 id uuid primary key default gen_random_uuid(),47 user_id uuid references auth.users(id) on delete cascade not null,48 code text not null unique,49 discount_value numeric(10, 2) not null,50 points_spent integer not null,51 stripe_coupon_id text,52 used_at timestamptz,53 created_at timestamptz not null default now()54);5556-- RLS: users can read their own data only57alter table public.loyalty_accounts enable row level security;58alter table public.loyalty_transactions enable row level security;59alter table public.tier_config enable row level security;60alter table public.redemption_codes enable row level security;6162create policy "Users read own loyalty account"63 on public.loyalty_accounts for select64 using (auth.uid() = user_id);6566create policy "Users read own transactions"67 on public.loyalty_transactions for select68 using (auth.uid() = user_id);6970-- Anyone can read tier config (public reference data)71create policy "Anyone reads tier config"72 on public.tier_config for select73 using (true);7475create policy "Users read own redemption codes"76 on public.redemption_codes for select77 using (auth.uid() = user_id);7879-- NO insert/update policies for loyalty_accounts or loyalty_transactions80-- All mutations go through Edge Functions using service_role key8182-- Performance indexes83create index loyalty_transactions_user_created_idx84 on public.loyalty_transactions (user_id, created_at desc);85create index loyalty_transactions_expiry_idx86 on public.loyalty_transactions (expires_at)87 where expires_at is not null;8889-- Auto-update updated_at on loyalty_accounts90create or replace function update_updated_at()91returns trigger language plpgsql as $$92begin93 new.updated_at = now();94 return new;95end;96$$;9798create trigger loyalty_accounts_updated_at99 before update on public.loyalty_accounts100 for each row execute function update_updated_at();Heads up: The NO insert/update RLS on loyalty_accounts and loyalty_transactions is intentional — clients have no write access. All mutations are performed by Edge Functions using SUPABASE_SERVICE_ROLE_KEY, which bypasses RLS. This is the critical security boundary that prevents users from awarding themselves arbitrary points from the browser console.
Build it — pick your path
Each tool fits this feature differently. Switch tabs to compare fit, time, the exact steps, and a copy-paste prompt.
Best all-round path: Lovable wires Supabase Auth, Edge Functions for the earning engine, and Realtime balance updates in one project. The Realtime subscription works out of the box once you connect Lovable Cloud.
Step by step
- 1Create a new Lovable project and enable Lovable Cloud so Supabase is provisioned automatically
- 2Open the Supabase SQL editor via the Cloud tab and run the data model SQL from this page — all four tables, RLS policies, and tier seed data
- 3Add SUPABASE_SERVICE_ROLE_KEY and STRIPE_SECRET_KEY in the Cloud tab under Secrets — the earning and redemption Edge Functions need both
- 4Paste the prompt below into Agent Mode; let it scaffold the balance chip, points history, tier progress bar, earning Edge Function, and redemption flow
- 5Test earning by triggering a purchase event in your app and confirming the balance chip updates in real time without a page reload
- 6Test redemption by clicking Redeem, verifying the points are deducted atomically, and confirming the discount code appears in the UI with a copy button
Build a loyalty points system using Supabase. The database schema is already created: loyalty_accounts (user_id, balance, lifetime_points, tier), loyalty_transactions (user_id, points_delta, reason, reference_id, reference_type, expires_at), tier_config (name, min_lifetime_points, benefits), and redemption_codes (user_id, code, discount_value, points_spent). All point mutations must happen server-side in Edge Functions using SUPABASE_SERVICE_ROLE_KEY — never from client code. Build these components: 1) Points balance chip in the app header: shows current balance from loyalty_accounts, subscribes to Supabase Realtime postgres_changes on the full loyalty_accounts row (not just balance) so balance AND tier update instantly after earning. 2) Points history page: list of loyalty_transactions newest first; each row shows points_delta (green for positive, red for negative), reason, and created_at. Use react-window if the list exceeds 100 rows. 3) Tier progress card: Progress bar from shadcn/ui showing lifetime_points progress toward the next tier threshold, fetched from tier_config table. Never hardcode tier thresholds in code. 4) Earning Edge Function at /functions/v1/award-points: accepts user_id, points_amount, reason, reference_id, and reference_type. Validates that reference_id does not already exist in loyalty_transactions (UNIQUE constraint will reject duplicates — catch the error and return a 409). Inserts a loyalty_transactions row, then updates loyalty_accounts balance += points_amount and lifetime_points += points_amount. After updating, check if lifetime_points now crosses a tier threshold in tier_config and update loyalty_accounts.tier if so. Use a PostgreSQL transaction for the insert + update. 5) Redemption flow: Redeem Points button that opens a dialog showing current balance and the redemption rate (500 points = $5 off). On confirm, call a /functions/v1/redeem-points Edge Function that: checks balance >= 500, runs UPDATE loyalty_accounts SET balance = balance - 500 WHERE user_id = :uid AND balance >= 500 RETURNING id (returns error if zero rows), calls Stripe Coupons API to create a $5 coupon code, inserts into redemption_codes, inserts a loyalty_transactions row (points_delta: -500, reason: 'Points redeemed'), returns the code. Show the code in the UI with a copy-to-clipboard button. If balance is insufficient, show an error toast — do not process. 6) Points-earned toast: after any earning event, show an animated toast with '+N points' and the new balance. Handle the edge case where a failed payment should not award points — only call the earning Edge Function after confirming the Stripe payment_intent status is 'succeeded'.
Where this path bites
- Lovable's AI tends to allow client-side point inserts using the Supabase client directly — the prompt above specifies Edge Function only, but verify the generated code does not include any supabase.from('loyalty_transactions').insert() calls in React components
- Stripe coupon creation in the redemption Edge Function requires the Stripe secret key in Secrets; Lovable may scaffold a placeholder that needs manual wiring
- pg_cron for points expiry must be enabled in the Supabase Dashboard under Database → Extensions — Lovable cannot enable extensions via prompt
Third-party services you'll need
The core loyalty ledger runs on Supabase alone. Additional services are needed for push notifications, points expiry scheduling at scale, and Stripe-backed redemption.
| Service | What it does | Free tier | Paid from |
|---|---|---|---|
| Supabase | PostgreSQL database (ledger + accounts + tier config), Realtime (live balance updates), Edge Functions (earning and redemption engine), pg_cron extension (points expiry) | 500 MB DB, 200 Realtime connections | Pro $25/mo: 8 GB DB, 500 Realtime connections (approx) |
| Stripe Coupons API | Generate percentage-off or fixed-amount discount codes at the moment of points redemption | Included in Stripe — no separate fee for coupon creation | 2.9% + 30¢ per charge when the coupon is applied (approx) |
| n8n | Alternative to pg_cron for points expiry scheduling and multi-channel loyalty notifications (email + push) | Self-hosted free | Cloud Starter approx €20/mo: 2,500 executions/mo (approx) |
| OneSignal | Push notifications for tier upgrades and points-earned events on mobile apps | Unlimited push, 10,000 subscribers | Growth $9/mo (approx): segmentation and targeting |
Swipe the table sideways to see pricing.
What it costs to run
Drag through the tiers to see how your monthly bill scales with users — no surprises later.
Estimated monthly running cost
Supabase free tier handles the ledger and Realtime connections. Stripe fees only when redemptions generate discount-backed purchases. OneSignal free tier covers push notifications.
Estimates use verified 2026 pricing and assume typical usage per user. Your real bill depends on activity, storage, and third-party plans.
What breaks when AI tools build this
The failures people actually hit on their first build — the symptom, why it happens, and the exact fix.
Client-side point inserts allow balance manipulation
Symptom: AI tools commonly generate a Supabase client call like supabase.from('loyalty_transactions').insert({points_delta: 100}) directly inside a React component's useEffect after a purchase completes. Any user can open the browser console and call this same function with an arbitrary points_delta value — instantly awarding themselves thousands of points.
Fix: Move ALL point mutations to a Supabase Edge Function that validates the triggering event server-side before inserting any row. For purchase-triggered awards, pass the Stripe payment_intent ID to the Edge Function and verify its status via the Stripe API before crediting points. Remove all Supabase write access from loyalty tables in the RLS policies — the data model SQL on this page has no insert policies for clients.
Double-award on page refresh or re-navigation
Symptom: Earning is triggered by a client-side useEffect that fires on component mount. If the user navigates to the purchase confirmation page, earns points, and then navigates back and forward again, the effect fires a second time and awards the same points again. At scale, the same Stripe webhook can fire twice due to Stripe's retry logic.
Fix: Make earning idempotent by storing the reference_id (Stripe payment_intent ID) on the loyalty_transactions row with a UNIQUE constraint indexed on (user_id, reference_id). Catch the UNIQUE constraint violation in the Edge Function and return a 409 Conflict without inserting a duplicate row. The data model SQL on this page includes this index.
Realtime balance update misses tier change
Symptom: Supabase Realtime subscriptions commonly target just the balance column. When the earning Edge Function updates both balance and tier in the same PostgreSQL transaction, the Realtime payload delivers the full updated row — but if the React subscription only watches balance, it reads the new balance while rendering the stale tier label from component state.
Fix: Subscribe to the full loyalty_accounts row using on('postgres_changes', {event: 'UPDATE', table: 'loyalty_accounts'}) and replace both balance and tier in state from the payload simultaneously. Never subscribe to individual columns if multiple fields can change in a single update.
Points redemption race condition over-spends balance
Symptom: A user clicks Redeem twice before the first request completes. Both requests read the same current balance (e.g. 500 points), both find it sufficient, and both issue a discount code — spending 1,000 points against a 500-point balance. The balance column goes negative, violating the CHECK constraint, or worse, the check is absent and the balance goes negative silently.
Fix: Use a conditional UPDATE with a balance check in the same statement: UPDATE loyalty_accounts SET balance = balance - 500 WHERE user_id = :uid AND balance >= 500 RETURNING id. If zero rows are returned, the balance was insufficient or a concurrent request already deducted it — return an error. The CHECK (balance >= 0) constraint in the data model SQL provides a final safety net.
Stripe coupon not applied to the right checkout
Symptom: AI generates a Stripe coupon and returns the code string to the client. The client is then responsible for manually entering the code at checkout. Users report the discount 'not working' because they closed the dialog, navigated to a new checkout session, or forgot to paste the code. The coupon was valid but unused.
Fix: Store the generated coupon code in the redemption_codes table and surface it persistently in the app UI with a copy-to-clipboard button that stays visible until used_at is populated. For the best UX, also pass the coupon code as discounts: [{coupon: code}] in the server-side Stripe Checkout session creation when the user initiates their next purchase — so the discount applies automatically.
Best practices
Never allow client code to write to loyalty_accounts or loyalty_transactions — all mutations must go through a server-side Edge Function that validates the triggering event before crediting points
Make point earning idempotent with a UNIQUE constraint on (user_id, reference_id) in loyalty_transactions so Stripe retry webhooks and page refreshes can never award the same points twice
Store tier thresholds in a tier_config database table rather than hardcoding them — product managers can adjust tier levels without a code deploy
Track lifetime_points separately from the redeemable balance so tier status never regresses when a user spends their points
Subscribe to the full loyalty_accounts row in Supabase Realtime, not just balance — balance and tier update in the same transaction and the UI should reflect both simultaneously
Use a conditional UPDATE (balance = balance - N WHERE balance >= N) for all redemptions — it is atomic and eliminates the race condition without needing advisory locks
Show the redemption code persistently in the UI until it is used, and pre-apply it server-side to the next Checkout session when possible — do not rely on users remembering to copy-paste a code
Log point expiry as negative loyalty_transactions rows rather than modifying existing rows — the ledger must be append-only for auditability
When You Need Custom Development
AI tools deliver a complete points-based loyalty system — earning engine, tier progression, real-time balance, Stripe redemption — without custom development. The cases that genuinely require it involve partner networks, regulatory classification, or enterprise rule complexity:
- You need a multi-brand coalition where points earned with Partner A can be redeemed with Partner B, requiring a shared ledger with partner API authentication and cross-tenant balance management
- Your earning rules are SKU-level or promotion-level ('double points on Product X this weekend only') with hundreds of configurable conditions that require a rule engine, not hardcoded Edge Function logic
- Your jurisdiction classifies loyalty points as financial instruments requiring formal accounting treatment, balance sheet reporting, or regulatory filings — this is beyond what an append-only Supabase ledger provides out of the box
- You want to offer physical reward catalog redemption (merchandise, prepaid cards) through third-party providers like Tremendous or Rybbon rather than Stripe discount codes, requiring catalog API integration and fulfillment webhooks
RapidDev builds these features for production
Real auth, database, and payments — fixed price, delivered in weeks. You own 100% of the code.
Frequently asked questions
How do I prevent users from cheating a loyalty points system?
Remove all write access from the loyalty_accounts and loyalty_transactions tables in your RLS policies so no client-side code can insert or update loyalty rows. All point mutations must happen in a Supabase Edge Function or Next.js API route that validates the triggering event server-side before crediting any points — for purchase events, verify the Stripe payment_intent status via the Stripe API before inserting a transaction row. This single architectural decision eliminates the entire category of client-side manipulation.
Can I add tiered membership levels (Silver, Gold, Platinum) to a loyalty program?
Yes, and the key is to store tier thresholds in a tier_config database table rather than hardcoding them. Each tier row has a name, a min_lifetime_points threshold, and a benefits JSON array. After each earning event, your Edge Function reads the updated lifetime_points from loyalty_accounts and queries tier_config to determine if the user has crossed a new threshold. Because tier is based on lifetime_points (never reduced by redemptions), tier status can only go up, never down when a user spends their balance.
How do I connect loyalty points to Stripe payments automatically?
Register a Stripe webhook endpoint pointing to your earning Edge Function. When payment_intent.succeeded fires, extract the payment_intent ID as the reference_id, compute the points to award (e.g. 1 point per dollar spent), and call your Edge Function. The UNIQUE constraint on (user_id, reference_id) ensures Stripe's retry logic cannot award duplicate points if the webhook fires twice for the same payment.
What is the best way to make loyalty points expire after a set time?
Store an expires_at timestamp on each loyalty_transactions row at insert time (e.g. earned_at + 365 days). Run a Supabase pg_cron job nightly that finds transactions with expires_at < now() that haven't been accounted for, inserts a negative loyalty_transactions row with reason 'Points expired', and decrements loyalty_accounts.balance. This keeps the ledger append-only and gives users a full audit trail of when their points expired and why. n8n Cloud (approx €20/mo) is an alternative to pg_cron if you want to send expiry reminder notifications alongside the balance deduction.
Can I let users redeem points for discount codes or free products?
Yes. For discount codes, call Stripe Coupons API (stripe.coupons.create) from your redemption Edge Function to generate a percentage-off or fixed-amount coupon code. Store the code in a redemption_codes table and display it in the app with a copy button. For the smoothest UX, also pass discounts: [{coupon: code}] in your server-side Stripe Checkout session creation so the discount applies automatically rather than requiring the user to paste a code manually.
How do I show a live points balance that updates instantly after a purchase?
Add a Supabase Realtime subscription in your balance chip component using on('postgres_changes', {event: 'UPDATE', table: 'loyalty_accounts'}). When your earning Edge Function updates the loyalty_accounts row, Supabase pushes the change over WebSocket and your UI updates in real time. Subscribe to the full row payload — not just balance — so the tier label also refreshes if the user just crossed a tier threshold.
How do I handle a refund that should reverse loyalty points?
Listen for the Stripe charge.refunded or payment_intent.refund.created webhook event. In your handler, look up the original loyalty_transactions row by the payment_intent reference_id, compute the points to reverse (proportional to the refund amount), and insert a new negative loyalty_transactions row (reason: 'Refund - Order #X'). Then decrement loyalty_accounts.balance by the same amount. Never delete or modify the original earning row — the ledger is append-only and the refund row is the audit record.
Is it possible to award bonus points for referrals, reviews, or social shares?
Yes — your earning Edge Function accepts a reason and reference_type, so you can call it from any event source. For referrals, call it when the referred user completes their first purchase and pass reference_type: 'referral'. For reviews, call it after a review is successfully submitted. For social shares, call it from a server-side verification step (e.g. confirming a tweet ID via the Twitter API) before awarding points — never trust a client-side 'I shared it' signal. The UNIQUE constraint on reference_id prevents the same referral, review, or share from being awarded twice.
Need this feature production-ready?
RapidDev builds a loyalty program into real apps — auth, database, payments — at $13K–$25K.
Book a free consultation30-min call. No commitment.