The three migrations added on this branch (cluster mixes, mix suggestions, known songs) haven't been released, so they're combined into 20261113150000_cluster_mixes.sql. The resulting schema is identical. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_018opPJMJjLWr4RqdG6vuK6p
46 lines
2.7 KiB
SQL
46 lines
2.7 KiB
SQL
-- A rule that periodically clusters a user's recent listens by genre and
|
|
-- maintains one Subsonic "mix" playlist per cluster.
|
|
CREATE TABLE "cluster_mix_rule"
|
|
(
|
|
id UUID NOT NULL PRIMARY KEY,
|
|
tracking_user VARCHAR(250) NOT NULL, -- Listenbrainz user to cluster
|
|
months INT NOT NULL DEFAULT 3, -- How many months of listens to cluster
|
|
cluster_count INT, -- Number of mixes. NULL = choose automatically
|
|
playlist_size INT NOT NULL DEFAULT 50, -- Tracks per mix
|
|
familiar_ratio DOUBLE PRECISION NOT NULL DEFAULT 0.3, -- Share of each mix made of recently played songs
|
|
name_template VARCHAR(250) NOT NULL DEFAULT 'Mix: {label}', -- {label} is replaced by the mix's genres
|
|
refresh_hours INT NOT NULL DEFAULT 24, -- How often to rebuild the mixes
|
|
last_run TIMESTAMPTZ, -- When the mixes were last rebuilt
|
|
discovery_count INT NOT NULL DEFAULT 15 -- New songs to suggest per mix
|
|
);
|
|
|
|
-- The Subsonic playlists currently owned by a cluster mix rule.
|
|
CREATE TABLE "cluster_mix_playlist"
|
|
(
|
|
id UUID NOT NULL PRIMARY KEY,
|
|
rule_id UUID NOT NULL REFERENCES "cluster_mix_rule" (id) ON DELETE CASCADE,
|
|
playlist_id VARCHAR(250) NOT NULL, -- ID of the playlist in subsonic
|
|
tags TEXT[] NOT NULL, -- Genres describing the cluster, used to keep playlists stable across rebuilds
|
|
name VARCHAR(250), -- Name of the playlist in subsonic
|
|
position INT NOT NULL DEFAULT 0, -- Order of the mix, most listened first
|
|
known_song_ids TEXT[] NOT NULL DEFAULT '{}' -- Songs in the playlist the user played in the clustered period
|
|
);
|
|
|
|
-- Songs not in the library, recommended by Listenbrainz and matched to a mix by genre
|
|
CREATE TABLE "mix_suggestion"
|
|
(
|
|
id UUID NOT NULL PRIMARY KEY,
|
|
rule_id UUID NOT NULL REFERENCES "cluster_mix_rule" (id) ON DELETE CASCADE,
|
|
mix_id UUID REFERENCES "cluster_mix_playlist" (id) ON DELETE SET NULL,
|
|
recording_mbid VARCHAR(36) NOT NULL,
|
|
title VARCHAR(500) NOT NULL,
|
|
artist VARCHAR(500) NOT NULL,
|
|
release VARCHAR(500),
|
|
cover_url TEXT,
|
|
score DOUBLE PRECISION NOT NULL,
|
|
status VARCHAR(16) NOT NULL DEFAULT 'new', -- new, liked or dismissed
|
|
youtube_id VARCHAR(32), -- Cached YouTube match
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (rule_id, recording_mbid)
|
|
);
|