Database per tenant

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.

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.

Topology01

• 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.

Credentials02

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)03

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.

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 runbook04

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

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:

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)05

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 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 API06

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:

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:

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 PgBouncer07

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 contracts08

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

Observability09

/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 mode10