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