Supabase Local Dev Workflow

Core Philosophy

1. Schema-driven development — all structural changes go to schema files, never direct SQL
2. RPC-first architecture — no direct

   supabase-js

   table calls; all data access through RPCs
3. DB functions as first-class citizens — business logic lives in the database

Process

Phase 1: Schema Changes

supabase/schemas/
├── 10_types/        # Enums, composite types, domains
├── 20_tables/       # Table definitions
├── 30_constraints/  # Check constraints, foreign keys
├── 40_indexes/      # Index definitions
├── 50_functions/    # RPCs, auth functions, internal utils
│   ├── _internal/   # Infrastructure utilities
│   └── _auth/       # RLS policy functions
├── 60_triggers/     # Trigger definitions
├── 70_policies/     # RLS policies
└── 80_views/        # View definitions

charts.sql

readings.sql

Phase 2: Apply & Fix

1. CLI auto-applies changes (

   supabase start

   )
2. Monitor logs for errors (constraint violations, dependencies)
3. If errors → use

   execute_sql

   MCP tool for data fixes only (UPDATE, DELETE, INSERT)
4. Never use

   execute_sql

   for schema structure — only schema files

Phase 3: Generate Types

supabase gen types typescript --local > src/types/database.ts

Phase 4: Iterate

Phase 5: Migration

1. Use

   supabase db diff

   to generate migration
2. Review migration — patch if manual SQL commands are missing

Reference Files

Conventions & Patterns

• 📋 Naming Conventions — Tables, columns, functions, indexes
• 🔐 RPC Patterns — RPC-first architecture, auth functions, RLS policies
• ⚡ Edge Functions — Project structure, shared utilities, CORS, error helpers
• 🔧 withSupabase Wrapper — Wrapper rules, role selection, client usage patterns

Setup & Infrastructure

• ⚙️ Setup Guide — Vault secrets, internal utility functions

Workflows

• 📝 Common Workflows — Adding entities, fields, creating RPCs

Entity Tracking

• 📊 Entity Registry Template — Track entities and schema files

Tools & Dependencies

execute_sql

Quick Reference

// ❌ WRONG
const { data } = await supabase.from("charts").select("*");

// ✅ CORRECT
const { data } = await supabase.rpc("chart_get_by_user", { p_user_id: userId });

-- ❌ WRONG — bypasses RLS then reimplements filtering manually
CREATE FUNCTION chart_get_by_id(p_chart_id uuid)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
  SELECT ... FROM public.charts WHERE id = p_chart_id AND user_id = auth.uid(); -- manual filter = fragile
END; $$;

-- ✅ CORRECT — RLS handles access control automatically
CREATE FUNCTION chart_get_by_id(p_chart_id uuid)
RETURNS jsonb LANGUAGE plpgsql SECURITY INVOKER SET search_path = '' AS $$
BEGIN
  SELECT ... FROM public.charts WHERE id = p_chart_id; -- RLS enforces permissions
END; $$;

• _auth_*

  functions called by RLS policies (they run during policy evaluation, need to bypass RLS to query the table they protect)

• _internal_*

  utility functions that need elevated access (e.g., reading vault secrets)
• Multi-table operations that need cross-table access the user's role can't reach
• Always document WHY with a comment:

  -- SECURITY DEFINER: required because ...

• Business logic:

  {entity}_{action}

  →

  chart_create

  (SECURITY INVOKER)
• Auth (RLS):

  _auth_{entity}_{check}

  →

  _auth_chart_can_read

  (SECURITY DEFINER — needed by RLS)
• Internal:

  _internal_{name}

  →

  _internal_get_secret

  (SECURITY DEFINER — elevated access)