Database Schema

Watermelon uses Supabase (PostgreSQL) for persistent data. All tables are in the public schema with Row-Level Security (RLS) policies.

Profiles

Stores user identity and gamification statistics.

Column	Type	Description
`id`	uuid	Primary key, matches Supabase auth user ID
`created_at`	timestamptz	Account creation time
`email`	text	User email (nullable)
`username`	text	Unique username
`display_name`	text	Public display name
`avatar_url`	text	Profile picture URL (DiceBear / Gravatar)
`plan`	text	`FREE`, `PREMIUM_INDIVIDUAL`, `PREMIUM_FAMILY`, etc.
`is_banned`	boolean	Admin ban flag
`telegram_id`	text	Telegram user ID for bot integration
`fcm_token`	text	Firebase Cloud Messaging token for push notifications

Gamification Columns

Column	Type	Description
`xp_total`	int	Total experience points
`xp_level`	int	Computed level: `GREATEST(1, FLOOR(SQRT(xp_total::FLOAT / 100.0)))`
`rank_tier`	text	Computed from hours listened (e.g. 🌱 Seed Listener → 👑 Eternal Echo)
`hours_listened`	float	Total listening hours
`minutes_listened`	float	Total listening minutes
`streak_days`	int	Current consecutive listening streak
`longest_streak`	int	All-time longest streak
`songs_played`	int	Total play events
`songs_completed`	int	Songs listened to >30s
`artists_discovered`	int	Unique artists played
`playlists_created`	int	User-created playlists count
`liked_songs_count`	int	Favorited songs count
`top_genre`	text	Most-played genre
`top_artist`	text	Most-played artist

Rank Tiers

Computed automatically via trigger on hours_listened update:

Hours	Tier
0	🌱 Seed Listener
5	🍃 Sprout Wave
15	🎧 Pulse Rider
35	🌊 Echo Drift
60	🎶 Resonance
100	📀 Vinyl Hunter
160	🎵 Frequency Soul
250	🌌 NovaBeat
350	💿 Harmonic Flow
500	🔥 Reverb X
700	⚡ Soundrift
950	🌠 Celestia Tone
1200	🎼 Wave Architect
1600	🌈 Spectrum Lord
2000	👑 Eternal Echo

Playlists

Column	Type	Description
`id`	uuid	Primary key
`created_at`	timestamptz	Creation time
`user_id`	uuid	Owner’s user ID
`name`	text	Playlist name
`description`	text	Optional description
`cover_url`	text	Cover image URL
`tags`	text[]	Array of genre/label tags
`like_count`	int	Community likes
`share_code`	text	Unique shareable code
`is_public`	boolean	Visibility flag
`updated_at`	timestamptz	Last modification time

Row-Level Security

Users can view their own playlists + any is_public = true playlist
Users can update/delete only their own playlists

Playlist Songs

Junction table for playlist ↔ song relationships with ordering.

Column	Type	Description
`id`	uuid	Primary key
`playlist_id`	uuid	→ `playlists.id`
`song_id`	uuid	→ `songs.id`
`added_at`	timestamptz	When added
`order_index`	int	Position in playlist

Songs

Cached metadata from YouTube Music.

Column	Type	Description
`id`	uuid	Primary key
`created_at`	timestamptz	Cache time
`video_id`	text	YouTube video ID
`title`	text	Song title
`artist`	text	Artist name
`album`	text	Album name
`thumbnail_url`	text	YouTube thumbnail URL
`duration_seconds`	int	Length in seconds

Favorites

Column	Type	Description
`id`	uuid	Primary key
`user_id`	uuid	→ `profiles.id`
`song_id`	uuid	→ `songs.id`
`created_at`	timestamptz	When favorited

Play Sessions

Listening history for analytics and recommendations.

Column	Type	Description
`id`	uuid	Primary key
`created_at`	timestamptz	Play start time
`user_id`	uuid	→ `profiles.id`
`song_id`	uuid	→ `songs.id`
`play_duration_seconds`	int	How long user listened
`was_skipped`	boolean	True if skipped before 30s
`completed`	boolean	True if listened >30s

User Actions

Analytics for recommendation engine.

Column	Type	Description
`id`	uuid	Primary key
`created_at`	timestamptz	Action time
`user_id`	uuid	→ `profiles.id`
`song_id`	uuid	→ `songs.id`
`action_type`	text	`PLAY`, `SKIP`, `COMPLETE`, `LIKE`, `UNLIKE`
`context`	text	Where the action happened (SEARCH, PLAYLIST, RADIO)

Achievements

Badge definitions.

Column	Type	Description
`id`	uuid	Primary key
`created_at`	timestamptz	Creation time
`code`	text	Unique slug (e.g. `night_owl`)
`name`	text	Display name (e.g. “Night Owl”)
`description`	text	What you did to earn it
`emoji`	text	🦉 etc.
`xp_value`	int	XP awarded
`requirement_type`	text	`STREAK`, `PLAY_COUNT`, `HOURS`, `DISCOVERY`, `PLAYLIST_COUNT`
`requirement_value`	int	Threshold to unlock

Built-in Badges

Code	Name	Emoji	Requirement
`first_listen`	First Listen	🎵	1st play
`night_owl`	Night Owl	🦉	Play at 2AM
`explorer`	Explorer	🧭	Discover 10 new artists
`playlist_pro`	Playlist Pro	📋	Create 5 playlists
`early_bird`	Early Bird	🐦	Play before 7AM
`marathoner`	Marathoner	🏃	50+ hours listened

User Achievements

Junction table: which user unlocked which badge.

Column	Type	Description
`id`	uuid	Primary key
`user_id`	uuid	→ `profiles.id`
`achievement_id`	uuid	→ `achievements.id`
`unlocked_at`	timestamptz	When earned
`seen`	boolean	Whether user has viewed it

Premium Requests

Payment verification queue for admin approval.

Column	Type	Description
`id`	uuid	Primary key
`created_at`	timestamptz	Request time
`user_id`	uuid	→ `profiles.id` (nullable)
`email`	text	User email
`plan`	text	Which plan purchased
`order_id`	text	Razorpay order ID
`payment_id`	text	Razorpay payment ID
`amount`	int	Amount in paise
`currency`	text	`INR`
`status`	text	`pending`, `approved`, `rejected`

Indexes

-- Performance indexes for leaderboard queries
CREATE INDEX idx_profiles_hours_listened ON public.profiles(hours_listened DESC);
CREATE INDEX idx_profiles_rank_tier ON public.profiles(rank_tier);
CREATE INDEX idx_playlist_songs_playlist_id ON public.playlist_songs(playlist_id);
CREATE INDEX idx_favorites_user_id ON public.favorites(user_id);
CREATE INDEX idx_play_sessions_user_id ON public.play_sessions(user_id);
CREATE INDEX idx_user_actions_user_id ON public.user_actions(user_id);

Triggers

Auto-Rank Assignment

CREATE TRIGGER on_profile_rank_update
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW WHEN (NEW.hours_listened IS DISTINCT FROM OLD.hours_listened)
  EXECUTE FUNCTION auto_assign_rank_tier();

Auto-Level Up

CREATE TRIGGER on_profile_level_up
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW
  EXECUTE FUNCTION auto_update_xp_level();

For more details, see the Self-Hosting Guide where the full SQL setup script is documented.

Architecture Gamification System