# ShareDB (OT) Collaboration

ShareDB is an OT-based real-time database that enables collaborative editing with automatic conflict resolution. The `@rowsncolumns/sharedb` package provides a React hook for integrating ShareDB with your spreadsheet.

## Installation

{% tabs %}
{% tab title="yarn" %}

```sh
yarn add "@rowsncolumns/sharedb sharedb reconnecting-websocket"
```

{% endtab %}

{% tab title="npm" %}

```sh
npm install "@rowsncolumns/sharedb sharedb reconnecting-websocket"
```

{% endtab %}
{% endtabs %}

## Quick Start

```tsx
import { useShareDBSpreadsheet } from "@rowsncolumns/sharedb";
import ShareDBClient from "sharedb/lib/client";
import ReconnectingWebSocket from "reconnecting-websocket";

// Create ShareDB connection
const socket = new ReconnectingWebSocket("ws://localhost:8080");
const connection = new ShareDBClient.Connection(socket);

function SpreadsheetEditor() {
  const [sheetData, setSheetData] = useState({});
  const [sheets, setSheets] = useState([]);
  const [tables, setTables] = useState([]);
  const [sheetId, setSheetId] = useState(1);
  const [activeCell, setActiveCell] = useState({ rowIndex: 1, columnIndex: 1 });

  const { onBroadcastPatch, users, synced, isLeader } = useShareDBSpreadsheet({
    connection,
    collection: "spreadsheets",
    documentId: "my-spreadsheet",
    userId: "user-123",
    title: "John Doe",
    sheetId,
    activeCell,
    initialSheets: [],
    onChangeSheetData: setSheetData,
    onChangeSheets: setSheets,
    onChangeTables: setTables,
    onChangeActiveSheet: setSheetId,
    calculateNow,
    enqueueGraphOperation: (op) => {
      // Handle dependency graph updates for formula recalculation
    },
  });

  // Pass onBroadcastPatch to useSpreadsheetState
  return <Spreadsheet sheetData={sheetData} sheets={sheets} users={users} />;
}
```

## Integration with useSpreadsheetState

Connect the ShareDB adapter to your spreadsheet state:

```tsx
import { useSpreadsheetState } from "@rowsncolumns/spreadsheet-state";
import { useShareDBSpreadsheet } from "@rowsncolumns/sharedb";

function CollaborativeSpreadsheet() {
  const {
    sheetData,
    sheets,
    tables,
    charts,
    embeds,
    namedRanges,
    protectedRanges,
    conditionalFormats,
    dataValidations,
    pivotTables,
    cellXfs,
    sharedStrings,
    setSheetData,
    setSheets,
    setTables,
    setCharts,
    setEmbeds,
    setNamedRanges,
    setProtectedRanges,
    setConditionalFormats,
    setDataValidations,
    setPivotTables,
    setCellXfs,
    setSharedStrings,
    enqueueGraphOperation,
    sheetId,
    activeCell,
    calculateNow,
  } = useSpreadsheetState({
    // Your spreadsheet state config
  });

  const { onBroadcastPatch, users, synced } = useShareDBSpreadsheet({
    connection,
    collection: "spreadsheets",
    documentId: "doc-id",
    userId: currentUser.id,
    title: currentUser.name,
    sheetId,
    activeCell,
    initialSheets: [],
    calculateNow,
    onChangeSheetData: setSheetData,
    onChangeSheets: setSheets,
    onChangeTables: setTables,
    onChangeCharts: setCharts,
    onChangeEmbeds: setEmbeds,
    onChangeNamedRanges: setNamedRanges,
    onChangeProtectedRanges: setProtectedRanges,
    onChangeConditionalFormats: setConditionalFormats,
    onChangeDataValidations: setDataValidations,
    onChangePivotTables: setPivotTables,
    onChangeCellXfs: setCellXfs,
    onChangeSharedStrings: setSharedStrings,
    enqueueGraphOperation,
  });

  // Pass onBroadcastPatch to your spreadsheet
  // ...
}
```

## Setting Up a ShareDB Server

### Basic Server

Create a file `server.js`:

```javascript
const http = require("http");
const express = require("express");
const ShareDB = require("sharedb");
const WebSocket = require("ws");
const WebSocketJSONStream = require("@teamwork/websocket-json-stream");

// Initialize ShareDB
const backend = new ShareDB();

// Create Express app and HTTP server
const app = express();
const server = http.createServer(app);

// Create WebSocket server
const wss = new WebSocket.Server({ server });

// Handle WebSocket connections
wss.on("connection", (ws) => {
  const stream = new WebSocketJSONStream(ws);
  backend.listen(stream);
});

// Start server
const PORT = process.env.PORT || 8080;
server.listen(PORT, () => {
  console.log(`ShareDB server listening on port ${PORT}`);
});
```

Install dependencies:

```bash
npm install express sharedb ws @teamwork/websocket-json-stream
```

Run the server:

```bash
node server.js
```

### Server with MongoDB Persistence

For production environments, persist data to MongoDB:

```bash
npm install sharedb-mongo mongodb
```

```javascript
const ShareDBMongo = require("sharedb-mongo");

// Connect to MongoDB
const db = new ShareDBMongo("mongodb://localhost:27017/spreadsheets");

// Initialize ShareDB with MongoDB
const backend = new ShareDB({ db });
```

### Server with PostgreSQL Persistence

```bash
npm install sharedb-postgres pg
```

```javascript
const ShareDBPostgres = require("sharedb-postgres");

const db = new ShareDBPostgres({
  connectionString: "postgresql://user:password@localhost:5432/spreadsheets",
});

const backend = new ShareDB({ db });
```

## Document Structure

The ShareDB document stores all spreadsheet data:

```typescript
type ShareDBSpreadsheetDoc = {
  // Cell data: flat map with keys like "1!A1" -> { value, sId, r, c }
  sheetData: Record<string, CellDataV3>;

  // Sheet definitions
  sheets: Sheet[];

  // Tables, charts, embeds
  tables: TableView[];
  charts: EmbeddedChart[];
  embeds: EmbeddedObject[];

  // Named ranges, protected ranges
  namedRanges: NamedRange[];
  protectedRanges: ProtectedRange[];

  // Conditional formats, data validations
  conditionalFormats: ConditionalFormatRule[];
  dataValidations: DataValidationRuleRecord[];

  // Pivot tables
  pivotTables: PivotTable[];

  // Cell formats and shared strings
  cellXfs: Record<string, CellFormat>;
  sharedStrings: Record<string, string>;
};
```

### Cell Key Format

Cell keys follow the pattern `${sheetId}!${A1Address}`:

| Key         | Description           |
| ----------- | --------------------- |
| `"1!A1"`    | Cell A1 on sheet 1    |
| `"2!B5"`    | Cell B5 on sheet 2    |
| `"1!AA100"` | Cell AA100 on sheet 1 |

### CellDataV3 Structure

Each cell value is stored with its position metadata:

```typescript
type CellDataV3<T> = {
  value: T; // The cell data (text, formula, number, etc.)
  sId: number; // Sheet ID
  r: number; // Row index (0-based)
  c: number; // Column index (0-based)
};
```

## Presence Awareness

The hook automatically manages presence, allowing you to display other users' cursor positions:

```tsx
const { users } = useShareDBSpreadsheet({ ... });

// Render collaborators in your grid
<CanvasGrid
  users={users}
  userId={currentUserId}
/>
```

Each user in the `users` array includes:

```typescript
type Collaborator = {
  userId: string;
  title: string;
  sheetId: number;
  activeCell: { rowIndex: number; columnIndex: number };
};
```

## Leader Election

The adapter automatically elects a leader among connected clients. The leader is responsible for coordinating recalculation operations.

```tsx
const { isLeader } = useShareDBSpreadsheet({ ... });

// Leader-specific logic
if (isLeader) {
  // This client is responsible for coordinating recalcs
}
```

## Error Handling

Handle connection errors with the `onError` callback:

```tsx
useShareDBSpreadsheet({
  // ...
  onError: (err) => {
    console.error("ShareDB error:", err);
    toast.error("Connection lost. Reconnecting...");
  },
});
```

## Why ShareDB for Spreadsheets?

ShareDB's [Operational Transformation (OT)](https://en.wikipedia.org/wiki/Operational_transformation) approach is particularly well-suited for spreadsheet collaboration compared to CRDT-based solutions like Yjs.

### Comparison with Y.js

| Aspect              | ShareDB (OT)                            | Yjs (CRDT)                                   |
| ------------------- | --------------------------------------- | -------------------------------------------- |
| Conflict Resolution | OT - Server determines canonical order  | CRDT - Automatic merge, eventual consistency |
| Server Required     | Yes                                     | Optional (P2P possible)                      |
| Recalculation       | Leader election makes coordination easy | Requires additional coordination layer       |
| Undo/Redo           | Manual (via Immer patches)              | Built-in                                     |
| Offline Support     | Requires server connection              | Full offline-first support                   |
| Audit Trails        | Sequential operation log                | Distributed history                          |
| Data Persistence    | MongoDB, PostgreSQL, etc.               | LevelDB, IndexedDB, etc.                     |

### Why OT Works Well for Spreadsheets

1. **Cell-level granularity** - The V3 flat map structure (`"sheetId!A1"` keys) maps perfectly to ShareDB's json0 OT type. Each cell is an independent key-value pair, so conflicts are rare.
2. **Server-centric calculation** - Spreadsheets typically need a server anyway for formula calculation, persistence, and permissions. OT's server authority aligns naturally with this architecture.
3. **Simpler undo/redo** - OT's sequential operation log makes history management straightforward. The server maintains a clear order of operations.
4. **Predictable conflict resolution** - When two users edit the same cell simultaneously (rare in practice), the server determines the winner. No surprising merged states.
5. **Batched operations** - Large operations (paste 1000 cells, delete rows) can be batched into a single atomic `submitOp` call, maintaining consistency.

### When to Choose ShareDB

* Need a traditional client-server architecture
* Want to leverage existing MongoDB/PostgreSQL infrastructure
* Prefer predictable OT semantics for conflict resolution
* Server-side formula calculation is required
* Need clean audit trails and operation history

### When to Choose Y.js

* Need offline-first capabilities
* Want peer-to-peer collaboration without a server
* Need built-in undo/redo support
* Decentralized architecture is preferred

For most spreadsheet use cases with a server backend, ShareDB provides a cleaner, more predictable collaboration model.