name: supabase-mastery
description: Supabase deep optimization for performance, security, and best practices. Covers PostgreSQL queries, RLS policies, real-time, and edge functions. Use when building or optimizing Supabase projects.
license: MIT
metadata:
author: thesimpleapp
version: "1.0"

Supabase Mastery

Deep optimization for Supabase projects - database, auth, storage, and edge functions.

Integration via Rube (Preferred)

Use Rube for all Supabase operations - no per-project API keys needed.

Quick Start

# List your projects
SUPABASE_LIST_ALL_PROJECTS

# List tables in a project
SUPABASE_LIST_TABLES project_ref="tzuhmsgldrzqjoqlimju"

# Run SQL query
SUPABASE_BETA_RUN_SQL_QUERY
  ref="tzuhmsgldrzqjoqlimju"
  query="SELECT * FROM users LIMIT 10"
  read_only=true

# Get table schema
SUPABASE_GET_TABLE_SCHEMAS
  project_ref="tzuhmsgldrzqjoqlimju"
  table_names=["users", "orders"]

Project Detection

Rube auto-detects project from:
1. CLAUDE.md project_ref field
2. Folder name matching project name
3. Ask user if ambiguous

See /rube-hub for full integration details.

Performance Impact Levels

Database Optimization

Index Strategy

-- ❌ Missing index on frequently queried column
SELECT * FROM orders WHERE user_id = 'xxx';

-- ✅ Add index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ✅ Partial index for common queries
CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status = 'pending';

-- ✅ Composite index for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Query Optimization

-- ❌ N+1 Query Pattern
-- First query: SELECT * FROM users
-- Then for each user: SELECT * FROM orders WHERE user_id = ?

-- ✅ Single query with join
SELECT u.*,
       COALESCE(json_agg(o.*) FILTER (WHERE o.id IS NOT NULL), '[]') as orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

-- ❌ SELECT * when you only need specific columns
SELECT * FROM users;

-- ✅ Select only needed columns
SELECT id, name, email FROM users;

-- ❌ Loading all records for pagination
SELECT * FROM products ORDER BY created_at;

-- ✅ Cursor-based pagination
SELECT * FROM products
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 20;

Connection Pooling

// Use connection pooler for serverless
const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;

// For Edge Functions / Serverless
// Use the pooler URL (port 6543) not direct (port 5432)
const connectionString = `postgresql://postgres:[password]@[host]:6543/postgres`;

EXPLAIN ANALYZE

-- Always check query plans for slow queries
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 'xxx'
AND created_at > NOW() - INTERVAL '30 days';

-- Look for:
-- - Seq Scan (might need index)
-- - High row estimates
-- - Slow nested loops

Row Level Security (RLS)

Policy Patterns

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Users can only see their own orders
CREATE POLICY "Users can view own orders"
ON orders FOR SELECT
USING (auth.uid() = user_id);

-- Users can insert their own orders
CREATE POLICY "Users can create own orders"
ON orders FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Users can update their own pending orders
CREATE POLICY "Users can update own pending orders"
ON orders FOR UPDATE
USING (auth.uid() = user_id AND status = 'pending')
WITH CHECK (auth.uid() = user_id);

-- Admin role bypass
CREATE POLICY "Admins can do anything"
ON orders FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role = 'admin'
  )
);

RLS Performance

-- ❌ Slow: Subquery in every policy check
USING (
  user_id IN (SELECT id FROM users WHERE org_id = auth.jwt()->>'org_id')
);

-- ✅ Fast: Direct comparison with JWT claims
USING (
  (auth.jwt()->>'org_id')::uuid = org_id
);

-- ✅ Index for RLS column
CREATE INDEX idx_orders_org_id ON orders(org_id);

Real-time Optimization

Channel Management

// ❌ Too many subscriptions
orders.forEach(order => {
  supabase.channel(`order-${order.id}`).subscribe();
});

// ✅ Single subscription with filter
const channel = supabase
  .channel('orders')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'orders',
      filter: `user_id=eq.${userId}`,
    },
    (payload) => handleOrderChange(payload)
  )
  .subscribe();

// Always cleanup
useEffect(() => {
  return () => {
    supabase.removeChannel(channel);
  };
}, []);

Broadcast for Presence

// Use broadcast for presence, not database
const room = supabase.channel('room-1', {
  config: {
    presence: {
      key: userId,
    },
  },
});

room
  .on('presence', { event: 'sync' }, () => {
    const state = room.presenceState();
    setOnlineUsers(Object.keys(state));
  })
  .subscribe(async (status) => {
    if (status === 'SUBSCRIBED') {
      await room.track({ online_at: new Date().toISOString() });
    }
  });

Edge Functions

Best Practices

// supabase/functions/process-order/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
};

serve(async (req) => {
  // Handle CORS preflight
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders });
  }

  try {
    // Create client with user's JWT
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
      {
        global: {
          headers: { Authorization: req.headers.get('Authorization')! },
        },
      }
    );

    // Verify user
    const { data: { user }, error: authError } = await supabase.auth.getUser();
    if (authError || !user) {
      return new Response(JSON.stringify({ error: 'Unauthorized' }), {
        status: 401,
        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
      });
    }

    // Process request
    const { orderId } = await req.json();

    const { data, error } = await supabase
      .from('orders')
      .update({ status: 'processed' })
      .eq('id', orderId)
      .eq('user_id', user.id) // Always filter by user
      .select()
      .single();

    if (error) throw error;

    return new Response(JSON.stringify(data), {
      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
    });
  } catch (error) {
    return new Response(JSON.stringify({ error: error.message }), {
      status: 500,
      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
    });
  }
});

Storage Optimization

// Generate signed URLs for private files
const { data } = await supabase.storage
  .from('documents')
  .createSignedUrl('file.pdf', 3600); // 1 hour expiry

// Transform images on the fly
const imageUrl = supabase.storage
  .from('avatars')
  .getPublicUrl('user.jpg', {
    transform: {
      width: 200,
      height: 200,
      resize: 'cover',
    },
  });

// Upload with proper content type
await supabase.storage
  .from('uploads')
  .upload(path, file, {
    contentType: file.type,
    cacheControl: '3600',
  });

Monitoring & Debugging

Query Performance

-- Find slow queries
SELECT
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Check index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Dashboard Queries

Check in Supabase Dashboard:
- Database → Query Performance
- Database → Index Advisor
- Logs → API Logs (for slow requests)

Security Checklist

□ RLS enabled on all tables
□ Service role key only in backend
□ Anon key only has minimal permissions
□ All policies tested
□ No secrets in client code
□ Webhook signatures verified
□ Rate limiting configured
□ Auth redirects to safe URLs only

Schema Design

Timestamps

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  total DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_updated_at
  BEFORE UPDATE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Soft Deletes

ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;

-- RLS policy excludes deleted
CREATE POLICY "Exclude deleted orders"
ON orders FOR SELECT
USING (deleted_at IS NULL);