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.

// 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:

Get user’s current badges from user_badges
Get all badge definitions from badges
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
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

-- 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:

On EventDetailsScreen: show available ticket_types for the event
Select ticket type and quantity
Create user_tickets row with status=‘active’, generate QR code
Call increment_ticket_sold(ticket_type_id) RPC
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:

Wire CreateReviewScreen to insert into reviews table
Add photo upload to Supabase Storage
Show reviews on EventDetailsScreen (query reviews where event_id=X)
Show average rating badge on event cards
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:

Insert into campaigns table
Show campaign list on profile/dashboard
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

Flow Docs

Explorer

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