Execute Supabase primary workflow: Schema from Requirements.
Use when Starting a new project with defined data requirements,
Refactoring an existing schema based on new features, or Creating migrations from specification documents.
Trigger with phrases like "supabase schema from requirements",
"generate database schema with supabase".
Translate business requirements into a production-ready Postgres schema inside Supabase. This skill covers the full path from specification document to applied migration: entity extraction, table creation with proper data types and constraints, Row Level Security policies, performance indexes, timestamp triggers, and TypeScript type generation. It is the highest-leverage activity in the early stages of any Supabase project because every downstream feature (auth, storage, realtime, edge functions) depends on well-designed tables.
@supabase/supabase-js v2+ installed in the project
Business requirements, PRD, or specification document identifying entities and access rules
Local Supabase running (supabase start) or a linked remote project
Instructions
Step 1: Parse Requirements and Create Migration
Read the requirements document and extract entities, attributes, relationships, and access control rules. Map each entity to a Postgres table.
Entity extraction example (project management app):
Entity
Key Columns
Relationships
Organization
name, slug, plan
has many Projects, has many Members
Project
name, description, status
belongs to Organization, has many Tasks
Task
title, priority, status, due_date
belongs to Project, assigned to User
Member
role (owner/admin/member)
junction linking User to Organization
Create the migration file:
npx supabase migration new create_tables
# Creates: supabase/migrations/<timestamp>_create_tables.sql
Write the migration SQL using standard Postgres data types (uuid, text, integer, boolean, timestamptz, jsonb):
-- supabase/migrations/<timestamp>_create_tables.sql
-- Enable required extensions
create extension if not exists "uuid-ossp";
create extension if not exists "moddatetime";
-- Organizations
create table public.organizations (
id uuid default uuid_generate_v4() primary key,
name text not null,
slug text unique not null,
plan text default 'free' check (plan in ('free', 'pro', 'enterprise')),
metadata jsonb default '{}'::jsonb,
created_at timestamptz default now() not null,
updated_at timestamptz default now() not null
);
-- Organization members (junction table)
create table public.members (
id uuid default uuid_generate_v4() primary key,
organization_id uuid references public.organizations(id) on delete cascade not null,
user_id uuid references auth.users(id) on delete cascade not null,
role text default 'member' check (role in ('owner', 'admin', 'member')),
created_at timestamptz default now() not null,
unique (organization_id, user_id)
);
-- Projects
create table public.projects (
id uuid default uuid_generate_v4() primary key,
organization_id uuid references public.organizations(id) on delete cascade not null,
name text not null,
description text,
status text default 'active' check (status in ('active', 'archived', 'deleted')),
settings jsonb default '{}'::jsonb,
created_at timestamptz default now() not null,
updated_at timestamptz default now() not null
);
-- Tasks
create table public.tasks (
id uuid default uuid_generate_v4() primary key,
project_id uuid references public.projects(id) on delete cascade not null,
assigned_to uuid references auth.users(id) on delete set null,
title text not null,
description text,
priority integer default 0 check (priority between 0 and 4),
status text default 'todo' check (status in ('todo', 'in_progress', 'done', 'cancelled')),
due_date date,
tags text[] default '{}',
created_at timestamptz default now() not null,
updated_at timestamptz default now() not null
);
-- Indexes for common query patterns
create index idx_members_user on public.members(user_id);
create index idx_members_org on public.members(organization_id);
create index idx_projects_org on public.projects(organization_id);
create index idx_tasks_project on public.tasks(project_id);
create index idx_tasks_assigned on public.tasks(assigned_to);
create index idx_tasks_status on public.tasks(status) where status not in ('done', 'cancelled');
create index idx_tasks_due on public.tasks(due_date) where due_date is not null;
create index idx_orgs_slug on public.organizations(slug);
-- Automatic updated_at triggers via moddatetime extension
create trigger handle_updated_at before update on public.organizations
for each row execute procedure moddatetime(updated_at);
create trigger handle_updated_at before update on public.projects
for each row execute procedure moddatetime(updated_at);
create trigger handle_updated_at before update on public.tasks
for each row execute procedure moddatetime(updated_at);
Data type selection guide:
Use case
Type
Notes
Primary keys
uuid
Always with uuid_generate_v4() default
Names, titles
text
Prefer over varchar in Postgres
Counts, ranks
integer
Use bigint for sequences
Flags
boolean
Default explicitly to true or false
Timestamps
timestamptz
Never use timestamp without timezone
Flexible data
jsonb
Queryable JSON; use for settings, metadata
Lists
text[]
Postgres arrays for simple tags or labels
Step 2: Add Row Level Security Policies
Every table exposed to the client must have RLS enabled. Write helper functions first to avoid repeating authorization logic across policies.
-- Helper: check if user is a member of an organization
create or replace function public.is_org_member(org_id uuid)
returns boolean as $$
select exists (
select 1 from public.members
where organization_id = org_id
and user_id = auth.uid()
);
$$ language sql security definer stable;
-- Helper: check if user is org admin or owner
create or replace function public.is_org_admin(org_id uuid)
returns boolean as $$
select exists (
select 1 from public.members
where organization_id = org_id
and user_id = auth.uid()
and role in ('owner', 'admin')
);
$$ language sql security definer stable;
-- Organizations RLS
alter table public.organizations enable row level security;
create policy "Users read own orgs"
on public.organizations for select
using (public.is_org_member(id));
create policy "Authenticated users create orgs"
on public.organizations for insert
with check (auth.uid() is not null);
create policy "Admins update orgs"
on public.organizations for update
using (public.is_org_admin(id));
create policy "Owners delete orgs"
on public.organizations for delete
using (
exists (
select 1 from public.members
where organization_id = id
and user_id = auth.uid()
and role = 'owner'
)
);
-- Members RLS
alter table public.members enable row level security;
create policy "Members view org roster"
on public.members for select
using (public.is_org_member(organization_id));
create policy "Admins manage members"
on public.members for all
using (public.is_org_admin(organization_id));
-- Projects RLS
alter table public.projects enable row level security;
create policy "Members view projects"
on public.projects for select
using (public.is_org_member(organization_id));
create policy "Admins manage projects"
on public.projects for all
using (public.is_org_admin(organization_id));
-- Tasks RLS
alter table public.tasks enable row level security;
create policy "Members view tasks"
on public.tasks for select
using (
exists (
select 1 from public.projects p
where p.id = project_id
and public.is_org_member(p.organization_id)
)
);
create policy "Members create tasks"
on public.tasks for insert
with check (
exists (
select 1 from public.projects p
where p.id = project_id
and public.is_org_member(p.organization_id)
)
);
create policy "Assignee or admin updates tasks"
on public.tasks for update
using (
assigned_to = auth.uid()
or exists (
select 1 from public.projects p
where p.id = project_id
and public.is_org_admin(p.organization_id)
)
);
RLS policy naming convention: Use short, descriptive names that state who and what action: "Users read own", "Admins manage members", "Assignee updates tasks".
Step 3: Apply Migration and Generate Types
# Apply migration locally
npx supabase db reset
# Or push to a linked remote project
npx supabase db push
# Generate TypeScript types from the live schema
npx supabase gen types typescript --local > types/supabase.ts
// This should return only rows the authenticated user can see
const { data, error } = await supabase.from('organizations').select('*')
if (error) {
console.error('RLS check failed:', error.message)
}
console.log(`User can see ${data?.length ?? 0} organizations`)
Output
SQL migration file under supabase/migrations/ with all tables, constraints, and indexes
RLS policies matching the access control rules from requirements
Helper functions (is_org_member, is_org_admin) for reusable authorization checks
moddatetime triggers for automatic updated_at management
Generated TypeScript types at types/supabase.ts for type-safe client queries
Partial indexes on frequently filtered columns (status, due_date)
Error Handling
Error
Cause
Solution
42P07: relation already exists
Table name collision in migration
Use create table if not exists or rename the table
23503: foreign key violation
Insert references a nonexistent parent row
Insert parent rows first, or check the UUID
42501: insufficient privilege
RLS helper function permissions
Add security definer to the function definition
42883: function uuid_generate_v4() does not exist
Extension not enabled
Add create extension if not exists "uuid-ossp"
supabase db push succeeds but tables missing
Migration was already recorded
Check supabase_migrations.schema_migrations and repair
PGRST204: column not found
TypeScript types out of sync
Re-run npx supabase gen types typescript --local > types/supabase.ts
new row violates row-level security
RLS policy blocks the operation
Verify auth.uid() matches expected value; check policy using clause
moddatetime trigger fails
Extension not enabled
Add create extension if not exists "moddatetime" at top of migration
Examples
E-commerce schema (different domain, same pattern):
create table public.products (
id uuid default uuid_generate_v4() primary key,
store_id uuid references public.stores(id) on delete cascade not null,
name text not null,
price integer not null check (price >= 0), -- cents
currency text default 'usd',
inventory integer default 0 check (inventory >= 0),
metadata jsonb default '{}'::jsonb,
is_active boolean default true,
created_at timestamptz default now() not null,
updated_at timestamptz default now() not null
);
alter table public.products enable row level security;
create policy "Anyone reads active products"
on public.products for select
using (is_active = true);
create policy "Store owners manage products"
on public.products for all
using (
exists (
select 1 from public.stores s
where s.id = store_id and s.owner_id = auth.uid()
)
);
create trigger handle_updated_at before update on public.products
for each row execute procedure moddatetime(updated_at);
Querying the e-commerce schema from the client:
// Products with store info
const { data: products } = await supabase
.from('products')
.select('*, store:store_id(name, slug)')
.eq('is_active', true)
.gte('inventory', 1)
.order('price', { ascending: true })
.limit(20)
