Database & Migrations

Surflink uses Supabase (PostgreSQL) with comprehensive Row Level Security (RLS). Access is org-based -- governed by org membership plus the per-role permission matrix -- not by a simple coach_id check.

Migrations are authoritative

The schema is the ordered set of files in supabase/migrations/ (27 migrations as of writing). Apply them in filename order:

supabase link --project-ref <your-project-ref>
supabase db push

The migrations build orgs/roles/members, multi-coach assignments, athlete providers, the identity graph, watchlist, public profiles, dedup, platform billing, competition heats, LiveHeats rankings, super-admin, and the hardened RLS objects.

supabase/schema.sql is a read-only snapshot of the live public schema for reference -- its own header says so. The migrations are authoritative; don't provision a new database from the snapshot alone.

Storage buckets (create manually)

No migration creates Storage buckets. You must create these by hand:

Seeding

There is no supabase/seed.sql. The only seed step is POST /api/achievements/seed, which idempotently populates the achievements table using the service-role client.

RLS helpers

The multi-coach migrations define SQL functions the policies rely on:

• user_org_perm(...) -- does the current user have an org-scope permission in an org?
• user_student_perm(...) -- does the current user have a student-scope permission for an athlete?
• org_module_enabled(...) -- is a module enabled for an org?

src/lib/permissions.js mirrors these in JavaScript for server-side checks (canOrg, canAthlete, requireModule), so authorization is enforced both in SQL (RLS) and in route handlers.

Table groups (high level)

• Identity & access: coaches, students, orgs, org_members, org_roles, org_invite_tokens, athlete_coaches, push_tokens.
• Sessions & video: sessions, session_clips, session_surfers, annotations.
• Curriculum: drills, drill_assignments, lessons, training_session_drills, training_plans.
• Tracking & gamification: session_journal, goals, goal_milestones, achievements, athlete_achievements, equipment.
• Social: messages, notifications, community_posts, community_likes.
• Billing: org_subscriptions (platform), coaching_packages, subscriptions, payments (marketplace).
• Competition & circuits: competitions, comp_heats, heat_entries, heat_scores, circuits, circuit_rankings, circuit_events, circuit_event_results, circuit_point_scales, wsl_athletes, qualification_pathways.
• Scouting: opponents, heat_matchups, heat_game_plans, event_prep.
• Providers/identity: athlete_identity, athlete_identity_links, watchlist, plus per-provider caches.
• Reports & ops: season_reports, sync_log, spots.

Naming note: the competitions list page reads a heats relation while the competition detail page uses comp_heats/heat_entries/heat_scores. That's a known inconsistency -- heat counts on the list cards can diverge from the detail page.