Your music, beautifully tracked. All yours. (coming soon) teal.fm
teal-fm atproto
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227CREATE TABLE artists ( mbid UUID PRIMARY KEY, name TEXT NOT NULL, play_count INTEGER DEFAULT 0);
-- releases are synologous to 'albums'CREATE TABLE releases ( mbid UUID PRIMARY KEY, name TEXT NOT NULL, play_count INTEGER DEFAULT 0);
-- recordings are synologous to 'tracks' BUT tracks can be in multiple releases!CREATE TABLE recordings ( mbid UUID PRIMARY KEY, name TEXT NOT NULL, play_count INTEGER DEFAULT 0);
CREATE TABLE plays ( uri TEXT PRIMARY KEY, did TEXT NOT NULL, rkey TEXT NOT NULL, cid TEXT NOT NULL, isrc TEXT, duration INTEGER, track_name TEXT NOT NULL, played_time TIMESTAMP WITH TIME ZONE, processed_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(), release_mbid UUID, release_name TEXT, recording_mbid UUID, submission_client_agent TEXT, music_service_base_domain TEXT, origin_url TEXT, FOREIGN KEY (release_mbid) REFERENCES releases (mbid), FOREIGN KEY (recording_mbid) REFERENCES recordings (mbid));
CREATE INDEX idx_plays_release_mbid ON plays (release_mbid);
CREATE INDEX idx_plays_recording_mbid ON plays (recording_mbid);
CREATE INDEX idx_plays_played_time ON plays (played_time);
CREATE INDEX idx_plays_did ON plays (did);
CREATE TABLE play_to_artists ( play_uri TEXT, -- references plays(uri) artist_mbid UUID REFERENCES artists (mbid), artist_name TEXT, -- storing here for ease of use when joining PRIMARY KEY (play_uri, artist_mbid), FOREIGN KEY (play_uri) REFERENCES plays (uri));
CREATE INDEX idx_play_to_artists_artist ON play_to_artists (artist_mbid);
-- Profiles tableCREATE TABLE profiles ( did TEXT PRIMARY KEY, handle TEXT, display_name TEXT, description TEXT, description_facets JSONB, avatar TEXT, -- IPLD of the image, bafy... banner TEXT, created_at TIMESTAMP WITH TIME ZONE);
-- User featured items tableCREATE TABLE featured_items ( did TEXT PRIMARY KEY, mbid TEXT NOT NULL, type TEXT NOT NULL);
-- Statii table (status records)CREATE TABLE statii ( uri TEXT PRIMARY KEY, did TEXT NOT NULL, rkey TEXT NOT NULL, cid TEXT NOT NULL, record JSONB NOT NULL, indexed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());
CREATE INDEX idx_statii_did_rkey ON statii (did, rkey);
-- Materialized view for artists' play countsCREATE MATERIALIZED VIEW mv_artist_play_counts ASSELECT a.mbid AS artist_mbid, a.name AS artist_name, COUNT(p.uri) AS play_countFROM artists a LEFT JOIN play_to_artists pta ON a.mbid = pta.artist_mbid LEFT JOIN plays p ON p.uri = pta.play_uriGROUP BY a.mbid, a.name;
CREATE UNIQUE INDEX idx_mv_artist_play_counts ON mv_artist_play_counts (artist_mbid);
-- Materialized view for releases' play countsCREATE MATERIALIZED VIEW mv_release_play_counts ASSELECT r.mbid AS release_mbid, r.name AS release_name, COUNT(p.uri) AS play_countFROM releases r LEFT JOIN plays p ON p.release_mbid = r.mbidGROUP BY r.mbid, r.name;
CREATE UNIQUE INDEX idx_mv_release_play_counts ON mv_release_play_counts (release_mbid);
-- Materialized view for recordings' play countsCREATE MATERIALIZED VIEW mv_recording_play_counts ASSELECT rec.mbid AS recording_mbid, rec.name AS recording_name, COUNT(p.uri) AS play_countFROM recordings rec LEFT JOIN plays p ON p.recording_mbid = rec.mbidGROUP BY rec.mbid, rec.name;
CREATE UNIQUE INDEX idx_mv_recording_play_counts ON mv_recording_play_counts (recording_mbid);
-- Global play count materialized viewCREATE MATERIALIZED VIEW mv_global_play_count ASSELECT COUNT(uri) AS total_plays, COUNT(DISTINCT did) AS unique_listenersFROM plays;
CREATE UNIQUE INDEX idx_mv_global_play_count ON mv_global_play_count(total_plays);
-- Top artists in the last 30 daysCREATE MATERIALIZED VIEW mv_top_artists_30days ASSELECT a.mbid AS artist_mbid, a.name AS artist_name, COUNT(p.uri) AS play_countFROM artists aINNER JOIN play_to_artists pta ON a.mbid = pta.artist_mbidINNER JOIN plays p ON p.uri = pta.play_uriWHERE p.played_time >= NOW() - INTERVAL '30 days'GROUP BY a.mbid, a.nameORDER BY COUNT(p.uri) DESC;
-- Top releases in the last 30 daysCREATE MATERIALIZED VIEW mv_top_releases_30days ASSELECT r.mbid AS release_mbid, r.name AS release_name, COUNT(p.uri) AS play_countFROM releases rINNER JOIN plays p ON p.release_mbid = r.mbidWHERE p.played_time >= NOW() - INTERVAL '30 days'GROUP BY r.mbid, r.nameORDER BY COUNT(p.uri) DESC;
-- Top artists for user in the last 30 daysCREATE MATERIALIZED VIEW mv_top_artists_for_user_30days ASSELECT prof.did, a.mbid AS artist_mbid, a.name AS artist_name, COUNT(p.uri) AS play_countFROM artists aINNER JOIN play_to_artists pta ON a.mbid = pta.artist_mbidINNER JOIN plays p ON p.uri = pta.play_uriINNER JOIN profiles prof ON prof.did = p.didWHERE p.played_time >= NOW() - INTERVAL '30 days'GROUP BY prof.did, a.mbid, a.nameORDER BY COUNT(p.uri) DESC;
-- Top artists for user in the last 7 daysCREATE MATERIALIZED VIEW mv_top_artists_for_user_7days ASSELECT prof.did, a.mbid AS artist_mbid, a.name AS artist_name, COUNT(p.uri) AS play_countFROM artists aINNER JOIN play_to_artists pta ON a.mbid = pta.artist_mbidINNER JOIN plays p ON p.uri = pta.play_uriINNER JOIN profiles prof ON prof.did = p.didWHERE p.played_time >= NOW() - INTERVAL '7 days'GROUP BY prof.did, a.mbid, a.nameORDER BY COUNT(p.uri) DESC;
-- Top releases for user in the last 30 daysCREATE MATERIALIZED VIEW mv_top_releases_for_user_30days ASSELECT prof.did, r.mbid AS release_mbid, r.name AS release_name, COUNT(p.uri) AS play_countFROM releases rINNER JOIN plays p ON p.release_mbid = r.mbidINNER JOIN profiles prof ON prof.did = p.didWHERE p.played_time >= NOW() - INTERVAL '30 days'GROUP BY prof.did, r.mbid, r.nameORDER BY COUNT(p.uri) DESC;
-- Top releases for user in the last 7 daysCREATE MATERIALIZED VIEW mv_top_releases_for_user_7days ASSELECT prof.did, r.mbid AS release_mbid, r.name AS release_name, COUNT(p.uri) AS play_countFROM releases rINNER JOIN plays p ON p.release_mbid = r.mbidINNER JOIN profiles prof ON prof.did = p.didWHERE p.played_time >= NOW() - INTERVAL '7 days'GROUP BY prof.did, r.mbid, r.nameORDER BY COUNT(p.uri) DESC;