Supabase Patterns and Antipatterns

Last Updated: 2026-01-23

Lessons learned from AI Archetypes, ATH Ops, Selene P2C, Say It Now, and other projects.

Project Setup & CLI

Creating a New Supabase Project

CRITICAL: Region Selection

When creating a new project, always use us-east-1 (N. Virginia) unless you have a specific reason for another region.

Why this matters:

• Region cannot be changed after project creation
• Wrong region = recreate entire project and migrate data
• us-east-1 has the best latency for US-based apps and is most commonly supported

Dashboard Setup Checklist:

1. Create project at database.new
2. Select organization (or create one)
3. Region: US East (N. Virginia) ← Don't skip this
4. Set a strong database password (save it!)
5. Wait for project to provision (~2 minutes)

Supabase CLI Setup

First-time setup (per machine):

# Login to Supabase (opens browser) - use npx, no global install needed
npx supabase login

Per-project setup:

# Initialize Supabase in your project
npx supabase init

# Link to your remote project (get project-ref from dashboard URL)
# URL format: https://supabase.com/dashboard/project/<project-ref>
npx supabase link --project-ref <project-ref>

# You'll be prompted for database password

Common CLI Gotchas:

Local Development

# Start local Supabase (requires Docker)
npx supabase start

# Stop local Supabase
npx supabase stop

# Reset local database (destructive!)
npx supabase db reset

Local URLs (default):

• Studio: http://localhost:54323
• API: http://localhost:54321
• DB: postgresql://postgres:postgres@localhost:54322/postgres

Production Database Connection (CRITICAL)

Problem: "Tenant or user not found" error when connecting via pooler.

Root Cause: The pooler hostname varies by project. It is NOT always aws-0-us-east-1. Your project might be on aws-1, aws-2, or a different server entirely.

# WRONG - assuming aws-0
postgresql://postgres.PROJECT_REF:[email protected]:6543/postgres

# RIGHT - get exact hostname from dashboard
postgresql://postgres.PROJECT_REF:[email protected]:6543/postgres
                                           ^^^
                                           This varies by project!

Always get the connection string from: Supabase Dashboard → Settings → Database → Connection string

This project's pooler: aws-1-us-east-1.pooler.supabase.com (saved in .env as SUPABASE_PROD_POOLER_URL)

Ports:

• 6543 = Transaction pooler (serverless, short-lived connections)
• 5432 = Session pooler (migrations, long-lived connections)

Direct connection note: db.PROJECT_REF.supabase.co often resolves to IPv6 only. If your network doesn't support IPv6, you must use the pooler.

Reference: Say It Now (2026-01-23) - lost 1+ hour before discovering aws-1 vs aws-0.

Quick Reference

Commands

# Link to project (one-time)
npx supabase link --project-ref <project-id>

# Push migrations to remote
npx supabase db push

# Check migration status
npx supabase migration list

# Create new migration
npx supabase migration new <description>

# Dump schema (useful for debugging)
npx supabase db dump --schema public

# Generate TypeScript types
npx supabase gen types typescript --project-id <project-id> > src/lib/database.types.ts

The Golden Rule of RLS

For anonymous access, ALWAYS include TO public:

-- WRONG (only works for authenticated users)
CREATE POLICY "anyone_insert" ON my_table
    FOR INSERT WITH CHECK (true);

-- CORRECT (works for anonymous users)
CREATE POLICY "anyone_insert" ON my_table
    FOR INSERT
    TO public
    WITH CHECK (true);

Row-Level Security (RLS)

RLS Role Reference

Pattern: Explicit TO Clause for Anonymous Access

Problem: RLS policies without a TO clause default to authenticated users only. This silently breaks anonymous access.

-- ANTIPATTERN: Missing TO clause - only works for authenticated users
CREATE POLICY "public_select" ON table FOR SELECT USING (true);

-- PATTERN: Explicit TO public - allows anonymous access
CREATE POLICY "public_select" ON table FOR SELECT TO public USING (true);

-- PATTERN: Explicit TO authenticated - clear intent
CREATE POLICY "auth_select" ON table FOR SELECT TO authenticated USING (...);

Pattern: Use auth.uid() Not Email for User Identification

Problem: Using auth.jwt() ->> 'email' requires an extra join and is less performant.

-- ANTIPATTERN: Email-based lookup (requires join, slower)
CREATE POLICY "users_select" ON data
    FOR SELECT USING (
        user_id IN (SELECT id FROM users WHERE email = auth.jwt() ->> 'email')
    );

-- PATTERN: auth_id foreign key (direct UUID comparison, faster)
CREATE POLICY "users_select" ON data
    FOR SELECT USING (auth_id = auth.uid());

Pattern: Helper Functions with SECURITY DEFINER

Problem: Complex RLS checks duplicated across policies.

-- PATTERN: Centralized helper functions
CREATE OR REPLACE FUNCTION is_superadmin()
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM users
        WHERE auth_id = auth.uid()
        AND role = 'superadmin'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Then use in policies
CREATE POLICY "superadmins_all" ON table FOR ALL
    TO authenticated
    USING (is_superadmin());

Note: SECURITY DEFINER means the function runs with the privileges of the function owner, bypassing RLS for the inner query.

Antipattern: Forgetting to Enable RLS

-- Tables are public by default until you enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- IMPORTANT: Service role key bypasses RLS entirely
-- Never expose service role key to client-side code

Pattern: Need Both INSERT and UPDATE Policies

If you INSERT a record and later UPDATE it, you need both policies:

CREATE POLICY "insert_policy" ON table FOR INSERT TO public WITH CHECK (true);
CREATE POLICY "update_policy" ON table FOR UPDATE TO public USING (true) WITH CHECK (true);

Antipattern: Using USING Instead of WITH CHECK for INSERT

-- WRONG: USING is for SELECT/UPDATE/DELETE (read check)
CREATE POLICY "insert" ON table FOR INSERT USING (true);

-- CORRECT: WITH CHECK is for INSERT/UPDATE (write check)
CREATE POLICY "insert" ON table FOR INSERT WITH CHECK (true);

API Key Management

Pattern: Environment Variables for Keys

// PATTERN: Environment variable with fallback for dev
const supabaseUrl = import.meta.env.PUBLIC_SUPABASE_URL;
const supabaseKey = import.meta.env.PUBLIC_SUPABASE_ANON_KEY;

// With explicit fallback (use sparingly)
const supabaseUrl = import.meta.env.PUBLIC_SUPABASE_URL || 'https://xxx.supabase.co';

Antipattern: Hardcoded Keys in Client Code

// ANTIPATTERN: Hardcoded key (will break on rotation)
const supabaseKey = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...';

Symptoms of Invalid API Key

• "Invalid API key" errors
• Anonymous/public routes fail
• "The organization access point was not found"

Fix: Check the .env file or Supabase dashboard for current keys.

Auth Patterns

Pattern: Configure Redirect URLs in Dashboard (CRITICAL)

Problem: Magic link / OAuth redirects to localhost instead of production site.

Cause: Supabase Site URL defaults to http://localhost:3000. This must be changed to your production URL or auth redirects will fail.

CRITICAL: This is a required configuration step for any new Supabase project with authentication.

Fix in Supabase Dashboard:

1. Go to: Authentication > URL Configuration
2. Set Site URL: https://your-domain.com
3. Add Redirect URLs:
   • https://your-domain.com/**
   • https://www.your-domain.com/**
   • http://localhost:3000/** (for local dev)
   • http://localhost:4321/** (Astro default port)

In Code: Always use production URL for redirects, even when testing locally:

// Always redirect to production site, not localhost
const redirectUrl = window.location.hostname === 'localhost'
  ? 'https://your-domain.com/dashboard'
  : window.location.origin + '/dashboard';

await supabase.auth.signInWithOtp({
  email,
  options: { emailRedirectTo: redirectUrl }
});

Reference: ath-ops auth redirect issue (2026-01-11)

Pattern: Link Users Table to auth.users

-- PATTERN: Foreign key to auth.users for proper linking
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    auth_id UUID UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE,
    email TEXT UNIQUE NOT NULL,
    -- application-specific fields
    role TEXT NOT NULL DEFAULT 'user'
);

Pattern: Automatic Profile Creation via Trigger

-- Create user profile automatically on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO users (auth_id, email, name)
    VALUES (NEW.id, NEW.email, NEW.raw_user_meta_data->>'name');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

Pattern: Session Check

const { data: { session } } = await supabase.auth.getSession();
if (!session) {
  window.location.href = '/auth/login';
  return;
}

Common Query Patterns

Query Through Parent Tables (FK Relationships)

Problem: Child table's organization_id may be NULL. Query via parent table.

// WRONG - organization_id may be NULL
const { data } = await supabase
  .from('quiz_results')
  .select('*')
  .eq('organization_id', orgId);

// CORRECT - go through parent table
const { data: sessions } = await supabase
  .from('quiz_sessions')
  .select('*, quiz_results(*)')
  .eq('org_id', orgId)
  .not('completed_at', 'is', null)
  .order('completed_at', { ascending: false });

Separate Queries When No Direct FK

When two tables both reference auth.users but don't have a direct FK to each other:

// WRONG - no direct FK relationship
const { data } = await supabase
  .from('org_members')
  .select('*, profiles(email, full_name)')  // 400 error!
  .eq('org_id', orgId);

// CORRECT - fetch separately and map
const { data: members } = await supabase
  .from('org_members')
  .select('*')
  .eq('org_id', orgId);

const userIds = members.map(m => m.user_id);
const { data: profiles } = await supabase
  .from('profiles')
  .select('id, email, full_name')
  .in('id', userIds);

const profileMap = new Map(profiles?.map(p => [p.id, p]) || []);
members.forEach(m => {
  m.profile = profileMap.get(m.user_id);
});

Fetch with Counts (Admin Dashboard)

const { data: orgs } = await supabase
  .from('organizations')
  .select(`
    *,
    org_members(count),
    quiz_sessions(count)
  `)
  .order('created_at', { ascending: false });

// Access counts
orgs.map(org => ({
  memberCount: org.org_members?.[0]?.count || 0,
  responseCount: org.quiz_sessions?.[0]?.count || 0
}));

Migration Patterns

Pattern: Idempotent Migrations

-- PATTERN: Use IF NOT EXISTS / IF EXISTS for safety
CREATE TABLE IF NOT EXISTS users (...);
DROP POLICY IF EXISTS "old_policy" ON users;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Pattern: Separate Schema and RLS Migrations

migrations/
├── 001_core_schema.sql      # Tables only
├── 002_rls_policies.sql     # RLS policies
├── 003_seed_data.sql        # Initial data

This makes it easier to fix RLS issues without recreating tables.

Migration File Naming

supabase/migrations/YYYYMMDDHHMMSS_description.sql

Example: 20260113000001_fix_anonymous_insert_policies.sql

Debugging RLS

Check What Policies Exist

SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE schemaname = 'public';

Test as Anonymous User

In Supabase SQL Editor, run:

-- Become anonymous
SET request.jwt.claims TO '{}';
SET ROLE anon;

-- Try your query
SELECT * FROM your_table;

-- Reset
RESET ROLE;

Test as Authenticated User

-- Simulate a specific user
SET request.jwt.claims TO '{"sub": "user-uuid-here", "email": "[email protected]"}';
SET ROLE authenticated;

-- Try your query
SELECT * FROM your_table;

-- Reset
RESET ROLE;

Test with curl (Most Reliable)

ANON_KEY="<your-anon-key>"
BASE_URL="https://<project-id>.supabase.co/rest/v1"

# Test SELECT
curl -s "$BASE_URL/table_name?select=*" \
  -H "apikey: $ANON_KEY"

# Test INSERT
curl -X POST "$BASE_URL/table_name" \
  -H "apikey: $ANON_KEY" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"field": "value"}'

Crisis Recovery

API Key Rotated

Symptoms:

• "Invalid API key" errors
• All Supabase operations fail
• Quiz returns 401

Fix:

1. Go to Supabase Dashboard → Settings → API
2. Copy new anon/public key
3. Update env vars and fallbacks
4. Rebuild and deploy

Quiz Results Not Saving (No Error)

Symptoms:

• Quiz completes but no new rows in database
• No error shown to user

Checklist:

1. Check RLS INSERT policy has TO public
2. Check application saves after core steps (not just at end)
3. Test INSERT with curl
4. Check browser console for JS errors
5. Verify parent record exists first (FK constraint)

Organization Lookup Fails

Symptoms:

• "The organization access point was not found"

Fix:

CREATE POLICY "public_select_orgs" ON organizations
    FOR SELECT TO public USING (true);

Common Error Messages

Key Lessons

1. Always use TO public for anonymous access - The #1 RLS mistake
2. Save data early, update later - Don't wait until end of flow
3. Query via parent tables - Child foreign keys may be NULL
4. No auto-join without direct FK - Fetch separately and map
5. Test anonymous operations with curl - Don't trust the browser
6. Check column names carefully - org_id vs organization_id
7. Filter system orgs in admin views - Avoid displaying test/default orgs
8. Configure Site URL immediately - Auth redirects break without it

References

• AI Archetypes Full Runbook
• Supabase Official Docs
• Supabase RLS Docs