Docs/Architecture/Data Architecture & Tenancy

Data Architecture & Tenancy

One Postgres database, one schema per tenant, one control-plane schema — routed by SET search_path on every connection. This is the highest-leverage and highest-risk design decision in the platform (ADR-0002): it gives tenants hard data isolation inside a single database, and it makes connection pooling and migration ordering genuinely dangerous. 

flowchart LR
  REQ["HTTP request
JWT with tenant_id"] --> F["TenantContextFilter"]
  F --> TC["TenantContext
(ThreadLocal)"]
  TC --> R["TenantSchemaResolver"]
  R --> CP["SchemaMultiTenantConnectionProvider
SET search_path = tenant_schema"]
  CP --> DB[("Postgres
· control plane: public
· tenant_a
· tenant_b
· …")]
  F -->|"no claim / bootstrap / health"| FB["public fallback
(metriced)"]
  FB --> DB

1. Tenant resolution request path

  1. TenantContextFilter reads the tenant_id JWT claim on every authenticated request.
  2. The claim is stored in TenantContext (a ThreadLocal; the AI layer restores it across Reactor thread hops on streamed calls).
  3. TenantSchemaResolver maps tenant → schema name.
  4. SchemaMultiTenantConnectionProvider issues SET search_path = <tenant_schema> when handing out a connection. From then on, every unqualified table reference lands in the tenant schema.
  5. Requests with no resolvable tenant — bootstrap, health checks, platform-admin paths — fall back to public, and the fallback is metriced.
The public-schema fallback is a known bug signature

If tenant data appears to "vanish" or writes land in the wrong place, the first suspect is a code path that ran without TenantContext — async executors, scheduled jobs, webhook handlers that skip the filter chain — and silently fell back to public. The fallback exists so bootstrap/health/platform-admin work without a tenant; it is deliberately observable (a dedicated metric) precisely because silent fallback is the classic failure shape.

2. What lives where

SchemaContents
public (control plane) tenant, app_user, plan_limits, platform_hosted_llm_provider, platform_setting, per_user_budget_settings, community_template
<tenant_schema> (one per tenant) Agents, workflows, knowledge documents + pgvector vector_store, Apache AGE graph memory, channel credentials and sessions, provider credentials (LlmProviderCredential), llm_call_log, audit_log, LLM gateway tables, approval records — everything tenant-owned
Redis Session memory (SessionMemoryService: per-conversation history lists and owner records under caller-scoped keys agentic:session:c1:<sha256>, 24h TTL; see Security Architecture §4.1), MCP ownership registry (RedisMcpOwnershipRegistry) for cross-replica STDIO connection ownership. Cache/coordination only — never the system of record.
Object storage S3-API abstraction via storage module (FileStorageService): MinIO in compose, S3/R2 in prod by config. Tenant data export and generated media/documents land here.

3. Flyway — two directories, one version namespace

Migrations live in two directories under core/app/src/main/resources/db/migration/:

DirectoryApplies to≈ FilesWhen it runs
db/migration/controlplanepublic~80On boot, once
db/migration/tenantEvery tenant schema~139+ (V334 latest claimed)At tenant provisioning (replayed into the new schema) and migrate-on-boot on the API shape
One version namespace — a collision breaks startup

The two directories share a single Flyway version namespace. Two files with the same V### number — or a renamed/renumbered file — corrupt the shared flyway_schema_history and stop core from booting. Rules that exist because of it:

  • Always pick the next free version across both directories.
  • Never rename or renumber an applied migration.
  • Run flyway-lint.sh before committing — it guards exactly this.

4. Connection pooling — PgBouncer must be session mode

Transaction pooling = cross-tenant data exposure

SET search_path is connection state, not transaction state. Under PgBouncer transaction pooling a connection checked out for one tenant's transaction can be handed back and reused mid-session for a different tenant — with the previous tenant's search_path still set. The result is reads and writes in the wrong tenant's schema. PgBouncer MUST run in pool_mode = session anywhere it fronts this database. This is a hard requirement of ADR-0002, not a tuning preference.

5. Secrets and encryption

One chokepoint: EncryptionService (core/crypto), AES-256-GCM, keyed by APP_ENCRYPTION_KEY (base64 32-byte) with rotation support via APP_ENCRYPTION_KEY_PREVIOUS. It encrypts LLM provider keys, channel tokens, MCP connection env vars, Google refresh tokens, Sarvam/ElevenLabs/Jev credentials, and hosted-pool keys. The same key value must be present everywhere core boots — a divergent key renders every stored credential undecryptable rather than merely failing loudly.

6. Provisioning and lifecycle

  • Signup: /api/signup creates the Keycloak user and provisions a fresh tenant schema; tenant migrations replay into it at provisioning time.
  • Migrate-on-boot: the API shape (core/app) runs tenant migrations across all schemas on startup; app-worker and app-jobs deliberately do not run Flyway.
  • Data export: tenant-data export uses the storage module (Master Roadmap item #22).

7. Backup posture

LayerSelf-hosted defaultNote
PostgresBundled PostgreSQL 16 (pgvector + AGE) in Compose, or a CloudNativePG cluster / your managed Postgres with HelmIrreplaceable. Back it up with pg_dumpall (Compose) or your operator's backup; see Backup & Restore
Valkey (Redis API)Bundled Valkey or your own Redis-compatible serviceContents are disposable by design (sessions/ownership can be rebuilt)
Object storageSeaweedFS S3 gateway (Full / Helm), local disk (Lite), or any S3 APITenant exports and generated artifacts
Read this before touching anything
  • Back up the database together with APP_ENCRYPTION_KEY; a dump without the key cannot decrypt stored provider keys and channel secrets.
  • Redis is reconstructable; Postgres is not. Never conflate the two when weighing an operation's blast radius.
  • llm_call_log and audit_log are per-tenant tables — platform-wide cost/audit queries must iterate schemas, not assume a central table.