Schema Design Patterns for SaaS Apps That Won't Make You Cry at 3am

We made the same mistake twice before we stopped counting. First with a side project, then with a client app, then — embarrassingly — with an early version of peal.dev's own internal tooling. The mistake: designing the schema around how the app works *today* instead of how it'll need to work in three months when a paying customer asks for team accounts, audit logs, or the dreaded "can we have sub-organizations?".

Database schemas are the one thing in software that's genuinely hard to change after the fact. You can refactor components, swap out libraries, rewrite API routes — painful, but doable. Migrating millions of rows across a restructured schema while keeping the app live? That's the stuff of 3am Slack messages and silent suffering. So let's get it right the first time.

Start With Multi-Tenancy, Even If You Have One Tenant

The most common SaaS schema mistake is building a single-tenant schema and bolting multi-tenancy on later. If you're building anything that might eventually have organizations, teams, or workspaces — and most SaaS apps do — design for it from day one. Even if you launch with a simple "one user = one account" model.

There are three main approaches to multi-tenancy in a relational database: separate databases per tenant, separate schemas per tenant, or a shared schema with a tenant ID column on every table. For most SaaS apps under 10,000 tenants, the shared schema approach with row-level security is the right call. It's operationally simpler and Postgres's RLS handles the isolation cleanly.

-- The foundation: every resource belongs to an organization
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  plan TEXT NOT NULL DEFAULT 'free',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  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', -- 'owner', 'admin', 'member'
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(organization_id, user_id)
);

-- Every resource table gets organization_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_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index everything you'll filter on
CREATE INDEX idx_projects_organization_id ON projects(organization_id);
CREATE INDEX idx_org_members_user_id ON organization_members(user_id);

Notice the `organization_members` junction table instead of just `user.organization_id`. This is intentional — users will eventually need to belong to multiple organizations, and when that feature request comes in (it always does), you won't need a migration. You're already there.

UUIDs vs. Auto-Increment: Just Use UUIDs

This debate still happens in 2024 and it shouldn't. Use UUIDs for your primary keys. Yes, they're slightly bigger. Yes, random UUIDs cause index fragmentation. No, it doesn't matter at the scale you're at. What does matter: you can generate IDs client-side before inserting, you're not leaking record counts through sequential IDs, and merging data from multiple sources or environments becomes trivial.

If index fragmentation genuinely concerns you at scale, use UUIDv7 (time-ordered) instead of UUIDv4. Postgres 17 has `gen_random_uuid()` built in for v4. For v7, you can use a small helper function or a library. Either way, stop putting auto-increment integers in your public-facing URLs — that's how competitors learn your user acquisition rate.

-- UUIDv7 helper in Postgres (time-ordered, better index performance)
CREATE OR REPLACE FUNCTION generate_ulid() RETURNS TEXT AS $$
DECLARE
  encoding   BYTEA = '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
  output     TEXT = '';
  unix_time  BIGINT;
  ulid       BYTEA;
BEGIN
  unix_time = (EXTRACT(EPOCH FROM CLOCK_TIMESTAMP()) * 1000)::BIGINT;
  ulid = decode(lpad(to_hex(unix_time), 12, '0'), 'hex');
  ulid = ulid || gen_random_bytes(10);
  FOR i IN 0..15 LOOP
    output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, i >> 1) >> (CASE WHEN i % 2 = 0 THEN 3 ELSE 0 END)) & 31));
  END LOOP;
  RETURN output;
END
$$ LANGUAGE plpgsql VOLATILE;

Soft Deletes: A Love-Hate Relationship

Soft deletes — adding a `deleted_at` column instead of actually removing rows — seem like a great idea until you've written your 47th query that has to remember to add `WHERE deleted_at IS NULL`. They're also a trap for unique constraints: if someone deletes a project named "Marketing" and creates a new one with the same name, your `UNIQUE(organization_id, name)` constraint will blow up.

That said, soft deletes genuinely earn their keep in two scenarios: when you need audit trails (compliance, debugging) and when users need to "undo" deletions. The trick is to not use them everywhere by default. Be deliberate. Apply them to high-value entities like organizations, users, and billing records. For ephemeral stuff like notifications or log entries, just hard delete.

// In Prisma, using soft deletes selectively
// schema.prisma
model Organization {
  id         String    @id @default(uuid())
  name       String
  slug       String    @unique
  deletedAt  DateTime? @map("deleted_at")
  createdAt  DateTime  @default(now()) @map("created_at")

  @@map("organizations")
}

// Wrap your Prisma client to auto-filter soft deletes
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient().$extends({
  query: {
    organization: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
      async findFirst({ args, query }) {
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
    },
  },
})

// When you actually need the deleted ones, bypass the extension
const allIncludingDeleted = await prisma.$extends({}).organization.findMany()

Rule of thumb: soft delete things your customers created and would be upset to lose permanently. Hard delete everything else. Logs, sessions, temp files — gone for real.

The Audit Log Pattern Nobody Implements Until It's Too Late

We've had two separate clients come to us asking to retroactively add audit logging to their apps. Both times the answer was: you can't, not fully, because you didn't log the right things at write time. An audit log isn't just knowing *that* something changed — it's knowing what it changed *from* and *to*, who did it, and when.

The cleanest pattern is a generic audit_logs table that captures before/after state as JSONB. It's not as queryable as purpose-built tables, but it's flexible enough to work across your entire schema without touching every model.

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  actor_id UUID REFERENCES users(id) ON DELETE SET NULL,
  action TEXT NOT NULL, -- 'created', 'updated', 'deleted', 'invited', etc.
  resource_type TEXT NOT NULL, -- 'project', 'member', 'subscription'
  resource_id UUID NOT NULL,
  before_state JSONB,
  after_state JSONB,
  metadata JSONB, -- IP address, user agent, etc.
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Query performance matters here — you'll sort by created_at a lot
CREATE INDEX idx_audit_logs_org_created ON audit_logs(organization_id, created_at DESC);
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX idx_audit_logs_actor ON audit_logs(actor_id, created_at DESC);

Wire this up at the service layer, not the database trigger layer. Database triggers are seductive but they lose context — they don't know *who* made the change, only that it happened. Your API handlers know the authenticated user. That's where you want to log from.

Subscriptions and Billing: Don't Reinvent Stripe

When it comes to billing schema, the single biggest mistake is trying to replicate Stripe's data model in your own database. Stripe already has the source of truth for payment methods, invoices, and subscription state. Your job is to store just enough to answer the question "does this organization have access to this feature right now?" without hitting Stripe's API on every request.

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  stripe_customer_id TEXT UNIQUE,
  stripe_subscription_id TEXT UNIQUE,
  plan TEXT NOT NULL DEFAULT 'free', -- 'free', 'pro', 'enterprise'
  status TEXT NOT NULL DEFAULT 'active', -- 'active', 'past_due', 'canceled', 'trialing'
  current_period_start TIMESTAMPTZ,
  current_period_end TIMESTAMPTZ,
  cancel_at_period_end BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Feature flags derived from plan
-- Instead of checking plan everywhere, derive capabilities
CREATE VIEW organization_capabilities AS
SELECT
  o.id as organization_id,
  s.plan,
  s.status,
  CASE
    WHEN s.plan = 'free' THEN 3
    WHEN s.plan = 'pro' THEN 25
    WHEN s.plan = 'enterprise' THEN 999999
    ELSE 3
  END as max_projects,
  s.plan IN ('pro', 'enterprise') AND s.status = 'active' as can_use_api,
  s.plan = 'enterprise' AND s.status = 'active' as can_use_sso
FROM organizations o
LEFT JOIN subscriptions s ON s.organization_id = o.id;

That view is a game-changer. Instead of scattering `if (plan === 'pro')` checks across your codebase, you query capabilities once and make decisions. When you add a new plan tier or change what's included, you update one place.

Migrations: The Part Everyone Gets Wrong

A schema is only as good as your ability to change it safely. A few hard-won rules from shipping migrations on live production databases:

Never drop a column in the same migration that removes it from application code. Remove from code first, deploy, then drop in a separate migration. Old instances of your app will still reference the old column during rolling deploys.
Adding a NOT NULL column to a large table will lock it. Add it as nullable, backfill the data, then add the NOT NULL constraint separately.
Adding an index on a production table? Use CREATE INDEX CONCURRENTLY. It's slower but doesn't lock reads/writes.
Every migration should be reversible. Write the down migration even if you think you'll never use it. You will.
Test migrations against a production-sized data dump, not your 12-row dev database. A migration that takes 200ms locally can take 45 minutes on real data.

The best time to think about rolling back a migration is before you write it, not at 2am when the deployment is stuck and the CEO is asking why the app is down.

We've shipped our share of templates on peal.dev with these patterns baked in — the multi-tenancy foundation, the audit log setup, the Stripe subscription sync. The point isn't to sell you a template, it's that these patterns are generic enough to work across almost any SaaS product. You shouldn't have to rediscover them the hard way on your fifth project like we did.

The One Schema Decision You Can't Undo

If we had to pick the single most important schema decision: get your tenant isolation model right before you have data. Everything else — column names, indexes, even table structure — can be migrated with enough care. But if you start storing all your customers' data in a flat namespace with no tenant concept, adding that later requires touching every table, every query, every API endpoint. We've done it. It's weeks of work and it's terrifying to ship.

Start with `organization_id` on every resource table. Even if you never build team features, even if it's just one user per account — the column costs you nothing and the insurance is worth everything. Future you, four months in with 200 paying customers asking for team invites, will send past you a thank-you note.