-- 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) );