Database ERD Examples for SaaS Apps: Users, Billing, Permissions, and Audit Logs

Overview

A good SaaS ERD example is not a single perfect schema. It is a durable model of the relationships your team expects to maintain over time. The most useful database ERD examples make business rules visible early: who owns an account, how billing relates to organizations, where authorization decisions are stored, and what exactly gets recorded for accountability.

For many SaaS apps, a practical starting point is to separate four concerns:

• Identity and access: users, organizations, memberships, roles, sessions, invitations
• Commercial data: products, plans, subscriptions, invoices, payment methods, usage records
• Authorization: roles, permissions, resource scopes, policy assignments
• Operational history: audit events, actor context, entity snapshots, request metadata

These concerns connect, but they should not collapse into one overloaded table design. A common mistake in early-stage products is using users as both the identity record, the billing owner, and the permission root. That may work for a simple single-tenant app, but it becomes difficult to maintain once teams, enterprise billing, delegated administration, and audit requirements arrive.

A more resilient baseline for a SaaS ERD example usually looks like this:

• users stores the person or principal identity
• organizations or accounts stores the tenant boundary
• memberships resolves many-to-many relationships between users and organizations
• subscriptions attaches commercial state to the organization, not just to one user
• roles and permissions model authorization separately from membership
• audit_logs records actions independently from mutable business tables

This structure also makes diagrams easier to review. Developers can reason about cardinality, admins can understand ownership, and product teams can see where feature growth will introduce schema pressure. If you want a broader framing for ERD notation versus implementation-level schema details, see ERD vs Database Schema Diagram: What to Use for Design, Documentation, and Reviews.

What follows is not a vendor-specific blueprint. It is a set of recurring patterns and variables worth tracking over time so the ERD remains useful after the first launch.

What to track

The value of a tracker-style reference is not merely listing tables. It is identifying the variables that tend to drift as SaaS products mature. These are the relationships and assumptions most likely to need review.

1. User and tenant boundaries

At minimum, track whether your application is:

• Single-tenant per customer or multi-tenant by design
• User-owned, workspace-owned, or organization-owned
• One user to one account, or one user to many organizations

A solid user model often includes:

• users: id, email, status, created_at
• organizations: id, name, lifecycle_state
• memberships: id, user_id, organization_id, role_id or membership_type, invited_by, joined_at
• invitations: email, organization_id, token, expires_at
• sessions or auth_identities: provider, provider_subject, last_seen_at

What to track over time:

• Whether a user can belong to multiple organizations
• Whether an organization can have multiple owners
• Whether deleted users should be hard-deleted, soft-deleted, or anonymized
• Whether external identities map one-to-one or many-to-one with internal users

These choices shape almost every downstream table. For example, if subscriptions belong to organizations but your permission checks still assume user-owned resources, the ERD will produce awkward joins and unclear business logic.

2. Billing as a first-class domain

A user billing schema should be explicit about what is being sold, who is charged, and what period the charge covers. Even if a payment processor manages the transaction details, your application still needs durable billing entities for product logic and support workflows.

Common tables include:

• plans: code, interval, status, billing_mode
• subscriptions: organization_id, plan_id, status, current_period_start, current_period_end, cancel_at, canceled_at
• subscription_items: subscription_id, feature_code or price_ref, quantity
• invoices: subscription_id, issued_at, due_at, amount, currency, state
• payment_methods: billing_owner_id, provider_ref, type, last_four, expires_at
• usage_records: organization_id, metric_code, quantity, measured_at

What to track:

• Whether billing belongs to the user, organization, or a dedicated billing entity
• Whether plans are immutable records or edited in place
• Whether invoices are snapshots or reconstructed from current plan data
• Whether usage is aggregated daily, hourly, or event by event
• Whether subscription state changes need an event table for history

A recurring problem in database ERD examples for SaaS is mixing present state with historical state. If a plan name or price changes, old invoices should usually remain understandable. That often suggests storing a snapshot or referencing versioned commercial records rather than assuming current plan metadata is enough.

3. Permissions database design

Authorization grows in layers. A startup may begin with a simple role column on memberships, then later need project-level access, feature flags for beta users, custom admin roles, or service accounts. If your ERD does not separate membership from permission assignment, feature delivery slows down as exceptions accumulate.

A practical permissions database design often includes:

• roles: id, code, name, scope_type
• permissions: id, code, resource_type, action
• role_permissions: role_id, permission_id
• membership_roles or direct role assignment tables
• resource_policies or access_grants: principal_id, resource_type, resource_id, permission_id or policy_code

What to track:

• Global roles versus tenant-scoped roles
• Role-based access control only, or a mix with resource-level grants
• Human users versus service accounts and API tokens
• Inheritance rules, such as organization admin implying project admin
• Whether permission checks are derived at runtime or partially materialized

If your app is moving toward microservices, it helps to track which service is authoritative for permission assignment and which services merely consume decisions. That boundary becomes important when the ERD is later represented alongside a broader software architecture diagram or microservices documentation. For system-level context, Microservices Architecture Diagram Guide: Patterns, Anti-Patterns, and Review Checklist is a useful companion.

4. Audit log schema

An audit log schema should answer a future question from support, security, or compliance without depending on the current UI. That means recording enough context to make the event interpretable even if the product changes.

A common baseline for audit_logs includes:

• id
• occurred_at
• actor_type and actor_id
• organization_id
• action
• target_type and target_id
• request_id or correlation key
• ip_address and user agent when relevant
• metadata for structured event details
• before_snapshot and after_snapshot only when justified

What to track:

• Whether events are append-only
• Whether mutable entities need snapshots for supportability
• Whether retention differs for operational logs versus formal audit records
• Whether audit events must support export by tenant
• Whether actor identity must distinguish user, admin, automation, and system process

Do not treat application logs and audit logs as interchangeable. Operational logs are optimized for debugging. Audit logs are optimized for traceability and explanation. They may share infrastructure, but in an ERD they serve different purposes.

5. Relationship stress points

Across all four domains, the recurring variables worth reviewing are cardinality, ownership, mutability, and history. In practice, that means watching for:

• One-to-many relationships that are quietly becoming many-to-many
• Fields that carry overloaded meanings, such as owner_id
• Status columns that imply transitions but do not preserve history
• JSON fields that are absorbing structured concepts too early
• Soft deletion rules that break uniqueness or reporting

These are usually the first signals that the ERD needs revision rather than just another migration script.

Cadence and checkpoints

The easiest time to review an ERD is before a major feature ships. The most useful time is on a regular cadence, even when no one is asking for it. For SaaS apps, a monthly or quarterly review is often enough to keep the model aligned with reality.

Monthly checkpoints

Use a lightweight review if the product is changing quickly:

• Compare the current ERD with the last month’s migrations
• Note any new join tables, enum expansions, or polymorphic references
• Review whether permission rules were added outside the existing model
• Check whether billing logic introduced historical data needs not shown in the ERD
• Inspect whether audit logging covers new sensitive actions

This review can be short. The goal is to keep the diagram trustworthy.

Quarterly checkpoints

Use a deeper review each quarter:

• Validate tenant boundaries and ownership rules
• Review indexes and uniqueness assumptions against current access patterns
• Confirm billing entities still match product packaging
• Revisit role and permission granularity
• Audit retention and export requirements for audit records
• Mark deprecated entities and planned replacements in the diagram

Quarterly reviews are also a good time to split the ERD into focused views: identity, billing, authorization, and audit. A single giant diagram becomes hard to maintain and harder to use in design reviews.

Checkpoints tied to change events

Do not wait for the calendar if one of these happens:

• You introduce organizations, teams, or workspaces
• You add annual billing, usage pricing, credits, or invoicing rules
• You move from simple roles to resource-level permissions
• You add compliance-driven audit requirements
• You split a monolith into services or add a reporting replica

These changes tend to expose hidden assumptions in the original SaaS ERD example.

How to interpret changes

Not every schema change means the model is wrong. Some changes are normal expansion. Others signal that the conceptual structure needs attention. The key is to interpret drift correctly.

Healthy change

These usually indicate normal product growth:

• New resource tables that fit existing tenant and permission boundaries
• Additional invoice or subscription attributes with clear historical meaning
• More audit event types using the same event structure
• New role definitions without changing the role-permission relationship

If the same design principles still apply, update the ERD and move on.

Warning signs

These often mean the model needs refactoring:

• Repeated exceptions like “except for enterprise accounts”
• Direct foreign keys from many domains into users when the business owner is really an organization
• Permission logic split between database fields, code constants, and ad hoc feature flags
• Billing reports that cannot reconstruct past state from current records
• Audit events that require joining too many mutable tables to understand what happened

When these appear, redraw the ERD before writing more code. A diagram review at this stage is cheaper than continuing with migrations that preserve the wrong assumptions.

Questions to ask during interpretation

• Has the tenant boundary changed?
• Did a one-to-one assumption become one-to-many?
• Are current-state tables being used to answer historical questions?
• Do permission rules map to stable entities, or to UI concepts that may change?
• Can an auditor or support engineer understand a key event without reading application code?

If the answer is no to any of these, the ERD should be updated as a design artifact, not just as implementation notes.

When to revisit

Revisit this topic whenever your SaaS app crosses a boundary from simple ownership to delegated administration, from flat subscriptions to structured billing, or from implicit trust to formal authorization and traceability. In practice, that means reviewing your ERD on a monthly or quarterly cadence and also after any feature that changes ownership, access, money, or accountability.

A practical workflow looks like this:

1. Keep a canonical ERD with separate focused views for users, billing, permissions, and audit logs.
2. Annotate assumptions directly in the diagram: tenant owner, billing owner, role scope, retention expectations.
3. Review migrations against the diagram once per month if the schema is changing often.
4. Run a quarterly design review with engineering, product, and operations to identify drift.
5. Update examples whenever a new recurring pattern appears, such as service accounts or usage-based billing.

If your team documents systems visually, connect the ERD to higher-level architecture views as the product evolves. A C4 container or context diagram can show where identity, billing, and audit responsibilities live, while the ERD captures the data model itself. For that broader perspective, see C4 Model Diagrams Explained: Levels, Examples, and Tooling for Software Teams.

The main reason to keep a growing reference of database ERD examples is simple: SaaS products repeat the same modeling problems, but the pressure points arrive at different times. If you document those patterns clearly and revisit them on a schedule, your schema decisions become easier to explain, easier to review, and easier to change without breaking the foundations of the product.

For your next review, start with four questions: Who owns the tenant boundary? Who gets billed? Who can do what? What evidence remains after the action? If your ERD answers those cleanly, it is doing useful work.