Isolation strategies

Stratum supports three isolation strategies, configurable per tenant at creation time. You can mix strategies within the same hierarchy.

Strategy comparison

Strategy	Boundary	Shared Pool	Cost	Use Case
SHARED_RLS	Row-Level Security	Yes	Lowest	High tenant count, shared infrastructure
SCHEMA_PER_TENANT	PostgreSQL schema	Yes	Medium	Mid-tier, logical separation
DB_PER_TENANT	Dedicated database	No	Highest	Maximum isolation, compliance

Shared RLS (default)

All tenants share the same tables. PostgreSQL Row-Level Security policies filter rows based on a session variable set per-transaction.

const tenant = await stratum.createTenant({
  name: "Acme Corp",
  slug: "acme_corp",
  isolation_strategy: "SHARED_RLS",
});

For a complete tutorial on setting up RLS with Prisma or Drizzle, see the Prisma + PostgreSQL RLS guide or the Drizzle ORM guide.

How RLS works

Every tenant-scoped table has an RLS policy that compares tenant_id with the app.current_tenant_id setting. The policy reads the setting with NULLIF(current_setting('app.current_tenant_id', true), ''). A connection with no tenant context then matches no rows instead of raising an error. On a pooled connection, the setting reads as '' after the transaction that set it ends.

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

Before executing any query, Stratum sets the tenant context:

BEGIN;
SELECT set_config('app.current_tenant_id', $1, true);  -- parameterized, transaction-local
-- Your queries run here (automatically filtered by RLS)
COMMIT;

The FORCE ROW LEVEL SECURITY flag ensures even table owners cannot bypass policies.

Setting up RLS on your tables

Use the @stratum-hq/db-adapters package or the CLI:

# CLI: scan tables and migrate
npx @stratum-hq/cli migrate --scan     # show RLS status
npx @stratum-hq/cli migrate orders     # add RLS to "orders" table

Or programmatically:

import { migrateTable } from "@stratum-hq/db-adapters";

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await migrateTable(client, "orders");  // adds tenant_id + RLS + policy
  await client.query("COMMIT");
} finally {
  client.release();
}

Querying with RLS

Use the database adapter to automatically set the tenant context:

import { createTenantPool } from "@stratum-hq/db-adapters";
import { getTenantContext } from "@stratum-hq/sdk";

const tenantPool = createTenantPool(pool, () => getTenantContext().tenant_id);

// This query is automatically filtered to the current tenant
const orders = await tenantPool.query("SELECT * FROM orders");

Subtree read scope

By default, a tenant context reads the rows of one tenant only. A parent can opt in to also read the rows of all its descendants. This is the subtree read scope. Use it when a parent must see a roll-up of its tree, for example an MSSP that monitors its MSPs and their clients.

Take this tree:

Acme MSSP
├── Northwind MSP
│   ├── Client A
│   └── Client B
└── Contoso MSP
    └── Client C

Pass { scope: "subtree" } to withTenantContext:

import { withTenantContext } from "@stratum-hq/db-adapters";

// Northwind MSP reads its own alerts and the alerts of Client A and Client B.
const alerts = await withTenantContext(
  pool,
  northwindId,
  (client) => client.query("SELECT * FROM alerts ORDER BY created_at DESC"),
  { scope: "subtree" },
);

In this scope, Northwind MSP reads its own rows and the rows of Client A and Client B. It does not read the rows of Acme MSSP, Contoso MSP or Client C. Client A in the subtree scope reads only its own rows, because it has no descendants. Acme MSSP in the subtree scope reads the rows of the whole tree.

The subtree scope has these rules:

• Reads only. Inserts, updates and deletes stay limited to the exact tenant. An insert for a descendant fails with a row-level security error. An update or a delete does not change a descendant’s rows. SELECT ... FOR UPDATE and FOR SHARE return the exact tenant’s rows only.
• All statuses. The subtree includes suspended, archived and pending descendants. To show active descendants only, filter by the tenant status in your query.
• Opt-in per transaction. The default scope is "exact". The scope ends with the transaction, so it cannot carry over to the next request on a pooled connection.
• The bypass does not change. A session with the RLS bypass reads every row, whatever the scope.
• The tree is the ID-based ancestry path. A move through moveTenant changes the subtree at once. A slug rename does not change it. Migration 031 also refuses a change to parent_id, ancestry_path, depth or ancestry_ltree unless the session has the RLS bypass, so a tenant context cannot change its own place in the tree. The library’s tree operations run with the bypass.

Migration 031 of @stratum-hq/lib adds the subtree policy to these Stratum tables, and to no others:

• config_entries, for rows that are not sensitive only
• permission_policies, abac_policies, roles and principal_roles
• audit_logs, usage_events and consent_records
• webhook_events and webhook_deliveries
• tenants, so a parent also reads the registry rows of its descendants

Credential-bearing rows stay exact-tenant in every scope: api_keys rows, webhooks rows and config_entries rows with sensitive = true. An API key row holds a key hash, a webhook row holds a signing secret hash and a URL that can carry credentials, and a sensitive config row holds a secret value. Only the exact tenant reads them. webhook_deliveries is scoped through webhook_events, so a parent reads a descendant’s delivery rows but not the webhook they belong to.

For your own tables, ask createPolicy for the subtree policy:

import { createPolicy, enableRLS } from "@stratum-hq/db-adapters";

await enableRLS(client, "alerts");
await createPolicy(client, "alerts", { subtreeRead: true });

This adds a second policy, for SELECT only, next to tenant_isolation:

CREATE POLICY tenant_subtree_read ON alerts FOR SELECT
  USING (
    current_setting('app.tenant_scope', true) = 'subtree'
    AND tenant_id = ANY ((SELECT stratum_subtree_tenant_ids())::uuid[])
  );

stratum_subtree_tenant_ids() returns the current tenant and all its descendants. PostgreSQL runs it once for each reference to the policy in a statement, so a query that joins several tables with the policy runs it once per table. Its cost grows with the size of the subtree, and each table matches tenant_id against the result, so the table needs an index on tenant_id. Migration 031 creates the function, so run the Stratum migrations first. stratum doctor and stratum migrate --scan count a table with this policy as isolated.

To run a background job in the subtree scope, pass the same option to runScopedJob:

import { runScopedJob } from "@stratum-hq/lib";

await runScopedJob(jobPool, northwindId, summarizeAlerts, { scope: "subtree" });

Schema-per-Tenant

Each tenant gets a dedicated PostgreSQL schema with its own set of tables. Tenants share the database but have logically separate namespaces.

const tenant = await stratum.createTenant({
  name: "Premium Client",
  slug: "premium_client",
  isolation_strategy: "SCHEMA_PER_TENANT",
});
// The tenant is created "pending". createTenant does not create the schema.

When the control plane creates a SCHEMA_PER_TENANT tenant, it provisions a dedicated schema named tenant_{slug} (here tenant_premium_client) with the Stratum base tables, and activates the tenant. With the library alone, create the schema yourself, for example with createSchema from @stratum-hq/db-adapters, then call stratum.activateTenant(tenant.id). See Tenants with their own storage.

SchemaRawAdapter, createSchemaTenantPool and setSchemaSearchPath from @stratum-hq/db-adapters route queries. Each sets search_path to the tenant schema alone, inside a transaction. public is not on the path. An unqualified table that is missing from the tenant schema raises an error. It does not read a table that all tenants share.

To add a schema after the tenant schema, pass extraSearchPath, for example a schema that holds extension functions. List only schemas that hold no tenant data:

import { SchemaRawAdapter } from "@stratum-hq/db-adapters";

const adapter = new SchemaRawAdapter(pool, { extraSearchPath: ["extensions"] });

Advantages

• Stronger logical separation than RLS
• Schema-level GRANT/REVOKE for fine-grained access control
• Per-schema pg_dump for tenant-specific backups
• Shared connection pool (unlike DB-per-tenant)

Considerations

• Schema count grows with tenant count (PostgreSQL handles thousands of schemas well)
• DDL changes must be applied to all tenant schemas

Database-per-Tenant

Each tenant gets a completely separate PostgreSQL database with its own connection pool.

const tenant = await stratum.createTenant({
  name: "Regulated Client",
  slug: "regulated_client",
  isolation_strategy: "DB_PER_TENANT",
});
// The tenant is created "pending". createTenant does not create the database.

The control plane provisions a database named stratum_tenant_{slug} (here stratum_tenant_regulated_client) and activates the tenant. With the library alone, create the database yourself, for example with createDatabase from @stratum-hq/db-adapters, then call stratum.activateTenant(tenant.id).

Advantages

• Maximum isolation: separate process space, WAL, backups
• Satisfies compliance requirements for physical data separation
• Independent pg_dump, pg_restore, and point-in-time recovery
• No shared-resource contention between tenants

Considerations

• Each database requires its own connection pool
• Higher resource usage (memory, connections)
• Cross-tenant queries not possible without external coordination

Tip

Use DB_PER_TENANT for tenants with strict regulatory requirements (financial services, healthcare) where you need to prove physical data separation.

Mixing strategies

You can mix isolation strategies within the same hierarchy:

// Root uses shared RLS (cost-effective for management)
const root = await stratum.createTenant({
  name: "Platform",
  slug: "platform",
  isolation_strategy: "SHARED_RLS",
});

// Standard MSPs use schema isolation
const standardMsp = await stratum.createTenant({
  name: "Standard MSP",
  slug: "standard_msp",
  parent_id: root.id,
  isolation_strategy: "SCHEMA_PER_TENANT",
});

// Regulated MSP gets its own database
const regulatedMsp = await stratum.createTenant({
  name: "Regulated MSP",
  slug: "regulated_msp",
  parent_id: root.id,
  isolation_strategy: "DB_PER_TENANT",
});

Note

The isolation strategy is set at tenant creation time and applies to that tenant’s data. Child tenants inherit the parent’s strategy by default but can specify a different one.

Security properties

Property	RLS	Schema	Database
Row-level filtering	Yes	Yes	N/A
Schema-level isolation	No	Yes	Yes
Connection-level isolation	No	No	Yes
Independent backups	No	Partial	Yes
Independent scaling	No	No	Yes
Cross-tenant query risk	Policy-dependent	Schema-dependent	None