How to Design a Database Schema for a SaaS Application

The Database Schema Decisions That Haunt SaaS Applications Later

Most SaaS applications do not fail because of bad product decisions. They fail — or spend months in painful migrations — because the database schema was designed for the first hundred users and not the first hundred thousand.

The schema you design in week one is the schema you are still living with in year three. Every table, every relationship, every index decision compounds over time. Get it right early and scaling feels natural. Get it wrong and every new feature becomes a migration nightmare.

This guide covers the exact schema patterns, multi-tenancy decisions, and structural choices that separate SaaS databases that scale from ones that become technical debt.

🎯 Quick Answer (30-Second Read)

• Multi-tenancy model: Row-level tenancy with a tenant_id on every table is the most practical starting point for most SaaS products
• Core tables every SaaS needs: tenants, users, memberships, subscriptions, audit_logs
• Biggest early mistake: Not adding tenant_id to every table from day one — retrofitting it later is a painful migration
• Row Level Security: Use it in PostgreSQL — it enforces tenant isolation at the database level, not just application level
• Soft deletes: Add deleted_at instead of hard deleting rows — you will need the audit trail
• Timestamps: Every table needs created_at and updated_at — non-negotiable

The Three Multi-Tenancy Models — And Which One to Pick

Before writing a single CREATE TABLE, you need to make the most consequential schema decision in SaaS: how do you isolate one customer's data from another's?

For the vast majority of SaaS products — especially early-stage — row-level tenancy with a shared database is the right starting point. It is cost-efficient, operationally simple, and scales to tens of thousands of tenants without architectural changes.

The decision to move to database-per-tenant is a growth problem. Start with row-level tenancy and migrate specific high-value enterprise customers to dedicated databases when the contract size justifies the operational overhead.

The Core Schema Every SaaS Application Needs

The Tenants Table

Everything in your schema starts here. Every other table references this one.

CREATE TABLE tenants (
  id          uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  name        text NOT NULL,
  slug        text UNIQUE NOT NULL,
  plan        text NOT NULL DEFAULT 'free'
                CHECK (plan IN ('free', 'pro', 'enterprise')),
  status      text NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'suspended', 'canceled')),
  settings    jsonb DEFAULT '{}'::jsonb,
  created_at  timestamptz DEFAULT now() NOT NULL,
  updated_at  timestamptz DEFAULT now() NOT NULL,
  deleted_at  timestamptz
);

CREATE INDEX idx_tenants_slug ON tenants(slug);
CREATE INDEX idx_tenants_status ON tenants(status) WHERE deleted_at IS NULL;

Why slug: Every tenant needs a human-readable identifier for subdomains (acme.yoursaas.com), URL paths (/app/acme/dashboard), and support tickets. Generate it from the company name, enforce uniqueness.

Why jsonb settings: Tenant-specific configuration — feature flags, preferences, custom limits — inevitably accumulates. A settings jsonb column avoids a proliferation of nullable boolean columns every time you add a per-tenant toggle.

Why deleted_at: Soft deletes. When a tenant cancels, you do not delete their data immediately. You mark deleted_at, stop billing, restrict access, and retain data for the grace period your terms of service specify.

The Users Table

CREATE TABLE users (
  id            uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  email         text UNIQUE NOT NULL,
  password_hash text,
  full_name     text,
  avatar_url    text,
  email_verified_at timestamptz,
  last_sign_in_at   timestamptz,
  created_at    timestamptz DEFAULT now() NOT NULL,
  updated_at    timestamptz DEFAULT now() NOT NULL,
  deleted_at    timestamptz
);

CREATE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;

Critical decision: Users are separate from tenants. A user can belong to multiple tenants. This is the membership table's job — do not store tenant_id directly on the users table.

The Memberships Table

This is the join table between users and tenants. It is where roles, permissions, and invitation state live.

CREATE TABLE memberships (
  id          uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id   uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role        text NOT NULL DEFAULT 'member'
                CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  status      text NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'invited', 'suspended')),
  invited_by  uuid REFERENCES users(id),
  invited_at  timestamptz,
  accepted_at timestamptz,
  created_at  timestamptz DEFAULT now() NOT NULL,
  updated_at  timestamptz DEFAULT now() NOT NULL,

  UNIQUE(tenant_id, user_id)
);

CREATE INDEX idx_memberships_tenant ON memberships(tenant_id);
CREATE INDEX idx_memberships_user ON memberships(user_id);

Why this pattern: A user who leaves a company and joins a new one should not lose their account. A user who is an admin in one tenant should not be an admin in another by default. Role and status live on the membership, not the user.

The Subscriptions Table

CREATE TABLE subscriptions (
  id                      uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id               uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  stripe_customer_id      text UNIQUE,
  stripe_subscription_id  text UNIQUE,
  plan                    text NOT NULL DEFAULT 'free',
  status                  text NOT NULL DEFAULT 'active'
                            CHECK (status IN (
                              'active', 'trialing', 'past_due',
                              'canceled', 'incomplete'
                            )),
  trial_ends_at           timestamptz,
  current_period_start    timestamptz,
  current_period_end      timestamptz,
  cancel_at_period_end    boolean DEFAULT false,
  created_at              timestamptz DEFAULT now() NOT NULL,
  updated_at              timestamptz DEFAULT now() NOT NULL
);

CREATE INDEX idx_subscriptions_tenant ON subscriptions(tenant_id);
CREATE INDEX idx_subscriptions_stripe_customer ON subscriptions(stripe_customer_id);

The Audit Logs Table

Every SaaS application needs a tamper-evident log of who did what and when. This is required for enterprise compliance and invaluable for debugging.

CREATE TABLE audit_logs (
  id          uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id   uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id     uuid REFERENCES users(id) ON DELETE SET NULL,
  action      text NOT NULL,
  resource    text NOT NULL,
  resource_id uuid,
  metadata    jsonb DEFAULT '{}'::jsonb,
  ip_address  inet,
  user_agent  text,
  created_at  timestamptz DEFAULT now() NOT NULL
);

CREATE INDEX idx_audit_logs_tenant ON audit_logs(tenant_id);
CREATE INDEX idx_audit_logs_tenant_created ON audit_logs(tenant_id, created_at DESC);
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource, resource_id);

Audit logs are append-only. Never update or delete them. No updated_at, no deleted_at. The entire value of an audit log comes from its immutability.

Enforcing Tenant Isolation with Row Level Security

Application-level tenant checks (WHERE tenant_id = $current_tenant) are necessary but not sufficient. A bug in your application layer — a missing WHERE clause, a copy-paste error in a query — can expose one tenant's data to another. That is a GDPR incident.

Row Level Security (RLS) in PostgreSQL enforces isolation at the database level. Even if your application sends a query without a tenant filter, the database will not return rows from other tenants.

-- Enable RLS on every tenant-scoped table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

-- Create a policy using a session variable
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Set the session variable in your application before queries
-- In your database middleware:
-- SET app.current_tenant_id = 'tenant-uuid-here';

Set app.current_tenant_id at the start of every database session in your application middleware. Every query in that session is automatically filtered to that tenant's rows. Forgetting a WHERE clause no longer causes a data breach — it causes an empty result set.

Schema Patterns for Common SaaS Features

Feature Flags per Tenant

CREATE TABLE tenant_features (
  tenant_id   uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  feature     text NOT NULL,
  enabled     boolean NOT NULL DEFAULT false,
  config      jsonb DEFAULT '{}'::jsonb,
  created_at  timestamptz DEFAULT now() NOT NULL,
  PRIMARY KEY (tenant_id, feature)
);

Usage Metering for Usage-Based Billing

CREATE TABLE usage_events (
  id          uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id   uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  event_type  text NOT NULL,
  quantity    integer NOT NULL DEFAULT 1,
  metadata    jsonb DEFAULT '{}'::jsonb,
  occurred_at timestamptz DEFAULT now() NOT NULL
);

CREATE INDEX idx_usage_tenant_type_time
  ON usage_events(tenant_id, event_type, occurred_at DESC);

Invitations

CREATE TABLE invitations (
  id          uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id   uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  email       text NOT NULL,
  role        text NOT NULL DEFAULT 'member',
  token       text UNIQUE NOT NULL,
  invited_by  uuid NOT NULL REFERENCES users(id),
  accepted_at timestamptz,
  expires_at  timestamptz NOT NULL DEFAULT (now() + interval '7 days'),
  created_at  timestamptz DEFAULT now() NOT NULL
);

CREATE INDEX idx_invitations_token ON invitations(token);
CREATE INDEX idx_invitations_tenant ON invitations(tenant_id);

The Non-Negotiable Schema Rules

Every table in a SaaS database must follow these rules without exception:

• id uuid DEFAULT gen_random_uuid() — UUIDs over serial integers. Serial integers expose row counts, are not safe for distributed systems, and leak business information (customer number 4 knows you have few customers).
• created_at timestamptz DEFAULT now() — every row needs a creation timestamp. You will filter, sort, and report on it.
• updated_at timestamptz — required for cache invalidation, sync systems, and debugging. Update it with a trigger or ORM hook on every write.
• deleted_at timestamptz — soft deletes on anything that has audit, compliance, or recovery value. Hard deletes are for truly ephemeral data only.
• tenant_id uuid NOT NULL — on every tenant-scoped table, non-nullable, indexed, foreign key to tenants. If you find yourself adding tenant_id as nullable, you are designing the table wrong.

My Take — What Nobody Tells You About SaaS Schema Design

The thing that took me longest to internalise is that a database schema is not a technical decision — it is a product decision that happens to be expressed in SQL. Every table you create is a commitment. Every column is a contract. The schema is the most honest map of what your product actually does, stripped of UI and marketing.

The worst schemas I have seen are the ones designed by developers who were thinking about the first feature instead of the tenth. They add role as a boolean column on the users table — is_admin — because right now there are only two roles. Six months later there are five roles and the migration is painful. The right question at schema design time is never "what do I need now?" It is "what will I regret not building in?"

Multi-tenancy is where I see the most expensive mistakes. Developers add tenant_id to three tables and forget it on the fourth. That fourth table becomes a cross-tenant data leak waiting to happen. RLS is the insurance policy. It will save you from yourself at 2am when you are shipping a hotfix under pressure and forget a WHERE clause.

The future of SaaS schema design is moving toward event sourcing for the audit and compliance layer — storing what happened rather than the current state. The current-state model is great for reads but loses history. Event sourcing preserves history but is complex to query. The pragmatic middle ground — current state tables plus an append-only audit_logs table — is where most production SaaS should live today.

And one more thing nobody says out loud: the decision between row-level tenancy and database-per-tenant is ultimately a sales decision, not a technical one. You use row-level tenancy until an enterprise customer demands dedicated infrastructure as a contract condition. Then you build it for them, charge more for it, and everyone wins.

Comparison: Multi-Tenancy Approaches at Scale

Real Developer Use Case

A developer building a project management SaaS started with a simple schema — projects, tasks, users — with no tenant_id anywhere because they were building for a single customer in the early days.

Three months later they onboarded their second customer. Adding tenant_id to four existing tables with live data, backfilling the values, updating every query, and enabling RLS took two weeks of migration work and introduced two production bugs during the process.

The developer who came after them added tenant_id to every new table on day one. They never had to do that migration. Two weeks of pain versus two minutes of foresight.

The schema is the one place in SaaS development where thinking ten steps ahead is not premature optimisation. It is the minimum viable level of care.

Interactive Demo: Row Level Security Simulator

Try this interactive simulation to see how RLS filters data based on the current tenant ID.

  

    
RLS Evaluation Engine
    

      Acme Corp (T1)
      Globex (T2)
      Service Role (Bypass)
    
  
  
  

    
  

  

    
    

      

        PostgreSQL Table (projects)
        All Rows
      
      

        
      
    
    
    
    

      

        Application Response
        2 Rows
      
      

        
      
    
  

Frequently Asked Questions

Should I use UUID or integer primary keys in a SaaS database?
Always UUID for SaaS. Integer primary keys expose business information — a competitor signing up as customer 12 knows you have 11 customers. They are also unsafe in distributed systems where multiple nodes generate IDs simultaneously. UUIDs are globally unique, do not leak row counts, and are safe to expose in URLs and APIs. The slight performance overhead is negligible compared to the benefits.

When should I move from row-level tenancy to database-per-tenant?
Move specific tenants to dedicated databases when the contract value justifies the operational overhead, when the tenant has compliance requirements (HIPAA, FedRAMP, GDPR data residency) that mandate data isolation, or when a single tenant's query load is affecting other tenants' performance. Do not migrate your entire product to database-per-tenant — maintain a hybrid where most tenants share infrastructure and high-value tenants get dedicated instances.

How do I handle schema migrations in a multi-tenant SaaS without downtime?
Use expand-contract migrations. First expand — add the new column as nullable with a default. Deploy application code that writes to both old and new columns. Backfill existing rows. Make the column non-nullable once backfill is complete. Deploy code that reads only from the new column. Then contract — drop the old column. This sequence allows zero-downtime migrations on tables with millions of rows.

Should every table have soft deletes?
Not every table — only tables where data has audit, compliance, recovery, or reference value. User accounts, tenant records, subscription data, and any user-generated content should use soft deletes. Ephemeral data — sessions, temporary tokens, rate limit counters — can be hard deleted. The practical rule: if someone could ask "what happened to X?" in a support ticket, X should have a soft delete.

How do I prevent one tenant's heavy queries from slowing down others?
This is called the noisy neighbour problem. Short-term: add appropriate indexes, use connection pooling (PgBouncer), and set statement timeouts to prevent runaway queries from holding locks. Medium-term: move reporting and analytics queries to read replicas so they do not compete with transactional queries. Long-term: move genuinely heavy tenants to dedicated database instances where their workload does not affect others.

Conclusion

A well-designed SaaS database schema is not something you build once and revisit when things break. It is a foundation you design carefully in week one because everything built on top of it inherits its constraints and its capabilities.

Start with row-level tenancy, UUIDs, soft deletes, and RLS enabled from day one. Add tenant_id to every table before you write your first query. Build the audit log table before you need it for a compliance requirement.

The developers who avoid painful migrations are not the ones who are smarter about SQL. They are the ones who asked "what will I regret not building in?" before they wrote the first CREATE TABLE.

Related reads: How to Create a SaaS with Next.js and Supabase · How SaaS Companies Actually Make Money · How to Deploy Next.js on Vercel Step-by-Step · Why Apps Crash During High Traffic