# How to Add Gamification to Your App (Copy-Paste Prompts Included)

- Tool: App Features
- Last updated: July 2026

## TL;DR

Gamification needs six pieces: an XP counter reading from Supabase with Realtime for instant updates, a badge gallery with locked/unlocked states, a streak widget with daily UTC reset, a points engine Edge Function triggered by user actions, a badge evaluation function, and a pg_cron job for streak resets. With Lovable you can ship a working system in 6-9 hours for $0/month — costs only appear past 1,000 users when Supabase Pro is needed.

## What a Gamification System Actually Is

Gamification adds game-like mechanics — XP points, badges, level progression, and daily streaks — to a non-game product to increase engagement and retention. The product decisions are: which user actions award points and how many, what badges exist and what unlocks them, how levels scale (linear or exponential XP thresholds), and whether the gamification is private (personal progress) or public (visible on a leaderboard). The visual components are straightforward — a progress bar, a badge grid, a flame icon. The complexity lives in the event-driven backend: making the XP counter update the instant a user completes a lesson, preventing double-awarding badges, and running the streak reset at exactly midnight UTC every night.

## Anatomy of the Feature

Six components. Lovable handles the Supabase schema and badge gallery on a first prompt. The pg_cron streak reset, canvas-confetti animation, and complex badge conditions reliably need a second pass.

- **XP counter + level bar** (ui): React component that reads xp and level from the user_gamification Supabase table via a Realtime subscription. shadcn/ui Progress component for the level bar filled by ((xp - current_level_min) / (next_level_min - current_level_min)) * 100 — using level breakpoints from a levels reference table. canvas-confetti npm package (MIT, free, 7KB) fires the celebration burst on level-up.
- **Badge gallery** (ui): CSS Grid of badge cards reading from a badges reference table (all available badges) joined with user_badges (earned badges). Unlocked badges render at full opacity with a checkmark; locked badges use Tailwind opacity-50 with a lock icon. The earn condition text (e.g. 'Complete 5 lessons') is always visible so users understand what to do next.
- **Streak widget** (ui): Reads current_streak and longest_streak from the user_streaks table. A countdown timer to midnight UTC is calculated with dayjs (dayjs.utc().endOf('day').diff(dayjs.utc(), 'second')) and displayed as 'Resets in 4h 22m'. Heroicons flame icon shifts from grey to orange at streak >= 3 and to red at streak >= 7.
- **Event listener / points engine** (backend): Supabase Edge Function (award_points) called from the client or from a Postgres trigger after qualifying actions. Receives event_type and user_id, looks up the point value in an event_point_values config table, increments user_gamification.xp, checks level thresholds, updates level if crossed, inserts into user_xp_log for audit, and updates user_streaks.last_active_date + increments current_streak if the date is new.
- **Badge evaluation engine** (backend): Supabase Edge Function (check_badges) called after award_points completes. Iterates the badges table, evaluates each badge's condition_type and condition_value against the user's current stats (total XP, streak length, specific action counts from user_xp_log), and inserts into user_badges with ON CONFLICT (user_id, badge_id) DO NOTHING for any newly earned badges. Emits a Supabase Realtime broadcast on channel 'badges:{user_id}' so the frontend can show the toast notification.
- **Streak reset cron** (backend): Postgres pg_cron job running daily at 00:05 UTC. UPDATE user_gamification SET current_streak = 0, last_active_date = NULL WHERE last_active_date < CURRENT_DATE - 1. Preserves longest_streak if current_streak was higher before reset. The 5-minute offset (00:05 not 00:00) avoids clock-edge race conditions between the cron and the award_points Edge Function.

## Data model

Five tables: the main gamification state per user, a badges reference catalogue, earned badges, an XP audit log, and a levels reference table for breakpoints. Paste this into the Supabase SQL Editor and run it before prompting your AI tool:

```sql
create table public.levels (
  level int primary key,
  xp_required int not null,
  title text
);

insert into public.levels (level, xp_required, title) values
  (1, 0, 'Beginner'),
  (2, 100, 'Explorer'),
  (3, 300, 'Achiever'),
  (4, 600, 'Expert'),
  (5, 1000, 'Master');

create table public.user_gamification (
  user_id uuid primary key references auth.users(id) on delete cascade,
  xp int not null default 0,
  level int not null default 1 references public.levels(level),
  current_streak int not null default 0,
  longest_streak int not null default 0,
  last_active_date date
);

create table public.badges (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  name text not null,
  description text,
  icon_url text,
  condition_type text not null,
  condition_value int not null
);

create table public.user_badges (
  user_id uuid references auth.users(id) on delete cascade not null,
  badge_id uuid references public.badges(id) on delete cascade not null,
  earned_at timestamptz not null default now(),
  primary key (user_id, badge_id)
);

create table public.user_xp_log (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade not null,
  action text not null,
  xp_awarded int not null,
  created_at timestamptz not null default now()
);

-- RLS
alter table public.user_gamification enable row level security;
alter table public.user_badges enable row level security;
alter table public.user_xp_log enable row level security;

create policy "Users read own gamification state"
  on public.user_gamification for select
  using (auth.uid() = user_id);

create policy "Users update own gamification state"
  on public.user_gamification for update
  using (auth.uid() = user_id);

create policy "Users insert own gamification row"
  on public.user_gamification for insert
  with check (auth.uid() = user_id);

create policy "Badges are public"
  on public.badges for select
  using (true);

create policy "Users read own badges"
  on public.user_badges for select
  using (auth.uid() = user_id);

create policy "Users read own xp log"
  on public.user_xp_log for select
  using (auth.uid() = user_id);

-- Indexes
create index user_xp_log_user_idx on public.user_xp_log (user_id, created_at desc);
create index user_xp_log_action_idx on public.user_xp_log (user_id, action);

-- pg_cron streak reset at 00:05 UTC daily
-- (run after enabling pg_cron extension in Dashboard → Database → Extensions)
select cron.schedule(
  'streak-reset',
  '5 0 * * *',
  $$
    update public.user_gamification
    set
      longest_streak = greatest(longest_streak, current_streak),
      current_streak = 0,
      last_active_date = null
    where last_active_date < current_date - 1
  $$
);
```

Seed the levels table with your actual XP breakpoints before launching — users will see 'Level 1' forever if the levels table is empty and the Edge Function cannot find a matching next-level threshold.

## Build paths

### Lovable — fit 4/10, 6-9 hours

Best all-round path: Lovable generates the full Supabase schema, award_points and check_badges Edge Functions, and the React badge gallery and XP bar in one or two prompts. Supabase Realtime for live XP updates and badge toasts works out of the box with the right prompt.

1. Create a Lovable project with Lovable Cloud enabled and paste the first prompt below for the schema and XP system
2. Verify the award_points Edge Function exists in Supabase Dashboard → Edge Functions and test it by invoking it with a test user_id and action
3. Enable pg_cron in Supabase Dashboard → Database → Extensions and run the cron schedule SQL from this page
4. Paste the second prompt (or add to the first) specifying the badge gallery and the canvas-confetti celebration animation
5. Test the complete flow: complete a qualifying action, verify XP increments in user_gamification, verify the badge gallery shows the new earned badge, and verify the confetti fires once and not on re-renders

Starter prompt:

```
Build a gamification system for a web app using Supabase. Create these tables: levels (level int pk, xp_required int, title text — seed with: level 1=0 XP Beginner, level 2=100 XP Explorer, level 3=300 XP Achiever, level 4=600 XP Expert, level 5=1000 XP Master), user_gamification (user_id uuid pk fk auth.users, xp int default 0, level int default 1 fk levels, current_streak int default 0, longest_streak int default 0, last_active_date date), badges (id uuid pk, slug text unique, name text, description text, icon_url text, condition_type text, condition_value int), user_badges (user_id fk, badge_id fk, earned_at, pk user_id+badge_id), user_xp_log (id pk, user_id fk, action text, xp_awarded int, created_at). RLS: user_gamification SELECT/UPDATE/INSERT own row; badges SELECT public; user_badges SELECT own rows. Create a Supabase Edge Function called award_points that: receives {user_id, action} in the request body; looks up points for the action from this map: lesson_complete=10, review_posted=5, daily_login=5, challenge_won=50; upserts a user_gamification row (ON CONFLICT user_id DO UPDATE SET xp = xp + points, last_active_date = today, current_streak = CASE WHEN last_active_date = yesterday THEN current_streak + 1 ELSE 1 END, level = (SELECT level FROM levels WHERE xp_required <= xp+points ORDER BY level DESC LIMIT 1)); inserts into user_xp_log; then calls check_badges. Create a Supabase Edge Function check_badges that: queries all badges, evaluates condition_type 'min_xp' against user_gamification.xp and condition_type 'min_streak' against current_streak; inserts into user_badges ON CONFLICT (user_id, badge_id) DO NOTHING for any newly earned badges; broadcasts on Supabase Realtime channel 'badges:{user_id}' with the newly earned badge data. UI: a GamificationWidget showing: XP counter (current XP / next level XP required from levels table), shadcn/ui Progress bar filled by ((xp - currentLevelMin) / (nextLevelMin - currentLevelMin)) * 100, streak flame icon (grey <3, orange >=3, red >=7) with countdown to midnight UTC using dayjs, and a level badge showing level number. On award_points response: if level increased, fire canvas-confetti; subscribe to 'badges:{userId}' Realtime channel and show a shadcn/ui Toast with the badge name and icon on each broadcast. Badge gallery page: CSS Grid of all badges from the badges table, left-joined with user_badges; locked badges show at opacity-50 with a lock icon; unlocked show with a gold checkmark and earned_at date.
```

Limitations:

- pg_cron streak reset must be enabled manually in Supabase Dashboard → Database → Extensions — the Edge Function covers daily login detection but the cron reset for inactive users requires the extension
- canvas-confetti animation often requires a follow-up prompt to wrap in useEffect with a one-shot flag — without it, confetti fires on every React re-render
- Complex badge conditions (e.g. 'complete 3 different lesson types in one week') need explicit condition_type definitions in the prompt — the AI simplifies to min_xp and min_streak by default

### V0 — fit 3/10, 7-10 hours

Best UI quality for the XP dashboard and badge gallery in a Next.js app — v0's Recharts integration and shadcn/ui components produce the cleanest visual output. Backend Edge Functions and Realtime subscriptions must be wired manually.

1. Prompt v0 to generate the GamificationDashboard component, badge gallery grid, and streak widget with the spec below
2. Add Supabase environment variables in the Vars panel: NEXT_PUBLIC_SUPABASE_URL and NEXT_PUBLIC_SUPABASE_ANON_KEY
3. Run the SQL schema from this page in the Supabase SQL Editor and seed the badges and levels tables
4. Create the award_points and check_badges Edge Functions manually in Supabase Dashboard → Edge Functions
5. Add a useEffect in the GamificationDashboard that subscribes to the Supabase Realtime channel 'badges:{userId}' and shows a shadcn/ui toast on each broadcast

Starter prompt:

```
Build a Next.js gamification dashboard component. Use Supabase with these tables: user_gamification (user_id uuid pk, xp int, level int, current_streak int, longest_streak int, last_active_date date), badges (id, slug, name, description, icon_url, condition_type, condition_value), user_badges (user_id, badge_id, earned_at, pk user_id+badge_id), levels (level int pk, xp_required int, title text). Fetch user_gamification and user_badges server-side via Supabase Server Client. Component GamificationDashboard: XP counter showing current XP and level title (looked up from levels table). Level progress bar using shadcn/ui Progress: fill = ((xp - currentLevelXpRequired) / (nextLevelXpRequired - currentLevelXpRequired)) * 100. Streak section: flame icon (Heroicons fire) colored grey if streak < 3, orange 3-6, red >= 7; show current_streak number and 'Resets in Xh Ym' using dayjs.utc().endOf('day').diff(dayjs.utc(), 'minute'). Badge gallery: CSS Grid 4-col, each badge is a Card showing icon_url (or placeholder), name, and description. Locked badges (not in user_badges) show at opacity-50 with a Lock icon overlay. Unlocked badges show a checkmark and earned_at formatted as 'Earned Mar 4'. Wrap canvas-confetti call in useEffect: useEffect(() => { if (justLeveledUp) { import('canvas-confetti').then(m => m.default({ particleCount: 120, spread: 70 })) } }, [justLeveledUp]) where justLeveledUp is a boolean prop. Add a client-side Supabase Realtime subscription in a useEffect that subscribes to 'badges:{userId}' channel and calls shadcn/ui useToast() with the badge name on each broadcast event.
```

Limitations:

- v0 does not scaffold Edge Functions or pg_cron — award_points and check_badges must be written and deployed manually in the Supabase Dashboard
- Supabase Realtime subscription for live badge toasts requires a custom useEffect that v0 generates as a placeholder but does not fully wire — verify the channel name and event shape manually
- The canvas-confetti one-shot flag must be verified: if justLeveledUp is derived from a prop that re-renders the parent, the confetti will fire more than once

### Custom — fit 4/10, 2-4 weeks

Necessary for complex rule engines (condition chaining, time-limited seasonal events), white-label badge systems where non-technical admins create new badges, or when gamification data must sync bidirectionally to Amplitude or Mixpanel.

1. Design a rule engine data model: conditions table with condition_type, operator (gte, eq, between), and condition_value; rules table linking multiple conditions with AND/OR logic; badge_rules join table
2. Build a condition evaluator function that fetches the user's stats (XP, streak, action counts by type) and tests each condition — returns a list of newly satisfied badge_ids
3. Create an admin UI where non-technical team members can create new badges, set conditions, upload icons, and set availability windows (start_date, end_date for seasonal badges)
4. Implement a Mixpanel or Amplitude event sink: after every award_points invocation, POST a gamification_event to the analytics platform with user_id, action, xp_awarded, and new_level for funnel analysis

Limitations:

- A condition-chaining rule engine requires careful design to avoid combinatorial explosion when evaluating many conditions across many users simultaneously
- Seasonal limited-time badges require start_date and end_date columns on the badges table and a cron job to deactivate expired badges — adds operational complexity

## Gotchas

- **XP counter updates in the database but the UI stays stale** — The Supabase Realtime subscription is created inside the React component but the channel is subscribed to the wrong table name or wrong filter. The AI often subscribes to a channel named 'gamification' but the table is named 'user_gamification', or subscribes without the user_id filter — so all users' updates broadcast to all clients. Fix: Confirm the Realtime subscription target exactly: supabase.channel('xp-update').on('postgres_changes', { event: 'UPDATE', schema: 'public', table: 'user_gamification', filter: `user_id=eq.${userId}` }, callback).subscribe(). Check Supabase Dashboard → Realtime → Inspector to verify the channel is receiving events.
- **The same badge is awarded twice or more** — check_badges is called multiple times in parallel when a user completes two actions in rapid succession. Both Edge Function invocations read the user's stats before either has written the new badge row, so both pass the badge condition check and both attempt to insert the badge — creating duplicate rows. Fix: Add INSERT INTO user_badges (user_id, badge_id, earned_at) VALUES ($1, $2, now()) ON CONFLICT (user_id, badge_id) DO NOTHING to the Edge Function. The PRIMARY KEY (user_id, badge_id) is the authoritative guard — even concurrent inserts will have one succeed and one silently do nothing.
- **Streak resets for users who were active today** — pg_cron is enabled but the timezone offset in the cron expression or the comparison logic is wrong. Users in UTC-5 who complete a lesson at 8 PM local time (01:00 UTC next day) lose their streak because last_active_date was written as yesterday in UTC even though it was today locally. Fix: Always store last_active_date as UTC in the Edge Function: new Date().toISOString().split('T')[0]. Document in the streak widget UI that 'Streak resets at midnight UTC' — this is the single most important user-facing communication to prevent support tickets.
- **Level progress bar shows over 100% or jumps to 0 after leveling up** — The level bar calculates fill as (xp / next_level_threshold) * 100. After leveling up, xp is not reset — it continues accumulating. So immediately after reaching level 2 (100 XP), a user with 150 XP shows 150% on the level 2 bar. The bug is using raw XP instead of XP within the current level. Fix: Calculate fill as: ((xp - currentLevel.xp_required) / (nextLevel.xp_required - currentLevel.xp_required)) * 100. Fetch both the current level row and the next level row from the levels table in the component. Cap the result at 100 with Math.min(fillPct, 100) to handle edge cases.
- **canvas-confetti fires on every React re-render** — The confetti call is placed directly in the component body or in a useEffect without a one-shot flag. Every time the parent component re-renders — including on unrelated state changes — the confetti fires again, flooding the screen and annoying users. Fix: Wrap in useEffect with a one-shot boolean: useEffect(() => { if (justLeveledUp) { confetti({ particleCount: 120, spread: 70 }); setJustLeveledUp(false); } }, [justLeveledUp]). The setJustLeveledUp(false) immediately after firing prevents re-runs on subsequent renders.

## Best practices

- Store point values in a Supabase config table (event_type TEXT, points_awarded INT) rather than hardcoding them in the Edge Function — changing reward values then requires a database update, not a code deployment
- Award badges with ON CONFLICT (user_id, badge_id) DO NOTHING — this is the only reliable way to prevent duplicate badges under concurrent invocations
- Calculate level bar fill using current and next level XP minimums from the levels table, not raw XP — raw XP breaks the display immediately after a level-up
- Run the streak reset cron at 00:05 UTC, not exactly 00:00, to avoid clock-edge race conditions with the award_points Edge Function that might fire at exactly midnight
- Always show the earn condition text for locked badges — 'Complete 5 lessons' is the motivation to return; a greyed-out badge with no explanation is a dead end
- Subscribe to Supabase Realtime on 'badges:{user_id}' (user-scoped channel) rather than a global channel — broadcasting badge events to all users is both a data privacy concern and a performance waste
- Test the badge evaluation function with a seeded user_xp_log that triggers every badge condition before launch — in production, you cannot un-award a badge without a manual database operation
- Document the streak reset timezone (UTC) prominently in the streak widget — this generates more support tickets than any other gamification edge case

## Frequently asked questions

### How many XP actions are too many — will users game the system?

Start with 4-6 action types maximum. More than that and users optimise for the easiest action rather than the most valuable one. Add server-side rate limiting (maximum 1 award per action type per 60 seconds) to prevent click-farming. Check user_xp_log for velocity anomalies in the first week after launch.

### Can I let admins create new badges without a developer?

With Lovable or v0, no — the badges table has rows but adding a new badge requires a database INSERT, which is a developer task unless you build an admin UI. For admin-editable badges, build a protected /admin/badges page that lets your team fill a form that inserts rows into the badges table via a Server Action.

### How do I handle users who change timezones and affect their streak?

Store and evaluate streaks entirely in UTC — never use the user's local timezone for streak calculation. Document in the streak widget: 'Streaks reset at midnight UTC'. If a user travels and their 'day' starts at a different time, the UTC reset is the authoritative clock. Trying to personalise reset time per timezone creates edge cases that are not worth the engineering cost for most apps.

### Can I add a public leaderboard alongside the badge system?

Yes — the user_gamification.xp column is the score source. Add a leaderboard_view materialized view with RANK() OVER (ORDER BY xp DESC) and refresh it via pg_cron every 5 minutes. The Leaderboard feature page on this site covers the full implementation including the current-user sticky row.

### How do I make the badge pop-up feel satisfying without being annoying?

Show the toast notification for 4 seconds, not indefinitely. Play a subtle sound if your app has audio (an HTML5 Audio element, 50ms clip). Fire canvas-confetti with 80-120 particles and a 0.6-second duration — longer feels gratuitous. Never show more than one badge notification at a time; queue them if multiple badges are earned simultaneously.

### Will gamification slow down my app if every action triggers a database write?

The award_points Edge Function adds roughly 50-150ms to the action that triggers it. For actions where speed matters (e.g. a real-time game tap), call award_points asynchronously after the main action completes — fire and forget with no await. For actions where the XP display matters immediately (e.g. lesson completion), await the Edge Function and update the UI with the response.

### How do I run a limited-time double-XP event?

Add a multiplier column to the event_point_values config table with a default of 1.0. Add a multiplier_active_until TIMESTAMPTZ column. In award_points, before calculating points, check if multiplier_active_until > now() and multiply the base points by the multiplier value. To start a double-XP event, update the row: SET multiplier = 2.0, multiplier_active_until = '2026-12-31 23:59:59Z'.

### Can users see each other's badges on their profiles?

The current RLS policy allows users to read only their own user_badges rows. To make badges public on profile pages, change the SELECT policy to: using (true). Fetch another user's badges by querying user_badges WHERE user_id = profileUserId — no auth.uid() filter. Make sure the badge icon URLs are publicly accessible (Supabase Storage public bucket or an external CDN).

---

Source: https://www.rapidevelopers.com/app-features/gamification
© RapidDev — https://www.rapidevelopers.com/app-features/gamification