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

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

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)