DocumentationServerPostgresql

Example: the PostgreSQL adapter

A complete adapter implementation, annotated. Use it to run PostgreSQL as your store, or as a reference when building your own adapter: it covers every part of the adapter surface.

The adapter stores each spreadsheet in two places: the configuration (columns, styles, comments, merges, nested headers, tables, everything but the cell values) as a row of the documents table in a JSONB column, and the worksheet data as one row per spreadsheet row in document_rows, keyed by document, worksheet and row index. Every operation is persisted as an incremental update: a jsonb_set on the exact configuration paths that changed, or a write of the data rows it touched. A one-cell edit on a 100,000-row document reads and writes one small row; nothing else is parsed or rewritten.

Configuration

Connection options

The connection comes from the POSTGRES_URL environment variable, falling back to postgres://postgres:postgres@postgresql:5432/postgres (the Docker Compose service name), or is set explicitly by calling the adapter as a function. Passing a new connection resets the pool; the next query uses the new configuration:

const adapter = require('@jspreadsheet/server-pgsql');

// Connection string…
adapter({ connection: 'postgres://user:pass@localhost:5432/jspreadsheet' });

// …or a node-postgres Pool config
adapter({ connection: { host: 'localhost', port: 5432, database: 'jspreadsheet', user: 'user', password: 'pass', max: 10 } });

Wiring the adapter into the server

Wire it like any adapter (Adapters). Its method surface is identical to the MongoDB adapter's, including the extension stores (listChats, setUsage, …).

Schema

Tables created on first use

CREATE TABLE documents (
    guid text PRIMARY KEY,
    user_id text,                 -- owner (JWT subject)
    status integer DEFAULT 1,
    created timestamptz DEFAULT now(),
    updated timestamptz DEFAULT now(),
    users jsonb,                  -- invited users: [{ email, hash, level }]
    prompts jsonb DEFAULT '{}',   -- legacy per-user prompt storage
    spreadsheet jsonb NOT NULL    -- the configuration: every worksheet with data: []
);
CREATE INDEX documents_user_id ON documents (user_id);

CREATE TABLE document_rows (      -- the worksheet data, one row per spreadsheet row
    guid text NOT NULL REFERENCES documents (guid) ON DELETE CASCADE,
    worksheet text NOT NULL,      -- worksheetId
    y integer NOT NULL,           -- row index
    row jsonb,                    -- the row: an array, or an object keyed by column name
    PRIMARY KEY (guid, worksheet, y) DEFERRABLE INITIALLY IMMEDIATE
);

CREATE TABLE chats (              -- agent chat sessions
    id text PRIMARY KEY,
    guid text NOT NULL,
    user_id text,
    title text,
    messages jsonb DEFAULT '[]',
    created timestamptz DEFAULT now(),
    updated timestamptz DEFAULT now()
);
CREATE INDEX chats_guid_user ON chats (guid, user_id);

CREATE TABLE token_usage (        -- accumulated AI token counters
    guid text NOT NULL,
    user_id text NOT NULL DEFAULT '',
    input bigint DEFAULT 0,
    output bigint DEFAULT 0,
    cache_read bigint DEFAULT 0,
    cache_write bigint DEFAULT 0,
    requests bigint DEFAULT 0,
    created timestamptz DEFAULT now(),
    updated timestamptz DEFAULT now(),
    PRIMARY KEY (guid, user_id)
);

Documents saved with the data inline

Documents written by the previous version of this adapter carry the data inside the JSONB body. They keep working: on first read, in one transaction, their rows move to document_rows and the body keeps data: []. No migration script is needed.

Querying the JSONB document body

The JSONB configuration and the relational rows are both queryable with standard operators, for reporting and migrations:

-- Worksheet names of a document
SELECT w->>'worksheetName'
FROM documents, jsonb_array_elements(spreadsheet->'worksheets') AS w
WHERE guid = $1;

-- All public documents of a user
SELECT guid, spreadsheet->>'name'
FROM documents
WHERE user_id = $1 AND NOT (spreadsheet ? 'privacy');

-- The value of column C on the first 10 rows of a worksheet
SELECT y, row->2 FROM document_rows
WHERE guid = $1 AND worksheet = $2 AND y < 10 ORDER BY y;

How incremental updates work

The adapter shares its operation-translation layer with the MongoDB adapter. Each spreadsheet method (setValue, insertRow, setWidth, setMerge, …) becomes path-level update operators; the PostgreSQL executor routes operators under a worksheet data path to the rows table and applies the rest to the JSONB column.

The read-translate-write cycle

  1. Inside a transaction, the configuration row is read with FOR UPDATE, together with the data rows the operation needs: the rows a value change points at, none for a style or a row insert, all of them for a column insert.
  2. The operators are applied to the in-memory copy, recording the minimal set of changed paths with MongoDB-compatible semantics (missing containers are created, arrays are null-padded, $push/$pull mutate the target array).
  3. The configuration paths are written as one UPDATE with chained jsonb_set(...) / #- expressions; the data rows that changed are written in one statement:
-- A setValue on B5 rewrites one row of the rows table in place:
UPDATE document_rows AS r SET row = p->'row'
FROM jsonb_array_elements($3::jsonb) AS p   -- [{ "y": 4, "row": [...] }]
WHERE r.guid = $1 AND r.worksheet = $2 AND r.y = (p->>'y')::int;

-- A setWidth compiles to a single leaf update of the configuration:
UPDATE documents
SET spreadsheet = jsonb_set(spreadsheet, '{worksheets,0,columns,1,width}', '140', true),
    updated = now()
WHERE guid = $1;

Hot-path and structural operations

Hot-path operations (cell edits, styles, comments, properties) touch one row or one leaf. Row operations (insert, delete, move, sort) never rewrite the data: they are index arithmetic on the y column (UPDATE document_rows SET y = y + 1 WHERE y >= 4), a single statement thanks to the deferrable key, followed by a write of the rows whose formulas the engine rewrote for the new positions. Column operations rewrite every row of the worksheet, since every row changes, plus the derived state (style, meta, comments, cells, mergeCells, footers), the same subtree flush the MongoDB adapter performs.

Consistency guarantees

Consistency mirrors the persistence contract:

Per-guid queue

Operations for one document are persisted strictly in apply order; different documents write concurrently on the pool.

Transactional application

The read-translate-write cycle runs in one transaction with the row locked, so a crash cannot half-apply a multi-part update; on error the transaction is rolled back before the resync path runs.

Error resync

On failure the queue is drained and the full live config is written via replace, resynchronizing the row with the in-memory truth.

Sizing and performance notes

TOAST compression is automatic

PostgreSQL compresses large JSONB values and stores them out-of-line, and any update rewrites the TOASTed value. That is why the data lives in its own table: the configuration JSONB stays small regardless of row count, and a cell edit rewrites one row of a few hundred bytes instead of the whole document.

No 16 MB ceiling

JSONB values can reach 1 GB, but the same recommendation applies: keep images out of the body and on S3, because the server also holds the document in memory.

Index for your access patterns

The adapter creates a b-tree on user_id for list. Add expression indexes if you query into the body, e.g. CREATE INDEX ON documents ((spreadsheet->>'name')).

Single writer per document

With guid-based routing one server instance owns each document, so row contention on documents is not a factor in normal operation.

When to choose it over MongoDB

Both incremental adapters are functionally equivalent. Choose PostgreSQL when it is already your operational stack, for backup/replication tooling, SQL reporting over spreadsheet content, or joining documents against your own relational data; choose MongoDB when you prefer its document tooling.