Supabase Migration Deep Dive

Overview

Supabase migrations are timestamped SQL files managed by the CLI that track schema changes across environments. This skill covers the full lifecycle — creating migrations, zero-downtime schema changes, batch backfills, versioning, rollback, and TypeScript type generation — using real CLI commands and createClient from @supabase/supabase-js.

When to use: Creating new database migrations, modifying production schemas without downtime, backfilling existing data after adding columns, managing migration history across dev/staging/production, rolling back failed migrations, or regenerating TypeScript types.

Prerequisites

• Supabase CLI installed: npm install -g supabase or npx supabase --version
• @supabase/supabase-js v2+ installed in your project
• Local Supabase running: npx supabase start
• Understanding of PostgreSQL DDL and transaction behavior

Instructions

Step 1: Create and Manage Migrations

Create each migration as a timestamped SQL file, write the DDL, test it against a local reset, then promote it through environments with db push:

npx supabase migration new add_profiles_table   # create timestamped SQL file
npx supabase migration list                      # show applied/pending status
npx supabase db reset                            # apply + seed locally (destructive)
npx supabase gen types typescript --local > lib/database.types.ts
npx supabase link --project-ref "<ref>" && npx supabase db push   # promote to remote

Write DDL that enables RLS, adds policies, indexes, and triggers in the same file so the schema is complete when applied. For the full worked migration (a profiles table with RLS policies, an email index, a signup trigger, and an updated_at trigger) plus the local-test and staging/production promotion commands, see creating migrations.

Step 2: Zero-Downtime Migration Patterns

Production schema changes must avoid locking tables. The core rules: adding a nullable column with a default is lock-free on Postgres 11+; build indexes with CREATE INDEX CONCURRENTLY in a -- supabase:disable-transaction migration; rename or retype a column in two phases (add new column, backfill, sync trigger, then drop the old one) rather than an in-place ALTER COLUMN. Example of the safe column-add:

-- Nullable column with a default does NOT lock the table in Postgres 11+
ALTER TABLE public.orders ADD COLUMN status text DEFAULT 'pending';
-- Then, in a separate -- supabase:disable-transaction migration:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON publi

Supabase Observability

Overview

Monitor Supabase projects end-to-end: Dashboard reports for API/database/auth metrics, supabase inspect db CLI for deep Postgres diagnostics, pgstatstatements for query analytics, log drains for external aggregation, Edge Functions for custom metrics, and alerting on quota thresholds.

Follow the three steps below from this file for the high-level workflow, then drill into the linked references/ files for the full commands, SQL, and function source.

Prerequisites

• Supabase CLI installed (npx supabase --version)
• Supabase project linked (supabase link --project-ref )
• Pro plan or higher for log drain support and extended log retention
• @supabase/supabase-js v2+ installed for application-level monitoring

Instructions

Step 1: Dashboard Reports and CLI Inspect Commands

Start with the built-in Dashboard > Reports (API Requests, Database, Auth Usage, Storage, Realtime) for high-level metrics. For deeper Postgres diagnostics, use the supabase inspect db subcommands — begin with cache hit ratio and sequential scans:

# Cache hit ratio — should be > 99% (below 95% means upgrade compute)
npx supabase inspect db cache-hit --linked

# Sequential scans — tables needing indexes
npx supabase inspect db seq-scans --linked

The full report table plus all nine inspect subcommands (table sizes, index usage, long-running queries, bloat, blocking, replication slots, and more) are in references/cli-inspect-commands.md.

Step 2: Query Analytics with pgstatstatements and Log Drains

Enable pgstatstatements for query-level metrics, then rank queries by execution time:

create extension if not exists pg_stat_statements;

-- Top slowest queries by average execution time
select substring(query, 1, 80) as query_preview, calls,
  round(mean_exec_time::numeric, 2) as avg_ms
from pg_stat_statements
where mean_exec_time > 50
order by mean_exec_time desc limit 20;

Forward logs to external aggregation with a log drain:

npx supabase log-drains add --name datadog-drain --type datadog \
  --datadog-api-key "$DATADOG_API_KEY" --datadog-region us1 --linked

The complete slow-query / high-frequency / connection-monitoring SQL set and every log-drain command (Datadog, webhook, list, remove) are in references/query-analytics-and-log-drains.md.

Step 3: Custom Metrics, Alerting, and Health Checks

Emit business-level metrics from a scheduled Edge Function, alert on quota thresholds, and expose a health endpoint for

Supabase Performance Tuning

Overview

Systematically improve Supabase query and database performance across three layers: PostgreSQL engine (indexes, query plans, materialized views), Supabase infrastructure (Supavisor connection pooling, Edge Functions, read replicas), and client SDK patterns (column selection, pagination, RPC functions). Every technique here is measurable — run EXPLAIN ANALYZE before and after to confirm the improvement.

Prerequisites

• Supabase project (local or hosted) with @supabase/supabase-js v2+ installed
• Supabase CLI installed (npx supabase --version to verify)
• Access to the SQL Editor in the Supabase Dashboard or a direct Postgres connection
• pgstatstatements extension enabled (Step 1 covers this)

Instructions

Step 1: Diagnose — Find What Is Slow

Start every performance effort with data. Enable pgstatstatements and run the Supabase CLI diagnostics to identify bottlenecks before optimizing.

Enable the stats extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Find the slowest queries by average execution time:

SELECT
  query,
  calls,
  mean_exec_time::numeric(10,2) AS avg_ms,
  total_exec_time::numeric(10,2) AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Check index usage and cache hit rates with the Supabase CLI:

# Which indexes are actually being used?
npx supabase inspect db index-usage

# What percentage of queries are served from cache vs disk?
npx supabase inspect db cache-hit

# Tables consuming the most space
npx supabase inspect db table-sizes

Inspect active connections for pooling issues:

SELECT state, count(*), max(age(now(), state_change)) AS max_age
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;

If idle connections exceed your plan's limit or active queries show high max_age, connection pooling (Step 2) and query optimization (Step 3) are the priority.

Step 2: Indexes and Query Plans

Indexes are the single highest-impact optimization. Use EXPLAIN ANALYZE to read query plans, then create targeted indexes.

Read a query plan:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'alice@example.com';

Look for Seq Scan on large tables — that means no index is being used. After adding an index, the plan should show Index Scan or Index Only Scan.

Create a basic index:

Supabase Policy Guardrails

Overview

Organizational governance for Supabase at scale: a shared RLS policy library (reusable templates for common access patterns), naming conventions (tables, columns, functions, policies), migration review process (CI checks ensuring RLS, preventing destructive operations, enforcing naming), cost alert configuration (billing thresholds and usage monitoring), and security audit scripts (scanning for exposed keys, missing RLS, overly permissive policies). All patterns use real createClient from @supabase/supabase-js and Supabase CLI commands.

Prerequisites

• Supabase project with supabase CLI installed and linked
• @supabase/supabase-js v2+ installed
• CI/CD pipeline (GitHub Actions recommended)
• Database access via psql or Supabase SQL Editor
• Pro plan recommended for cost alerts and usage API

Instructions

Step 1 — Shared RLS Policy Library and Naming Conventions

RLS Policy Templates

Create reusable RLS policy templates that teams apply to new tables. This prevents each developer from writing ad-hoc policies and ensures consistent access control.

-- supabase/migrations/00000000000000_rls_policy_library.sql
-- Shared RLS policy library — apply these templates to new tables

-- ============================================================
-- Template 1: Owner-only access (user owns the row)
-- Usage: tables with a user_id column (todos, profiles, settings)
-- ============================================================
CREATE OR REPLACE FUNCTION public.rls_owner_only(table_name text, user_column text DEFAULT 'user_id')
RETURNS void AS $$
BEGIN
  EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', table_name);

  EXECUTE format(
    'CREATE POLICY "owner_select" ON public.%I FOR SELECT USING (%I = auth.uid())',
    table_name, user_column
  );
  EXECUTE format(
    'CREATE POLICY "owner_insert" ON public.%I FOR INSERT WITH CHECK (%I = auth.uid())',
    table_name, user_column
  );
  EXECUTE format(
    'CREATE POLICY "owner_update" ON public.%I FOR UPDATE USING (%I = auth.uid())',
    table_name, user_column
  );
  EXECUTE format(
    'CREATE POLICY "owner_delete" ON public.%I FOR DELETE USING (%I = auth.uid())',
    table_name, user_column
  );
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- Template 2: Organization-scoped access (user is member of org)
-- Usage: tables with org_id referencing org_members
-- ============================================================
CREATE OR REPLACE FUNCTION public.rls_org_scoped(
  table_name text,
  org_column text DEFAULT 'org_id',
  allo

Supabase Rate Limits

Overview

Supabase enforces rate limits and quotas across every API surface — PostgREST, Auth, Storage, Realtime, and Edge Functions — and the numbers scale by plan tier. This skill gives you the exact per-tier limits, connection pooling via Supavisor, retry/backoff and pagination patterns, and dashboard monitoring so you stay within quota and handle 429 errors gracefully.

Prerequisites

• Active Supabase project (any tier)
• @supabase/supabase-js v2+ installed
• Project URL and anon/service-role key available
• Node.js 18+ or equivalent runtime

Instructions

Step 1 — Know your tier limits

Rate limits differ per surface and per plan. The headline API limits:

Auth, Storage, Realtime, Edge Functions, and Database connections each carry their own quotas. See the full per-surface breakdown in rate-limit-tiers.md before you architect.

Step 2 — Pool connections with Supavisor

Supavisor is Supabase's built-in connection pooler (replaced PgBouncer). Pick the mode by workload:

Transaction mode (port 6543) returns a connection to the pool after each transaction — the right default for serverless. Session mode (port 5432) holds a dedicated connection for LISTEN/NOTIFY and prepared statements. Full client setup and connection-string formats are in implementation.md.

Step 3 — Retry, paginate, and batch

Wrap queries in an exponential-backoff retry that recognizes 429s and pool exhaustion:

// Retryable when the error is a rate limit, "too many requests",
// code 429, or PGRST000 (connection pool exhausted). Delay doubles
// per attempt with jitter, capped at maxDelayMs, honoring Retry-After.
const users = await withRetry(() =>
  supabase.from('users').select('id, email, created_at').eq('active', true)
)

Then cut request volume two ways: paginate large reads with .range(from,

Supabase Reliability Patterns

Overview

Production Supabase apps need six reliability layers: circuit breakers (stop calling Supabase when it's down to prevent cascading failures), offline queue (buffer writes when the network is unavailable and replay when reconnected), graceful degradation (serve cached or fallback data during outages), health checks (detect Supabase availability before routing traffic), retry with exponential backoff (handle transient errors without overwhelming the service), and dual-write (write critical data to both Supabase and a backup store). All patterns use real createClient from @supabase/supabase-js.

Prerequisites

• @supabase/supabase-js v2+ installed
• TypeScript project with Supabase client configured
• For offline queue: browser environment with IndexedDB or server with Redis
• For dual-write: secondary data store (Redis, DynamoDB, or local SQLite)

Instructions

Step 1 — Circuit Breaker and Retry with Exponential Backoff

A circuit breaker tracks failures per Supabase service (database, auth, storage) and stops making calls when a threshold is exceeded. Combined with retry logic, it prevents both cascading failures and unnecessary retries during extended outages.

Circuit Breaker Implementation

// lib/circuit-breaker.ts
import { createClient } from '@supabase/supabase-js'
import type { Database } from './database.types'

type CircuitState = 'CLOSED' | 'OPEN' | 'HALF_OPEN'

interface CircuitBreakerOptions {
  failureThreshold: number    // failures before opening
  resetTimeoutMs: number      // ms before trying again (half-open)
  halfOpenSuccesses: number   // successes in half-open to close
  name: string                // for logging
}

class CircuitBreaker {
  private state: CircuitState = 'CLOSED'
  private failures = 0
  private lastFailureTime = 0
  private halfOpenSuccesses = 0

  constructor(private opts: CircuitBreakerOptions) {}

  async call<T>(fn: () => Promise<T>, fallback?: () => T): Promise<T> {
    // Check if circuit should transition from OPEN to HALF_OPEN
    if (this.state === 'OPEN') {
      if (Date.now() - this.lastFailureTime > this.opts.resetTimeoutMs) {
        this.state = 'HALF_OPEN'
        this.halfOpenSuccesses = 0
        console.log(`[CircuitBreaker:${this.opts.name}] OPEN → HALF_OPEN`)
      } else {
        if (fallback) return fallback()
        throw new Error(`Circuit breaker ${this.opts.name} is OPEN`)
      }
    }

    try {
      const result = await fn()

      // Success in HALF_OPEN: count toward recovery
      if (this.state === 'HALF_OPEN') {
        this.halfOpenSuccesses++
        if (this.half

Supabase Upgrade Migration

Overview

Upgrade @supabase/supabase-js and the Supabase CLI with breaking-change detection, automated code migration, and rollback planning. Covers the v1-to-v2 migration path (auth method renames, data/error destructuring, realtime API overhaul), minor version bumps, @supabase/ssr adoption, and Python SDK upgrades via pip install --upgrade supabase.

Current State

!npm list @supabase/supabase-js 2>/dev/null | grep supabase || echo 'supabase-js not installed'

!supabase --version 2>/dev/null || echo 'CLI not installed'

!pip show supabase 2>/dev/null | grep Version || echo 'Python SDK not installed'

Prerequisites

• @supabase/supabase-js or the Python supabase package installed in the project
• Git with a clean working tree (no uncommitted changes)
• Test suite available for post-upgrade verification
• Node.js >= 18 (for supabase-js v2) or Python >= 3.8 (for Python SDK)

Instructions

Step 1: Audit Versions, Scan Usage, and Review Breaking Changes

Check every installed Supabase package and find all import sites in the codebase.

# Check current SDK version
npm list @supabase/supabase-js

# Check CLI version
supabase --version

# Check Python SDK version
pip show supabase | grep Version

# Find all JS/TS Supabase imports
grep -rn "from '@supabase/supabase-js'" --include="*.ts" --include="*.tsx" --include="*.js" src/ lib/ app/ 2>/dev/null
grep -rn "createClient" --include="*.ts" --include="*.tsx" --include="*.js" src/ lib/ app/ 2>/dev/null

# Find all Python Supabase imports
grep -rn "from supabase" --include="*.py" src/ app/ 2>/dev/null

supabase-js v1 → v2 breaking changes:

CREATE EXTENSION IF NOT EXISTS pg_net WITH SCHEMA extensions;

CREATE OR REPLACE FUNCTION public.notify_order_created()
RETURNS trigger AS $$
BEGIN
  PERFORM net.http_post(
    url     := 'https://<project-ref>.supabase.co/functions/v1/on-order-created',
    headers := jsonb_build_object(