jeremylongshore 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-jsv2+ 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 moddatetimetriggers for automaticupdated_atmanagement- Generated TypeScript types at
types/supabase.tsfor 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
- 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.
Source: jeremylongshore/claude-code-plugins-plus-skills → skills/.curated/supabase-schema-from-requirements/SKILL.md
Also appears in: jeremylongshore/claude-code-plugins-plus-skills/plugins/saas-packs/supabase-pack/skills/supabase-schema-from-requirements/SKILL.md