Migrations, storage buckets, and RLS
Database & Migrations
Surflink uses Supabase (Postgres) with row-level security on every table. Access is org-based — org membership plus the per-role permission matrix — not a simple owner check.
Migrations are authoritative
The schema is the ordered set of files in supabase/migrations/ (139 as of 2026-09-16). Apply them in filename order:
supabase link --project-ref <your-project-ref>
supabase db push
Migrations are forward-only and additive-first. The September 2026 trims are themselves migrations that drop the tables of removed features, so the live schema and the code agree. src/lib/migrationManifest.json lists what the repo contains; the daily migration-drift cron compares it with the live ledger.
Tables by domain
| Domain | Tables |
|---|---|
| Tenancy + people | orgs, org_members, org_roles, org_invite_tokens, org_locations, org_audit_log, coaches, athletes, athlete_coaches, platform_settings, platform_invites, waitlist, admin_audit_log, parental_consents |
| Footage + analysis | sessions, session_clips, session_surfers, clip_maneuvers, annotations, focus_areas, batch_upload_jobs, camera_clock_offsets |
| Guided reviews | session_reviews, review_receipts, review_replies (keypoints live in annotations) |
| TV | tv_devices, tv_pairing_codes |
| Wearable | wearable_sessions, wearable_clip_sync, wearable_event_labels |
| Scheduling | training_sessions, training_session_participants |
| Coaching content | drills, drill_assignments |
| Messaging | conversations, conversation_participants, messages, message_blocks, message_reports |
| Notifications | notifications, notification_deliveries, notification_preferences, notification_type_channels, push_tokens |
| Competition (coach-owned) | competition_results, competition_heats, opponents, qualification_pathways |
| Competition (imported, read-only) | circuits, circuit_events, circuit_event_results, circuit_rankings, circuit_point_scales, liveheats_athletes, liveheats_events, liveheats_organisations, wsl_athletes (frozen index), sync_log |
| Billing | org_subscriptions (one row per workspace — written only by the Stripe webhook, the daily trial cron and the audited admin grant), stripe_webhook_events (idempotency) |
| Telemetry + misc | activity_events (admin Live feed, pruned nightly), spots, enroll_rate_limits |
Storage buckets
| Bucket | Purpose |
|---|---|
raw-uploads | Resumable upload target for raw clips before analysis |
session-videos | Processed videos cached from SurfVision |
session-data | Analysis sidecars (*.keypoints.json, *.overlays.json, *.masks.json, *.pockets.json) and raw watch samples — private, served through access-checked proxies |
clip-thumbnails | Clip poster frames (created by the clip_thumbnails migration) |
annotation-audio | Voice notes on annotations, guided-review takes and athlete replies |
message-attachments | Photos sent in messages (private, signed URLs) |
avatars | Profile photos |
The three analysis buckets (raw-uploads, session-videos, session-data) are created by hand in the Supabase dashboard. Deleting a session or a clip removes its objects from every bucket.
RLS helpers
Policies rely on SQL functions defined in the migrations: user_org_perm(...) (does the caller hold an org-scope key in this org?), user_athlete_perm(...) (does the caller hold an athlete-scope key for this athlete?) and org_module_enabled(...). src/lib/permissions.js mirrors them in JavaScript (canOrg, canAthlete, requireModule), so authorization is enforced both in SQL and in route handlers.
Seeding
There is no seed file; all data is created through the app. scripts/ui-audit-seed.mjs builds a demo workspace for App Review and mirrors the permission catalog (a test pins it).