Recommendation SQL & RPCs

Relevant source files

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

• supabase/migrations/20260603143952_feed_tuning_v1.sql
• supabase/migrations/20260601180027_recommendation_health_rpc.sql
• supabase/migrations/20260531160846_recommendation_v2.sql
• apps/web/lib/queries/feed.ts
• apps/web/lib/recommendation-health/metrics.ts
• supabase/scripts/maintenance/feed-tuning-baseline-snapshot.sql

The Recommendation system in Kocteau is a hybrid music discovery engine that prioritizes human taste as source material, using lightweight PostgreSQL-based algorithms for routing. It leverages explicit user preferences, inferred activity signals, and editorial curation to populate the "For You" feed.

Sources: README.md, AGENTS.md:12, supabase/migrations/20260531160846_recommendation_v2.sql:1-12

Recommendation Architecture

The architecture relies heavily on Supabase RPCs and Postgres logic to calculate scores in real-time. It avoids external ML dependencies, using user_preference_tags and entity_preference_tags as the core vocabulary for matching.

Core Scoring Components

The system calculates a base_score for candidate reviews based on several weighted factors:

• Taste Match: Alignment between the user's taste profile and the entity/author tags.
• Social Signals: Follower status and previous "affinity" (likes/bookmarks) with specific authors.
• Engagement: Raw rating, like counts, and comment counts.
• Recency: A time-decay function ensures fresh content is prioritized.
• Quality: A review_depth_score rewards longer, written reviews over simple ratings.

Sources: supabase/migrations/20260603143952_feed_tuning_v1.sql:133-163, README.md

Data Flow Diagram

This diagram illustrates how user signals are transformed into a ranked feed.

The diagram shows the transformation of explicit and inferred signals into a personalized taste profile used by the scoring RPC.
Sources: supabase/migrations/20260603143952_feed_tuning_v1.sql:27-163

Key Database Functions & RPCs

get_recommended_review_ids

This is the primary engine for the "For You" feed. It aggregates signals into a single score and assigns a "reason" for each recommendation (e.g., taste_match, following).

Parameter	Type	Default	Description
p_limit	integer	8	Maximum number of results to return.
p_cursor_score	numeric	NULL	Cursor score for pagination.
p_cursor_created_at	timestamp	NULL	Cursor timestamp for pagination.
p_cursor_id	uuid	NULL	Cursor ID for pagination ties.

Recommendation Reasons:

1. entity_taste: Strong match between user preferences and track tags.
2. taste_match: Match between user and author taste profiles.
3. following: Review by an author the user follows.
4. familiar_entity: Review for a track the user has interacted with.
5. author_affinity: Review by an author the user has liked/bookmarked previously.
6. popular_recent: General fallback for high-quality recent content.

Sources: supabase/migrations/20260603143952_feed_tuning_v1.sql:18-241, apps/web/lib/queries/feed.ts:168-181

infer_entity_preference_tags_from_user

This function automatically populates entity_preference_tags when a user interacts with a track, effectively "training" the system on what specific music entities represent based on the taste of the listeners who review them.

Sources: supabase/migrations/20260531160846_recommendation_v2.sql:71-110

Diversity and Tuning

To prevent a "bubble" where familiar tracks or authors dominate, the system applies diversity penalties during the finalization phase.

Diversity Logic Flow

This diagram shows how rank-based penalties reduce the score of redundant entries from the same author or entity.
Sources: supabase/migrations/20260603143952_feed_tuning_v1.sql:164-187

Penalty Type	Value	Logic
Author Penalty	0.55	Multiplied by (author_rank - 1) to downrank multiple reviews from one person.
Entity Penalty	0.95	Multiplied by (entity_rank - 1) to prevent one track from filling the feed.

Sources: supabase/migrations/20260603143952_feed_tuning_v1.sql:178-180

Health and Analytics

The get_recommendation_health_snapshot RPC provides maintainers with aggregate metrics to monitor the efficacy of the recommendation engine.

Metrics Monitored

• Fallback Rate: The frequency at which the system fails to generate personalized recommendations and reverts to the "Latest" feed.
• Open Rate: The percentage of review impressions that lead to a full review open.
• Starter Conversion: Effectiveness of editorial "starter tracks" in prompting new user reviews.
• Tag Coverage: Ensuring preference_tags are adequately distributed across active entities.

Sources: supabase/migrations/20260601180027_recommendation_health_rpc.sql:1-160, apps/web/lib/recommendation-health/metrics.ts:10-45

Health Snapshot Schema

The RPC returns a complex JSONB object structured for the Studio UI:

{
  "window": { "days": 14, "startAt": "...", "endAt": "..." },
  "feed": { "loads": 100, "fallbacks": 5, "fallbackRate": 0.05 },
  "reasons": [{ "reason": "taste_match", "impressions": 50, "openRate": 0.2 }],
  "tagCoverage": [{ "kind": "genre", "taggedTracks": 20, "tagCount": 5 }]
}

Sources: supabase/migrations/20260601180027_recommendation_health_rpc.sql:161-235

Curation and Starter Tracks

When personalized data is sparse (cold-start), the system utilizes starter_tracks. These are curated tracks tagged with preference_tags that match a user's initial onboarding signals.

• Logic: get_starter_tracks ranks curated tracks against user onboarding tags.
• Personalization: When a user reviews a starter track, the editorial tags are copied to entity_preference_tags to bootstrap the recommendation graph.

Sources: README.md, supabase/migrations/20260601180027_recommendation_health_rpc.sql:110-130

Maintenance

Maintainers use baseline snapshots to evaluate ranking changes. The feed-tuning-baseline-snapshot.sql script provides read-only aggregate queries to compare performance before and after tuning migrations.

Sources: supabase/scripts/maintenance/feed-tuning-baseline-snapshot.sql:1-30