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

- Tool: App Features
- Last updated: July 2026

## TL;DR

A mobile leaderboard needs four pieces: a Supabase materialized view with RANK() that aggregates scores, a Flutter list with animated row reordering, a current-user sticky row showing rank even when outside the top N, and Firebase Cloud Messaging for rank-change push notifications. With FlutterFlow you can ship this in 3-5 hours for $0/month — costs only rise past 10,000 users when Supabase Pro is needed for realtime connection headroom.

## What a Leaderboard Feature Actually Is

A leaderboard ranks users by score, XP, or completions and shows them where they stand relative to everyone else — the social proof mechanism that turns individual progress into competitive engagement. The product decisions are: what entity is scored (points, challenges completed, purchases), whether the leaderboard is global or scoped to a group, how often ranks refresh (real-time vs every 5 minutes), and what happens when the user is ranked #247 — do they still see their position? The ranked list is the easy half; the current-user sticky row, the animated rank transitions, and the deep-link from a push notification are where first builds reliably break.

## Anatomy of the Feature

Six components. FlutterFlow handles the list widget and basic Supabase queries well on a first prompt. The current-user sticky row, FCM deep-link, and animated reordering reliably require custom Dart action blocks.

- **Leaderboard list widget** (ui): Flutter ListView.builder rendering each ranked user as a row with CircleAvatar (from Supabase Storage URL), Text for rank number, Text for username, and Text for total score. Animated row reordering uses the implicitly_animated_list package or AnimatedList — provides smooth slide transitions when rank positions change on a Realtime update.
- **Score aggregation view** (data): Supabase PostgreSQL materialized view that aggregates scores: SELECT user_id, username, avatar_url, SUM(points) AS total_score, RANK() OVER (ORDER BY SUM(points) DESC) AS rank FROM user_events JOIN users ON users.id = user_events.user_id GROUP BY user_id, username, avatar_url. Refreshed every 5 minutes via Supabase pg_cron (REFRESH MATERIALIZED VIEW CONCURRENTLY leaderboard_view) to avoid locking the table during high-write periods.
- **Current user rank query** (data): A separate Supabase query that runs alongside the top-N fetch: SELECT rank, total_score FROM leaderboard_view WHERE user_id = auth.uid(). Returns the current user's rank and score regardless of whether they appear in the top 50. Displayed as a sticky footer row with distinct background color so it stands out from the ranked list above.
- **Realtime subscription** (backend): Supabase Realtime channel subscribed to the underlying user_events table (INSERT events). When a new score event arrives, the Flutter StreamBuilder triggers a re-fetch of the leaderboard_view. Debounced to 2 seconds to avoid thrashing the UI during burst scoring (e.g. rapid tap games).
- **Time-scope filter** (backend): FlutterFlow page state variable (scopeFilter: 'all_time' | 'weekly' | 'daily') that controls the Supabase query. Weekly and daily scopes add .gte('created_at', startOfWeek) to the user_events query before aggregation. Week boundaries are calculated in UTC to ensure consistent resets.
- **Push notification deep link** (service): Firebase Cloud Messaging (FCM) via the firebase_messaging Flutter package. When a user's rank improves (detected by comparing old vs new rank after a pg_cron refresh), a Supabase Edge Function sends an FCM notification payload including the new rank and a deep link URL. The Flutter app handles the notification in FirebaseMessaging.onMessageOpenedApp and navigates to LeaderboardPage with the current user's row scrolled into view.

## Data model

Three tables plus one materialized view. The materialized view is refreshed by pg_cron every 5 minutes, keeping rank calculations fast even at 10,000+ users. Paste this into the Supabase SQL Editor and run it before building:

```sql
create table public.users (
  id uuid primary key references auth.users(id) on delete cascade,
  username text not null unique,
  avatar_url text
);

create table public.user_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.users(id) on delete cascade not null,
  event_type text not null,
  points int not null default 0,
  created_at timestamptz not null default now()
);

-- RLS
alter table public.user_events enable row level security;

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

create policy "Users insert own events"
  on public.user_events for insert
  with check (auth.uid() = user_id);

-- Materialized leaderboard view
create materialized view public.leaderboard_view as
  select
    u.id as user_id,
    u.username,
    u.avatar_url,
    coalesce(sum(e.points), 0) as total_score,
    rank() over (order by coalesce(sum(e.points), 0) desc) as rank
  from public.users u
  left join public.user_events e on e.user_id = u.id
  group by u.id, u.username, u.avatar_url
with data;

create unique index leaderboard_view_user_idx on public.leaderboard_view (user_id);

-- Allow all authenticated users to read the leaderboard
create policy "Auth users read leaderboard"
  on public.leaderboard_view for select
  using (auth.role() = 'authenticated');

-- pg_cron refresh every 5 minutes (enable pg_cron extension first)
select cron.schedule(
  'refresh-leaderboard',
  '*/5 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY public.leaderboard_view'
);

-- Index for time-scoped queries on user_events
create index user_events_time_idx on public.user_events (created_at, user_id);
create index user_events_user_score_idx on public.user_events (user_id, points);
```

The UNIQUE index on leaderboard_view (user_id) is required for REFRESH CONCURRENTLY — without it, Postgres refuses to perform a concurrent refresh. Enable pg_cron in Supabase Dashboard → Database → Extensions before running the cron schedule call.

## Build paths

### Flutterflow — fit 5/10, 3-5 hours

The primary path for a mobile leaderboard: native Flutter list widgets with smooth animations, direct Supabase Backend Query with Realtime stream, and pg_cron materialized view for scale-safe aggregation.

1. Create a FlutterFlow project connected to your Supabase project and import the leaderboard_view and user_events tables
2. Add a LeaderboardPage with a Column layout: scope filter row (three TextButton tabs for All Time / This Week / Today) at the top, a ListView bound to a Backend Query on leaderboard_view ordered by rank ASC with LIMIT 50, then a sticky Container at the bottom showing the current user's rank from a separate Backend Query filtered by user_id = currentUserUid
3. Switch the main leaderboard Backend Query to Realtime Stream mode — it will rebuild the list whenever user_events receives a new INSERT (re-fetch the view)
4. In the Action editor for each row, add a conditional: if the row's user_id equals currentUserUid, highlight it with a different background color even within the top 50 list
5. Add a custom action block for FCM deep-link handling: call FirebaseMessaging.instance.getInitialMessage() in the app initialization action and navigate to LeaderboardPage if the notification payload contains 'screen': 'leaderboard'
6. Enable pg_cron in Supabase Dashboard → Database → Extensions and run the cron schedule SQL from this page to activate the 5-minute materialized view refresh

Limitations:

- Code export requires FlutterFlow Pro ($70/mo) — the free plan runs only in the FlutterFlow preview app
- Animated row reordering (rows sliding up/down on rank change) requires a Dart custom code block using implicitly_animated_list — FlutterFlow's visual builder does not expose AnimatedList
- FCM push notification wiring for rank-change alerts requires a Supabase Edge Function that FlutterFlow cannot generate; it must be written separately in the Supabase Dashboard → Edge Functions editor

### Lovable — fit 2/10, 5-8 hours

Can generate a web-based leaderboard in React with Supabase Realtime — valid for a web dashboard or admin view, but not a mobile-native leaderboard. Use FlutterFlow for the mobile path.

1. Create a Lovable project with Lovable Cloud and paste the prompt below for a web leaderboard page
2. Verify the Supabase materialized view is created and pg_cron is enabled for the 5-minute refresh
3. Test Realtime updates by opening two browser windows and scoring points in one — the other should update the rank list within the refresh interval
4. Add Framer Motion animations for rank position changes if needed via a follow-up prompt

Starter prompt:

```
Build a web leaderboard page in React using Supabase. Read from a materialized view called leaderboard_view with columns: user_id, username, avatar_url, total_score, rank. Query: .from('leaderboard_view').select('*').order('rank', { ascending: true }).limit(50). Show each row as a card with: rank number (gold color for #1, silver for #2, bronze for #3), CircleAvatar from avatar_url with initials fallback, username, total_score formatted with commas. Below the top 50, show the current user's row in a sticky footer: .from('leaderboard_view').select('rank, total_score').eq('user_id', session.user.id). Add three filter tabs: All Time (no date filter), This Week (.gte('created_at', startOfISOWeek(new Date()))), Today (.gte('created_at', startOfDay(new Date()))). Subscribe to Supabase Realtime on the user_events table for INSERT events; on each event, re-fetch the leaderboard_view. Animate rank changes with Framer Motion layout animation. Show a loading skeleton while the initial fetch is in progress. Show an empty state if no scores exist yet.
```

Limitations:

- This is a web-only path — the result is a browser leaderboard, not a native mobile experience
- Animated rank shuffling in React with Framer Motion requires explicit key-based layout animations and often needs a follow-up prompt to get right
- The Lovable preview iframe may not reflect true mobile scroll performance — test on a real phone via the published URL

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

Needed when the leaderboard drives real prizes or monetary rewards requiring verified score integrity, anti-cheat logic detecting bot-like patterns, or multi-region resets with timezone-adjusted week boundaries.

1. Design a weighted scoring system: different event types award different points (e.g. 'lesson_complete': 10 pts, 'challenge_won': 50 pts, 'daily_login': 5 pts) stored in an event_point_values config table rather than hardcoded in the client
2. Implement a time-decay function that reduces the effective weight of older events: effective_score = points * exp(-decay_rate * days_since_event) for recency-weighted leaderboards
3. Build an Upstash Redis caching layer for the top-100 list — serve reads from Redis (updated on every score event) rather than querying the materialized view on every app open
4. Implement anti-cheat server-side: reject event_type + user_id combinations that appear more than N times per minute, and flag accounts whose score velocity exceeds 3 standard deviations from the mean

Limitations:

- Anti-cheat logic and score integrity auditing add 1-2 weeks of additional complexity beyond the core leaderboard
- Redis caching requires Upstash Pro ($0.2/100K commands approx) at high read volume — but eliminates database load for leaderboard reads entirely

## Gotchas

- **Leaderboard rank never updates in real time** — The Supabase Realtime subscription is placed on the materialized view (leaderboard_view) rather than the source table (user_events). Materialized views are regular PostgreSQL relations — they do not emit Realtime postgres_changes events because they are not updated row-by-row. The subscription fires on nothing, and the list never refreshes. Fix: Subscribe to user_events (the source table) for INSERT events via Supabase Realtime. When an event fires, re-query the leaderboard_view in the Flutter StreamBuilder callback. The view returns pre-calculated ranks so the re-query is fast. Alternatively, poll the view every 30 seconds with a Flutter Timer if Realtime connection count is a concern.
- **Current user's rank row is missing when they are outside the top 50** — The leaderboard query fetches LIMIT 50 rows. If the current user is ranked #247, they do not appear in the results — and without a separate query for their own rank, the UI shows no indication of where they stand. This is the most common UX complaint about leaderboards built with AI tools. Fix: Run two queries in parallel: one for the top 50 (leaderboard_view ORDER BY rank ASC LIMIT 50), and one for the current user's own row (leaderboard_view WHERE user_id = auth.uid()). In the Flutter widget, show the top-50 list, then add a sticky footer Container that shows the user's rank row — but only if their user_id does not already appear in the top 50.
- **AnimatedList crashes on first load** — FlutterFlow's AnimatedList widget requires an initial data snapshot to exist before the Realtime subscription starts delivering updates. If the Realtime stream fires before the initial fetch completes — common on fast connections — the widget throws a RangeError because the list has 0 items but the animation tries to insert at position N. Fix: Fetch the initial leaderboard list synchronously (Backend Query in page load action), store it in a page state variable, and only then attach the Realtime stream. Show a loading shimmer (FlutterFlow Skeleton widget) while the initial fetch is in progress.
- **Scores inflate from rapid double-taps** — The Flutter action that awards points fires on every button tap, including rapid double-taps or network-slow multiple taps. A single user action inserts 3-5 point events in 200ms — inflating their score and rank in a way that is invisible to the player but obvious in the raw data. Fix: Add two layers of protection: client-side (disable the scoring button for 1 second after tap using a page state boolean), and server-side (in the Supabase INSERT policy or an Edge Function, check whether a row with the same event_type + user_id exists within the last 1 second before inserting).
- **FCM deep link navigates to the wrong screen on cold start** — FirebaseMessaging.onMessageOpenedApp only fires when the user opens a notification while the app is running in the background. If the app is completely closed (cold start) when the notification arrives, this stream never fires — the app opens to the default home screen and the user has to navigate to the leaderboard manually. Fix: Add FirebaseMessaging.instance.getInitialMessage() in the Flutter app's main() function (before runApp). If it returns a non-null RemoteMessage with 'screen': 'leaderboard' in the data payload, push LeaderboardPage as the initial route.

## Best practices

- Always run two parallel queries — top-N list and current user's own rank — and merge them in the widget rather than trying to fetch a variable-size list that always includes the current user
- Use REFRESH MATERIALIZED VIEW CONCURRENTLY (not without CONCURRENTLY) so the pg_cron refresh does not block reads during the 100-500ms refresh window
- Create a UNIQUE index on leaderboard_view (user_id) before attempting a concurrent refresh — Postgres requires it and will throw an error without it
- Debounce the Realtime-triggered re-query by 2 seconds during burst scoring events — otherwise a rapid-point game causes a UI re-render on every single tap
- Store avatar_url in the users table (copied from auth.users metadata on signup) so the leaderboard view join never needs to hit the auth schema at query time
- Document that leaderboard resets (weekly, daily) happen at midnight UTC and show this in the filter tab label — users in UTC-8 will see the weekly reset at 4 PM local time and need the context
- Cap the top-N list at 100 rows maximum — a leaderboard showing 10,000 rows is not a leaderboard, it is a user directory; paginated deep browsing belongs in a separate analytics view

## Frequently asked questions

### How do I make the leaderboard update in real time without the user refreshing?

Subscribe to Supabase Realtime on the user_events table for INSERT events. When a new event fires, re-query the leaderboard_view (which has pre-calculated RANK values). Wrap the Flutter list in a StreamBuilder so it rebuilds automatically. Note: you subscribe to user_events, not the materialized view — materialized views do not emit Realtime events.

### How do I show a user their own rank when they are not in the top 50?

Run a second Supabase query alongside the top-50 fetch: SELECT rank, total_score FROM leaderboard_view WHERE user_id = auth.uid(). Show this result in a sticky footer row at the bottom of the leaderboard screen with a distinct background. Only show the footer if the user's user_id does not already appear in the top-50 results.

### Can I have separate leaderboards for different groups or teams?

Yes — add a group_id column to user_events and create a separate materialized view filtered by group_id. Alternatively, add a WHERE clause to the leaderboard query based on the user's team membership. If groups are dynamic (users can switch teams), ensure historical events carry the group_id that was active when the event occurred.

### How do I prevent users from gaming the score system?

Add two guards: client-side (disable the scoring button for 1 second after each tap) and server-side (in the Supabase Edge Function or INSERT policy, reject events from the same user of the same type within 1 second). For competitive prize leaderboards, log every event with a server timestamp and run a nightly anomaly detection query flagging users whose scoring velocity exceeds 3 standard deviations from the mean.

### Can I send a push notification when a user moves up in the rankings?

Yes — after each pg_cron leaderboard refresh, a Supabase Edge Function compares old vs new rank for each user. If rank improved, it sends an FCM notification via the Firebase Admin SDK with the new rank in the payload. Set up the Edge Function in Supabase Dashboard → Edge Functions and trigger it from the cron job.

### How do I add weekly and monthly leaderboard resets?

Create separate materialized views for each time scope — leaderboard_weekly_view adds WHERE created_at >= date_trunc('week', now()) to the user_events join. Refresh them via separate pg_cron jobs. In the Flutter UI, switch between views based on the selected tab. Weekly views reset automatically because the WHERE clause advances with the clock — no explicit reset needed.

### How do I show animated rank changes when positions shift?

Use the implicitly_animated_list package in Flutter: pass it a sorted list keyed by user_id and it automatically animates items sliding up or down when their position changes. On each Realtime event, re-fetch the leaderboard_view and update the Flutter widget's data source — the animation handles the rest.

---

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