Supabase Schema

Supabase Schema Skill

Natural language to production-ready Supabase migration pipeline.

When to Trigger

Activate this skill when the user:

• Asks to design a database schema or data model
• Wants to create tables or set up Supabase
• Requests a migration file or SQL generation
• Describes entities and relationships in plain English

Input

A natural language description of the data model. Examples:

• "I need a users table with auth, a posts table with comments, and a media uploads table"
• "Set up a multi-tenant SaaS schema with organizations, members, and billing"
• "Create a simple blog with categories, tags, and full-text search"

Output Pipeline

Step 1: Parse Requirements into Entity-Relationship Model

Read the user's description and extract:

• Entities (tables) with their attributes
• Relationships (one-to-one, one-to-many, many-to-many)
• Constraints (unique, not null, check)
• Access patterns (who reads/writes what)

Present a summary table before generating SQL:

Step 2: Generate CREATE TABLE Statements

Generate SQL with appropriate Postgres types:

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE public.users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL,
  full_name text,
  avatar_url text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

Type selection rules:

• Primary keys: uuid with gen_random_uuid() default — never serial/bigserial
• Timestamps: timestamptz — never timestamp without timezone
• Free text: text — never varchar unless a hard length limit is required
• Structured data: jsonb — never json
• Enums: prefer CHECK constraints over Postgres ENUM types (easier to migrate)
• Money: numeric(12,2) or bigint (cents) — never float/double
• Status fields: text with CHECK (status IN ('draft', 'published', 'archived'))

Step 3: Generate RLS Policies

Enable RLS on every table and generate default policies:

ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- Users can read all published posts
CREATE POLICY "Public posts are viewable by everyone"
  ON public.posts FOR SELECT
  USING (status = 'published');

-- Users can CRUD their own posts
CREATE POLICY "Users can manage their own posts"
  ON public.posts FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Default RLS rules:

• Users can only read/write their own rows unless explicitly public
• Service role bypasses RLS (for server-side operations)
• Anonymous access is denied by default
• Admin roles get separate, broader policies

Step 4: Generate Indexes

-- Foreign key indexes (Postgres does NOT auto-index FK columns)
CREATE INDEX idx_posts_user_id ON public.posts (user_id);
CREATE INDEX idx_comments_post_id ON public.comments (post_id);

-- Common query pattern indexes
CREATE INDEX idx_posts_status ON public.posts (status) WHERE status = 'published';
CREATE INDEX idx_posts_created_at ON public.posts (created_at DESC);

Index rules:

• Always index foreign key columns
• Add partial indexes for status filters
• Add DESC indexes for timestamp ordering
• Consider GIN indexes for jsonb and full-text search columns
• Do not over-index — each index slows writes

Step 5: Generate Updated-At Trigger

CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

Step 6: Generate TypeScript Types

Use Supabase MCP to generate types:

• Tool: generate_typescript_types with the project ID
• Output the types to src/types/database.ts

Step 7: Optionally Apply Migration

Safety protocol — ALWAYS follow this sequence:

1. Show the complete SQL to the user
2. Ask for explicit confirmation before applying
3. Use Supabase MCP apply_migration with a descriptive name
4. Verify by running list_tables after application

Never auto-apply without confirmation. This is a hard rule.

Best Practices Checklist

Before presenting the migration, verify:

• Every table has id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• Every table has created_at timestamptz NOT NULL DEFAULT now()
• Every table has updated_at timestamptz NOT NULL DEFAULT now()
• RLS is enabled on every table
• At least one RLS policy exists per table
• All foreign keys have corresponding indexes
• ON DELETE behavior is specified (CASCADE or SET NULL)
• Status/enum columns use CHECK constraints
• The handle_updated_at trigger is applied to all tables
• No hardcoded user IDs or secrets in the SQL

Example Interaction

User: "I need a schema for a task management app with projects, tasks, and team members"

Agent output:

1. Entity-relationship summary table
2. Full SQL migration (extensions, tables, RLS, indexes, triggers)
3. Confirmation prompt: "Ready to apply this migration? (yes/no)"
4. On confirmation: apply via MCP and generate TypeScript types