Supabase Install & Auth

Overview

Install the Supabase SDK, CLI, and project credentials from scratch — covering package install, environment configuration, client initialization, and connection verification for both TypeScript (@supabase/supabase-js) and Python (supabase).

Key facts:

• npm package: @supabase/supabase-js
• Python package: supabase (via pip)
• Client factory: createClient() — never new SupabaseClient()
• Dashboard: https://supabase.com/dashboard (Settings > API for keys)
• Docs: https://supabase.com/docs

Prerequisites

• Node.js 18+ (for JS/TS) or Python 3.8+ (for Python)
• Package manager: npm, pnpm, or yarn (JS) / pip (Python)
• A Supabase project created at https://supabase.com/dashboard
• Docker Desktop (only if using local development via supabase start)

Instructions

Step 1 — Install the SDK and CLI

Install the SDK and the Supabase CLI:

JavaScript / TypeScript:

# Install the SDK
npm install @supabase/supabase-js

# For SSR frameworks (Next.js, SvelteKit, Nuxt), also install:
npm install @supabase/ssr

# Install the Supabase CLI (for types, migrations, local dev)
npm install -D supabase

Python:

# Install the SDK
pip install supabase

# Install the CLI (alternative: brew install supabase/tap/supabase)
npm install -g supabase

Verify the CLI is available:

npx supabase --version

Step 2 — Configure Environment Variables

Retrieve project credentials from the Supabase Dashboard (Settings > API) and create the env file:

# .env.local (or .env)
SUPABASE_URL=https://<project-ref>.supabase.co
SUPABASE_KEY=eyJhbGciOiJIUzI1NiIs...          # anon key — safe for client-side
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1...  # admin key — server-side ONLY

Add env files to .gitignore immediately:

.env
.env.local
.env.*.local

Security rules:

• The anon key (SUPABASE_KEY) is safe for client-side code. It respects Row Level Security (RLS) policies.
• The service role key (SUPABASESERVICEROLE_KEY) bypasses RLS entirely. Use only on the server. Never bundle into client code or expose in browser.

Step 3 — Initialize the Client and Verify

Create a client singleton and verify connectivity:

TypeScript — client-side (anon key):

// lib/supabase

Supabase Known Pitfalls

Overview

The twelve most common Supabase mistakes, ranked by severity: security (service_role exposure, missing RLS, permissive policies), data integrity (ignoring { data, error }, missing .select() after mutations, .single() on optional results), performance (select('*'), N+1 queries, missing FK indexes, synchronous auth checks), and maintainability (no generated types, multiple client instances, hardcoded connection strings). Each pitfall shows the broken code, explains why it fails, and provides the correct pattern using createClient from @supabase/supabase-js.

Prerequisites

• Access to a Supabase project codebase for review
• @supabase/supabase-js v2+ installed
• Basic understanding of Row Level Security (RLS)

Step 1 — Security Pitfalls (Critical)

These mistakes can expose all your data to any user with browser dev tools.

Pitfall 1: Exposing service_role Key in Client Code

// BAD: service_role key in a NEXT_PUBLIC_ variable — shipped to every browser
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_SERVICE_ROLE_KEY!  // CATASTROPHIC
)
// This key bypasses ALL RLS. Anyone can:
// - Read every row in every table
// - Delete the entire database
// - Create admin users
// - Access every file in storage

// CORRECT: anon key on client, service_role only on server
// Client (browser):
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!  // respects RLS
)

// Server only (API routes, server actions):
const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,  // NO NEXT_PUBLIC_ prefix
  { auth: { autoRefreshToken: false, persistSession: false } }
)

Detection:

# Find service_role references in client-side files
grep -rn 'SERVICE_ROLE' --include="*.tsx" --include="*.jsx" --include="*.ts" src/ app/ components/ pages/
# Find NEXT_PUBLIC_ + SERVICE_ROLE combination
grep -rn 'NEXT_PUBLIC.*SERVICE_ROLE' .env* *.ts *.tsx

Pitfall 2: Tables Without RLS Enabled

-- BAD: table created without enabling RLS
CREATE TABLE public.medical_records (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id uuid REFERENCES auth.users(id),
  diagnosis text,
  ssn text  -- PII fully exposed to anyone with the anon key!
);
-- With RLS disabled, the anon key can read EVERY row via the PostgREST API

-- CORRECT: always enable RLS immediately after CREATE TABLE
CREATE

Supabase Load & Scale

Overview

Supabase scaling operates at six layers: read replicas (offload analytics and reporting queries), connection pooling (Supavisor pgBouncer replacement with transaction/session modes), compute upgrades (vCPU/RAM tiers), CDN for Storage (cache public bucket assets at the edge), Edge Function regions (deploy functions closer to users), and table partitioning (split billion-row tables for query performance). This skill covers each layer with real createClient configuration, SQL, and CLI commands.

Prerequisites

• Supabase project on a Pro plan or higher (read replicas require Pro+)
• @supabase/supabase-js v2+ installed
• supabase CLI installed and linked to your project
• Database access via psql or Supabase SQL Editor
• TypeScript project with generated database types

Step 1 — Read Replicas and Connection Pooling

Read replicas let you route read-heavy queries (dashboards, reports, search) to replica databases while keeping writes on the primary. Supabase uses Supavisor (their pgBouncer replacement) for connection pooling with two modes: transaction (default, shares connections between requests) and session (holds a connection per client session, needed for prepared statements).

Configure the Read Replica Client

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

// Primary client — handles all writes and real-time subscriptions
export const supabase = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
)

// Read replica client — use for analytics, dashboards, search
// The read replica URL is available in Dashboard > Settings > Database
export const supabaseReadOnly = createClient<Database>(
  process.env.SUPABASE_READ_REPLICA_URL!,  // e.g., https://<project-ref>-ro.supabase.co
  process.env.SUPABASE_ANON_KEY!,          // same anon key works for replicas
  {
    db: { schema: 'public' },
    // Replicas may have slight lag (typically <100ms)
    // Do NOT use for reads-after-writes in the same request
  }
)

// Server-side admin client with connection pooling via Supavisor
// Use the pooled connection string (port 6543) instead of direct (port 5432)
export const supabaseAdmin = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  {
    auth: { autoRefreshToken: false, persistSession: false },
    db: { schema: 'public' },
  }
)

Direct Postgres Connections via Supavisor Pooling

# Transaction mode (default, port 6543) — b

Supabase Local Dev Loop

Overview

Run the full Supabase stack locally — Postgres, Auth, Storage, Realtime, Edge Functions, and Studio — using Docker and the Supabase CLI. Local development mirrors production APIs exactly, enabling offline work, fast iteration, and repeatable migration workflows. Schema changes flow through supabase db diff to generate migrations, and supabase db reset replays them cleanly.

Prerequisites

• Docker Desktop installed and running (required for all local services)
• Node.js 18+ (for npx supabase commands)
• No global install needed — all commands use npx supabase

Instructions

Step 1: Initialize Project and Start Local Stack

# Initialize Supabase in your project root
npx supabase init

This creates a supabase/ directory:

supabase/
├── config.toml          # Local stack configuration (ports, auth settings)
├── migrations/          # SQL migration files (version-controlled)
└── seed.sql             # Seed data (runs after migrations on db reset)

Start the local stack (first run pulls Docker images — takes a few minutes):

npx supabase start

The CLI prints all local endpoints and keys:

API URL:          http://localhost:54321
GraphQL URL:      http://localhost:54321/graphql/v1
S3 Storage URL:   http://localhost:54321/storage/v1/s3
DB URL:           postgresql://postgres:postgres@localhost:54322/postgres
Studio URL:       http://localhost:54323
Inbucket URL:     http://localhost:54324
anon key:         eyJhbGciOiJI...
service_role key: eyJhbGciOiJI...

Create .env.local from these values (git-ignored):

# .env.local
SUPABASE_URL=http://localhost:54321
SUPABASE_ANON_KEY=<anon-key-from-supabase-start>
SUPABASE_SERVICE_ROLE_KEY=<service-role-key-from-supabase-start>
DATABASE_URL=postgresql://postgres:postgres@localhost:54322/postgres

Verify the stack is running:

npx supabase status

Step 2: Create Migrations and Seed Data

Create a migration file with a descriptive name:

npx supabase migration new create_profiles
# Creates: supabase/migrations/<timestamp>_create_profiles.sql

Write the migration SQL:

-- supabase/migrations/<timestamp>_create_profiles.sql
create table public.profiles (
  id uuid references auth.users(id) primary key,
  username text unique not null,
  avatar_url text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Always enable RLS on public tables
alter table public.profiles enable row level security;

create policy "Public profiles are viewable 

Supabase Migration Deep Dive

Overview

Supabase migrations are SQL files managed by the CLI that track schema changes across environments. This skill covers the complete migration lifecycle: creating migrations with npx supabase migration new, writing zero-downtime schema changes that avoid table locks, backfilling data in batches, managing schema versioning across environments, planning rollback strategies, and regenerating TypeScript types after schema changes. Every pattern uses real Supabase 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

Use the Supabase CLI to create, test, and apply migrations. Each migration is a timestamped SQL file that runs in order.

Create a new migration:

# Create a migration file with a descriptive name
npx supabase migration new add_profiles_table
# Creates: supabase/migrations/20260322120000_add_profiles_table.sql

# List all migrations and their status
npx supabase migration list

# Check which migrations have been applied locally
npx supabase db reset --dry-run

Write the migration SQL:

-- supabase/migrations/20260322120000_add_profiles_table.sql

-- Create the profiles table
CREATE TABLE public.profiles (
  id uuid REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  email text UNIQUE NOT NULL,
  full_name text,
  avatar_url text,
  bio text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "users_read_own_profile" ON public.profiles
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "users_update_own_profile" ON public.profiles
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Create an index for email lookups
CREATE INDEX idx_profiles_email ON public.profiles(email);

-- Auto-create profile on user signup (trigger)
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name, avatar_url)
  VALUES (
    new.id,
    new.email,
    new.raw_user_meta_data ->> 'fu

Supabase Multi-Environment Setup

Overview

Production Supabase deployments require separate projects per environment — each with its own URL, API keys, database, and RLS policies. This skill configures a three-tier environment architecture (local dev, staging, production) with safe migration promotion via supabase db push, environment-aware createClient initialization, database branching for preview deployments, and CI/CD pipelines that prevent accidental cross-environment operations.

When to use: Setting up a new project with multiple environments, migrating from a single-project setup to multi-env, adding staging to an existing dev/prod split, or configuring preview environments with database branching.

Prerequisites

• Three separate Supabase projects created at supabase.com/dashboard (dev, staging, production)
• Supabase CLI installed: npm install -g supabase or npx supabase --version
• @supabase/supabase-js v2+ installed in your project
• Node.js 18+ with framework that supports .env files (Next.js, Nuxt, SvelteKit, etc.)
• A secret management solution for CI (GitHub Actions Secrets, Vercel env vars, etc.)

Instructions

Step 1: Environment Files and Project Layout

Create one Supabase CLI project with shared migrations and per-environment credential files. Each .env.* file points to a different Supabase project.

Project structure:

my-app/
├── supabase/
│   ├── config.toml              # Local CLI config
│   ├── migrations/              # Shared migrations (all envs use the same schema)
│   │   └── 20260101000000_initial.sql
│   ├── seed.sql                 # Dev-only seed data (runs on db reset only)
│   └── functions/               # Edge Functions (deployed per env)
├── .env.local                   # Local dev → supabase start
├── .env.staging                 # Staging project credentials
├── .env.production              # Production project credentials
└── .gitignore                   # Must include .env.staging, .env.production

Environment files:

# .env.local — local development (safe defaults from supabase start)
NEXT_PUBLIC_SUPABASE_URL=http://127.0.0.1:54321
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6InNlcnZpY2Vfcm9sZSIsImV4cCI6MTk4MzgxMjk5Nn0.EGIM96RAZx35lJzdJsyH-qQwv8Hdp7fsn3W0YpN81IU
DATABASE_URL=postgresql://postgres:postgres@127.0.0.1:54322/postgres
SUPABASE_ENV=local

# .env.staging — staging project
NEXT_PUBLIC_SUPABASE_URL=https:/

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.

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

Supabase Dashboard provides built-in reports under Dashboard > Reports:

For deeper Postgres diagnostics, use the CLI inspect commands:

# Table sizes — find the largest tables
npx supabase inspect db table-sizes --linked

# Index usage — find unused indexes wasting space
npx supabase inspect db index-usage --linked

# 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

# Long-running queries — find stuck queries
npx supabase inspect db long-running-queries --linked

# Table index sizes — compare index vs table size
npx supabase inspect db table-index-sizes --linked

# Bloat — estimate wasted space from dead tuples
npx supabase inspect db bloat --linked

# Blocking queries — find lock contention
npx supabase inspect db blocking --linked

# Replication slots — check replication health
npx supabase inspect db replication-slots --linked

Step 2: Query Analytics with pgstatstatements and Log Drains

Enable pgstatstatements for detailed query-level metrics:

-- Enable the extension (Dashboard > Database > Extensions, or SQL)
create extension if not exists pg_stat_statements;

-- Top 20 slowest queries by average execution time
select
  substring(query, 1, 80) as query_preview,
  calls,
  round(mean_exec_time::numeric, 2) as avg_ms,
  round(max_exec_time::numeric, 2) as max_ms,
  round(total_exec_time::numeric, 2) as total_m

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 Production Deployment Checklist

Overview

Actionable 14-step checklist for taking a Supabase project to production. Covers RLS enforcement, key separation, connection pooling (Supavisor), backups/PITR, network restrictions, custom domains, auth emails, rate limits, monitoring, Edge Functions, Storage policies, indexes, and migrations. Based on Supabase's official production guide.

Prerequisites

• Supabase project on Pro plan or higher (required for PITR, network restrictions)
• Separate production project (never share dev/prod)
• @supabase/supabase-js v2+ installed
• Supabase CLI installed (npx supabase --version)
• Domain and DNS configured for custom domain
• Deployment platform ready (Vercel, Netlify, Cloudflare, etc.)

Instructions

Step 1: Enforce Row Level Security on ALL Tables

RLS is the single most critical production requirement. Without it, any client with your anon key can read/write every row.

-- Audit: find tables WITHOUT RLS enabled
-- This query MUST return zero rows before going live
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = false;

-- Enable RLS on a table
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Create a basic read policy (authenticated users see own rows)
CREATE POLICY "Users can view own profile"
  ON public.profiles
  FOR SELECT
  USING (auth.uid() = user_id);

-- Create an insert policy
CREATE POLICY "Users can insert own profile"
  ON public.profiles
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Create an update policy
CREATE POLICY "Users can update own profile"
  ON public.profiles
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

• [ ] RLS enabled on every public table (zero rows from audit query above)
• [ ] SELECT, INSERT, UPDATE, DELETE policies defined for each table
• [ ] Policies tested with both authenticated and anonymous roles
• [ ] No tables use USING (true) without intent (public read tables only)

Step 2: Enforce Key Separation — Anon vs Service Role

The anon key is safe for client-side code. The service_role key bypasses RLS entirely and must never leave server-side environments.

// Client-side — ONLY use anon key
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!  // Safe for browsers
);

// Server-side only — service_role key (API routes, webhooks, cron jobs

Supabase Rate Limits

Overview

Supabase enforces rate limits and quotas across every API surface — PostgREST, Auth, Storage, Realtime, and Edge Functions. Limits scale by plan tier. This skill covers the exact numbers per tier, connection pooling via Supavisor, retry/backoff patterns, pagination to reduce payload, and dashboard monitoring so you can stay within quotas 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 — Understand Rate Limits by Tier and Surface

Every Supabase project has per-surface limits that differ by plan. Know these numbers before you architect:

API Request Limits

Auth Rate Limits

Auth limits are per-IP and per-user. Configure custom limits in Dashboard > Authentication > Rate Limits.

Storage Bandwidth

Realtime Connections

Edge Functions

Databa