SaaS Database Design: Schema Patterns, Multi-Tenancy & Best Practices

Start with PostgreSQL

Use PostgreSQL for your SaaS. It handles 99% of use cases:

• Relational data with ACID guarantees
• JSON columns for flexible data (JSONB)
• Full-text search built-in
• Scales to millions of rows easily
• Excellent with Supabase, RDS, Neon, etc.

You can always add Redis (caching) or Elasticsearch (search) later. Start with Postgres.

Core Schema Pattern

Every SaaS needs these tables:

-- Users (handled by auth service, but you need a profiles table)
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Organizations/Teams (for B2B)
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  owner_id UUID REFERENCES profiles(id),
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Organization membership
CREATE TABLE organization_members (
  organization_id UUID REFERENCES organizations(id),
  user_id UUID REFERENCES profiles(id),
  role TEXT NOT NULL DEFAULT 'member',
  PRIMARY KEY (organization_id, user_id)
);

-- Subscriptions (link to Stripe)
CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id),
  stripe_subscription_id TEXT UNIQUE,
  status TEXT NOT NULL,
  plan TEXT NOT NULL,
  current_period_end TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Multi-Tenancy Approaches

Option 1: Shared Database, Tenant Column (Recommended)

All tenants share tables, each row has an organization_id.

Pros: Simple, cheap, easy to query across tenants
Cons: Must remember to filter by org, RLS essential

Implementation:

-- Every tenant-specific table has organization_id
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) NOT NULL,
  name TEXT NOT NULL,
  ...
);

-- RLS ensures data isolation
CREATE POLICY "Org members see org data" ON projects
  FOR SELECT USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

Option 2: Schema Per Tenant

Each tenant gets their own PostgreSQL schema.

Pros: Strong isolation, easy per-tenant backup
Cons: Complex migrations, harder to scale

Option 3: Database Per Tenant

Each tenant gets their own database.

Pros: Complete isolation, easy compliance
Cons: Expensive, complex operations

Start with Option 1. Move to Option 2/3 only if enterprise customers require it for compliance.

Essential Patterns

Always Use UUIDs

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

Not sequential integers. UUIDs are globally unique, don't leak information, and work across databases.

Always Add Timestamps

created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()

You'll need these for debugging, analytics, and sync.

Use JSONB for Flexible Data

settings JSONB DEFAULT '{}',
metadata JSONB DEFAULT '{}'

Perfect for user preferences, feature flags, or data that varies between records.

Index Thoughtfully

-- Index foreign keys
CREATE INDEX idx_projects_organization ON projects(organization_id);

-- Index frequently queried columns
CREATE INDEX idx_projects_status ON projects(status) WHERE status != 'archived';

Common Mistakes

• No RLS — Without Row Level Security, one bug exposes all customer data
• N+1 queries — Fetch related data with JOINs, not loops
• Missing indexes — Query gets slow? Check EXPLAIN and add indexes
• Storing files in DB — Use S3/Supabase Storage for files
• No soft delete — Add deleted_at column instead of hard deleting
• Premature sharding — PostgreSQL handles millions of rows. Don't shard until you must.

Migration Strategy

Schema will change. Plan for it:

1. Use a migration tool — Prisma, Drizzle, or raw SQL files with versioning
2. Never delete columns in prod — Add new columns, deprecate old ones
3. Test migrations on copy — Always test against production data copy
4. Make migrations reversible — Can you roll back if something breaks?