Collaborative Playlist · Architecture

Complete system map

Command and read path

Web / mobile client→API gateway→Playlist service→Owner partition

Owner partition↔SQL primary shard→Read replica→Redis snapshot cache

Committed event path

SQL outbox→Outbox publisher→Kafka by playlist_id→Fanout workers

Kafka→WebSocket gateways→Subscribed clients+Cache projector

Media boundary

Playlist service→ media_idCatalog service→Track metadata→Audio CDN

The playlist domain stores media references and ordering. Catalog metadata and audio delivery remain separate services.

Components and responsibilities

Component	Responsibility	Why it exists
API gateway	TLS, authentication, quotas, request IDs	Keeps edge policy separate from playlist logic.
Playlist service	Reads, command validation, routing, response assembly	Stateless instances scale request processing horizontally.
Owner partition	Serialize active mutations for one playlist	One commit order makes move and delete races deterministic.
SQL primary shard	Canonical playlists, membership, entries, versions, mutations, outbox	One transaction preserves authorization, order, deduplication, and publication intent.
Redis	Popular public snapshots and short-lived presence	Read-heavy traffic avoids repeated SQL work; durable truth remains in SQL.
Kafka	Ordered committed events by playlist ID	Fanout, cache projection, audit export, and analytics scale independently.
WebSocket gateway	Persistent connections and playlist subscriptions	Connection state stays outside command servers.
Catalog service	Track availability and display metadata	Playlist membership survives catalog metadata changes.

Relational source of truth

playlist(id PK, owner_id, title, visibility, version, shard_key)playlist_member(playlist_id, user_id, role, membership_epoch)playlist_entry(playlist_id, entry_id, position_key, media_id, added_by)playlist_mutation(playlist_id, version, operation_id UNIQUE, actor_id, payload)outbox(event_id PK, playlist_id, version, payload, published_at)

Shard and cluster rows by playlist_id. Index entries by (playlist_id, position_key) and members by (playlist_id, user_id). The mutation log uses (playlist_id, version) for reconnect replay.

Open playlist interaction

The gateway authenticates the caller and forwards the playlist ID.
The playlist service checks Redis for a versioned snapshot of a popular public playlist.
A miss reads playlist metadata, membership, and one ordered entry page from SQL.
The service resolves bounded track metadata through the catalog service and returns entries plus playlist version.
The client opens a WebSocket subscription with its last applied version.
The gateway replays later mutation events or directs the client to refresh a snapshot when the retained log has a gap.

Mutation interaction

The client applies a local add, remove, or move and sends operation_id, base_version, and stable entry anchors.
The playlist service hashes playlist_id to the owner partition.
The owner authenticates the actor, reads membership, and checks the unique operation ID.
Inside one SQL transaction, lock the playlist row or compare its version, validate anchors, update the entry, and increment the playlist version.
The same transaction appends the canonical mutation and an outbox event.
Commit establishes the authoritative result; the service acknowledges the sender with the accepted position and version.
The outbox publisher sends the event to Kafka. Fanout workers deliver it to WebSocket gateways and the cache projector advances Redis.
Clients apply events in version order, detect gaps, and reconcile pending optimistic operations.

Why a relational database

Transactional invariantMembership authorization, entry mutation, version increment, deduplication record, and outbox event commit together.

Access patternsPoint reads by playlist ID and ordered range scans by position key map directly to indexes.

Bounded aggregateA playlist is a natural transaction and partition boundary with modest row counts.

Operational recoveryBackups, replicas, constraints, and transactional logs provide a clear durable authority.

A document store simplifies retrieval of small playlists but creates large-document rewrites, concurrency control, and pagination limits. A pure event store supports history well but requires a materialized list and additional transaction handling for membership and deduplication.

Sharding and hot playlists

Place every canonical row for one playlist on the shard selected by hash(playlist_id); mutations stay single-shard.
Partition Kafka by playlist_id so downstream consumers observe the SQL commit order.
A popular playlist creates a hot read key, handled through Redis snapshots, read replicas, and CDN-safe public metadata.
A highly active collaborative playlist creates a hot write partition. Queue its commands at one owner, batch fanout, and enforce per-playlist limits.
Large playlists use cursor pagination over (position_key, entry_id); clients subscribe from the returned playlist version.

Failure and recovery

Failure	System response
Lost mutation acknowledgement	The retry reuses `operation_id` and returns the committed result.
Owner process stops	Routing assigns a replacement, which loads the current SQL version and resumes serialization.
Kafka unavailable	The SQL transaction commits with its outbox row; publication resumes later.
WebSocket disconnects	The client reconnects from its last version and receives replay or a fresh snapshot.
Redis unavailable	Reads fall through to SQL under circuit breaking and request coalescing.
Read replica lags	Collaborative reads use the primary or require a replica version at least as new as the client cursor.

Requirements that change the design

New requirement	Architecture change
Sustained offline editing and device-held authority	Adopt the P2P/local-first architecture and a move-aware list CRDT.
Millions of passive followers	Project versioned public snapshots to object storage and CDN distribution.
Strict synchronous moderation	Add policy checks inside the command path before the SQL commit.
Cross-region writes to one playlist	Choose one home-region leader or adopt a multi-leader CRDT protocol with explicit conflict semantics.

Architecture recall

Which rows commit in one transaction?
Why is Kafka downstream of a SQL outbox?
Which component owns client connections?
How does a reconnecting client repair a version gap?
What creates a hot write partition?
Which requirement selects the P2P branch?