SaaS Database Design Guide
A production SaaS database starts with four core tables — users, organisations, memberships, and subscriptions — with every subsequent table including an organisation_id foreign key for tenant isolation. Row-level security enforces that isolation at the database level — independent of application code.
The database schema is the most expensive thing to change in a live SaaS product. Getting it right before the first user is the highest-leverage technical decision you make.
▸ Design Principles
Six non-negotiable rules for SaaS database design
These six principles apply to every table in every SaaS product we build at 4Byte Agency. Violating any one of them creates problems that compound as the product scales.
Tenant isolation from migration zero
Every table that holds customer data has an organisation_id column with a NOT NULL constraint and a foreign key to the organisations table. This is established in the first migration — not added later.
RLS on every tenant table
Row-level security policies are enabled alongside the creation of every tenant table. Application-layer authorisation is the first line of defence; RLS is the last. Both must exist.
UUIDs as primary keys
All tables use UUID v4 primary keys — not auto-incrementing integers. This prevents ID enumeration attacks, works across distributed systems, and integrates natively with Supabase Auth.
Timestamps on every table
Every table has created_at and updated_at columns with timezone awareness (timestamptz). These enable audit trails, data exports, sync operations, and incremental queries — all of which you will eventually need.
Soft deletes over hard deletes
Tenant data is never permanently deleted without an explicit data retention policy. A deleted_at timestamptz column (null = active) enables safe recovery, audit compliance, and prevents cascade issues.
Indexes before queries, not after
Foreign key columns and WHERE-clause columns are indexed in the same migration that creates the table. Retroactive indexing on large tables requires CONCURRENTLY mode and careful timing to avoid locks.
▸ Schema Patterns
The complete SaaS schema — table by table
Every column, every constraint, every RLS note — exactly as 4Byte implements on day one of every SaaS engagement.
usersIndividual authenticated identities. Managed by Supabase Auth — do not duplicate auth fields.
Column Definitions
id uuid PRIMARY KEY DEFAULT gen_random_uuid()email text UNIQUE NOT NULLfull_name textavatar_url textcreated_at timestamptz DEFAULT now() NOT NULLupdated_at timestamptz DEFAULT now() NOT NULLRLS Policy Note
Users can only SELECT their own row. Updates require matching auth.uid() = id.
organisationsThe tenant entity. Every customer account is one organisation. All other tenant data has an organisation_id pointing here.
Column Definitions
id uuid PRIMARY KEY DEFAULT gen_random_uuid()name text NOT NULLslug text UNIQUE NOT NULLplan text NOT NULL DEFAULT 'free'stripe_customer_id text UNIQUEcreated_at timestamptz DEFAULT now() NOT NULLupdated_at timestamptz DEFAULT now() NOT NULLRLS Policy Note
Users can SELECT organisations they are members of. Only owners can UPDATE.
membershipsJoin table between users and organisations. The role column drives all RBAC decisions across the product.
Column Definitions
id uuid PRIMARY KEY DEFAULT gen_random_uuid()user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADEorganisation_id uuid NOT NULL REFERENCES organisations(id) ON DELETE CASCADErole text NOT NULL CHECK (role IN ('owner', 'admin', 'member'))created_at timestamptz DEFAULT now() NOT NULLUNIQUE(user_id, organisation_id)RLS Policy Note
Members can SELECT their own memberships. Owners can INSERT/DELETE others. Enforce role hierarchy in a DB function.
invitationsPending team invitations before the invited user creates an account. Token-based, time-limited.
Column Definitions
id uuid PRIMARY KEY DEFAULT gen_random_uuid()organisation_id uuid NOT NULL REFERENCES organisations(id) ON DELETE CASCADEemail text NOT NULLrole text NOT NULL DEFAULT 'member'token text UNIQUE NOT NULL DEFAULT gen_random_uuid()expires_at timestamptz NOT NULLaccepted_at timestamptzcreated_at timestamptz DEFAULT now() NOT NULLRLS Policy Note
Organisation admins can SELECT and INSERT invitations for their organisation only.
subscriptionsMirrors Stripe subscription state. Updated exclusively via webhook handlers — never directly from the frontend.
Column Definitions
id uuid PRIMARY KEY DEFAULT gen_random_uuid()organisation_id uuid NOT NULL REFERENCES organisations(id) ON DELETE CASCADEstripe_subscription_id text UNIQUE NOT NULLstripe_price_id text NOT NULLstatus text NOT NULLplan_id text NOT NULLcurrent_period_start timestamptz NOT NULLcurrent_period_end timestamptz NOT NULLcancel_at_period_end boolean NOT NULL DEFAULT falsecreated_at timestamptz DEFAULT now() NOT NULLupdated_at timestamptz DEFAULT now() NOT NULLRLS Policy Note
Members can SELECT their organisation's subscription. Only service-role key can INSERT/UPDATE (via webhook handler).
Extending the Schema
Every feature-specific table you add follows the same pattern: uuid PRIMARY KEY + organisation_id NOT NULL REFERENCES organisations(id) ON DELETE CASCADE + created_at / updated_at + RLS enabled. This pattern applies to every table that holds tenant data without exception.
▸ Data Relationships
The four relationship patterns in SaaS database design
Every table in your SaaS schema relates to others through one of these four patterns. Knowing which pattern applies — and how to implement it correctly — prevents structural problems that appear as your data scales.
Organisation → Projects
The most common SaaS relationship. One organisation has many projects. Implemented via a foreign key (organisation_id) on the child table.
Implementation Pattern
projects.organisation_id REFERENCES organisations(id) ON DELETE CASCADEDelete Strategy
CASCADE — deleting an organisation deletes all its projects. Always the right choice for owned child records.
Users ↔ Organisations (via memberships)
A user can belong to multiple organisations, and an organisation has many users. Requires a join table (memberships) with foreign keys to both sides and a UNIQUE constraint on the combination.
Implementation Pattern
memberships(user_id, organisation_id) with UNIQUE(user_id, organisation_id)Delete Strategy
CASCADE on both sides — deleting a user removes their memberships; deleting an org removes all memberships.
Comments → Parent Comments (threaded)
A record references another record of the same type. Used for threaded comments, nested categories, org hierarchies. Implemented via a nullable parent_id foreign key on the same table.
Implementation Pattern
comments.parent_id REFERENCES comments(id) ON DELETE CASCADE NULLABLEDelete Strategy
CASCADE (deletes children with parent) or SET NULL (orphans children). Choose based on product behaviour.
Attachments → (Projects | Tasks | Comments)
A table references one of several possible parent types. PostgreSQL doesn't natively support polymorphic FKs — use separate nullable FK columns per parent type or a separate junction table per relationship.
Implementation Pattern
attachments(project_id NULLABLE, task_id NULLABLE, comment_id NULLABLE) — only one non-nullDelete Strategy
Handle each FK separately with appropriate CASCADE or SET NULL behaviour per parent type.
▸ Indexing Strategy
Database indexes — when to add them and which kind
Missing indexes are the most common cause of SaaS database performance problems. Here are the four index types, when each applies, and real SQL examples for each.
Foreign Key Indexes
Every foreign key column must have an index. Without them, JOINs and CASCADE operations perform full table scans — which become catastrophic at scale.
SQL Examples
CREATE INDEX idx_memberships_user_id ON memberships(user_id)CREATE INDEX idx_memberships_org_id ON memberships(organisation_id)CREATE INDEX idx_subscriptions_org_id ON subscriptions(organisation_id)Filter Column Indexes
Columns used in WHERE clauses need indexes — especially status, type, and date columns used for filtering active records, event logs, and time-range queries.
SQL Examples
CREATE INDEX idx_subscriptions_status ON subscriptions(status)CREATE INDEX idx_items_deleted_at ON items(deleted_at) WHERE deleted_at IS NULLCREATE INDEX idx_events_created_at ON events(created_at DESC)Composite Indexes
When queries commonly filter by two columns together (organisation_id AND status, organisation_id AND created_at), a composite index eliminates two separate index lookups.
SQL Examples
CREATE INDEX idx_items_org_status ON items(organisation_id, status)CREATE INDEX idx_items_org_created ON items(organisation_id, created_at DESC)CREATE INDEX idx_events_org_type ON events(organisation_id, event_type)Partial Indexes
For soft-delete patterns, a partial index on non-deleted records is dramatically smaller and faster than a full-column index — because it only indexes the active rows.
SQL Examples
CREATE INDEX idx_items_active ON items(organisation_id) WHERE deleted_at IS NULLCREATE INDEX idx_users_verified ON users(email) WHERE email_confirmed_at IS NOT NULLDiagnosing missing indexes in production: Run EXPLAIN ANALYZE on slow queries. A Seq Scan on a large table is a missing index signal. A Index Scan means the index is being used. Supabase provides a query performance dashboard that highlights the slowest queries automatically.
▸ Migration Best Practices
Six rules for safe database migrations on live SaaS products
A migration mistake on a live SaaS database can lock tables, corrupt data, or cause downtime. These rules prevent the most common and most expensive migration failures.
Never drop a column in the same migration as the code change that removes it
SafetyIf the migration runs before the code deploy, the column is gone before the code stops reading it. Always deploy the code change first, then drop the column in a follow-up migration after the old code is no longer running.
Add columns as nullable first, then add NOT NULL after backfilling
SafetyAdding a NOT NULL column without a default to a table with existing rows will fail immediately. Add as nullable, backfill existing rows, then add the constraint in a third migration after all rows have values.
Create indexes CONCURRENTLY on live databases
PerformanceStandard CREATE INDEX takes an exclusive lock on the table — blocking all reads and writes for its duration. CREATE INDEX CONCURRENTLY builds the index without locking, taking longer but never blocking production traffic.
Test migrations on a staging clone before production
SafetyA migration that runs in 10ms on a 1,000-row staging table may take 8 minutes on a 50-million-row production table. Always test against a production-scale data clone before deploying migrations to live.
Keep migrations additive — never rewrite history
IntegrityNever modify an already-applied migration file. If you need to change something, create a new migration that makes the correction. Rewriting applied migrations causes schema drift between environments and breaks the migration chain.
Wrap multi-statement migrations in explicit transactions
IntegrityIf a migration with multiple statements fails partway through without a transaction, the database is left in a partial state. Explicit BEGIN / COMMIT ensures either all changes apply or none do.
▸ Performance Patterns
Six database performance patterns every SaaS product needs
These patterns address the most common database performance problems across the 45+ SaaS products 4Byte Agency has shipped — with real impact numbers for each.
Pagination with Cursors, not OFFSET
The Problem
OFFSET-based pagination (LIMIT 20 OFFSET 1000) forces the database to scan and discard 1,000 rows on every page request. Performance degrades linearly as page number increases.
The Solution
Cursor-based pagination uses WHERE created_at < :cursor ORDER BY created_at DESC LIMIT 20 — the database seeks directly to the cursor position and reads only 20 rows, regardless of page depth.
Select Only Required Columns
The Problem
SELECT * fetches every column — including large TEXT, JSONB, and bytea columns — even when only 2-3 fields are needed. This wastes bandwidth, memory, and serialisation time.
The Solution
Always specify required columns in SELECT statements. With Prisma, use select: { id: true, name: true, status: true } instead of finding the full record.
Avoid N+1 Queries
The Problem
Fetching a list of 50 records then making a separate query for each record's related data results in 51 queries. At scale this causes visible latency and database overload.
The Solution
Use Prisma's include for eager loading related data in a single query, or use raw SQL JOINs for complex relationships. Audit every API route that returns a list of records.
JSONB for Flexible Attributes
The Problem
Adding a new column for every flexible product attribute (custom fields, metadata, settings) causes schema bloat, frequent migrations, and sparse rows where most records have NULL in most columns.
The Solution
Store flexible, optional, or highly variable attributes in a JSONB column (metadata, settings, custom_fields). JSONB is indexable, queryable, and avoids schema changes for attribute additions.
Database-Level Constraints
The Problem
Relying solely on application code to enforce data validity (unique emails, valid enums, non-null fields) allows invalid data to reach the database when bugs or race conditions occur.
The Solution
Duplicate critical validation at the database level: UNIQUE constraints, CHECK constraints for enums, NOT NULL for required fields, and FOREIGN KEY constraints with appropriate ON DELETE behaviour.
Connection Pooling via PgBouncer
The Problem
PostgreSQL has a hard limit on simultaneous connections (typically 100 default). Each serverless function invocation opens a new connection — 200 concurrent users can exhaust the connection pool instantly.
The Solution
Route all database connections through PgBouncer (built into Supabase). Connection pooling multiplexes thousands of application connections across a small pool of actual database connections.
▸ Common Mistakes
Database design mistakes that break SaaS products at scale
These six mistakes appear regularly in SaaS products that come to 4Byte Agency for rebuild or rescue. All are cheap to prevent and expensive to fix retroactively.
Using auto-increment integers as primary keys
Consequence
Integer IDs are sequential and guessable. Users can enumerate other tenants' resources by incrementing the ID in URLs. Also causes conflicts in distributed or multi-region setups.
4Byte Standard
Use UUID v4 or UUID v7 (time-ordered) as primary keys. Supabase provides gen_random_uuid() as a default. UUIDs are non-guessable and work across distributed systems.
Storing Stripe data without webhook verification
Consequence
If subscription state is written from the frontend without webhook verification, a malicious user can fake a payment and gain access to paid features by sending a crafted request.
4Byte Standard
All Stripe subscription state changes must come exclusively through webhook handlers that verify the Stripe-Signature header. Never trust frontend-supplied subscription status.
No updated_at trigger
Consequence
Without an automated updated_at trigger, developers must remember to manually update the timestamp on every write — which they inevitably forget. Stale updated_at values break sync operations, caches, and audit trails.
4Byte Standard
Create a PostgreSQL trigger function that automatically updates updated_at to now() on every row UPDATE. Apply it to every table that has an updated_at column.
Putting business logic in the database layer
Consequence
Stored procedures and complex triggers make business logic invisible to code reviewers, impossible to unit test, and difficult to debug. Schema changes become risky when logic is spread across application and database layers.
4Byte Standard
Keep database logic minimal — constraints, indexes, and RLS policies only. All business logic lives in the application layer where it can be tested, versioned, and reviewed.
Over-normalising the schema prematurely
Consequence
Splitting data into too many tables for theoretical purity results in complex multi-table JOINs for every query. At SaaS scale, query complexity hurts more than minor denormalisation.
4Byte Standard
Start with a clean but pragmatic schema. Denormalise intentionally for frequently-joined, read-heavy data once you have query performance data — not upfront to avoid future JOINs.
No database-level enum validation
Consequence
Using a plain text column for status or type fields allows any string to be inserted — including typos, casing variants, and invalid states. Application-level validation alone is insufficient.
4Byte Standard
Use PostgreSQL CHECK constraints (CHECK (role IN ('owner', 'admin', 'member'))) or CREATE TYPE enums for all columns with a fixed set of valid values. The database rejects invalid values before they reach storage.
▸ 4Byte Database Standards
The complete database checklist before any SaaS goes live
Schema
- UUID primary keys on all tables
- organisation_id on every tenant table
- created_at + updated_at on every table
- deleted_at for soft-delete on user data
- UNIQUE constraints on email, slug, stripe IDs
- CHECK constraints on all enum-like columns
Security
- RLS enabled on every tenant table
- SELECT policy scoped to organisation membership
- INSERT/UPDATE/DELETE require role check
- Service-role key used only for webhook handlers
- No sensitive data in JWT payload
- Webhook handlers verify Stripe-Signature header
Performance
- Indexes on all foreign key columns
- Indexes on all WHERE-clause filter columns
- Composite indexes for common column pairs
- Partial indexes for soft-delete active rows
- Connection pooling via PgBouncer enabled
- EXPLAIN ANALYZE run on all list queries
Migration Safety
- Versioned migration files via Prisma Migrate
- Staging environment clone for migration testing
- Two-step process for column removal
- Nullable-first for new NOT NULL columns
- CONCURRENTLY flag for production indexes
- Explicit transactions on multi-statement migrations
▸ FAQ
Frequently asked questions about SaaS database design
How do you design a database for a SaaS product?+
Designing a SaaS database starts with four core tables: users, organisations, memberships, and subscriptions. Every subsequent table includes an organisation_id foreign key for tenant isolation. Row-level security (RLS) policies are enabled from the first migration to enforce data boundaries at the database level. Indexes are added on all foreign keys and commonly filtered columns from the start.
What is the best database for a SaaS product?+
PostgreSQL is the best database for most SaaS products in 2025. It supports row-level security for multi-tenant isolation, relational data modelling, JSONB columns for flexible data, full-text search, and scales to billions of rows. Most SaaS teams access PostgreSQL via Supabase (managed) with Prisma ORM for type-safe queries.
How do you handle multi-tenant data isolation in a database?+
The most common and cost-effective approach for SaaS MVPs is a shared database with organisation_id columns on every tenant table, enforced by PostgreSQL row-level security (RLS) policies. RLS policies ensure that even if application-level authorisation has a bug, the database layer prevents cross-tenant data access.
What database indexes does a SaaS product need?+
Every SaaS database needs: indexes on all foreign key columns (organisation_id, user_id, etc.), indexes on columns used in WHERE clauses for filtering (status, created_at, type), and composite indexes for frequently combined filters. The most impactful first index is always on organisation_id — the primary tenant isolation column.
How do you handle database migrations safely in a SaaS product?+
Safe SaaS database migrations follow these principles: never remove columns in the same migration that removes their usage in code (two-step deletion), always add columns as nullable first before making them required, use versioned migration files managed by Prisma Migrate, test migrations on a staging database before applying to production, and avoid long-running migrations that lock tables during peak usage.
Should a SaaS product use UUIDs or integer IDs?+
Use UUIDs (specifically UUID v4 or v7) as primary keys for SaaS products. UUIDs prevent ID enumeration attacks (users guessing other resource IDs), work across distributed systems without coordination, and integrate natively with Supabase Auth. The performance difference vs integer IDs is negligible for most SaaS workloads.
▸ Related Resources
Go deeper into SaaS technical design
▸ Get the schema right from day one
Need your SaaS database designed and built correctly?
We'll design your schema, write RLS policies, set up indexing, configure migrations, and build the entire product using the exact patterns from this guide — refined across 45+ production SaaS products.