Supabase Database

This skill teaches agents how to work with Supabase database operations including migrations, RLS policies, database functions, and SQL queries following official best practices.

When to Use

• •When creating or modifying database schema
• •When writing database migrations
• •When implementing Row Level Security (RLS) policies
• •When creating PostgreSQL functions or triggers
• •When writing SQL queries for Supabase
• •When working with database seeds or test data

Core Principles

Migration-Based Schema Changes

All schema changes via migrations:

• •Create migrations: supabase db diff -f migration_name
• •Apply migrations: supabase db push
• •Never modify schema directly in Supabase Dashboard
• •All migrations versioned and tracked in supabase/migrations/

Row Level Security (RLS)

Always enable RLS on new tables:

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Write granular policies:

• •Separate policies for SELECT, INSERT, UPDATE, DELETE
• •Separate policies for authenticated vs anon roles
• •Test policies thoroughly before deploying

Type Safety

Use auto-generated types:

• •Generate types: supabase gen types typescript --local > src/integrations/supabase/types.ts
• •Use generated types: Tables<'startups'>, TablesInsert<'contacts'>
• •Never define types manually — always generate from schema

Migration Workflow

Creating Migrations

1. Make schema changes in local database:

supabase start
# Make changes via Supabase Studio or SQL editor

2. Generate migration from diff:

supabase db diff -f migration_name

3. Review generated migration:

-- supabase/migrations/YYYYMMDDHHmmss_migration_name.sql
-- Review SQL before applying

4. Apply migration:

# Local
supabase db reset

# Remote
supabase db push

Migration Naming Convention

Format: YYYYMMDDHHmmss_short_description.sql

Examples:

• •20260115120001_extensions_and_helpers.sql
• •20260115120002_core_tables.sql
• •20260115120003_crm_tables.sql

Migration Structure

Template:

-- Migration: description
-- Created: YYYY-MM-DD

-- Step 1: Create extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Step 2: Create types
CREATE TYPE status_type AS ENUM ('active', 'inactive');

-- Step 3: Create tables
CREATE TABLE startups (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  org_id UUID NOT NULL REFERENCES organizations(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Step 4: Enable RLS
ALTER TABLE startups ENABLE ROW LEVEL SECURITY;

-- Step 5: Create policies
CREATE POLICY "Users can read own org's startups"
  ON startups FOR SELECT
  USING (org_id = user_org_id());

-- Step 6: Create indexes
CREATE INDEX idx_startups_org_id ON startups(org_id);

-- Step 7: Create triggers (if needed)
CREATE TRIGGER update_updated_at
  BEFORE UPDATE ON startups
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

RLS Policies

Policy Structure

Granular Policies:

-- Separate policy for each operation
CREATE POLICY "policy_name_select" ON table_name
  FOR SELECT USING (condition);

CREATE POLICY "policy_name_insert" ON table_name
  FOR INSERT WITH CHECK (condition);

CREATE POLICY "policy_name_update" ON table_name
  FOR UPDATE USING (condition) WITH CHECK (condition);

CREATE POLICY "policy_name_delete" ON table_name
  FOR DELETE USING (condition);

Helper Functions

Common helpers in this project:

• •user_org_id() — Current user's org_id
• •org_role() — User's role in org
• •startup_in_org(id) — Check startup belongs to user's org

Example:

CREATE POLICY "Users can read own org's startups"
  ON startups FOR SELECT
  USING (org_id = user_org_id());

Policy Patterns

Org-scoped tables:

-- Users can only access their own org's data
USING (org_id = user_org_id())

Startup-scoped tables:

-- Users can access if startup belongs to their org
USING (startup_id IN (
  SELECT id FROM startups WHERE org_id = user_org_id()
))

User-scoped tables:

-- Users can only access their own data
USING (user_id = auth.uid())

Database Functions

Function Template

CREATE OR REPLACE FUNCTION function_name(param1 TYPE)
RETURNS RETURN_TYPE
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Function logic
  RETURN result;
END;
$$;

Common Helper Functions

Update updated_at trigger:

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

User org helper:

CREATE OR REPLACE FUNCTION user_org_id()
RETURNS UUID AS $$
  SELECT org_id FROM organization_members WHERE user_id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER;

Type Generation

Generate TypeScript Types

Command:

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

Or from remote:

supabase gen types typescript --project-id your-project-ref > src/integrations/supabase/types.ts

Using Generated Types

In TypeScript code:

import { Tables, TablesInsert, TablesUpdate } from '@/integrations/supabase/types';

type Startup = Tables<'startups'>;
type NewStartup = TablesInsert<'startups'>;
type UpdateStartup = TablesUpdate<'startups'>;

Never define types manually — always generate from schema.

SQL Query Patterns

Supabase Client Queries

Select with filters:

const { data, error } = await supabase
  .from('startups')
  .select('*')
  .eq('org_id', orgId)
  .order('created_at', { ascending: false });

Insert with RLS:

const { data, error } = await supabase
  .from('startups')
  .insert({ name: 'Startup Name', org_id: orgId })
  .select()
  .single();

Update with RLS:

const { data, error } = await supabase
  .from('startups')
  .update({ name: 'New Name' })
  .eq('id', startupId)
  .select()
  .single();

Best Practices

✅ DO

• •Create migrations for all schema changes
• •Enable RLS on all tables
• •Write granular policies (separate SELECT/INSERT/UPDATE/DELETE)
• •Test policies before deploying
• •Generate types after schema changes
• •Use helper functions for common patterns
• •Add indexes for frequently queried columns
• •Use proper foreign key constraints

❌ DON'T

• •Don't modify schema directly in Dashboard (use migrations)
• •Don't disable RLS (security risk)
• •Don't create overly permissive policies
• •Don't define types manually (generate from schema)
• •Don't skip policy testing
• •Don't forget to add indexes for foreign keys
• •Don't use SELECT * in production (specify columns)

Reference

• •Rules: .cursor/rules/supabase/supabase-*.mdc (multiple files)
• •CLI Rules: .cursor/rules/supabase/supabase-cli.mdc
• •Schema Rules: .cursor/rules/supabase/supabase-schema.mdc
• •Migration Rules: .cursor/rules/supabase/supabase-create-migration.mdc
• •RLS Rules: .cursor/rules/supabase/supabase-create-rls-policies.mdc
• •Function Rules: .cursor/rules/supabase/supabase-create-db-functions.mdc
• •Supabase Docs: PostgreSQL

Created: 2025-01-16
Based on: Supabase database best practices
Version: 1.0.0