SaaS Schema Design Patterns That Don't Come Back to Haunt You

We've reviewed a lot of SaaS codebases — our own included — and the pattern is almost always the same. The schema made total sense when there were 3 users. Then someone added teams. Then billing. Then audit logs. Then six months later you're staring at a `user_id` column that sometimes means the current user, sometimes means the original creator, and occasionally means a service account that doesn't even exist anymore.

Schema design for SaaS isn't glamorous, but it's load-bearing. Get it wrong and you'll spend the next two years writing migrations at 2am, untangling foreign keys you swore made sense at the time. This post covers the patterns that have actually served us well — and the anti-patterns we've had to migrate away from.

Start With the Organization, Not the User

The single biggest mistake in early SaaS schemas is modeling around users when you should be modeling around organizations (or workspaces, or teams — whatever your product calls them). Users are members of organizations. Organizations own everything else. If you wire data directly to users from day one, adding team features later means rewriting half your schema and all of your access control logic.

The core hierarchy looks like this: Organization → Members (users with roles) → Resources. Every resource in your system should trace back to an organization, not a user. Even if you're building a solo-user tool today, structure it this way from the start. You'll thank yourself when someone asks for a 'team plan' in month four.

-- The foundation: organizations own everything
CREATE TABLE organizations (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug        TEXT UNIQUE NOT NULL,  -- for URLs: /dashboard/acme-corp
  name        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Users exist independently of orgs
CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT UNIQUE NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- The junction: membership with roles
CREATE TABLE organization_members (
  org_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'
  invited_by  UUID REFERENCES users(id),
  joined_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (org_id, user_id)
);

-- Resources belong to orgs, not users
CREATE TABLE projects (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  created_by  UUID NOT NULL REFERENCES users(id),  -- audit, not ownership
  name        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ON projects(org_id);
CREATE INDEX ON organization_members(user_id);  -- find all orgs for a user

Notice `created_by` on projects — that's for audit purposes, not ownership. The project belongs to the org. If the user who created it leaves the organization, the project stays. This distinction sounds obvious but it's wrong in more codebases than it should be.

Billing: Keep It Separate and Link Late

Billing data lives in two places: your Stripe account and your database. Your job is to keep them loosely coupled and not duplicate more than you need to. The mistake we see constantly is storing subscription details directly on the organizations table. That column sprawl happens fast.

-- Separate billing table, not columns on organizations
CREATE TABLE billing_subscriptions (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id                UUID UNIQUE NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  stripe_customer_id    TEXT UNIQUE NOT NULL,
  stripe_subscription_id TEXT UNIQUE,
  plan_id               TEXT NOT NULL DEFAULT 'free',  -- 'free', 'pro', 'enterprise'
  status                TEXT NOT NULL DEFAULT 'active', -- mirrors Stripe status
  trial_ends_at         TIMESTAMPTZ,
  current_period_end    TIMESTAMPTZ,
  cancel_at_period_end  BOOLEAN NOT NULL DEFAULT false,
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Usage-based billing? Track it here
CREATE TABLE billing_usage (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  metric      TEXT NOT NULL,  -- 'api_calls', 'storage_gb', 'seats'
  quantity    INTEGER NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ON billing_usage(org_id, metric, recorded_at DESC);

One `billing_subscriptions` row per organization, linked via `stripe_customer_id`. When a webhook comes in from Stripe, you look up by `stripe_customer_id` or `stripe_subscription_id`, update this table, and you're done. No org-table migrations required when you add a new plan field.

Never store pricing amounts in your database. Store plan identifiers and look up prices from Stripe or a config file. Price changes shouldn't require database migrations.

Audit Logs: Design Them Before You Need Them

Every SaaS app eventually needs audit logs. Enterprise customers will ask for them on your first sales call. Building them in retroactively is miserable — you're basically adding event tracking to every mutation in your codebase at once. Design the table upfront and start writing to it from day one.

CREATE TABLE audit_logs (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id        UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  actor_id      UUID REFERENCES users(id) ON DELETE SET NULL,  -- null for system actions
  actor_type    TEXT NOT NULL DEFAULT 'user',  -- 'user', 'system', 'api_key'
  action        TEXT NOT NULL,  -- 'project.created', 'member.removed', 'billing.upgraded'
  resource_type TEXT,           -- 'project', 'member', 'subscription'
  resource_id   TEXT,           -- the ID of what changed
  metadata      JSONB,          -- before/after state, IP, user agent, etc.
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- You'll query this by org, filtered by time range
CREATE INDEX ON audit_logs(org_id, created_at DESC);
-- Optional: find all actions on a specific resource
CREATE INDEX ON audit_logs(resource_type, resource_id);

The `metadata` JSONB column is where you store whatever context makes sense for that action type. For a `member.removed` event, you might store `{removed_user_email, removed_by_email, previous_role}`. Don't over-normalize this — you want audit logs to be self-contained snapshots, not normalized records that break when the underlying data changes.

Use past-tense dot-notation actions: `project.created`, `project.deleted`, `billing.plan_changed`. Consistent naming makes filtering and display trivial. Invent your convention on day one and document it somewhere your team will actually look.

Soft Deletes vs Hard Deletes: Pick a Lane

This is a religious war in some teams, but here's our take: soft deletes by default for user-visible resources (projects, documents, members), hard deletes for everything else. The 'undo' and 'recover deleted item' features that product will eventually request? Much easier with soft deletes.

// Drizzle ORM schema with soft delete support
import { pgTable, uuid, text, timestamp, boolean } from 'drizzle-orm/pg-core';

export const projects = pgTable('projects', {
  id:        uuid('id').primaryKey().defaultRandom(),
  orgId:     uuid('org_id').notNull().references(() => organizations.id),
  name:      text('name').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  deletedAt: timestamp('deleted_at', { withTimezone: true }),  // null = active
});

// Helper to filter out deleted records — use this everywhere
export function notDeleted<T extends { deletedAt: unknown }>(table: T) {
  return isNull(table.deletedAt);
}

// Soft delete function
export async function softDeleteProject(db: DrizzleDB, projectId: string) {
  return db
    .update(projects)
    .set({ deletedAt: new Date() })
    .where(eq(projects.id, projectId));
}

// Always filter in your queries
export async function getOrgProjects(db: DrizzleDB, orgId: string) {
  return db
    .select()
    .from(projects)
    .where(and(
      eq(projects.orgId, orgId),
      isNull(projects.deletedAt)  // don't forget this
    ));
}

The trap with soft deletes is forgetting `deleted_at IS NULL` in queries. One missed filter and deleted data shows up in your UI. The helper function pattern above helps, but you should also add a check to your code review checklist. We've shipped bugs from exactly this.

• Add a partial index on deleted_at: CREATE INDEX ON projects(org_id) WHERE deleted_at IS NULL — makes your active-record queries fast
• If using unique constraints (like unique project names per org), they'll conflict with soft-deleted records — use partial unique indexes too
• Consider a separate 'trash' retention policy — hard delete soft-deleted records older than 30 days via a cron job
• Never soft delete security-sensitive data like API keys or auth tokens — hard delete those immediately

Invitations and the Pending Member Problem

Inviting someone to your SaaS by email sounds simple until you realize: what if they don't have an account yet? What if they sign up with a different email? What if the invite expires? You need an invitations table that lives separately from your members table.

CREATE TABLE organization_invitations (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email       TEXT NOT NULL,
  role        TEXT NOT NULL DEFAULT 'member',
  token       TEXT UNIQUE NOT NULL,  -- the secret in the invite link
  invited_by  UUID NOT NULL REFERENCES users(id),
  accepted_at TIMESTAMPTZ,
  expires_at  TIMESTAMPTZ NOT NULL DEFAULT (now() + INTERVAL '7 days'),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Prevent duplicate pending invites for the same email+org
CREATE UNIQUE INDEX ON organization_invitations(org_id, email)
  WHERE accepted_at IS NULL;

CREATE INDEX ON organization_invitations(token);
CREATE INDEX ON organization_invitations(email);  -- claim invites on signup

The `token` is what goes in the invite email URL. When someone accepts, you create the `organization_members` row and set `accepted_at`. The partial unique index means you can't send two pending invites to the same person for the same org, but once they've accepted, you can send new ones.

Also important: on user signup, check the `organization_invitations` table for pending invites matching their email. Auto-accept them. Nothing is more annoying than signing up, clicking an invite link, and being told 'invitation not found' because the flow expected you to click the link first.

Feature Flags and Plan Limits in the Schema

SaaS plans usually come with limits: 5 projects on free, unlimited on pro, 10 team members on starter. Where do these live? Not hardcoded in your application logic scattered across 40 files. Put them somewhere central.

// Define your plan limits in one place — a config file or database table
export const PLAN_LIMITS = {
  free: {
    projects: 3,
    members: 1,
    storageGb: 1,
    apiCallsPerMonth: 1000,
    features: ['basic_analytics'],
  },
  pro: {
    projects: 50,
    members: 10,
    storageGb: 50,
    apiCallsPerMonth: 100000,
    features: ['basic_analytics', 'advanced_analytics', 'custom_domain'],
  },
  enterprise: {
    projects: Infinity,
    members: Infinity,
    storageGb: Infinity,
    apiCallsPerMonth: Infinity,
    features: ['basic_analytics', 'advanced_analytics', 'custom_domain', 'sso', 'audit_logs'],
  },
} as const;

export type Plan = keyof typeof PLAN_LIMITS;
export type Feature = typeof PLAN_LIMITS[Plan]['features'][number];

// Check a limit before creating a resource
export async function canCreateProject(db: DrizzleDB, orgId: string): Promise<boolean> {
  const sub = await db.query.billingSubscriptions.findFirst({
    where: eq(billingSubscriptions.orgId, orgId),
  });
  
  const plan = (sub?.planId ?? 'free') as Plan;
  const limit = PLAN_LIMITS[plan].projects;
  
  if (limit === Infinity) return true;
  
  const count = await db
    .select({ count: sql<number>`count(*)` })
    .from(projects)
    .where(and(
      eq(projects.orgId, orgId),
      isNull(projects.deletedAt)
    ));
  
  return Number(count[0].count) < limit;
}

// Check feature access
export function hasFeature(plan: Plan, feature: Feature): boolean {
  return (PLAN_LIMITS[plan].features as readonly string[]).includes(feature);
}

This lives in one file. When you add a new plan or change a limit, you change it here and nowhere else. Gate checks happen in your server actions or API routes before mutations. No schema migration needed to change a plan limit.

Store what tier an org is on. Derive the limits from code. This separation means you can change limits without touching your database.

UUID vs. Auto-increment: Just Use UUIDs

This debate was mostly settled years ago, but we still see `SERIAL` primary keys in new SaaS schemas. Use UUIDs. Specifically, use `gen_random_uuid()` in PostgreSQL (v4 UUIDs) or `uuid_generate_v7()` if you want time-ordered UUIDs that index better.

The practical reasons: UUIDs are safe to expose in URLs without leaking enumeration information, they work across distributed systems if you ever get there, and you can generate them client-side before the database insert (useful for optimistic updates). The performance penalty versus auto-increment is real but negligible until you're at a scale where you have bigger problems.

One pattern we like: use UUIDs for all primary keys but also add short human-readable IDs for customer-facing references. Think `proj_4xkR9m` style IDs for support conversations. Implement these as a generated column or create them in your application layer.

The Timestamps You'll Always Wish You Had

Every table should have at minimum `created_at`. Most should have `updated_at`. Some should have `deleted_at` (soft deletes). But there are a few more timestamps that are worth adding to specific tables before you need them:

• users.last_seen_at — invaluable for churn analysis and 'users inactive for 30 days' emails
• users.email_verified_at — not just a boolean, you want to know when they verified
• organizations.trial_started_at — separate from billing, for analytics
• organization_members.last_active_at — per-org activity, not global user activity
• billing_subscriptions.churned_at — track when they actually cancelled vs when access ends

Adding a timestamp column to a large table later is a zero-downtime migration (nullable column, backfill separately), but you lose historical data you can never recover. Add the columns before you need them.

Also: always use `TIMESTAMPTZ` (timestamp with time zone) in PostgreSQL. Never `TIMESTAMP`. Store everything in UTC. Display in user's timezone in the frontend. This rule has saved us from more bugs than I can count.

The Schema You're Building Into a Template

If you're starting a new SaaS project, you probably don't want to design all this from scratch every time. The patterns in this post — org-first hierarchy, separate billing table, invitations flow, audit logs, soft deletes — are exactly what we've built into the peal.dev templates. The schema decisions are already made, the migrations are written, and the TypeScript types are generated. You can spend day one on product instead of schema archaeology.

But even if you're not using a template, writing down your schema patterns in a `SCHEMA.md` in your repo is worth doing. Future you (and future teammates) will genuinely appreciate knowing why the `organization_invitations` table has a partial unique index instead of a regular one.

The best schema is the one you can explain to a new team member in 15 minutes. If you can't, you've accumulated too much implicit knowledge in your database design.

The Patterns That Actually Matter

To pull it all together: model around organizations, not users. Keep billing separate. Design audit logs before you need them. Soft delete user-visible resources. Define plan limits in code, not schema. Use UUIDs and timestamps with time zones everywhere. These aren't revolutionary insights — they're the boring stuff that separates a schema you can evolve from one that fights you every time you need to change it.

The schema migrations you'll write in six months are already determined by the decisions you make today. Most of ours that turned into painful migrations were cases where we knew the right pattern, decided to 'do it properly later', and then paid the price when 'later' arrived at the worst possible time. Do it right the first time. Your 2am self will be grateful.