Database

Synapass keeps state in four stores, chosen so each one is the right shape for what it holds. This page covers what lives where, how migrations work, and the persistence boundaries that matter when something goes wrong.

The four stores#

PostgreSQL needs transactions and foreign keys — a policy write that half-applies is worse than a failed write. Redis needs atomic increments on high-churn ephemeral data. ClickHouse is columnar and analytical; one row per request per attempt would bloat Postgres and make dashboards slow. NATS gives durable consumers, so a worker restart resumes rather than losing work.

The usage row is in Postgres and the trace is in ClickHouse on purpose: usage must be complete because it is billed, whereas traces are high-volume and analytical. They duplicate a few fields for that reason.

Postgres tables#

Core#

Intelligence#

Tools and tunnels#

ClickHouse tables#

Redis keys#

Bodies live in Redis; only metadata reaches Postgres. That keeps the system of record small while leaving the dashboard able to inspect what is cached without ever serving a stored body.

NATS subjects#

Migrations#

Migrations are embedded in the binary and applied in order. Each is recorded in schema_migrations with a checksum, and a mismatch on an already-applied migration is an error, not a silent re-run — a migration that changed after it was applied means the two databases are not the same shape.

Applying them#

Compose sets SYNAPASS_POSTGRES_AUTO_MIGRATE=true, so the gateway migrates at startup. Under change control, turn that off and migrate as a release step:

synapass migrate

The same command migrates both Postgres and ClickHouse.

Ownership: managed_by#

Catalogue rows carry managed_by: bootstrap | api. The bootstrapper applies the configuration file only to rows it owns, so a provider, model or policy edited through the API survives a deploy. To hand a row back to the file, delete it through the API and let the seeder recreate it.

This is the mechanism that stops a dashboard edit from being silently reverted by the next docker compose up.

Persistence boundaries worth knowing#

The record pipeline is lossy on purpose. Usage rows, traces and audit events are written asynchronously and batched. A response never blocks on a write. The cost is that a crash can lose the last few seconds of telemetry, which is why synapass_async_dropped_total and synapass_async_queue_depth exist. What is synchronous — authentication, policy, budgets — is never lossy.

Audit covers the control plane, not inference traffic. A provider created through the API is audited, with before and after values. An inference request is not: its accountability need is served by usage_records, and auditing every request would dwarf the signal. An audit write failure is logged and never fails the mutation it accompanies, because refusing a completed action would leave the system matching neither the operator's intent nor the audit log.

Overrides are events, not edits. Revoking a kill switch means writing the inverse row (enabled: false), so the log keeps what was true and when.

Tool run rows are written before the first step, because agent_steps has a foreign key to agent_runs. A persistence failure does not fail the request, but it is logged, counted and flagged on the event.

Retention#

Prune ClickHouse by partition rather than row-by-row; it is built for whole-part deletes.

Common problems#

Related: Architecture · Observability · Providers · Back to README