Configure and manage Supabase projects using MCP (Model Context Protocol). Use this skill when working with Supabase databases, setting up MCP servers, designing database schemas, implementing Row Level Security, managing migrations, or building modern data architectures with PostgreSQL. Essential for Supabase development, database design, and AI-powered database operations.
Configure and manage Supabase projects using MCP (Model Context Protocol). Use this skill when working with Supabase databases, setting up MCP servers, designing database schemas, implementing Row Level Security, managing migrations, or building modern data architectures with PostgreSQL. Essential for Supabase development, database design, and AI-powered database operations.
Supabase MCP Skill
Overview
This skill provides comprehensive guidance for working with Supabase through the Model Context Protocol (MCP), enabling AI-powered database operations, modern schema design, and production-ready database architectures.
When to Use This Skill
Use this skill when you encounter ANY of the following:
Setup & Configuration
Setting up or configuring Supabase MCP servers (Cursor, Claude Desktop, Claude Code CLI)
Connecting AI tools to Supabase projects
Debugging MCP connection issues
Configuring read-only mode, project scoping, or feature groups
Database Design
Designing database schemas following best practices
-- Create profiles table linked to auth.userscreate table public.profiles (
id uuid references auth.users(id) primary key,
username text uniquenot null,
full_name text,
avatar_url text,
created_at timestamptz default now()
);
-- Enable RLSalter table public.profiles enable row level security;
-- Allow anyone to view profilescreate policy "Users can view all profiles"
on public.profiles forselectusing (true);
-- Users can only update their own profilecreate policy "Users can update own profile"
on public.profiles forupdateusing (auth.uid() = id);
4. Multi-Tenant Organizations with RLS
-- Organizations tablecreate table public.organizations (
id uuid primary keydefault gen_random_uuid(),
name text not null,
slug text uniquenot null
);
-- Organization members junction tablecreate table public.org_members (
org_id uuid references public.organizations(id) ondelete cascade,
user_id uuid references auth.users(id) ondelete cascade,
role text not nullcheck (role in ('owner', 'admin', 'member')),
primary key (org_id, user_id)
);
-- RLS: Users can only see organizations they're members ofalter table public.organizations enable row level security;
create policy "Users can view own organizations"
on public.organizations forselectusing (
exists (
select1from public.org_members
where org_members.org_id = organizations.id
and org_members.user_id = auth.uid()
)
);
5. Session Load-or-Create Pattern
// Load existing session or create new oneconst { data: existing, error: fetchError } = await supabase
.from('user_sessions')
.select('*')
.eq('phone_number', phoneNumber)
.single();
// Handle "no rows returned" as valid (not an error)if (fetchError && fetchError.code !== 'PGRST116') {
thrownewError(`Failed to load session: ${fetchError.message}`);
}
if (existing) {
// Update existing sessionawait supabase
.from('user_sessions')
.update({ last_active_at: newDate().toISOString() })
.eq('id', existing.id);
return existing;
}
// Create new session if none existsconst { data: newSession } = await supabase
.from('user_sessions')
.insert({ phone_number: phoneNumber })
.select()
.single();
return newSession;
-- Products with full history trackingcreate table public.products_history (
id uuid not null,
name text not null,
price numeric(10,2) not null,
-- Temporal columns
valid_from timestamptz not nulldefault now(),
valid_to timestamptz, -- null = current version-- Audit columns
changed_by uuid references auth.users(id),
change_reason text,
primary key (id, valid_from)
);
-- View for current products onlycreateview public.products asselect id, name, price
from public.products_history
where valid_to isnull;
8. Materialized View for Analytics
-- Aggregate analytics data for fast queriescreate materialized view public.orders_analytics asselect
date_trunc('day', created_at) as order_date,
count(*) as total_orders,
sum(total) as revenue,
avg(total) as avg_order_value
from public.orders
where status ='completed'groupby date_trunc('day', created_at);
-- Refresh functioncreateor replace function refresh_analytics()
returns void as $$
begin
refresh materialized view public.orders_analytics;
end;
$$ language plpgsql;
9. Audit Logging with Triggers
-- Audit logs tablecreate table public.audit_logs (
id bigint generated always asidentityprimary key,
table_name text not null,
record_id uuid not null,
action text not nullcheck (action in ('insert', 'update', 'delete')),
old_data jsonb,
new_data jsonb,
user_id uuid references auth.users(id),
created_at timestamptz default now()
);
-- Trigger function for automatic auditingcreateor replace function public.audit_trigger()
returnstriggeras $$
begininsert into public.audit_logs (
table_name, record_id, action, old_data, new_data, user_id
) values (
TG_TABLE_NAME,
coalesce(NEW.id, OLD.id),
lower(TG_OP),
to_jsonb(OLD),
to_jsonb(NEW),
auth.uid()
);
returnNEW;
end;
$$ language plpgsql security definer;
10. Event-Driven Pattern with pg_notify
-- Publish events using PostgreSQL NOTIFYcreateor replace function orders_service.notify_order_created()
returnstriggeras $$
begin
perform pg_notify(
'order_created',
json_build_object(
'order_id', NEW.id,
'user_id', NEW.user_id,
'total', NEW.total
)::text
);
returnNEW;
end;
$$ language plpgsql;
createtrigger order_created_trigger
after inserton orders_service.orders
foreachrowexecutefunction orders_service.notify_order_created();
Key Concepts
Row Level Security (RLS)
PostgreSQL feature that restricts which rows users can access based on policies. Essential for multi-tenant applications and user data isolation.
MCP (Model Context Protocol)
Protocol enabling AI assistants to interact with external services like Supabase. Provides natural language querying and schema operations. The Supabase MCP server is also available via mcp-lite on Edge Functions (zero cold starts, global deployment) and as a self-hosted Docker image (supabase/mcp-server:latest).
Upsert Pattern
Insert a new row or update if it already exists, using onConflict. Atomic operation preventing race conditions.
Composite Keys
Unique constraints across multiple columns (e.g., session_id,key). Used for scoping data within contexts.
Index Foreign Keys - Always index columns used in joins
Use EXPLAIN ANALYZE - Understand query execution plans
Avoid N+1 Queries - Use joins or batch fetching
Materialized Views - For expensive analytical queries
Connection Pooling - Use pgBouncer for high-concurrency
Common Workflows
Natural Language Database Queries
Ask Claude: "Show me all users who signed up last month but haven't completed their profile"
Schema Design with AI
Ask Claude: "Design a schema for a multi-tenant SaaS application with organizations, users, and billing"
Migration Generation
Ask Claude: "Generate a migration to add a 'status' column to the posts table with an enum type"
RLS Policy Creation
Ask Claude: "Create RLS policies so users can only see posts from organizations they're members of"
Query Optimization
Ask Claude: "Analyze this query and suggest optimizations: SELECT * FROM orders WHERE user_id = '...' AND created_at > '2024-01-01'"
Troubleshooting Quick Reference
MCP Connection Failures
Verify API keys are correct and not expired
Check network connectivity to MCP endpoints
Ensure organization permissions are properly set
Try: claude mcp list to see configured servers
RLS Policy Errors
Test policies with different user contexts
Use auth.uid() for current user checks
Remember service role keys bypass RLS
Check policy using EXPLAIN: EXPLAIN SELECT * FROM table_name;
Migration Failures
Always test migrations on branches first
Check for dependency issues (foreign keys, triggers)
Verify data compatibility before schema changes
Use supabase db reset locally to test from scratch
Performance Issues
Review query plans with EXPLAIN ANALYZE
Check for missing indexes on foreign keys
Monitor connection pool utilization
Use pg_stat_user_indexes to track index usage
Version History
v2.1 (2026-04-21) - Updated for mcp-lite Edge Functions deployment (zero cold starts), self-hosted MCP via Docker, noted continued public alpha status
v2.0 (2025-11-01) - Enhanced with official Supabase documentation, production patterns from Twilio-Aldea, TypeScript/SQL examples, and comprehensive RLS guide
v1.0 - Initial skill creation with MCP setup, database patterns, schema design, security, and tools reference
