mercy_night_supabase_contract v1.0.0
2026-06-11 · project mery_night · ref atoqfwltxzwjsuyfudif
Backend for MercyNight, an interactive party-music platform: a server device (TV/desktop) hosts a session, guests join via QR code from their phones and add YouTube songs.
Core mechanic: each player has a personal queue; the server round-robins
across players to pick the one current song (see PLAYER_QUEUE_ALGORITHM.md
— never change that design). Voting is democratic: love/meh/skip per song,
with a skip majority ending playback. All writes that affect fairness
(session lifecycle, rotation, playback) go through edge functions/RPCs —
clients only mutate their own rows.
Tables
users
Core user record, created on first login.
| Field | Type | Null | Notes |
|---|---|---|---|
| id | uuid | PK | |
| google_id | text | ||
| text | |||
| name | text | ||
| avatar_url | text | ✓ | |
| preferences | jsonb | ✓ | |
| created_at | timestamptz | ✓ | |
| last_seen | timestamptz | ✓ |
select: authenticated · insert: authenticated · update: own_row · delete: edge_function_only
sessions
A party session hosted on a server device (TV/desktop). Carries the round-robin state (player_rotation_order, current_player_index); mutated only by edge functions.
| Field | Type | Null | Notes |
|---|---|---|---|
| id | uuid | PK | |
| code | text | ||
| host_id | uuid | ✓ | |
| name | text | ✓ | |
| theme | text | ✓ | |
| state | session_state | enum(waiting, active, paused, idle, ended) | |
| game_mode | game_mode | ✓ | enum(classic, quiz_battle, music_bingo, guess_intro, battle_bands, theme_night, karaoke) |
| rules | jsonb | ✓ | |
| created_at | timestamptz | ✓ | |
| ended_at | timestamptz | ✓ | |
| current_player_index | integer | ✓ | |
| player_rotation_order | jsonb | ✓ |
select: authenticated · insert: edge_function_only · update: edge_function_only · delete: edge_function_only
participants
Who is in a session, with presence/activity state. Active participants form the round-robin rotation.
| Field | Type | Null | Notes |
|---|---|---|---|
| session_id | uuid | PK | |
| user_id | uuid | PK | |
| joined_at | timestamptz | ✓ | |
| state | text | ✓ | |
| is_active | boolean | ✓ | |
| avatar_state | text | ✓ |
select: authenticated · insert: authenticated · update: own_row · delete: edge_function_only
queue_items
One row per song in a player's personal queue. participant_user_id binds the item to its rotation slot — the round-robin core (PLAYER_QUEUE_ALGORITHM.md).
| Field | Type | Null | Notes |
|---|---|---|---|
| id | uuid | PK | |
| session_id | uuid | ||
| song_id | uuid | ||
| position | integer | ||
| status | queue_item_status | enum(queued, playing, played, skipped) | |
| added_at | timestamptz | ||
| added_by_user_id | uuid | ||
| added_by_user_name | text | ✓ | |
| played_at | timestamptz | ✓ | |
| participant_user_id | uuid | ✓ |
select: authenticated · insert: own_row · update: own_row · delete: own_row
songs
YouTube song metadata cache.
| Field | Type | Null | Notes |
|---|---|---|---|
| id | uuid | PK | |
| youtube_id | text | ||
| title | text | ||
| artist | text | ||
| thumbnail_url | text | ✓ | |
| duration | integer | ✓ | |
| explicit_flag | boolean | ✓ | |
| genre | text | ✓ | |
| release_year | integer | ✓ | |
| cached_at | timestamptz | ✓ |
select: authenticated · insert: authenticated · update: edge_function_only · delete: edge_function_only
votes
One love/meh/skip vote per user per song in a session. A skip majority (game-logic edge function) ends playback democratically.
| Field | Type | Null | Notes |
|---|---|---|---|
| session_id | uuid | PK | |
| song_id | uuid | PK | |
| user_id | uuid | PK | |
| vote_type | vote_type | enum(love, meh, skip) | |
| timestamp | timestamptz | ✓ |
select: authenticated · insert: own_row · update: own_row · delete: own_row
reactions
Floating emoji reactions during playback.
| Field | Type | Null | Notes |
|---|---|---|---|
| id | uuid | PK | |
| session_id | uuid | ✓ | |
| user_id | uuid | ✓ | |
| emoji | text | ||
| timestamp | timestamptz | ✓ | |
| displayed_at | timestamptz | ✓ |
select: authenticated · insert: own_row · update: edge_function_only · delete: edge_function_only
user_profiles
Extended user profile (display name, genres, moods).
| Field | Type | Null | Notes |
|---|---|---|---|
| id | uuid | PK | |
| user_id | uuid | ||
| display_name | text | ✓ | |
| bio | text | ✓ | |
| avatar_type | text | ✓ | |
| avatar_url | text | ✓ | |
| favorite_genres | text[] | ✓ | |
| preferred_moods | text[] | ✓ | |
| personal_notes | text | ✓ | |
| created_at | timestamptz | ✓ | |
| updated_at | timestamptz | ✓ |
select: authenticated · insert: own_row · update: own_row · delete: own_row
user_public_profiles view
Read-only public view of user_profiles (no personal_notes).
| Field | Type | Null | Notes |
|---|---|---|---|
| id | uuid | PK | |
| user_id | uuid | ||
| display_name | text | ✓ | |
| bio | text | ✓ | |
| avatar_type | text | ✓ | |
| avatar_url | text | ✓ | |
| favorite_genres | text[] | ✓ | |
| preferred_moods | text[] | ✓ | |
| created_at | timestamptz | ✓ |
select: authenticated · insert: edge_function_only · update: edge_function_only · delete: edge_function_only
user_connections
Follow relationships between users.
| Field | Type | Null | Notes |
|---|---|---|---|
| id | uuid | PK | |
| follower_id | uuid | ||
| following_id | uuid | ||
| connection_type | text | ✓ | |
| created_at | timestamptz | ✓ |
select: authenticated · insert: own_row · update: own_row · delete: own_row
user_achievements
Achievement unlocks and progress per user; awarded server-side only.
| Field | Type | Null | Notes |
|---|---|---|---|
| user_id | uuid | PK | |
| achievement_id | uuid | PK | |
| unlocked_at | timestamptz | ✓ | |
| progress | numeric | ✓ | |
| session_id | uuid | ✓ |
select: authenticated · insert: edge_function_only · update: edge_function_only · delete: edge_function_only
RPC Functions
add_song_to_player_queue(p_session_id: uuid, p_user_id: uuid, p_youtube_id: text, p_title: text, p_artist: text, p_thumbnail_url: text, p_duration: integer) → uuid
Adds a song to the calling player's personal queue; returns the queue item id.
advance_to_next_player_song(p_session_id: uuid) → json
Advances rotation; returns rows (next_song_id, next_player_id, next_player_name).
get_current_player(p_session_id: uuid) → json
Current player in rotation; returns rows (user_id, user_name, avatar_url, queue_count).
initialize_player_rotation(p_session_id: uuid) → void
Seeds player_rotation_order on the session from active participants.
get_vote_tally(session_uuid: uuid, song_uuid: uuid) → json
Vote counts for a song (love_votes, meh_votes, skip_votes, total_participants, ...).
start_playing_song(queue_item_id: uuid) → void
Marks a queue item as playing and finishes the previous one in the session.
request_data_export(user_id: uuid, email: text) → uuid
Queues a GDPR data export; returns the (possibly reused pending) request id.
get_daily_challenges(p_date: date) → json
Stable per-day challenge set; returns SETOF challenge_pool (table not in client contract, hence json).
Edge Functions
POST session-management()
Session lifecycle (create/join/leave/end/state/play-song/advance-song/...). Action via URL path suffix.
POST game-logic()
Voting with democratic skip, playback start, rotation calculation.
POST youtube-integration()
YouTube search, video details, trending, availability checks.
POST quiz-generation()
AI quiz challenge generation backed by challenge_pool.
POST realtime-coordination()
Broadcast state, presence, heartbeat, event acknowledgement.
Realtime
Publication: public.sessions, public.participants, public.queue_items, public.votes, public.reactions
session_updated←public.sessions(audience: authenticated)queue_updated←public.queue_items(audience: authenticated)vote_cast←public.votes(audience: authenticated)
Storage
avatars— public · image/png, image/jpeg · max 5 MB