supabase-admin

license	Apache-2.0
name	supabase-admin
description	Supabase administration, RLS policies, migrations, and schema design. Use for database architecture, Row Level Security, performance tuning, auth integration. Activate on "Supabase", "RLS", "migration", "policy", "schema", "auth.uid()". NOT for Supabase Auth UI configuration (use dashboard), edge functions (use cloudflare-worker-dev), or general SQL without Supabase context.
allowed-tools	["Read","Write","Edit","Bash","Grep","Glob","mcp__supabase__*"]
category	DevOps & Infrastructure
tags	["supabase","rls","database","postgres","migration","schema","security"]

Supabase Administration Expert

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

Decision Points

1. RLS Pattern Selection

Check data sensitivity & access patterns:
├── Public content (blogs, docs)
│   └── Use: Public read + owner write policies
├── User-owned data (profiles, settings)
│   └── Use: Owner-only access (auth.uid() = user_id)
├── Multi-tenant (org data)
│   ├── Small orgs (<1000 users): RLS with org_id filter
│   └── Large orgs (>1000 users): Schema per tenant
└── Admin content (system config)
    └── Use: Role-based policies with profiles.role check

2. Delete Strategy Decision Tree

Evaluate data retention needs:
├── Needs audit trail or recovery?
│   ├── High compliance: Soft delete (deleted_at timestamp)
│   └── Medium compliance: Soft delete + cascade trigger
├── Performance critical with large volume?
│   ├── Yes: Hard delete with CASCADE
│   └── No: Soft delete acceptable
└── Referenced by other tables?
    ├── Critical refs: Use ON DELETE RESTRICT
    └── Clean cascade: Use ON DELETE CASCADE

3. Migration Risk Assessment

Check change impact:
├── Schema changes
│   ├── Adding nullable column: Low risk, deploy anytime
│   ├── Adding NOT NULL: Medium risk, needs backfill
│   └── Dropping column: High risk, needs staged deployment
├── RLS policy changes
│   ├── Relaxing permissions: Low risk
│   └── Restricting access: High risk, test with real data
└── Index changes
    ├── Creating index: Use CONCURRENTLY flag
    └── Dropping index: Check query performance impact first

Failure Modes

Schema Bloat Anti-Pattern

Symptoms: Tables with 20+ columns, nullable columns everywhere, poor query performance Detection: SELECT count(*) FROM information_schema.columns WHERE table_name = 'bloated_table' returns >15 Fix: Normalize into related tables, use JSONB for flexible attributes, add NOT NULL constraints with defaults

RLS Performance Killer

Symptoms: Queries taking >1000ms, high CPU on simple selects, missing auth.uid() in WHERE clauses Detection: EXPLAIN ANALYZE shows sequential scans on user_id columns without indexes Fix: Add CREATE INDEX idx_table_user_id ON table(user_id) for every RLS-filtered column

Migration Lock Hell

Symptoms: Deployment timeouts, blocked writes during migration, "relation does not exist" errors Detection: Migration contains ALTER TABLE ADD COLUMN x NOT NULL without DEFAULT Fix: Split into: 1) Add nullable column with default, 2) Backfill data, 3) Add NOT NULL constraint

JWT Parsing Overload

Symptoms: RLS policies calling auth.uid() for every row, poor performance on large tables Detection: Policy has auth.uid() = user_id in USING clause without subquery Fix: Rewrite as user_id = (SELECT auth.uid()) to parse JWT once per query

Cascade Confusion

Symptoms: Data disappearing unexpectedly, orphaned records, referential integrity errors Detection: Foreign keys without explicit ON DELETE behavior, surprise empty result sets Fix: Explicitly set ON DELETE CASCADE/RESTRICT/SET NULL based on business logic

Worked Examples

Multi-Tenant RLS with Performance Optimization

Scenario: SaaS app where users belong to organizations, need org-isolated data

Step 1: Schema Design Decision

-- Decision: Use org_id column vs separate schemas
-- Trade-off: RLS filtering vs complete isolation
-- Choice: RLS (easier ops, shared resources)

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

Step 2: Index Strategy (Critical for Performance)

-- Compound index: org_id first (highest selectivity for RLS)
CREATE INDEX idx_posts_org_user ON posts(org_id, user_id);
-- Single index for user lookups
CREATE INDEX idx_posts_user ON posts(user_id);

Step 3: RLS Policy with Org Context

-- Get user's org_id from profiles table
CREATE POLICY "Org members only" ON posts
  FOR ALL USING (
    org_id = (
      SELECT profiles.org_id 
      FROM profiles 
      WHERE profiles.id = auth.uid()
    )
  );

Step 4: Performance Validation

-- Test query performance
EXPLAIN ANALYZE 
SELECT * FROM posts 
WHERE org_id = 'test-org-id' 
LIMIT 10;
-- Should show Index Scan, not Seq Scan
-- Should be <100ms for tables with <1M rows

Expert Insight: Novices miss the compound index ordering. They create (user_id, org_id) which forces RLS to scan all user posts then filter by org. Experts put org_id first since RLS filters by org membership.

Quality Gates

RLS audit checklist - all must pass before production:

• All user tables have ALTER TABLE x ENABLE ROW LEVEL SECURITY
• Every auth.uid() reference has corresponding index on user_id column
• RLS policies tested with SET ROLE anon and real user JWTs
• EXPLAIN ANALYZE shows Index Scan (not Seq Scan) for auth queries
• Policy queries execute in <100ms with production data volume
• Migration tested on copy of production data (not empty dev DB)
• No ALTER TABLE ADD COLUMN x NOT NULL without DEFAULT in single transaction
• Foreign key constraints have explicit ON DELETE behavior (not default)
• Full-text search uses GIN indexes, not LIKE queries
• Auth triggers handle edge cases (profile creation failures, duplicate emails)

NOT-FOR Boundaries

Do NOT use this skill for:

• Supabase Auth UI styling/configuration → Use Supabase dashboard documentation
• Edge Functions development → Use cloudflare-worker-dev skill instead
• Client-side Supabase SDK → Use official Supabase JavaScript docs
• General PostgreSQL optimization → Use postgresql-dba skill for non-Supabase context
• Real-time subscriptions setup → Use Supabase Realtime documentation
• Storage bucket policies → Use Supabase Storage documentation

When to delegate:

• Complex analytical queries → postgresql-dba skill
• Frontend integration → Framework-specific skills
• DevOps deployment → docker-deployment or kubernetes-ops skills