supabase-known-pitfalls

What this skill does

# 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

```typescript
// 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:**

```bash
# 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

```sql
-- 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 TABLE public.medical_records (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id uuid REFERENCES auth.users(id),
  diagnosis text,
  ssn text
);
ALTER TABLE public.medical_records ENABLE ROW LEVEL SECURITY;

-- Then add policies for legitimate access
CREATE POLICY "patients_read_own" ON public.medical_records
  FOR SELECT USING (patient_id = auth.uid());
```

**Detection:**

```sql
-- Find all tables without RLS (run in SQL Editor)
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'public'
AND rowsecurity = false
AND tablename NOT LIKE '\_%';
```

### Pitfall 3: Overly Permissive RLS Policies

```sql
-- BAD: lets any authenticated user read ALL messages
CREATE POLICY "anyone_can_read" ON public.messages
  FOR SELECT USING (auth.uid() IS NOT NULL);
-- Every logged-in user sees every other user's private messages

-- BAD: lets any authenticated user update ANY row
CREATE POLICY "anyone_can_update" ON public.profiles
  FOR UPDATE USING (auth.uid() IS NOT NULL);
-- Users can edit each other's profiles

-- CORRECT: scope to the user's own data
CREATE POLICY "read_own_messages" ON public.messages
  FOR SELECT USING (
    sender_id = auth.uid() OR recipient_id = auth.uid()
  );

CREATE POLICY "update_own_profile" ON public.profiles
  FOR UPDATE USING (id = auth.uid());
```

### Pitfall 4: Not Using Connection Pooling in Serverless

```typescript
// BAD: direct connection string in a serverless function
// Each Lambda/Edge invocation opens a new connection — exhausts pool in minutes
const connectionString = 'postgresql://postgres:[email protected]:5432/postgres'

// CORRECT: use the pooled connection string (Supavisor, port 6543)
const connectionString = 'postgresql://postgres.xxx:[email protected]:6543/postgres'
// Transaction mode: shares connections across requests
// Required for serverless (Vercel, Netlify, Cloudflare Workers, AWS Lambda)
```

## Step 2 — Data Integrity Pitfalls (High)

These mistakes cause silent data loss, null pointer errors, and inconsistent state.

### Pitfall 5: Not Handling { data, error }

```typescript
import { createClient } from '@supabase/supabase-js'
const supabase = createClient(url, key)

// BAD: destructuring only data — errors silently ignored
const { data } = await supabase.from('orders').insert(order).select().single()
console.log(data.id)  // TypeError: Cannot read property 'id' of null
// The insert failed (maybe RLS blocked it), data is null, error has the reason

// CORRECT: always check error before using data
const { data, error } = await supabase.from('orders').insert(order).select().single()
if (error) {
  console.error('Insert failed:', error.code, error.message, error.details)
  throw new Error(`Order creation failed: ${error.message}`)
}
// Now data is guaranteed to be non-null
console.log(data.id)
```

### Pitfall 6: Missing .select() After Insert/Update/Upsert

```typescript
import { createClient } from '@supabase/supabase-js'
const supabase = createClient(url, key)

// BAD: insert without .select() returns NO data
const { data } = await supabase.from('todos').insert({ title: 'Buy milk' })
console.log(data)  // null! Not the inserted row.
// Supabase mutations return null by default (like SQL INSERT without RETURNING)

// CORRECT: chain .select() to get the inserted/updated row back
const { data, error } = await supabase
  .from('todos')
  .insert({ title: 'Buy milk' })
  .select('id, title, is_complete, created_at')  // like SQL RETURNING
  .single()

if (error) throw new Error(`Insert failed: ${error.message}`)
console.log(data)  // { id: '...', title: 'Buy milk', is_complete: false, ... }
```

### Pitfall 7: .single() on Empty or Multiple Results

```typescript
import { createClient } from '@supabase/supabase-js'
const supabase = createClient(url, key)

// BAD: .single() throws PGRST116 when no rows match
const { data, error } = await supabase
  .from('profiles')
  .select('id, username, avatar_url')
  .eq('username', searchTerm)
  .single()
// error: { code: 'PGRST116', message: 'JSON object requested, multiple (or no) rows returned' }
// This is an ERROR, not just null — it breaks your flow

// BAD: .single() also throws when MULTIPLE rows match (PGRST200)

// CORRECT: use .maybeSingle() for 0-or-1 results
const { data, error } = await supabase
  .from('profiles')
  .select('id, username, avatar_url')
  .eq('username', searchTerm)
  .maybeSingle()
// data is null if no match (no error thrown)
// data is the row if exactly one match
// error only if 2+ rows match

// RULE OF THUMB:
// .single()      — use ONLY when you KNOW exactly 1 row exists (e.g., by primary key)
// .maybeSingle() — use when 0 or 1 rows might match (lookups by unique field)
// neither        — use when you expect an array of results
```

## Step 3 — Performance and Maintainability Pitfalls (Medium/Low)

### Pitfall 8: select('*') Everywhere

```typescript
import { createClient } from '@supabase/supabase-js'
const supabase = createClient(url, key)

// BAD: fetches ALL columns including large text/jsonb/bytea fields
const { data } = await supabase.from('posts').select('*')
// Problems:
// 1. Transfers unnecessary data (slower, more bandwidth)
// 2. May leak sensitive columns (SSN, internal notes, hashed passwords)
// 3. No TypeScript autocompletion — type is too broad
// 4. Cannot be