Database Schema & Migrations

Relevant source files

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

• supabase/config.toml
• supabase/migrations/20260603143952_feed_tuning_v1.sql
• README.md
• apps/web/lib/validation/schemas.ts
• package.json
• AGENTS.md

Kocteau utilizes a PostgreSQL database managed via Supabase to store music reviews, user profiles, taste signals, and editorial curation data. The system is designed to be local-first for contributors, using the Supabase CLI to manage migrations, schema linting, and type generation. The schema serves as the foundation for the application's recommendation engine and social discovery features.

Sources: README.md:162-181, CONTRIBUTING.md:27-35

Core Data Model

The database is structured around several core entities that facilitate music reviews and personalized discovery. Central to this is the relationship between users (profiles), music entities, and reviews.

Primary Tables

The following table summarizes the core public tables used in the Kocteau ecosystem:

Table Name	Description	Key Fields
profiles	User account data, including display names, bios, and official status.	id, username, is_official
entities	Music tracks or albums indexed from external providers (e.g., Deezer).	id, provider, provider_id, type
reviews	User-generated listening notes and ratings for specific entities.	id, author_id, entity_id, rating, body
preference_tags	Taxonomy of music descriptors (genre, mood, era).	id, label, kind, slug
user_preference_tags	Explicit or inferred taste signals mapped to specific users.	user_id, tag_id, weight
entity_preference_tags	Descriptor tags mapped to music tracks/albums.	entity_id, tag_id, weight

Sources: README.md:162-181, AGENTS.md:377-385

Entity Relationship Diagram

The following diagram illustrates the relationships between users, reviews, and the taste-based recommendation infrastructure.

Migration Workflow & Tooling

Migrations in Kocteau are versioned and managed through the Supabase CLI. They are stored in the supabase/migrations directory as ordered SQL files that define the schema, Row-Level Security (RLS) policies, and Stored Procedures (RPCs).

Developer Workflow

Contributors interact with the database using the following commands defined in the project configuration:

• Initial Setup: pnpm supabase:start launches the local Docker-backed Supabase stack.
• Resetting State: pnpm supabase:reset applies all migrations and loads seed data.
• Validation: pnpm supabase:lint checks for SQL errors and best practices.
• Type Safety: pnpm supabase:types generates TypeScript definitions based on the current schema.

Sources: package.json:20-40, CONTRIBUTING.md:27-35

Seed Data

The database is initialized with deterministic product configuration using seed files. These include:

• ./seeds/00_preference_tags.sql: Populates the initial music taxonomy.
• ./seeds/10_editorial_starter_picks.sql: Loads curated tracks for the cold-start experience.

Sources: supabase/config.toml:68-71

Recommendation Logic & RPCs

Complex data retrieval, such as the personalized "For You" feed, is handled via SQL functions (Stored Procedures) to ensure performance and security.

Feed Tuning (v1)

The get_recommended_review_ids function implements a multi-stage scoring algorithm to rank reviews for a specific user.

The scoring logic considers several factors:

1. Entity Taste: Match between user tags and track tags (weighted 1.75).
2. Author Affinity: Previous positive interactions with the reviewer.
3. Review Depth: Bonus for longer, written reviews (e.g., 120+ or 280+ characters).
4. Diversity Pressure: Penalties applied to the same author or entity to ensure feed variety.

Sources: supabase/migrations/20260603143952_feed_tuning_v1.sql:31-158, apps/web/lib/recommendations/feed-ranking-sql.test.ts:25-40

Security & Access Control

Kocteau utilizes PostgreSQL Row-Level Security (RLS) and granular grants to protect user data.

Security Configuration

• Schemas: Only public and graphql_public are exposed via the API.
• Permissions: Functions like get_recommended_review_ids have PUBLIC access revoked and are granted specifically to the authenticated role.
• Vault: Sensitive values can be managed via the db.vault configuration.

Sources: supabase/config.toml:10-14, supabase/migrations/20260603143952_feed_tuning_v1.sql:190-194, SECURITY.md:17-21

Conclusion

The Kocteau database schema provides a robust framework for music discovery by combining traditional relational data (profiles, entities) with a weighted signal-based taxonomy (preference tags). Through versioned migrations and local-first CLI tooling, the project ensures that the data architecture remains consistent across development and production environments.