Skip to content
TesseraQLTesseraQL

Multi-tenancy

A multi-tenant TesseraQL application serves several customer organizations — tenants — from one deployment, keeping each tenant’s data isolated from every other’s. Tenancy is off by default: an application is single-tenant until tenancy.enabled: true is set, and nothing on this page applies before then. Once enabled, every request resolves a tenant before its SQL runs, and that tenant drives either a row predicate (shared schema) or the choice of connection pool (schema/database per tenant).

Tenancy answers which organization’s data a request may touch. What a caller may see within their tenant — their department, their own records — is the job of organizational data scoping, which deliberately mirrors the tenant-predicate mechanism one level deeper.

Isolation modes

Section titled “Isolation modes”

The tenancy block lives in config/application.yml alongside the database coordinates. Three modes exist; shared-schema is the default when tenancy is enabled.

Shared schema

Section titled “Shared schema”

All tenants share the main datasource and every tenant-owned table carries a tenant column. Isolation is a predicate the route author writes into the SQL:

config/application.yml
tenancy:
  enabled: true
  mode: shared-schema
  required: true          # reject requests without a resolvable tenant (the default)
  resolver:
    type: header
    source: X-Tenant-Id
sources:
  main:
    sql:
      file: list.sql
      mode: query
      params:
        tenant_id: tenant.id
select id, name
from items
where tenant_id = /* tenant_id */ 'dummy'
order by id

Because the predicate is hand-written, lint watches for its absence: see TQL-TENANT-3001 below.

Schema per tenant and database per tenant

Section titled “Schema per tenant and database per tenant”

Each tenant gets its own connection pool, declared under tenancy.datasources — one block per tenant id, same keys as any pool (jdbcUrl required, username/password optional and secrets by reference). The two modes share one mechanism and differ only in where the URLs point: for schema-per-tenant, each pool targets a different schema of the same server (for example a currentSchema parameter on a PostgreSQL URL); for database-per-tenant, entirely separate databases:

config/application.yml
tenancy:
  enabled: true
  mode: database-per-tenant
  resolver:
    type: header
    source: X-Tenant-Id
  datasources:
    acme:
      jdbcUrl: jdbc:postgresql://db-acme:5432/app
      username: app
      password: ${secret.env.ACME_DB_PASSWORD}
    globex:
      jdbcUrl: jdbc:postgresql://db-globex:5432/app
      username: app
      password: ${secret.env.GLOBEX_DB_PASSWORD}

In these modes the application tables need no tenant column — isolation is structural. The resolved tenant’s pool replaces main for that request’s SQL; a resolved tenant with no configured pool is rejected with 403 (TQL-TENANT-4031) rather than silently falling back to a shared pool. Tenant routing replaces only main: an explicit non-main datasource: on a route is deployment-shared infrastructure and is never tenant-routed (multi-datasource routes).

main’s role pools come with it. Where main declares a jobPool or a fileTransferPool (deployment), each tenant gets the same role. It is a second pool onto the tenant’s own coordinate, sized by main’s block, or by the tenant block’s own jobPool: / fileTransferPool: when it declares one. A perTenant job runs on its tenant’s jobPool, and a transfer started for a tenant runs on that tenant’s fileTransferPool. Each role pool is one more HikariCP pool per tenant, so declare them with minimumIdle: 0: they hold no connection while idle. shared-schema has no tenant pools, so its tenants use main’s role pools. Tenants cannot be grouped onto one pool (A, B and C on one, D, E and F on another). Each tenant id is its own pool, and a tenant block’s maximumPoolSize / minimumIdle is the lever when there are many.

A file transfer is that request’s SQL too (file transfers): a file-export extracts from the tenant’s pool and a file-import writes its rows there, both refused for an unknown tenant before any transfer row exists. An export’s after: statement runs on the same pool at either timing — the transfer records its tenant so a first download, a later request, resolves it again. It records what that statement announces, too, so the fetch needs no route to read it from (file transfers). The transfer’s own record and its execution verdict stay on main with the other framework tables, so in these modes a run is two connections: the rows commit first, then the verdict. A failure between the two leaves rows that landed under a RUNNING record the reaper closes as abandoned and the log names — never a COMPLETED verdict over rows that did not land. Until 0.18.0 both recipes ran on the main pool in every mode, 202 and COMPLETED, for an unknown tenant too (docs/audit-low-leads.md, G24).

The transfer’s {transferId} subtree — status, file, cancel — is the tenant’s as well as the route’s (edge hygiene E0): a request resolved to another tenant, or to none where the transfer had one, reads the transfer as unknown (TQL-LD-2822). The file leg resolves the tenant like the other two, so required: true holds on the one URL that serves the bytes. Until 0.18.0 the subtree was scoped to app and route only and the file leg carried security alone (G31). A transfer recorded before 0.18.0 has no tenant and, under tenancy, is reachable by no tenant — recorded, not shimmed. The owner queries that list a subject’s own transfers (job-inbox.md) carry the tenant with the same rule: the caller’s tenant, or none on both sides.

The vocabulary is refused, not guessed: an enabled tenancy whose mode is not one of the three above, or whose resolver.type is not header, claim or host, does not boot (TQL-TENANT-4032) and does not lint (TQL-TENANT-3002); so does a per-tenant mode declaring no tenancy.datasources, since it would refuse every tenant. A misspelled mode used to read as “no isolation” — no pools, no resolver, every tenant on the shared pool, and the shared-schema lint switched off with it — in silence (G25).

Tenant resolution

Section titled “Tenant resolution”

Resolution runs once per request, after authentication, and publishes the tenant into the execution context. Two resolver types exist:

  • header (the default) — the tenant id is read from a request header, X-Tenant-Id unless resolver.source names another. Typically a gateway in front of the application sets and trusts this header.
  • claim — the tenant id is read from the authenticated principal, following the dotted path in resolver.source (default tenantId). The principal’s tenant is populated by the authentication mechanism — a JWT tenantClaim, an OIDC claim mapping, or an API key / mTLS client whose tenant is bound to the credential so a caller cannot escalate across tenants (authentication).

With required: true (the default when tenancy is enabled), a request with no resolvable tenant is rejected with 400 (TQL-TENANT-4001) — deny-by-default. The resolved tenant is also recorded on the route’s telemetry span.

Resolution by subdomain is the third resolver: resolver.type: host with resolver.source: "{tenant}.example.com" reads the request’s Host header — the suffix must match exactly, the tenant is the single remaining label, and only slug labels ([a-z0-9-]+) resolve, so a crafted Host header can never smuggle an arbitrary tenant id. Anything else resolves nothing and, when required, rejects with TQL-TENANT-4001.

The tenant in SQL

Section titled “The tenant in SQL”

The resolved tenant joins the 2-way SQL bind sources as tenant: tenant.id is the tenant identifier, and tenant.attributes.<name> exposes any additional tenant metadata. On per-tenant jobs the same tenant.id bind is available even though there is no request.

A source that holds its rows (response-shaping.md) keys them by the connector, the resolved tenant and every bind: in shared schema the tenant.id bind is part of the key, in a per-tenant mode the tenant’s pool is, so one tenant’s hold is never another’s rows. A statement that binds no tenant returns the same rows for every tenant held or not — the case TQL-TENANT-3001 warns about. The per-table version a write raises is per table, not per tenant: a tenant’s invalidates: drops every tenant’s hold of that table, which is over-invalidation and correct.

Lint and errors

Section titled “Lint and errors”
Code Meaning
TQL-TENANT-3001 warning — a shared-schema SQL binding (main, a command steps: entry, a non-main sources: entry) neither binds tenant.* nor mentions a tenant column in its SQL; the statement would leak rows across tenants. Bind tenant.id or filter by a tenant column. The lint read main alone from #753 to 0.18.0.
TQL-TENANT-3002 error — tenancy.mode or tenancy.resolver.type is outside its vocabulary, or a per-tenant mode declares no tenancy.datasources; the runtime refuses the same at boot.
TQL-TENANT-4001 400 — no tenant could be resolved for the request (with required: true).
TQL-TENANT-4031 403 — the resolved tenant has no configured datasource in a per-tenant mode; a file transfer for that tenant is refused the same way, before any transfer row.
TQL-TENANT-4032 boot refusal — an enabled tenancy names a mode or resolver.type outside its vocabulary, or a per-tenant mode with no pools.
TQL-TENANT-5005 the tenant registry query failed.

Data scoping reuses this exact shape — a parameterized predicate at an author-chosen site, backed by a lint — to confine rows within the tenant by the principal’s organizational reach. Tenancy decides whose database or rows a request touches at all; scoping decides which of those rows this particular caller may see.

The tenant directory

Section titled “The tenant directory”

Anything that fans out over all tenants — per-tenant jobs, or listing tenants at all in shared schema — needs a directory of tenant ids. It is resolved in precedence order:

  1. a static tenancy.tenants list in config,
  2. a tenancy.registry.sql query run against the main datasource (first column of each row), bounded by tesseraql.sql.timeoutSeconds like every other declared statement,
  3. the keys of tenancy.datasources.

Shared-schema deployments have no pools to enumerate, so they use the list or the registry query:

tenancy:
  enabled: true
  mode: shared-schema
  registry:
    sql: select tenant_id from tenants order by tenant_id

Per-tenant infrastructure

Section titled “Per-tenant infrastructure”
  • Migrations. The app’s db/migration set runs against main and, in the per-tenant modes, against every configured tenant pool, each with its own history — see application layout. Tenant pools are read from config at startup, so onboarding a tenant in a per-tenant mode is a config change.
  • Jobs. perTenant: true runs a job once per tenant in the directory, each run on that tenant’s own datasource (or the shared one, scoped by the tenant.id bind) as a separate execution record — see jobs.
  • Framework tables. The managed tql_* tables (preferences, shortcuts, inbox, workflow, delegations, route audit) live on the main datasource in every mode and discriminate by a tenant_id column, as do the default identity store’s users and groups. Notification outbox writes from per-tenant job runs likewise land on main, where the dispatcher reads them.

What tenancy is not

Section titled “What tenancy is not”
  • Not per-tenant: named datasources, templates, messages, feature flags, and the rest of the application configuration are deployment-wide. One tenant cannot get different behavior, only different data.
  • Not self-service onboarding: per-tenant pools come from configuration; there is no runtime API to create a tenant’s database.
  • Not authorization: tenancy never decides what a caller may do inside their tenant — that is route policy plus data scoping.
  • Not automatic SQL rewriting: in shared schema the tenant predicate is written by the author and checked by lint, never injected behind the query.

Where to go next

Section titled “Where to go next”

Next

Section titled “Next”