<objective> Clean and format SQL migrations for direct paste into Supabase SQL Editor. Ensures idempotency, proper RLS policies (especially service role patterns), standardized formatting, and dependency documentation. </objective>

<quick_start> Clean any SQL migration:

•Fix typos (- → -- for comments)
•Add idempotency (IF NOT EXISTS, DROP ... IF EXISTS)
•Fix RLS policies (service role uses TO service_role, not JWT checks)
•Remove dead code (unused enums)
•Standardize casing (NOW(), TIMESTAMPTZ)
•Add dependencies comment at end

sql

DROP POLICY IF EXISTS "Policy name" ON table_name;
CREATE POLICY "Policy name" ON table_name ...

</quick_start>

<success_criteria> SQL cleanup is successful when:

•All policies and triggers use DROP ... IF EXISTS before CREATE
•Service role policies use TO service_role (not JWT checks)
•Indexes use IF NOT EXISTS
•No unused enums remain
•Dependencies listed at end of migration
•SQL runs without errors in Supabase SQL Editor </success_criteria>

<core_patterns> Clean SQL migrations for direct paste into Supabase SQL Editor.

Cleanup Checklist

Run through each item:

•Fix typos - Common: - instead of -- on comment lines
•Add idempotency - IF NOT EXISTS on indexes, DROP ... IF EXISTS before policies/triggers
•Remove dead code - Enums created but never used (TEXT + CHECK often preferred)
•Fix RLS policies - Service role must use TO service_role, not JWT checks
•Standardize casing - NOW() not now(), TIMESTAMPTZ not timestamptz
•Remove clutter - Verbose RAISE NOTICE blocks, redundant comments, file path headers
•Validate dependencies - List required tables at end

Output Format

sql

-- ============================================
-- Migration Name
-- Created: YYYY-MM-DD
-- Purpose: One-line description
-- ============================================

-- ============================================
-- Table Name
-- ============================================

CREATE TABLE IF NOT EXISTS ...

-- ============================================
-- Indexes
-- ============================================

CREATE INDEX IF NOT EXISTS ...

-- ============================================
-- Row Level Security
-- ============================================

ALTER TABLE ... ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "..." ON ...;
CREATE POLICY "..." ON ...

-- ============================================
-- Functions
-- ============================================

CREATE OR REPLACE FUNCTION ...

-- ============================================
-- Triggers
-- ============================================

DROP TRIGGER IF EXISTS ... ON ...;
CREATE TRIGGER ...

Common Fixes

RLS Policy for Service Role

sql

-- WRONG (doesn't work reliably)
CREATE POLICY "Service role access" ON my_table
    FOR ALL
    USING (auth.jwt() ->> 'role' = 'service_role');

-- CORRECT
CREATE POLICY "Service role access" ON my_table
    FOR ALL
    TO service_role
    USING (true)
    WITH CHECK (true);

Idempotent Policies

sql

-- Always drop before create
DROP POLICY IF EXISTS "Policy name" ON table_name;
CREATE POLICY "Policy name" ON table_name ...

Idempotent Triggers

sql

DROP TRIGGER IF EXISTS trigger_name ON table_name;
CREATE TRIGGER trigger_name ...

Unused Enums

If you see enum created but table uses TEXT CHECK (...) instead, remove the enum:

sql

-- DELETE THIS - never used
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_enum') THEN
        CREATE TYPE my_enum AS ENUM ('a', 'b', 'c');
    END IF;
END$$;

-- Table actually uses TEXT with CHECK (keep this)
status TEXT NOT NULL CHECK (status IN ('a', 'b', 'c'))

Dependencies Section

Always end with dependencies note if tables are referenced:

sql

-- Dependencies: businesses, call_logs, subscription_plans
-- Requires function: update_updated_at_column()

Reference Files

•reference/rls-patterns.md - Common RLS policy patterns for Supabase
•reference/function-patterns.md - Trigger functions, atomic operations