# Supabase

> Supabase CLI, Postgres performance, and schema patterns. Use for database operations, queries, RLS, and migrations.

- Skill: `djnsty23/supabase` (Agent Skill, multi-file: 12 files)
- Install (CLI): `npx skillmds@latest add djnsty23/supabase`
- Raw SKILL.md: https://api.skillmd.com/api/skills/djnsty23/supabase/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: djnsty23 (https://skillmd.com/u/djnsty23)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/djnsty23/supabase

---


# Supabase

Use CLI instead of MCP - more reliable, fewer permission issues.

## Common Commands

```bash
# Apply migrations (limit output)
supabase db push --project-ref PROJECT_ID 2>&1 | tail -10

# Run SQL directly
supabase db execute --sql "SELECT * FROM table LIMIT 5" --project-ref PROJECT_ID

# Deploy edge functions
supabase functions deploy FUNCTION_NAME --project-ref PROJECT_ID

# Deploy all functions
supabase functions deploy --project-ref PROJECT_ID

# List projects
supabase projects list

# Check status
supabase status --project-ref PROJECT_ID
```

## Context-Efficient Patterns

```bash
# Limit output to reduce context
supabase db push 2>&1 | tail -5

# Check if migration exists before applying
supabase db execute --sql "SELECT 1 FROM table LIMIT 1" 2>&1 | grep -q "1" && echo "exists"

# Run in background for long operations
Bash({ command: "supabase functions deploy --project-ref X", run_in_background: true })
```

## Project IDs

Get from CLAUDE.md or:
```bash
supabase projects list 2>&1 | grep -E "^\w"
```

## Multi-Org Auth

CLI only supports one token at a time. System env var may not match the current project — always check. A 401 means wrong token, do not retry.

```bash
# Option 1: Inline token (best for multi-org)
SUPABASE_ACCESS_TOKEN=$SUPABASE_TOKEN_REELR supabase db push --project-ref XXX

# Option 2: Source project's .env.local first
# .env.local is auto-loaded by session-start hook - no need to source
supabase db push --project-ref $SUPABASE_PROJECT_ID

# Option 3: Use --db-url with connection string (bypasses auth)
supabase db execute --db-url "postgresql://postgres:PASSWORD@db.XXX.supabase.co:5432/postgres" --sql "..."
```

**Project .env.local should have:**
```env
SUPABASE_ACCESS_TOKEN=sbp_xxx
SUPABASE_PROJECT_ID=xxx
SUPABASE_DB_PASSWORD=xxx
```

## Direct psql (Most Reliable)

**Use Pooler URL (IPv4 compatible), not direct connection:**
```bash
# Pooler - IPv4 compatible (use this)
psql "postgresql://postgres.REF:PASS@aws-0-REGION.pooler.supabase.com:6543/postgres" -c "SELECT 1"
```

Get pooler URL: Dashboard > Connect > Connection String > Session Pooler

---

## Postgres Performance

### Missing Indexes (Critical)
```sql
-- BAD: Full table scan
SELECT * FROM orders WHERE customer_id = 123;

-- GOOD: Add index
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
```

### N+1 Queries (Critical)
```sql
-- BAD: N+1 queries
SELECT * FROM orders WHERE id = 1;
SELECT * FROM customers WHERE id = (order.customer_id); -- repeated

-- GOOD: Single join
SELECT o.*, c.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.id = 1;
```

### RLS Performance (Critical)
```sql
-- BAD: Function call in RLS (slow)
CREATE POLICY "users" ON profiles
  USING (user_id = get_current_user_id());

-- GOOD: Use auth.uid() directly
CREATE POLICY "users" ON profiles
  USING (user_id = auth.uid());
```

### Connection Pooling
```
Supabase default: Transaction mode (pgbouncer)
- Use for serverless/edge functions
- Prepared statements require session mode
- Set pool size based on: max_connections / num_instances
```

### Foreign Key Indexes
```sql
-- Always index foreign keys!
ALTER TABLE orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
```

### Priority Reference

| Priority | Category | Impact |
|----------|----------|--------|
| 1 | Query Performance | High - Missing indexes, composite indexes |
| 2 | Connection Management | High - Pooling, limits, idle timeout |
| 3 | Security & RLS | High - RLS basics, RLS performance |
| 4 | Schema Design | High - Data types, PKs, FK indexes, partitioning |
| 5 | Concurrency & Locking | Medium-High - Short transactions, deadlock prevention |
| 6 | Data Access Patterns | Medium - N+1, pagination, batch inserts, upsert |

### Detailed References

| File | When to Load |
|------|--------------|
| `${CLAUDE_SKILL_DIR}/references/query-missing-indexes.md` | Query optimization |
| `${CLAUDE_SKILL_DIR}/references/conn-pooling.md` | Connection issues |
| `${CLAUDE_SKILL_DIR}/references/security-rls-performance.md` | Slow RLS policies |
| `${CLAUDE_SKILL_DIR}/references/security-rls-basics.md` | Setting up RLS |
| `${CLAUDE_SKILL_DIR}/references/data-n-plus-one.md` | Multiple query issues |
| `${CLAUDE_SKILL_DIR}/references/monitor-explain-analyze.md` | Query debugging |

---

## Schema & RLS Patterns

### Standard Table Template

```sql
CREATE TABLE IF NOT EXISTS public.[table_name] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Always enable RLS
ALTER TABLE public.[table_name] ENABLE ROW LEVEL SECURITY;

-- User owns row
CREATE POLICY "Users access own data"
  ON public.[table_name] FOR ALL
  USING (auth.uid() = user_id);
```

### Profiles Table (Standard)

```sql
CREATE TABLE public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT,
  full_name TEXT,
  avatar_url TEXT,
  role TEXT DEFAULT 'user',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Auto-create on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  INSERT INTO public.profiles (id, email)
  VALUES (NEW.id, NEW.email);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
```

### CLI Workflow

```bash
# View schema
npx supabase db dump --schema public | head -200

# Create migration
npx supabase migration new create_[table_name]

# Apply migration
npx supabase db push
```

### RLS Runtime Verification

After applying migrations, verify RLS actually works via REST API:
```bash
# Test as anonymous (should fail on protected tables)
curl -s 'https://REF.supabase.co/rest/v1/TABLE?select=*&limit=1' \
  -H 'apikey: ANON_KEY' \
  -H 'Authorization: Bearer ANON_KEY' | head -5

# Should return empty array or 401, NOT actual data
# If data returns, RLS policy is too permissive
```

### Migration Rollback Pattern

For every migration that changes schema, prepare a rollback:
```sql
-- Migration: add_column.sql
ALTER TABLE public.items ADD COLUMN status TEXT DEFAULT 'active';

-- Rollback (keep as comment or separate file):
-- ALTER TABLE public.items DROP COLUMN status;
```

For destructive changes, add columns as nullable with defaults first, migrate data, then drop old columns in a separate migration.

### Safety Rules

**Do:**
- Enable RLS on every table
- Use migrations for schema changes
- Include ON DELETE CASCADE for FKs
- Add created_at/updated_at columns
- Add new columns as nullable with defaults (never NOT NULL without default on existing tables)
- Verify RLS policy logic (not just enabled — check USING clauses are correct)
- Use service client only in server-side code, never expose to client

**Avoid:**
- Disable RLS in production
- Hardcode secrets in migrations
- Delete tables without confirmation
- `INSERT` policies with `WITH CHECK (true)` on sensitive tables
- `USING (true)` on tables containing PII

### Detailed Rules

| Rule | When to Load |
|------|--------------|
| `${CLAUDE_SKILL_DIR}/rules/rls-patterns.md` | RLS policy examples |
| `${CLAUDE_SKILL_DIR}/rules/security-patterns.md` | Security hardening |
| `${CLAUDE_SKILL_DIR}/rules/multi-account.md` | Multi-account CLI setup |

Source: [supabase/agent-skills](https://github.com/supabase/agent-skills)