Database Design Patterns for SaaS Applications

Every SaaS application has the same set of recurring database design problems. How do you isolate data between customers? How do you model subscriptions and billing? How do you track who changed what? These patterns appear so frequently that there are well-established solutions — but the details matter, and choosing the wrong approach early is expensive to undo.

This guide covers the most important database design patterns for SaaS applications. For each pattern, you will learn what it solves, how to implement it, and what trade-offs to expect. ER Flow is the tool used throughout to visualize and design these schemas.

Multi-Tenancy: The Foundation of SaaS

Multi-tenancy is the ability to serve multiple customers (tenants) from a single application instance. How you handle it at the database level determines your data isolation guarantees, operational complexity, and scalability ceiling.

Shared tables with `tenant_id` is the most common approach. Every tenant-scoped table gets a tenant_id column (a foreign key to a tenants table), and every query filters by tenant_id. The schema is simple: users(id, tenant_id, email, ...), orders(id, tenant_id, user_id, ...). The risk is application-level bugs that accidentally expose one tenant's data to another. Mitigate this with row-level security in PostgreSQL or by enforcing tenant_id filters in a base query class. Schema-per-tenant gives each tenant their own namespace within the same database instance. The application selects the correct schema at connection time. This provides stronger isolation but adds operational overhead when you have hundreds of tenants.

Database-per-tenant is the gold standard for isolation, used by enterprise SaaS products that must satisfy strict compliance requirements. Each tenant gets a completely separate database instance. Migration management becomes a distributed systems problem and costs scale linearly with tenant count. Reserve this for situations where isolation is a hard requirement, not just a preference.

Subscription and Billing Schema

The subscription billing pattern involves four core tables. Plans define what is offered: id, name, price_monthly_cents, price_yearly_cents, features (JSON), trial_days, is_active. Subscriptions track what a tenant is subscribed to: id, tenant_id, plan_id, status (an enum of trialing, active, past_due, canceled, paused), trial_ends_at, current_period_start, current_period_end, canceled_at, and payment_gateway_subscription_id.

Invoices record what was billed: id, tenant_id, subscription_id, amount_cents, currency, status (draft, open, paid, void, uncollectible), due_date, paid_at, and payment_gateway_invoice_id. Payments record individual payment attempts: id, invoice_id, amount_cents, status, payment_method, gateway_payment_id, failed_reason, created_at. Store monetary values as integers in the smallest currency unit (cents for USD) to avoid floating-point precision issues entirely.

User Authentication and Roles

A flexible auth schema needs to handle social login, password reset, email verification, and role-based access. Core tables: users(id, email, email_verified_at, password_hash, remember_token, created_at, updated_at). For social auth, add oauth_providers(id, user_id, provider, provider_user_id, access_token, refresh_token, expires_at). For role-based access, add roles(id, tenant_id, name, permissions JSON) and user_roles(user_id, role_id) as a junction table.

For fine-grained permissions in complex SaaS products, consider a separate permission table rather than embedding permissions in JSON: permissions(id, name, description) and role_permissions(role_id, permission_id). This lets you add new permissions without schema migrations. Index user_roles.user_id and role_permissions.role_id heavily — these columns are read on every authorization check throughout the application.

Audit Logging

Every production SaaS application needs to know who changed what and when. The standard pattern uses a polymorphic audit log: audit_logs(id, tenant_id, user_id, auditable_type, auditable_id, event, old_values JSON, new_values JSON, ip_address, user_agent, created_at). The auditable_type and auditable_id columns create a polymorphic reference — one audit log table covers changes to any entity in your schema.

Index on (tenant_id, auditable_type, auditable_id) for querying the audit history of a specific record, and on (tenant_id, user_id) for querying all changes made by a specific user. Store old_values and new_values as JSON with only the changed fields, not the full record — this keeps the table size manageable even for high-frequency entities. ER Flow lets you model this polymorphic relationship visually, which makes it easy to communicate the design to teammates who are not familiar with polymorphic patterns.

Soft Deletes

In SaaS, hard-deleting records is often a bad idea. A customer accidentally deletes something and expects you to restore it. You need to keep billing records for compliance. You want to analyze churn by examining deleted accounts. Soft deletes solve this with a single deleted_at TIMESTAMP NULL column. A NULL value means the record is active; a timestamp means it was deleted.

The trade-off is that every query must include WHERE deleted_at IS NULL — or you rely on a query scope in your ORM. Make sure to include deleted_at as part of composite indexes on frequently queried columns, and place it after the high-cardinality filter columns. If you ever need to truly purge records for GDPR compliance, you can hard-delete rows where deleted_at falls outside your retention window.

Feature Flags Schema

Feature flags let you control which features are enabled per tenant or per user without code deployments. A practical schema: features(id, key, description, is_enabled_globally, created_at) as the registry of all features. tenant_features(tenant_id, feature_key, is_enabled, overrides JSON) for per-tenant overrides. user_features(user_id, feature_key, is_enabled) for per-user overrides. When checking if a feature is enabled, the precedence is: user-level override → tenant-level override → global default.

Cache feature flag lookups aggressively — you check them on every request. Use Redis with a short TTL (60 seconds) and invalidate the cache when a feature setting changes. This pattern is more flexible than embedding feature flags in the plans table because features and plans can evolve independently. In ER Flow, designing this as three separate tables with clear relationships prevents the common mistake of coupling feature availability directly to subscription tier.

Event Sourcing for Audit-Heavy Domains

For domains where the full history of changes is critical — financial transactions, compliance records, order state machines — consider event sourcing at the database level. Instead of updating records in place, you append events: domain_events(id, tenant_id, aggregate_type, aggregate_id, event_type, payload JSON, metadata JSON, occurred_at, sequence_number). The current state of any entity is derived by replaying its events.

This is significantly more complex than standard CRUD, but it gives you an immutable audit trail, the ability to replay events for debugging, and a natural foundation for event-driven architecture. Use it selectively for the parts of your schema where history matters most. ER Flow lets you model both your event store and your read model (the denormalized views built from events) in the same diagram, making the relationship between the two explicit and reviewable by the whole team.

Designing SaaS schemas well requires thinking several steps ahead. The patterns described here have been battle-tested across hundreds of production SaaS applications. Start with the shared-table multi-tenancy approach, add row-level security as you grow, and layer in event sourcing only where it genuinely solves a problem you have today.