Music Playlist Service (Spotify Playlists)

0) Problem Restatement

Design the playlist feature of a music app. Users create playlists, add and remove songs, reorder them by dragging, and play them in a predictable order. Some playlists are collaborative, so several people edit the same playlist at the same time. The same song can appear more than once in a playlist.

Databricks asked this mostly as data modeling + APIs ("stable item IDs, duplicates, moves, concurrent edits"). Amazon asked a broader version with music search and playback controls.

1) Requirements

1.1 Functional

• Create, rename and delete playlists.
• Add a track (at the end or at a position), remove an item, and move an item.
• Read the playlist in order (paginated), and play it.
• Collaborative playlists with multiple editors.
• Playback controls: play, pause, next, previous, shuffle, repeat (client side, with a server-synced queue).

1.2 Non-Functional

• Deterministic order: everyone sees the same order.
• Concurrent edits don't corrupt the list (no lost items, no duplicated positions).
• Fast reads for popular public playlists (millions of followers).

1.3 Scale Estimates

• 500M playlists, average 60 items, max ~10,000.
• Writes: ~50M playlist edits/day ≈ 600/sec.
• Reads: ~2B playlist loads/day ≈ 25K/sec, so heavily cached.

1.4 API Design

• POST /v1/playlists { name, collaborative }
• POST /v1/playlists/{id}/items { track_id, after_item_id? } → { item_id, version }
• DELETE /v1/playlists/{id}/items/{item_id}
• POST /v1/playlists/{id}/items/{item_id}/move { after_item_id }
• GET /v1/playlists/{id}/items?cursor=&limit=100
• Writes accept If-Match: version for safe concurrent edits (optional).

2) Core Design Decisions

2.1 Stable item IDs

Each entry in a playlist gets its own item_id, separate from track_id. That's how we support the same song twice, and how "remove the second copy" or "move this one" is unambiguous even while others are editing.

2.2 How to store order

• Option A: position numbers (1, 2, 3, ...): moving item 500 to the top means renumbering 499 rows. That's slow, and concurrent moves conflict.
• Option B: fractional index (our choice): each item has a sortable rank key. To insert between ranks "a" and "b", pick a key in between (e.g., "an"). A move only updates one row. When keys get too long after many inserts in the same spot, a background job rebalances the playlist.
• Option C: linked list (prev/next pointers): moves are cheap, but reading in order and paginating is slow, and concurrent pointer updates are error-prone.

2.3 Architecture Diagram

flowchart LR
    C["Clients - app, web, speakers"] --> API["Playlist API"]
    API --> DB[("Playlist DB - items sorted by rank")]
    API --> K[("Change events")]
    K --> CACHE["Cache invalidation"]
    K --> PUSH["Push updates to open clients"]
    API --> CACHE2[("Playlist cache")]
    PUSH --> C

3) Data Model

CREATE TABLE playlists (
  playlist_id UUID PRIMARY KEY, owner_id UUID, name TEXT,
  collaborative BOOLEAN, version BIGINT, updated_at TIMESTAMP
);
CREATE TABLE playlist_items (
  playlist_id UUID, item_id UUID, track_id TEXT,
  rank TEXT,              -- fractional index, e.g. 'a0', 'a0V', 'a1'
  added_by UUID, added_at TIMESTAMP,
  PRIMARY KEY (playlist_id, item_id)
);
CREATE INDEX ON playlist_items (playlist_id, rank, item_id);   -- ordered reads; item_id breaks ties
CREATE TABLE playlist_collaborators (playlist_id UUID, user_id UUID, role TEXT);

4) Key Flows

4.1 Add at the end

Read the current max rank, create a key just after it, insert the item, bump playlist.version, and emit a change event.

4.2 Move

4.3 Read

5) Deep Dive — Concurrent edits (collaborative playlists)

• Most edits don't conflict: two users adding different songs both succeed, since each insert is its own row.
• Two users move the same item: last write wins on that item's rank. The item ends up in one place, never duplicated or lost.
• User A deletes item X while user B moves X after Y: the move fails with "item not found". The client refreshes.
• User B inserts after item Y, which was just deleted: fall back to inserting after Y's old position (the server remembers deleted items' ranks briefly), or return a conflict so the client can retry.
• Optimistic concurrency option: clients send If-Match: version. If the playlist changed, they get a 409, re-fetch the changes since their version, and replay the edit. This is safest for bulk operations like "sort by title".
• Live updates: open clients receive change events (via WebSocket/push), so everyone's view converges quickly.

6) Playback Queue (the Amazon variant)

• The play queue is separate from the playlist: now_playing, up_next[], history[], plus shuffle and repeat modes.
• Shuffle: generate a random permutation once (with a seed), so "previous" and "next" are consistent, and store it with the queue.
• Repeat one/all: when the queue ends, either replay the current item or restart the order.
• Sync the queue state to the server, so switching devices continues where you left off (Spotify Connect style).
• Music search is a separate service: a search index over tracks, artists and albums, with typeahead.

7) Trade-offs & Alternatives

8) Common Follow-up Questions

• "Undo?" Keep an edit log per playlist (who did what). Undo applies the reverse operation.
• "Offline edits?" Queue operations on the device with their item IDs, and replay them on reconnect with the same conflict rules.
• "Huge public playlists?" Cache the first pages at the CDN, and push invalidations on change.

9) Wrap-Up

Give every playlist entry its own item ID, store order with fractional-index rank keys so moves touch one row, and read with (rank, item_id) cursors for stable pagination. Collaborative edits use row-level last-write-wins with optional version checks and live push updates, and the playback queue is a separate, synced object with a stored shuffle order.