Supabase Schema from Requirements

Overview

Translate business requirements into a production-ready Postgres schema inside Supabase, covering the full path from specification to applied migration: entity extraction, tables with proper types and constraints, Row Level Security policies, performance indexes, timestamp triggers, and TypeScript type generation. Getting this right early matters 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): Full worked migration (project-management app — tables, FKs, indexes, moddatetime triggers): references/worked-schema-examples.md.

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 reusable security definer helper functions first, then write per-table policies that call them. The skeleton for one table:

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

The full worked policy set — the is_org_member / is_org_admin helper functions and every select/insert/update/delete policy across organizations, members, projects, and tasks — is in references/rls-policies.md. Name policies after who and what action ("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

Pass the generated Database type as the generic parameter to createClient (see import type { Database } from './types/supabase') for end-to-end type safety on every query. Full typed-client walkthrough — typed inserts, foreign-key joins, nested multi-table joins, and an RLS-enforcement check — is in references/typescript-client.md.

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). The complete e-commerce migration SQL — stores, products, orders, order_items with FKs and indexes — is in references/worked-schema-examples.md, and the matching typed client queries are in references/typescript-client.md.

Resources

Next Steps

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

supabase-schema-from-requirements