Multi-Tenant Schema Design: What We Got Wrong (And How to Fix It)

We've rebuilt a multi-tenant schema from scratch exactly once. It was not fun. It happened because we made the classic move of 'we'll fix the data model later' — and later arrived at the worst possible time, with real users and real data. So here's everything we wish someone had told us before we started.

There are three main approaches to multi-tenant schema design, and each one is a different trade-off between cost, complexity, and isolation. Most tutorials pick one and pretend the others don't exist. We're going to walk through all three, tell you when each one makes sense, and give you the actual SQL to implement them.

The Three Approaches (And Who Should Use Each)

Before we get into code, let's be honest about the landscape. You've got: shared schema with a tenant_id column on everything, separate schemas per tenant in the same database, and completely separate databases per tenant. These are not equally valid for everyone — your choice depends on how many tenants you expect, how paranoid your customers are about data isolation, and how much operational complexity you can stomach.

• Shared schema (row-level isolation): Best for B2C SaaS, high tenant counts, cost-sensitive startups. Think Notion, Linear.
• Schema-per-tenant: Sweet spot for B2B SaaS with compliance requirements. Moderate tenant counts (under a few thousand). Think tools sold to mid-market companies.
• Database-per-tenant: Enterprise, high-security, or when tenants are actually paying enough to justify the ops cost. Think Salesforce's biggest customers.

Most early-stage SaaS companies should start with shared schema and row-level security. It's cheaper, simpler to migrate, and Postgres's RLS is genuinely good now. Here's how to do it properly.

Shared Schema: Row-Level Security Done Right

The naive implementation is just adding tenant_id to every table and filtering in your queries. Don't do it this way. You will forget to add the filter somewhere, and you'll leak data. Instead, use Postgres Row-Level Security to enforce tenant isolation at the database level, so forgetting a WHERE clause doesn't become a security incident.

-- Create your tables with tenant_id
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create a policy that uses the current session's org context
CREATE POLICY tenant_isolation ON projects
  USING (organization_id = current_setting('app.current_org_id')::UUID);

-- In your app, set this at the start of each request
-- SET LOCAL app.current_org_id = 'your-org-uuid';

In your Next.js API routes or server actions, you'd set the context at the start of the request using a transaction. The key insight is SET LOCAL — it only applies to the current transaction, so you're not accidentally leaking context between requests in a connection pool.

// lib/db.ts — wrapping queries with tenant context
import { db } from './drizzle'; // or prisma, whatever you're using
import { sql } from 'drizzle-orm';

export async function withTenantContext<T>(
  organizationId: string,
  fn: () => Promise<T>
): Promise<T> {
  return await db.transaction(async (tx) => {
    await tx.execute(
      sql`SELECT set_config('app.current_org_id', ${organizationId}, true)`
    );
    return await fn();
  });
}

// Usage in a server action
export async function getProjects(organizationId: string) {
  return withTenantContext(organizationId, async () => {
    return db.select().from(projects);
    // RLS automatically filters to the current org — no WHERE clause needed
  });
}

The true = true PostgreSQL RLS bypass is the thing that bites people. If you're using a superuser connection, RLS is ignored. Use a role with limited permissions for your application connection. Your migration user and your app user should be different.

The Organization → Member → Role Pattern

Almost every SaaS needs some form of team management. Users belong to organizations, they have roles within those organizations, and those roles determine what they can do. The mistake we see constantly is baking permissions into the user table rather than making them a relationship. Here's the schema pattern that scales without becoming a nightmare:

CREATE TYPE org_role AS ENUM ('owner', 'admin', 'member', 'viewer');

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL, -- for URLs like /org/acme/dashboard
  plan TEXT NOT NULL DEFAULT 'free',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role org_role NOT NULL DEFAULT 'member',
  invited_by UUID REFERENCES users(id),
  joined_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(organization_id, user_id) -- a user can only be in an org once
);

-- Index for the most common query: "what orgs does this user belong to?"
CREATE INDEX idx_org_members_user ON organization_members(user_id);
CREATE INDEX idx_org_members_org ON organization_members(organization_id);

The slug column on organizations is worth calling out specifically. You want this from day one because people will want readable URLs. Adding unique slugs to an existing table with thousands of rows is a painful migration. Add it now, enforce uniqueness, and use it in your routing.

Handling Billing at the Schema Level

Billing state lives on the organization, not the user. This matters because in B2B SaaS, individuals don't pay — companies do. We've seen codebases where the Stripe customer ID was on the user model, and when the billing owner left the company, the whole subscription became orphaned. Don't do that.

-- Extend organizations with billing state
ALTER TABLE organizations ADD COLUMN stripe_customer_id TEXT UNIQUE;
ALTER TABLE organizations ADD COLUMN stripe_subscription_id TEXT UNIQUE;
ALTER TABLE organizations ADD COLUMN subscription_status TEXT DEFAULT 'trialing';
ALTER TABLE organizations ADD COLUMN subscription_period_end TIMESTAMPTZ;
ALTER TABLE organizations ADD COLUMN trial_ends_at TIMESTAMPTZ;

-- Or if you're greenfield, build it in from the start:
CREATE TABLE organization_billing (
  organization_id UUID PRIMARY KEY REFERENCES organizations(id) ON DELETE CASCADE,
  stripe_customer_id TEXT UNIQUE,
  stripe_subscription_id TEXT UNIQUE,
  status TEXT NOT NULL DEFAULT 'trialing',
  plan TEXT NOT NULL DEFAULT 'free',
  period_start TIMESTAMPTZ,
  period_end TIMESTAMPTZ,
  trial_ends_at TIMESTAMPTZ,
  cancel_at_period_end BOOLEAN DEFAULT FALSE,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

We prefer a separate organization_billing table because billing logic changes constantly, and keeping it isolated makes it easier to add columns without touching your core organizations table. The 1-to-1 relationship via primary key is basically free in terms of query complexity.

Feature Flags and Limits by Plan

Here's a pattern that saves you a lot of pain when you start offering multiple plans: don't hardcode plan limits in your application code. Store them in the database. This sounds obvious until you've shipped a hotfix at midnight because a limit was wrong in three different places in the codebase.

CREATE TABLE plan_features (
  plan TEXT NOT NULL,
  feature TEXT NOT NULL,
  limit_value INTEGER, -- NULL means unlimited
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (plan, feature)
);

-- Seed data for your plans
INSERT INTO plan_features (plan, feature, limit_value) VALUES
  ('free', 'projects', 3),
  ('free', 'team_members', 1),
  ('free', 'api_calls_per_month', 1000),
  ('pro', 'projects', 50),
  ('pro', 'team_members', 10),
  ('pro', 'api_calls_per_month', 50000),
  ('enterprise', 'projects', NULL),   -- NULL = unlimited
  ('enterprise', 'team_members', NULL),
  ('enterprise', 'api_calls_per_month', NULL);

-- Query to check if an org can create more projects
SELECT 
  pf.limit_value,
  COUNT(p.id) as current_count,
  (pf.limit_value IS NULL OR COUNT(p.id) < pf.limit_value) as can_create
FROM plan_features pf
JOIN organizations o ON o.plan = pf.plan
LEFT JOIN projects p ON p.organization_id = o.id
WHERE o.id = $1
  AND pf.feature = 'projects'
GROUP BY pf.limit_value;

Yes, this adds a database query when checking limits. That's fine. You can cache the plan features table aggressively since it changes rarely, and having a single source of truth for what each plan allows is worth it. When you introduce a new pro tier, you change one row, not a dozen if-statements scattered across your codebase.

Audit Logs: Build Them In From Day One

Enterprise customers will ask about audit logs. It will happen sooner than you expect. The good news is that the schema for a basic audit log is simple, and retrofitting it is dramatically harder than just adding it up front.

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  actor_id UUID REFERENCES users(id) ON DELETE SET NULL, -- null if system action
  action TEXT NOT NULL, -- 'project.created', 'member.invited', 'billing.upgraded'
  resource_type TEXT NOT NULL, -- 'project', 'member', 'organization'
  resource_id UUID,
  metadata JSONB DEFAULT '{}',
  ip_address INET,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- You'll mostly query by org + time range
CREATE INDEX idx_audit_logs_org_time 
  ON audit_logs(organization_id, created_at DESC);

-- Partition this table by month if you expect high volume
-- For most SaaS apps, a regular table with the index above is fine

The metadata JSONB column is your escape hatch for storing action-specific data without creating fifty different columns. When a project is deleted, store the project name in metadata. When a plan is upgraded, store the old and new plan. This makes audit log entries readable without needing to join to other tables that might have since been deleted.

If you're using Supabase or a managed Postgres, partition the audit_logs table by month once you cross ~1M rows per month. Before that, the index is enough. Premature partitioning is a real thing.

The Mistakes We Keep Seeing

After building a few SaaS products and looking at a lot of schemas, the same mistakes come up again and again. We're listing them here not to be preachy but because we've made most of them ourselves.

• Storing arrays of IDs in a column instead of a junction table. It feels faster until you need to query or update them.
• Using integer IDs for tenant-visible resources. UUIDs are slightly bigger but they can't be enumerated. Don't let users guess /projects/1, /projects/2.
• No created_at / updated_at on tables. You will want to know when something changed. Add both, make them NOT NULL with defaults.
• Soft deletes everywhere by default. deleted_at columns sound nice until you're debugging why a unique constraint is failing on an email that 'doesn't exist'. Use them selectively.
• Missing indexes on foreign keys. Postgres doesn't automatically index foreign keys. Every organization_id, user_id, project_id column that you JOIN or filter on needs an index.
• Putting everything in the public schema in Supabase. Use separate schemas (app, auth, billing) to keep things organized and make RLS policies easier to reason about.

The UUID enumeration one is especially worth repeating. We've seen production SaaS apps where you could just increment the project ID in the URL and see other organizations' projects. That's not a bug, that's a breach. Use gen_random_uuid() and sleep better.

If you're starting a new SaaS project and want these patterns already wired up — organizations, members, roles, billing, RLS — our templates at peal.dev have all of this built in and tested. Sometimes it's easier to start from something that already has the bones right.

The practical takeaway here is boring but important: your schema is harder to change than your application code. Migrations on tables with real data are stressful. Spend an extra hour designing your data model before you write a single line of application code. Draw it out, think through the queries you'll need, add the indexes, and get the tenant isolation story straight before you ship. Everything else you can refactor. Tenant isolation mistakes can cause data leaks.