Design a Notion-like workspace (blocks and sync)

Everything is a block: a page is a block of type page whose content array lists child block ids in order, and each child points back with parent_id. This one uniform shape makes nesting, drag and drop, and turning a to-do into a heading (change type, keep properties) trivial. The content array is the source of truth for order, which makes reordering a single operation on one parent row: listRemove(id) then listAfter(id, after=X). Using 'insert after X' instead of 'insert at index 3' matters for concurrency: if two people insert at the same time, positions computed by index shift and collide, while 'after X' still lands in a sensible place. An alternative is fractional indexing (each child has a sortable position key like 'a0', 'a0V' between neighbours), which avoids rewriting a big array but needs occasional rebalancing. Keep the parent_id pointer even though it duplicates information, because permission checks, breadcrumbs and 'move' validation need to walk up the tree. Guard against cycles on move (a page cannot be moved inside its own descendant) by walking up from the new parent before applying.

Each record has a version that increases on every change. Clients apply their own operations optimistically, keep them in a pending queue, and send them as idempotent transactions. The server is the single ordering point per workspace shard: it applies transactions in the order they commit and bumps versions. For most fields (checked, type, a database property, the content array) operation-level last-writer-wins is fine: operations are small and target one property, so two people editing different blocks never conflict, and list operations like listAfter merge naturally. Rich text inside one block is where real conflicts happen: two people typing in the same paragraph. Whole-value last-writer-wins would erase one person's words. The fix is to represent the block's text with a text CRDT (Yjs or Automerge style) or OT, so concurrent inserts both survive; this also enables longer offline editing because CRDT merges do not need a central server to transform operations. When the client receives a newer version from the server for a record it has pending ops on, it rebases: applies the server state and replays its pending ops on top. Presence (cursors, who is viewing) is ephemeral and goes through pub/sub only, never the database.

Shard Postgres by workspace_id: all blocks of a workspace live on one shard, so a transaction that moves a page or edits several blocks is a local ACID transaction, and loading a page never crosses shards. Notion publicly described this approach: 480 logical shards mapped onto a smaller number of physical Postgres hosts, so capacity can be added by moving logical shards rather than re-hashing data. Use a routing table (workspace_id → logical shard → physical host) in the application or a proxy like PgBouncer. The risk is a giant workspace: a 100,000-employee company produces far more blocks and traffic than average and can overload its host. Mitigations: place the largest workspaces on dedicated physical hosts, add read replicas for page loads, cache hot pages in Redis, and rate-limit bulk operations like imports per workspace. Keep cross-workspace features (search across your workspaces, user profile) in separate services so they never need cross-shard joins.

Permissions are stored only where sharing was explicitly set (workspace root, a few shared pages); every other block inherits from the nearest ancestor with explicit permissions. A naive check walks parent_id pointers up to the root on every request, which is many queries for deep pages. Two optimisations: cache each block's 'permission root' (the nearest ancestor with explicit rules) in Redis or a denormalised column, and cache the computed role per (user, permission root). Invalidate when a page moves or its sharing changes; moves are rare compared to edits, so invalidation cost is acceptable. Search is the hard case: you cannot check permissions for 10,000 hits one by one. Index each document with the principals that can read its permission root (users, groups, workspace) and filter in Elasticsearch by the caller's principals, then do a final exact check on the top 20 results to catch stale index entries. Public links get a separate principal ('anyone with link') so they can be revoked instantly.

Blocks have relationships, need multi-row atomic updates (move = edit two parents and the child) and benefit from SQL for database views. Sharded Postgres keeps those transactions local to one shard. A wide-column store would scale writes more easily but push atomicity and querying into application code.

LWW on small operations is simple, cheap and correct for most fields. CRDTs give true concurrent text editing and offline merges but add metadata and memory overhead. A hybrid is common: CRDT or OT for text inside a block, plain versioned operations for structure and properties.

Sending only 'record X is now version N' keeps the WebSocket tier simple and lets the client fetch through the normal permission-checked API. Pushing full operations saves a round trip and is better for fast typing. Many systems push operations for small changes and fall back to notify-then-fetch for large ones.

A local SQLite cache makes page opens instant and survives flaky networks, at the cost of a sync protocol and cache invalidation complexity. Without it, every page open costs a server round trip and many block reads, which is noticeably slower on mobile networks.

An all-hands page viewed by 5,000 people turns every edit into 5,000 pushes. Use per-page channels with WebSocket gateways subscribing once per node (not per user), coalesce updates every ~100 ms, and serve the record fetch from a Redis cache instead of Postgres.

A page with 20,000 blocks or a database with 500,000 rows is slow to load and query. Load pages in chunks, virtualise rendering, and index database property values (a properties table or generated columns with indexes) for filtered views; very large databases can be served by a separate query engine.

One workspace running a bulk import can saturate its shard. Rate-limit bulk writes per workspace, run imports as background jobs with batching, and move big tenants to dedicated hosts.

Edits stay in the local pending queue and the editor keeps working. On reconnect the client retries pending transactions (idempotent by transaction id), fetches newer versions and rebases its pending operations on top.

Saves still work through the HTTP API; users simply stop seeing others' changes live. Clients detect the dropped socket, fall back to polling syncRecordValues every few seconds, and resubscribe when the gateway recovers. No data is lost because realtime carries notifications, not the source of truth.

Fail over to a synchronous or semi-synchronous replica in another zone (tens of seconds). Workspaces on that shard see save errors briefly; their clients queue edits locally and retry. Other shards are unaffected.

If the CDC consumer falls behind, new pages are briefly missing from search but the editor is unaffected. Alert on consumer lag and replay from Kafka offsets; the index can always be rebuilt from Postgres.