Skip to content

RFC 0002 — Database discovery

Status: accepted (scaffold) Date: 2026-07-24 Implemented in: apps/api/internal/store/discovery

Problem

Every request arrives scoped to a tenant. Before any handler can touch a repository it has to answer: which database, which schema, with which credentials.

Getting this wrong has two failure modes and they are not symmetric. Route to the wrong database and you serve one customer's trajectories to another — for a platform selling assurance, that is terminal. Route rigidly and you cannot move a tenant between clusters, cannot offer dedicated isolation to the enterprise buyer who demands it, and cannot migrate anyone without downtime.

The usual shortcut is to derive the database from a naming convention: tenant acme lives in database o4ve_acme. That works until the first tenant is moved, at which point the convention is a lie that is now encoded in every service.

Decision

Discovery is an explicit, versioned, cached lookup through one chain. It is the only way a store is obtained, and it is the first thing every handler does.

principal ─▶ tenant ─▶ Catalog.Lookup ─▶ Placement ─▶ SecretResolver.DSN ─▶ Opener.Open ─▶ Store
                          (authoritative)   (no creds)      (rotatable)        (pooled)

Four small interfaces, each replaceable independently:

Interface Answers Production implementation
Catalog which cluster, database, schema, and state tenant_placement table in the control-plane DB
SecretResolver what credential reaches it secrets manager, by secret_ref
Opener how to build a pool for it pgx pool, RLS session variable set on checkout
Resolver all of the above, cached the front door handlers call

The Placement record

tenant_id, tier, cluster, database, schema, secret_ref, region, state, revision

Three properties matter:

It is authoritative, never derived. A tenant's location is data, not a naming rule. Moving a tenant is an update to one row.

It holds no credentials. Only a secret_ref. Credentials rotate on their own schedule without a catalog write, and a leaked catalog dump is not a breach.

It carries state and revision. State gates writes: active accepts everything, read_only and migrating reject writes with 409 while reads keep serving, retired rejects everything. Revision is what makes cache invalidation cheap — see below.

Isolation tiers

The tier a tenant is on decides how the placement renders into a connection. All three are served by the same code path.

Tier Layout Isolation mechanism For
pooled shared database, shared schema tenant_id column + Postgres RLS keyed on o4ve.tenant_id self-serve, most tenants
schema shared database, own schema search_path per connection mid-market, noisy-neighbour relief
dedicated own database, possibly own cluster physical enterprise, residency requirements

RLS is the important detail in the pooled tier. The application sets o4ve.tenant_id when it checks a connection out, and the policy on resource compares every row against it. A handler that forgets its WHERE tenant_id = ... returns zero rows rather than someone else's data — the failure mode is a visible bug instead of a silent breach. Application-level scoping stays in place as well; RLS is the backstop, not the plan.

A tenant moves between tiers by writing a new placement, not by changing code.

Caching

Opening a pool per request would be ruinous, and looking the catalog up per request is a round trip on the hot path. The Resolver caches both, with a two-level check:

  1. Inside the TTL (60s default), the cached store is returned with no lookup at all.
  2. After the TTL, the catalog is consulted — but if revision has not moved, the existing pool is kept and the TTL extended. Only a revision change tears down and reopens.

Steady-state traffic therefore costs one lookup per tenant per TTL and zero pool churn, while a migration takes effect within one TTL everywhere. Lowering the TTL raises lookup cost linearly; it does not change correctness.

Migration and cutover

Moving a tenant between clusters, without a maintenance window:

  1. Provision the target. Replicate.
  2. Set state = read_only, bump revision. Within one TTL every node is rejecting writes with 409 and reads keep serving.
  3. Drain replication lag to zero.
  4. Rewrite cluster, database, secret_ref; set state = active; bump revision.
  5. Within one TTL every node has reopened against the target. Retire the source.

Writes fail visibly for the duration of step 3 rather than being silently split between two databases. Clients see 409 with a stable problem type and retry.

Schema migrations across many databases

The dedicated tier means the schema exists in N places, so migrations must be fanned out and must be idempotent — every statement in schema.sql is IF NOT EXISTS or an idempotent ALTER. The fan-out iterates the catalog, applies to each placement, and records the applied revision per placement. Expand-then-contract is mandatory: add columns, deploy code tolerant of both shapes, backfill, then drop. A migration that cannot be rolled forward across a heterogeneous fleet is not shippable.

Alternatives considered

Convention-derived database names. Zero infrastructure, and the first tenant migration breaks it permanently. Rejected.

One database, forever, RLS only. Simplest, and genuinely sufficient for a long time — but the enterprise buyer for a platform whose product is assurance will ask for physical isolation, and retrofitting a discovery layer later means touching every query site. The tiers exist because that conversation is inevitable; the pooled tier is what almost everyone actually runs.

Connection routing in a proxy (pgbouncer/pgcat) instead of the app. Moves the decision out of Go, which is attractive, but the app still needs the placement to set the RLS session variable and to reject writes during cutover. The proxy is a useful optimization later, not a replacement for this layer.

Consequences

Good. One place decides tenancy routing, so it can be tested and audited on its own. Tier changes and cluster moves are data. Single-database deployments — including O4VE_STORE=memory locally — go through the identical path via StaticCatalog and SingleStore, so the request path in development is the request path in production.

Cost. Every handler must call bind() before touching a store; forgetting to is the one mistake this design does not catch by itself. A lint rule or a store handle that can only be obtained from a resolved request would close that hole, and should be added.

Open. Cross-tenant reads for an aggregate benchmark layer do not fit this model at all — that is a separate, anonymized, read-only path, and it needs its own RFC before any of it is built.