ADR 0009: Tenant databases on orbit-rs are isolated by schema

Status: Accepted Date: 2026-10-24 Scope: Symphony Deciders: Ravindra Boddipalli (accepted 2026-10-24)

Context

open_tenant in crates/symphony-app/src/lib.rs builds Config { backend: "sqlite", url: <path> } for every trial and tenant, so moving only the main database to orbit-rs leaves tenants on ephemeral disk. A cluster that hosts many tenants needs isolation inside one server. orbit-rs does not implement schemas today (ADR 0003 adds them), and the Symphony question “database per tenant or schema per tenant, and the cluster sharding key” is open in DATA_DURABILITY.md.

Decision

Each tenant gets its own schema (tenant_<name>) in a shared orbit-rs database, selected per connection with options=-csearch_path=tenant_<name>. open_tenant takes a backend and URL per tenant instead of hardcoding SQLite. The operator database stays in public. Open for the owner: database per tenant as the alternative; the sharding key above one cluster; roles per tenant and GRANT USAGE on the schema.

Consequences

  • Depends on ADR 0003 being implemented.
  • Schemas are a namespace, not a security boundary (ADR 0003). Calling this isolation requires roles, GRANT/REVOKE USAGE and table privileges in orbit-rs first; until then, documents must not claim tenant isolation.
  • Quota enforcement (tenant_database_size) cannot stat a file and needs a per-schema size query.
  • Install, upgrade and columns_query (current_schema()) already run per schema on PostgreSQL.

Verification

A flow test that creates two tenants in one orbit-rs database with the same table names and checks they do not see each other’s rows, before and after a leader failover.

Implementation record (2026-10-25)

Built in Symphony as TenantStore (crates/symphony-app/src/tenant_store.rs, commit d0b26176), opt-in with SYMPHONY_TENANT_BACKEND=orbit-rs and SYMPHONY_TENANT_ADMIN_URL; SQLite files remain the default. The built form differs from the decision text in these ways:

  • Naming and selection. The schema has the same name as the tenant’s role (the database name, for example trial-<hex>), not tenant_<name>. The default "$user" search path then selects it, so no options=-csearch_path is sent.
  • Roles. Each tenant is a login role that owns its schema and holds nothing elsewhere (ADR 0013). Its password is derived from the server’s secret key and the database name, so none is stored. A superuser URL is used only to create, size and remove tenants.
  • Offboarding is DROP OWNED BY and DROP ROLE. Provisioning refuses a taken name before changing anything.
  • Quota uses pg_total_relation_size summed over the tenant’s schema.
  • Backups. pg_dump cannot yet read orbit-rs (it stops at catalog queries and COPY), so Symphony backs a cluster tenant up as a data file of DELETE and INSERT lines and restores it in one transaction. Making pg_dump work is a separate item under ADR 0001.
  • Speed. Setting up a tenant sends tens of thousands of statements. A whole-table MAX/MIN (COALESCE(MAX(id), 0) + 1) copied every row and took 48 ms at 9,000 rows; it now reads the one column, which cut setup from 173 s to 34 s on a debug build.
  • Transactions. A failed statement aborts the transaction on orbit-rs, as on PostgreSQL. Symphony’s tolerated ADD COLUMN upgrades therefore run in a savepoint; before that, tables such as symphony_session were silently never created.

Verified: tests against a live orbit-rs with two tenants (cross-tenant read, write and create denied; removal deletes role and schema), the admin console on a cluster tenant (create, open, back up, change, restore, delete), and a three-node cluster whose leader process is killed while a tenant is in use: the tenant keeps reading and writing through the remaining nodes. The existing Symphony test suites for trials and backups were run on SQLite and pass.