Database

Choosing SQLite or PostgreSQL, what else derives from DATABASE_URL, migrating between them, cluster mode, and the dialect traps that reach operators.

One variable selects the backend. DATABASE_URL starting with postgres:// or postgresql:// gets the PostgreSQL adapter. Anything else is treated as a SQLite file path. There is no separate driver setting.

DATABASE_URL=/data/tokens.db                                  # SQLite
DATABASE_URL=postgres://user:pw@db.internal:5432/workbench    # PostgreSQL

The default is ./data/tokens.db. Both backends implement the same adapter contract and run the same logical schema, so a deployment can move between them.

What else derives from DATABASE_URL#

When it is a file path, two other directories default to siblings of the database file's own directory:

That matters for two reasons. Chromium profiles grow — they filled a shared volume to 88% in production once — so they share a disk budget with the database unless you point BROWSER_PROFILES_DIR elsewhere. And when you switch DATABASE_URL to a PostgreSQL URL, its dirname is no longer a meaningful filesystem path: set BROWSER_PROFILES_DIR and JOTS_DIR explicitly.

Relocating the directories is only half the answer — these are the knobs that bound what they hold:

Full validation details are in environment variables.

Schema#

erDiagram
  users ||--o{ connections : owns
  users ||--o{ pending_auth : starts
  users ||--o{ audit_log : generates
  users ||--o{ oauth_auth_codes : authorizes
  users ||--o{ oauth_refresh_tokens : holds
  oauth_clients ||--o{ oauth_auth_codes : issued_for
  oauth_clients ||--o{ oauth_refresh_tokens : issued_for

  users {
    TEXT id PK
    TEXT email UK
    TEXT google_sub UK
    TEXT keycloak_sub UK
    TEXT api_key_hash
    TEXT api_key_sha
    BLOB api_key_enc
    BOOLEAN is_admin
    INTEGER created_at
  }
  connections {
    INTEGER id PK
    TEXT user_id FK
    TEXT integration
    BLOB access_token
    BLOB refresh_token
    INTEGER expires_at
    TEXT scopes
    BLOB cookies
    TEXT config
  }
  pending_auth {
    TEXT state PK
    TEXT user_id FK
    TEXT integration
    TEXT code_verifier
    TEXT session_data
    TEXT config
    INTEGER expires_at
  }
  audit_log {
    INTEGER id PK
    TEXT user_id FK
    TEXT integration
    TEXT tool
    TEXT action
    BOOLEAN success
    TEXT error
    INTEGER duration_ms
    INTEGER created_at
  }
  oauth_clients {
    TEXT client_id PK
    TEXT client_name
    TEXT redirect_uris
  }
  oauth_auth_codes {
    TEXT code PK
    TEXT client_id FK
    TEXT user_id FK
    TEXT code_challenge
    TEXT redirect_uri
    INTEGER expires_at
  }
  oauth_refresh_tokens {
    TEXT token_hash PK
    TEXT client_id FK
    TEXT user_id FK
    TEXT scope
    INTEGER expires_at
  }

Both google_sub and keycloak_sub are unique, but keycloak_sub gets its uniqueness from a partial index — WHERE keycloak_sub IS NOT NULL — so any number of users can have it unset while no two can share a value.

connections is unique on (user_id, integration) — one credential per user per integration. pending_auth carries three different flows discriminated by its integration column: a plugin name for plugin OAuth, google-sso / keycloak-sso for portal login, and __oauth_authorize__ for an MCP authorize ticket.

Column-level detail is in database schema.

Schema initialization#

There is no versioned migration table. applySchema() dispatches on the adapter's dialect and is idempotent: a CREATE TABLE IF NOT EXISTS block followed by additive column adds. On SQLite those are bare ALTER TABLE … ADD COLUMN statements in a try/catch that swallows only "duplicate column name" and rethrows everything else. On PostgreSQL they are one ADD COLUMN IF NOT EXISTS batch.

It runs from an explicit initDb() call at boot, before plugins load and before routes register — not as an import side effect. There is nothing to run by hand. Starting the server against an empty database creates the schema.

Migrating SQLite to PostgreSQL#

The migration script lives in packages/server/src/migrate/ and is a dry run unless you pass --apply.

export SOURCE_SQLITE_PATH=/data/tokens.db
export TARGET_DATABASE_URL=postgres://user:pw@db.internal:5432/workbench

npm run migrate:sqlite-to-postgres -w @workbench/server

tsx server/migrate/sqlite-to-postgres.js

npm run migrate:sqlite-to-postgres -w @workbench/server -- --apply --skip=pending_auth,oauth_auth_codes

Cluster mode#

CLUSTER_ENABLED=true forks one worker per availableParallelism(). Accepted values are exactly true, false, 1, 0 — anything else is a validation error at boot, not a falsy value.

Cluster mode requires PostgreSQL. With a SQLite DATABASE_URL the process prints an explicit message and exits 1. SQLite cannot be safely shared across processes.

Connection budgeting is the thing to get right:

total connections = PG_POOL_MAX × worker count

PG_POOL_MAX defaults to 2 and is per worker. On a 16-core host that is 32 connections from one deployment. Keep the total well under the server's max_connections. PG_CONNECT_TIMEOUT_MS (default 5000, 0 = unlimited) caps how long a request waits for a free pool slot before being rejected.

Two caveats:

• The primary process reforks a dead worker with no backoff, so a worker crash-looping on bad configuration will spin.
• Graceful shutdown — SIGTERM/SIGINT → drain the pool → exit — is registered per worker, not on the primary. The primary dies on the default signal disposition.

Some state is still per-process and not shared across workers:

• The transient connect-flow records that connect / wait_for_connection poll — use sticky routing for those.
• Browser sessions. The per-user Chromium and its live-view channel live in module-level maps beside the process that spawned them, and the browser itself listens on loopback inside that container. Cookie capture, every browser_* tool, and the live view only work on the owning process. Across replicas the X-Browser-Session key makes that routable (see the proxy setup), but stickiness cannot route inside a worker pool — so keep CLUSTER_ENABLED off wherever the browser feature is used. Otherwise a second worker answering for the same user spawns its own Chromium on the same profile directory and clears the singleton lock the first one is holding. See browser session pod affinity.

SSO nonces are stored in the pending_auth row beside their state, so they follow the database and need no stickiness. Jot upload tokens are rows in that same table (under a __jot_upload__ sentinel integration), so they need none either, and their single-use guarantee holds across every worker and replica — see the jots guide.

PostgreSQL behaviours worth knowing#

These are handled inside the adapter, but they explain what you'll see and why a custom query written against the wrong assumption breaks.

The audit log's sqlite destination writes through the shared adapter, so despite the name it inserts into whichever backend is configured, PostgreSQL included.