Database Design for SaaS Applications

Design your database for the first 12 to 24 months, not for imaginary hyperscale. For a small SaaS, the main goals are clear ownership boundaries, predictable queries, safe schema changes, and a path to multi-tenant growth. Start with a relational database, define core entities early, and enforce constraints in the schema instead of only in application code.

Quick Fix / Quick Setup

Use this PostgreSQL starter schema as a solid default for many B2B and team-based SaaS products.

-- PostgreSQL starter schema for a small SaaS
create extension if not exists pgcrypto;

create table organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  plan text not null default 'free',
  created_at timestamptz not null default now()
);

create table users (
  id uuid primary key default gen_random_uuid(),
  email text not null unique,
  password_hash text not null,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table memberships (
  organization_id uuid not null references organizations(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  role text not null default 'member',
  created_at timestamptz not null default now(),
  primary key (organization_id, user_id)
);

create table projects (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references organizations(id) on delete cascade,
  name text not null,
  created_at timestamptz not null default now()
);
create index idx_projects_organization_id on projects(organization_id);

create table subscriptions (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null unique references organizations(id) on delete cascade,
  provider text not null default 'stripe',
  provider_customer_id text unique,
  provider_subscription_id text unique,
  status text not null default 'inactive',
  current_period_end timestamptz,
  created_at timestamptz not null default now()
);

create table audit_logs (
  id bigserial primary key,
  organization_id uuid references organizations(id) on delete cascade,
  user_id uuid references users(id) on delete set null,
  action text not null,
  entity_type text not null,
  entity_id text not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);
create index idx_audit_logs_org_created_at on audit_logs(organization_id, created_at desc);

Good default: PostgreSQL + UUID primary keys + explicit foreign keys + join table for membership + indexes on tenant-scoped foreign keys. This schema is enough to launch many B2B and team-based SaaS products.

What’s happening

A SaaS database usually models users, accounts or organizations, memberships, app resources, billing state, and audit history.

The biggest early mistake is mixing user-owned and tenant-owned data without a clear ownership rule.

Most MVPs should start with PostgreSQL because it supports transactions, constraints, JSONB, indexing, and safe migrations well.

Your schema should make it hard to create invalid data: use foreign keys, unique constraints, not-null columns, and default timestamps.

Design queries around real application screens and API calls, not just around tables.

Step-by-step implementation

1. Pick the ownership model first

Choose one of these before creating app tables:

• user-owned
• organization-owned
• mixed, with explicit boundaries

For most SaaS products, organization-owned data is the safest long-term default.

Example ownership rule:

• users = identity
• organizations = billing and tenant boundary
• memberships = user access to tenant
• projects, documents, workspaces, reports = belong to organization

If you expect team features or B2B billing, read Multi-Tenant vs Single-Tenant Architecture before finalizing the schema.

2. Define the core entity set

A small SaaS usually needs these tables early:

• users
• organizations
• memberships
• product resources such as projects
• subscriptions
• audit_logs
• invitations
• webhook_events
• session or API token tables if applicable

A practical starter table for invitations:

create table invitations (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references organizations(id) on delete cascade,
  email text not null,
  role text not null default 'member',
  token text not null unique,
  expires_at timestamptz not null,
  accepted_at timestamptz,
  created_at timestamptz not null default now()
);

create index idx_invitations_organization_id on invitations(organization_id);

3. Choose primary keys deliberately

UUIDs are usually the best default for SaaS products:

• safe to expose in APIs
• easy across systems
• avoid predictable integer sequences
• practical for background jobs and event processing

If you use PostgreSQL UUID generation:

create extension if not exists pgcrypto;

If you need simpler internal-only IDs, integers are still valid. The key is consistency.

4. Add ownership columns to every tenant-scoped table

Every tenant resource table should include organization_id or your tenant key.

Bad:

create table projects (
  id uuid primary key default gen_random_uuid(),
  name text not null
);

Good:

create table projects (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references organizations(id) on delete cascade,
  name text not null,
  created_at timestamptz not null default now()
);

This design makes tenant filtering explicit in queries:

select *
from projects
where organization_id = $1
order by created_at desc
limit 20;

5. Enforce constraints in the database

Do not rely only on app validation.

Use:

• not null
• unique
• foreign key
• check constraints where useful
• explicit deletion behavior

Examples:

alter table memberships
  add constraint memberships_role_check
  check (role in ('owner', 'admin', 'member'));

alter table subscriptions
  add constraint subscriptions_status_check
  check (status in ('inactive', 'trialing', 'active', 'past_due', 'canceled'));

If your auth layer is evolving, keep auth-related tables separate from product-domain tables. See Structuring a Flask/FastAPI SaaS Project for project-level separation patterns.

6. Add indexes for actual query patterns

At minimum, index:

• tenant foreign keys
• user foreign keys
• external provider IDs
• timestamps used in sorting
• uniqueness boundaries

Examples:

create index idx_projects_org_created_at
  on projects(organization_id, created_at desc);

create index idx_memberships_user_id
  on memberships(user_id);

create index idx_audit_logs_org_created_at
  on audit_logs(organization_id, created_at desc);

create unique index idx_invitations_org_email_pending
  on invitations(organization_id, email)
  where accepted_at is null;

Match indexes to real queries, not guesses.

7. Keep billing state in dedicated tables

Do not scatter billing columns across unrelated product tables.

Use a dedicated subscriptions table and optionally a webhook_events table.

Example:

create table webhook_events (
  id bigserial primary key,
  provider text not null,
  external_event_id text not null unique,
  event_type text not null,
  payload jsonb not null,
  processed_at timestamptz,
  failed_at timestamptz,
  created_at timestamptz not null default now()
);

create index idx_webhook_events_processed_at
  on webhook_events(processed_at);

This gives you idempotency for external events using unique(external_event_id).

If billing is part of your launch path, pair schema design with deployment-safe migrations from Database Migration Strategy.

8. Separate append-only history from mutable state

Use dedicated tables for:

• audit logs
• webhook events
• background job runs
• usage records
• imports/exports

Do not overload operational tables with history data.

Example background job tracking table:

create table job_runs (
  id bigserial primary key,
  job_name text not null,
  organization_id uuid references organizations(id) on delete cascade,
  status text not null check (status in ('queued', 'running', 'succeeded', 'failed')),
  started_at timestamptz,
  finished_at timestamptz,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index idx_job_runs_org_created_at
  on job_runs(organization_id, created_at desc);

9. Use JSONB only for optional metadata

Good JSONB usage:

• raw webhook payloads
• integration metadata
• optional provider fields
• debug metadata

Bad JSONB usage:

• ownership
• roles
• billing status
• foreign keys
• fields you join on often

Keep critical relational data in typed columns.

10. Plan migrations from day one

Never rely on direct production edits.

Use a migration tool such as Alembic. Example workflow:

alembic revision -m "create organizations users memberships"
alembic upgrade head
alembic current
alembic history

For app structure and environment separation, also review Environment Variables and Secrets Management.

11. Add timestamps and deletion behavior explicitly

Operational tables should generally include:

• created_at
• updated_at where rows change over time

Example:

alter table projects
add column updated_at timestamptz not null default now();

Deletion rules should be intentional:

• on delete cascade for child records tied to a parent tenant
• on delete set null for historical actor references
• restrict where deletion should be blocked

12. Document the relationships

Create a simple ERD and keep it current.

Common causes

• No clear distinction between user-owned and tenant-owned data.
• Missing foreign keys leading to orphaned rows and broken joins.
• No indexes on organization_id, user_id, or external provider IDs.
• Using JSON fields for core relational data that should be typed and constrained.
• Unique constraints missing or defined at the wrong scope.
• Direct production schema edits instead of tracked migrations.
• Soft deletes added everywhere, making queries and uniqueness checks harder.
• Subscription and auth data mixed into unrelated domain tables.
• No audit or event tables for webhooks, billing changes, or security-sensitive actions.
• Ignoring deletion behavior and retention requirements until production.

Debugging tips

Inspect schema and constraints:

psql "$DATABASE_URL" -c "\dt"
psql "$DATABASE_URL" -c "\d organizations"
psql "$DATABASE_URL" -c "\d users"
psql "$DATABASE_URL" -c "\d memberships"
psql "$DATABASE_URL" -c "select table_name from information_schema.tables where table_schema='public' order by table_name;"
psql "$DATABASE_URL" -c "select conname, pg_get_constraintdef(oid) from pg_constraint where connamespace = 'public'::regnamespace;"
psql "$DATABASE_URL" -c "select schemaname, tablename, indexname, indexdef from pg_indexes where schemaname='public' order by tablename;"

Check query plans on tenant-scoped list views:

psql "$DATABASE_URL" -c "explain analyze select * from projects where organization_id = 'ORG_UUID' order by created_at desc limit 20;"

Check tenant distribution and hotspot accounts:

psql "$DATABASE_URL" -c "select organization_id, count(*) from projects group by organization_id order by count(*) desc limit 20;"

Inspect billing records:

psql "$DATABASE_URL" -c "select * from subscriptions where provider_customer_id is not null limit 10;"

Inspect migration state:

alembic current
alembic history
alembic upgrade head

Practical checks:

• if joins are slow, verify the foreign key column also has an index
• if uniqueness bugs appear, confirm the constraint scope matches the business rule
• if tenant leaks happen, search for product queries missing organization_id
• if webhook processing duplicates records, add a unique external event ID
• if migrations fail in production, test against a recent data snapshot first

For final launch validation, use SaaS Production Checklist.

FAQ

What is the best database for a small SaaS?

PostgreSQL is usually the best default because it gives you transactions, relational integrity, strong indexing, JSONB, and a mature ecosystem for migrations and hosting.

Should I design for multi-tenancy immediately?

If your product has teams, workspaces, B2B billing, or shared data, yes. Add a tenant container like organizations early so you do not need a painful rewrite later.

Are UUIDs better than integer IDs?

For many SaaS products, yes. UUIDs are safer to expose publicly and easier across distributed systems. Integers are still valid if simplicity matters more and IDs stay internal.

When should I denormalize data?

Only after measuring query bottlenecks. Start normalized, add indexes, and use materialized views or summary tables only when read performance requires it.

How do I keep schema changes safe in production?

Use migration tools, test migrations against production-like data, avoid destructive changes in one step, and always have backups before major schema updates.

Final takeaway

A good SaaS schema is not complicated. It is explicit about ownership, strict about constraints, indexed for real queries, and easy to migrate.

Start with PostgreSQL, model tenants clearly, keep billing and auth relationships clean, and design for safe operational changes.

If the schema makes invalid states difficult and common queries cheap, you are on the right path.