data-models.md

Data Models — WakeRef

Generated: 2026-06-11 · Updated: 2026-09-30 (full rescan / deep — competitions agenda, audience counters, inbox rate limits, storage hardening) · Source of truth: scripts/wakeref_schema.sql (reference dump) and scripts/wakeref_post_restore.sql (executable: views, functions, RLS, grants, triggers, bucket). One-off changes applied to the live DB are traced in scripts/migrations/0001…0026.

WakeRef stores all data in a single Supabase PostgreSQL database (public schema). There is no ORM and no migration framework — schema changes are applied by hand in the Supabase SQL Editor, recorded as a numbered file under scripts/migrations/, and mirrored into the two SQL files above. The frontend talks to the database directly through PostgREST (the @supabase/supabase-js client), so the RLS policies, grants, the two views and the RPC functions are the API contract (see api-contracts.md).

The live DB is the source of truth, not the SQL files. Content seeds (tips, descriptions, aliases, contexts — migrations 0009, 0011–0015) were one-off operations; the files are their trace.

Entity overview

Three families: the trick reference, the competitions agenda, and the judging tools — plus inboxes and audience counters that serve all three.

Table	Family	Purpose	anon read	anon write
categories	reference	14 fixed trick categories with color + sort order	✅ all rows	❌
figures	reference	Trick entries (core entity)	✅ published = true	❌
prerequisites	reference	Many-to-many self-join on figures	✅ all rows	❌
videos	reference	Video references (Storage uploads or external links)	✅ takedown_requested = false	❌
competitions	agenda	Public agenda of cable competitions	✅ published = true	❌
competition_videos	agenda	Ordered video links of a competition	✅ if parent competition is published	❌
video_submissions	inbox	Public "suggest a video" box	❌	✅ insert (column-scoped, rate-limited)
takedown_requests	inbox	Copyright removal requests	❌	✅ insert (column-scoped, rate-limited)
competition_submissions	inbox	Public "a competition is missing" box	❌	✅ insert (column-scoped, rate-limited)
compositions	engagement	Saved runs from the Compo page	❌ (one row by id via RPC)	✅ insert (rate-limited)
figure_views	audience	Per-figure, per-day view counter	❌	via track_figure_view only
page_routes	audience	Whitelist of counted non-trick page patterns	✅ all rows	❌
page_views	audience	Per-route-pattern, per-day view counter	❌	via track_page_view only
competition_views	audience	Per-competition, per-day view counter	❌	via track_competition_view only
judge_runs	judging	Reference runs + official solution for /entrainement-juge	❌ (2 RPCs)	❌
parcours	judging	Competition courses shared with judges by short code	❌ (get_parcours)	❌

All admin mutations run as the authenticated role (a single account).

Enumerated types

• sport_type — wakeboard | wakeskate | seated (figures.sport, videos.sport, judge_runs.discipline). seated = wakeboard assis / handiwake; a first-class discipline, never a label bolted onto a name.
• video_source — videos.source_type, judge_runs.source_type; default upload. The front end detects YouTube/Instagram from the URL first (src/lib/videoSource.js) and only falls back to this column when the URL won't parse.

---

Trick reference

categories

Column	Type	Notes
id	integer PK	sequence-backed
name	text	UNIQUE
slug	text	UNIQUE — used in /figures?cat=<slug>
color	text	hex; no longer rendered — the "papier" design removed category colors (ink + magenta only)
sort_order	integer	default 0

Mirrored client-side in src/data/categories.js (adds icons). Keep the two in sync when adding a category.

figures — core entity

Column	Type	Notes
id	integer PK
slug	text UNIQUE	/figures/:slug; also referenced by value in SCORING_SLUGS (lib/compoGrids.js)
name	text
category_id	integer FK → categories
sport	sport_type	native discipline, default wakeboard
sports	text[] NOT NULL	multi-discipline membership, always ⊇ {sport}
difficulty	smallint	CHECK 1–5
description, description_en	text	bilingual
tips, tips_en	text[]	bilingual
tips_seated, tips_seated_en, tips_wakeskate, tips_wakeskate_en	text[]	per-discipline override; empty ⇒ falls back to tips
is_switch, switch_of	boolean, FK → figures	switch variant → base trick
built_on_id	FK → figures	"built on" parent; must target the base of a switch group, never a switch; kept acyclic by a trigger
published	boolean	default true; RLS hides unpublished rows from anon
contexts	text[]	kicker · feature · flat · air_trick (src/data/contexts.js); corrected in bulk by migration 0012
approach	text[]	entry stance — hs/ts standing, regular/fakie seated (no CHECK)
rotation	text[]	fs / bs
inverted, rewind	boolean
spin, inverts	smallint	degrees / flip count (default 0)
rewind_degs	smallint[]	per-rewind amplitudes
rotation_type	text[]	CHECK ⊆ {ole, handle_pass}
aliases	text[] NOT NULL default {}	spoken/written aliases for the voice matcher (scripts/gen-aliases.mjs)
created_at, updated_at	timestamptz	updated_at via trigger

Switch groups (coalesce(switch_of, id)) share videos and prerequisites. Built-on tree: the view exposes parent, direct children and recursive root, all resolved from the base of the switch group. Approach axis has no DB CHECK — the valid set lives in admin/FigureForm.jsx and FigureDetail.jsx; add any new value in both. Seated naming: default implicit (FS 180 = regular), variant prefixed (Fakie FS 180), never "Seated …".

prerequisites

Composite PK (figure_id, requires_id), both FK → figures. "To land figure_id, first know requires_id."

videos

Column	Type	Notes
id	integer PK
figure_id	FK → figures, NOT NULL	(why competitions got their own table)
title, caption	text
file_path	text	path in the videos bucket for uploads
source_type	video_source	fallback only — see above
source_url	text	external link
creator_name, creator_url	text	attribution
sport	sport_type	discipline shown in the clip (migration 0003)
performer_gender	text	CHECK ∈ {man, woman, other} — drives the quiz's gender parity (migration 0003)
takedown_requested	boolean	true hides the row everywhere
takedown_email, takedown_at		set when a takedown is processed
sort_order	integer	ordering within a figure (surfaced by figures_full)
uploaded_at	timestamptz	drives recent_video_figures

Thumbnails live by convention at thumbnails/<shortcode>.jpg in the bucket. For Instagram (no public thumbnail) the admin drops one through VideoThumbField, shrunk in the browser by lib/compressImage.js; the path is shared by figure and competition videos.

---

Competitions agenda

Design frozen on 2026-09-06 (_bmad-output/brainstorming/brainstorming-session-2026-09-06-19-13.md). Unrelated to the judging tools.

competitions

Column	Type	Notes
id	bigint identity PK	what idFromParam reads from /competitions/:idSlug — the slug is decorative
name	text NOT NULL	1–160 chars
date_start	date NOT NULL
date_end	date	null = one-day event; CHECK date_end >= date_start
date_precision	text	day | year. For year, date_start holds Dec 31st (CHECK competitions_year_is_dec31, date_end null) — a storage artefact that must never reach the screen
wakepark	text	≤ 160 — proper noun, no _en
affiliation	text	federal | independent (default)
tour_name	text	≤ 160 — free text; a tour is not an entity. Its logo (competitions/tours/<slug>.png) and page (/competitions/circuit/<slug>) are derived from slugify(tour_name)
tour_url, info_url, entry_url, live_video_url, live_scoring_url, organiser_instagram_url, wakepark_url	text	≤ 500 each; CHECK competitions_urls_http forces ^https?:// (blocks javascript:, which type="url" lets through)
poster_path, logo_path	text	Storage paths competitions/<uuid>-affiche.<ext> or …-logo.<ext> (UUID names since 2026-09-13, so a draft's file can't be guessed)
cancelled	boolean	the single editorial flag
published	boolean	default true; anon sees published rows only
created_at, updated_at	timestamptz	trigger competitions_updated_at

Deliberately absent: no status column (past / live / upcoming are derived from dates by competitionState()), no discipline (implicit default = all disciplines), no translatable prose, hence no _en columns. Index competitions_date_start_idx (date_start desc).

competition_videos

Column	Type	Notes
id	bigint identity PK
competition_id	FK → competitions on delete cascade
url	text NOT NULL	1–500, ^https?://
title	text	≤ 160
sort_order	integer	index (competition_id, sort_order)
created_at	timestamptz

A dedicated table (not JSONB) because rows are ordered and individually edited. CompetitionForm saves them by wholesale delete-then-insert without a transaction — it validates first, but a network cut between the two statements leaves the list empty (known, accepted while a single admin edits).

competition_submissions

Column	Type	Notes
id	bigint identity PK
name	text NOT NULL	2–160
date_text	text NOT NULL	2–80, free text ("en juin", "été 2027")
url	text	≤ 500, ^https?://
status	text	pending (default) | handled | rejected
created_at	timestamptz	indexed desc

Deliberately not a draft competitions row: a submission is raw input, a competition is editorial content. Anon may insert only name, date_text, url. Each insert fires the notify-competition-submission Edge Function via a DB webhook.

---

Inboxes & engagement

video_submissions

figure_id (FK, NOT NULL), source_url (NOT NULL), title, creator_name, creator_url, caption, status (pending | approved | rejected), submitted_at (indexed desc). Anon INSERT is granted per column (the six content fields), so a client can neither back-date a row nor insert it pre-approved. Each insert fires notify-video-submission.

takedown_requests

video_id (FK → videos), name, email (NOT NULL), message, handled (default false), created_at (indexed desc). Anon INSERT granted on video_id, name, email, message only.

compositions — saved Compo runs

Column	Type	Notes
id	text PK	short id in /composition/:id
name	text	≤ 80
data	jsonb NOT NULL	run snapshot incl. gridKey; ≤ 50 KB
score	integer	denormalized, normalized to /20
created_at	timestamptz

Rate limits on public inserts

Table	Trigger	Cap	Shape
compositions	compositions_rate_limit	20 / min	BEFORE INSERT, per row
competition_submissions	competition_submissions_rate_limit	10 / min and 60 / day	AFTER INSERT FOR EACH STATEMENT (a row trigger doesn't see rows of the same batch insert)
video_submissions	video_submissions_rate_limit	10 / min and 60 / day	idem (migration 0026)
takedown_requests	takedown_requests_rate_limit	10 / min and 60 / day	idem (migration 0026)

All security definer, raising errcode = 'PT429' with a French message the forms display. The cap is global, not per submitter — Postgres cannot see the IP — so it protects the admin's inbox, not the form's availability.

---

Audience counters

Three home-made counters, no identifier, no IP, no cookie (nothing to consent to): they count views, not visitors, and include JS-executing bots. Dedup is client-side, once per browser per day (src/lib/vuDuJour.js, one localStorage key per counter). Never purged (2026-06-16 decision).

figure_views

Composite PK (figure_id, day), views integer. FK on delete cascade. Written by track_figure_view (ignores unknown ids), read by most_viewed_figures (home) and the four admin stats RPCs. Index on day.

page_routes + page_views

• page_routes(path PK, label) — the whitelist of counted route patterns, seeded in post_restore (17 rows). It stores patterns, not URLs (/composition/:id, never /composition/a1b2c3). track_page_view is open to anon; without the whitelist any string could be posted and the table would grow unbounded.
• page_views(path FK → page_routes on delete cascade, day, views) — PK (path, day), index on day.
• Deliberately absent: /figures/:slug and /competitions/:id (they have their own per-id counters), chromeless judging surfaces, admin, 404. /competitions/circuit/:slug is aggregated (a tour has no id).
• The pattern list lives twice — JS in src/lib/pageViews.js, SQL in the seed. node scripts/test-page-routes.mjs fails when they drift. Adding a counted page = one line in each + one row on the live DB.

competition_views

PK (competition_id, day), FK on delete cascade, index on day. Written by track_competition_view for published competitions only, counted by id so a rename keeps its history.

The 2026-08-14 → 2026-09-11 rows of page_views and competition_views are a reconstruction, imported from a Vercel "Top Pages" export: totals exact, daily split estimated. Don't read the shape of those four weeks as data.

---

Judging tools

judge_runs — reference runs for /entrainement-juge

Column	Type	Notes
id	bigint identity PK
name	text NOT NULL	≤ 120
discipline	sport_type NOT NULL
grid_key	text NOT NULL	wakeboard · wakeskate · seated_mp1 · seated_mp5
difficulty	text NOT NULL	CHECK ∈ {easy, medium, hard}
category	text	free grouping label
source_type, video_path, video_url		upload (bucket) vs external
solution	jsonb NOT NULL	the official run (Compo snapshot); ≤ 50 KB
published	boolean default false
created_at, updated_at	timestamptz	trigger judge_runs_updated_at

No anon policy. Public access only through list_judge_runs() (metadata, no solution) and get_judge_run_solution(id).

parcours — competition courses

Column	Type	Notes
id	text PK	8-char base36 share code — what the judge types at /juge
name	text NOT NULL UNIQUE	1–80
data	jsonb NOT NULL	{cableSpin, nbPoulies, poulieStart, parcours[]}; ≤ 50 KB
created_at, updated_at	timestamptz	trigger parcours_touch

PK and name are both unique, so an insert can raise 23505 for two reasons; src/lib/judge/api.js disambiguates (DuplicateNameError vs regenerate-and-retry, up to 5 attempts). Defined in scripts/competition_parcours.sql and mirrored in post_restore. Heats scored against a course are never stored server-side.

---

Views

Both are security_invoker, and neither carries a published filter — the option is the protection. It does not survive a create view on its own: every create view must be followed by its alter view … set (security_invoker = true) in the same breath (it drifted once on figures_card, fixed by migration 0021).

figures_card — light read model

id, slug, name, sport, difficulty, contexts, category_name, category_slug, sports, aliases. Backs /figures, the home rows (most_viewed_figures and recent_video_figures return setof figures_card) and — cached as wakeref_voice_figures — the offline catalogue of the voice matcher.

figures_full — detail read model

The figures columns plus:

• category_name, category_slug, category_color
• switch_of_figure — {id, name, slug} of the base trick
• switch_versions — switch variants of this figure
• prerequisites — {id, name, slug}[], taken from the base of the switch group (a switch inherits them)
• videos — non-takedown videos shared across the switch group, ordered by sort_order, each with performer_gender
• built_on_figure — immediate parent ({id, name, slug, switch_names}), anchored on the base; omitted if the parent is itself a switch
• built_on_children — direct children of the base (the "+1" level), switches excluded, ordered by rotation / inverted / name
• base_figure — root of the built-on chain (recursive CTE)
• decomposition columns (spin, inverts, rewind_degs, rotation_type) and discipline columns (tips_seated*, tips_wakeskate*, sports), appended at the end so create or replace view stays compatible

Triggers

Trigger	Table	Timing	Action
figures_updated_at	figures	BEFORE UPDATE	set_updated_at()
figures_built_on_acyclic	figures	BEFORE INSERT/UPDATE OF built_on_id	rejects cycles and self-references
compositions_rate_limit	compositions	BEFORE INSERT (row)	20/min
competition_submissions_rate_limit	competition_submissions	AFTER INSERT (statement)	10/min, 60/day
video_submissions_rate_limit	video_submissions	AFTER INSERT (statement)	10/min, 60/day
takedown_requests_rate_limit	takedown_requests	AFTER INSERT (statement)	10/min, 60/day
parcours_touch	parcours	BEFORE UPDATE	updated_at = now()
judge_runs_updated_at	judge_runs	BEFORE UPDATE	set_updated_at()
competitions_updated_at	competitions	BEFORE UPDATE	set_updated_at()

Indexes

Index	Target	Purpose
figures_search_idx	GIN on to_tsvector('french', immutable_unaccent(name‖description))	French full-text search
videos_figure_id_idx	videos(figure_id) WHERE takedown_requested = false	video aggregation
figures_switch_of_idx, figures_built_on_id_idx, figures_category_id_idx	figures	switch groups, tree, category join
prerequisites_requires_id_idx	prerequisites(requires_id)	prerequisite sub-query
figure_views_day_idx, page_views_day_idx, competition_views_day_idx	(day)	rolling windows
competitions_date_start_idx	competitions(date_start desc)	agenda feed
competition_videos_competition_idx	(competition_id, sort_order)	ordered links of a comp
competition_submissions_created_idx, video_submissions_submitted_idx, takedown_requests_created_idx	(created_at / submitted_at desc)	admin inbox order + rate-limit windows

Extensions

unaccent (in the extensions schema), wrapped by public.immutable_unaccent(text) so it can sit in the GIN index expression.

Row-Level Security summary

RLS is enabled on all 16 tables.

• Public SELECT: categories, prerequisites, page_routes (all rows); figures (published); videos (takedown_requested = false); competitions (published); competition_videos (exists a published parent — without it, links of an unpublished comp would leak).
• Admin full access (for all using ((select auth.role()) = 'authenticated') — the sub-select makes it an initplan, evaluated once per query): figures, categories, prerequisites, videos, judge_runs, competitions, competition_videos; parcours via four per-command policies.
• Public INSERT only: takedown_requests, video_submissions, compositions, competition_submissions. Admin gets SELECT (+ UPDATE on the two submission boxes, DELETE on compositions).
• Admin SELECT only: figure_views, page_views, competition_views.
• No anon policy at all: judge_runs, parcours — public reads only through security definer RPCs.

A policy is not a grant. RLS decides which rows a role may see; grant select decides whether it may read the table at all — both are required. figure_views shipped with a policy but its grant only in post_restore, and stayed unreadable on the live DB for months (fixed by migration 0022). Every migration that creates a table must carry its own grants.

Storage (videos bucket, public)

• file_size_limit = 25 MB (the wider of the two app caps — a bucket can't tell a poster from a clip). The browser-side refusSiTropLourd() check (25 MB video / 5 MB image) exists for a usable message, not for security.
• SELECT on storage.objects is admin-only (migration 0025). A public bucket serves /object/public/… without any policy; the SELECT policy only governs listing, and it used to let anon enumerate every file name, drafts included.
• INSERT / DELETE admin-only. No UPDATE policy — replacing a file means remove then upload, never upsert.
• Layout: <figure_id>/<timestamp>.<ext> (figure uploads), thumbnails/<shortcode>.jpg, runs/<timestamp>.<ext> (judge-run videos), competitions/<uuid>-affiche.<ext> or …-logo.<ext> (posters/logos), competitions/tours/<slug>.png, competitions/ffsnw.png.

How schema changes are managed

1. Write scripts/migrations/NNNN-<topic>.sql and apply it in the Supabase SQL Editor. Include the grants of any table it creates.
2. Mirror it into scripts/wakeref_post_restore.sql (executable) and scripts/wakeref_schema.sql (reference dump, not executed).
3. After any create view, re-apply security_invoker = true.
4. Keep src/data/categories.js / contexts.js in sync, and page_routes ↔ src/lib/pageViews.js (node scripts/test-page-routes.mjs).

Backups: .github/workflows/backup.yml runs pg_dump daily at 03:00 UTC into the private backups bucket (30-day retention).