Skip to content

Adding a tenant table

The five required steps, from schema to isolation test.

On this page

A tenant table is any table whose rows belong to one workspace. Every step below is required; skipping one produces a table that looks fine and leaks across tenants. Read Tenancy and row-level security first.

  1. 11. Schemawith organization_id, cascade
  2. 22. Generate the table migration
  3. 33. Hand-write the RLS migration
  4. 44. Query through withTenant
  5. 55. Isolation testblocks merge

The example adds a widgets table.

Step 1: schema

ts
// src/db/schema/widgets.ts
import { pgTable, text, uuid } from 'drizzle-orm/pg-core';
import { primaryKeyColumn, timestamps } from './columns';
import { organizations } from './organizations';

export const widgets = pgTable('widgets', {
  id: primaryKeyColumn(),                       // uuidv7(), minted by Postgres
  organizationId: uuid('organization_id')
    .notNull()
    .references(() => organizations.id, { onDelete: 'cascade' }),
  name: text('name').notNull(),
  ...timestamps,
});

Then register it, in all four places:

  • export it from src/db/schema/index.ts
  • add it to drizzleSchema in src/db/client.ts
  • add the name to TENANT_SCOPED_TABLES
  • add the name to POLICY_PROTECTED_TABLES

The isolation suite reads the last two lists and fails if a table in them has no policy. That is what catches a skipped step 3.

Also add the table to the privacy data inventory (src/privacy/data-inventory.ts): what personal data it holds, how long it lives, and which export carries it. A test fails until you do.

Step 2: generate the table migration

bash
pnpm db:generate --name=add_widgets

Read the generated SQL in full.

Step 3: hand-write the RLS migration

drizzle-kit does not generate policies, so write them yourself, every time:

bash
pnpm db:generate --custom --name=widgets_rls
sql
ALTER TABLE public.widgets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.widgets FORCE ROW LEVEL SECURITY;

CREATE POLICY widgets_tenant_isolation ON public.widgets
  FOR ALL
  USING      (organization_id = public.current_tenant_id())
  WITH CHECK (organization_id = public.current_tenant_id());

GRANT SELECT, INSERT, UPDATE, DELETE ON public.widgets TO ikazebot_app;

Easy to get wrong:

MistakeConsequence
ENABLE without FORCEThe table owner (migrations, scripts) bypasses the policy
USING without WITH CHECKA tenant can write rows stamped with another tenant's id
current_setting(...)::uuid instead of current_tenant_id()A missing tenant becomes a 500 instead of zero rows
No GRANTThe app role cannot touch the table at all

Then run the migrations; see Migrations.

Step 4: query through withTenant

ts
const rows = await this.tenants.withTenant(organizationId, (tx) =>
  tx.select().from(widgets).where(eq(widgets.organizationId, organizationId)),
);

Keep the explicit where. RLS is the safety net, not the filter. Every list endpoint is paginated from its first commit, and every new query pattern gets its index in the same migration.

Step 5: the isolation test

This is non-negotiable and blocks merge. Following src/db/rls.spec.ts:

  1. Seed a row under org A and a row under org B.
  2. From org B's context, assert org A's row is:
    • invisible to select
    • unaffected by update
    • not removed by delete

The suite runs as ikazebot_app against a real Postgres. It refuses any other role, because a run as the superuser would pass whether or not isolation holds. See Testing.

Get started free — 50 credits a month