PostgreSQL Schema Design for SaaS: Multi-Tenancy, Normalization Decisions, and Indexing Strategy

The database schema is the hardest thing to change after launch. A poor schema design forces expensive migrations, leaks tenant data across boundaries, makes queries slow, and creates endless impedance mismatch between your data model and your domain. Getting it right early pays dividends for years.

This guide covers the core decisions for SaaS database schemas: multi-tenancy strategy, normalization tradeoffs, indexing, and the structural patterns every SaaS app needs.

Multi-Tenancy Strategy

Option A: Shared Schema (Recommended for Most SaaS)

All tenants share the same tables. Every table has a workspace_id (or organization_id) foreign key. Row-level security enforces isolation.

-- Every resource table carries workspace_id
CREATE TABLE projects (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name         TEXT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'active',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at   TIMESTAMPTZ  -- Soft delete
);

-- Composite index: workspace first, always
-- Most queries filter by workspace_id first
CREATE INDEX idx_projects_workspace ON projects(workspace_id, status) WHERE deleted_at IS NULL;

Pros: Simple operations, no migration fan-out, shared connection pool. Cons: Requires RLS or application-level filtering; cross-tenant queries are possible if you forget WHERE workspace_id = ?.

Option B: Schema-Per-Tenant

Each tenant gets a dedicated PostgreSQL schema (CREATE SCHEMA tenant_abc). All tables are identical across schemas.

-- Create tenant schema
CREATE SCHEMA tenant_abc;
CREATE TABLE tenant_abc.projects (
  id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  ...
);

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

Pros: Strong isolation; easy to backup/restore a single tenant; no RLS needed. Cons: Schema migrations run N times (one per tenant); connection pooling is harder; >1,000 tenants becomes unwieldy.

Recommendation: Shared schema up to ~10,000 tenants with RLS; schema-per-tenant only when contractual isolation is required (enterprise deals, regulated data).

Core SaaS Schema

-- Workspaces (tenant root)
CREATE TABLE workspaces (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name         TEXT NOT NULL,
  slug         TEXT NOT NULL UNIQUE,
  plan         TEXT NOT NULL DEFAULT 'free',  -- 'free' | 'pro' | 'enterprise'
  stripe_customer_id TEXT UNIQUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Users (global — one user can belong to multiple workspaces)
CREATE TABLE users (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email          TEXT NOT NULL UNIQUE,
  name           TEXT NOT NULL,
  email_verified BOOLEAN NOT NULL DEFAULT FALSE,
  password_hash  TEXT,           -- NULL for OAuth-only accounts
  avatar_url     TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_active_at TIMESTAMPTZ
);

CREATE INDEX idx_users_email ON users(lower(email));  -- Case-insensitive lookup

-- Workspace membership (many-to-many)
CREATE TABLE workspace_members (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  user_id      UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role         TEXT NOT NULL DEFAULT 'member',  -- 'owner' | 'admin' | 'member'
  joined_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (workspace_id, user_id)
);

CREATE INDEX idx_workspace_members_user ON workspace_members(user_id);
CREATE INDEX idx_workspace_members_workspace ON workspace_members(workspace_id, role);

-- Invitations
CREATE TABLE workspace_invites (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  email        TEXT NOT NULL,
  role         TEXT NOT NULL DEFAULT 'member',
  token        TEXT NOT NULL UNIQUE,  -- Random secure token
  invited_by   UUID NOT NULL REFERENCES users(id),
  accepted_at  TIMESTAMPTZ,
  expires_at   TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '7 days',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (workspace_id, email)  -- One pending invite per email per workspace
);

CREATE INDEX idx_workspace_invites_token ON workspace_invites(token) WHERE accepted_at IS NULL;

Normalization Tradeoffs

The goal is not "maximum normalization" or "minimum normalization" — it's making the right tradeoffs for your query patterns.

Normalize: User and Workspace Identity

-- ✅ Normalize: don't store user name on every resource
-- Wrong: SELECT p.*, p.created_by_name FROM projects p
-- Right: JOIN users u ON u.id = p.created_by_user_id

CREATE TABLE projects (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id   UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name           TEXT NOT NULL,
  description    TEXT,
  created_by     UUID NOT NULL REFERENCES users(id),
  assignee_id    UUID REFERENCES users(id),  -- Nullable FK
  status         TEXT NOT NULL DEFAULT 'active',
  priority       TEXT NOT NULL DEFAULT 'medium',
  due_date       DATE,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at     TIMESTAMPTZ
);

Denormalize: Computed Aggregates

-- ✅ Denormalize: don't recompute counts on every page load
-- Store running counts on the parent record
ALTER TABLE workspaces ADD COLUMN project_count INTEGER NOT NULL DEFAULT 0;
ALTER TABLE workspaces ADD COLUMN member_count  INTEGER NOT NULL DEFAULT 1;

-- Keep in sync via trigger
CREATE OR REPLACE FUNCTION update_workspace_project_count()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' AND NEW.deleted_at IS NULL THEN
    UPDATE workspaces SET project_count = project_count + 1 WHERE id = NEW.workspace_id;
  ELSIF TG_OP = 'UPDATE' AND OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL THEN
    UPDATE workspaces SET project_count = project_count - 1 WHERE id = NEW.workspace_id;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_workspace_project_count
AFTER INSERT OR UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION update_workspace_project_count();

Store Snapshots: Financial Records

-- ✅ Denormalize for financial records: snapshot the price at transaction time
-- Never JOIN to current product price for historical invoices
CREATE TABLE invoice_line_items (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_id   UUID NOT NULL REFERENCES invoices(id),
  product_id   UUID REFERENCES products(id),          -- Can be NULL if product deleted
  product_name TEXT NOT NULL,                         -- Snapshot at time of invoice
  unit_price   INTEGER NOT NULL,                      -- Cents, snapshot
  quantity     INTEGER NOT NULL DEFAULT 1,
  amount       INTEGER NOT NULL,                      -- unit_price × quantity
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Soft Deletes

-- Soft delete pattern: add deleted_at to every resource table
-- Partial index excludes deleted rows from most queries (much smaller index)
CREATE INDEX idx_projects_workspace_active ON projects(workspace_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- Hard delete cleanup: run nightly for GDPR compliance (>90 days after soft delete)
DELETE FROM projects WHERE deleted_at < NOW() - INTERVAL '90 days';

-- Query pattern: always filter deleted_at in application queries
-- In Prisma: use middleware to auto-add where: { deletedAt: null }

Indexing Strategy

-- Rule 1: Always index foreign keys that you JOIN or filter on
-- PostgreSQL does NOT auto-index FKs (unlike MySQL)
CREATE INDEX idx_projects_workspace    ON projects(workspace_id);
CREATE INDEX idx_projects_assignee     ON projects(assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX idx_projects_created_by   ON projects(created_by);

-- Rule 2: Composite indexes: put equality columns first, range columns last
-- Query: WHERE workspace_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX idx_projects_ws_status_created
  ON projects(workspace_id, status, created_at DESC)
  WHERE deleted_at IS NULL;

-- Rule 3: Partial indexes for common filtered queries (smaller, faster)
CREATE INDEX idx_invites_pending ON workspace_invites(workspace_id, email)
  WHERE accepted_at IS NULL AND expires_at > NOW();
-- Caution: NOW() in index definition doesn't work — use a trigger or omit

-- Rule 4: GIN index for full-text search
ALTER TABLE projects ADD COLUMN search_vector TSVECTOR
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
  ) STORED;
CREATE INDEX idx_projects_search ON projects USING GIN(search_vector);

-- Rule 5: Don't over-index — each index slows INSERT/UPDATE
-- Check unused indexes:
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

Updated_at Trigger

-- Auto-update updated_at on every change
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

-- Apply to every table that has updated_at
CREATE TRIGGER trg_projects_updated_at
BEFORE UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Row-Level Security

-- Enable RLS for shared-schema multi-tenancy
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Application sets this at the start of each request
-- SET LOCAL app.current_workspace_id = '<workspace_id>';

CREATE POLICY projects_workspace_isolation ON projects
  USING (workspace_id = current_setting('app.current_workspace_id')::uuid);

-- GRANT: app user can read/write but not bypass RLS
-- (BYPASSRLS is only for the superuser or migration role)
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user;

Prisma: Workspace-Aware Middleware

// lib/prisma.ts — auto-inject workspace_id into queries
import { PrismaClient } from "@prisma/client";

const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === "development" ? ["query", "warn", "error"] : ["error"],
  });

if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

// Soft delete middleware: auto-filter deleted records
prisma.$use(async (params, next) => {
  const SOFT_DELETE_MODELS = ["Project", "Task", "Comment"];

  if (SOFT_DELETE_MODELS.includes(params.model ?? "")) {
    if (params.action === "findMany" || params.action === "findFirst") {
      params.args.where = {
        ...params.args.where,
        deletedAt: null,
      };
    }
    if (params.action === "delete") {
      params.action = "update";
      params.args.data = { deletedAt: new Date() };
    }
  }
  return next(params);
});

Common Anti-Patterns

-- ❌ ANTI-PATTERN 1: EAV (Entity-Attribute-Value) for dynamic fields
-- Nearly always a mistake — impossible to query efficiently
CREATE TABLE attributes (entity_id UUID, key TEXT, value TEXT);
-- ✅ Use JSONB for flexible attributes instead
ALTER TABLE projects ADD COLUMN custom_fields JSONB NOT NULL DEFAULT '{}';
CREATE INDEX idx_projects_custom ON projects USING GIN(custom_fields);

-- ❌ ANTI-PATTERN 2: Storing arrays as comma-separated strings
-- tags TEXT = 'react,typescript,nextjs'
-- ✅ Use PostgreSQL array type or a junction table
ALTER TABLE projects ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
CREATE INDEX idx_projects_tags ON projects USING GIN(tags);
-- Query: WHERE 'react' = ANY(tags)

-- ❌ ANTI-PATTERN 3: No updated_at — can't build change detection or sync
-- ✅ Every mutable table gets created_at + updated_at

-- ❌ ANTI-PATTERN 4: Storing user roles as a text field without enum validation
-- status TEXT = 'actve' (typo — no constraint catches it)
-- ✅ PostgreSQL CHECK constraint or application-level validation
ALTER TABLE workspace_members ADD CONSTRAINT chk_role
  CHECK (role IN ('owner', 'admin', 'member'));

Cost and Timeline Estimates

Schema design itself is free — the cost is developer time:

PostgreSQL hosting (2026):

• Supabase free: 500MB storage, pauses after inactivity
• Supabase Pro: $25/month — 8GB storage, no pause
• Neon: $19/month — serverless, auto-scale
• RDS PostgreSQL: $70–200/month for db.t4g.medium with Multi-AZ

See Also

• PostgreSQL Row-Level Security for Multi-Tenancy
• PostgreSQL Schema Migrations with Prisma
• PostgreSQL Triggers and Audit Logging
• SaaS Multi-Workspace Architecture
• Prisma Advanced Patterns and Extensions

---

Working With Viprasol

The schema decisions you make in week one affect your team's velocity for years. Our team reviews your domain model, identifies the right multi-tenancy strategy, designs composite indexes for your actual query patterns, and implements RLS to enforce isolation at the database level — not just the application level.

What we deliver:

• Multi-tenant schema with workspaces → members → resources hierarchy
• Composite indexes matched to your actual query patterns
• Soft delete with partial indexes (excludes deleted rows from index)
• Row-level security with SET LOCAL app.current_workspace_id
• Prisma middleware: soft-delete filter + workspace auto-injection
• Normalization decisions documented with rationale

Talk to our team about your database schema architecture →

Or explore our SaaS development services.