Database Design Patterns for Multi-Tenant SaaS Applications

One of the most consequential architectural decisions in a SaaS application is how you handle multi-tenancy at the database level. The choice affects security, performance, cost, operational complexity, and how easily you can scale. There's no one-size-fits-all answer — the right strategy depends on your specific requirements.

Strategy 1: Shared Database, Shared Schema (Row-Level Isolation)

All tenants share the same database and tables. A tenant_id column on every table identifies which tenant owns each row.

-- Every table includes tenant_id
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  customer_id UUID NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Composite index for tenant-scoped queries
  INDEX idx_orders_tenant (tenant_id, created_at DESC)
);

-- Row Level Security (PostgreSQL)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::UUID);
  
-- Set tenant context per request
SET app.current_tenant = 'tenant-uuid-here';

Pros: Simplest to implement, lowest cost (one database), easy migrations (apply once). Cons: Noisy neighbor risk, tenant data breach risk if RLS misconfigured, harder to offer per-tenant customization.

Strategy 2: Shared Database, Schema-Per-Tenant

Each tenant gets their own database schema (namespace) within a shared database. Tables are identical but isolated by schema.

-- Create schema for new tenant
CREATE SCHEMA tenant_acme;

-- Tables exist within the tenant's schema
CREATE TABLE tenant_acme.orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Application sets search_path per request
SET search_path TO tenant_acme, public;

Pros: Better isolation than row-level, per-tenant schema customization possible, moderate cost. Cons: Migrations must be applied to every schema, connection pooling complexity, PostgreSQL has practical limits around 10,000 schemas.

Strategy 3: Database-Per-Tenant

Each tenant gets their own database instance. Maximum isolation at maximum cost.

Pros: Complete isolation, independent scaling, per-tenant backup/restore, easiest compliance story. Cons: Highest cost, most operational complexity, cross-tenant analytics require aggregation infrastructure.

Decision Matrix

Choose your strategy based on these factors:

• 0-100 tenants, price-sensitive: Shared schema with RLS
• 100-1000 tenants, moderate customization needs: Schema-per-tenant
• Enterprise with strict compliance requirements: Database-per-tenant
• Hybrid approach: Shared schema for standard tiers, dedicated databases for enterprise clients

Implementation with Prisma

For the shared schema approach with Prisma, implement tenant isolation at the middleware level:

// Prisma middleware for automatic tenant filtering
prisma.$use(async (params, next) => {
  const tenantId = getTenantFromContext();
  
  // Inject tenant_id into all queries
  if (params.action === 'findMany' || params.action === 'findFirst') {
    params.args.where = { ...params.args.where, tenantId };
  }
  
  if (params.action === 'create') {
    params.args.data = { ...params.args.data, tenantId };
  }
  
  return next(params);
});

The most important thing is to choose a strategy that matches your current scale while having a migration path to more isolation if needed. Don't over-engineer for scale you don't have, but don't paint yourself into a corner either.