supabase-schema-from-requirements

Supabase Schema from Requirements

Overview

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.

Prerequisites

• Supabase CLI installed (npm install -g supabase) and project linked (supabase link)
• @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

Use the generated types with the Supabase client:

import { createClient } from '@supabase/supabase-js'
import type { Database } from './types/supabase'

const supabase = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// Typed insert
const { data: org, error } = await supabase
  .from('organizations')
  .insert({ name: 'Acme Corp', slug: 'acme', plan: 'pro' })
  .select()
  .single()

// Typed select with foreign key join
const { data: tasks } = await supabase
  .from('tasks')
  .select('*, project:project_id(name, organization_id)')
  .eq('status', 'todo')
  .order('due_date', { ascending: true })

// Nested join across multiple tables
const { data: orgWithProjects } = await supabase
  .from('organizations')
  .select(`
    id, name, slug,
    projects:projects(
      id, name, status,
      tasks:tasks(id, title, status, assigned_to)
    )
  `)
  .eq('slug', 'acme')
  .single()

Verify RLS is working:

// 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)

Resources

• Row Level Security Guide
• Database Migrations
• Supabase CLI Reference
• JavaScript Client — Select
• TypeScript Support
• PostgreSQL Data Types

Next Steps

For auth integration, file storage, and realtime subscriptions on top of this schema, proceed to supabase-auth-storage-realtime-core.