How to Add a Content Challenge Tracker to Your App — Copy-Paste Prompts Inside

1-- Challenges (admin-created or user-created depending on your config)
2create table public.challenges (
3  id uuid primary key default gen_random_uuid(),
4  title text not null,
5  frequency text not null check (frequency in ('daily', 'weekly')),
6  start_date date not null,
7  end_date date not null,
8  created_by uuid references auth.users(id) on delete set null,
9  max_participants int,
10  created_at timestamptz default now()
11);
12
13-- One row per user per day per challenge
14create table public.challenge_entries (
15  id uuid primary key default gen_random_uuid(),
16  challenge_id uuid references public.challenges(id) on delete cascade not null,
17  user_id uuid references auth.users(id) on delete cascade not null,
18  entry_date date not null,
19  created_at timestamptz default now(),
20  constraint uq_challenge_user_date unique (challenge_id, user_id, entry_date)
21);
22
23-- Streak tallies — updated by streak_calculator trigger
24create table public.user_streaks (
25  challenge_id uuid references public.challenges(id) on delete cascade not null,
26  user_id uuid references auth.users(id) on delete cascade not null,
27  current_streak int not null default 0,
28  longest_streak int not null default 0,
29  last_entry_date date,
30  primary key (challenge_id, user_id)
31);
32
33-- FCM device tokens, refreshed on every app launch
34create table public.user_devices (
35  id uuid primary key default gen_random_uuid(),
36  user_id uuid references auth.users(id) on delete cascade not null,
37  fcm_token text not null,
38  platform text not null check (platform in ('ios', 'android')),
39  timezone text not null default 'UTC',
40  updated_at timestamptz default now(),
41  constraint uq_user_fcm unique (user_id, fcm_token)
42);
43
44-- Leaderboard view (read-safe for all authenticated users)
45create or replace view public.v_leaderboard as
46  select
47    us.challenge_id,
48    us.user_id,
49    us.current_streak,
50    us.longest_streak,
51    row_number() over (partition by us.challenge_id order by us.current_streak desc) as rank
52  from public.user_streaks us
53  order by us.current_streak desc
54  limit 100;
55
56-- RLS
57alter table public.challenges enable row level security;
58alter table public.challenge_entries enable row level security;
59alter table public.user_streaks enable row level security;
60alter table public.user_devices enable row level security;
61
62-- Challenges: anyone authenticated can read; only admins/creators can insert
63create policy "Authenticated users can read challenges"
64  on public.challenges for select
65  using (auth.role() = 'authenticated');
66
67create policy "Creators can insert challenges"
68  on public.challenges for insert
69  with check (auth.uid() = created_by);
70
71-- Entries: own rows only for insert; own rows only for select
72create policy "Users can insert own entries"
73  on public.challenge_entries for insert
74  with check (auth.uid() = user_id);
75
76create policy "Users can read own entries"
77  on public.challenge_entries for select
78  using (auth.uid() = user_id);
79
80-- Streaks: own rows only for write; ALL rows readable for leaderboard
81create policy "Users can read all streaks (leaderboard)"
82  on public.user_streaks for select
83  using (true);
84
85create policy "System can upsert streaks"
86  on public.user_streaks for all
87  using (auth.uid() = user_id)
88  with check (auth.uid() = user_id);
89
90-- Devices: own rows only
91create policy "Users manage own devices"
92  on public.user_devices for all
93  using (auth.uid() = user_id)
94  with check (auth.uid() = user_id);
95
96-- Index for fast streak lookup and entry calendar queries
97create index entries_user_challenge_date_idx
98  on public.challenge_entries (user_id, challenge_id, entry_date desc);
99
100create index devices_user_idx
101  on public.user_devices (user_id);