Tenant Isolation

Row-Level Security, AsyncLocalStorage, and ltree hierarchy — how BrainstormRouter isolates tenant data.

Three layers of isolation

BrainstormRouter uses three complementary mechanisms for tenant isolation. None of them alone is sufficient — the combination provides defense in depth.

Layer 1: Row-Level Security (Postgres)

Tenant-scoped tables enforce RLS at the database level. From src/db/schema/tenants.ts:76-92:

const tenantRlsUsing = sql`tenant_id = current_setting('app.current_tenant')::uuid`;

export const tenantProviderKeys = pgTable(
  "tenant_provider_keys",
  {
    /* columns */
  },
  (table) => [
    pgPolicy("tpk_select", {
      for: "select",
      using: tenantRlsUsing,
    }),
    pgPolicy("tpk_insert", {
      for: "insert",
      withCheck: tenantRlsUsing,
    }),
    pgPolicy("tpk_update", {
      for: "update",
      using: tenantRlsUsing,
      withCheck: tenantRlsUsing,
    }),
    pgPolicy("tpk_delete", {
      for: "delete",
      using: tenantRlsUsing,
    }),
  ],
);

Every SELECT, INSERT, UPDATE, and DELETE on a tenant-scoped table is filtered by current_setting('app.current_tenant'). This is a Postgres session variable set by the application before each query. Even if application code has a bug that omits a WHERE tenant_id = ? clause, the database enforces isolation.

The same RLS pattern applies to:

• tenant_provider_keys — encrypted BYOK API keys
• organizations — org structure within a tenant
• users — user accounts within a tenant

Layer 2: AsyncLocalStorage (Node.js)

From src/db/tenant-als.ts:

import { AsyncLocalStorage } from "node:async_hooks";

export type TenantContext = {
  tenantId: string;
  roles?: RbacRole[];
  actorId?: string;
};

export const tenantAls = new AsyncLocalStorage<TenantContext>();

export function getCurrentTenantId(): string | undefined {
  return tenantAls.getStore()?.tenantId;
}

Auth middleware sets the tenant context at request ingress. Every downstream function — database queries, memory operations, billing — reads from tenantAls without explicit parameter threading. This prevents the most common multi-tenancy bug: forgetting to pass tenantId through a deep call chain.

Layer 3: ltree hierarchy (MSP sub-tenants)

For managed service providers, tenants can have sub-tenants in a tree structure:

// From tenants table schema
parentTenantId: uuid("parent_tenant_id"),
tenantPath: ltree("tenant_path"),

The tenant_path column uses Postgres's ltree extension for O(1) hierarchical queries. A path like root.parent_uuid.child_uuid (hyphens converted to underscores) enables:

• Subtree queries: WHERE tenant_path <@ 'root.parent_uuid' — find all sub-tenants
• Ancestor queries: WHERE 'root.parent_uuid.child_uuid' <@ tenant_path — find all parents
• Depth queries: WHERE nlevel(tenant_path) = 2 — find direct children

System tables: intentional RLS exceptions

Two tables skip RLS by design:

API Keys (api_keys)

API keys are a system authentication table. The auth middleware needs to look up any API key to determine which tenant it belongs to. RLS would create a chicken-and-egg problem: you need the tenant ID to query with RLS, but you need to query to find the tenant ID.

Memory Extraction Queue (memory_extraction_queue)

From the schema comment in src/db/schema/memory-extraction.ts:4-10:

> No RLS — this is a system/infrastructure queue table (like api_keys). > The worker must claim jobs cross-tenant; RLS would block withSystemContext > since it clears app.current_tenant to ''. > > Tenant isolation is enforced at the application layer: enqueue is scoped by > the authenticated tenantId, and the worker processes each job in its own > tenant context for RMM writes.

The extraction worker processes jobs for all tenants. It claims a batch of pending jobs (any tenant), then runs each job within that job's tenant context. Application-layer isolation for the queue; database-layer isolation for the memory writes.

Encrypted provider keys

Tenant provider keys (BYOK) use a KEK/DEK encryption model:

// From tenantProviderKeys schema
apiKeyEncrypted: customType<{ data: Buffer }>({
  dataType() { return "bytea"; },
})("api_key_encrypted").notNull(),
keyEncryptionKeyId: text("key_encryption_key_id"),

Keys are encrypted at rest with a data encryption key (DEK), which is itself encrypted by a key encryption key (KEK) stored in AWS Secrets Manager. Even if the database is compromised, API keys are unreadable without the KEK.