-- Playlists: named, ordered lists of tracks that belong to one user. -- -- position is the place of an item in its playlist, counted from 0 without gaps; the code keeps it -- that way inside a transaction (it is not unique, so a shift never trips over itself). A track -- can be in a playlist more than once, which is why an item has its own id. CREATE TABLE playlists ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, name TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', created_at INTEGER NOT NULL, -- unix seconds updated_at INTEGER NOT NULL ); CREATE INDEX idx_playlists_user ON playlists(user_id, updated_at); CREATE TABLE playlist_items ( id INTEGER PRIMARY KEY, playlist_id INTEGER NOT NULL REFERENCES playlists(id) ON DELETE CASCADE, track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE, position INTEGER NOT NULL CHECK (position >= 0), added_at INTEGER NOT NULL ); CREATE INDEX idx_playlist_items_order ON playlist_items(playlist_id, position); CREATE INDEX idx_playlist_items_track ON playlist_items(track_id);