The Schema You Write on Day 1 Is the Schema You Live With
AI tools can refactor your React components in minutes. They can rewrite your API routes cleanly. They can update your CSS system without breaking anything.
They cannot easily fix a bad database schema once your database has real data in it. Schema migrations on live databases with production data are among the most stressful engineering tasks that exist.
Get the schema right before you have users.
---
The Core User Profile Pattern
Almost every SaaS application needs a user profile table. Here is the production-ready pattern for Supabase/PostgreSQL:
sql
-- Users table (managed by Supabase Auth automatically)
-- auth.users is created for you — you reference it via foreign key
-- Public profile data
CREATE TABLE public.profiles (
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
email TEXT NOT NULL,
full_name TEXT,
avatar_url TEXT,
stripe_customer_id TEXT UNIQUE, -- Linked to Stripe for payments
subscription_tier TEXT DEFAULT 'free' -- 'free' | 'pro' | 'enterprise'
CHECK (subscription_tier IN ('free', 'pro', 'enterprise')),
credits INTEGER NOT NULL DEFAULT 5 -- For credit-based features
CHECK (credits >= 0),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Auto-create profile when user signs up
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $
BEGIN
INSERT INTO public.profiles (id, email)
VALUES (NEW.id, NEW.email);
RETURN NEW;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION handle_new_user();
-- RLS: users can only see and edit their own profile
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own profile" ON public.profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own profile" ON public.profiles FOR UPDATE USING (auth.uid() = id);
---
The Content/Posts Pattern
For any app where users create content (blog posts, projects, items, listings):
sql
CREATE TABLE public.posts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
title TEXT NOT NULL CHECK (length(title) BETWEEN 3 AND 200),
slug TEXT UNIQUE NOT NULL,
content TEXT,
status TEXT NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'published', 'archived')),
metadata JSONB DEFAULT '{}'::jsonb, -- Flexible extra fields without schema changes
view_count INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
published_at TIMESTAMPTZ -- NULL until published
);
-- Indexes for common query patterns
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status) WHERE status = 'published';
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
-- RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Published posts are public" ON posts FOR SELECT
USING (status = 'published');
CREATE POLICY "Users manage own posts" ON posts FOR ALL
USING (auth.uid() = user_id);
---
The Transactions/Credits Pattern
For any credit-based system, never just update a counter field. Maintain a transaction log:
sql
-- Never just UPDATE profiles SET credits = credits - 1
-- Always record the transaction
CREATE TABLE public.credit_transactions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
amount INTEGER NOT NULL, -- Positive = credit added, Negative = credit spent
reason TEXT NOT NULL, -- 'purchase', 'generation', 'refund', 'signup_bonus'
reference_id TEXT, -- Stripe session ID, generation ID, etc.
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Idempotent credit addition (safe to call multiple times with same reference_id)
CREATE OR REPLACE FUNCTION add_credits(
p_user_id UUID,
p_amount INTEGER,
p_reason TEXT,
p_reference_id TEXT
)
RETURNS VOID AS $
BEGIN
INSERT INTO credit_transactions (user_id, amount, reason, reference_id)
VALUES (p_user_id, p_amount, p_reason, p_reference_id)
ON CONFLICT (reference_id) DO NOTHING; -- Prevents double-crediting
UPDATE profiles
SET credits = credits + p_amount
WHERE id = p_user_id;
END;
$ LANGUAGE plpgsql;
---
The Decisions That Prevent Painful Migrations
Always include created_at and updated_at. You will want to sort and filter by these. Adding them later to a large table requires a migration and backfill.
Always use UUIDs for primary keys, not serial integers. UUIDs are safe to generate client-side (no round trip to DB), do not reveal your total record count, and work across distributed systems.
Use TEXT instead of VARCHAR(n). In PostgreSQL, there is no performance difference. You will inevitably need to increase a VARCHAR limit — with TEXT you never need to.
Store metadata JSONB on every content table. When you need to add new optional fields to a table, you can do it without a schema migration by putting them in metadata. Saves enormous time during rapid iteration.
Design RLS policies from day one. Adding Row Level Security to a table with existing data requires auditing every query. Design it in from the start.