Supabase Performance Audit Prompt (MCP-Executable)

Please perform a comprehensive performance audit of this Supabase database using the Supabase MCP tools.

IMPORTANT: After analysis, present your findings and proposed migrations. Wait for my confirmation before applying any migrations to the database.

Follow these steps:

1. Get Performance Advisories

• Use Supabase MCP to fetch all performance advisors:
• Use get_advisors with type: "performance" and project_id

Review all performance recommendations and categorize by severity (WARN, INFO).

2. Audit RLS Performance

Query all RLS policies to identify performance issues:

• Auth RLS InitPlan: Policies calling auth.uid() or auth.jwt() directly instead of wrapped in subqueries
• Multiple Permissive Policies: Duplicate policies on the same table/role/action that should be consolidated

3. Analyze Index Usage

Query index usage statistics to identify:

• Unused indexes: Indexes that consume space without providing query benefits
• Unindexed foreign keys: Foreign key columns without covering indexes
• Index bloat: Large indexes that may need rebuilding

4. Query Table Statistics

Get table size and row count statistics to identify:

• Large tables that need optimization or archiving
• Tables with high dead row counts that need vacuuming

5. Analyze Query Performance

Check query performance patterns:

• Sequential scans: Tables with high sequential scan counts on large tables (indicates missing indexes)
• Full-text search: Verify tsvector columns have appropriate GIN indexes

6. Create Optimization Migrations

For each performance issue found from the queries above, create SQL migration files:

• Fix Auth RLS InitPlan: For policies calling auth.uid() directly, wrap them with (SELECT auth.uid())
• Consolidate Multiple Policies: Combine duplicate policies for the same table/role/action into single policies with OR logic
• Add Missing Indexes: Create indexes for unindexed foreign keys and frequently queried columns
• Remove Unused Indexes: Only after careful verification, drop indexes that are never used

7. Present Plan and Wait for Confirmation

Before applying any changes:

1. Show all proposed SQL migrations
2. Explain the expected impact of each change
3. Highlight any potential risks
4. Wait for explicit confirmation before proceeding

8. Apply Migrations (After Confirmation)

Once confirmed, use apply_migration to apply performance fixes:

• Apply each migration with descriptive names (e.g., fix_auth_rls_initplan, add_missing_indexes)
• Save all SQL to your project's migration folder
• Regenerate TypeScript types using generate_typescript_types

9. Verify Improvements

After applying migrations, re-run the audit queries to verify:

• RLS policies are optimized (auth functions wrapped in subqueries)
• New indexes are being used
• Table statistics show improvements

---

Output Format

Please provide:

1. Executive summary: Key performance bottlenecks identified
2. Detailed findings: Organized by category (RLS, Indexes, Queries)
3. Optimization migrations: SQL migration files for each performance issue
4. Expected impact: Estimated performance improvement for each fix
5. Verification steps: How to confirm optimizations were applied

Notes

• Only use Supabase MCP tools (execute_sql, get_advisors, apply_migration, generate_typescript_types)
• Focus exclusively on database-level performance (RLS, indexes, queries)
• All fixes should be SQL migrations that can be applied via MCP
• Do not include application-level performance checks
• Test that optimizations don't break RLS permissions