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.
- 11. Schemawith organization_id, cascade
- 22. Generate the table migration
- 33. Hand-write the RLS migration
- 44. Query through withTenant
- 55. Isolation testblocks merge
The example adds a widgets table.
Step 1: schema
// 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
drizzleSchemainsrc/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
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:
pnpm db:generate --custom --name=widgets_rls
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:
| Mistake | Consequence |
|---|---|
ENABLE without FORCE | The table owner (migrations, scripts) bypasses the policy |
USING without WITH CHECK | A 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 GRANT | The app role cannot touch the table at all |
Then run the migrations; see Migrations.
Step 4: query through withTenant
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:
- Seed a row under org A and a row under org B.
- From org B's context, assert org A's row is:
- invisible to
select - unaffected by
update - not removed by
delete
- invisible to
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.