Databases

Vaultbase stores everything in one database. SQLite is the default and needs nothing: the file lives in the data directory, backups are a copy of it, and one process serves reads on every core. Postgres is for the cases SQLite is not built for: several servers in front of one database, a managed database your provider backs up and scales, or a database other tools already share.

Beta

Postgres support is in beta. The record API, rules, filters, full-text search, expand, batch, history, files, auth, admin, MCP, hooks and realtime run on it, and the full test suite runs against it in CI. The features listed under Not on Postgres yet answer with a clear error rather than a wrong result.

Choosing the database

The database is chosen by the environment, never by a setting: the server has to open it before it can read any setting.

# SQLite (default): <VAULTBASE_DATA_DIR>/data.db
./vaultbase

# Postgres
VAULTBASE_DATABASE_URL=postgres://user:password@db.internal:5432/vaultbase ./vaultbase

The schema is created and kept up to date at every start, on both. Point a fresh Postgres database at the server and it is ready; point it at a database that an older Vaultbase used and the missing tables, columns and indexes are added.

Postgres 16 or later: the test suite runs against 16, and index suggestions read its EXPLAIN (GENERIC_PLAN). The setup wizard’s first step shows which database the server is on; choose before creating the admin, since nothing is copied from one to the other.

Uploads, logs and the encryption key still live in the data directory on Postgres; only the database moves.

What is the same

Everything the API promises: collections and fields, rules and filters, expand, batch, record history, files and thumbnails, authentication and OAuth2, API tokens, admin, MCP, webhooks, feature flags, notifications, hooks, routes, cron and workers. Values are stored the same way on both databases (booleans as 0/1, dates as Unix seconds, JSON as text), so rules and hooks written for one work on the other.

Not on Postgres yet

Feature	On Postgres
Restore from the backups page	Not available (501); stop the server and run pg_restore with the downloaded .dump
SQL runner	Read-only mode only, inside a read-only transaction with the time budget; no sandbox
Read worker threads, vaultbase cluster	Not used; the connection pool gives read concurrency
helpers.db in hooks	Not available; use helpers.sql
vaultbase backup, wipe and doctor CLI	Not available (exit 1); use pg_dump, DROP SCHEMA or your provider’s tools

Backups on Postgres

The backups page, scheduled backups, the S3 copy and downloads work on Postgres when a pg_dump binary is available: on the PATH, or named by VAULTBASE_PG_DUMP. Its major version must be at least the server’s. Each backup is a pg_dump archive in the custom format (.dump) of the schema Vaultbase uses; restore one with pg_restore. Without pg_dump the backups API answers 501, and your provider’s backups are the way.

Differences to know about

• strftime() in filters accepts %Y %m %d %H %M %S %j with a literal format, and a stored date needs the 'unixepoch' modifier. Other codes answer 400. Single-record rule checks accept the same forms on both databases (see Filter functions).
• Week buckets in aggregations are ISO weeks on Postgres and SQLite’s %W weeks (counted from the first Monday) on SQLite.
• ~ is case-insensitive for every letter on Postgres (ILIKE) and for ASCII letters on SQLite.
• An email-cooldown race that SQLite settles by insertion order is settled by the second of issue, then the id, on Postgres: within one second a parallel request may send one extra email.

Several servers

Any number of servers can share one Postgres database, behind a load balancer or not. A change made through one (a collection, a rule, a field, a hook, a route, a job, a webhook, a flag, a setting) takes effect on the others within a few tens of milliseconds, and realtime subscribers on every server hear about every write, each judged by the collection’s rules as usual. Servers tell each other over Postgres LISTEN/NOTIFY; nothing else is needed between them. Servers that start together take turns upgrading the schema.

What each server needs:

• The same VAULTBASE_JWT_SECRET and VAULTBASE_ENCRYPTION_KEY. Without them each server generates its own into its data directory, and a token signed by one is refused by the others. The server warns at boot when either is unset.
• Shared file storage. Uploaded files live in the data directory unless storage is S3; with several servers, use S3 (or a directory they all mount).

What stays per server: rate limits (each server counts its own requests), logs, metrics, and hooks or routes loaded from files in the data directory. Scheduled jobs, backups, queue jobs and webhook deliveries each run once, on whichever server claims them first. A queue job is held under a lease while it runs, so a server that starts up, or another server, doesn’t run it again; only a job whose lease ran out is taken back (see Queue workers). Cron jobs registered from code (registerJob) are per process: each server runs its own.

How servers stay in step:

• If-Match holds across servers. A conditional PATCH or DELETE holds the record’s row lock from the ETag check to the commit, so two servers can’t both pass the same ETag: one write succeeds, the other gets 412.
• Change messages are sized and retried. NOTIFY payloads are counted in UTF-8 bytes (Postgres’s limit is 8000 bytes), record events never share a message with cache invalidations, and a failed NOTIFY is retried.
• A missed invalidation is caught. Every invalidation also moves the vaultbase_peer_generation sequence. Each server polls it every 10 seconds; a move it didn’t make and may not have heard about makes it reset its caches.

Realtime has no durable replay: an event sent while a client was disconnected (or while its server’s LISTEN connection was down) isn’t delivered later. Refetch what you show after a reconnect.

Hooks

helpers.sql runs on either database with ? placeholders and an array of parameters:

const rows = await helpers.sql.query("SELECT id FROM vb_posts WHERE author = ?", [ctx.auth.id]);
await helpers.sql.transaction(async () => { /* commit on return, roll back on throw */ });

On Postgres, jsonb’s ? operator is written ?? in a statement that has parameters (data ?? 'key'); without parameters, and for ?| and ?&, write it as usual.

helpers.db is synchronous and SQLite-only; it keeps working on SQLite and is deprecated.

Moving a SQLite install to Postgres

There is no built-in migration yet. The schema is identical in shape, so a table-by-table copy with a tool of your choice works: create the collections on the new server first (or copy vaultbase_collections and start the server once, which creates the tables), then copy the rows.

For contributors

The test suite runs on Postgres when VAULTBASE_TEST_DB names a server: every initDb(":memory:") opens a fresh schema there and closeDb drops it. Suites about SQLite-only features skip themselves.

docker run -d --name vaultbase-pg -e POSTGRES_USER=vaultbase -e POSTGRES_PASSWORD=vaultbase -e POSTGRES_DB=vaultbase -p 5433:5432 postgres:16-alpine
VAULTBASE_TEST_DB=postgres://vaultbase:vaultbase@127.0.0.1:5433/vaultbase bun test