Multi Tenancy and Isolation

Multi-tenancy and isolation

This page explains how one openrunic deployment keeps several organisations' data apart, which layer enforces what, and which layer is not built yet. It is for anyone writing a repository, a query, or a migration.

The tenant key

The tenant key is tenantId, and only tenantId. It is a String @db.Uuid on every model except Organisation itself, where the row's own id is the tenant id. Every one of the 46 tenant-scoped models declares a real foreign key back to Organisation with onDelete: Cascade, so purging a tenant is a single delete.

facilityId is not a tenancy key. It is a business scoping column that the policy layer uses to decide whether a user may act at a given place of service. Confusing the two is the mistake this page exists to prevent.

Tenant-prefixed constraints

The convention stated at the top of packages/database/prisma/schema.prisma is that every composite index and every unique constraint is tenant-prefixed. In practice:

@@unique([tenantId, mrn])                    // Patient
@@unique([tenantId, email])                  // User
@@unique([tenantId, system, value])          // PatientIdentifier
@@unique([tenantId, accessionNumber])        // Specimen
@@unique([tenantId, key, version])           // FormDefinition
@@unique([tenantId, system, code, version])  // TerminologyCode
@@unique([tenantId, sourceEventId, type])    // Task
@@unique([tenantId, seq])                    // AuditEvent
@@unique([tenantId, hash])                   // AuditEvent

Without the prefix, two tenants could not both have a patient with MRN OR-100482, and a uniqueness violation in one organisation would surface as a bug in another.

There are deliberate exceptions. Child-scoped uniques reach the tenant transitively through their parent, so they are not prefixed: [userId, facilityId] on UserFacility, [roleId, permissionId] on RolePermission, [claimId, sequence] on ClaimLine, [remittanceId, sequence] on RemittanceLine, [diagnosticReportId, sequence] on ResultObservation, and [formSubmissionId, fieldKey, repeatIndex] on FormPromotedValue.

Two uniques are global on purpose: Organisation.slug, which addresses the tenant, and Statement.payLinkToken, which is a bearer token in a URL and must be unique across the whole deployment.

Three layers

flowchart TD
    A[Layer 1: application scoping<br/>tenant-scoped Prisma client extension] --> B[Layer 2: Postgres row-level security<br/>designed, not yet enabled]
    B --> C[Layer 3: generated cross-tenant tests<br/>every repository path]
    style B stroke-dasharray: 5 5

Loading

Layer 1: the tenant-scoped client

packages/database/src/tenant.ts exports createTenantClient(client, { tenantId }), which returns a Prisma client extension named openrunic-tenant-scope applied to all operations on all models. It does four things.

It hard-codes the list of tenant-scoped models rather than inferring it, so a new model does not silently opt out of scoping. The list is asserted against the schema by an integration test in the API package.

It ANDs the tenant predicate rather than merging it:

// withTenantWhere
return { AND: [where, { tenantId }] };

Merging would let a caller widen the result set with an OR clause. Wrapping cannot.

It applies the tenant stamp last when writing, so data naming a different tenant is corrected rather than honoured.

It throws on the raw escape hatches: $queryRaw, $queryRawUnsafe, $executeRaw, and $executeRawUnsafe. There is no string-SQL path through a tenant-scoped client.

The root createPrismaClient is still exported, because migrations, the seed, and CLI tooling legitimately need an unscoped client. An ESLint boundary rule keeps it out of apps/api.

tenant.ts is explicit about its own limits:

This is not the last line of defence. Treat this as the thing that makes correct code easy, not as the thing that makes incorrect code safe.

Layer 2: Postgres row-level security

Not enabled today. No migration contains ENABLE ROW LEVEL SECURITY, CREATE POLICY, or current_setting. The intended policy is written out in the schema header so that the design is reviewable before it ships:

ALTER TABLE "Patient" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Patient" FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON "Patient"
  USING      ("tenantId" = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK ("tenantId" = current_setting('app.tenant_id', true)::uuid);

The reason it is deferred is ordering: enabling row-level security before the API wraps each request in a transaction that runs SET LOCAL app.tenant_id would lock the application out of its own database.

Four design notes are already settled and recorded:

• current_setting(..., true) passes missing_ok, so an unset session returns NULL and therefore sees zero rows, rather than erroring. Failing closed and quietly is the correct behaviour for a session that forgot to identify itself.
• FORCE is required, because the table owner would otherwise bypass its own policy.
• SET LOCAL is transaction-scoped, so a pooled connection cannot leak a tenant id into the next checkout.
• Migrations and the seed run as a separate role granted BYPASSRLS.

Layer 3: cross-tenant tests

A generated suite in apps/api walks the Prisma DMMF and attempts a cross-tenant read and a cross-tenant write through every repository path. Each must be denied, and each denial must produce an audit record. A denial that leaves no trace is treated as a failure, because an isolation breach nobody can see afterwards is not meaningfully prevented.

How this reaches the API

The chain is described in full on API design. The parts that matter for isolation:

tenantScope() sets the tenant id from the authenticated principal and from nothing else. If a request carries an x-openrunic-tenant header that disagrees with the principal, the request is rejected with 403. There is no silent fallback, and a header can never widen access.

The audit middleware then binds the repository registry to that tenant with registry.forRequest({ tenantId, audit }). The repository interfaces in apps/api/src/repositories/types.ts deliberately take no tenant parameter. A handler cannot name another organisation, because there is no argument through which to name one.

Two Prisma details follow from the scoping extension and are documented in apps/api/src/repositories/db-port.ts:

• Reads use findFirst, never findUnique.
• Writes use updateMany, never update.

Both are required because the extension rewrites where into AND: [original, { tenantId }], and that compound predicate is not a legal filter for the unique-argument variants.

Cross-tenant misses are 404, not 403

Reading a record that exists in another organisation returns 404 rather than 403. A 403 would confirm that the id exists somewhere, which is an information leak across a tenant boundary. The route tests assert this specifically.

Rules for contributors

• Never add a tenant id parameter to a repository method. If a handler needs one, the scoping layer has been bypassed.
• Never call a raw query method. The tenant client throws on all four, and that is intentional.
• Tenant-prefix every new composite index and unique constraint, unless the constraint is child-scoped and reaches the tenant through its parent. Say which in a comment.
• When adding a model, add it to the tenant-scoped model list. The integration test will fail if you forget, which is the point.
• When writing a route test, include the cross-tenant case. Use the two synthetic demo tenants that the API test fixtures already provide.

Related pages

• Data model
• Security model
• API design
• Audit and hash chain