Multi-Tenant SaaS Database Schema

Reviewed by the Free ER Diagram maintainers. Updated September 2, 2026. Read our editorial policy.

Worked example · Updated September 2, 2026

A business-to-business SaaS application usually has people who belong to organizations, permissions that differ by organization, tenant-owned resources, and a billing relationship. Putting organization_id on every table is only the start. The schema also needs constraints that make tenant boundaries hard to cross accidentally.

Security note: tenant isolation must be enforced in queries, authorization code, and preferably database policies. An ER diagram documents ownership but does not enforce access by itself.

Model Overview

SQL DDL

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  display_name VARCHAR(160) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE organizations (
  id BIGINT PRIMARY KEY,
  slug VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE memberships (
  organization_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  role VARCHAR(30) NOT NULL,
  joined_at TIMESTAMP NOT NULL,
  PRIMARY KEY (organization_id, user_id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE projects (
  id BIGINT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  project_key VARCHAR(40) NOT NULL,
  name VARCHAR(200) NOT NULL,
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  UNIQUE (organization_id, project_key),
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE subscriptions (
  id BIGINT PRIMARY KEY,
  organization_id BIGINT NOT NULL UNIQUE,
  provider_customer_id VARCHAR(120) NOT NULL UNIQUE,
  plan_code VARCHAR(60) NOT NULL,
  status VARCHAR(30) NOT NULL,
  current_period_end TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id)
);

CREATE TABLE invoices (
  id BIGINT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  subscription_id BIGINT NOT NULL,
  provider_invoice_id VARCHAR(120) NOT NULL UNIQUE,
  amount_due DECIMAL(12,2) NOT NULL,
  currency CHAR(3) NOT NULL,
  status VARCHAR(30) NOT NULL,
  issued_at TIMESTAMP NOT NULL,
  paid_at TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (subscription_id) REFERENCES subscriptions(id)
);

CREATE TABLE audit_events (
  id BIGINT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  actor_user_id BIGINT,
  action VARCHAR(80) NOT NULL,
  target_type VARCHAR(80) NOT NULL,
  target_id BIGINT,
  occurred_at TIMESTAMP NOT NULL,
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (actor_user_id) REFERENCES users(id)
);

Relationship Decisions

Memberships separates identity from tenancy

The memberships join table creates a many-to-many relationship between users and organizations. Role belongs on the membership because the same user may be an owner in one tenant and a viewer in another. The composite primary key ensures that a person has one active membership row per organization. Applications that need invitations should model invitations separately so an unaccepted email address is not mistaken for an authenticated user.

Tenant-owned keys should be scoped

Project keys only need to be unique inside an organization, so the unique constraint includes organization_id. This is more useful than making project_key globally unique and documents the true business rule. Child tables below projects should carry organization_id as well when row-level security or tenant-partitioned indexes are important. Composite foreign keys can then guarantee that a child and its project belong to the same organization.

Billing history is not application state

The subscription row describes the current commercial relationship, while invoices preserve individual billing events. Amount and currency are stored on each invoice because plans and exchange arrangements change. Webhook handlers should use the provider invoice identifier as an idempotency boundary. Never delete paid invoices when a subscription is cancelled.

Audit actors may become unavailable

The actor foreign key is nullable so system jobs and deleted identities can still produce an audit event. In stricter systems, preserve an actor label or service identity snapshot on the event. Audit records should be append-only and subject to a documented retention policy.

Queries, Indexes, and Isolation

Most product queries should begin with organization_id, so index projects and other tenant-owned tables with that column first. Membership lookup needs both directions: the primary key supports organization-to-user queries, while a second index on user_id supports listing a user's organizations. Billing dashboards benefit from indexes on invoices(organization_id, issued_at) and invoices(status, issued_at).

Every authenticated request should establish the active organization and verify membership before reading tenant data. Avoid accepting an organization ID from a request and trusting it without an authorization check. PostgreSQL row-level security can add a database boundary, but its session context and background-job behavior require careful tests. Also test caches, object storage paths, exports, logs, and analytics because isolation failures often occur outside the main relational query.

Review This Model in the Diagram Tool

Paste the DDL into Free ER Diagram and group tables into Identity, Product, Billing, and Operations. Confirm that memberships has two parent relationships, projects has both an organization owner and a creator, subscriptions is one-to-zero-or-one per organization, and invoices remains one-to-many. Then compare the generated diagram with the authorization paths in your application.