kazdenc Database Schema Design
Design a Postgres database schema from product requirements. If target is provided, scope to that feature. Otherwise, ask the user what they're building.
Step 1: Identify Entities and Relationships
Read the requirements (PRD, user stories, or conversation). Extract:
- Nouns = candidate tables (users, projects, comments, invoices)
- Verbs = candidate relationships (creates, belongs to, has many)
- Adjectives/states = candidate columns or enums (active, draft, published)
Ask clarifying questions if the requirements are ambiguous. Don't guess at business logic.
Step 2: Design Tables
Follow these conventions on every table:
| Convention | Rule | Example |
|---|---|---|
| Table names | snake_case, plural |
user_profiles, org_members |
| Column names | snake_case |
first_name, created_at |
| Primary keys | uuid, auto-generated |
id uuid default gen_random_uuid() primary key |
| Timestamps | On every table, non-nullable | created_at timestamptz default now() not null |
| Updated timestamp | On every table | updated_at timestamptz default now() not null |
| Foreign keys | Named explicitly, with on delete |
user_id uuid references users(id) on delete cascade |
| Soft deletes | Use deleted_at timestamptz when needed |
Only when business requires undo/recovery |
| Booleans | Prefix with is_ or has_ |
is_active, has_verified_email |
Postgres Type Recommendations
| Data | Use | Don't use | Why |
|---|---|---|---|
| Identifiers | uuid |
serial, integer |
Non-sequential, safe to expose, no collision across tables |
| Timestamps | timestamptz |
timestamp |
Always store timezone-aware. Convert on display. |
| Money | numeric(12,2) or bigint (cents) |
float, real |
Floating point causes rounding errors |
text with check constraint |
varchar(255) |
text is faster in Postgres. Constrain with check. |
|
| Short strings | text |
varchar(n) |
Postgres text has no performance penalty. Use check(length(col) <= n) if needed. |
| Status/enum | text with check, or Postgres enum |
Magic integers | check (status in ('draft','published','archived')) |
| JSON data | jsonb |
json |
jsonb is indexable and more efficient |
| IP addresses | inet |
text |
Native type supports operations |
| Tags/arrays | text[] or junction table |
CSV in text column | Arrays for simple cases, junction table for queryable tags |
Step 3: Define Relationships
One-to-Many
Put the foreign key on the "many" side:
create table posts (
id uuid default gen_random_uuid() primary key,
user_id uuid references users(id) on delete cascade not null,
title text not null,
created_at timestamptz default now() not null,
updated_at timestamptz default now() not null
);
One-to-One
Same as one-to-many but add a unique constraint:
create table user_profiles (
id uuid default gen_random_uuid() primary key,
user_id uuid references users(id) on delete cascade not null unique,
bio text,
avatar_url text,
created_at timestamptz default now() not null,
updated_at timestamptz default now() not null
);
Many-to-Many
Use a junction table. Name it <table_a>_<table_b> in alphabetical order, or use a domain name if clearer:
create table project_members (
project_id uuid references projects(id) on delete cascade not null,
user_id uuid references users(id) on delete cascade not null,
role text not null default 'member' check (role in ('owner','admin','member','viewer')),
joined_at timestamptz default now() not null,
primary key (project_id, user_id)
);
Step 4: Add Indexes
Create indexes for:
| Index when | Example |
|---|---|
| Foreign keys (always) | create index idx_posts_user_id on posts(user_id); |
Columns in WHERE clauses |
create index idx_posts_status on posts(status); |
Columns in ORDER BY |
create index idx_posts_created_at on posts(created_at desc); |
| Unique lookups | create unique index idx_users_email on users(email); |
| Composite queries | create index idx_posts_user_status on posts(user_id, status); |
| Full-text search | create index idx_posts_title_search on posts using gin(to_tsvector('english', title)); |
Don't over-index. Each index costs write performance. Start with foreign keys and obvious query patterns. Add more based on actual query plans.
Step 5: Write the Migration
Put everything in a Supabase migration:
npx supabase migration new create_initial_schema
Structure the migration file:
-- 1. Create enums (if using Postgres enums)
-- 2. Create tables (parent tables first, then children)
-- 3. Create indexes
-- 4. Enable RLS on all tables
-- 5. Create RLS policies
-- 6. Create functions and triggers (e.g., updated_at trigger)
-- Updated_at trigger function (create once, reuse)
create or replace function update_updated_at()
returns trigger as $$
begin
new.updated_at = now();
return new;
end;
$$ language plpgsql;
-- Example table
create table users (
id uuid default gen_random_uuid() primary key,
email text not null unique,
full_name text,
avatar_url text,
created_at timestamptz default now() not null,
updated_at timestamptz default now() not null
);
-- Apply updated_at trigger
create trigger set_updated_at
before update on users
for each row execute function update_updated_at();
-- Enable RLS
alter table users enable row level security;
-- Indexes
create index idx_users_email on users(email);
Step 6: Supabase-Specific Guidance
| Concern | Approach |
|---|---|
| Auth users | Supabase manages auth.users. Reference with auth.uid(). Create a public.users or public.profiles table that mirrors/extends it. |
| Auto-create profile | Use a trigger on auth.users insert to create a row in public.profiles |
| RLS | Enable on every table. Write policies immediately. See supabase-setup skill for patterns. |
| Realtime | Add tables to Supabase Realtime publication if live updates are needed: alter publication supabase_realtime add table <table>; |
| Type generation | Run npx supabase gen types typescript --local after every migration |
Auto-create profile trigger
create or replace function public.handle_new_user()
returns trigger as $$
begin
insert into public.profiles (user_id, email, full_name, avatar_url)
values (
new.id,
new.email,
new.raw_user_meta_data->>'full_name',
new.raw_user_meta_data->>'avatar_url'
);
return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
after insert on auth.users
for each row execute function public.handle_new_user();
Schema Review Checklist
| Check | Pass? |
|---|---|
Every table has id uuid primary key |
|
Every table has created_at and updated_at |
|
All foreign keys have on delete behavior defined |
|
| All foreign keys have indexes | |
| RLS is enabled on every table | |
No varchar without justification (use text) |
|
No timestamp without tz (use timestamptz) |
|
| No floating point for money | |
| Junction tables have composite primary keys | |
updated_at trigger is applied to all tables |
|
Migration runs cleanly on supabase db reset |
|
| Types regenerated after migration |