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

- Tool: App Features
- Last updated: July 2026

## TL;DR

A content challenge tracker needs a check-in mechanism, a streak engine in Postgres, push reminders via Firebase Cloud Messaging, and a leaderboard view. With FlutterFlow and Supabase you can ship a fully working system in 1–2 days for $0/month up to ~1,000 users. The hard parts — timezone-safe streak calculation and FCM token refresh — are handled server-side in a Supabase Edge Function plus a Postgres function.

## What a Content Challenge Tracker Actually Is

A content challenge tracker turns a passive habit into a social game: users commit to posting daily or weekly, a streak counter rewards consistency, and a leaderboard shows how they rank against peers. The mechanics — check-in button, streak calculation, push reminder before deadline, shareable badge on completion — are a proven retention loop used by Duolingo, Strava, and every fitness app with a following. The implementation has three distinct layers: a mobile UI built in FlutterFlow for the calendar strip, progress rings, and leaderboard; a Postgres function (streak_calculator) that counts consecutive days accurately across timezones; and a Supabase Edge Function that sends FCM push notifications 2 hours before the daily deadline. Skipping any layer produces a tracker that looks right but counts streaks wrong or goes silent when users expect a nudge.

## Anatomy of the Feature

Six components. FlutterFlow builds the two UI components correctly on a single prompt. The streak engine and push scheduler are the parts that require direct work in Supabase — AI tools cannot configure pg_cron or FCM for you.

- **Challenge calendar strip** (ui): Horizontal scrollable date strip built with Flutter's ListView.builder and DateUtils. Each day cell shows a status icon — pending ring, green checkmark for complete, grey X for missed. Today's cell is highlighted. Tapping an incomplete today-cell opens the check-in modal; past cells are read-only.
- **Check-in action layer** (backend): Records a timestamped entry in the Supabase challenge_entries table. The insert is idempotent thanks to a UNIQUE(challenge_id, user_id, entry_date) constraint — double-taps and retry logic cannot inflate the count. After a successful insert, Supabase Realtime broadcasts the new count to any open admin or leaderboard views.
- **Streak engine** (backend): A Postgres function named streak_calculator fires as an AFTER INSERT trigger on challenge_entries. It counts consecutive calendar days with entries going backward from today, writes current_streak and longest_streak to the user_streaks table, and sets last_entry_date. If the streak breaks, it triggers the Edge Function to send a broken-streak notification via FCM.
- **Progress dashboard** (ui): A Flutter page using the fl_chart package (free, pub.dev) to render a circular progress ring showing completion percentage. Below the ring: three stat chips — longest streak, current streak, total check-ins — populated from user_streaks and a COUNT query on challenge_entries via Supabase .select().
- **Leaderboard** (data): A Supabase database view (v_leaderboard) ordered by current_streak DESC, joining user_streaks with auth.users to expose a display name. The FlutterFlow page queries this view, renders rows in a ListView with rank number badges (1st = gold, 2nd = silver, 3rd = bronze), and supports pull-to-refresh and pagination limited to top 100.
- **Push notification scheduler** (service): A Supabase Edge Function (Deno) triggered by pg_cron on a schedule (e.g. daily at 19:00 UTC). It queries challenge_entries to find users who have NOT checked in yet today, looks up their FCM device tokens from user_devices, and calls the FCM v1 API to send a reminder push. Device tokens are refreshed on every app launch and stored via upsert.

## Data model

Five tables cover the full feature. Run this in the Supabase SQL editor (Dashboard → SQL Editor → New query). The UNIQUE constraint on challenge_entries is what prevents double check-ins — do not omit it.

```sql
-- Challenges (admin-created or user-created depending on your config)
create table public.challenges (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  frequency text not null check (frequency in ('daily', 'weekly')),
  start_date date not null,
  end_date date not null,
  created_by uuid references auth.users(id) on delete set null,
  max_participants int,
  created_at timestamptz default now()
);

-- One row per user per day per challenge
create table public.challenge_entries (
  id uuid primary key default gen_random_uuid(),
  challenge_id uuid references public.challenges(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  entry_date date not null,
  created_at timestamptz default now(),
  constraint uq_challenge_user_date unique (challenge_id, user_id, entry_date)
);

-- Streak tallies — updated by streak_calculator trigger
create table public.user_streaks (
  challenge_id uuid references public.challenges(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  current_streak int not null default 0,
  longest_streak int not null default 0,
  last_entry_date date,
  primary key (challenge_id, user_id)
);

-- FCM device tokens, refreshed on every app launch
create table public.user_devices (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade not null,
  fcm_token text not null,
  platform text not null check (platform in ('ios', 'android')),
  timezone text not null default 'UTC',
  updated_at timestamptz default now(),
  constraint uq_user_fcm unique (user_id, fcm_token)
);

-- Leaderboard view (read-safe for all authenticated users)
create or replace view public.v_leaderboard as
  select
    us.challenge_id,
    us.user_id,
    us.current_streak,
    us.longest_streak,
    row_number() over (partition by us.challenge_id order by us.current_streak desc) as rank
  from public.user_streaks us
  order by us.current_streak desc
  limit 100;

-- RLS
alter table public.challenges enable row level security;
alter table public.challenge_entries enable row level security;
alter table public.user_streaks enable row level security;
alter table public.user_devices enable row level security;

-- Challenges: anyone authenticated can read; only admins/creators can insert
create policy "Authenticated users can read challenges"
  on public.challenges for select
  using (auth.role() = 'authenticated');

create policy "Creators can insert challenges"
  on public.challenges for insert
  with check (auth.uid() = created_by);

-- Entries: own rows only for insert; own rows only for select
create policy "Users can insert own entries"
  on public.challenge_entries for insert
  with check (auth.uid() = user_id);

create policy "Users can read own entries"
  on public.challenge_entries for select
  using (auth.uid() = user_id);

-- Streaks: own rows only for write; ALL rows readable for leaderboard
create policy "Users can read all streaks (leaderboard)"
  on public.user_streaks for select
  using (true);

create policy "System can upsert streaks"
  on public.user_streaks for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Devices: own rows only
create policy "Users manage own devices"
  on public.user_devices for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Index for fast streak lookup and entry calendar queries
create index entries_user_challenge_date_idx
  on public.challenge_entries (user_id, challenge_id, entry_date desc);

create index devices_user_idx
  on public.user_devices (user_id);
```

The user_streaks SELECT policy uses USING (true) so any authenticated user can read all participants' streaks — this is what powers the leaderboard. INSERT and UPDATE remain restricted to own rows. The streak_calculator Postgres function (not shown here) must run with security definer privileges to upsert into user_streaks on behalf of the triggering user.

## Build paths

### Lovable — fit 2/10, 2-3 days

Lovable can scaffold the Supabase schema and a React-based progress dashboard, but delivers a PWA — no native Flutter calendar strip or FCM push. Only viable if 'mobile' means mobile browser.

1. Create a new Lovable project with Lovable Cloud enabled so Supabase auth and tables are provisioned automatically
2. Paste the prompt below; Lovable will generate the check-in page, streak counter display, and leaderboard table
3. Open Supabase Dashboard and manually add the streak_calculator Postgres trigger — Lovable does not generate triggers
4. Configure push reminders separately via Web Push API and service workers (FCM is not wired by Lovable automatically)
5. Publish and test check-in flow on a real mobile browser over HTTPS; confirm the UNIQUE constraint prevents double check-ins

Starter prompt:

```
Build a content challenge tracker as a mobile-first PWA. Use Supabase as the backend. Create these tables in Supabase: challenges (id, title, frequency daily/weekly, start_date, end_date, created_by), challenge_entries (id, challenge_id, user_id, entry_date DATE — store in user local timezone — with UNIQUE(challenge_id, user_id, entry_date)), user_streaks (challenge_id, user_id, current_streak, longest_streak, last_entry_date). UI: a horizontal scrollable calendar strip for the current month showing each day as pending/complete/missed using status dots; a large streak counter with a flame emoji that shows the current_streak value from user_streaks; a circular progress ring (use a CSS conic-gradient) showing completion percentage; a leaderboard list sorted by current_streak descending. Check-in button: on tap, insert a row into challenge_entries with today's date in the user's local timezone (use new Date().toLocaleDateString('en-CA') to get YYYY-MM-DD in local time). If Supabase returns error code 23505 (unique violation), show a toast 'Already checked in today'. Loading skeleton while data loads. Empty state if no active challenge with a 'Browse challenges' CTA. Handle all three UI states: loading, empty, and loaded. Enable RLS so users can only write their own entries but read all user_streaks rows for the leaderboard.
```

Limitations:

- No native push notifications — Lovable PWA requires a separate Web Push setup with VAPID keys, which is not auto-configured
- Streak calculation logic must be implemented as a Postgres trigger manually in Supabase Dashboard — Lovable does not generate triggers or pg_cron jobs
- No native calendar strip widget — the CSS-based alternative is functional but lacks the native feel of Flutter's ListView.builder
- Limited offline support; missed connectivity will lose a check-in attempt

### Flutterflow — fit 5/10, 1-2 days

Best fit for this mobile-only feature. FlutterFlow's visual Flutter builder handles the calendar UI natively, wires Supabase queries visually, and configures FCM push through the Firebase settings panel — no separate SDK setup required.

1. Create a new FlutterFlow project; under Settings → Firebase, connect your Firebase project to enable FCM push notifications
2. Add Supabase as the backend under Settings → Supabase and run the SQL schema from this page in the Supabase SQL Editor
3. Build the challenge home page: add a horizontal ListView for the calendar strip, a Text widget bound to user_streaks.current_streak, and a fl_chart RadialBarChart for the progress ring — bind all three to Supabase queries
4. Add a Button labeled 'Check In Today' with an Action: Supabase Insert into challenge_entries; add error handling via a Custom Action that catches Postgres error 23505 and shows a SnackBar 'Already checked in today'
5. In the Action editor, wire up Firebase Messaging to request push permission on first launch and call FirebaseMessaging.instance.getToken(), then upsert the token into user_devices
6. Configure the Supabase Edge Function for daily push reminders in the Supabase Dashboard under Edge Functions; schedule it via pg_cron under Database → Extensions

Limitations:

- The streak_calculator Postgres trigger and pg_cron schedule must be written directly in the Supabase SQL editor — FlutterFlow has no visual interface for Postgres functions or scheduled jobs
- Leaderboard pagination beyond the first page requires a custom Dart action; FlutterFlow's built-in Supabase query does not expose range-based pagination natively
- Code export for streak engine customization (grace periods, weighted scoring) requires the Pro plan at $70/mo
- FCM v1 API in the Edge Function requires a Firebase service account JSON key — store it as a Supabase Secret, not hardcoded

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

Full Flutter + Supabase + pg_cron build is the right call when challenge rules are complex — multi-phase milestones, team streaks, grace periods, or wearable auto-verification — and an AI scaffold would need more rework than it saves.

1. Flutter app with Supabase Flutter SDK: challenge calendar as a custom StatefulWidget using DateTime arithmetic and a Supabase stream for live check-in counts
2. streak_calculator implemented as a Postgres function with AT TIME ZONE conversion using the user's stored timezone — handles DST and midnight rollover correctly
3. pg_cron job in Supabase triggering the FCM Edge Function nightly; the function filters to users not yet checked in and respects per-challenge notification opt-out flags stored in a user_challenge_prefs table
4. Offline-first check-in queue using SQLite (sqflite) on device, with a sync worker that replays queued entries when connectivity returns and deduplicates via the UNIQUE constraint

Limitations:

- Significantly higher upfront cost compared to FlutterFlow scaffold
- Timezone edge cases around DST transitions and user travel require dedicated QA with date-mocked unit tests
- FCM service account rotation must be documented and operationalized — a lapsed key silently stops all push notifications

## Gotchas

- **Streaks reset at the wrong time due to UTC vs local date mismatch** — Supabase stores entry_date as a DATE, which defaults to UTC. A user in UTC-8 who checks in at 11:00pm local time is actually past midnight UTC — their entry lands on the next UTC calendar day. The streak engine then sees a gap and resets the streak, even though the user checked in before their local midnight deadline. This is the most common complaint in challenge apps and impossible to debug without timezone logging. Fix: Store entry_date in the user's local timezone. On the Flutter side, use DateTime.now().toLocal() and format as YYYY-MM-DD before inserting. Store the user's IANA timezone string (e.g. 'America/Los_Angeles') in user_devices.timezone. In the streak_calculator Postgres function, convert timestamps using AT TIME ZONE with the stored timezone string before comparing dates.
- **Duplicate check-in entries from double-tap or FlutterFlow retry** — FlutterFlow's 'Add Row' action can fire twice if the user double-taps the check-in button or if a slow network response triggers the automatic retry. Without a database constraint, this creates two entries for the same day, inflates completion counts, and makes the streak engine miscalculate. Fix: The UNIQUE(challenge_id, user_id, entry_date) constraint in the schema above is the primary defense — Postgres will reject the second insert with error code 23505. Add a FlutterFlow Custom Action that catches this error and shows a SnackBar reading 'Already checked in today' instead of displaying a generic error. Also disable the check-in button immediately on first tap using a loading state variable.
- **FCM device tokens go stale after app reinstall** — When a user reinstalls the app, their FCM token changes. The old token remains in user_devices. The nightly Edge Function attempts delivery and FCM returns a 404 (Registration Not Found) or 410 (Unregistered) error. If the Edge Function swallows these errors silently, the user never receives reminders and the user_devices table accumulates dead tokens indefinitely. Fix: On every app launch, call FirebaseMessaging.instance.getToken() in FlutterFlow's App State initialization and upsert the result into user_devices (using the unique constraint on user_id + fcm_token). In the Edge Function, inspect each FCM response: if the status is 404 or 410, delete that row from user_devices before proceeding to the next token.
- **Leaderboard returns empty for all users due to RLS blocking cross-user reads** — The default Supabase RLS pattern for user-owned data uses USING (auth.uid() = user_id). Applied to user_streaks, this means every user can only see their own row — the leaderboard query returns exactly one result or nothing. This looks like a data problem but is actually an RLS configuration issue. Fix: Add a separate SELECT policy on user_streaks with USING (true) alongside the restricted INSERT/UPDATE policies. This lets any authenticated user read all streak rows (which is intentional — it powers the public leaderboard) while write operations remain restricted to own rows. The SQL schema in this page includes this policy.
- **Offline check-in sync creates backdated entries that break the streak display** — If FlutterFlow's local state caching is enabled and a user checks in while offline, the entry may be inserted with the current timestamp when connectivity returns — but if that's the next day, the date is off by one. The streak engine recalculates correctly based on the stored date, but the Supabase Realtime subscription may have already missed the event, leaving the leaderboard stale. Fix: Require connectivity for check-ins: show a 'No internet connection — check in requires connectivity' banner when offline rather than queuing the entry. If offline-first is a product requirement, capture the check-in date at the moment of tap (not at sync time) and include it in the payload, then trigger a manual Realtime broadcast after the sync resolves.

## Best practices

- Store entry_date in the user's local timezone from the first line of code — retrofitting timezone awareness into a live app with existing streak data is a painful migration
- Make the check-in button idempotent at the UI level (disable on first tap, re-enable after response) in addition to the database UNIQUE constraint — the constraint is the safety net, not the primary UX
- Refresh FCM tokens on every app launch, not only on first install — token rotation on reinstall and app updates is silent and frequent
- Show 'Last updated X minutes ago' on the leaderboard and add a manual refresh button — pg_cron-based refreshes run on a schedule, and users will assume stale data is a bug
- Send push notifications in the user's local timezone using their stored timezone string — a reminder at 9pm UTC landing at 2am local destroys retention overnight
- Cache the user's own streak in Flutter app state immediately after check-in before the Supabase response confirms — optimistic UI prevents the awkward delay between tap and counter update
- Log every FCM delivery attempt and response code in an Edge Function execution log or a Supabase table — silent 404s from stale tokens are the hardest notification bugs to diagnose without logs
- Scope leaderboards to the active challenge by default, not all-time global — a global leaderboard dominated by power users from day one discourages new participants from engaging

## Frequently asked questions

### Can I reset a user's streak from the admin panel?

Yes — add an admin-only Supabase RPC function that updates user_streaks.current_streak to 0 for a given user and challenge_id. Protect the function with a role check using auth.jwt() ->> 'role' = 'admin'. In FlutterFlow, wire an admin page with a user lookup and a 'Reset Streak' button that calls this RPC. Never modify streak rows directly from the app client — always go through a server-side function that can enforce the role check.

### How do I prevent users from backdating their check-ins?

The entry_date is generated server-side or validated server-side — never trust the date sent by the client. In the Supabase Edge Function or Postgres trigger, compare the submitted entry_date against now() AT TIME ZONE user_timezone and reject any date that is not today or (if you allow it) yesterday within a grace period. Using a Postgres trigger with BEFORE INSERT means no client code path can bypass the validation.

### What happens to the leaderboard when a challenge ends?

The leaderboard remains readable after the end_date — it shows final standings. Add a 'Challenge ended' banner by comparing the current date against challenges.end_date in your UI query. If you want to archive results, insert a snapshot of user_streaks into a challenge_results table at the end_date and query from there for historical views. This prevents ongoing app activity from polluting past challenge standings.

### Can one user join multiple active challenges simultaneously?

Yes — the data model is keyed by (challenge_id, user_id), so a user can have independent streaks in as many challenges as they join. The UI should show a challenge picker on the home screen that switches the calendar strip and streak counter to the selected challenge's data. The leaderboard is also per-challenge, scoped by challenge_id in the query.

### How do I send a reminder only to users who haven't checked in yet today?

In the Supabase Edge Function, run a query that finds all user_device rows where the user_id does NOT appear in challenge_entries for today's date in their local timezone. Use a LEFT JOIN or NOT EXISTS subquery: select devices.* from user_devices devices where not exists (select 1 from challenge_entries e where e.user_id = devices.user_id and e.entry_date = (now() AT TIME ZONE devices.timezone)::date). Then call FCM only for the returned device tokens.

### Can the check-in require a photo upload as proof?

Yes — add a photo_url column to challenge_entries and create a Supabase Storage bucket for proof photos. In FlutterFlow, add an image picker action before the Supabase insert: upload the image to Storage, get the public URL, and include it in the insert payload. Add a NOT NULL constraint on photo_url for challenges where proof is mandatory. The calendar strip can show a thumbnail of the uploaded photo for completed days.

### How do I show a streak flame animation that grows as the streak increases?

Use Flutter's AnimatedContainer or a Lottie animation file triggered when current_streak updates. In FlutterFlow, add a Lottie widget (Lottie.asset from the pub.dev package) bound to a page state variable for streak milestone thresholds — e.g. a small flame at 1-6 days, a medium flame at 7-29 days, a large flame at 30+ days. Trigger a confetti animation (using the confetti package) when the streak hits a milestone like 7, 30, or 100 days.

### Is FlutterFlow good enough for this feature, or do I need a developer?

FlutterFlow handles the calendar UI, Supabase queries, FCM push wiring, and leaderboard display well — a non-technical founder can ship a complete challenge tracker in 1-2 days. The two things that require direct SQL editing are the streak_calculator Postgres trigger and the pg_cron schedule for the daily push job. Both are copy-paste operations in the Supabase Dashboard, not code you write from scratch. Custom development only becomes necessary if your challenge rules are more complex than binary check-in, you need offline-first with conflict resolution, or you require SSO and compliance exports.

---

Source: https://www.rapidevelopers.com/app-features/content-challenge-tracker
© RapidDev — https://www.rapidevelopers.com/app-features/content-challenge-tracker