CRUD Operations
Relevant source files
The following files were used as context for generating this wiki page:
CRUD (Create, Read, Update, Delete) operations in the MoodTune backend manage the persistence of mood analysis results and user data within a Supabase PostgreSQL database. The system is designed to handle two primary types of data: individual song analyses and comprehensive playlist analyses.
These operations are abstracted into a crud module, which is called by the API routers to interact with the database using the Supabase Python client. This architecture ensures that business logic for data persistence is separated from the HTTP request handling layer.
Sources: app/crud/init.py:1, CLAUDE.md:8-12
Database Schema and Entities
The system utilizes two main tables in Supabase to store analysis results. Both tables implement Row Level Security (RLS) to ensure that users can only access their own data.
Song Analyses Schema
The song_analyses table stores results for individual audio files or single track lookups.
| Field | Type | Description |
|---|---|---|
id | UUID | Primary key, auto-generated |
user_id | UUID | Reference to auth.users(id), cascade on delete |
track_id | TEXT | Optional Spotify track ID |
track_name | TEXT | Name of the analyzed track |
artist_name | TEXT | Name of the artist |
mood_results | JSONB | Complex object containing mood and features |
created_at | TIMESTAMPTZ | Timestamp of creation, defaults to NOW() |
Sources: db/song_analyses.sql:1-9
Playlist Analyses Schema
The playlist_analyses table stores aggregated mood data for entire Spotify playlists.
| Field | Type | Description |
|---|---|---|
id | UUID | Primary key, auto-generated |
user_id | UUID | Reference to auth.users(id), cascade on delete |
playlist_id | TEXT | Spotify playlist identifier |
playlist_name | TEXT | Name of the playlist at time of analysis |
mood_results | JSONB | Aggregated mood metrics and top tracks |
created_at | TIMESTAMPTZ | Timestamp of creation |
Sources: db/playlist_analyses.sql:1-8
Data Access Patterns
CRUD operations are triggered by API endpoints to either store new analysis results or retrieve historical data for a specific user.
Creation Flow
When a user analyzes a song or playlist, the system calculates mood results and then persists them.
Retrieval and History
History endpoints allow users to fetch their past analyses with pagination support.
Security and Authorization
CRUD operations adhere to a defense-in-depth security model using Supabase Row Level Security (RLS). While the backend uses a service role key that can bypass RLS, the application logic explicitly filters queries by the user_id derived from the Supabase JWT.
-- Example RLS Policy for Song Analyses
CREATE POLICY "Users can view own song analyses"
ON song_analyses FOR SELECT
USING (auth.uid() = user_id);
Sources: db/song_analyses.sql:14-25, app/api/v1/analysis.py:145-150
Implementation Details
Analysis Creation
When creating a record, the system bundles mood classification (e.g., "Happy & Energetic") and raw audio features (tempo, energy, valence) into a JSONB column.
# app/api/v1/song_analysis.py:126-132
await crud_song_analysis.create_song_analysis(
user_id=UUID(current_user["id"]),
track_name=name,
artist_name=artist,
track_id=track_id,
mood_results={**mood, **{"features": features}},
)
Sources: app/api/v1/song_analysis.py:126-132
Analysis Retrieval
Retrieval operations utilize limit and offset parameters to manage large sets of historical data. Results are typically ordered by created_at DESC to show the most recent analyses first.
Sources: app/api/v1/analysis.py:112-117, db/song_analyses.sql:11-12
Conclusion
CRUD operations in the MoodTune backend provide a robust mechanism for persisting complex audio analysis data. By leveraging Supabase's PostgreSQL capabilities and RLS, the system ensures data integrity and user isolation while maintaining a clean separation between API routes and the data persistence layer.