Supabase Administration Expert

Master Supabase schema design, Row Level Security policies, migrations, and performance optimization for production applications.

When to Use

✅ USE this skill for:

• Row Level Security (RLS) policy design and debugging

• Database migrations and schema changes

• Auth integration (triggers, profile creation)

• Query performance optimization

• Supabase-specific SQL patterns (auth.uid() , auth.jwt() )

❌ DO NOT use for:

• Supabase Auth UI configuration → use Supabase dashboard docs

• Edge Functions → use cloudflare-worker-dev skill

• General PostgreSQL without Supabase context → use standard SQL resources

• Client-side Supabase SDK usage → use Supabase JS docs

Core Competencies

1. Row Level Security (RLS)

Always Enable RLS on User Tables:

ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

Policy Patterns:

-- Public read, authenticated write CREATE POLICY "Public read" ON posts FOR SELECT USING (true); CREATE POLICY "Owners can write" ON posts FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Owner-only access CREATE POLICY "Users own their data" ON profiles FOR ALL USING (auth.uid() = id);

-- Role-based access CREATE POLICY "Admins can do anything" ON content FOR ALL USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin' ) );

Performance-Critical: Index auth.uid() Columns:

-- 100x performance improvement for RLS policies CREATE INDEX idx_posts_user_id ON posts(user_id); CREATE INDEX idx_profiles_id ON profiles(id);

Subquery Optimization for JWT Functions:

-- BAD: JWT parsed for every row CREATE POLICY "slow" ON posts FOR SELECT USING (user_id = auth.uid());

-- GOOD: JWT parsed once via subquery CREATE POLICY "fast" ON posts FOR SELECT USING (user_id = (SELECT auth.uid()));

2. Migration Best Practices

File Naming Convention:

supabase/migrations/ ├── 001_initial_schema.sql ├── 002_add_profiles_trigger.sql ├── 003_forum_tables.sql └── 004_add_rls_policies.sql

Migration Template:

-- Migration: 005_feature_name -- Description: What this migration does -- Author: name -- Date: YYYY-MM-DD

-- Up migration BEGIN;

-- Your DDL here CREATE TABLE ...; ALTER TABLE ...; CREATE POLICY ...;

COMMIT;

-- Down migration (as comment for reference) -- DROP TABLE ...; -- DROP POLICY ...;

Safe Migration Patterns:

-- Add column with default (no table lock) ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- Add NOT NULL constraint safely ALTER TABLE users ADD COLUMN email text; UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL; ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Create index concurrently (no lock) CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

3. Auth Integration

Auto-create Profile on Signup:

-- Function to create profile CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS TRIGGER AS $$ BEGIN INSERT INTO public.profiles (id, email, display_name) VALUES ( NEW.id, NEW.email, COALESCE(NEW.raw_user_meta_data->>'display_name', split_part(NEW.email, '@', 1)) ); RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger on auth.users CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

Check Auth Status in Policies:

-- Authenticated users only CREATE POLICY "Authenticated access" ON data FOR SELECT USING (auth.role() = 'authenticated');

-- Get current user's ID SELECT auth.uid();

-- Get current user's JWT claims SELECT auth.jwt();

4. Common Schema Patterns

Timestamps with Defaults:

CREATE TABLE posts ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE, content text NOT NULL, created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now() );

-- Auto-update updated_at CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER update_posts_updated_at BEFORE UPDATE ON posts FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Soft Delete Pattern:

ALTER TABLE posts ADD COLUMN deleted_at timestamptz;

CREATE POLICY "Hide deleted" ON posts FOR SELECT USING (deleted_at IS NULL);

Full-Text Search:

-- Add search vector column ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Create GIN index CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Update function CREATE OR REPLACE FUNCTION posts_search_update() RETURNS TRIGGER AS $$ BEGIN NEW.search_vector := to_tsvector('english', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, '')); RETURN NEW; END; $$ LANGUAGE plpgsql;

-- Search query SELECT * FROM posts WHERE search_vector @@ plainto_tsquery('english', 'search terms');

5. Debugging RLS Issues

Common Problem: Empty Results, No Error

-- Check if RLS is enabled SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public';

-- List all policies SELECT * FROM pg_policies WHERE tablename = 'your_table';

-- Test as specific role SET ROLE anon; SELECT * FROM your_table LIMIT 1; RESET ROLE;

-- Test with specific user SET request.jwt.claims TO '{"sub": "user-uuid-here"}'; SELECT * FROM your_table;

Diagnostic Query:

-- Check what the current user can see SELECT auth.uid() as current_user, auth.role() as current_role, (SELECT count(*) FROM your_table) as visible_rows;

Quick Reference

Task Command

Enable RLS ALTER TABLE t ENABLE ROW LEVEL SECURITY;

Create policy CREATE POLICY "name" ON t FOR action USING (condition);

Drop policy DROP POLICY "name" ON t;

Check policies SELECT * FROM pg_policies WHERE tablename = 't';

Current user SELECT auth.uid();

Force RLS for owner ALTER TABLE t FORCE ROW LEVEL SECURITY;

References

See /references/ for detailed guides:

• rls-patterns.md

• Advanced RLS policy patterns

• migration-checklist.md

• Pre-deployment checklist

• performance-tuning.md

• Query and index optimization

• social-schema.md

• Schema patterns for social features