Supabase for Next.js SaaS

Core Insight: Two lanes: user-scoped data via Supabase client (RLS-enforced), server-scoped via Drizzle (privileged). Use getClaims() not getSession(). Transaction pooler requires prepare: false.

Table of Contents

• THE EXACT PROMPT
• Architecture
• Quick Start
• Environment Variables
• Workflow Checklist
• Critical Gotchas
• Quick Commands
• AGENTS.md Blurb
• References

---

THE EXACT PROMPT

Set up Supabase for a Next.js SaaS with:
1. Auth: Google OAuth only
2. ORM: Drizzle with Supabase pooler (IPv4)
3. Environment: [Vercel / Cloudflare / local dev]
4. Cost tier: [Free / Pro with Spend Cap / Team]

Follow supabase-guide skill patterns.

---

Architecture

┌─────────────────────────────────────────────────────────────────────┐
│                     TWO-LANE DATA ACCESS MODEL                       │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│   LANE A: User-scoped (RLS-enforced)                                │
│   ══════════════════════════════════                                │
│   @supabase/ssr → PostgREST Data API                                │
│   • Browser + Server Components                                     │
│   • JWT validated via getClaims()                                   │
│   • Multi-tenant reads/writes obey RLS "naturally"                  │
│                                                                     │
│   LANE B: Server-scoped (privileged)                                │
│   ══════════════════════════════════                                │
│   Drizzle → postgres.js → Supavisor pooler                          │
│   • Migrations, admin tasks, billing webhooks                       │
│   • Transaction mode (port 6543): prepare: false                    │
│   • Session mode (port 5432): migrations only                       │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

Why this split: Browser access uses Data API with RLS. Server/serverless uses pooler. IPv4-only environments (Vercel/GH Actions) cannot use direct connections (IPv6).

---

Quick Start

1. Drizzle + Pooler (Transaction Mode)

// src/db/index.ts
import postgres from "postgres"
import { drizzle } from "drizzle-orm/postgres-js"
import * as schema from "./schema"

const g = globalThis as unknown as { __sql?: ReturnType<typeof postgres> }

export const sql = g.__sql ?? postgres(process.env.DATABASE_URL!, {
  prepare: false,  // REQUIRED for transaction pooler
  max: process.env.NODE_ENV === "production" ? 5 : 1,
})

if (process.env.NODE_ENV !== "production") g.__sql = sql

export const db = drizzle(sql, { schema })

2. Supabase SSR Clients

// src/lib/supabase/client.ts
import { createBrowserClient } from "@supabase/ssr"

export function supabaseBrowser() {
  return createBrowserClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!
  )
}

// src/lib/supabase/server.ts
import { createServerClient } from "@supabase/ssr"
import { cookies } from "next/headers"

export function supabaseServer() {
  const store = cookies()
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!,
    {
      cookies: {
        getAll: () => store.getAll(),
        setAll: (toSet) => {
          try { toSet.forEach(({ name, value, options }) => store.set(name, value, options)) }
          catch {}
        },
      },
    }
  )
}

3. Middleware (Token Refresh)

// middleware.ts
import { createServerClient } from "@supabase/ssr"
import { NextResponse, type NextRequest } from "next/server"

export async function middleware(request: NextRequest) {
  let response = NextResponse.next({ request: { headers: request.headers } })

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!,
    {
      cookies: {
        getAll: () => request.cookies.getAll(),
        setAll: (toSet) => {
          toSet.forEach(({ name, value }) => request.cookies.set(name, value))
          response = NextResponse.next({ request: { headers: request.headers } })
          toSet.forEach(({ name, value, options }) => response.cookies.set(name, value, options))
        },
      },
    }
  )

  await supabase.auth.getClaims()  // NOT getSession()
  return response
}

export const config = {
  matcher: ["/((?!_next/static|_next/image|favicon.ico|.*\\.(?:svg|png|jpg|jpeg|gif|webp)$).*)"],
}

---

Environment Variables

# Supabase Auth / API
NEXT_PUBLIC_SUPABASE_URL="https://<project_ref>.supabase.co"
NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY="sb_publishable_..."  # or anon key during transition
SUPABASE_SERVICE_ROLE_KEY="..."  # server-only, NEVER ship to client

# Postgres: app runtime (transaction pooler, port 6543)
DATABASE_URL="postgresql://postgres.<project_ref>:<PASSWORD>@aws-0-<region>.pooler.supabase.com:6543/postgres?sslmode=require"

# Postgres: migrations (session pooler, port 5432)
DATABASE_URL_MIGRATIONS="postgresql://postgres.<project_ref>:<PASSWORD>@aws-0-<region>.pooler.supabase.com:5432/postgres?sslmode=require"

Username format: postgres.<project_ref> — the dot is real, copy from Dashboard → Connect button.

---

Workflow Checklist

Phase 1: Project Setup

• Create prod + staging Supabase projects (blast-radius isolation)
• Copy connection strings from Connect button (not guessed)
• Set env vars: NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY, DATABASE_URL, DATABASE_URL_MIGRATIONS
• Enable Spend Cap (Pro plan) — COST-OPTIMIZATION.md

Phase 2: Auth (Google-only)

• Enable Google provider in Dashboard → Auth → Providers
• Disable email/password, magic links, phone
• Add redirect URI in Google Cloud Console: https://<project_ref>.supabase.co/auth/v1/callback
• Set up SSR clients + middleware — AUTH-SSR.md
• Create OAuth callback route — GOOGLE-OAUTH.md

Phase 3: Database + Drizzle

• Install: bun add drizzle-orm postgres && bun add -d drizzle-kit
• Create src/db/index.ts with prepare: false — DRIZZLE-SETUP.md
• Create drizzle.config.ts pointing to session pooler for migrations
• Run drizzle-kit generate and drizzle-kit migrate

Phase 4: RLS + Data Model

• Create user_profiles table keyed by auth.users.id
• Enable RLS on ALL tables in public schema
• Create trigger to auto-create profile on signup
• Index columns used in RLS policies — RLS-PATTERNS.md

Phase 5: Cost Optimization

• Use transaction pooler for runtime, session for migrations
• Harden Data API: move sensitive tables to private schema
• Set up cron rollups for event-heavy tables
• Configure Smart CDN + cache-control for Storage — COST-OPTIMIZATION.md

---

Critical Gotchas

Symptom	Cause	Fix
ENETUNREACH / network unreachable	Direct connection is IPv6	Use pooler connection string
prepared statement does not exist	Transaction pooler + prepared statements	Set prepare: false in postgres.js
Tenant or user not found	Wrong pooler host or username format	Copy exact string from Connect dialog; include .project_ref in username
Users randomly logged out	Token not refreshing in middleware	Use getClaims() in middleware, not getSession()
RLS policy not blocking	Table missing enable row level security	Run ALTER TABLE x ENABLE ROW LEVEL SECURITY
Slow queries with RLS	No index on RLS filter columns	Add index on user_id, tenant_id columns
Surprise bills	No Spend Cap, uncached egress	Enable Spend Cap; use Smart CDN + high cache-control

Full troubleshooting: TROUBLESHOOTING.md

---

Security Rules

1. Use getClaims(), not getSession() — cookies can be spoofed; getClaims() validates JWT signature every time
2. Never expose service_role key — bypasses RLS, backend-only
3. Enable RLS on every table in public — tables without RLS are accessible via anon role
4. Use app_metadata not user_metadata for tenant_id — user_metadata is user-modifiable

---

Quick Commands

# Supabase CLI
supabase login
supabase link --project-ref $PROJECT_REF
supabase db pull                        # Pull remote schema
supabase migration new <name>           # Create migration
supabase db push                        # Apply migrations

# Drizzle
drizzle-kit generate                    # Generate migrations
drizzle-kit migrate                     # Apply migrations
drizzle-kit studio                      # Visual browser

# Secrets (Edge Functions)
supabase secrets set --env-file .env    # Push secrets
supabase secrets list                   # Verify

---

AGENTS.md Blurb

Copy this to your project's AGENTS.md:

### Supabase

Supabase CLI is installed. Project: `<PROJECT_REF>`.

Auth:

\`\`\`bash
supabase login
supabase link --project-ref $PROJECT_REF
\`\`\`

Database:

\`\`\`bash
supabase db pull           # Pull schema to migrations
supabase migration new <name>
supabase db push           # Apply migrations
\`\`\`

Connection strings:
- **Runtime (Drizzle):** Transaction pooler (port 6543), `prepare: false`
- **Migrations:** Session pooler (port 5432)

Copy exact connection string from Dashboard → Connect button.

---

References

Topic	Reference
Copy-paste patterns	QUICK-REFERENCE.md
Two-lane architecture + cost model	ARCHITECTURE.md
Drizzle + pooler setup	DRIZZLE-SETUP.md
Next.js SSR auth with getClaims()	AUTH-SSR.md
Google OAuth setup	GOOGLE-OAUTH.md
RLS patterns for SaaS	RLS-PATTERNS.md
Pooler gotchas (IPv4/IPv6, modes)	POOLER-GOTCHAS.md
Cost optimization (Spend Cap, cron)	COST-OPTIMIZATION.md
Troubleshooting guide	TROUBLESHOOTING.md

---

Validation

# After setup, verify:
# 1. Google OAuth flow works (login → callback → dashboard)
# 2. Drizzle can connect: bun run -e "import { db } from './src/db'; console.log('OK')"
# 3. RLS blocks unauthorized access via Data API
# 4. Middleware refreshes tokens (check cookies in DevTools)