SaaS Subscription Database ER Diagram

What this diagram shows

A SaaS subscription database models the core entities that track customer billing, feature access, and usage. The flow moves from account creation through subscription lifecycle management, usage metering, invoice generation, and entitlements enforcement. Accounts hold customer identity and contact data. Subscriptions track active plans and billing cycles. Usage records capture per-meter consumption (API calls, seats, storage). Invoices aggregate charges from subscriptions and overage usage. Entitlements define which features each plan tier unlocks. This schema handles recurring billing, usage-based charges, plan changes, and seat management while maintaining audit trails for disputes and compliance.

Key components

• Accounts — Stores customer organisation details, billing contact, and payment method status.
• Subscriptions — Tracks active plan assignments per account, billing cycle dates, and current plan tier.
• Plan Tiers — Defines available service levels with base price, billing frequency, and feature quotas.
• Usage Meters — Records consumption events (API calls, seats, concurrent users) timestamped per subscription.
• Invoices — Aggregates subscription base charges and usage overages into billable line items per period.
• Entitlements — Maps plan tiers to feature flags or rate limits that govern what customers can access.
• Invoice Line Items — Breaks down each invoice into individual charges (recurring, overage, discount, tax).

When to use it

Use this schema when building a SaaS application that charges on recurring subscriptions, usage, or both. It applies whether you offer tiered pricing, seat-based licensing, or metered consumption. Essential if you need to track active subscribers, forecast recurring revenue, dispute invoices, or audit entitlements. Avoid it for one-time purchases or affiliate marketplaces where transaction tracking alone suffices without lifecycle management.

Common mistakes

• Storing invoice data denormalized on the Subscription row instead of creating separate Invoice and LineItem tables, which prevents auditing historical charges and handling plan changes mid-cycle.
• Missing a Usage_Meter_Quota column on Plan_Tiers, forcing joins at query time to check whether a customer has exceeded their limit instead of caching entitlement state.
• Conflating Entitlements with Plan_Tiers by hardcoding feature access in the plan name rather than creating a junction table, which blocks customers from holding different feature sets under the same plan tier.

Adapting it to your system

Start by listing your plan tiers and their prices, then identify every consumable resource (seats, API requests, storage). Create a Usage_Meter row for each. Add custom fields to Accounts for your billing requirements (VAT ID, cost centre, internal ref). If you charge for feature access separately from consumption, add a Features table and junction it to Entitlements. If you support discounts or credits, add a Discounts table linked to Subscriptions or Invoices. Map your actual payment processor webhooks (Stripe, Zuora, Chargebee) to Invoice and Subscription status columns to stay synchronised.