SaaS subscription billing schema template

Tables in this data model

• accounts — 4 columns
• plans — 3 columns
• prices — 4 columns
• subscriptions — 6 columns
• usage_records — 5 columns
• invoices — 6 columns
• invoice_lines — 4 columns
• payments — 5 columns
• coupons — 4 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE accounts (
  account_id  INTEGER      NOT NULL PRIMARY KEY,
  name        VARCHAR(150) NOT NULL,
  billing_email VARCHAR(255) NOT NULL,
  currency    CHAR(3)      NOT NULL
);

CREATE TABLE plans (
  plan_id INTEGER     NOT NULL PRIMARY KEY,
  name    VARCHAR(80) NOT NULL,
  tier    VARCHAR(20) NOT NULL
);

CREATE TABLE prices (
  price_id    INTEGER       NOT NULL PRIMARY KEY,
  plan_id     INTEGER       NOT NULL REFERENCES plans(plan_id),
  interval    VARCHAR(10)   NOT NULL,
  unit_amount DECIMAL(10,2) NOT NULL
);

CREATE TABLE subscriptions (
  subscription_id INTEGER     NOT NULL PRIMARY KEY,
  account_id      INTEGER     NOT NULL REFERENCES accounts(account_id),
  price_id        INTEGER     NOT NULL REFERENCES prices(price_id),
  status          VARCHAR(20) NOT NULL,
  seats           INTEGER     NOT NULL,
  current_period_end DATE     NOT NULL
);

CREATE TABLE usage_records (
  usage_id        INTEGER   NOT NULL PRIMARY KEY,
  subscription_id INTEGER   NOT NULL REFERENCES subscriptions(subscription_id),
  metric          VARCHAR(40) NOT NULL,
  quantity        INTEGER   NOT NULL,
  recorded_at     TIMESTAMP NOT NULL
);

CREATE TABLE invoices (
  invoice_id      INTEGER       NOT NULL PRIMARY KEY,
  account_id      INTEGER       NOT NULL REFERENCES accounts(account_id),
  subscription_id INTEGER       REFERENCES subscriptions(subscription_id),
  status          VARCHAR(20)   NOT NULL,
  total           DECIMAL(12,2) NOT NULL,
  due_on          DATE          NOT NULL
);

CREATE TABLE invoice_lines (
  line_id    INTEGER       NOT NULL PRIMARY KEY,
  invoice_id INTEGER       NOT NULL REFERENCES invoices(invoice_id),
  description VARCHAR(200) NOT NULL,
  amount     DECIMAL(12,2) NOT NULL
);

CREATE TABLE payments (
  payment_id INTEGER       NOT NULL PRIMARY KEY,
  invoice_id INTEGER       NOT NULL REFERENCES invoices(invoice_id),
  amount     DECIMAL(12,2) NOT NULL,
  status     VARCHAR(20)   NOT NULL,
  paid_at    TIMESTAMP
);

CREATE TABLE coupons (
  coupon_id  INTEGER     NOT NULL PRIMARY KEY,
  account_id INTEGER     REFERENCES accounts(account_id),
  percent_off DECIMAL(5,2),
  expires_on DATE
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates