SaaS Multi Tenant Schema — Lab

Diagram Views

erDiagram users ||--o{ organization_members : belongs_to organizations ||--o{ organization_members : has organizations ||--o{ roles : defines organization_members ||--o{ member_roles : maps roles ||--o{ member_roles : grants organizations ||--|| subscriptions : billed_with subscriptions ||--o{ invoices : emits users { bigint id PK text email } organizations { bigint id PK text slug text name text plan } organization_members { bigint id PK bigint organization_id FK bigint user_id FK } roles { bigint id PK bigint organization_id FK text key } member_roles { bigint member_id PK,FK bigint role_id PK,FK } subscriptions { bigint id PK bigint organization_id FK text status } invoices { bigint id PK bigint subscription_id FK int amount_cents text status }

SQL Snippets

schema.sql

create table users (
  id bigint primary key generated always as identity,
  email text not null unique,
  full_name text not null,
  created_at timestamptz not null default now()
);

create table organizations (
  id bigint primary key generated always as identity,
  slug text not null unique,
  name text not null,
  plan text not null check (plan in ('free','pro','enterprise')),
  created_at timestamptz not null default now()
);

create table organization_members (
  id bigint primary key generated always as identity,
  organization_id bigint not null references organizations(id) on delete cascade,
  user_id bigint not null references users(id) on delete cascade,
  joined_at timestamptz not null default now(),
  unique (organization_id, user_id)
);

create table roles (
  id bigint primary key generated always as identity,
  organization_id bigint not null references organizations(id) on delete cascade,
  key text not null,
  description text,
  unique (organization_id, key)
);

create table member_roles (
  member_id bigint not null references organization_members(id) on delete cascade,
  role_id bigint not null references roles(id) on delete cascade,
  assigned_at timestamptz not null default now(),
  primary key (member_id, role_id)
);

create table subscriptions (
  id bigint primary key generated always as identity,
  organization_id bigint not null unique references organizations(id) on delete cascade,
  provider text not null,
  provider_ref text not null,
  status text not null check (status in ('trialing','active','past_due','cancelled')),
  current_period_end timestamptz,
  created_at timestamptz not null default now()
);

create table invoices (
  id bigint primary key generated always as identity,
  subscription_id bigint not null references subscriptions(id) on delete cascade,
  amount_cents integer not null check (amount_cents >= 0),
  status text not null check (status in ('open','paid','failed','void')),
  issued_at timestamptz not null default now(),
  paid_at timestamptz
);

create index idx_org_members_user on organization_members(user_id);
create index idx_invoices_subscription_issued on invoices(subscription_id, issued_at desc);

seed.sql

insert into users (email, full_name)
values
  ('owner@acme.dev', 'Acme Owner'),
  ('admin@acme.dev', 'Acme Admin'),
  ('analyst@acme.dev', 'Acme Analyst');

insert into organizations (slug, name, plan)
values
  ('acme', 'Acme Inc', 'pro');

insert into organization_members (organization_id, user_id)
values
  (1, 1),
  (1, 2),
  (1, 3);

insert into roles (organization_id, key, description)
values
  (1, 'owner', 'Organization owner'),
  (1, 'admin', 'Workspace administrator'),
  (1, 'viewer', 'Read only access');

insert into member_roles (member_id, role_id)
values
  (1, 1),
  (2, 2),
  (3, 3);

insert into subscriptions (organization_id, provider, provider_ref, status, current_period_end)
values
  (1, 'stripe', 'sub_001', 'active', now() + interval '30 days');

insert into invoices (subscription_id, amount_cents, status, issued_at, paid_at)
values
  (1, 9900, 'paid', now() - interval '10 days', now() - interval '9 days'),
  (1, 9900, 'open', now(), null);

queries.sql

-- 1) Members and roles in a tenant
select
  o.slug as org,
  u.email,
  r.key as role
from organization_members om
join organizations o on o.id = om.organization_id
join users u on u.id = om.user_id
left join member_roles mr on mr.member_id = om.id
left join roles r on r.id = mr.role_id
where o.slug = 'acme'
order by u.email;

-- 2) Open invoices per organization
select
  o.name,
  count(i.id) as open_invoices,
  coalesce(sum(i.amount_cents), 0) / 100.0 as open_amount
from organizations o
join subscriptions s on s.organization_id = o.id
left join invoices i on i.subscription_id = s.id and i.status = 'open'
group by o.id
order by open_amount desc;

-- 3) Active subscriptions with next renewal date
select
  o.slug,
  s.status,
  s.current_period_end
from subscriptions s
join organizations o on o.id = s.organization_id
where s.status in ('trialing', 'active')
order by s.current_period_end;