Database Schema

Surflink uses Supabase (PostgreSQL) with a comprehensive schema of 50+ tables. All tables have Row Level Security (RLS) enabled to ensure data isolation between coaches.

The full schema is defined in supabase/schema.sql.

Table Groups

Users & Auth

Table	Description
`coaches`	Coach profiles linked to `auth.users` -- name, email, avatar, bio, location, home break
`students`	Student profiles linked to a coach -- name, email, age, stance, skill level, invite status
`parent_links`	Links parent `auth.users` to students with configurable permissions
`push_tokens`	Device tokens for APNs/FCM push notifications

Sessions & Video

Table	Description
`sessions`	Surf sessions -- title, location, date, duration, video URLs, AI stats, processing status
`session_clips`	Individual video clips within a session -- filename, size, job ID, status, HLS URL
`session_surfers`	Links tracked surfer IDs (from AI) to student profiles
`annotations`	Coach annotations on video -- timestamp, type (drawing/text/voice), data JSON
`ride_scores`	Coach-scored rides with start/end timestamps and multi-criteria scores

Curriculum & Training

Table	Description
`drills`	Exercise library -- title, description, category, skill level, duration, multi-step instructions
`drill_assignments`	Coach assigns drills to students with due dates and status tracking
`lessons`	Scheduled lessons -- student, location, spot, duration, conditions forecast, linked session
`lesson_drills`	Links drills to lessons in order
`training_plans`	Multi-week structured programs with plan JSON
`skill_definitions`	Taxonomy of surf skills -- name, category, prerequisites, linked drills
`skill_assessments`	Coach-assessed progress per student per skill (not_started through mastered)

Tracking & Gamification

Table	Description
`session_journal`	Enriched session logs with mood/energy/confidence ratings, conditions, wave counts
`goals`	Metric-based goals with target values and status
`goal_milestones`	Checkpoint milestones within goals
`achievements`	Badge definitions -- slug, name, description, icon, category, threshold
`student_achievements`	Earned badges per student with timestamps
`equipment`	Student gear quiver -- boards, fins, wetsuits, etc.

Table	Description
`messages`	Coach-student messages with read tracking
`notifications`	In-app notifications with type, title, body, and read status
`community_posts`	Community feed posts with optional session links
`community_likes`	Post likes (unique per user per post)
`highlight_reels`	Curated clip collections with public sharing

Billing

Table	Description
`coaching_packages`	Package definitions -- name, price, session count, duration, Stripe price ID
`subscriptions`	Active student subscriptions with Stripe subscription IDs
`payments`	Payment records with amount, type, Stripe payment ID, status

Competitions

Table	Description
`competitions`	Competition events -- name, date, location, format, status
`heats`	Heat rounds within competitions
`heat_entries`	Student entries per heat with jersey color and seed
`heat_scores`	Wave-by-wave scores (0-10) with interference tracking
`competition_results`	Individual athlete competition history with placement and scoring
`competition_heats`	Heat-by-heat breakdown within competition results

Circuits & WSL

Table	Description
`circuits`	Tour/competition series (WSL, local, custom) with season tracking
`circuit_rankings`	Athlete rankings within a circuit
`circuit_events`	Events within a circuit series
`circuit_event_results`	Per-event results with placement and points
`circuit_point_scales`	Placement-to-points mapping for each circuit
`wsl_athletes`	Scraped WSL athlete profiles with career stats
`qualification_pathways`	Rank/points-based qualification tracking for athletes

Scouting & Game Plans

Table	Description
`opponents`	Scouted opponent profiles with strengths/weaknesses
`heat_matchups`	Head-to-head results against opponents
`heat_game_plans`	Per-heat strategy plans with target scores and maneuvers
`event_prep`	Pre-competition preparation (board selection, strategy, travel)

Reports & Invites

Table	Description
`season_reports`	Generated season reports with share tokens
`parent_invite_tokens`	Parent invite tokens with email, expiry, and acceptance tracking
`sync_log`	Cron job execution tracking

Location

Table	Description
`spots`	Saved surf spots with coordinates

Row Level Security

Every table has RLS enabled. The general policy pattern:

Coaches have full CRUD access to data they own (their students, sessions, drills, etc.)
Students have read access to data linked to them (their sessions, scores, drill assignments, etc.) and write access to specific tables (journal, messages, drill assignment status)
Parents have read access to data for their linked student(s)
Public data -- Skill definitions, achievements, active coaching packages, and public highlight reels are readable by anyone

Example Policy

-- Coaches can manage their sessions
create policy "Coaches can manage sessions"
  on public.sessions for all
  using (
    coach_id in (
      select id from public.coaches
      where user_id = auth.uid()
    )
  );

-- Students can view sessions they're linked to
create policy "Students can view linked sessions"
  on public.sessions for select
  using (
    id in (
      select session_id from public.session_surfers
      where student_id in (
        select id from public.students
        where user_id = auth.uid()
      )
    )
  );

API Reference

Integrations