Multi-Tenant SaaS Database Schema (Shared Database, Tenant Isolation)

Database Schemas · er diagram · CC-BY-4.0

ER diagram for a B2B SaaS schema using the shared-database, tenant-id pattern — tenants own users, workspaces, projects, documents, subscriptions, and audit logs.

Source: https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/
Curated by Archigram editorial
postgres saas multi-tenant rls schema-design stripe

Mermaid source

erDiagram
    TENANT ||--o{ USER : has
    TENANT ||--o{ WORKSPACE : owns
    WORKSPACE ||--o{ PROJECT : contains
    PROJECT ||--o{ DOCUMENT : contains
    USER ||--o{ MEMBERSHIP : has
    WORKSPACE ||--o{ MEMBERSHIP : has
    USER ||--o{ AUDIT_LOG : creates
    TENANT ||--o{ SUBSCRIPTION : has
    SUBSCRIPTION ||--o{ INVOICE : produces

    TENANT {
        uuid id PK
        string name
        string plan_tier
        timestamp created_at
        timestamp deleted_at
    }
    USER {
        uuid id PK
        uuid tenant_id FK
        string email UK
        string display_name
        timestamp created_at
    }
    WORKSPACE {
        uuid id PK
        uuid tenant_id FK
        string name
        string slug
        timestamp created_at
    }
    MEMBERSHIP {
        uuid id PK
        uuid user_id FK
        uuid workspace_id FK
        string role
        timestamp joined_at
    }
    PROJECT {
        uuid id PK
        uuid workspace_id FK
        string name
        timestamp created_at
    }
    DOCUMENT {
        uuid id PK
        uuid project_id FK
        string title
        jsonb content
        timestamp updated_at
    }
    SUBSCRIPTION {
        uuid id PK
        uuid tenant_id FK
        string stripe_sub_id
        string status
        timestamp current_period_end
    }
    INVOICE {
        uuid id PK
        uuid subscription_id FK
        int amount_cents
        string status
        timestamp issued_at
    }
    AUDIT_LOG {
        uuid id PK
        uuid tenant_id FK
        uuid user_id FK
        string action
        jsonb metadata
        timestamp at
    }

What this diagram shows

A canonical multi-tenant SaaS data model. Every business object (user, workspace, project, document, audit log) carries a tenant_id foreign key so a single shared Postgres database can serve all tenants while enforcing row-level isolation. Workspaces sit between tenants and projects to model the most common product hierarchy (Stripe-style: Account → Workspace → Project → Resource). Memberships are a many-to-many join between users and workspaces with a role column. Subscriptions and invoices integrate with Stripe. Audit logs are append-only and indexed by tenant + user + time.

When to use it

Use the shared-database pattern (this diagram) when you have hundreds-to-thousands of tenants of similar size, want simple operations, and trust application-level or row-level-security tenant filters. Switch to a database-per-tenant pattern only when you need strong physical isolation (regulated industries, very large per-tenant data, noisy-neighbor risk) or when individual tenants demand it contractually.

How to adapt it for your project

For Postgres, enforce isolation with Row Level Security policies on tenant_id rather than relying solely on application code (defense in depth). For very large tenants, partition the largest tables (documents, audit_logs) by tenant_id range or hash. If you outgrow Postgres write throughput, add a citus extension and shard by tenant_id — this schema is designed for clean sharding. For Lite/Free plans, drop the workspace layer and let projects belong directly to the tenant. Add soft-delete (deleted_at) to every table you might want to undelete.

Key concepts

Related diagrams