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

1create table public.levels (
2  level int primary key,
3  xp_required int not null,
4  title text
5);
6
7insert into public.levels (level, xp_required, title) values
8  (1, 0, 'Beginner'),
9  (2, 100, 'Explorer'),
10  (3, 300, 'Achiever'),
11  (4, 600, 'Expert'),
12  (5, 1000, 'Master');
13
14create table public.user_gamification (
15  user_id uuid primary key references auth.users(id) on delete cascade,
16  xp int not null default 0,
17  level int not null default 1 references public.levels(level),
18  current_streak int not null default 0,
19  longest_streak int not null default 0,
20  last_active_date date
21);
22
23create table public.badges (
24  id uuid primary key default gen_random_uuid(),
25  slug text not null unique,
26  name text not null,
27  description text,
28  icon_url text,
29  condition_type text not null,
30  condition_value int not null
31);
32
33create table public.user_badges (
34  user_id uuid references auth.users(id) on delete cascade not null,
35  badge_id uuid references public.badges(id) on delete cascade not null,
36  earned_at timestamptz not null default now(),
37  primary key (user_id, badge_id)
38);
39
40create table public.user_xp_log (
41  id uuid primary key default gen_random_uuid(),
42  user_id uuid references auth.users(id) on delete cascade not null,
43  action text not null,
44  xp_awarded int not null,
45  created_at timestamptz not null default now()
46);
47
48-- RLS
49alter table public.user_gamification enable row level security;
50alter table public.user_badges enable row level security;
51alter table public.user_xp_log enable row level security;
52
53create policy "Users read own gamification state"
54  on public.user_gamification for select
55  using (auth.uid() = user_id);
56
57create policy "Users update own gamification state"
58  on public.user_gamification for update
59  using (auth.uid() = user_id);
60
61create policy "Users insert own gamification row"
62  on public.user_gamification for insert
63  with check (auth.uid() = user_id);
64
65create policy "Badges are public"
66  on public.badges for select
67  using (true);
68
69create policy "Users read own badges"
70  on public.user_badges for select
71  using (auth.uid() = user_id);
72
73create policy "Users read own xp log"
74  on public.user_xp_log for select
75  using (auth.uid() = user_id);
76
77-- Indexes
78create index user_xp_log_user_idx on public.user_xp_log (user_id, created_at desc);
79create index user_xp_log_action_idx on public.user_xp_log (user_id, action);
80
81-- pg_cron streak reset at 00:05 UTC daily
82-- (run after enabling pg_cron extension in Dashboard → Database → Extensions)
83select cron.schedule(
84  'streak-reset',
85  '5 0 * * *',
86  $$
87    update public.user_gamification
88    set
89      longest_streak = greatest(longest_streak, current_streak),
90      current_streak = 0,
91      last_active_date = null
92    where last_active_date < current_date - 1
93  $$
94);