Server-side durable persistence for canvas state

[FuJacob]

Context

Canvas state currently lives only in client React state and an in-memory op log on the collab server. Page reload or server restart = all work lost. Sim persists to PostgreSQL across normalized tables.

What to build

Database schema (PostgreSQL)

Separate tables for different entity types (following Sim's pattern of targeted updates):

• canvas_nodes — node metadata, positions, dimensions, type-specific data (content, language, etc.)
• canvas_edges — connections between nodes (fromNodeId, toNodeId)
• canvas_crdt_ops — durable CRDT operation log for text content (replaces in-memory docOpLog)

This separation enables:

• Targeted updates: Moving a node only writes x/y columns, not the entire canvas
• Query optimization: Different operations hit different tables
• Independent scaling: Text ops (high volume) isolated from structural ops

Persistence strategy

• Structural ops (node create/move/delete, edge create/delete): persist-first — write to DB before broadcasting to peers (pessimistic)
• Value ops (text edits via CRDT): broadcast-first with async persistence — low-latency for typing, durable within a coalescing window
• Server-side coalescing for value updates: batch writes within a ~25ms window

Snapshot loading

• On client join (join event), server loads full canvas state from DB and sends canvas_snapshot
• Replace current in-memory snapshot with DB-backed snapshot

Migration from in-memory

• Move docOpLog and docSeenOpKeys from CanvasWebSocketHandler to PostgreSQL
• Add connection pooling (HikariCP, already in Spring Boot)

Current code references

• services/collab/src/main/java/com/leetdoodle/collab/handler/CanvasWebSocketHandler.java — in-memory op log (docOpLog, docSeenOpKeys)
• frontend/src/canvas/model/useCanvasDocument.ts — client-side state (source of truth today)

Acceptance criteria

• Canvas state survives page reload
• Canvas state survives server restart
• Structural ops persist before broadcast
• CRDT ops persisted durably (async OK, but guaranteed)
• Joining a canvas loads full state from DB