Multi-tenant database design on Postgres with Drizzle ORM

Three multi-tenancy models

There are three standard approaches to tenant data isolation. Each trades isolation strength against operational complexity.

1. Database per tenant

Each tenant gets a completely separate database — separate connection string, separate backups, separate migration run. This is the strongest isolation: there is literally no shared state between tenants.

• When to choose: strict data-sovereignty or compliance requirements that mandate physical separation, or a small number of very large enterprise tenants who contract for dedicated infrastructure.
• Cost: highest. Every tenant adds connection overhead, migration and backup complexity, and operational monitoring burden.
• In the starter kit: not implemented. The kit uses the shared-database model for simplicity and zero-extra-cost operation.

2. Schema per tenant

All tenants share one database instance, but each has its own Postgres schema (namespace). Tables are duplicated per schema, so migration is per-tenant rather than global.

• When to choose: moderate isolation with a manageable tenant count (tens, not thousands), where per-tenant schema customization is a real requirement.
• Cost: moderate. Connection routing must resolve the schema name, migrations run per-schema, and schema sprawl becomes a maintenance problem.
• In the starter kit: not implemented. The kit keeps one shared public schema the way the service layer expects.

3. Shared tables with an org_id column

All tenants share the same tables. A tenant boundary column — here org_id — identifies the owning tenant on every tenant-scoped row, and every query filters by it.

• When to choose: cost-sensitive SaaS, rapid prototyping, teams that want one migration and one backup. This is the most common model for starter kits.
• Cost: lowest operationally, but isolation depends on application-layer correctness — a missed org filter is a data leak. That is why the kit backs it with defense-in-depth options (RLS) too.
• In the starter kit: this is the implemented model. organizations is the root tenant table; memberships, invites, and audit_log all carry an org_id.

The shared model in the Drizzle schema

The whole data model lives in src/lib/server/db/schema.ts as Drizzle pgTable definitions. The schema comment states the design intent explicitly: the application-facing model is identical to the SQLite starter so the entire service layer, RBAC, billing, and audit code ports unchanged — only the driver changes. The organizations table is the tenant root, and memberships binds a user to an org with a unique constraint on (org_id, user_id):

export const organizations = pgTable(
  'organizations',
  {
    id: uuid('id').primaryKey(),
    name: text('name').notNull(),
    slug: text('slug').notNull(),
    createdAtMs: bigint('created_at_ms', { mode: 'number' }).notNull()
  },
  (t) => [uniqueIndex('organizations_slug_uq').on(t.slug)]
);

export const memberships = pgTable(
  'memberships',
  {
    id: uuid('id').primaryKey(),
    orgId: uuid('org_id')
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    role: text('role').notNull(),
    createdAtMs: bigint('created_at_ms', { mode: 'number' }).notNull()
  },
  (t) => [
    uniqueIndex('memberships_org_user_uq').on(t.orgId, t.userId),
    index('memberships_user_idx').on(t.userId)
  ]
);

The org_id column is the tenant boundary, and memberships_org_user_uq is the binding guarantee: a user gets exactly one membership row per org — no duplicates — while remaining free to join many orgs. invites.org_id and audit_log.org_id extend the same boundary to onboarding and the audit trail.

Postgres-specific type choices

The schema makes deliberate Postgres-native choices that keep the service layer portable to the SQLite starter:

• uuid primary keys: app-generated UUIDv4. id: uuid('id').primaryKey() everywhere a PK is an id; the app supplies the random values, so keys are unforgeable-by-guessing and collision-safe across environments.
• bigint Unix-epoch-millisecond timestamps: columns like created_at_ms are bigint('created_at_ms', { mode: 'number' }) — milliseconds, UTC everywhere. This matches the app contract and the SQLite starter exactly, so timestamps need no per-driver translation.
• One table diverges on purpose: sessions.id is a text primary key holding the sha256(token) of the raw session token, and audit_log.org_id/actor_user_id are plain indexed text (no foreign key) so history survives member or org removal. These are documented, intentional exceptions.

Why keep the service layer portable? Because every domain service is a pure function that accepts a Db — typed as PostgresJsDatabase<typeof schema> — and imports nothing from SvelteKit. The same service layer runs unchanged on the Multi-tenant SvelteKit Starter (SQLite); only the driver and schema types differ. If you keep the app-facing types aligned, that portability is preserved.

How isolation is enforced per request

The key architectural decision: tenant scoping lives in the service layer, not scattered across route handlers. Routes are thin — they parse a form, call a service, and fail or redirect. Every service function takes the orgId (and typically the caller's user id) and applies it as a Drizzle eq() predicate inside the query, so no code path touches tenant data without the org in scope.

Because the caller's membership is part of the WHERE clause, a user who is not a member gets nothing back — there is no separate permission check that can be forgotten. This mirrors the kit's own getOrgForUser-style pattern, where an unmatched join returns null and the layout turns that into a 404:

// service layer (orgs/members domain), abridged
import { and, eq } from 'drizzle-orm';
import { memberships, organizations } from '../db/schema';

/** Returns the org plus the caller's role, or null when not a member. */
export async function getOrgForUser(db: Db, userId: string, orgId: string) {
  const row = (
    await db
      .select({ org: organizations, role: memberships.role })
      .from(organizations)
      .innerJoin(
        memberships,
        and(eq(memberships.orgId, organizations.id), eq(memberships.userId, userId))
      )
      .where(eq(organizations.id, orgId))
      .limit(1)
  )[0];
  if (!row) return null;
  return { org: row.org, role: row.role as Role };
}

For mutating actions, authority is re-derived fresh inside the same request. Callers pass an actorRole; the service runs requireRole() (which re-reads the membership row), then requirePermission(), then a hierarchy check — all before any write. That leaves no trust in client-submitted role fields: a forged POST with a bogus role or org id fails because the scoped query finds no row.

Tenancy is application-enforced by default and is the runtime boundary. Postgres RLS shipped in rls/0010_rls_policies.sql is an additional database-level safety net — see the RLS defense-in-depth guide — not a replacement for the service layer. A pooled Postgres connection has no per-user identity the way Supabase's per-request auth.uid() does, so the app layer must stay the enforceable boundary.

The membership model

A user may belong to many orgs; the memberships row is the binding. The unique index on (org_id, user_id) prevents duplicate memberships within an org without capping how many orgs a user can join. The app resolves the active org from the session user's memberships at request time rather than assuming a single org.

Roles are scoped to the org, not global: owner > admin > member, with ranks 2 > 1 > 0. Hierarchy rules are enforced everywhere — act downward only, grant strictly below yourself, no self-modification, and a single-owner invariant (the last owner can neither leave nor be removed). Capabilities derive from DB state via the RBAC matrix in src/lib/server/rbac.ts; the UI reads a server-computed permissions record that is cosmetic only.

The org owns all data. Memberships, invites, and audit entries all reference the org, not the user — so when a user is removed, the org's data stays.

Invite flow as tenant onboarding

Creating an org is self-service and makes the creator its owner. Adding other members is the invite flow — the tenant onboarding mechanism. There is no self-service join into an existing org.

1. A member creates an invite; the raw token is shown once, and only its sha256 hash is stored (invites.tokenHash).
2. The invitee opens the single-use link, which expires after 7 days (a expiresAtMs timestamp).
3. Acceptance is atomic — a conditional UPDATE ... WHERE ... accepted_at_ms IS NULL AND revoked_at_ms IS NULL RETURNING id — so two concurrent clicks have exactly one winner, no explicit transaction needed.
4. On accept, a memberships row is created (subject to the seat gate), and the user is now scoped to the org.

The optional invites.email field is a human "intended recipient" note, not an enforcement boundary: the link works for whoever holds the raw token (documented), because email-enforced invites would require a transactional email provider.

Keeping the service layer portable (why it matters)

The same org-scoped query patterns transfer directly between the SQLite starter and this Postgres starter. Whether you later move to schema-per-tenant or database-per-tenant, the org_id scoping you established in the service layer is the load-bearing part — the migration is a connection and type change, not a rewrite of your domain logic. And because domain services accept any Db, adding read replicas later (separate write/read Drizzle handles) doesn't touch the service logic either — see the read replicas guide.

Production checklist

• Every tenant-scoped table carries an org_id (orgId) column — organizations is the root, and memberships/invites/audit rows all reference it. No cross-tenant data lives outside an org scope.
• Every query that reads or writes tenant data applies the org scope as a Drizzle eq() predicate inside the query, not as a separate check that can be forgotten. The caller's membership is part of the WHERE clause.
• Roles are per-org. The membership row is unique on (org_id, user_id), so a user cannot hold two memberships in the same org but can belong to many orgs.
• Primary keys are app-generated uuid (UUIDv4); timestamps are Unix epoch milliseconds as bigint (mode: 'number'), UTC everywhere — matching the app contract.
• The invite flow is the tenant onboarding mechanism. There is no self-service join into an existing org; a member sends a single-use hashed invite, the invitee accepts, and a membership row is created.
• Service functions accept a Db and take orgId (and caller identity) as arguments — routes are thin and never touch the database directly for writes.
• No trust in client-role fields: every mutating call re-derives authority from a fresh membership read (requireRole()) inside the same request, then runs requirePermission() before any write.
• Set up indexes on (org_id, ...) for frequently queried tables. The kit ships memberships_org_user_uq (unique) and per-org indexes on invites and audit_log.
• Test tenant isolation: the 194-test suite runs against a real Postgres test database, truncating tracked tables per test, and pins the hierarchy and error-mapping contracts end to end.

Related guides: RLS defense-in-depth · Session management