database

Database & Data Modeling

Every SaaS app needs a database, and the schema decisions you make early are expensive to change later. This skill helps you choose the right database, design a clean schema, and set up security — explained without jargon.

Core Principles

• Choose the database that matches your hosting platform. Don't fight the defaults.
• Schema design is product design. Get the relationships right early — migrations are painful later.
• Every SaaS app is multi-tenant. Every table needs a way to isolate customer data.
• Start simple. You don't need Redis, Elasticsearch, or a data warehouse at $0-10k MRR.
• Row Level Security is not optional. One leaked customer seeing another's data kills trust.

Choosing a Database

For Most Solo Founders: Use What Your Platform Gives You

Building With	Default Database	Use It?
Supabase	PostgreSQL (built-in)	Yes — best option for most SaaS
Vercel + Prisma	Supabase, Neon, or PlanetScale	Yes — pick one, stick with it
Lovable	Supabase (integrated)	Yes — don't fight the integration
Replit	SQLite or Supabase	Supabase for production SaaS
Railway	PostgreSQL	Yes
Firebase	Firestore	Yes, if you're already in Google ecosystem

The short answer: Use Supabase (PostgreSQL) unless you have a specific reason not to. It gives you database + auth + storage + realtime + Row Level Security in one service.

When You Might Need Something Else

Need	Consider
Full-text search	Supabase has built-in text search. Only add Algolia/Typesense if it's not enough
Caching	Start without it. Add Upstash Redis only when you have measurable latency issues
File storage	Supabase Storage, Cloudflare R2, or S3
Analytics/reporting	Supabase views or materialized views first. Data warehouse later (post-$10k MRR)

---

Schema Design for SaaS

The Three Tables Every SaaS Needs

-- 1. Users (who uses the app)
create table users (
  id uuid primary key default gen_random_uuid(),
  email text unique not null,
  full_name text,
  avatar_url text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- 2. Organizations / Teams (multi-tenancy)
create table organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text unique not null,
  plan text default 'free',
  stripe_customer_id text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- 3. Memberships (who belongs to which org)
create table memberships (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references users(id) on delete cascade,
  org_id uuid references organizations(id) on delete cascade,
  role text default 'member' check (role in ('owner', 'admin', 'member')),
  created_at timestamptz default now(),
  unique(user_id, org_id)
);