You're three months into building your SaaS and the schema you designed in an afternoon is starting to fight back. Adding a new feature requires touching six tables. A simple billing query takes 800ms. Your multi-tenant data is one missing WHERE clause away from a support nightmare. We've been there. Twice, actually — once for each time we thought we were smarter than we needed to be.
Schema design isn't glamorous. Nobody tweets about their beautifully normalized junction table. But it's the decision that silently determines whether you're shipping features in six months or rewriting everything because the foundation cracked. This post covers the patterns we actually use when building SaaS applications — the ones that scale without requiring a database administrator on retainer.
The Organization/Workspace Layer: Get This Right First
Almost every SaaS has the same top-level entity: a thing that owns resources and has members. Whether you call it an organization, workspace, team, or account doesn't matter. What matters is that you design this layer before anything else touches the database, because it propagates everywhere.
The classic mistake is building the user as the primary owner of resources first, then bolting on organizations later. You end up with a half-tenant, half-user ownership mess where some resources have a userId and some have an organizationId and some have both, and your WHERE clauses look like a crime scene.
-- Start with this shape. Everything hangs off organizations.
CREATE TABLE organizations (
id TEXT PRIMARY KEY DEFAULT gen_ulid(),
slug TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
plan TEXT NOT NULL DEFAULT 'free',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ -- soft delete, trust us
);
CREATE TABLE organization_members (
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'member', -- 'owner' | 'admin' | 'member'
invited_by TEXT REFERENCES users(id),
joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (organization_id, user_id)
);
-- Every resource table gets this column. Non-negotiable.
-- organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADENotice the slug column on organizations. You'll use this for subdomain routing (acme.yourapp.com), for URLs, and for support lookups. Generate it from the name at creation time, make it unique, let users change it once with a redirect. The id is for foreign keys. The slug is for humans.
Audit Columns: The Four You'll Regret Not Having
Every table in a SaaS application needs at minimum four columns that you'll desperately wish you had when you don't. Add them from the start. The cost is basically nothing.
- created_at — when was this row created. Default to now(), never let the application set this.
- updated_at — when was this row last modified. Trigger-maintained or ORM-maintained. Critical for cache invalidation and debugging.
- deleted_at — null means active, timestamp means soft-deleted. More on this in a second.
- created_by — which user_id created this. You'll need this for audit logs, activity feeds, and support tickets.
Soft deletes deserve a longer conversation. Hard deleting records in a SaaS feels clean until a customer emails support saying their data disappeared. With soft deletes, you can restore records, maintain referential integrity, keep audit trails, and avoid cascading delete nightmares. The trade-off is every query needs a WHERE deleted_at IS NULL. In Drizzle, you wrap that in a helper. In Prisma, you use middleware. Either way, it's one line of boilerplate for years of peace of mind.
Subscription and Plan State: Stop Querying Stripe in Hot Paths
Your subscription state lives in Stripe. Your application state needs to live in your database. These are not the same thing. The number of SaaS apps we've seen that call the Stripe API to check if a user has an active subscription on every page load is too high. That's a latency bomb and a Stripe rate limit waiting to happen.
CREATE TABLE subscriptions (
id TEXT PRIMARY KEY DEFAULT gen_ulid(),
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
stripe_customer_id TEXT UNIQUE NOT NULL,
stripe_subscription_id TEXT UNIQUE,
status TEXT NOT NULL DEFAULT 'trialing',
-- 'trialing' | 'active' | 'past_due' | 'canceled' | 'unpaid'
plan_id TEXT NOT NULL DEFAULT 'free',
plan_interval TEXT, -- 'month' | 'year'
current_period_start TIMESTAMPTZ,
current_period_end TIMESTAMPTZ,
trial_ends_at TIMESTAMPTZ,
cancel_at_period_end BOOLEAN NOT NULL DEFAULT false,
canceled_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Index for the most common lookup
CREATE INDEX idx_subscriptions_org_id ON subscriptions(organization_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status)
WHERE status NOT IN ('canceled');Your Stripe webhooks write to this table. Your application reads from this table. Stripe is the source of truth; your database is the fast cache. When a webhook arrives for customer.subscription.updated, you update the row. When your middleware needs to check plan limits, it reads from your database. Simple, fast, correct.
Keep a stripe_event_id column somewhere — or a dedicated webhook_events table — to handle duplicate webhook delivery. Stripe guarantees at-least-once delivery, not exactly-once. Idempotency isn't optional.
Flexible Metadata Without Going Full EAV
At some point you'll have attributes that vary per record. Custom fields, user preferences, feature-specific settings. The temptation is to add another column to the table every time. That works until you have 40 columns, half of them NULL most of the time, and your team is arguing about whether to add a column or a JSON blob.
The Entity-Attribute-Value (EAV) pattern — a separate table with entity_id, key, value columns — sounds flexible but it's a query nightmare. Joins multiply, aggregations become painful, and type safety evaporates. Instead, use a JSON column for structured, queryable metadata. Postgres handles this well.
// Drizzle schema with JSONB for flexible settings
import { pgTable, text, jsonb, timestamptz } from 'drizzle-orm/pg-core';
export const organizationSettings = pgTable('organization_settings', {
organizationId: text('organization_id')
.primaryKey()
.references(() => organizations.id, { onDelete: 'cascade' }),
// Typed as a specific shape — don't use `any`
branding: jsonb('branding').$type<{
logoUrl: string | null;
primaryColor: string | null;
customDomain: string | null;
}>(),
features: jsonb('features').$type<{
ssoEnabled: boolean;
apiAccessEnabled: boolean;
auditLogRetentionDays: number;
}>().default({
ssoEnabled: false,
apiAccessEnabled: false,
auditLogRetentionDays: 30,
}),
updatedAt: timestamptz('updated_at').notNull().defaultNow(),
});
// Query by JSONB field when needed
// SELECT * FROM organizations WHERE settings->>'plan' = 'enterprise';
// In Drizzle: sql`${organizationSettings.features}->>'ssoEnabled' = 'true'`One settings table per major entity (organization, user, project) keeps things organized without proliferating columns. You get the flexibility of JSON with the structure of a typed schema — as long as you actually type the JSONB columns and validate on write.
Invitation Flows: A Pattern People Usually Mess Up
Inviting team members to an organization sounds simple. It's not. You need to handle: inviting existing users, inviting email addresses that don't have accounts yet, re-inviting after expiry, revoking invitations, and preventing duplicate invites. Most schemas we've seen collapse all of this into one table that handles none of it well.
CREATE TABLE invitations (
id TEXT PRIMARY KEY DEFAULT gen_ulid(),
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
email TEXT NOT NULL,
role TEXT NOT NULL DEFAULT 'member',
token TEXT UNIQUE NOT NULL, -- secure random, used in invite link
invited_by TEXT NOT NULL REFERENCES users(id),
accepted_by TEXT REFERENCES users(id), -- set when accepted
status TEXT NOT NULL DEFAULT 'pending',
-- 'pending' | 'accepted' | 'revoked' | 'expired'
expires_at TIMESTAMPTZ NOT NULL DEFAULT (now() + INTERVAL '7 days'),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
accepted_at TIMESTAMPTZ,
-- One pending invite per email per org
CONSTRAINT unique_pending_invite
UNIQUE (organization_id, email) WHERE status = 'pending'
);
CREATE INDEX idx_invitations_token ON invitations(token)
WHERE status = 'pending';
CREATE INDEX idx_invitations_email ON invitations(email)
WHERE status = 'pending';The partial unique constraint on (organization_id, email) WHERE status = 'pending' is doing real work here — it prevents duplicate pending invitations without blocking re-inviting someone who previously accepted or whose invite expired. The partial index on token speeds up the lookup when a user clicks the invite link, while only indexing rows you'll actually query by token.
Usage and Limits: How to Track Without Killing Performance
Every SaaS eventually needs to enforce limits. Seats, API calls, storage, whatever your unit of value is. The naive approach is COUNT(*) queries every time someone tries to do something. That works until it doesn't — and on a serverless database with a connection pool, it hits harder than you expect.
The pattern that holds up: keep a denormalized counter table that gets updated transactionally with the operations that change the count. You're trading some write complexity for fast, cheap reads.
// Usage counters — updated via triggers or application logic
// Always in the same transaction as the thing being counted
export const usageCounters = pgTable('usage_counters', {
organizationId: text('organization_id')
.primaryKey()
.references(() => organizations.id, { onDelete: 'cascade' }),
seatCount: integer('seat_count').notNull().default(0),
projectCount: integer('project_count').notNull().default(0),
// For metered billing: reset monthly, track against billing period
apiCallsThisPeriod: integer('api_calls_this_period').notNull().default(0),
periodResetAt: timestamptz('period_reset_at').notNull(),
updatedAt: timestamptz('updated_at').notNull().defaultNow(),
});
// When adding a member, do this in one transaction:
async function addMember(db: Database, orgId: string, userId: string) {
return await db.transaction(async (tx) => {
// Check limit before inserting
const [usage] = await tx
.select()
.from(usageCounters)
.where(eq(usageCounters.organizationId, orgId))
.for('update'); // lock the row
const plan = await getPlanLimits(orgId);
if (usage.seatCount >= plan.maxSeats) {
throw new Error('SEAT_LIMIT_REACHED');
}
await tx.insert(organizationMembers).values({ organizationId: orgId, userId });
await tx
.update(usageCounters)
.set({ seatCount: sql`${usageCounters.seatCount} + 1` })
.where(eq(usageCounters.organizationId, orgId));
});
}The SELECT FOR UPDATE on the counter row prevents race conditions where two simultaneous requests both see seatCount = 4, both check it against a limit of 5, and both succeed — leaving you with 6 seats on a plan that allows 5. We learned this one the hard way. Not at 2am, but almost.
Indexes: The Ones You'll Actually Need
Schema design without talking about indexes is incomplete. Postgres doesn't add indexes automatically beyond the primary key, and your ORM definitely won't remind you. Here's the short list of indexes every SaaS schema should have from day one:
- Every foreign key column — Postgres doesn't auto-index these, and cascade operations without indexes are slow.
- user.email — you'll look up by email on every login. Make it UNIQUE and you get the index for free.
- Any status column you filter on frequently — use partial indexes to skip inactive/archived rows.
- created_at on high-volume tables — time-range queries are everywhere in dashboards and admin panels.
- Composite indexes for your most common WHERE clause patterns — don't index column by column, index the query.
Run EXPLAIN ANALYZE on your slow queries before adding indexes, not before. Every index adds write overhead and takes up space. The right time to add an index is when you have a query that's slow, not when you're guessing about the future.
Pulling It Together
The patterns here aren't magic. They're the result of building things, having them break in predictable ways, and fixing them enough times to see the patterns. Organization-first schema design. Consistent audit columns. Local subscription state synced from Stripe via webhooks. JSONB for flexible settings with typed columns. Partial constraints for business rules. Transactional counters for limit enforcement.
If you're starting a new SaaS and want these patterns wired up from day one — including Drizzle schemas, Stripe webhook handlers that keep subscription state fresh, and multi-tenant row-level security — our templates at peal.dev have this foundation built in. You can skip the three months of "oh we should have designed it this way" and start with the good version.
The most important thing, though, is making these decisions explicitly and early. A schema you designed consciously, even if imperfect, beats a schema that grew organically through feature additions every time. Write down why you made the choices you made. Future you, staring at a 2am production incident, will be grateful.
Your schema is the most expensive code to change in a running SaaS. Migrations on tables with millions of rows, foreign key changes, column renames — all of them have real downtime risk. Invest the thinking upfront. It's the one place where planning actually pays off faster than just shipping.