SaaS subscription billing data model

This example models how a SaaS company bills its customers: organizations and their users, a catalog of plans with prices per currency and interval, subscriptions made of items (seats, flat fees or metered usage), the usage reported against them, and the invoices and payments each billing period produces.

#Design notes

• Plans and prices are separate. A plan is what you sell (Team, Business); a price is how much, in which currency, how often and on which model (flat, per seat, metered, tiered). Prices are never edited, only replaced, so old invoices stay explainable.
• A subscription has items. Each item is one price with a quantity: 25 seats of Team monthly, plus a metered add-on. Upgrades change items rather than the subscription itself.
• Usage is recorded, not summed in place. usage_record keeps every report with an idempotency key; invoicing sums it for the period.
• Invoices copy amounts. Invoice lines store quantity, unit amount and amount as billed, and flag prorations from mid-period changes.
• Payments are attempts. Failed attempts are kept with their reason, which drives dunning and the PAST_DUE status.
• Status, interval and pricing model are reference lists with their values.

To extend it: add coupon and discount, credit_note, tax_rate, payment_method (stored as a provider token), or entitlement for feature flags per plan.

#Diagram

SaaS Subscription Billing: 10 entities, 6 reference lists and 11 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

Open this model in EntityModel.com to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or download model.json.

#Entities

#Attributes

#Invoice

A bill for one billing period of a subscription, or a one-off charge.

#Invoice Line

A charge on an invoice: a subscription item for the period, prorations, or usage.

#Organization

A customer company or team that subscribes. The billing entity.

#Payment

A payment attempt for an invoice through the payment provider.

#Plan

A product tier customers can subscribe to, such as Team or Business.

#Price

A price for a plan in one currency and billing interval. Prices are never edited; a new price replaces an old one.

#Subscription

An organization's subscription. Its items say which prices it pays and in what quantity.

#Subscription Item

One price on a subscription with its quantity, such as 25 seats of Team monthly.

#Usage Record

Metered usage reported against a subscription item, summed at the end of each period.

#User

A person who signs in. Belongs to one organization; seat-based plans count active users.

#Billing Interval

How often a price is billed.

#Country

Countries, identified by their ISO 3166-1 alpha-2 code.

#Currency

Currencies, identified by their ISO 4217 code.

#Invoice Status

Lifecycle states of an invoice.

#Pricing Model

How a price turns into an amount.

#Subscription Status

Lifecycle states of a subscription.

#Relationships

#Reference data

#Billing Interval

3 values · Source: Example values

#Country

6 values · Source: ISO 3166-1 (excerpt)

#Currency

5 values · Source: ISO 4217 (excerpt)

#Invoice Status

5 values · Source: Example values

#Pricing Model

4 values · Source: Example values

#Subscription Status

5 values · Source: Example values

#SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt schema.yml and data contracts.

-- SaaS Subscription Billing — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

-- Reference list: Subscription Status
CREATE TABLE subscription_status (
  status_code TEXT NOT NULL,
  status_name TEXT NOT NULL,
  PRIMARY KEY (status_code)
);
COMMENT ON TABLE subscription_status IS 'Lifecycle states of a subscription.';

-- Reference list: Invoice Status
CREATE TABLE invoice_status (
  status_code TEXT NOT NULL,
  status_name TEXT NOT NULL,
  PRIMARY KEY (status_code)
);
COMMENT ON TABLE invoice_status IS 'Lifecycle states of an invoice.';

-- Reference list: Billing Interval
CREATE TABLE billing_interval (
  interval_code TEXT NOT NULL,
  interval_name TEXT NOT NULL,
  months INTEGER NOT NULL,
  PRIMARY KEY (interval_code)
);
COMMENT ON TABLE billing_interval IS 'How often a price is billed.';

-- Reference list: Pricing Model
CREATE TABLE pricing_model (
  model_code TEXT NOT NULL,
  model_name TEXT NOT NULL,
  PRIMARY KEY (model_code)
);
COMMENT ON TABLE pricing_model IS 'How a price turns into an amount.';

-- Reference list: Country
CREATE TABLE country (
  country_code TEXT NOT NULL,
  country_name TEXT NOT NULL,
  PRIMARY KEY (country_code)
);
COMMENT ON TABLE country IS 'Countries, identified by their ISO 3166-1 alpha-2 code.';
COMMENT ON COLUMN country.country_code IS 'ISO 3166-1 alpha-2 code.';
COMMENT ON COLUMN country.country_name IS 'English short name.';

-- Reference list: Currency
CREATE TABLE currency (
  currency_code TEXT NOT NULL,
  currency_name TEXT NOT NULL,
  PRIMARY KEY (currency_code)
);
COMMENT ON TABLE currency IS 'Currencies, identified by their ISO 4217 code.';
COMMENT ON COLUMN currency.currency_code IS 'ISO 4217 code.';

CREATE TABLE organization (
  organization_id BIGINT NOT NULL,
  organization_name TEXT NOT NULL,
  billing_email TEXT NOT NULL,
  country_code TEXT NOT NULL,
  tax_id TEXT,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (organization_id)
);
COMMENT ON TABLE organization IS 'A customer company or team that subscribes. The billing entity.';
COMMENT ON COLUMN organization.country_code IS 'Drives tax.';
COMMENT ON COLUMN organization.tax_id IS 'VAT or tax number.';

CREATE TABLE app_user (
  user_id BIGINT NOT NULL,
  organization_id BIGINT NOT NULL,
  email TEXT NOT NULL,
  full_name TEXT,
  role TEXT NOT NULL,
  is_active BOOLEAN NOT NULL,
  PRIMARY KEY (user_id)
);
COMMENT ON TABLE app_user IS 'A person who signs in. Belongs to one organization; seat-based plans count active users.';
COMMENT ON COLUMN app_user.role IS 'Owner, admin or member.';

CREATE TABLE plan (
  plan_id INTEGER NOT NULL,
  plan_code TEXT NOT NULL,
  plan_name TEXT NOT NULL,
  is_public BOOLEAN NOT NULL,
  PRIMARY KEY (plan_id)
);
COMMENT ON TABLE plan IS 'A product tier customers can subscribe to, such as Team or Business.';
COMMENT ON COLUMN plan.is_public IS 'Shown on the pricing page.';

CREATE TABLE plan_price (
  price_id INTEGER NOT NULL,
  plan_id INTEGER NOT NULL,
  currency_code TEXT NOT NULL,
  billing_interval_code TEXT NOT NULL,
  pricing_model TEXT NOT NULL,
  unit_amount NUMERIC(12,2) NOT NULL,
  is_active BOOLEAN NOT NULL,
  PRIMARY KEY (price_id)
);
COMMENT ON TABLE plan_price IS 'A price for a plan in one currency and billing interval. Prices are never edited; a new price replaces an old one.';
COMMENT ON COLUMN plan_price.unit_amount IS 'Price per unit (seat, unit of usage or flat).';

CREATE TABLE subscription (
  subscription_id BIGINT NOT NULL,
  organization_id BIGINT NOT NULL,
  subscription_status_code TEXT NOT NULL,
  started_at TIMESTAMP NOT NULL,
  current_period_start DATE NOT NULL,
  current_period_end DATE NOT NULL,
  trial_ends_at TIMESTAMP,
  cancel_at_period_end BOOLEAN NOT NULL,
  canceled_at TIMESTAMP,
  PRIMARY KEY (subscription_id)
);
COMMENT ON TABLE subscription IS 'An organization''s subscription. Its items say which prices it pays and in what quantity.';

CREATE TABLE subscription_item (
  subscription_item_id BIGINT NOT NULL,
  subscription_id BIGINT NOT NULL,
  price_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (subscription_item_id)
);
COMMENT ON TABLE subscription_item IS 'One price on a subscription with its quantity, such as 25 seats of Team monthly.';
COMMENT ON COLUMN subscription_item.quantity IS 'Seats or units; 1 for flat and metered prices.';

CREATE TABLE usage_record (
  usage_record_id BIGINT NOT NULL,
  subscription_item_id BIGINT NOT NULL,
  quantity BIGINT NOT NULL,
  recorded_at TIMESTAMP NOT NULL,
  idempotency_key TEXT NOT NULL,
  PRIMARY KEY (usage_record_id)
);
COMMENT ON TABLE usage_record IS 'Metered usage reported against a subscription item, summed at the end of each period.';
COMMENT ON COLUMN usage_record.idempotency_key IS 'Stops duplicate reports.';

CREATE TABLE invoice (
  invoice_id BIGINT NOT NULL,
  invoice_number TEXT NOT NULL,
  organization_id BIGINT NOT NULL,
  subscription_id BIGINT,
  invoice_status_code TEXT NOT NULL,
  currency_code TEXT NOT NULL,
  period_start DATE,
  period_end DATE,
  subtotal_amount NUMERIC(12,2) NOT NULL,
  tax_amount NUMERIC(12,2) NOT NULL,
  total_amount NUMERIC(12,2) NOT NULL,
  issued_at TIMESTAMP NOT NULL,
  due_at TIMESTAMP NOT NULL,
  PRIMARY KEY (invoice_id)
);
COMMENT ON TABLE invoice IS 'A bill for one billing period of a subscription, or a one-off charge.';

CREATE TABLE invoice_line (
  invoice_line_id BIGINT NOT NULL,
  invoice_id BIGINT NOT NULL,
  subscription_item_id BIGINT,
  description TEXT NOT NULL,
  quantity BIGINT NOT NULL,
  unit_amount NUMERIC(12,2) NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  is_proration BOOLEAN NOT NULL,
  PRIMARY KEY (invoice_line_id)
);
COMMENT ON TABLE invoice_line IS 'A charge on an invoice: a subscription item for the period, prorations, or usage.';
COMMENT ON COLUMN invoice_line.is_proration IS 'Credit or charge for a mid-period change.';

CREATE TABLE payment (
  payment_id BIGINT NOT NULL,
  invoice_id BIGINT NOT NULL,
  provider_reference TEXT NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  succeeded BOOLEAN NOT NULL,
  attempted_at TIMESTAMP NOT NULL,
  failure_reason TEXT,
  PRIMARY KEY (payment_id)
);
COMMENT ON TABLE payment IS 'A payment attempt for an invoice through the payment provider.';
COMMENT ON COLUMN payment.provider_reference IS 'Payment provider''s charge ID.';

-- Keys between tables
ALTER TABLE app_user ADD CONSTRAINT uq_app_user_email UNIQUE (email);
ALTER TABLE plan ADD CONSTRAINT uq_plan_plan_code UNIQUE (plan_code);
ALTER TABLE usage_record ADD CONSTRAINT uq_usage_record_idempotency_key UNIQUE (idempotency_key);
ALTER TABLE invoice ADD CONSTRAINT uq_invoice_invoice_number UNIQUE (invoice_number);
ALTER TABLE organization ADD CONSTRAINT fk_organization_country_code FOREIGN KEY (country_code) REFERENCES country (country_code);
ALTER TABLE plan_price ADD CONSTRAINT fk_plan_price_currency_code FOREIGN KEY (currency_code) REFERENCES currency (currency_code);
ALTER TABLE plan_price ADD CONSTRAINT fk_plan_price_billing_interval_code FOREIGN KEY (billing_interval_code) REFERENCES billing_interval (interval_code);
ALTER TABLE plan_price ADD CONSTRAINT fk_plan_price_pricing_model FOREIGN KEY (pricing_model) REFERENCES pricing_model (model_code);
ALTER TABLE subscription ADD CONSTRAINT fk_subscription_subscription_status_code FOREIGN KEY (subscription_status_code) REFERENCES subscription_status (status_code);
ALTER TABLE invoice ADD CONSTRAINT fk_invoice_invoice_status_code FOREIGN KEY (invoice_status_code) REFERENCES invoice_status (status_code);
ALTER TABLE invoice ADD CONSTRAINT fk_invoice_currency_code FOREIGN KEY (currency_code) REFERENCES currency (currency_code);
ALTER TABLE app_user ADD CONSTRAINT fk_member_of FOREIGN KEY (organization_id) REFERENCES organization (organization_id);
ALTER TABLE plan_price ADD CONSTRAINT fk_price_of FOREIGN KEY (plan_id) REFERENCES plan (plan_id);
ALTER TABLE subscription ADD CONSTRAINT fk_subscribed_by FOREIGN KEY (organization_id) REFERENCES organization (organization_id);
ALTER TABLE subscription_item ADD CONSTRAINT fk_item_of FOREIGN KEY (subscription_id) REFERENCES subscription (subscription_id);
ALTER TABLE subscription_item ADD CONSTRAINT fk_item_price FOREIGN KEY (price_id) REFERENCES plan_price (price_id);
ALTER TABLE usage_record ADD CONSTRAINT fk_usage_for FOREIGN KEY (subscription_item_id) REFERENCES subscription_item (subscription_item_id);
ALTER TABLE invoice ADD CONSTRAINT fk_billed_to FOREIGN KEY (organization_id) REFERENCES organization (organization_id);
ALTER TABLE invoice ADD CONSTRAINT fk_bills FOREIGN KEY (subscription_id) REFERENCES subscription (subscription_id);
ALTER TABLE invoice_line ADD CONSTRAINT fk_line_on FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id);
ALTER TABLE invoice_line ADD CONSTRAINT fk_line_for_item FOREIGN KEY (subscription_item_id) REFERENCES subscription_item (subscription_item_id);
ALTER TABLE payment ADD CONSTRAINT fk_pays FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id);

-- Reference values
INSERT INTO subscription_status (status_code, status_name) VALUES
  ('TRIALING', 'Trialing'),
  ('ACTIVE', 'Active'),
  ('PAST_DUE', 'Past due'),
  ('PAUSED', 'Paused'),
  ('CANCELED', 'Canceled');

INSERT INTO invoice_status (status_code, status_name) VALUES
  ('DRAFT', 'Draft'),
  ('OPEN', 'Open'),
  ('PAID', 'Paid'),
  ('VOID', 'Void'),
  ('UNCOLLECTIBLE', 'Uncollectible');

INSERT INTO billing_interval (interval_code, interval_name, months) VALUES
  ('MONTH', 'Monthly', 1),
  ('QUARTER', 'Quarterly', 3),
  ('YEAR', 'Yearly', 12);

INSERT INTO pricing_model (model_code, model_name) VALUES
  ('FLAT', 'Flat fee'),
  ('PER_SEAT', 'Per seat'),
  ('METERED', 'Metered usage'),
  ('TIERED', 'Tiered');

INSERT INTO country (country_code, country_name) VALUES
  ('US', 'United States'),
  ('CA', 'Canada'),
  ('GB', 'United Kingdom'),
  ('DE', 'Germany'),
  ('FR', 'France'),
  ('AU', 'Australia');

INSERT INTO currency (currency_code, currency_name) VALUES
  ('USD', 'US Dollar'),
  ('CAD', 'Canadian Dollar'),
  ('GBP', 'Pound Sterling'),
  ('EUR', 'Euro'),
  ('AUD', 'Australian Dollar');

#Mermaid

Paste into a mermaid code block on GitHub, GitLab or Notion to show the diagram there.

---
title: SaaS Subscription Billing
---
%% Made with EntityModel.com
erDiagram
  organization {
    bigint organization_id PK "Organization ID"
    string organization_name "Organization Name"
    string billing_email "Billing Email"
    string country_code FK "Drives tax."
    string tax_id "VAT or tax number."
    timestamp created_at "Created At"
  }
  app_user {
    bigint user_id PK "User ID"
    bigint organization_id FK "Organization ID"
    string email "Email"
    string full_name "Full Name"
    string role "Owner, admin or member."
    boolean is_active "Is Active"
  }
  plan {
    int plan_id PK "Plan ID"
    string plan_code "Plan Code"
    string plan_name "Plan Name"
    boolean is_public "Shown on the pricing page."
  }
  plan_price {
    int price_id PK "Price ID"
    int plan_id FK "Plan ID"
    string currency_code FK "Currency Code"
    string billing_interval_code FK "Billing Interval Code"
    string pricing_model FK "Pricing Model"
    decimal unit_amount "Price per unit (seat, unit of usage or flat)."
    boolean is_active "Is Active"
  }
  subscription {
    bigint subscription_id PK "Subscription ID"
    bigint organization_id FK "Organization ID"
    string subscription_status_code FK "Subscription Status Code"
    timestamp started_at "Started At"
    date current_period_start "Current Period Start"
    date current_period_end "Current Period End"
    timestamp trial_ends_at "Trial Ends At"
    boolean cancel_at_period_end "Cancel At Period End"
    timestamp canceled_at "Canceled At"
  }
  subscription_item {
    bigint subscription_item_id PK "Subscription Item ID"
    bigint subscription_id FK "Subscription ID"
    int price_id FK "Price ID"
    int quantity "Seats or units; 1 for flat and metered prices."
  }
  usage_record {
    bigint usage_record_id PK "Usage Record ID"
    bigint subscription_item_id FK "Subscription Item ID"
    bigint quantity "Quantity"
    timestamp recorded_at "Recorded At"
    string idempotency_key "Stops duplicate reports."
  }
  invoice {
    bigint invoice_id PK "Invoice ID"
    string invoice_number "Invoice Number"
    bigint organization_id FK "Organization ID"
    bigint subscription_id FK "Subscription ID"
    string invoice_status_code FK "Invoice Status Code"
    string currency_code FK "Currency Code"
    date period_start "Period Start"
    date period_end "Period End"
    decimal subtotal_amount "Subtotal Amount"
    decimal tax_amount "Tax Amount"
    decimal total_amount "Total Amount"
    timestamp issued_at "Issued At"
    timestamp due_at "Due At"
  }
  invoice_line {
    bigint invoice_line_id PK "Invoice Line ID"
    bigint invoice_id FK "Invoice ID"
    bigint subscription_item_id FK "Subscription Item ID"
    string description "Description"
    bigint quantity "Quantity"
    decimal unit_amount "Unit Amount"
    decimal amount "Amount"
    boolean is_proration "Credit or charge for a mid-period change."
  }
  payment {
    bigint payment_id PK "Payment ID"
    bigint invoice_id FK "Invoice ID"
    string provider_reference "Payment provider's charge ID."
    decimal amount "Amount"
    boolean succeeded "Succeeded"
    timestamp attempted_at "Attempted At"
    string failure_reason "Failure Reason"
  }
  subscription_status {
    string status_code PK "Status Code"
    string status_name "Status Name"
  }
  invoice_status {
    string status_code PK "Status Code"
    string status_name "Status Name"
  }
  billing_interval {
    string interval_code PK "Interval Code"
    string interval_name "Interval Name"
    int months "Months"
  }
  pricing_model {
    string model_code PK "Model Code"
    string model_name "Model Name"
  }
  country {
    string country_code PK "ISO 3166-1 alpha-2 code."
    string country_name "English short name."
  }
  currency {
    string currency_code PK "ISO 4217 code."
    string currency_name "Currency Name"
  }
  app_user }o--|| organization : "Member Of"
  plan_price }o--|| plan : "Price Of"
  subscription }o--|| organization : "Held By"
  subscription_item }o--|| subscription : "Item Of"
  subscription_item }o--|| plan_price : "Pays"
  usage_record }o--|| subscription_item : "Reported For"
  invoice }o--|| organization : "Billed To"
  invoice }o--|| subscription : "Bills"
  invoice_line }o--|| invoice : "Line On"
  invoice_line }o--|| subscription_item : "Charges"
  payment }o--|| invoice : "Pays"
  organization }o..|| country : "Country Code"
  plan_price }o..|| currency : "Currency Code"
  plan_price }o..|| billing_interval : "Billing Interval Code"
  plan_price }o..|| pricing_model : "Pricing Model"
  subscription }o..|| subscription_status : "Subscription Status Code"
  invoice }o..|| invoice_status : "Invoice Status Code"
  invoice }o..|| currency : "Currency Code"

#Frequently asked questions

#How do you model subscriptions in a database?

Separate the catalog (plan, price) from what a customer holds (subscription and subscription items with quantities). Generate an invoice per billing period from the items, with invoice lines that copy the amounts charged, and record payments against invoices.

#How should SaaS pricing changes be stored?

Never update a price in place. Add a new price row and point new or migrated subscription items at it, keeping the old one for history. Invoices store the amounts they charged, so past bills never change.