Database Schema Definition

Relevant source files

The following files were used as context for generating this wiki page:

• db/playlist_analyses.sql
• db/song_analyses.sql
• db/spotify_oauth_states.sql
• app/crud/analysis.py
• app/schemas/spotify.py
• TEST_PLAN.md

The MoodTune backend utilizes a PostgreSQL database managed via Supabase to store user-related data, Spotify integration details, and audio analysis results. The schema is designed to support the processing of both individual songs and full Spotify playlists, maintaining a history of mood classifications and technical audio features.

The architecture relies on Supabase's authentication system and leverages Row Level Security (RLS) to ensure that users can only access their own analysis data. Data interaction is handled directly through the Supabase Python client without an ORM like SQLAlchemy.

Sources: CLAUDE.md:8-10, README.md:3-3, db/playlist_analyses.sql:12-24

Core Data Entities

The database is structured around three primary functional areas: mood analysis storage, Spotify integration state management, and user connection persistence.

Analysis Storage

The system stores two types of analysis: individual song analyses and playlist-level analyses. Both use JSONB columns to store complex mood result objects, which include primary mood labels, confidence scores, and raw audio features.

Playlist Analyses Table

This table stores the results of analyzing a complete Spotify playlist.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier (gen_random_uuid)
user_id	UUID	NOT NULL, FK	Reference to auth.users(id)
playlist_id	TEXT	NOT NULL	The Spotify Playlist ID
playlist_name	TEXT	NOT NULL	Name of the playlist at time of analysis
mood_results	JSONB	NOT NULL	Calculated mood metrics and track stats
created_at	TIMESTAMPTZ	NOT NULL	Default to NOW()

Sources: db/playlist_analyses.sql:1-8

Song Analyses Table

Stores results for individual tracks, whether uploaded as files or analyzed via external URLs.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier
user_id	UUID	NOT NULL, FK	Reference to auth.users(id)
track_id	TEXT	-	Optional Spotify Track ID
track_name	TEXT	NOT NULL	Name of the song
artist_name	TEXT	NOT NULL	Name of the artist
mood_results	JSONB	NOT NULL	Librosa-derived features and mood tags
created_at	TIMESTAMPTZ	NOT NULL	Timestamp of record creation

Sources: db/song_analyses.sql:1-9

Entity Relationship Overview

The following diagram illustrates the relationship between the project's data structures and the Supabase Auth system.

Integration and Security

Spotify OAuth State Management

To prevent CSRF attacks during the Spotify OAuth flow, the system maintains a short-lived state store.

CREATE TABLE IF NOT EXISTS spotify_oauth_states (
    state TEXT PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL
);

Sources: db/spotify_oauth_states.sql:2-7

Row Level Security (RLS)

The database implements a "defense-in-depth" strategy using RLS. While the backend often uses a service role key to bypass these checks, the policies ensure that direct client access (via the anon key) remains restricted to the owner's data.

• Select Policy: USING (auth.uid() = user_id)
• Insert Policy: WITH CHECK (auth.uid() = user_id)
• Delete Policy: USING (auth.uid() = user_id)

Sources: db/song_analyses.sql:14-24, db/playlist_analyses.sql:13-23

Indexing Strategy

To optimize performance for history retrieval, composite indices are used on the analysis tables to support frequent queries filtered by user and sorted by date.

CREATE INDEX IF NOT EXISTS idx_playlist_analyses_user_id_created_at
    ON playlist_analyses(user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_song_analyses_user_id_created_at
    ON song_analyses(user_id, created_at DESC);

Sources: db/playlist_analyses.sql:10-11, db/song_analyses.sql:11-12

Data Interaction Logic

The CRUD layer handles data persistence asynchronously. For example, the playlist analysis creation process converts the user_id to a string and inserts the complex mood_results dictionary into the JSONB column.

async def create_playlist_analysis(
    user_id: UUID,
    playlist_id: str,
    playlist_name: str,
    mood_results: dict,
) -> dict:
    # ... insertion logic using Supabase client ...
    resp = (
        _supabase.table("playlist_analyses")
        .insert({
            "user_id": str(user_id),
            "playlist_id": playlist_id,
            "playlist_name": playlist_name,
            "mood_results": mood_results,
        })
        .execute()
    )

Sources: app/crud/analysis.py:9-30

Summary

The MoodTune database schema provides a robust foundation for mood-tracking applications. By combining structured relational data for users and connections with flexible JSONB storage for analytical results, the system manages the high-dimensional data produced by audio analysis services while maintaining strict security via Supabase RLS and OAuth state validation.