Supabase Skill

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
• Implementing table relationships (one-to-many, many-to-many, polymorphic)
• Choosing between normalization patterns (single table vs class table inheritance)
• Designing for multi-tenancy or microservices
• Working with temporal data or audit logging

Security & Access Control

• Implementing Row Level Security (RLS) policies
• Setting up authentication integration with auth.users
• Building role-based access control (RBAC)
• Securing multi-tenant applications
• Preventing unauthorized data access

Migrations & Schema Evolution

• Creating and managing database migrations
• Evolving schemas without breaking changes
• Handling backward compatibility
• Using Supabase branching for testing

Production Patterns

• Session management with timeout logic
• Memory persistence patterns (dual persistence with soul + process memory)
• Message storage with region-based organization
• State management with validation
• Error handling and graceful degradation

Performance & Optimization

• Indexing strategies for query performance
• Query optimization with EXPLAIN ANALYZE
• Connection pooling configuration
• Read replica setup for scaling

Quick Reference Examples

1. MCP Server Setup (Claude Code CLI)

# Basic installation with project scoping
claude mcp add --transport stdio \
  --env SUPABASE_ACCESS_TOKEN=your_personal_access_token \
  --scope local \
  supabase -- npx -y @supabase/mcp-server-supabase@latest \
  --project-ref=your_project_ref

# With read-only mode (safe for exploration)
claude mcp add --transport stdio \
  --env SUPABASE_ACCESS_TOKEN=your_token \
  --scope local \
  supabase -- npx -y @supabase/mcp-server-supabase@latest \
  --project-ref=your_project_ref \
  --read-only

2. Singleton Supabase Client (TypeScript)

import { createClient, SupabaseClient } from '@supabase/supabase-js';

let supabaseClient: SupabaseClient | null = null;

export function getSupabaseClient(): SupabaseClient {
  if (!supabaseClient) {
    const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
    const supabaseServiceKey = process.env.SUPABASE_SERVICE_KEY;

    if (!supabaseUrl || !supabaseServiceKey) {
      throw new Error('Missing Supabase environment variables');
    }

    supabaseClient = createClient(supabaseUrl, supabaseServiceKey, {
      auth: {
        autoRefreshToken: false,  // Server-side: no refresh needed
        persistSession: false,     // Server-side: no persistence
      },
    });
  }

  return supabaseClient;
}

3. User Profile with RLS (SQL)

-- Create profiles table linked to auth.users
create table public.profiles (
  id uuid references auth.users(id) primary key,
  username text unique not null,
  full_name text,
  avatar_url text,
  created_at timestamptz default now()
);

-- Enable RLS
alter table public.profiles enable row level security;

-- Allow anyone to view profiles
create policy "Users can view all profiles"
  on public.profiles for select
  using (true);

-- Users can only update their own profile
create policy "Users can update own profile"
  on public.profiles for update
  using (auth.uid() = id);

4. Multi-Tenant Organizations with RLS

-- Organizations table
create table public.organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text unique not null
);

-- Organization members junction table
create table public.org_members (
  org_id uuid references public.organizations(id) on delete cascade,
  user_id uuid references auth.users(id) on delete cascade,
  role text not null check (role in ('owner', 'admin', 'member')),
  primary key (org_id, user_id)
);

-- RLS: Users can only see organizations they're members of
alter table public.organizations enable row level security;

create policy "Users can view own organizations"
  on public.organizations for select
  using (
    exists (
      select 1 from 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 one
const { 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') {
  throw new Error(`Failed to load session: ${fetchError.message}`);
}

if (existing) {
  // Update existing session
  await supabase
    .from('user_sessions')
    .update({ last_active_at: new Date().toISOString() })
    .eq('id', existing.id);

  return existing;
}

// Create new session if none exists
const { data: newSession } = await supabase
  .from('user_sessions')
  .insert({ phone_number: phoneNumber })
  .select()
  .single();

return newSession;

6. Upsert Pattern for Key-Value Storage

// Save or update soul memory (upsert pattern)
const { data, error } = await supabase
  .from('soul_memories')
  .upsert(
    {
      session_id: sessionId,
      key: 'user_name',
      value: { firstName: 'Alice', lastName: 'Smith' },
      updated_at: new Date().toISOString(),
    },
    { onConflict: 'session_id,key' }  // Composite unique constraint
  )
  .select()
  .single();

7. Temporal Data Pattern (History Tracking)

-- Products with full history tracking
create table public.products_history (
  id uuid not null,
  name text not null,
  price numeric(10,2) not null,

  -- Temporal columns
  valid_from timestamptz not null default 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 only
create view public.products as
select id, name, price
from public.products_history
where valid_to is null;

8. Materialized View for Analytics

-- Aggregate analytics data for fast queries
create materialized view public.orders_analytics as
select
  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'
group by date_trunc('day', created_at);

-- Refresh function
create or replace function refresh_analytics()
returns void as $$
begin
  refresh materialized view public.orders_analytics;
end;
$$ language plpgsql;

9. Audit Logging with Triggers

-- Audit logs table
create table public.audit_logs (
  id bigint generated always as identity primary key,
  table_name text not null,
  record_id uuid not null,
  action text not null check (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 auditing
create or replace function public.audit_trigger()
returns trigger as $$
begin
  insert 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()
  );
  return NEW;
end;
$$ language plpgsql security definer;

10. Event-Driven Pattern with pg_notify

-- Publish events using PostgreSQL NOTIFY
create or replace function orders_service.notify_order_created()
returns trigger as $$
begin
  perform pg_notify(
    'order_created',
    json_build_object(
      'order_id', NEW.id,
      'user_id', NEW.user_id,
      'total', NEW.total
    )::text
  );
  return NEW;
end;
$$ language plpgsql;

create trigger order_created_trigger
after insert on orders_service.orders
for each row execute function 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.

JSONB Storage

PostgreSQL's binary JSON storage format. Allows flexible schemas while maintaining query performance.

Temporal Data

Tracking historical changes with valid_from and valid_to timestamps. Enables point-in-time queries.

Service Role vs Anon Key

• Service Role: Admin access, bypasses RLS (use server-side only)
• Anon Key: User access, subject to RLS (safe for client-side)

Reference Files

Core References

references/mcp-setup.md - MCP Server Configuration

Complete guide for setting up Supabase MCP servers:

• Remote (hosted) vs Local (self-hosted) installation
• Configuration for Cursor, Claude Desktop, Claude Code CLI
• Authentication with OAuth and Personal Access Tokens
• Security best practices (read-only mode, project scoping)
• Troubleshooting connection and auth issues

references/database-patterns.md - Modern Database Design

Architectural patterns for scalable applications:

• Data Lakehouse Pattern (OLTP + OLAP in one system)
• Microservices Pattern (database per service)
• Event Sourcing Pattern (complete audit trail)
• CQRS Pattern (separate read/write models)
• Inheritance patterns (single table vs class table)
• Polymorphic associations
• Temporal data patterns (SCD Type 2)

references/schema-design.md - PostgreSQL Best Practices

Guidelines for robust schema design:

• Primary key strategies (UUID vs serial)
• Foreign key relationships and cascading
• Index design for query optimization
• Constraint enforcement
• Default values and computed columns
• JSON/JSONB usage patterns
• Normalization strategies

references/security.md - RLS and Authentication

Production-grade security implementation:

• Row Level Security policy patterns
• Authentication integration with auth.users
• Role-based access control (RBAC)
• Data encryption strategies
• Audit logging patterns
• Multi-tenancy security models

references/tools-reference.md - MCP Tools

Complete documentation of available MCP tools:

• Natural language querying
• Schema introspection
• Migration management
• Project configuration retrieval
• Database branching (experimental)
• Log analysis and debugging

Official Supabase Documentation

references/official-docs/row-level-security.md

Complete RLS guide from official Supabase docs:

• 10+ RLS policy patterns
• Production examples from real repositories
• Common pitfalls and solutions
• Testing RLS policies
• Performance considerations

references/official-docs/migrations.md

Database migrations guide:

• Imperative migrations (SQL files)
• Declarative schemas (schema.yml)
• Migration workflow and best practices
• Handling breaking changes
• Using Supabase branches for testing

Production Patterns

references/production-patterns.md

Real-world patterns from Twilio-Aldea (production SMS AI platform):

• Session management with timeout logic
• Dual persistence (soul + process memory)
• Message storage with region-based organization
• State management with validation
• Error handling and graceful degradation
• Load-or-create patterns

Code Examples

assets/examples/supabase-client.ts

Client initialization with singleton pattern for server-side applications

assets/examples/session-management.ts

Session management with automatic timeout and state reset

assets/examples/memory-store.ts

Dual persistence pattern (soul memory + process memory)

assets/examples/rls-policies.sql

10 production-ready RLS policy patterns covering common use cases

GitHub Repository (NEW)

references/github/README.md

Official Supabase JS client README from GitHub (supabase/supabase-js, 4,045 stars)

references/github/issues.md

38 GitHub issues showing common problems and solutions:

• JWT token validation issues
• Next.js cache integration
• BigInt ID handling
• WebSocket race conditions
• RLS header propagation
• Realtime subscription bugs

references/github/CHANGELOG.md

Complete version history with breaking changes and migrations

references/github/releases.md

410 GitHub releases with detailed release notes and migration guides

references/github/file_structure.md

Repository structure (521 files) showing source code organization

Working with This Skill

For Beginners

Start Here:

1. Read references/mcp-setup.md for initial MCP configuration
2. Review assets/examples/supabase-client.ts for client setup basics
3. Study references/official-docs/migrations.md for schema management
4. Practice simple CRUD operations through MCP
5. Review Quick Reference Examples #1-3 above

First Project:

• Set up MCP in read-only mode to explore safely
• Create a simple user profiles table with RLS
• Practice natural language queries through MCP
• Create your first migration

For Intermediate Users

Deepen Your Knowledge:

1. Study references/official-docs/row-level-security.md for comprehensive RLS patterns
2. Review assets/examples/rls-policies.sql for production-ready policies
3. Implement session management using assets/examples/session-management.ts
4. Explore references/database-patterns.md for advanced designs
5. Review Quick Reference Examples #4-7 above

Practice Projects:

• Multi-tenant SaaS application with organizations + members + RLS
• Audit logging system with triggers
• Session-based state management with timeout logic
• Temporal data tracking (product price history)

For Advanced Users

Master Production Patterns:

1. Study references/production-patterns.md for real-world patterns
2. Implement dual persistence with assets/examples/memory-store.ts
3. Design microservices architecture with database-per-service
4. Optimize performance with indexing strategies and query analysis
5. Review Quick Reference Examples #8-10 above

Advanced Challenges:

• Event sourcing with CQRS pattern
• Multi-region replication and consistency
• High-availability MCP deployment with load balancing
• Complex RLS policies with dynamic role assignment
• Performance optimization for million-row tables

Navigation Tips

Finding the Right Documentation:

• MCP setup issues? → references/mcp-setup.md
• Security/RLS questions? → references/official-docs/row-level-security.md or references/security.md
• Schema design decisions? → references/database-patterns.md or references/schema-design.md
• Production examples? → references/production-patterns.md or assets/examples/
• Migration strategies? → references/official-docs/migrations.md

Quick Lookups: Use Quick Reference Examples section for immediate code snippets without diving into full docs.

Best Practices Summary

Database Design

1. Start Simple, Evolve Gradually - Begin normalized, denormalize based on usage
2. Use UUIDs for Primary Keys - Better for distributed systems, prevents enumeration
3. Leverage JSONB Sparingly - Prefer typed columns for known fields
4. Implement RLS from Day One - Security built-in, not bolted-on
5. Version Your Schema - Use migrations for all changes

MCP Usage

1. Use Project Scoping - Limit access to specific projects
2. Enable Read-Only Mode - For development/exploration
3. Manual Tool Approval - Keep enabled in production contexts
4. Monitor API Usage - Track operations through dashboard
5. Test in Branching - Use branches for testing schema changes

Security

1. Implement RLS Policies - Never rely on application-level security alone
2. Use Service Role Keys Carefully - Bypass RLS only when necessary
3. Audit All Operations - Log administrative database operations
4. Encrypt Sensitive Data - Use PostgreSQL encryption for PII
5. Regular Security Reviews - Periodically audit RLS policies

Performance

1. Index Foreign Keys - Always index columns used in joins
2. Use EXPLAIN ANALYZE - Understand query execution plans
3. Avoid N+1 Queries - Use joins or batch fetching
4. Materialized Views - For expensive analytical queries
5. 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