Gamified Medical Terminology Learning App — Duolingo-Inspired, Published on iOS & Android

Spaced repetition requires per-term state (ease factor, interval, mastery level) that must survive app restarts, device switches, and offline use — without any on-device dependency or data loss.

Designed a `user_progress` table in Supabase with columns for `ease_factor`, `interval_days`, `mastery_level`, `last_reviewed_at`, and `next_review_at`. The SM-2 logic runs entirely in Dart (progress_service.dart) after each quiz answer, and immediately upserts the updated state to Supabase. Row Level Security ensures users can only read/write their own records.

Supabase's PostgREST join-filter behavior made it impossible to correctly filter user_progress by topic in a single query — resulting in incorrect completion percentages on the learning path.

Implemented a deliberate two-query pattern: first fetch all term IDs for a given topic, then query user_progress filtered by that ID list. This ensures accurate term-level completion data without relying on brittle join filters.

On sign-up, client-side profile creation created a race condition — the app could try to read the profile before the INSERT committed, causing crashes on the first session.

Replaced client-side profile creation with a PostgreSQL SECURITY DEFINER function triggered by `auth.users` INSERT. The profile row is always created within the same transaction as the auth user, eliminating the race condition entirely.

Computing real-time global rankings across all users by XP (with streak as tiebreaker) is expensive if done client-side, and Supabase doesn't natively support computed rankings.

Created a PostgreSQL VIEW (`leaderboard`) that uses `ROW_NUMBER() OVER (ORDER BY total_xp DESC, current_streak DESC)` to produce a pre-ranked result set. The Flutter app queries this view directly — rankings are always correct and database-computed.