Database schema

Every table and column, what writes it, and where the SQLite and PostgreSQL backends differ.

Seven tables. The logical shape is identical on both backends — only the dialect differs.

The backend is chosen from DATABASE_URL alone: a postgres:// or postgresql:// prefix selects PostgreSQL, and anything else is treated as a SQLite file path. There is no separate driver variable.

erDiagram
  users ||--o{ connections : "has credentials for"
  users ||--o{ pending_auth : "starts flows"
  users ||--o{ audit_log : "generates"
  users ||--o{ oauth_auth_codes : "authorizes"
  users ||--o{ oauth_refresh_tokens : "grants"
  oauth_clients ||--o{ oauth_auth_codes : "redeems"
  oauth_clients ||--o{ oauth_refresh_tokens : "holds"

  users {
    TEXT id PK
    TEXT email UK
    TEXT google_sub UK
    TEXT keycloak_sub
    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
    INTEGER created_at
    INTEGER updated_at
  }
  pending_auth {
    TEXT state PK
    TEXT user_id
    TEXT integration
    INTEGER expires_at
    TEXT session_data
    TEXT code_verifier
    TEXT config
  }
  audit_log {
    INTEGER id PK
    TEXT user_id
    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
    INTEGER created_at
  }
  oauth_auth_codes {
    TEXT code PK
    TEXT client_id FK
    TEXT user_id FK
    TEXT redirect_uri
    TEXT code_challenge
    TEXT scope
    TEXT resource
    INTEGER expires_at
  }
  oauth_refresh_tokens {
    TEXT token_hash PK
    TEXT client_id FK
    TEXT user_id FK
    TEXT scope
    INTEGER created_at
    INTEGER expires_at
  }

The relationships are logical. There are no foreign-key constraints in the DDL — user_id and client_id are plain TEXT columns.

Every timestamp is a Unix time in seconds, stored as an INTEGER.

users#

Identity. One row per person, created on first SSO login or by the local seed script.

connections#

The source of truth for "is this user connected to this integration". One row per user × integration, enforced by UNIQUE(user_id, integration). Writes are upserts on that pair.

The upsert uses COALESCE(excluded.config, connections.config), so a token refresh that re-stores without a config never wipes a stored instance origin.

Disconnecting deletes the whole row. No provider-side revocation is attempted.

pending_auth#

In-flight handshake state, minutes-lived. One table serves three unrelated flows, discriminated by the integration column:

Rows are single-use: verifying a state deletes it, and redeeming an authorize ticket deletes it unconditionally whether or not the binding check passes. Expired rows are swept opportunistically whenever a new one is created.

audit_log#

Every tool execution, success or failure.

Indexes: idx_audit_user(user_id, created_at) and idx_audit_integration(integration, created_at).

Rows are written by the audit logger when AUDIT_LOG_DEST=sqlite — which, despite the name, writes to whichever backend is configured. The other destinations write nothing here.

Nothing prunes this table. Plan retention yourself.

oauth_clients#

MCP clients registered through POST /register.

No client secret column exists — every registered client is public.

oauth_auth_codes#

MCP authorization codes. 32 random bytes, 60-second TTL.

The row is deleted on any lookup hit, before the checks run — a code cannot be replayed even by a failed attempt. Revoking an agent also deletes its outstanding codes.

oauth_refresh_tokens#

Rotating refresh tokens, 30-day TTL.

This table is what GET /api/agents reads: live rows grouped by client_id, joined to oauth_clients for the name, reporting the earliest created_at and latest expires_at. The grouping query is dialect-aware — STRING_AGG on PostgreSQL, GROUP_CONCAT on SQLite.

Rotation runs SELECT, DELETE, INSERT in one transaction, and single-use is enforced by the DELETE affecting exactly one row.

Schema initialisation#

There is no versioned migration table. Schema setup is one idempotent function that dispatches on the adapter's dialect, and it is a deliberate explicit call at startup rather than an import side effect — which is what lets the test suite stand up a throwaway database of either dialect against the exact production DDL.

• SQLite runs the CREATE TABLE IF NOT EXISTS block, then eleven bare ALTER TABLE … ADD COLUMN statements in a try/catch that swallows only duplicate column name and rethrows anything else.
• PostgreSQL runs the same tables, then one ADD COLUMN IF NOT EXISTS batch — no try/catch needed on 9.6 and later.

The eleven added columns are the ones marked as migrations above: users.email, users.google_sub, users.api_key_enc, users.keycloak_sub, users.api_key_sha, connections.cookies, connections.config, pending_auth.session_data, pending_auth.code_verifier, pending_auth.config, and oauth_refresh_tokens.created_at. Two partial indexes follow: idx_users_keycloak_sub (unique, WHERE keycloak_sub IS NOT NULL) and idx_users_api_key_sha.

Where the two backends differ#

better-sqlite3 is synchronous while the adapter interface is async, so an await inside a SQLite transaction callback yields the loop and a second caller could issue BEGIN mid-transaction. The SQLite adapter chains transactions through a promise queue that stays alive across rejections. Only transactions are serialized — single statements are already atomic.

Migrating SQLite to PostgreSQL#

npm run migrate:sqlite-to-postgres -w @workbench/server   # dry run by default

The script applies the production schema to the target first, diffs source and target columns and reports any source-only columns it will not copy, refuses a non-empty target unless --allow-nonempty, copies everything in one transaction with INSERT … ON CONFLICT DO NOTHING, resyncs every SERIAL sequence with setval, and verifies row counts per table. Nothing is written without --apply.

pending_auth and oauth_auth_codes hold short-lived handshake state. --skip them unless the cutover is immediate. The script does not cut over — that is a separate DATABASE_URL change.