Multi-tenant database design

Three multi-tenancy models

There are three standard approaches to multi-tenant data isolation. Each trades isolation strength for operational complexity.

1. Database per tenant

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

• When to choose: strict data sovereignty requirements, compliance certifications (SOC 2, HIPAA) that mandate physical separation, enterprise tenants who contractually require dedicated infrastructure.
• Cost: highest. Each tenant adds connection overhead, migration complexity (you run migrations against N databases), 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 tenant has its own schema (namespace). Tables are duplicated per schema, so migration is per-tenant rather than global.

• When to choose: moderate isolation needs with a manageable tenant count (tens, not thousands). PostgreSQL schemas are the canonical example.
• Cost: moderate. Migrations must run per-schema, connection routing must resolve the schema name, and schema sprawl becomes a maintenance problem at scale.
• In the starter kit: not implemented. SQLite does not support named schemas in the PostgreSQL sense.

3. Shared database with tenant_id column

All tenants share the same tables. A tenant_id column on every tenant-scoped row identifies which tenant owns the data. All queries are filtered by this column.

• When to choose: small-to-medium tenants, cost-sensitive deployments, rapid prototyping, teams that want operational simplicity. This is the most common model for SaaS starters.
• Cost: lowest. One database, one migration, one backup. But isolation depends on application-layer correctness — a missed filter is a data leak.
• In the starter kit: this is the implemented model. Every data table has an org_id column, and every query is scoped at the application layer.

The shared database approach in practice

The starter kit's schema is defined as Drizzle sqliteTable objects in src/lib/server/db/schema.ts. Every table that holds tenant-scoped data carries an org_id (orgId) column. The organizations table is the root of the tenant tree, and memberships bind a user to an org — the unique index prevents a user from holding two memberships in the same org:

import { index, integer, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core';

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

export const memberships = sqliteTable(
  'memberships',
  {
    id: text('id').primaryKey(),
    orgId: text('org_id')
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    role: text('role').notNull(),
    createdAtMs: integer('created_at_ms').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. Every query that reads or writes tenant-scoped data must include it in the filter, and the kit enforces this in the service layer — service functions take orgId and scope with Drizzle eq(memberships.orgId, orgId)-style predicates rather than relying on convention.

How isolation is enforced per request

The key architectural decision: tenant scoping lives in the service layer, not scattered across route handlers. Every service function takes an orgId (and often the caller's user id) and applies it as a Drizzle eq() filter, so no code path touches tenant data without the org in scope. The app resolves "which org" by reading the session user's memberships. A representative service pattern (abridged from src/lib/server/services/orgs.ts) looks like this:

// src/lib/server/services/orgs.ts (abridged)
import { and, eq } from 'drizzle-orm';
import { memberships, organizations } from '../db/schema';

/** Returns every org the user is a member of. */
export async function listOrgsForUser(db: Db, userId: string) {
  return db
    .select({ id: organizations.id, name: organizations.name, slug: organizations.slug, role: memberships.role })
    .from(memberships)
    .innerJoin(organizations, eq(organizations.id, memberships.orgId))
    .where(eq(memberships.userId, userId));
}

/** Returns the org plus the caller's role, or null when the caller is 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 };
}

The org scope is applied inside the query as an eq(memberships.orgId, orgId) predicate. Because the caller's membership is part of the WHERE clause, a user who is not a member of an org gets nothing back — there is no separate "permission check" that can be forgotten. Routes are thin: they call the service, and orgId is a required argument with no default.

Form actions follow the same pattern — every mutating action re-derives authority inside the service call it invokes, so a forged POST with a bogus ID returns an error because the scoped query finds no row.

The membership model

The same user can belong to multiple orgs. A memberships row is the binding between a user and an org, and the unique index is on (org_id, user_id) — it prevents duplicate memberships in the same org without limiting 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 — an admin role in one org does not grant any permissions in another. The role hierarchy (owner > admin > member) controls what actions are permitted within that org. See the RBAC and role hierarchy guide for the full permission matrix.

The org owns all data. Membership rows, invites, and audit log entries all reference the org, not the user. When a user is removed from an org, the org's data stays (it belongs to the org, not the individual).

Invite flow as tenant onboarding

Creating an org is self-service: the /app route exposes a createOrg form action that creates an org and makes the caller its owner. The invite flow is the way to add OTHER members to an org you already belong to:

1. An existing org member sends an invite link (via the invite action).
2. The invitee clicks the link, which contains a time-limited token.
3. Upon acceptance, a membership row is created linking the user to the org.
4. The user's memberships now include that org — every subsequent request resolves the active org from those memberships.

Because org creation is self-service, a user can spin up their own org and becomes its owner by default; other members are brought in via invites.

The invite token is single-use and expires after a configurable window. Expired or already-accepted invites return clear error messages. See the invite link flow guide for implementation details.

Audit log design for tenant-scoped data

The audit log follows an append-only, org-scoped pattern. Every entry records the org, the actor, the action, and the target resource:

export const auditLog = sqliteTable(
  'audit_log',
  {
    seq: integer('seq').primaryKey({ autoIncrement: true }),
    orgId: text('org_id'),
    actorUserId: text('actor_user_id'),
    action: text('action').notNull(),
    targetType: text('target_type'),
    targetId: text('target_id'),
    metadataJson: text('metadata_json'),
    createdAtMs: integer('created_at_ms').notNull()
  },
  (t) => [index('audit_org_seq_idx').on(t.orgId, t.seq)]
);

Cross-tenant reads are not possible because the same per-request org scoping applies to audit log queries. A user in org A who tries to read the audit log will only see entries where org_id matches org A's ID. There is no admin-level "view all tenants" endpoint in the shipped code.

The append-only nature means audit entries are never updated or deleted by application code. If you need to comply with right-to-erasure regulations, handle that at the infrastructure level (redaction or deletion at the database layer) rather than through the application's audit API.

For the full audit log architecture, see the append-only audit log design guide.

SQLite considerations for multi-tenancy

SQLite is a deliberate choice for the starter kit. It has real advantages and real limits for multi-tenant workloads:

• Single-file simplicity: the entire database is one file. Backups are file copies. There is no connection pool to tune, no server process to monitor, no credentials to rotate.
• WAL mode: Write-Ahead Logging allows concurrent reads while a write is in progress. This is critical for multi-tenant apps where multiple users are reading simultaneously while occasional writes happen. WAL is enabled by default in the kit.
• Good enough for small-to-medium tenants: SQLite handles thousands of concurrent readers and hundreds of writes per second on modern hardware. For a SaaS with dozens to low hundreds of active tenants, this is sufficient.
• Limits to be honest about: SQLite uses file-level locking for writes. Under heavy write concurrency (hundreds of simultaneous writes), you'll see contention. At that point, migrate to PostgreSQL or MySQL — the query patterns (org_id scoping) transfer directly.

The migration path from SQLite to a client-server database is straightforward because the kit does not use SQLite-specific query features beyond standard SQL. Replace the connection, run the same migrations, and the application layer works unchanged.

When to outgrow shared-database

The shared-database model has honest limits. You should plan a migration when any of these become true:

• Data sovereignty requirements: a tenant contractually requires that their data reside in a specific jurisdiction or on dedicated infrastructure. The shared model cannot satisfy this — you need database-per-tenant or at minimum schema-per-tenant.
• Per-tenant customization: if tenants need custom schemas (extra columns, custom tables), the shared model breaks down. Schema-per-tenant or database-per-tenant gives you that flexibility.
• Compliance certifications: SOC 2 Type II, HIPAA, and similar frameworks may require audit trails that demonstrate physical or logical separation. The shared model relies on application-layer enforcement, which auditors may not accept without additional controls.
• Write concurrency limits: when simultaneous writes across tenants exceed what a single database can handle, you need to partition — either by moving heavy-tenant workloads to their own database, or by switching to a client-server database that handles concurrent writes natively.

None of these are failures. They are natural evolution points. The starter kit is designed to get you to revenue with minimal infrastructure cost, and the query patterns it establishes (org_id scoping) transfer cleanly to any of these more complex models.

Production checklist

• Every table that holds tenant data has a tenant_id (org_id) column — no exceptions. If a column exists but isn't scoped, it will leak data eventually.
• Every server-side query (load function, form action, API endpoint) resolves the current org from the session before touching the database. There is no "default org" fallback.
• The membership row is the binding between a user and an org. The same user can belong to multiple orgs; the app resolves the active org from the session user's memberships at request time. The unique constraint is per (org_id, user_id), which prevents duplicate memberships without limiting org count.
• Roles are per-org, not global. The same user could be an admin in one org and a member in another, but each role is scoped to the org it was granted in.
• The invite flow is the tenant onboarding mechanism. There is no self-service signup into an org — someone already in the org sends an invite, the invitee accepts, a membership row is created, and the user is now scoped.
• Audit log entries are append-only and org-scoped. There is no cross-tenant read path because RBAC + org scope are enforced per request, not as a one-time migration check.
• SQLite with WAL mode is a valid production choice for small-to-medium tenants. WAL allows concurrent reads while a write is in progress, which is the main bottleneck on single-file databases.
• Set up database indexes on (tenant_id, ...) for every frequently-queried table. An unscoped query that scans all tenants is both a performance problem and a data-leak risk.
• Test tenant isolation with a two-org fixture: create two orgs, insert data into both, then verify that queries from org A never return org B data. This should be in the test suite, not manual QA.

Related guides: Multi-tenant auth architecture · RBAC & role hierarchy · Append-only audit log