---
title: Database per tenant
description: Catalog mode — one fabriq tier serving a dedicated Postgres database per tenant, with explicit provisioning, a bounded pool, and a sweeping worker plane.
---

Catalog mode gives every tenant its **own Postgres database** behind one
fabriq tier. The facade, `core/`, and every call site are unchanged — the
tenant on `ctx` routes to that tenant's database instead of a shared one.
Choose it for contractual isolation, per-tenant backup/restore/export,
residency, or hosts (like TwinOS) that co-locate fabriq's tables with
other extensions' tables in one database per tenant.

```yaml
catalog:
  dsn: postgres://fabriq_ctl@ctl-db:5432/fabriq_control   # control plane
  clusterDsns:                                            # server-level DSNs
    eu-1: postgres://fabriq_app@pg-eu-1:5432/postgres
    us-1: postgres://fabriq_app@pg-us-1:5432/postgres
  cacheTtl: 30s          # route freshness (suspension visibility)
  maxActiveShards: 128   # open tenant pools, LRU-evicted idle-first
```

Catalog mode is mutually exclusive with `postgres.dsn` and `shards` — one
routing authority. Projections additionally require `redis`.

## Topology

- **Control database** — holds only `fabriq_tenant_catalog` (tenant →
  cluster, database name, state, migration version). It is consulted on
  route cache misses and by the sweeper's scans; it is never a data path.
  "One small Postgres" is the intended size.
- **Clusters** — ordinary Postgres servers named in `clusterDsns`. Spread
  tenants across **hundreds of databases per cluster, not thousands**:
  autovacuum, backups, and connection memory all scale with database
  count — and on TimescaleDB images, so do per-database scheduler
  workers (a laptop-sized container degrades past ~100–150 databases;
  the load harness reproduces this). Add clusters before a cluster's
  database count gets silly.
  Residency falls out of placement: an EU tenant provisions onto `eu-1`
  and its rows never leave it.
- **Tenant databases** — created by provisioning, named `fabriq_{tenant}`,
  each carrying fabriq's full migration chain (all infra tables are
  `fabriq_`-prefixed, so forge extensions' prefixed tables co-exist).
  RLS stays ON inside each database: routing is the first isolation
  boundary, RLS the second.
- **One fabriq tier** — any number of replicas. Serving pools are lazy
  (dial on first touch) and capped (`maxActiveShards`); worker replicas
  cooperate per tenant database via advisory locks.

## Credentials

The serving tier must **not** be superuser — RLS does not bind superusers,
and boot refuses such credentials (`catalog.allowSuperuser: true` exists
for dev/test only). Give the serving role CONNECT + DML on tenant
databases. Provisioning (the CLI) is a separate concern and needs
CREATEDB + the migration chain's rights; run it with its own role.

Every cluster DSN is dialed at boot — a dead or misconfigured cluster
fails the deploy, not the first tenant request routed to it.

## High availability (opt-in)

The control database (`fabriq_tenant_catalog`) is a single Postgres by
default. That is fine for steady state, but a control-primary outage longer
than `cacheTtl` stops routing for any uncached tenant, blocks provisioning,
and halts the reconciler's leader election. HA is **opt-in read-replica
fallback** for the routing read path — writes and election stay put, on a
floating write endpoint your infra repoints on failover. This is the
standard managed-Postgres HA division of labour: fabriq never promotes a
standby and never writes to a replica.

**Topology:** one primary (the write endpoint named by `catalog.dsn` — a
floating DNS name, VIP, HAProxy target, or your managed provider's endpoint,
e.g. RDS/Cloud SQL) plus one or more hot-standby replicas behind streaming
replication. Promotion, fencing, and repointing the floating endpoint are
your infra's job (`pg_promote`, Patroni, RDS/Cloud SQL failover, etc.) —
fabriq has no promotion path of its own, which is what rules out
split-brain.

```yaml
catalog:
  dsn: postgres://fabriq_ctl@ctl-db:5432/fabriq_control   # floating write endpoint
  replicaDsns:                                            # optional hot standbys
    - postgres://fabriq_ctl@ctl-replica-1:5432/fabriq_control
    - postgres://fabriq_ctl@ctl-replica-2:5432/fabriq_control
  clusterDsns:
    eu-1: postgres://fabriq_app@pg-eu-1:5432/postgres
```

Leave `replicaDsns` empty and nothing changes — single-Postgres is still the
default, byte-for-byte.

**What fabriq does:**

- **Read fallback, primary-first.** Routing reads try the primary first; a
  replica is only consulted when the primary is unreachable (not merely
  "returned not found" — that's an authoritative answer). Steady-state
  staleness is zero, because the primary answers every request it can.
- **Clean typed write failure.** Provisioning `Put` and the reconciler
  elector never touch a replica. During a primary outage they fail with a
  typed, retryable error; the idempotent provisioner resumes once the write
  endpoint is reachable again.
- **Automatic reconnect.** Once the floating endpoint repoints at the
  promoted node, fabriq's pool reconnects on its own — no restart needed.
- **Elector re-campaigns.** The reconciler's leader abdicates on session
  loss and re-campaigns against whatever now answers the write endpoint. A
  brief dual-leader window during promotion is harmless: the reconciler is
  idempotent.

**What the infra does:** decide when to promote a standby, fence the old
primary (so it can never accept writes again), and repoint the floating
write endpoint at the new primary. fabriq has no opinion on *which* replica
gets promoted or *when* — it only reacts to where the write endpoint points.

**Staleness is safe by construction.** A replica can lag. Two guards make
that harmless instead of dangerous:

- A NotFound served from a replica is marked **degraded** and is never
  negative-cached — a lagged replica can't route a real tenant off for a
  cache TTL.
- The version gate (fleet upgrades, above) is **fail-closed**: if a
  replica's recorded migration version is behind the binary's floor, routing
  through it returns `CodeUnavailable` rather than a route to a
  schema-behind database. That `CodeUnavailable` is temporary and
  self-healing: when it is derived from a replica read during an outage it is
  marked degraded (non-cacheable), so it clears on the very next request once
  the replica catches up or the primary recovers — never pinned, never a
  silent correctness hazard.

**Boot behavior differs deliberately between a bad config and a bad
network:** a malformed replica DSN is a configuration error and **fails the
boot** (same treatment as any other invalid config). An **unreachable**
replica does **not** fail the boot — it silently no-ops as a fallback that
just isn't there yet, because refusing to boot over a transient network
blip would defeat the point of HA. The replica pool dials lazily.

**Metrics** (alongside the sweep/pool instruments above):
`fabriq_catalog_read_primary_total`, `fabriq_catalog_read_replica_total`,
and `fabriq_catalog_read_failover_total`. A nonzero, climbing
`failover_total` is the operational signal that routing reads are being
served by a replica — i.e. the primary is currently unreachable.

## Provisioning runbook

Provisioning is **explicit** (never an implicit first-write side effect)
and **idempotent** (re-running converges; a crash resumes from the
catalog state row):

```bash
export FABRIQ_CATALOG_DSN=postgres://fabriq_ctl@ctl-db:5432/fabriq_control
export FABRIQ_CLUSTER_DSNS="eu-1=postgres://...,us-1=postgres://..."

fabriq tenant provision acme --cluster eu-1   # pending→creating→migrating→active
fabriq tenant list                            # states + versions, fleet-wide
fabriq tenant suspend acme                    # route off within cacheTtl; DB untouched
fabriq tenant resume acme
```

fabriq **never drops a database**. Offboarding is `suspend` (routes the
tenant off); the physical `DROP DATABASE` is a deliberate human step
after whatever retention your contracts demand. A tenant stuck in
`failed` is listable (`tenant list`) and safe to re-`provision`.

### Fleet upgrades

Each catalog entry records its database's migration version, and the
router **fails closed**: a tenant whose database is below the binary's
migration floor gets a typed 503 instead of a silently-corrupting serve.
Rolling a new fabriq version is therefore:

```bash
fabriq tenant migrate-all --batch 8 --max-failures 3
```

The roller walks the fleet in bounded batches, records new versions, and
stops at the failure budget (always safe to re-run). Deploy binaries and
roll in either order — the gate protects both directions.

## Worker plane (the sweeper)

Instead of boot-time loops per database, worker replicas run a **sweeper**:
scan the catalog, and for each active tenant try-claim that database's own
advisory locks for one maintenance pass — relay the outbox, materialize
quiet CRDT documents, compact due logs. Losers of a claim skip cleanly, so
any number of replicas cooperate.

Idle tenants back off exponentially (5s → 5min) in a decaying table; the
write path publishes `fabriq:wake:{tenant}` over Redis so busy tenants are
swept on the next pass. Cost tracks **active** tenants, not fleet size — a
full scheduling pass over 10k catalog entries with 100 active costs ~20ms.

Projection sinks (graph/search) stay shared; each tenant's projection
bookkeeping lives in its own database. Blue-green rebuilds, the drift
reconciler, live queries, and document history archiving all route per
tenant in catalog mode now: the reconciler elects a single scanner on the
catalog control database; rebuilds replay from each tenant's own database;
live queries require Redis (the tailer); and archiving requires
`storage.storageDriver` and seals each tenant's trimmed CRDT history to its
own CAS bucket.

Fleet-wide reporting across all those per-tenant databases is now an
opt-in third projection peer rather than a deferred item: the
[analytics sink](/docs/fabriq/(operations)/analytics-sink) reads the same shared event stream
graph/search already consume, so it needs no per-tenant-database wiring
here — it is the one deliberate, narrow, auditable place tenant data is
co-located, gated by explicit per-entity marking and field redaction.

## Tenant management API

With the admin extension mounted and `WithTenantsAdmin()` enabled, the
`tenants.admin` capability exposes the provisioning surface over HTTP
(catalog mode only) — the HTTP twin of the `fabriq tenant` CLI, backed by
the same idempotent state machine:

| Method + path | Effect |
| --- | --- |
| `POST {base}/tenants` `{tenantId, clusterId}` | Provision (async job → `{jobId}`) |
| `POST {base}/tenants/migrate-all` | Roll the fleet to head (async job) |
| `GET {base}/tenants/jobs/:id` / `.../stream` | Poll / SSE-stream a job |
| `GET {base}/tenants` | List catalog entries |
| `GET {base}/tenants/:id` | One tenant's state + version |
| `POST {base}/tenants/:id/suspend` / `.../resume` | Route off / back on |

Provision and migrate-all run as background jobs (HTTP returns `202` with a
`jobId`); poll `jobs/:id` or stream `jobs/:id/stream` for progress. The
job-status routes are unguarded beyond the unguessable job id (parity with
the migrations job API).

### Connection info

`WithConnectionsRead()` enables a **read-only** topology surface under the
`connections.read` capability — deliberately separate from the mutating
`tenants.admin`, so a dashboard viewer can inspect the fleet without gaining
provision/suspend rights:

| Method + path | Effect |
| --- | --- |
| `GET {base}/connections` | Configured Postgres clusters + Redis/FalkorDB/Elasticsearch/blob, with pool occupancy and a bounded health probe per connection |
| `GET {base}/tenants/:id/connection` | One tenant's dedicated database (the same `TenantDSN` the router dials), redacted + health-probed |

Each entry reports host, port, database, username, SSL mode, cluster id, and
reachability (`{reachable, latencyMs, error}`); catalog mode adds pool
occupancy (`open`/`held` vs `cap`). **Secrets are redacted server-side**:
passwords and any secret query params are parsed out of every DSN before
serialization and never sent — a configured password surfaces only as a fixed
masked placeholder, never its value. The health probe is bounded so a dead
store cannot wedge the request; `/tenants/:id/connection` is catalog-mode only.

## Connections and PgBouncer

fabriq's own pools are already bounded: `maxActiveShards` × per-shard pool
size is the tier's connection ceiling, and idle-first LRU eviction closes
cold tenants' pools. If cluster-side connection memory still bites (many
tiers, small servers), put **PgBouncer in transaction mode** in front of
each cluster and point `clusterDsns` at it — fabriq speaks plain
parameterized SQL on the serving path. Two caveats:

- The **worker's advisory-lock claims and the relay's LISTEN/NOTIFY need
  real sessions.** Point the worker replicas at the clusters directly (or
  a session-mode pool); the serving tier can ride transaction mode.
- `SET LOCAL` (fabriq's tenant stamping) is transaction-scoped and safe
  in transaction mode.

## Failure contracts

| Failure | Behavior |
| --- | --- |
| Control DB down | Cached routes keep serving until `cacheTtl`; uncached tenants 503 (`unavailable`); the sweeper pauses; recovery is immediate — transport errors are never negative-cached |
| Control primary down, no replicas configured | Same as above — this is the HA-off default |
| Control primary down, replicas configured | Routing reads fall through to a replica; uncached tenants keep resolving instead of 503ing; writes/provisioning still fail typed until the write endpoint recovers |
| Replica reports a stale (behind-floor) version | Router fails closed (`CodeUnavailable`) for that read — marked degraded so it is not cached, clears on the next request, never a route to a schema-behind database |
| A replica is unreachable | Skipped in favor of the next replica (or the primary's original error if none answer); does not fail boot |
| Primary failover / promotion completes | fabriq's write pool reconnects automatically once the floating endpoint repoints; the reconciler elector abdicates and re-campaigns on the promoted node |
| Malformed `catalog.replicaDsns` entry | Fails config validation at boot (same as any invalid DSN) — never silently ignored |
| Tenant DB down | That tenant's dial breaker opens (fast 503s, no dial storm); neighbors unaffected; the sweeper isolates and backs off; recovery is automatic once it dials |
| Provisioning crash | The catalog row resumes the state machine; `failed` tenants are listable |
| Version skew | Router fails closed per tenant until `migrate-all` catches it up |
| Pool cap hit | Acquire waits ≤ 5s for an evictable pool, then 503 |
| Two workers, one tenant | Advisory lock: one wins, the loser skips silently |

These are tested contracts (`chaos_catalog_integration_test.go`), not
aspirations.

## Observability

`/metrics` (forge extension) gains, beyond the shared instruments:
`fabriq_sweep_pass_duration_seconds`, `fabriq_sweep_tenants_tracked`,
`fabriq_sweep_tenants_eligible`, `fabriq_sweep_{swept,busy,errors}_total`,
and `fabriq_pool_shards_{open,held}`. Watch errors-total (a tenant
backing off on failures) and pool open vs cap (undersized
`maxActiveShards` shows up as acquire waits). With HA replicas configured,
also watch `fabriq_catalog_read_{primary,replica,failover}_total` (see
"High availability" above) — `failover_total` climbing is the signal that
the control primary is currently unreachable.

## Choosing a mode

| | `single`/`shards` (ADR 0002/0007) | `catalog` (ADR 0011) |
| --- | --- | --- |
| Isolation | RLS rows / RLS + shard | **physical database** + RLS |
| Tenants per DB | many | **one** |
| Onboarding | implicit | explicit verb (< 5s) |
| Backup/restore per tenant | filtered export | **pg_dump the database** |
| Fleet ceiling | shard count | pool-capped, thousands |
| Worker plane | boot-time loops | catalog sweeper |
| Cross-tenant reporting | [analytics sink](/docs/fabriq/(operations)/analytics-sink) (opt-in, mode-agnostic) | [analytics sink](/docs/fabriq/(operations)/analytics-sink) (opt-in, mode-agnostic) |