Reference
Durable Data Layout
Look up syntax, contracts, layouts, algorithms, and exact behavior.
The database layout is small but deliberately complete. Users and sessions support authentication. Canvases own JSON state and revision. Members represent sharing. Folders organize only the owner's own canvases. Operations form an append-only record of durable mutations, and history rows point to forward/inverse operations for undo and redo.
There are two different storage shapes in play. Relational tables model identity, ownership, membership, folders, and append-only operation metadata because those are relationships the database is good at enforcing. The canvas drawing itself is stored as JSON because the shape schema is polymorphic and evolves with editor features. The operation validator is the boundary that keeps the flexible JSON state from becoming arbitrary untrusted data.
users id, username, email, password_hash sessions token_hash, user_id, expires_at canvases id, owner_id, name, folder_id, sort_order, state, revision canvas_members canvas_id, user_id canvas_folders id, owner_id, parent_id, name, sort_order canvas_ops id, canvas_id, revision, op canvas_history id, canvas_id, user_id, forward_op, inverse_op, undone_at
The state column is JSON because shape variants evolve quickly and because operations already validate the patch surface. Relational rows are used where relationships matter: users, sessions, members, folders, operation metadata, and history entries.
The revision column is the bridge between those two worlds. It is stored with the canvas row, advanced under the same lock that writes JSON state, and echoed to clients in every durable WebSocket message. That single number lets clients detect missed operations, lets the UI display saved progress, and gives distributed fanout a convergence check without requiring sticky sessions or durable Redis streams.
PostgreSQL users, sessions, canvases, folders, members canvas state snapshots revision numbers operation log undo/redo history Redis Pub/Sub fanout presence TTLs rate-limit counters access/deletion invalidation Browser viewport selection toolbar draft values local color and drag previews