Skip to content

data-model.md

Latest commit

 

History

History
95 lines (72 loc) · 3.79 KB

data-model.md

File metadata and controls

95 lines (72 loc) · 3.79 KB
Raw

Data Model

All schema lives in supabase/schema.sql. Run it once in the Supabase SQL editor — it creates all tables, policies, views, and Realtime config in a single pass.

Tables

profiles

One row per user. Created when the user sets a handle after signing in (including anonymous/guest users — anonymous sessions have a valid auth.users row and UUID).

Column Type Notes
id uuid PK References auth.users(id), cascades on delete
handle text UNIQUE NOT NULL Lowercase, 1–30 chars, [a-z0-9-]
avatar_public_id text Cloudinary public_id for the user's avatar
created_at timestamptz

RLS: Public SELECT. INSERT/UPDATE restricted to auth.uid() = id.

cards

One card per creation. A user can have up to 3 non-rejected cards.

Column Type Notes
id uuid PK
user_id uuid References profiles(id), cascades on delete
nation text One of arg/bra/fra/eng/spa/ger
stats jsonb { speed, shooting, passing, dribbling, tackling, strength } — all integers 0–99
source_public_id text Cloudinary public_id of the original user photo
card_url text Full Cloudinary composition URL (baked-in stats, regenerated on level-up)
level int DEFAULT 1 Increments on level-up (added in 005)
xp int DEFAULT 0 Current XP within the level (added in 005)
moderation_status text DEFAULT 'pending' pending, approved, or rejected (added in 006)
rejection_reason text Human-readable reason, set on rejection (added in 006)
moderated_at timestamptz Timestamp of moderation verdict (added in 006)
created_at timestamptz

RLS: Anon users see only approved cards. Owners see all their own cards. INSERT/DELETE restricted to own user_id.

The cards table is in the Supabase Realtime publication so the card creation UI can subscribe to moderation status changes without polling.

battles

One row per battle. Only the service role can insert — the RLS policy has WITH CHECK (false).

Column Type Notes
id uuid PK
challenger_card_id uuid References cards(id), cascades
opponent_card_id uuid References cards(id), cascades
category text 'simulation' (always — stats across all 6 attrs are compared)
challenger_value int Challenger total score
opponent_value int Opponent total score
winner_card_id uuid NULLABLE References cards(id). NULL = draw
created_at timestamptz

RLS: Public SELECT. Insert WITH CHECK (false) — no client can write, ever.

The battles table is in the Supabase Realtime publication for the live leaderboard.

Views

leaderboard

Aggregates win counts per user. Created in 003_battles.sql.

select p.id as user_id, p.handle,
  count(b.*) filter (where c.user_id = p.id) as wins
from profiles p
left join cards c on c.user_id = p.id
left join battles b on b.winner_card_id = c.id
group by p.id, p.handle
order by wins desc

security_invoker = on means the view runs with the caller's RLS context, not the definer's.

Stats shape

The stats jsonb column always contains exactly these six keys:

type CardStats = {
  speed: number      // 0–99
  shooting: number
  passing: number
  dribbling: number
  tackling: number
  strength: number
}

Starting stats are randomly generated in lib/cards/generateStats.ts (range 30–45). On each level-up every stat increments by 1 (cap 99).

Setup

Run supabase/schema.sql in your Supabase SQL editor once. It creates all tables, views, RLS policies, and enables Realtime on cards and battles in a single pass. Then enable anonymous sign-ins in the Supabase dashboard (Authentication → Sign In / Providers).