bvandeusen
6e0d0e5723
Build a persistent, decaying model of each user's taste, recomputed daily, that later phases consume across every recommendation surface. Phase 1 only BUILDS the object — no behaviour change to what's surfaced yet. Core mechanic — graded engagement (replaces binary was_skipped for learning; was_skipped stays for History): a play's completion ratio maps to a signal in [-1,+1] via two linear ramps (instant-skip → -1, ~0.30 neutral, ≥0.90 → +1). Time-decayed (half-life ~75d) so recent behaviour dominates and the profile tracks drift. Per operator constraints: - No explicit dislike button — negatives come only from passive behaviour (early skips). Nothing recorded to regret or opt out of. - Negatives are track-scoped; artist/tag weight is the decayed SUM of their tracks' engagement, so one skip nets out against many good plays (a DB test asserts a liked artist stays positive despite an early-skipped track). A floor clamp bounds how negative any single entity can get. - migration 0035: taste_profile_artists / taste_profile_tags (signed weight, indexed by (user, weight DESC)). - internal/taste: engagement.go (pure curve + decay) + profile.go (accumulate plays + like bonuses, floor damping, size caps, atomic-replace). - scheduler: rebuildUserDaily recomputes the profile before the playlist build (so phase 2 can read it), best-effort — a taste failure never blocks playlist building. Wired into the daily job + startup catch-up only (not manual/lazy rebuilds). - tests: pure (engagement curve, decay, ranking, floor, genre split) + DB-backed (positive/negative weights, aggregation-protects-artist, like bonus, atomic replace). All green vs real Postgres. Config knobs live in taste.DefaultConfig() for now; wiring them into the server RecommendationConfig is a later follow-up. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
67 lines
2.3 KiB
SQL
67 lines
2.3 KiB
SQL
-- Taste profile (#796 phase 1). The daily build computes signed artist/tag
|
|
-- weights in Go (graded engagement + decay + like bonus) and atomic-replaces
|
|
-- them here. Read queries serve "top taste" lookups (used in phase 2).
|
|
|
|
-- name: ListPlayEngagementInputsForUser :many
|
|
-- One row per play in the decay-relevant window. completion is the play's
|
|
-- completion ratio (precomputed column when present, else duration_played /
|
|
-- track duration, clamped to [0,1]); age_days drives the time-decay. Genre
|
|
-- is split into tags in Go. Quarantined tracks are excluded.
|
|
SELECT
|
|
t.artist_id,
|
|
t.genre,
|
|
LEAST(GREATEST(
|
|
COALESCE(pe.completion_ratio,
|
|
pe.duration_played_ms::float8 / NULLIF(t.duration_ms, 0),
|
|
0), 0), 1)::float8 AS completion,
|
|
(EXTRACT(epoch FROM now() - pe.started_at) / 86400.0)::float8 AS age_days
|
|
FROM play_events pe
|
|
JOIN tracks t ON t.id = pe.track_id
|
|
WHERE pe.user_id = $1
|
|
AND pe.started_at > now() - ($2::float8 * INTERVAL '1 day')
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM lidarr_quarantine q
|
|
WHERE q.user_id = $1 AND q.track_id = t.id
|
|
);
|
|
|
|
-- name: ListLikedTrackTasteInputsForUser :many
|
|
-- (artist_id, genre) for each track the user has explicitly liked. Feeds the
|
|
-- track-like bonus into the liked track's artist and tags.
|
|
SELECT t.artist_id, t.genre
|
|
FROM general_likes gl
|
|
JOIN tracks t ON t.id = gl.track_id
|
|
WHERE gl.user_id = $1;
|
|
|
|
-- name: ListLikedArtistIDsForUser :many
|
|
-- Artists the user has explicitly liked; feeds the artist-like bonus.
|
|
SELECT artist_id FROM general_likes_artists WHERE user_id = $1;
|
|
|
|
-- name: DeleteTasteProfileArtistsForUser :exec
|
|
DELETE FROM taste_profile_artists WHERE user_id = $1;
|
|
|
|
-- name: InsertTasteProfileArtist :exec
|
|
INSERT INTO taste_profile_artists (user_id, artist_id, weight)
|
|
VALUES ($1, $2, $3);
|
|
|
|
-- name: DeleteTasteProfileTagsForUser :exec
|
|
DELETE FROM taste_profile_tags WHERE user_id = $1;
|
|
|
|
-- name: InsertTasteProfileTag :exec
|
|
INSERT INTO taste_profile_tags (user_id, tag, weight)
|
|
VALUES ($1, $2, $3);
|
|
|
|
-- name: ListTasteProfileArtistsForUser :many
|
|
-- Top-weighted taste artists (phase-2 consumption + tests).
|
|
SELECT artist_id, weight
|
|
FROM taste_profile_artists
|
|
WHERE user_id = $1
|
|
ORDER BY weight DESC
|
|
LIMIT $2;
|
|
|
|
-- name: ListTasteProfileTagsForUser :many
|
|
SELECT tag, weight
|
|
FROM taste_profile_tags
|
|
WHERE user_id = $1
|
|
ORDER BY weight DESC
|
|
LIMIT $2;
|