SaaS Database ER Diagram

A SaaS product almost always starts with the same skeleton: users, workspaces (teams), membership with roles, projects, tasks, and billing through subscriptions and invoices. Workspace membership is a classic many-to-many between users and workspaces, modeled here with an explicit role column on the join table.

This schema draws the billing boundary around the workspace, not the user. workspace_members is a join table between users and workspaces, and its composite primary key (workspace_id, user_id) does two things: it makes the pair unique, and it carries role as an attribute of the membership itself — being an admin is a property of you-in-that-workspace, not of you. workspaces.owner_id is a deliberate denormalization: the founder's identity is needed even before any membership row exists, and it survives role changes on the members list.

The billing side reads as a vertical chain. subscriptions.workspace_id is UNIQUE, so a workspace has exactly one subscription, and plan changes are updates to that single row rather than inserts. invoices hang below with a plain one-to-many — one subscription, many invoices over time. On the product side, projects and tasks form a deep one-to-many chain, and tasks.assignee_id is nullable: a task can sit in the backlog with nobody on it. comments attach to tasks rather than projects, which keeps the discussion anchored to work items.

Delete behavior follows the money. Projects cascade to tasks and tasks to comments, so removing a project is one statement. But subscriptions references workspaces with no rule: a workspace that has ever been billed cannot simply be dropped, which is intentional — subscription and invoice rows are financial records that outlive the product data. The same logic protects users: tasks.assignee_id and comments.author_id reference users without any ON DELETE rule, so deleting a user who authored or owns anything fails. Production SaaS products usually soft-delete accounts instead.

What is missing first is invitations: workspace_members can only reference users who already exist, so onboarding a teammate needs an invitations table (workspace, email, token, expiry, intended role) that converts into a membership row on acceptance. The second gap is per-workspace history — an audit_log table keyed by workspace records who changed what, which matters the moment workspaces are shared. And if plans carry seat or feature limits, a plans table referenced by subscriptions.plan (currently a free-text VARCHAR) takes hardcoded plan names out of application code.

The diagram

Tables (8)

Relationships (10)

• 1:Nusers.id→workspaces.owner_id
• 1:1workspaces.id→workspace_members.workspace_idON DELETE CASCADE
• 1:1users.id→workspace_members.user_idON DELETE CASCADE
• 1:1workspaces.id→subscriptions.workspace_id
• 1:Nsubscriptions.id→invoices.subscription_id
• 1:Nworkspaces.id→projects.workspace_idON DELETE CASCADE
• 1:Nprojects.id→tasks.project_idON DELETE CASCADE
• 1:Nusers.id→tasks.assignee_id
• 1:Ntasks.id→comments.task_idON DELETE CASCADE
• 1:Nusers.id→comments.author_id

The SQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE workspaces (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  owner_id INTEGER NOT NULL REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE workspace_members (
  workspace_id INTEGER NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role VARCHAR(20) NOT NULL DEFAULT 'member',
  PRIMARY KEY (workspace_id, user_id)
);

CREATE TABLE subscriptions (
  id SERIAL PRIMARY KEY,
  workspace_id INTEGER NOT NULL UNIQUE REFERENCES workspaces(id),
  plan VARCHAR(30) NOT NULL DEFAULT 'free',
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  current_period_end TIMESTAMP
);

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  subscription_id INTEGER NOT NULL REFERENCES subscriptions(id),
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  issued_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  workspace_id INTEGER NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name VARCHAR(120) NOT NULL,
  description TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  assignee_id INTEGER REFERENCES users(id),
  title VARCHAR(200) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'todo',
  due_date DATE,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  author_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides