📐 Design Your Entire Schema in 30 Minutes

Stop overthinking. Your schema just needs to work, not win awards.

The Universal SaaS Schema:

-- 90% of SaaS apps need exactly this
CREATE SCHEMA app;

-- Users & Auth
CREATE TABLE app.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email CITEXT UNIQUE NOT NULL,
    password_hash TEXT,
    verified BOOLEAN DEFAULT FALSE,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Your Core Entity (projects/documents/whatever)
CREATE TABLE app.items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES app.users(id),
    title TEXT NOT NULL,
    content TEXT,
    data JSONB DEFAULT '{}',
    status TEXT DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Events/Analytics
CREATE TABLE app.events (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID REFERENCES app.users(id),
    event TEXT NOT NULL,
    properties JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for speed
CREATE INDEX ON app.items(user_id, created_at DESC);
CREATE INDEX ON app.events(user_id, created_at DESC);

Schema Anti-Patterns to Avoid:
❌ Over-normalization - Not everything needs its own table
❌ Premature optimization - Don't index everything
❌ Complex relationships - Start with foreign keys, add complexity later
❌ Perfect naming - You can rename later

The JSONB Escape Hatch:

-- Can't decide on columns? Use JSONB
ALTER TABLE items ADD COLUMN data JSONB DEFAULT '{}';

-- Now you can add any field without migrations
UPDATE items SET data = data || '{"color": "blue", "size": "large"}';

-- Query it like normal columns
SELECT * FROM items WHERE data->>'color' = 'blue';

Real Developer Wisdom:
"Spent 2 weeks designing perfect schema. Rewrote it day 2 of production anyway." - Tom K.

"Ship with 5 tables. We're at 500K users, still have 12 tables." - Maria S.

Your Schema Challenge:
Draw your schema on paper. Take a photo. Share it.

No SQL yet. Just boxes and arrows. 5 minutes max.

Tomorrow: Deployment options that won't break the bank.

#PostgreSQL #SchemaDesign #Database #MVP

@postgres