Example: the PostgreSQL adapter

This page walks through a complete adapter implementation, useful both if you want to run PostgreSQL as your store and as a reference when building your own adapter, since it demonstrates every part of the adapter surface in one place.

The adapter stores each spreadsheet as a row in a documents table with the document body in a JSONB column, and persists every spreadsheet operation as an incremental property update, a jsonb_set on the exact paths that changed, rather than rewriting the document. A one-cell edit on a large document updates one JSON leaf; the rest of the row is untouched.

Configuration

Connection options

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

const adapter = require('./postgresql');

// 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 into the server exactly like any adapter. The method surface is identical to the MongoDB adapter, including the extension stores (listChats, setUsage, …). See Adapters.

Schema

Tables created on first use

The adapter creates its tables 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 document body
);
CREATE INDEX documents_user_id ON documents (user_id);

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)
);

Querying the JSONB document body

Because the body is JSONB, it is queryable with standard operators, which is useful 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');

How incremental updates work

The adapter shares its operation-translation layer with the MongoDB adapter. Each spreadsheet method (setValue, insertRow, setWidth, setMerge, …) is translated into path-level update operators; the PostgreSQL executor then applies them to the JSONB column.

The read-translate-write cycle

Inside a transaction, the document row is read with FOR UPDATE.
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).
The recorded paths are written as one UPDATE with chained jsonb_set(...) / #- expressions:

-- A setValue on an existing cell compiles to a single leaf update:
UPDATE documents
SET spreadsheet = jsonb_set(spreadsheet, '{worksheets,0,data,4,2}', '"new value"', true),
    updated = now()
WHERE guid = $1;

Hot-path and structural operations

Hot-path operations (cell edits, styles, comments, properties) touch one leaf. Structural operations (insert/delete/move of rows and columns, sorting) write the affected worksheet arrays plus the derived state (style, meta, comments, cells, mergeCells), 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

Large JSONB values are compressed and stored out-of-line by PostgreSQL; multi-megabyte documents are supported. Note that any update rewrites the TOASTed value, so the benefit of incremental updates on very large documents is in bandwidth and WAL volume for the common small ops, not in avoiding the value rewrite itself.

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, and MongoDB when you prefer its document tooling. There is no capability gap between them.