Storage

Included from Basic tier

OpsChain's persistent state lives in two places: the data store (a PostgreSQL database or an embedded SQLite file) and the blobstore (content-addressed bulk bytes — a local directory, or a shared S3-compatible store). Both are selected by config.yaml — the store, blob_root and blobstore keys, deploy-authoritative; see opschain docs config — and both are swept by retention (opschain docs retention) and maintained by database maintenance (opschain docs database-maintenance).

Choosing the store

The store key is interpreted by shape, not by a separate type switch:

• A value starting postgres:// or postgresql:// opens the PostgreSQL adapter.
• Anything else is a SQLite database file path.
• Empty means <opschain home>/opschain.db — the embedded default.

The scheduler, retention and database-maintenance jobs share the same connection and derive their SQL dialect from the same decision. The embedded SQLite store is the zero-config development harness — the run engine, the runner and agent fleet, licensing and the local secret store behind the API. The runner and agent registry, the fleet queue and the scheduler speak both dialects and work there in full; only the fleet watch (the aged-out runner sweep and its events) and the queue's run-owner enrichment need PostgreSQL. Every estate, identity, governance, workflow-catalog, notification and provider family lives in the PostgreSQL store, with the server-emitted events, and answers the typed "PostgreSQL required" posture there until the deployment moves to a postgres:// store of its own (opschain docs api-v2, "the embedded SQLite store", has the family list).

The SQLite server is a labelled development harness

A server with no store configured has no sign-in, so the binary makes the posture visible rather than letting it pass for an evaluation server:

• Loopback by default. The bind address defaults to 127.0.0.1:8443 (the same default on every store). On the SQLite store an explicit non-loopback address — --listen 0.0.0.0:8443, or listen: in config.yaml — is honoured, and the startup notice says so, because anything that can reach the address can use the harness.
• A startup notice. Once the socket is open the server prints, on its notice stream, the posture by name (development harness on the embedded SQLite store), what it serves (the run engine behind the run/step/log API, the runner and agent fleet, licensing, the local secret store), what it does not (users and sign-in, the estate, templates, the workflow catalog, notifications, governance, providers, the GUI), the address it bound and whether that is loopback, and the evaluation route below. The notice carries no secret.
• No GUI. The GUI needs a server on PostgreSQL. On the SQLite store every GUI path at the root origin answers one plain HTML page (status 200, no scripts, no external assets) saying so and naming the evaluation route; /api, /healthz, /readyz, /metrics and the admin plane answer exactly as before. gui.enabled: false still restores the bare root (unknown paths 404).
• Kept: the local secret store behind secret-vault:// references, and the static admin token (--admin-token) as the admin plane's guard.

Evaluating the full server

The full server — users and sign-in, the estate, templates, workflows, governance, notifications and the GUI — needs PostgreSQL, and the one-command way to evaluate it is the go-opschain Docker Compose (github.com/LimePoint/go-opschain), which starts PostgreSQL, the server and the GUI together and is kept current with every release:

git clone git@github.com:LimePoint/go-opschain.git && cd go-opschain
./configure        # asks a few questions; writes .env, including PGPASSWORD
docker compose up

The server and the GUI answer at http://localhost:8443 (plain HTTP, the local-evaluation posture), and the first boot prints the admin password once to the server's log (docker compose logs server). Its PostgreSQL answers database opschain as user opschain on port 5432 — the DSN postgres://opschain@localhost:5432/opschain with the PGPASSWORD its ./configure wrote to the compose's .env. A binary of your own can be pointed at that database instead of the compose's server: opschain server configure --store postgres://opschain@localhost:5432/opschain, with PGPASSWORD=… in server/keys.env (never in the DSN). server configure prints this route whenever the store it persists is the SQLite file. Orchestrating Docker from the binary (server start --with-postgres) and an embedded PostgreSQL are deliberately not offered.

PostgreSQL connection environment fallbacks

Credentials never need to appear in config.yaml: pgx follows libpq semantics, so every connection parameter absent from the DSN falls back to the standard PostgreSQL environment variables — PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, PGSSLMODE, and the rest of the libpq family — which deployments keep in keys.env. A bare store: postgres:// resolves entirely from the environment; a partial DSN (postgres://127.0.0.1:5432/opschain) fills only its missing parameters. This behaviour is pinned by tests so a pgx upgrade cannot silently drop it.

Local mode

opschain … --local — the authoring lane that runs changes and workflows in your repository without a server — keeps its history (runs, steps, events, logs, artifacts, database property layers) in its own SQLite file, <opschain home>/local/opschain.db, beside its keyset and apart from any server's store: the same engine and the same logical schema, created and migrated on the first local command that needs it. There is nothing to start and nothing to manage; opschain state reset and state doctor manage its contents (opschain docs cli/state; state doctor prints the file as store: <path>). It is a per-user lab store: nothing in it is licensed, served or reachable from the network — keep the home private.

The layout under one opschain home:

Path	Lane	Keyset
<opschain home>/local/opschain.db	local mode (--local)	local/keys.env — generated on first use
<opschain home>/opschain.db	a server on SQLite (server start with no store configured)	server/keys.env — written by server configure

The two lanes never share a file: each encrypts its sensitive values with its own keyset, so one file read by both would refuse the other lane's envelopes by name. server backup and server restore act on the server store only; state reset acts on the local store only.

Moving an existing local store. Local stores used to live at <opschain home>/opschain.db. The first local command that opens the store on such a home moves the file to local/opschain.db (with its -wal/-shm sidecars) when all three hold: local/opschain.db does not exist yet, opschain.db does, and no server is configured in the home (server/config.yaml absent). It prints one line on stderr naming both paths — local store moved: <old> → <new> … — and the data reads as before with the same local/keys.env. When a server is configured, the home's opschain.db is the server's file and is never touched: local mode starts a fresh store at local/opschain.db.

Encryption at rest — your local keyset. The local store owns a generated keyset of its own, <opschain home>/local/keys.env (OPSCHAIN_PRIMARY_KEY and OPSCHAIN_KEY_DERIVATION_SALT, 32 random bytes each, mode 0600, created on the first local command that opens the store, the same vocabulary as a server's server/keys.env). With it the local store encrypts exactly what the server store encrypts: sensitive step outputs (opschain step set-output --sensitive, also masked ****** in the rendered logs) and the database property and settings layers are written as encryption envelopes, never in plaintext, and never with a server's keys from the environment. The rules:

• Generated once, never regenerated over. A complete keys.env is reused for the life of the store; an incomplete one (one entry missing) stops every local command with an error naming the file — restore it rather than deleting it. Only an empty file is regenerated.
• Keep it with the file. local/opschain.db and local/keys.env travel together: a store opened without its keyset refuses to open rather than reading encrypted rows as garbage, and a removed or replaced keyset makes every value encrypted with the old one unrecoverable. Deleting the home's local/opschain.db and local/keys.env together (while no local command runs) is the reset to a fresh first start.
• Existing stores upgrade in place. A store created before the keyset existed keeps its plaintext rows readable; each row is encrypted by its next write (a new sensitive output, a new property version). opschain state reset --project <code> deletes a project's old plaintext rows outright if you want them gone now.
• state doctor reports it (store: <path> then keyset: <path> (present, mode 0600) — the file and the keyset in force), and state reset keeps it — a project reset never touches the keyset unless you pass --purge-keys, and even then it is removed only once nothing in the store is still encrypted with it (another project's sensitive outputs or layers keep it, with the reason printed). See opschain docs cli/state-reset.

Migrations

Migrations run at server start, inside the store adapter's Open, before anything is served — there is no separate migrate command.

• PostgreSQL: an empty database (no public.nodes table) is first bootstrapped with the embedded base schema — the engine-relevant subset of the adopted upstream db/structure.sql, applied in a single transaction so a failed apply leaves the database empty rather than half-built. A database that already carries the adopted upstream schema is used as-is. Then the package's embedded goose migrations are applied: additive-only (the new subsystems — step_transitions, log_segments, leases, queue_ready, and the go_port_shims port bridge rows), tracked in goose's own version table, never in the upstream schema_migrations lineage.
• SQLite: the schema is the SQLite dialect of the same logical schema, owned by this repo from day one and migrated with its own embedded goose migrations, creating the database file as needed.

Two safety mechanisms guard the migrate step on PostgreSQL:

• The migration lock. The whole step — empty-database bootstrap, goose, ledger reconciliation — plus the boot-time table ensures outside goose and the offline opschain server migrate up verb run under one cluster-wide advisory lock, so any number of servers booting against one database produce exactly one schema writer; the rest wait and proceed.
• The newer-schema gate. Every migration records its phase in the migration ledger: expand (additive — an older binary runs unchanged against the migrated schema) or contract (an older binary cannot be trusted against it). A binary booting against a database migrated by a NEWER build starts only when every migration it does not know is recorded expand-phase — then it notes rollback mode on the boot log and records a warn:database:schema_ahead event. Any unknown contract-phase or unrecorded migration refuses the boot, naming the versions and the remedy (run the newer build, or restore a backup taken at this binary's schema level). OPSCHAIN_SKIP_MIGRATION_VERIFY=1 is the emergency bypass, shouted on the boot log.

Supported PostgreSQL versions

A new deployment runs PostgreSQL 18.6 or newer. The previous major (17) is supported: the server starts with a notice and records a warn:database:version_n_minus_1 event once per boot so the upgrade can be planned from the audit history. Anything older than 17 is refused at start-up with a message naming this page; an 18.x minor below 18.6 starts with a notice recommending the minor upgrade. The window moves one major at a time as new PostgreSQL releases are adopted; this page always states the current minimum and the supported previous major.

The blobstore

The blobstore is content-addressed storage keyed by sha256:<hex> digests, laid out as sha256/<first two hex chars>/<hex>. Identical content yields the same digest, so blobs are shared and deleted only when no row references them. Two backends, selected by the blobstore.kind config key:

• fs (the default): a local directory rooted at blob_root (default <opschain home>/blobs), written via a temp-file-plus-rename on the same filesystem so a partially written blob is never visible under its digest. Single-server only — a second replica cannot read this one's bytes.
• s3: a generic S3-compatible store (one shared bucket on cloud object storage; a site-replicated store such as MinIO on-prem) every server replica reaches, speaking the plain S3 REST contract with SigV4 signing. An S3 object is only ever visible complete, so the same atomic-write promise holds. The /readyz blobstore check probes whichever backend is configured. Keys, credentials and addressing are the blobstore.* config keys (opschain docs config).

What lives in blobs:

• Step log segment bytes — the log pipe compresses each sealed segment and stores it as a blob; the log_segments rows in the store carry the blob_digest. A running step attempt's log accumulates in one spool on one server replica (its log owner — the first replica that received a chunk of the attempt, recorded in log_attempt_owners; other replicas forward the attempt's chunks there); the owner seals the spool into a segment when it crosses the rolling threshold and, whatever its size, when the attempt ends, so a completed step is served from blobs only and reads identically from every replica.
• Artefact content — bytes uploaded by runner jobs (PutArtefact) and through the artefacts API; the artefacts rows carry the digest, size and content type.

Retention's export-before-delete archives live in the blobstore too: each batch is sealed as one verified tar object (rows + referenced blob bytes + manifest), referenced by digest in the retention:sweep event — so archives survive replica loss and leader movement like every other blob.

Key material: keys.env

$OPSCHAIN_HOME/server/keys.env (0600) holds the server's secrets as KEY=value lines, provisioned by opschain server configure. The names are the upstream environment variables, so operator-supplied and generated keys share one vocabulary:

Key	Role
OPSCHAIN_TOKEN_SECRET_KEY	The explicit HS256 secret user tokens are validated with. Optional on an adopted database: when empty, the secret resolves from the stored token.secret_key setting; when set it wins (deploy-authoritative).
OPSCHAIN_PRIMARY_KEY	ActiveRecord column-encryption primary key: with the salt, the properties/settings data columns are written as encryption envelopes and decrypted on read (plaintext rows still read).
OPSCHAIN_DETERMINISTIC_KEY	Exists for upstream parity; it feeds only the deterministic encryption scheme, which no upstream column uses.
OPSCHAIN_KEY_DERIVATION_SALT	The key-derivation salt paired with the primary key.
OPSCHAIN_PREVIOUS_KEYS	Optional. The rotated-out primary keys, comma-separated, each optionally <key>:<its own salt> (a bare key derives with the current salt): still decrypted, never written under. Set during a key rotation; dropped once opschain admin encryption status lists the key as retired.

Without encryption credentials, the PostgreSQL adapter refuses to open a database that already holds encrypted rows rather than silently writing plaintext beside them. Losing the column-encryption keys makes encrypted data unrecoverable — keys.env is never included in a backup or a support bundle (opschain docs support-bundle reports key presence only), so back it up separately.

Key references and rotation. Every envelope the server writes names the key that sealed it (the ki header: the leading 64 bits of SHA-256 over the derived AES key, 16 hex characters everywhere it is printed), so the current key can be rotated: set the new key as OPSCHAIN_PRIMARY_KEY, move the old one into OPSCHAIN_PREVIOUS_KEYS, roll every replica, run opschain admin encryption reencrypt, and drop the old key once opschain admin encryption status reports it retired. Envelopes written before key references existed (or by the Rails application) decrypt by trying the current key and then each previous key, and are rewritten with a reference by the next re-encryption run. The server keeps a registry of every key id that has been a current key against the database (encryption_keys): a server booting without a registered, unretired key refuses to start, naming the key id — so every replica of an estate must carry the same key set, and a replica rolled with a stale keys.env fails loudly at boot instead of request by request. The verbs, with the inventory of encrypted columns they count, are opschain docs cli/admin-encryption; the rotation drill and its evidence are in the encryption key rotation runbook of the operations runbooks.

SQLite vs PostgreSQL: the differences the code encodes

Secrets follow the store: the local AGE file store under the home serves the local lane and a server on the SQLite store, while a server on PostgreSQL resolves secret-vault:// references only through registered secrets providers and never reads the AGE store (opschain docs secrets-providers).

Concern	PostgreSQL	SQLite
Scheduler claims	FOR UPDATE SKIP LOCKED batch claims — the multi-replica path	The degenerate single-process path: plain single-writer transactions
Scheduler connection	Shared pgx pool	One connection (MaxOpenConns 1), WAL journal mode, busy_timeout 10000, synchronous NORMAL, immediate transactions
Timestamp encoding	Native timestamp columns	Scheduler tables: fixed-width UTC text (lexicographic = chronological); store tables: unix-nanosecond integers
Database maintenance	Per-table ANALYZE / VACUUM (ANALYZE) over the churn set	A single PRAGMA optimize per run
Port bridge rows	go_port_shims beside the adopted tables (retention deletes them with their rows)	Not needed — the schema is native
Auth stack	The adopted auth tables back the authenticator; the admin gate can authorise from them	No PG pool, so the v1 API serves unauthenticated — the loopback-bound development harness, labelled at startup, the static admin token as the admin plane's guard
GUI	Served at the root origin (gui.enabled)	Not served: every GUI path answers the plain "needs PostgreSQL" page
Bind address	listen as configured (default 127.0.0.1:8443)	Default 127.0.0.1:8443; an explicit non-loopback listen is honoured and called out in the startup notice
Column encryption	ActiveRecord-compatible envelopes when keys are configured	The same envelopes and the same column set: a server's SQLite store with keys.env keys configured, local mode always with its own generated local/keys.env
opschain server backup	Refused with guidance (below)	Supported online

Backup and restore

• Embedded (SQLite): opschain server backup <dir> takes an online, consistent backup — the store via VACUUM INTO (transactional, safe against a running server under WAL) plus blobs.tar of the blob root, sealed by a SHA-256 manifest.json (format opschain-backup/v1). opschain server restore <dir> verifies the manifest before touching anything and requires the server stopped; the next start's reconciliation handles in-flight state (leases that died with the backup fail fast as runner_lost, retriable; queued steps dispatch again). Keys are NOT included — back up server/keys.env (and the vault master key) separately. See opschain docs cli/server-backup and opschain docs cli/server-restore.
• PostgreSQL: both commands refuse with the documented delegation — back the database up with the operator's standard tooling (pg_dump / PITR) together with the blob root and the key material; opschain server backup covers embedded SQLite deployments only.

Failure modes

• A bad DSN or unreachable database fails boot with a named error (parse postgres dsn: …, ping postgres: …) — the server never starts half-stored.
• A failed empty-database bootstrap rolls back to an empty database; if even the rollback fails, the error says the database may be partially initialised and should be dropped/recreated or restored from backup.
• Encrypted rows with no keys refuse to open (see keys.env above, and the local keyset under "Local mode") — the failure is at start, loud, not a silent plaintext write. A sensitive step output written to a store that has no keys is refused by name, never stored in plaintext.
• A partially written blob is never visible: writes land under a tmp scratch directory and are renamed into place atomically; an unknown digest reads as not-found.

Related pages

• opschain docs config — the store, listen and blob_root keys.
• opschain docs cli/server-start, opschain docs cli/server-configure — the harness notice and the evaluation hint.
• opschain docs retention — what ages out of both stores, and the archive target.
• opschain docs database-maintenance — keeping the store healthy.
• opschain docs cli/server-backup, opschain docs cli/server-restore — the embedded backup surface.