Supabase Schema From Requirements

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

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:

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

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.