Building a Local-First Collaborative Spreadsheet with Yjs, SQLite, and WebSockets

Traditional cloud-native spreadsheet applications (like Google Sheets) operate on a Server-Authoritative Model. When you edit a cell, your input goes to a central server which validates it, updates the master database, and sends the updated state back to other users.

If you lose internet access, standard cloud apps freeze completely.

Local-First architectures flip this paradigm. The local device holds the master copy of the database (running in-browser SQLite or IndexedDB). Edits occur instantly on local storage at 0ms latency with zero network dependency. When internet is restored, conflict-free sync occurs in the background using Conflict-Free Replicated Data Types (CRDTs) over WebSockets.

In this guide, we'll design and build a fully offline-capable, real-time Collaborative Spreadsheet using Yjs (the leading JS CRDT framework) and in-browser SQLite.

⚡ 1. The Local-First CRDT Data Pipeline

In a local-first collaborative spreadsheet:

1. 2.
   Local Storage (SQLite): Persists raw tabular cell values and formulas locally. It is the single source of truth for the local UI.
2. 4.
   Yjs (CRDT Layer): Maintains an in-memory replicated map representation of the spreadsheet cells. Yjs manages the mathematical merge logic to resolve editing conflicts automatically.
3. 6.
   WebSocket Provider: Streams lightweight binary updates (diffs) between users when connected.

   [User Edits Cell] ──> [SQLite (Instant Local Save)]
                                │
                        (Sync to Local CRDT)
                                ▼
                       [Yjs Document State] 
                                │
                    (Stream Binary Update Delta)
                                ▼
                   [WebSocket Sync / Broadcaster]

🏗️ 2. Designing the Replicated Spreadsheet State with Yjs

Yjs represents data as specialized shared types. For a spreadsheet, we represent our cells as a shared Y.Map, where each key is a cell coordinate (e.g. "A1", "B4") and the value is a JSON object containing the raw value, formula, and style.

javascript
import * as Y from 'yjs';
import { WebsocketProvider } from 'y-websocket';

class CollaborativeSpreadsheet {
  constructor(roomId) {
    // 1. Create a raw Yjs Document
    this.ydoc = new Y.Doc();

    // 2. Initialize a shared map for the spreadsheet cells
    this.sharedCells = this.ydoc.getMap('cells');

    // 3. Connect to the WebSocket sync network
    this.provider = new WebsocketProvider(
      'wss://api.sachinsharma.dev/yjs-sync', 
      roomId, 
      this.ydoc
    );

    this.setupListeners();
  }

  setupListeners() {
    // 4. Capture incoming remote changes from other users
    this.sharedCells.observe((event) => {
      event.changes.keys.forEach((change, key) => {
        if (change.action === 'add' || change.action === 'update') {
          const updatedCell = this.sharedCells.get(key);
          console.log(`📡 Remote cell update detected on [${key}]:`, updatedCell);
          
          // Trigger local DOM update and SQLite persistence
          this.updateLocalCellUI(key, updatedCell);
          this.saveCellToSQLite(key, updatedCell.value, updatedCell.formula);
        }
      });
    });
  }

  // 5. Update cell state locally and trigger automatic network broadcast
  updateCell(cellId, value, formula = "") {
    const cellData = { value, formula, updatedBy: 'Sachin' };
    
    // Yjs automatically captures this update, merges conflicts, 
    // and streams binary diffs down the WebSocket provider!
    this.sharedCells.set(cellId, cellData);
    
    // Save to browser SQLite instantly (0ms latency!)
    this.saveCellToSQLite(cellId, value, formula);
  }
}

💻 3. Client-Side SQLite Persistence

To ensure data survives page refreshes and functions completely offline, we persist our spreadsheet state inside an in-browser SQLite database running via WebAssembly (@vlcn.io/crsqlite or standard sql.js).

javascript
import initSqlJs from 'sql.js';

let db;

async function initLocalSQLite() {
  const SQL = await initSqlJs({ locateFile: file => `https://sql.js.org/dist/${file}` });
  
  // Allocate database in browser local IndexedDB virtual storage
  db = new SQL.Database();
  
  // Create spreadsheet table
  db.run(`
    CREATE TABLE IF NOT EXISTS spreadsheet_cells (
      cell_id TEXT PRIMARY KEY,
      cell_value TEXT,
      cell_formula TEXT,
      last_updated INTEGER
    );
  `);
  console.log("💾 Browser SQLite Database successfully initialized!");
}

async function saveCellToSQLite(cellId, value, formula) {
  const stmt = db.prepare(`
    INSERT OR REPLACE INTO spreadsheet_cells (cell_id, cell_value, cell_formula, last_updated)
    VALUES (?, ?, ?, ?);
  `);
  stmt.run([cellId, value, formula, Date.now()]);
  stmt.free();
}

🚀 4. Resolving Conflicts: Why CRDTs outperform OT

In traditional collaborative systems (like Operational Transformation - OT used in Google Docs), client edits must go to a central server that decides the exact chronological order of events and resolves overlapping edits.

CRDTs (Conflict-Free Replicated Data Types) use mathematical logical clocks (State-based / Op-based merging) that allow clients to merge updates locally in any order.

If two users edit the exact same cell simultaneously:

• User A sets A1 to "42" at logical tick 4.
• User B sets A1 to "99" at logical tick 4.
• Yjs resolves this deterministically across all nodes using unique client IDs as ties (e.g. client ID with the higher number wins).
• Both users automatically converge to the exact same final spreadsheet state without any server-side mediation!

🏁 5. Conclusion

Building local-first applications represents a massive leap forward for user experience. Shifting master data storage straight to client-side WebAssembly SQLite databases delivers an instantaneous, zero-latency experience that remains fully functional in offline mode, while Yjs CRDT merges guarantee conflict-free real-time sync when network access is restored.