System Design: A Real-Time Collaborative Task Board

A task board looks simple until two people drag the same card at once, one of them on a flaky connection, while your app runs on four servers. This post walks through a real-time collaborative app system design for a Trello-style board: data model, card ordering, transport, fan-out, conflicts, presence and recovery.

Real-time collaborative app system design: requirements

Functional

• Boards hold ordered columns of ordered cards (title, description, assignee).
• Users create, edit and move cards; every viewer sees changes without refreshing.
• Presence shows who is viewing the board.
• Per-board roles: owner, editor, viewer.

Non-functional

• Changes reach other viewers within a few hundred milliseconds (a target, not a measurement).
• Acknowledged edits are never lost, and all clients converge on the same order.
• Dropped connections and restarts recover without a page reload.

Scale assumptions

Sizing assumptions, not real product data: 10,000 concurrent connections across several instances, usually under 10 viewers per board, a few thousand cards per board, and human-paced writes. So per-board writes can be serialized cheaply; the hard problems are fan-out and recovery.

Data model: boards, columns and cards

PostgreSQL is the source of truth. A per-board sequence number and an append-only event log carry most of the real-time design.

CREATE TABLE boards (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name      text NOT NULL,
  last_seq  bigint NOT NULL DEFAULT 0
);

CREATE TABLE board_members (
  board_id  uuid REFERENCES boards (id),
  user_id   uuid,
  role      text NOT NULL CHECK (role IN ('owner', 'editor', 'viewer')),
  PRIMARY KEY (board_id, user_id)
);

CREATE TABLE board_columns (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  board_id  uuid NOT NULL REFERENCES boards (id),
  name      text NOT NULL,
  sort_key  text COLLATE "C" NOT NULL,
  UNIQUE (board_id, sort_key) DEFERRABLE
);

CREATE TABLE cards (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  board_id        uuid NOT NULL REFERENCES boards (id),
  column_id       uuid NOT NULL REFERENCES board_columns (id),
  sort_key        text COLLATE "C" NOT NULL,
  title           text NOT NULL,
  description     text NOT NULL DEFAULT '',
  assignee_id     uuid,
  version         integer NOT NULL DEFAULT 1,
  field_versions  jsonb NOT NULL DEFAULT '{}',
  UNIQUE (column_id, sort_key) DEFERRABLE
);

CREATE TABLE board_events (
  board_id  uuid REFERENCES boards (id),
  seq       bigint,
  op_id     uuid UNIQUE,
  event     jsonb NOT NULL,
  PRIMARY KEY (board_id, seq)
);

• COLLATE "C" sorts by byte values, matching JavaScript's < on ASCII strings. A natural-language collation can order "a" and "B" differently from your client.
• DEFERRABLE checks uniqueness per statement, so a rebalance can rewrite a column's keys in one UPDATE.
• op_id is the client's idempotency key (NULL for system events).
• (board_id, seq) makes "events after N" an index range scan; see my PostgreSQL indexing guide.

Ordering cards with fractional indexing

Integer positions force renumbering every card below an insert. Fractional indexing gives each card a key that sorts between its neighbors, so a move updates one row.

Floats look tempting, but a double has a 52-bit mantissa (Number.EPSILON is 2^-52). Repeatedly halving the gap between 1 and 2 yields only 52 midpoints before the average equals a neighbor. String keys have no such ceiling. This function treats them as base-62 fractions between 0 and 1:

// Base-62 digits in ASCII order, so string order is numeric order.
const DIGITS =
  "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
const BASE = DIGITS.length;
const VALID_KEY = /^[0-9A-Za-z]*[1-9A-Za-z]$/; // no trailing "0"

const digitAt = (s: string, i: number) =>
  i < s.length ? DIGITS.indexOf(s[i]) : 0;

// lo = card above, hi = card below, null = column edge.
function keyBetween(lo: string | null, hi: string | null): string {
  for (const k of [lo, hi]) {
    if (k !== null && !VALID_KEY.test(k)) throw new Error(`bad key: ${k}`);
  }
  const a = lo ?? "";
  if (hi !== null && a >= hi) throw new Error(`bad range: ${a} >= ${hi}`);

  let prefix = "";
  let bounded = hi !== null; // still sharing hi's prefix?

  for (let i = 0; ; i++) {
    const da = digitAt(a, i);
    const db = bounded ? digitAt(hi!, i) : BASE;

    if (db - da > 1) {
      const d =
        hi === null ? da + 1 // append
        : lo === null ? db - 1 // prepend
        : Math.floor((da + db) / 2); // midpoint
      return prefix + DIGITS[d];
    }
    if (db - da === 1) {
      // Adjacent digits: use hi's prefix, or go deeper.
      if (bounded && hi!.length > i + 1) return prefix + DIGITS[db];
      bounded = false;
    }
    prefix += DIGITS[da];
  }
}

Clients send intent ("after card A"); the server computes the key, and the browser runs the same function for optimistic rendering.

Precision and rebalancing trade-offs

• Growth: repeated inserts into one gap add a character every five or six inserts. Appends step by one digit, so 1,000 consecutive appends yield a 17-character key.
• Rebalancing: past a length threshold (say 32 characters), rewrite the column's keys evenly in one transaction with one column.rebalanced event.
• Collisions: concurrent drops into one gap could compute the same key. Per-board write serialization prevents it; the unique constraint is the backstop.
• Long columns: a variable-length integer part makes appends grow logarithmically.

Choosing the real-time transport

The board loads over HTTP: GET /boards/:id returns columns, cards and last_seq from one REPEATABLE READ transaction, so they agree.

SSE plus HTTP POSTs is a legitimate, simpler design, especially over HTTP/2. With live presence, my default is WebSockets: pings and commands flow both ways over one connection with one auth context. Polling GET /boards/:id/events?since=N is the degraded mode.

Fan-out across server instances

Each instance subscribes to board:{id} for every board its clients have open. The write path:

1. UPDATE boards SET last_seq = last_seq + 1 WHERE id = $1 RETURNING last_seq. The row lock serializes the board's writers until commit; under READ COMMITTED, later statements see earlier committed moves.
2. Apply the change and insert the event with that seq.
3. Commit, then publish { boardId, seq }.

Redis pub/sub

• At-most-once delivery: a subscriber that errors or disconnects loses the message.
• Slow subscribers get disconnected; the default Pub/Sub output-buffer limit is 32 MB hard, 8 MB per 60 seconds soft.
• In Redis Cluster, classic pub/sub reaches every node; sharded pub/sub (Redis 7.0+) stays within a shard.
• Publishes from different servers can arrive out of commit order.

PostgreSQL LISTEN/NOTIFY

• Notifications arrive only if the transaction commits, in commit order, with no extra infrastructure.
• Payloads must be shorter than 8000 bytes by default: send IDs, not cards.
• Each instance needs a dedicated connection; registrations end with the session and nothing is replayed.

My default: LISTEN/NOTIFY while PostgreSQL is your only stateful dependency, Redis as instances multiply. Either way the bus is a doorbell, not a mailbox: the event log is the record, and seq reveals what the doorbell missed.

A typed message protocol

type CardFields = { title: string; description: string; assigneeId: string | null };
type Card = CardFields & { id: string; columnId: string; sortKey: string; version: number };

type ClientMessage =
  | { type: "subscribe"; boardId: string; sinceSeq: number | null }
  | { type: "card.move"; opId: string; boardId: string; cardId: string;
      columnId: string; afterCardId: string | null }
  | { type: "card.update"; opId: string; boardId: string; cardId: string;
      baseVersion: number; patch: Partial<CardFields> }
  | { type: "presence.ping"; boardId: string };

type BoardEvent =
  | { kind: "card.moved"; cardId: string; columnId: string; sortKey: string; version: number }
  | { kind: "card.updated"; cardId: string; patch: Partial<CardFields>; version: number }
  | { kind: "column.rebalanced"; columnId: string; sortKeys: Record<string, string> };

type ServerMessage =
  | { type: "event"; boardId: string; seq: number; opId: string | null; event: BoardEvent }
  | { type: "reject"; opId: string; reason: "forbidden" | "conflict" | "invalid"; current?: Card }
  | { type: "resync.required"; boardId: string }
  | { type: "presence"; boardId: string; userIds: string[] };

With a never check in the default branch, a new message type breaks the build until every switch handles it. Validate at runtime too; types vanish at the socket boundary. A repeated opId returns the original result instead of applying twice.

Concurrency and conflicts

Optimistic UI

The client applies its command immediately and queues it as pending, rendering confirmed server state with pending commands replayed on top. A matching event clears it; a reject drops it and the UI settles back. That instant feedback also helps responsiveness metrics.

Per-field last-write-wins with versions

card.update sends only changed fields plus the client's baseVersion. Each write bumps version and records it in field_versions for the fields it touched.

• Simple fields (title, assignee): last write wins per field, so a rename and a concurrent reassignment both survive.
• Guarded fields (description): if field_versions.description exceeds baseVersion, the server rejects with the current card and the UI offers a merge.

Events carry version, so clients skip changes they already have.

When you need CRDTs or OT

Last-write-wins fails when people edit inside one field together, like co-writing a rich-text description. That's where CRDTs (Yjs, Automerge) or operational transformation earn their complexity. Scope them to the description.

Presence

Presence is ephemeral, so it stays out of PostgreSQL and the event log. Clients ping every 15 seconds or so; the server keeps a Redis sorted set per board (member userId:connectionId, score = timestamp) and drops entries after about three missed pings. Updates ride the bus, where at-most-once delivery is fine: the next ping corrects any loss.

Reconnects and missed events

Clients track the last applied seq per board and apply events strictly in order.

• Gap (expected 41, got 43): wait briefly in case 42 is late, then request a replay.
• Reconnect: subscribe with sinceSeq. The server subscribes to the live channel first, replays board_events after sinceSeq, then flushes buffered live events, dropping duplicate seqs. The reverse order leaves a gap.
• Too far behind: if sinceSeq predates the retention window, the server sends resync.required and the client reloads the snapshot.

Use exponential backoff with jitter, or a deploy triggers a reconnect stampede.

Authorization per board

• Authenticate the upgrade. The browser WebSocket constructor accepts only a URL and subprotocols, so use the session cookie or a short-lived ticket.
• Check Origin with cookies; RFC 6455 uses it to protect against unauthorized cross-origin use by browser scripts.
• Check membership on subscribe and every command. Viewers can't mutate.
• Scope queries with AND board_id = $1, or members can edit other boards' cards by guessing IDs.
• Revoke live: removing a member publishes a control message that drops their subscriptions.

Scaling and failure modes

• Any instance serves any board because fan-out goes through the bus; sticky sessions aren't needed for correctness.
• Hot boards: per-board serialization caps writes at one per lock hold, about 200 per second at 5 ms each. Far above human pace.
• Bus outage: writes still commit; clients notice the silence and poll the events endpoint.
• Slow clients: the browser WebSocket API has no backpressure, so keep messages small and close sockets whose send buffer keeps growing.

Key takeaways

• PostgreSQL is the source of truth; a per-board seq and event log make any transport recoverable.
• Use string fractional keys with COLLATE "C", and plan for rebalancing.
• Treat pub/sub and LISTEN/NOTIFY as doorbells, not mailboxes.
• Per-field last-write-wins covers most edits; save CRDTs for shared text.
• Authorize every message, not just the connection.