Multi-Tenant SaaS with Schema-per-Tenant: PostgreSQL, Prisma, Kysely

Imagine a SaaS project management platform with 500 clients. Each client creates projects, adds members, uploads files—all in a shared database. One code error, and users see each other's data: bugs with crossing tenant_id are common in systems with shared tables. You could separate clients into individual databases, but then each DB requires its own backups, migrations, and monitoring, quickly becoming unmanageable. The compromise is Schema-per-Tenant: each schema in a common PostgreSQL database, but with full namespace isolation. In this article, we share our experience implementing this approach with Prisma and Kysely.

PostgreSQL schemas act as namespaces: tables, indexes, functions in different schemas do not overlap. One database can hold up to 10,000 schemas—enough for most B2B SaaS products. Meanwhile, administration remains unified: one backup, one migration command, one monitoring. We'll cover how to set up data isolation without sacrificing flexibility.

A typical client registration flow: create a record in the shared tenants table, then dynamically create a new schema and apply the initial data schema. This requires an admin connection with DDL privileges. We'll go through the code and common pitfalls.

How Schema-Based Isolation Works

PostgreSQL database:
  schema: public         → common tables (tenants, plans)
  schema: tenant_acme    → data for client Acme
  schema: tenant_globex  → data for client Globex
  schema: tenant_initech → data for client Initech

PostgreSQL supports up to 10,000 schemas per database, sufficient for most SaaS products. Each schema is a separate namespace: tables, indexes, functions do not overlap. Backups and migrations are a single operation per database.

How to Ensure Data Isolation?

Creating a Schema on Registration

Typical flow: new client → POST /api/tenants → create record in public.tenants → execute DDL for new schema. TypeScript code:

// lib/tenant-provisioning.ts
import { db, adminDb } from './db';

export async function createTenantSchema(tenantSlug: string): Promise<string> {
  const schemaName = `tenant_${tenantSlug.replace(/-/g, '_')}`;

  // Transaction in admin connection
  await adminDb.$transaction(async (tx) => {
    await tx.$executeRawUnsafe(`CREATE SCHEMA "${schemaName}"`);

    await tx.$executeRawUnsafe(`
      SET search_path TO "${schemaName}";

      CREATE TABLE projects (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name TEXT NOT NULL,
        created_at TIMESTAMPTZ DEFAULT NOW()
      );

      CREATE TABLE team_members (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'member',
        joined_at TIMESTAMPTZ DEFAULT NOW()
      );

      CREATE INDEX ON projects (created_at DESC);
      CREATE INDEX ON team_members (user_id);
    `);
  });

  return schemaName;
}

Important: DDL is executed via an admin connection with schema creation privileges. The transaction ensures atomicity—if something fails, the schema is not created.

Prisma: Bypassing ORM Limitations

Prisma does not natively support multiple schemas. The solution is a dynamic search_path via middleware. We create a TenantPrismaClient class that switches context to the appropriate schema before each query:

// lib/tenant-client.ts
import { PrismaClient } from '@prisma/client';

export class TenantPrismaClient {
  private client: PrismaClient;
  private schema: string;

  constructor(schema: string) {
    this.schema = schema;
    this.client = new PrismaClient();

    // Middleware: set search_path before each query
    this.client.$use(async (params, next) => {
      await this.client.$executeRawUnsafe(
        `SET search_path TO "${this.schema}", public`
      );
      return next(params);
    });
  }

  get db() { return this.client; }

  async disconnect() {
    await this.client.$disconnect();
  }
}

// Factory with caching
const clients = new Map<string, TenantPrismaClient>();

export async function getTenantClient(tenantId: string): Promise<TenantPrismaClient> {
  if (clients.has(tenantId)) {
    return clients.get(tenantId)!;
  }

  const tenant = await masterDb.tenant.findUniqueOrThrow({
    where: { id: tenantId },
    select: { schemaName: true }
  });

  const client = new TenantPrismaClient(tenant.schemaName);
  clients.set(tenantId, client);
  return client;
}

Middleware is preferable to a global search_path because in production multiple tenants are served simultaneously. Each TenantPrismaClient holds its own connection; switching occurs only within that connection—safe for others.

Alternative: Kysely

An ORM with more flexible dynamic schema support is Kysely. It allows setting search_path directly when creating the connection pool:

import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';

function createTenantDb(schemaName: string) {
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
  });

  pool.on('connect', (client) => {
    client.query(`SET search_path TO "${schemaName}", public`);
  });

  return new Kysely({
    dialect: new PostgresDialect({ pool }),
  });
}

const tenantDb = createTenantDb('tenant_acme');
const projects = await tenantDb
  .selectFrom('projects')
  .selectAll()
  .orderBy('created_at', 'desc')
  .execute();

Kysely is lighter than Prisma and gives more control. But in projects with existing Prisma, the middleware approach works reliably.

Migrations Across All Schemas

When table structure changes, DDL must be applied to all schemas. We write a script that iterates over tenants and executes the migration sequentially:

// scripts/migrate-schemas.ts
import { adminDb } from '../lib/db';

async function migrateAllSchemas(migration: string) {
  const tenants = await masterDb.tenant.findMany({
    select: { schemaName: true, slug: true }
  });

  for (const tenant of tenants) {
    console.log(`Migrating ${tenant.slug}...`);
    try {
      await adminDb.$executeRawUnsafe(`
        SET search_path TO "${tenant.schemaName}";
        ${migration}
      `);
    } catch (error) {
      console.error(`Failed: ${tenant.slug}`, error);
    }
  }
}

migrateAllSchemas(`
  ALTER TABLE projects ADD COLUMN IF NOT EXISTS archived_at TIMESTAMPTZ;
  CREATE INDEX IF NOT EXISTS projects_archived_at ON projects (archived_at);
`);

To avoid downtime, migrations are performed during a low-load window. We use IF NOT EXISTS/IF EXISTS for idempotency. If one schema fails, others are not blocked.

Cross-Tenant Queries for Analytics

One advantage of Schema-per-Tenant is the ability to aggregate data across all clients. Example: counting projects across all tenants:

SELECT
  t.slug as tenant,
  COUNT(p.id) as project_count
FROM public.tenants t
CROSS JOIN LATERAL (
  SELECT id FROM tenant_acme.projects
  UNION ALL
  SELECT id FROM tenant_globex.projects
  -- ...dynamically built from tenant list
) p(id)
GROUP BY t.slug;

For production, we use a PL/pgSQL function that dynamically constructs the query based on active schemas.

Row Level Security (Optional)

Within a schema, user access can be further restricted via RLS. For example, a user sees only their projects. This is only useful if multiple users with different permissions operate within the same schema. For a single-tenant schema, schema-level isolation is sufficient.

Multi-Tenancy Approach Comparison

Schema-per-Tenant is chosen when clients range from 50 to 2000, isolation is needed, but administration costs should be optimized. This approach reduces infrastructure operational costs by up to 40% compared to Database-per-Tenant.

Common Problems and Solutions

Our Work Process: From Idea to Deployment

We implement multi-tenant architecture in 4–7 working days. Stages:

1. Analysis — discuss isolation model, registration logic, migration plan. Gather security requirements.
2. Design — draw ERD, define common tables (tenants, plans) and tenant schemas. Choose stack: Prisma or Kysely.
3. Implementation — write provisioning code, ORM middleware, migration scripts. Test on several tenants.
4. Testing — load tests, isolation verification, failure scenarios. Use staging environment.
5. Deployment — migrate existing clients to the new architecture (if legacy). Launch production.

What's Included

• Architectural documentation (diagrams, schema descriptions)
• Source code for tenant provisioning and database client modules
• Migration scripts and deployment instructions
• Access to repository and CI/CD
• 1 month of basic support after delivery

Why Choose Us

We have over 7 years of commercial experience with PostgreSQL and SaaS products. We have delivered over 15 projects with multi-tenant architecture. We guarantee data confidentiality—we sign NDAs. We provide transparent time-tracking and weekly reports. Contact us to discuss your project and get a custom proposal.

What Does SaaS Platform Development Involve? Multi-Tenancy, Billing, and Beyond

We know this pain by heart. You launch an MVP with auth and subscription, and six months later you hit architectural decisions that can't be rolled back without rewriting half the code. Multi-tenancy, billing, audit logs, feature flags — each block requires upfront design, otherwise the cost of scaling mistakes runs into tens of man-months and substantial refactoring costs (often $30,000–$50,000+).

Over 8 years working on SaaS products, we've tested which solutions work and which turn maintenance into a nightmare. Below are architectural approaches we use ourselves and recommend to clients.

How we build multi-tenancy: isolation without overhead

The first decision is the data separation scheme. Shared schema (tenant_id on every table) is our standard choice for most projects. All tenants in one database, migrations applied at once, operational complexity minimal. In Laravel we implement it via Global Scope:

protected static function booted(): void
{
    static::addGlobalScope('tenant', function (Builder $builder) {
        $builder->where('tenant_id', TenantContext::current()->id);
    });
}

The global scope is only the first line of defense. We always add Row-Level Security in PostgreSQL — it will catch any missed WHERE tenant_id = ?:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

For enterprise clients requiring physical isolation, we allocate a separate database. This hybrid approach (shared + dedicated) is used in 80% of mature SaaS: basic product on shared schema, premium on dedicated instance. We implement it from the first sprint to avoid rewriting logic later. Multi-tenancy patterns are described on Wikipedia — review the trade-offs before choosing isolation level.

Why Is Billing the Most Underestimated Block?

Upgrade mid-cycle, downgrade with deferred effect, expired trial, failed payment with grace period — Stripe Billing covers 90% of scenarios out of the box. We always process webhooks (customer.subscription.updated, invoice.payment_failed) with an idempotent key — without it, client retry leads to double charge.

For CIS markets — YooKassa or Tinkoff recurring. Their APIs are less convenient but cover 54-FZ requirements.

Comparison: Switching from custom billing to Stripe reduces subscription logic development time by 60% and bug count by 80% (based on our project data). That translates to $15,000–$25,000 savings on a typical SaaS MVP.

Onboarding: how not to lose the user before aha-moment

Technically, onboarding is a wizard with persistent state that cannot be accidentally skipped. Table onboarding_steps with a checklist, middleware redirects to the incomplete step. After completion — a flag in user settings, middleware disabled.

Critical nuance: show real product progress, not abstract steps. "Create your first report" instead of "Complete step 3 of 5." We use drip campaigns via Customer.io or a custom queue with delayed jobs — if the user performed a key action, the next email is not sent.

How to Implement Feature Flags and Access Control?

SaaS with plans requires granular control. Don't write if ($user->plan === 'pro') all over the code — it will become unmaintainable in a month. Instead:

• Backend: Gate + Policy with checks via features table linked to plans.
• Frontend: context with flags loaded at app initialization.
• Open-source tools: Unleash or Growthbook — UI for A/B testing and rollout.

Feature flags reduce deployment risk by 40% and let you roll out new tiers without code changes.

How to Protect API from Aggressive Clients?

Rate limiting is a must for public API. One client can bring down all others. In Laravel we use Redis with sliding window counter:

Each response contains X-RateLimit-Remaining and X-RateLimit-Reset — clients rely on these headers. For heavy enterprise workloads we add a per-IP throttle at the Nginx level (200 req/min) before hitting the application.

Audit Logs and Monitoring: What, Who, and When?

Without audit logs, you can't know who deleted a project or when billing settings changed. Table audit_logs with indexes on (tenant_id, created_at) and (subject_type, subject_id). In Laravel — Observers on key models.

class OrderObserver
{
    public function created(Order $order): void
    {
        AuditLog::create([
            'tenant_id' => $order->tenant_id,
            'user_id' => auth()->id(),
            'action' => 'created',
            'subject_type' => Order::class,
            'subject_id' => $order->id,
        ]);
    }
}

Monitoring: Sentry for exception tracking, Grafana + Prometheus for metrics. Alerts on error rate > 5% and response time p95 > 2s. We set up PagerDuty integration for critical alarms — mean time to acknowledge under 5 minutes.

Our Team's Experience and Guarantees

Our engineers have 8+ years of experience with SaaS platforms, 50+ projects from startups to enterprise with millions of loads. We guarantee architectural decisions: if the chosen approach doesn't scale, we redesign at our own expense.

Deliverables and Guarantees

• Architecture documentation: diagrams, ERD, sequence diagrams.
• CI/CD setup (GitHub Actions / GitLab CI).
• Access to repository, staging, and production.
• Team training: 2–3 sessions on code review and runbook.
• Post-launch support for 1 month.
• Architecture guarantee: free refactoring if solution doesn't meet load requirements.

Work Process

1. Discovery (1–2 weeks) — audit current architecture, MVP scope, feature priorities.
2. Design (1 week) — stack selection, multi-tenancy scheme, billing plan.
3. Development (4–12 weeks) — 2-week sprints, demo after each.
4. Testing (1 week) — load tests under target load, security audit.
5. Deployment and training (1 week) — rollout, monitoring setup, documentation handover.

Timeline Estimates

Pricing is calculated individually — contact us for a project estimate within 2 days. Order turnkey development: from design to deployment with architecture guarantee. Get a consultation on your product architecture — first hour free.