Supabase (OT) Collaboration

Add real-time collaboration to Spreadsheet using Supabase with PostgreSQL persistence

The @rowsncolumns/supabase package provides real-time collaboration with PostgreSQL persistence and Operational Transformation (OT) for conflict resolution.

Features

• PostgreSQL Persistence: All spreadsheet data stored in Supabase

• Real-time Collaboration: Live updates via Supabase Realtime

• Operational Transformation: Conflict resolution for concurrent edits

• Presence Tracking: See active collaborators and their cursors

• Leader Election: Coordinated recalculation

Installation

yarn

yarn add @rowsncolumns/supabase @supabase/supabase-js

npm

Setup

1. Run Database Migration

Run the SQL migration in your Supabase project's SQL Editor:

-- See libs/supabase/migrations/001_create_spreadsheet_tables.sql
-- Or run via CLI:
supabase db push

2. Deploy Edge Function

# Copy the edge function to your supabase project
cp -r node_modules/@rowsncolumns/supabase/edge-functions/spreadsheet-ot supabase/functions/

# Deploy
supabase functions deploy spreadsheet-ot

Usage

Initializing Supabase Client

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!,
  {
    realtime: {
      params: {
        eventsPerSecond: 20,
      },
    },
  },
);

Using the Hook

import {
  SheetData,
  useSpreadsheetState,
} from "@rowsncolumns/spreadsheet-state";
import { useSupabase } from "@rowsncolumns/supabase";
import {
  SpreadsheetProvider,
  CanvasGrid,
  Sheet,
  CellData,
} from "@rowsncolumns/spreadsheet";

const initialSheets: Sheet[] = [
  { sheetId: 1, title: "Sheet 1", index: 0, rowCount: 1000, columnCount: 26 },
];

const MySpreadsheet = () => {
  const userId = "user-123";
  const documentId = "my-spreadsheet-id"; // Unique ID for this document

  const [sheets, onChangeSheets] = useState<Sheet[]>(initialSheets);
  const [sheetData, onChangeSheetData] = useState<SheetData<CellData>>({});
  const [tables, onChangeTables] = useState<TableView[]>([]);
  // ... other state

  const {
    activeCell,
    activeSheetId,
    enqueueGraphOperation,
    calculateNow,
    // ... other hooks
  } = useSpreadsheetState({
    sheets,
    sheetData,
    onChangeSheetData,
    onChangeSheets,
    onChangeTables,
    // ... other props
    onChangeHistory(patches) {
      // Send patches to Supabase for persistence and broadcast
      onBroadcastPatch(patches);
    },
  });

  // Supabase collaboration hook with persistence
  const { onBroadcastPatch, users, synced, isLeader, connectionState } =
    useSupabase({
      supabase,
      documentId,
      userId,
      title: `User ${userId}`,
      sheetId: activeSheetId,
      activeCell,
      initialSheets,

      // State setters - hook will populate these on initial load
      onChangeSheetData,
      onChangeSheets,
      onChangeTables,
      onChangeCharts,
      onChangeEmbeds,
      onChangeNamedRanges,
      onChangeProtectedRanges,
      onChangeConditionalFormats,
      onChangeDataValidations,
      onChangePivotTables,
      onChangeCellXfs,
      onChangeSharedStrings,

      // Calculation
      enqueueGraphOperation,
      calculateNow,

      onError: (err) => console.error("Supabase error:", err),
    });

  return (
    <div>
      {/* Connection status */}
      <div>
        Status: {connectionState}
        {synced ? " (synced)" : " (syncing...)"}| Role:{" "}
        {isLeader ? "Leader" : "Follower"}| Users: {users.length + 1}
      </div>

      <CanvasGrid
        // ... other props
        users={users}
        userId={userId}
      />
    </div>
  );
};

const App = () => (
  <SpreadsheetProvider>
    <MySpreadsheet />
  </SpreadsheetProvider>
);

API Reference

Props

Prop	Type	Required	Description
supabase	SupabaseClient	Yes	Supabase client instance
documentId	string	Yes	Unique document identifier
userId	string | number	Yes	Current user ID
title	string	Yes	User display name
sheetId	number	Yes	Active sheet ID
activeCell	CellInterface	No	Current active cell
initialSheets	Sheet[]	Yes	Initial sheets if document is new
edgeFunctionUrl	string	No	Edge function URL (default: /functions/v1/spreadsheet-ot)
onChangeSheetData	Dispatch	Yes	Sheet data setter
onChangeSheets	Dispatch	Yes	Sheets setter
onChangeTables	Dispatch	Yes	Tables setter
enqueueGraphOperation	Function	Yes	Calculation enqueue function
calculateNow	Function	Yes	Calculate now function

Return Value

Property	Type	Description
onBroadcastPatch	Function	Broadcast patches to other clients
users	Collaborator[]	Active collaborators
synced	boolean	Initial sync complete
isLeader	boolean	Is leader for recalc coordination
connectionState	ConnectionState	"connected", "connecting", or "disconnected"

Ephemeral-Only Mode

If you only need ephemeral collaboration without persistence, use @rowsncolumns/supabase-spreadsheet instead:

import { useSupabaseSpreadsheet } from "@rowsncolumns/supabase-spreadsheet";

const { users, onBroadcastPatch } = useSupabaseSpreadsheet({
  supabase,
  userId,
  userName: `User ${userId}`,
  activeCell,
  sheetId: activeSheetId,
  onChangeSheetData,
  onChangeSheets,
  onChangeTables,
});

Note: This does not persist data - it only synchronizes state between connected clients.