Database Design Patterns for SaaS Applications

What Database Decisions Define a SaaS Architecture?

SaaS applications have database requirements that single-tenant applications do not. Multiple organizations share the same infrastructure, but their data must be strictly isolated. Features like audit logs, soft deletes, and subscription-aware access control are standard requirements that should be designed in from day one, not bolted on later.

The database design you choose at the start will either support or constrain your SaaS product for years. These are the patterns worth getting right.

How Do You Handle Multi-Tenancy?

There are three approaches, each with different trade-offs:

Shared database, shared schema (most common for early-stage SaaS): All tenants share the same tables. Every table has an organization_id foreign key. Row-Level Security in PostgreSQL enforces isolation at the database level. This approach is simple to implement, cost-efficient, and sufficient for most SaaS products up to hundreds of tenants.

Shared database, separate schemas: Each tenant gets their own PostgreSQL schema (namespace) within the same database. Migrations must run across all schemas. This adds complexity but improves isolation and makes per-tenant data operations (backup, export) easier.

Separate databases per tenant: Maximum isolation, suitable for enterprise customers with strict compliance requirements. Operationally complex — connection pool management becomes non-trivial. Use only when a customer contract specifically requires it.

For Bangkok SaaS startups, start with the shared schema approach and RLS. You can migrate to separate schemas later if needed.

What Is Soft Delete and Why Use It?

Hard deletes (DELETE FROM orders WHERE id = ?) permanently remove records. Soft deletes mark a record as deleted with a deleted_at timestamp instead:

ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;

All queries filter by WHERE deleted_at IS NULL. Soft-deleted records remain in the database for audit purposes, can be recovered if a user deletes something by mistake, and preserve referential integrity for related records.

The tradeoff is that every query must include the deleted_at IS NULL filter. Use a Drizzle or Prisma middleware to apply this filter automatically.

How Do You Build an Audit Log?

Regulations and enterprise customers increasingly require a record of who changed what and when. Implement an audit log table:

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL,
  actor_id UUID NOT NULL,
  action TEXT NOT NULL,      -- 'order.created', 'user.deleted'
  resource_type TEXT NOT NULL,
  resource_id UUID NOT NULL,
  changes JSONB,              -- before/after state
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Write to this table inside a database transaction alongside the main operation, or use PostgreSQL triggers for automatic capture. Never delete audit log records.

How Do You Handle Subscription-Aware Access?

Store the tenant's subscription tier and feature flags in the organizations table. Check these in your RLS policies and application middleware to restrict access to premium features. Design your feature flag system to be additive — new features start disabled and are enabled per tier, rather than disabling features as organizations downgrade.

Ready to Build Something Fast?

Get a free quote on LINE. We reply within 24 hours.