Flow Docs

2026-03-30-supabase-migration-phase5-missing-features

5 min read

# Phase 5: Missing Features — Implementation Plan
 
> **For agentic workers:** REQUIRED: Use superpowers:subagent-driven-development (if subagents available) or superpowers:executing-plans to implement this plan. Steps use checkbox (`- [ ]`) syntax for tracking.
 
**Goal:** Implement the remaining stub features to production quality: badge award system, leaderboards, ticket purchase flow, enhanced reviews, and campaign management.
 
**Architecture:** All features built on Supabase PostgreSQL + Edge Functions. Mobile (Flutter) and Web (Next.js) both query Supabase directly. Badge awards triggered by Edge Functions evaluating criteria against user actions.
 
**Tech Stack:** Flutter/Dart (mobile), Next.js/TypeScript (web), Supabase Edge Functions (Deno), PostgreSQL
 
**Spec:** `flow_docs/docs/superpowers/specs/2026-03-29-supabase-only-migration-design.md`
**Depends on:** Phases 1-4 completed.
 
---
 
## Chunk 1: Badge Award System
 
### Task 1: Create badge evaluation Edge Function
 
**Files:**
- Create: `flow_backend/supabase/functions/evaluate-badges/index.ts`
 
- [ ] **Step 1: Create evaluate-badges Edge Function**
 
This function checks if a user has met criteria for any unearned badges and awards them. Called after XP changes, event attendance, crew creation, etc.
 
```typescript
// Evaluates badge criteria for a given user_id
// Called by process-rsvp and other triggers
// Checks: events_attended, connections, crews_created, events_created, etc.
```
 
The function should:
1. Get user's current badges from `user_badges`
2. Get all badge definitions from `badges`
3. For each unearned badge, evaluate criteria:
   - `events_attended`: count from `event_attendees` where status='going'
   - `connections`: count from `connections` where status='accepted'
   - `crews_created`: count from `crews` where creator_id=user
   - `events_created`: count from `events` where organizer_id=user
   - `days_active`: diff between now and profile.created_at
   - `karma_score`: from profiles.karma_score
4. Award badge by inserting into `user_badges` + creating notification
 
- [ ] **Step 2: Deploy and commit**
 
### Task 2: Create leaderboard PostgreSQL view
 
- [ ] **Step 1: Create leaderboard migration**
 
**File:** `flow_backend/supabase/migrations/20260330000006_leaderboards.sql`
 
```sql
-- Create materialized view for leaderboards (refreshed periodically)
CREATE MATERIALIZED VIEW IF NOT EXISTS public.leaderboard_xp AS
SELECT
    p.id,
    p.username,
    p.first_name,
    p.last_name,
    p.profile_picture,
    p.xp,
    p.level,
    p.karma_score,
    RANK() OVER (ORDER BY p.xp DESC) as rank
FROM public.profiles p
WHERE p.status = 'active' AND p.xp > 0
ORDER BY p.xp DESC
LIMIT 100;
 
-- Index for fast rank lookups
CREATE UNIQUE INDEX IF NOT EXISTS idx_leaderboard_xp_id ON public.leaderboard_xp (id);
 
-- RLS: Public read
-- Note: Materialized views don't support RLS directly, access via RPC
 
-- RPC to get leaderboard
CREATE OR REPLACE FUNCTION public.get_leaderboard(p_limit INT DEFAULT 50)
RETURNS TABLE(
    id UUID,
    username TEXT,
    first_name TEXT,
    last_name TEXT,
    profile_picture TEXT,
    xp INT,
    level INT,
    karma_score NUMERIC,
    rank BIGINT
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY SELECT * FROM public.leaderboard_xp LIMIT p_limit;
END;
$$;
 
GRANT EXECUTE ON FUNCTION public.get_leaderboard(INT) TO authenticated;
 
-- Refresh function (called by cron)
CREATE OR REPLACE FUNCTION public.refresh_leaderboard()
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY public.leaderboard_xp;
END;
$$;
```
 
- [ ] **Step 2: Commit**
 
### Task 3: Add leaderboard screen to mobile
 
- [ ] **Step 1: Create leaderboard screen in Flutter**
 
**File:** `flow_mobile/lib/features/gamification/screens/leaderboard_screen.dart`
 
Fetch from `_client.rpc('get_leaderboard', params: {'p_limit': 50})` and display as a ranked list with user avatar, name, XP, level, and rank badge.
 
- [ ] **Step 2: Add navigation route and commit**
 
---
 
## Chunk 2: Ticket Purchase Flow
 
### Task 4: Complete ticket purchase in mobile
 
- [ ] **Step 1: Read current TicketWalletScreen**
 
Read `flow_mobile/lib/features/tickets/` to understand current state.
 
- [ ] **Step 2: Implement ticket selection and purchase flow**
 
The mobile app needs:
1. On EventDetailsScreen: show available `ticket_types` for the event
2. Select ticket type and quantity
3. Create `user_tickets` row with status='active', generate QR code
4. Call `increment_ticket_sold(ticket_type_id)` RPC
5. Show ticket in TicketWalletScreen with QR code
 
For MVP (zero budget): free tickets only + "cash" payment method. Stripe integration deferred.
 
QR code generation: Use `qr_flutter` package (already in pubspec or add it).
 
- [ ] **Step 3: Add ticket scanning for event organizers**
 
On EventDetailsScreen (organizer view): add "Scan Ticket" button that reads QR codes and marks `user_tickets.status = 'used'` + calls check-in.
 
- [ ] **Step 4: Commit**
 
---
 
## Chunk 3: Enhanced Reviews & Campaigns
 
### Task 5: Complete review system
 
- [ ] **Step 1: Read current CreateReviewScreen**
 
Read `flow_mobile/lib/features/reviews/` to understand current state.
 
- [ ] **Step 2: Enhance review flow**
 
The reviews table already exists with full schema (rating, content, photos, status). Need to:
1. Wire CreateReviewScreen to insert into `reviews` table
2. Add photo upload to Supabase Storage
3. Show reviews on EventDetailsScreen (query `reviews` where event_id=X)
4. Show average rating badge on event cards
5. Reviews auto-trigger `update_event_rating()` function (already exists)
 
- [ ] **Step 3: Commit**
 
### Task 6: Complete campaign management
 
- [ ] **Step 1: Read current campaign screens**
 
Read mobile and web campaign-related files.
 
- [ ] **Step 2: Wire campaign CRUD**
 
The `campaigns` table already exists. Wire the CreateCampaignScreen to:
1. Insert into `campaigns` table
2. Show campaign list on profile/dashboard
3. Allow status management (draft → active → completed)
 
- [ ] **Step 3: Commit**
 
---
 
## Chunk 4: Verification
 
### Task 7: Test all new features
 
- [ ] **Step 1: Test badge awards** — Attend an event, verify badge earned if criteria met
- [ ] **Step 2: Test leaderboard** — Verify ranked list shows real XP data
- [ ] **Step 3: Test ticket flow** — Create free ticket, verify QR, scan to check in
- [ ] **Step 4: Test reviews** — Write review with photo, verify on event page
- [ ] **Step 5: Test campaigns** — Create and manage campaign lifecycle
 
- [ ] **Step 6: Update spec**
 
```
Phase 5: Missing Features — ✅ COMPLETED
```
 
---
 
## Summary
 
| Task | What | Est. |
|------|------|------|
| 1 | Badge evaluation Edge Function | 15 min |
| 2 | Leaderboard view + RPC | 10 min |
| 3 | Leaderboard mobile screen | 10 min |
| 4 | Ticket purchase flow | 20 min |
| 5 | Enhanced review system | 15 min |
| 6 | Campaign management | 10 min |
| 7 | E2E testing | 15 min |
 
**Total: ~95 minutes**

Graph View