Multi-tenancy & hard per-tenant isolation

Goooy is multi-tenant. By default tenancy is logical: every organization shares one PostgreSQL database, isolated by orgId row-scoping + RBAC (see architecture §7). This document covers the opt-in hard-isolation mode, where each tenant’s data lives in its own PostgreSQL schema or database.

Status: Isolation is end-to-end. The REST request path, every background worker, every native-client protocol + public/token route, inbound mail delivery, and a cross-tenant platform-admin surface are all tenant-aware; release-upgrade and per-org data-backfill tooling exist. The default (shared) mode is unchanged and byte-identical, since every isolation code path hard-short-circuits to the singleton, and remains the recommended deployment unless you need physical isolation. See Coverage for the per-subsystem map and the few remaining caveats.

Modes (TENANT_ISOLATION)

Related config (all have safe defaults): CONTROL_DATABASE_URL (where the catalog lives; defaults to DATABASE_URL), TENANT_CONN_LIMIT (per-tenant pool size, default 3), TENANT_CLIENT_CACHE_MAX (max cached tenant clients, default 50).

How it works

                         ┌─────────────────────────────┐
  request ──auth──▶ req.user.orgId ─▶ control catalog ──▶ Tenant{schemaName|databaseUrl}
                         └─────────────────────────────┘            │
                                                                     ▼
                         req.db = PrismaClient(  ?schema=t_<id>  | goooy_<id>  )

• Control plane (catalog). Three models in the main Prisma schema, queried through a dedicated controlDb client (src/db.ts) pointed at CONTROL_DATABASE_URL:
  • Tenant: id (== the tenant’s Organization.id), isolationMode, schemaName or databaseUrl, status (PROVISIONING|READY|SUSPENDED|FAILED).
  • TenantDomain: domain → tenantId, the routing index for login/register/SSO, inbound mail and the mail-stack lookups (which happen before any JWT exists, so they resolve the tenant by email domain). Signup writes the first one; every domain an administrator adds later is registered too (and released when it is deleted), and a name another tenant holds is refused (“Domain already exists”).
  • TenantAddress: address → tenantId for an address on a domain no tenant hosts: an isolated tenant’s external guests, registered when they accept an invitation, so they can sign in again by password.
  • PlatformUser: cross-tenant administrators (they have no home org), see Platform administration.
• Per-request client (req.db). src/plugins/tenancy.ts decorates every request with req.db: DB. After auth it resolves: authenticated user → clientForOrgId(orgId); SYSADMIN → the control/shared client (never a single-tenant schema); shared mode → the singleton. Every REST handler uses req.db instead of importing the singleton.
• Client cache (src/lib/tenancy/clientCache.ts). An LRU of per-tenant PrismaClients keyed by tenant id; LRU eviction disconnects the pool (after a grace delay so in-flight queries finish). Drained on graceful shutdown. The background schedulers do not use it: each tick borrows the cached client of a busy tenant or opens a short-lived one that is closed after that tenant’s sweep (withTenantClient), so a tick over more tenants than TENANT_CLIENT_CACHE_MAX no longer churns the cache and piles up pools.
• Provisioning (src/lib/tenancy/provisioner.ts). /auth/signup generates the tenant id control-plane-first, writes the catalog row (PROVISIONING), creates the schema/database (DDL on a privileged connection, outside any transaction), applies the full schema via prisma migrate deploy (serialized by an in-process mutex to avoid advisory-lock contention), then creates the Organization/Domain/admin and the AI role catalog inside the new home, and only then marks the tenant READY. Any failure rolls the whole tenant back. The DDL runs on the data server (DATABASE_URL, where the tenant lives), never on a separate control database. A tenant left PROVISIONING by a process that died mid-way (a deploy, an OOM kill) no longer holds its domain for ever: after 30 minutes the next signup for that domain rolls it back.
• Tenant-prefixed opaque tokens. Refresh tokens and password-reset tokens are minted as <tenantId>.<random> in isolation mode, so /auth/refresh, /auth/logout and /auth/password/reset (which carry no JWT) can route to the right tenant DB. Unprefixed legacy tokens resolve to the shared client.

Operational requirements (isolation modes)

• DB privileges. The runtime role needs CREATE on the database for schema mode, or CREATEDB for database mode. (The default shared mode needs neither.) The backfill CLI additionally needs to SET session_replication_role (superuser), which disables FK triggers during the bulk copy; provisioning + upgrades do not.
• Migrations. New tenants are migrated at provision time with the committed migrations. The API image’s start command runs prisma migrate deploy (the base schema) and then node dist/jobs/migrateTenants.js, which brings every READY and SUSPENDED tenant to the release before the API serves (a suspended tenant comes back on resume and must not come back on an old schema). A tenant with nothing pending costs one query, so an ordinary restart is not slowed; a tenant whose database is the base one (a hand-registered tenant-zero) was migrated by the base deploy. With a separate CONTROL_DATABASE_URL the job migrates that catalog database first. It also reconciles the catalog with each tenant: its domains into TenantDomain, its guests into TenantAddress, the AI role catalog when the tenant has none or was just migrated. It exits non-zero if a tenant fails to migrate, which stops the pod from starting on a mismatched schema. From a checkout: npm run db:migrate-tenants --workspace @goooy/api. With many tenants and a release that adds a migration, the first start can take a few seconds per tenant: give the API container a startup probe that allows for it.
• Connections. Keep TENANT_CLIENT_CACHE_MAX × TENANT_CONN_LIMIT well under PostgreSQL max_connections. For database mode at scale, front the tenant databases with PgBouncer (transaction pooling; ?pgbouncer=true) and run provisioning DDL/migrations on a direct (non-pooled) connection.
• Mail stack. With the integrated mail stack (Postfix/Dovecot), run it with mail.lookups: api (the chart’s default; it refuses sql under isolation). The SQL maps and passdb read one database, the control one, where an isolated tenant’s domains and people do not exist: its mail would be refused at RCPT and its native logins fail. With api the app answers Postfix’s maps (socketmap) and Dovecot’s password checks from the tenant’s own schema or database, resolved by domain through TenantDomain (mail-server.md). Every domain of a tenant is in that catalog (see TenantDomain above). Mail for a tenant that is suspended or still being set up is deferred, never routed to the base database (where it was accepted and lost, or bounced): the app’s SMTP/LMTP ingest answers 451 at RCPT and the socketmap answers TEMP, so the sender retries.
• Platform admin. Create a cross-tenant administrator with npm run platform:admin --workspace @goooy/api -- <email> <password> (writes a PlatformUser into the control DB). It signs in at the normal POST /api/v1/auth/login (its domain isn’t a hosted tenant, so login falls through to the platform path) and then drives /api/v1/platform/* (see below).

Coverage (what is tenant-aware)

Every subsystem that touches tenant data now resolves the caller’s per-tenant client, and each collapses to the singleton in shared mode.

• REST request path. req.db from req.user.orgId (src/plugins/tenancy.ts).
• Background workers. Every scheduler sweeps the base client + one target per READY tenant, each under its own Redis tick-lock, through one shared helper, sweepTenants (src/lib/tenancy/sweep.ts, generalized from the AI-agent scheduler). Covers reminder-push, imap-fetch, scheduled-send, snooze, jmap-tombstone-prune, retention, esign-reminders, migration, and the imap-sync outbox + daily reconcile. In shared mode each is a single runWithTickLock against the singleton, byte-identical to the original global tick. (jmap-upload-gc is S3-only and stays global, because the bucket is shared between tenants.) A database-mode tenant whose databaseUrl aliases the base DB (a hand-registered “tenant-zero”) is swept only by the shared target, never also as its own, because a scheduler that acts-then-deletes (scheduled-send/esign) could double-fire across replicas. The imap-sync outbox also mirrors the ImapSyncState enable gate into each tenant schema per tick, so a tenant provisioned after boot starts enqueuing.
• Inbound mail (SMTP/LMTP). lib/smtp-ingest.ts groups each message’s recipients by the tenant that hosts their domain (resolveTenantByDomain) and delivers each group into that tenant’s DB; unhosted/bootstrap domains fall back to the base client. The Postfix loopback that backs intra-server delivery therefore files each copy into the recipient’s tenant.
• Native-client protocols + public/token routes. All resolve the tenant from their authenticated principal:
  • DAV / EAS / MAPI / EWS / OAB / FSSHTTP / office+files WebDAV authenticate through lib/dav-auth.ts, which now looks the user up in the right tenant DB (by the OFBA/URL token’s embedded orgId, or the Basic login email’s domain). Each handler binds that DB for the rest of the request via an AsyncLocalStorage-scoped client (scopedDb / useTenantDb in src/db.ts), so the many helpers that read the module prisma route to the caller’s tenant with no per-helper plumbing. The binding is always at the handler’s own top level (a binding set in a nested helper or a Fastify hook does not propagate to the handler (verified) and an unbound read falls back to the singleton, never another tenant).
  • JMAP resolves its contextFor(user) DB from the user’s orgId (Bearer JWT or Basic).
  • SCIM bearer tokens are minted scim_<orgId>_<random> so the static per-org token routes to its tenant; the handler binds req.scimDb.
  • OFBA sign-in resolves the tenant by the login email’s domain, like /auth/login.
  • Public share links and incoming chat webhooks mint <tenantId>.<random> tokens (like refresh/reset tokens) and route via clientForPrefixedToken.
  • The MCP agent surface token is org-prefixed; a forged prefix just selects a tenant where the agent isn’t found (404), never another tenant’s data.
• WebSocket gateways (chat / meet / office). Each resolves the tenant DB once per connection from the ?token= JWT’s orgId and threads it explicitly through the connection’s handlers (ALS can’t span a long-lived socket’s later message callbacks). Chat presence and custom status go to the online people of the person’s own organization only (the online set in Redis is deployment-wide; it used to reach every organization and every tenant).
• Safe-links clicks (/mail/safelinks/:token, no JWT) read the policy and write the click log in the recipient organization’s own database, named by the signed token.
• External guests sign in through TenantAddress (above); the login, password-reset and SSO-start routes look an address up there when its domain is no tenant’s.
• Create-defaults (Admin ▸ Defaults, saved by a SYSADMIN) are read from the control database, so they apply to new users and domains in every tenant.
• Open-format export (dist/jobs/exportOrg.js, the chart’s CronJob) exports every READY or SUSPENDED tenant from its own database, plus the base database’s organizations.
• Platform admin. See below.

Partner organizations

Several organizations on one deployment can let each other in on two things: the address book (their people appear in the other side’s directory, with the organization’s name) and free/busy (their busy blocks show in the other side’s scheduling assistant and time suggestions; never titles or details). The typical case is a gemeenschappelijke regeling (GR): a few small gemeenten sharing one IT organization and one Goooy.

• Grants are one-way and per kind. An organization’s admin (Admin → Partner organizations) names a partner by one of its mail domains and chooses what to share with it. The partner’s admin decides the other direction. Changes are in the audit trail (partner.grant, partner.revoke).
• Stored in the control catalog (OrgPartnership, next to Tenant), because in schema and database mode the two organizations live in different schemas or databases. A partner’s data is read through that partner’s own client (lib/partners.ts), so the same grants work in every mode.
• Free/busy stops at the organization otherwise. /calendar/freebusy, /calendar/suggest, EWS GetUserAvailability and ActiveSync ResolveRecipients answer for the caller’s own organization and for organizations that share free/busy with it; everyone else is “no information”. (Before this, /calendar/freebusy and /suggest answered for any address on the server in shared mode.)
• Where partners appear: the web address book, recipient suggestions and people pickers (/contacts/gal). Not yet in the native clients’ directories (the Outlook offline address book, EWS ResolveNames, ActiveSync GAL search, CardDAV), and chat stays within the organization.

A shared-services setup (GR)

1. TENANT_ISOLATION=schema (or database) so each gemeente is its own tenant, with its own domains, admins, retention, DLP and archive.
2. The GR’s IT department runs the platform: platform admins provision the tenants (POST /api/v1/platform/tenants) and can suspend one; they never see mail.
3. Each gemeente’s admin grants the partner gemeenten the address book and free/busy.
4. Backups, the mail stack (with mail.lookups: api, see Operational requirements) and object storage stay shared infrastructure; tell each gemeente so in its processing register (Admin → Privacy). The backup CronJob dumps every tenant database in database mode; see what a backup contains.

Platform administration (/api/v1/platform)

A cross-tenant PlatformUser (no home org, lives in the control DB) signs in at the normal /auth/login. Its email domain isn’t a hosted tenant, so login falls through to a platform path and returns a bounded-lifetime platform token (PLATFORM_SESSION_TTL, no refresh rotation; a normal tenant user or a tenant SYSADMIN can never obtain one). The token is accepted only on /api/v1/platform/* (guarded by requirePlatform; every other route refuses it with 403) and is bound to the control DB. Each platform request re-reads the PlatformUser: switching it off (active = false) or re-running the platform:admin CLI for it (which rotates the password) ends the tokens it already had. Endpoints: GET /tenants (list), GET /tenants/:id (catalog + a best-effort live per-tenant summary fanned out into that tenant’s DB), POST /tenants (provision a new tenant: schema/db + Organization + first ORGADMIN, the same path /auth/signup drives), and PATCH /tenants/:id/status (suspend/resume). A suspend flips the tenant to SUSPENDED (a new TenantStatus), clientForOrgId then refuses its requests with 503, and it publishes a Redis eviction so the change takes effect on every replica’s client cache immediately, not just on LRU turnover.

Migration tooling

• Release upgrade: the image runs dist/jobs/migrateTenants.js at start; npm run db:migrate-tenants is the same from a checkout (see Operational requirements).
• Backfill: npm run backfill:tenant -- <orgId> [--commit] migrates one existing shared-mode org into its own schema (schema mode only): t_<orgId>, with any character other than [a-z0-9_] turned into _ (the seeded bootstrap-org becomes t_bootstrap_org). It registers the tenant, creates + migrates the schema, then copies the org’s owned rows in FK-dependency order in one transaction (each table filtered by a direct orgId column or a foreign key into an already-copied owned parent; tables, columns, enum types and foreign keys come from the database catalog, since the Prisma 7 runtime DMMF carries neither enums nor relation columns; enum columns are cast through text into the tenant schema’s own types). The AI role templates are copied whole and the org’s DKIM records with them; LegalHoldItem and LdapConfig (owner by plain string) are handled by name. It verifies per-table counts and that every foreign key in the tenant resolves (a reference to another organization is cleared, or the row left out, and reported), registers the org’s guest addresses and marks the tenant READY. It never mutates the source: sign-in and mail go to the tenant from then on, but the base schedulers still see the old rows (scheduled sends, flows, reminders would run twice), so reclaim them promptly once the tenant is verified. It runs as a dry-run plan unless --commit. database-mode backfill (cross-DB copy) is not automated.

Remaining caveats

• Provisioning is synchronous within /auth/signup (and POST /platform/tenants) and shells out to the Prisma CLI. A failed provision rolls back fully (drops the partial schema/db + frees the domain) so a retry can reuse it. High-volume signup should move to an async job.
• No cross-tenant admin dashboards. The existing per-org admin surface works per tenant, and the platform surface manages the catalog + per-tenant summaries, but there is no aggregated cross-tenant reporting (stats/usage) fanned out across every schema.
• Backfill skip list + database mode: see Migration tooling.

Verification

docker compose up -d postgres redis
npm run db:deploy --workspace @goooy/api      # control catalog + base schema
node apps/api/test/smoke-tenancy.mjs          # boots its own schema-mode API, 64 asserts
TENANCY_SMOKE_MODE=database node apps/api/test/smoke-tenancy.mjs   # the same in database mode
TENANCY_IT_ADMIN_URL=postgresql://goooy:goooy@localhost:5432/postgres \
  node --import tsx --test apps/api/test/tenancy-r8.integration.ts  # backfill, scheduler pools, split control DB

The suite provisions two tenants, proves each gets its own t_<id> schema, that by-email login routes to the right tenant, that neither tenant sees the other’s data, and that tenant-prefixed refresh tokens round-trip. It then exercises the platform surface: a PlatformUser (created via the platform:admin CLI) logs in, lists all tenants, provisions a new one, and suspends/resumes a tenant (proving a suspended tenant’s live session is blocked with 503 via cross-request cache eviction, then restored). Tenant users and platform tokens are each refused from the other’s surface. Section 8 covers chat presence, safe links, domains added after signup, mail for a suspended tenant, guest sign-in, the AI role catalog, create-defaults, a crashed provisioning, the open-format export, the tenant migration job and platform-token revocation. It is wired into run-all.mjs behind RUN_TENANCY_SMOKE=1 (the default gate boots the shared-mode API only). Shared-mode behavior is covered by the full smoke suite, which passes unchanged.