max / makenotwork

541 B · 12 lines History Blame Raw

1	-- Track unique listeners per item (authenticated users only).
2	-- play_count remains total plays (including replays -- valuable for creators).
3	-- unique_play_count tracks distinct listeners.
4	CREATE TABLE IF NOT EXISTS user_plays (
5	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
6	item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
7	played_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
8	PRIMARY KEY (user_id, item_id)
9	);
10
11	ALTER TABLE items ADD COLUMN IF NOT EXISTS unique_play_count INTEGER NOT NULL DEFAULT 0;
12