SOPs

Supabase → Aurora Migration (Next.js + NextAuth)

When to Use This SOP

You have a Next.js app on Vercel using Supabase Cloud (database + auth + REST). You want to move to AWS Aurora Serverless v2 (scale-to-zero Postgres) and NextAuth v5 magic-link, eliminating the Supabase dependency entirely.

Reference implementation: ~/apps/james-ai-library (books.jameshurst.com). Full repo-specific runbook with exact code at ~/apps/james-ai-library/docs/aurora-migration-runbook.md.

Up-Front Decisions (lock these before starting)

Aurora region. Match the Vercel function region for the project. us-west-2 for iad1-default projects is fine; the latency hit from open-internet TLS is ~30ms either way.
Engine version. Match the Supabase source’s Postgres version exactly. Check it: psql "$SUPABASE_DSN" -c "SHOW server_version;". Mismatched versions cause pg_dump/pg_restore quirks that waste a day.
Serverless v2 sizing.
- Personal / low-traffic: min ACU 0, max ACU 1.0. Scale-to-zero. ~$0/month idle. 5–15s cold start on first request after idle.
- Production SaaS: min ACU 0.5, max ACU 2-4. No cold start, ~$45/month minimum. Set this if cold-start UX is unacceptable.
Auth: magic-link or keep passwords? Magic-link is simpler, more secure, fewer pages. Choose passwords only if you need account creation flow open to the public (whitelist sign-in needs a manual user-insert step).
Sessions: JWT or database? JWT. Database sessions break Vercel edge middleware (the pg adapter is Node-only). JWT cookies are verifiable on the edge.

Pre-Flight Checklist

Repo has a working npm run build against current Supabase setup
You have the Supabase project’s full connection string (Dashboard → Database → Connection String)
You have AWS credentials with RDS create/modify permissions
You have a Resend account with the sender domain verified (<domain> configured, not just <email>)
You have a feature branch ready: git checkout -b feat/aurora-migration
You can drop the old lib/db.ts (legacy Supabase wrapper) — if not, plan to rename it to lib/db-client.ts to avoid file-vs-directory shadow with lib/db/

Phase 0: AWS Infrastructure Setup

This is out-of-band (AWS console clicks, not git). Do it first so the cluster exists when you start wiring.

AWS Console → RDS → Create Database
- Engine: PostgreSQL
- Version: match Supabase exactly (see Up-Front Decisions #2)
- Template: Aurora PostgreSQL-Compatible
- Instance class: Serverless v2
- Min/Max ACU: per Up-Front Decisions #3
- Cluster ID: <project-slug>-cluster (e.g. books-jameshurst-com-cluster)
- Database name: <project_slug> (e.g. james_library)
- Publicly accessible: YES (security group handles ingress)
- Save the master password to ~/apps/cc/secrets/ via secrets_cli.py add immediately. Do not lose it.
EC2 → Security Groups → edit the auto-created SG
- Inbound: PostgreSQL (5432), source 0.0.0.0/0. TLS is required by client, so open-internet is fine for personal sites. Lock down via Vercel Secure Compute or VPC peering for production.

Build the DSN and stash it:

postgresql://<master_user>:<password>@<cluster-endpoint>:5432/<db>?sslmode=require

secrets_cli.py add <PROJECT>_AURORA_DSN '<dsn>'

Verify connectivity from your machine:
Terminal window
```
psql "<dsn>" -c "SELECT version();"
```
If this hangs: SG is wrong. If it errors on SSL: the client lib needs sslmode=require.

Phase 1: Schema Move

Dump the schema from Supabase (no data, no Supabase-internal objects):

pg_dump \
  --schema-only \
  --no-owner \
  --no-privileges \
  --no-tablespaces \
  --schema=public \
  "$SUPABASE_DSN" > schema.sql

Hand-clean schema.sql:
- Delete CREATE POLICY ... statements (Supabase RLS — Aurora does not need them; auth lives in the app).
- Delete ALTER TABLE ... ENABLE ROW LEVEL SECURITY statements.
- Delete SET search_path = public, auth, ... (Aurora has no auth schema).
- Delete any CREATE EXTENSION lines for extensions Aurora does not have (pgsodium, vault). Keep uuid-ossp, pgcrypto, pg_trgm — Aurora has these.
- Delete foreign keys that reference auth.users — they will not resolve in Aurora.

Add the NextAuth tables to schema.sql (the @auth/drizzle-adapter expects these exact column names):

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text,
  email text NOT NULL UNIQUE,
  "emailVerified" timestamp with time zone,
  image text
);

CREATE TABLE accounts (
  provider text NOT NULL,
  "providerAccountId" text NOT NULL,
  "userId" uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type text NOT NULL,
  refresh_token text, access_token text, expires_at integer,
  token_type text, scope text, id_token text, session_state text,
  PRIMARY KEY (provider, "providerAccountId")
);

CREATE TABLE sessions (
  "sessionToken" text PRIMARY KEY,
  "userId" uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires timestamp with time zone NOT NULL
);

CREATE TABLE "verificationTokens" (
  identifier text NOT NULL,
  token text NOT NULL,
  expires timestamp with time zone NOT NULL,
  PRIMARY KEY (identifier, token)
);

Quoted PascalCase column names are non-negotiable — the adapter generates SQL with them quoted.

Apply schema to Aurora:
Terminal window
```
psql "$AURORA_DSN" < schema.sql
```
Iterate on cleanup until it applies clean.

Phase 2: Data Move

Dump data only:

pg_dump \
  --data-only \
  --disable-triggers \
  --no-tablespaces \
  --no-privileges \
  --schema=public \
  "$SUPABASE_DSN" > data.sql

Strip SET statements that reference Supabase schemas:
Terminal window
```
sed -i '' '/^SET search_path/d' data.sql
```

Import into Aurora (disable FK during import, re-enable after):

psql "$AURORA_DSN" << 'SQL'
SET session_replication_role = replica;
\i data.sql
SET session_replication_role = DEFAULT;
SQL

Seed the NextAuth users table from your app’s profiles table (so foreign keys to user_id resolve when users sign in):
```
INSERT INTO users (id, email, name)
SELECT id, email, full_name FROM profiles
ON CONFLICT (email) DO NOTHING;
```

Verify row counts match Supabase:

for t in profiles books <other_tables>; do
  supa=$(psql "$SUPABASE_DSN" -tAc "SELECT COUNT(*) FROM $t")
  aur=$(psql "$AURORA_DSN" -tAc "SELECT COUNT(*) FROM $t")
  echo "$t: supabase=$supa aurora=$aur"
done

Phase 3: Application Code Changes

Two sub-phases. Ship Phase A to a Vercel preview branch first, verify auth, then start Phase B.

Phase A — Auth swap (no data layer changes yet)

Install deps:

npm install next-auth@beta @auth/drizzle-adapter drizzle-orm drizzle-kit pg resend
npm install --save-dev @types/pg

Create lib/db/index.ts (Drizzle client + pool):

import { drizzle } from "drizzle-orm/node-postgres"
import { Pool } from "pg"
import * as schema from "./schema"

const rawConnectionString = process.env.DATABASE_URL
  || "postgres://build:build@localhost:5432/build"
const connectionString = rawConnectionString
  .replace(/([?&])sslmode=[^&]*&?/g, "$1")
  .replace(/[?&]$/, "")

const pool = new Pool({
  connectionString,
  ssl: { rejectUnauthorized: false },
  max: 10,
})

export const db = drizzle(pool, { schema })
export * from "./schema"

The fallback DSN matters: Vercel’s build-time page-data collection runs without runtime env vars. Without the fallback, the build crashes.

Create lib/db/schema.ts with all your existing tables expressed in Drizzle, plus the NextAuth tables (users, accounts, sessions, verificationTokens).

Create auth.config.ts (edge-safe, no adapter, used by middleware):

import type { NextAuthConfig } from "next-auth"

export const authConfig = {
  pages: { signIn: "/auth/sign-in", error: "/auth/error" },
  callbacks: {
    authorized({ auth, request }) {
      const isLoggedIn = !!auth?.user
      const isPublic = ["/", "/some-public-route"].includes(request.nextUrl.pathname)
        || request.nextUrl.pathname.startsWith("/auth/")
      if (isPublic) return true
      return isLoggedIn
    },
  },
  providers: [],  // providers added in auth.ts
} satisfies NextAuthConfig

Create auth.ts (full config with adapter + providers; not edge-safe):

import NextAuth from "next-auth"
import Resend from "next-auth/providers/resend"
import { DrizzleAdapter } from "@auth/drizzle-adapter"
import { eq } from "drizzle-orm"
import { authConfig } from "@/auth.config"
import { db, users, accounts, sessions, verificationTokens, profiles } from "@/lib/db/index"

export const { handlers, signIn, signOut, auth } = NextAuth({
  ...authConfig,
  session: { strategy: "jwt" },  // CRITICAL: not "database"
  adapter: DrizzleAdapter(db, {
    usersTable: users,
    accountsTable: accounts,
    sessionsTable: sessions,
    verificationTokensTable: verificationTokens,
  }),
  providers: [
    Resend({
      from: "<App Name> <<sender>@<domain>>",
      apiKey: process.env.RESEND_API_KEY,
    }),
  ],
  callbacks: {
    ...authConfig.callbacks,
    async signIn({ user }) {
      const email = user.email?.toLowerCase()
      if (!email) return false
      const [profile] = await db
        .select({ id: profiles.id })
        .from(profiles)
        .where(eq(profiles.email, email))
        .limit(1)
      return Boolean(profile)
    },
    async jwt({ token, user, trigger }) {
      if (user?.id) token.id = user.id
      if (user?.id || trigger === "update") {
        const userId = (user?.id ?? token.id) as string | undefined
        if (userId) {
          const [profile] = await db.select().from(profiles)
            .where(eq(profiles.id, userId)).limit(1)
          if (profile) {
            token.role = profile.role
            token.full_name = profile.full_name
          }
        }
      }
      return token
    },
  },
})

Replace middleware.ts:

import NextAuth from "next-auth"
import { authConfig } from "@/auth.config"
export default NextAuth(authConfig).auth
export const config = { matcher: ["/((?!api|_next/static|_next/image|favicon.ico).*)"] }

Replace auth pages:
- app/auth/sign-in/page.tsx — magic-link form (single email field, calls signIn("resend", { email }))
- app/auth/verify-request/page.tsx — post-submit “check your email” landing
- app/auth/error/page.tsx — handles AccessDenied, Verification, Configuration
- Delete: app/auth/sign-up/, app/auth/reset-password/, app/auth/update-password/ (no passwords)
Strip the password form from app/profile/page.tsx.
Add types/next-auth.d.ts to augment Session.user with id, role, etc.
Set Vercel preview branch env vars (Settings → Environment Variables, scoped to the feature branch):
- DATABASE_URL — Aurora DSN
- AUTH_SECRET — openssl rand -base64 32
- AUTH_URL — the preview URL (or leave blank to let NextAuth derive)
- AUTH_TRUST_HOST=true
- RESEND_API_KEY
- Keep existing NEXT_PUBLIC_SUPABASE_* and SUPABASE_SERVICE_ROLE_KEY for now — Phase B routes still need them.
npm run build locally — must pass. Edge-runtime warnings about jose/CompressionStream are expected and harmless.
Push the branch, deploy to preview, test the magic-link end-to-end against Aurora users table. Do not move to Phase B until you have signed in successfully on the preview URL.

Phase B — Data layer swap

Rewrite every API route that calls supabase.from(...) to use Drizzle:

// BEFORE
const { data } = await supabase.from("books").select("*").order("created_at", { ascending: false })

// AFTER
const rows = await db.select().from(books).orderBy(desc(books.created_at))

List your routes first (grep -rl "supabase\.from" app/api/), check them off as you convert.

Rewrite lib/db.ts (if it exists as a Supabase REST wrapper) to call your new API routes server-side. Or, better, rename it to lib/db-client.ts to remove the file-vs-directory shadow with lib/db/. TypeScript resolves @/lib/db to the file first, which silently shadows your new directory.
Rewrite lib/server-logger.ts (or equivalent) to insert via Drizzle into app_logs (or your log table).
Rewrite admin pages that called getAuthClient() or similar Supabase admin helpers.
Delete lib/supabase.ts and npm uninstall @supabase/ssr @supabase/supabase-js.
Strip Supabase env vars from env.example and from Vercel (preview and production).
npm run build — must pass. Grep the repo: grep -r "supabase" --exclude-dir=node_modules --exclude-dir=.next . should return zero results.

Phase 4: Cutover

Re-sync Aurora data from Supabase — your initial dump is days/weeks old by now. Repeat Phase 2 step 1-4 to copy any new rows.
Switch production env vars on Vercel:
- DATABASE_URL → Aurora DSN
- AUTH_URL → production URL (e.g. https://<project>.com)
- Remove NEXT_PUBLIC_SUPABASE_*, SUPABASE_SERVICE_ROLE_KEY
Merge feat/aurora-migration → main. Vercel auto-deploys.
Smoke test production: sign in, verify protected routes, verify CRUD.
Watch Vercel function logs for 24 hours. Look for: connect ECONNREFUSED (Aurora SG misconfig), relation does not exist (missing table in Aurora), column ... does not exist (Drizzle schema mismatch).
After 1-2 weeks of stable production:
- Supabase Dashboard → Project Settings → Danger Zone → Delete Project
- Cancel the Supabase Pro subscription if you were on one

Common Gotchas (in priority order)

Edge middleware crashes with `pg` adapter

Symptom: Error: The edge runtime does not support Node.js 'pg' module. Cause: You used session: { strategy: "database" } (or omitted it — database is default with an adapter). Fix: Force session: { strategy: "jwt" } in auth.ts. Adapter still works for user/account storage; only sessions move to JWT.

File-vs-directory shadow on `@/lib/db`

Symptom: Module not found or unexpected Supabase methods on the db object even after you wrote lib/db/index.ts. Cause: lib/db.ts (the legacy Supabase wrapper) shadows lib/db/index.ts in TypeScript resolution. Fix: Rename lib/db.ts to lib/db-client.ts before creating the new directory, or import explicitly from @/lib/db/index until Phase B deletes the old file.

Vercel build crashes collecting page data with no `DATABASE_URL`

Symptom: Build fails at “Collecting page data” with a Postgres connection error. Cause: Vercel runs page-data collection at build time without runtime secrets. Fix: lib/db/index.ts falls back to a dummy DSN when DATABASE_URL is missing. Page data collection harmlessly fails its DB query and the build completes.

SSL certificate verification fails

Symptom: certificate verify failed. Cause: Node 22+ defaults to strict verify; Aurora cert chain not in Node’s trust store by default. Fix: ssl: { rejectUnauthorized: false } in the pg.Pool config. Safe because (a) Aurora cert is publicly signed, (b) DSN points to official RDS endpoint, (c) TLS is still required (no plaintext).

Connection pool exhaustion

Symptom: remaining connection slots are reserved for non-replication superuser connections. Cause: Too many Vercel functions holding open connections. Fix: Lower max in pg.Pool (e.g. 5). For higher traffic, switch to Aurora Data API or a connection pooler (RDS Proxy).

Foreign key violations during data import

Symptom: violates foreign key constraint "books_user_id_fkey". Cause: books.user_id references rows that do not exist yet in profiles. Fix: Use SET session_replication_role = replica; around the \i data.sql block (Phase 2 step 3). It is in the playbook for a reason — do not skip it.

Cold start UX

Symptom: First request after idle takes 5–15 seconds. Cause: Serverless v2 with min ACU 0 scaled to zero. Fix: Either accept it (fine for personal sites) or schedule a Vercel Cron to ping /api/diagnose every 5 minutes (eliminates scale-to-zero savings; pick one).

Symptom: User signs in, session.user.role is undefined. Cause: Email case mismatch between profiles.email and the users.email row NextAuth created. Fix: .toLowerCase() both sides of the comparison in the signIn callback. Normalize on insert too.

Verification Checklist (sign off before declaring done)

What This SOP Does Not Cover (yet)

Drizzle migration tooling. This playbook hand-writes SQL and applies via psql. drizzle-kit generate is configured but unused. Next iteration: codify the migration workflow.
AWS Secrets Manager → Vercel auto-sync. This playbook assumes manual env var entry. The Vercel × AWS integration exists; document it on the next run.
Blue-green cutover. This playbook does a hard cutover (Phase A on preview, then merge). For higher-stakes apps, consider running Aurora as a read-replica of Supabase via logical replication, then promoting.
CloudWatch alarms. None configured by default. Add CPUUtilization > 80% and DBLoad > <max ACU * 8> alarms for production apps.
RDS Proxy. For apps with high serverless concurrency (>50 simultaneous functions), introduce RDS Proxy to avoid connection exhaustion. Adds ~$15/month.

Reference Implementation

Repo: ~/apps/james-ai-library/
Full runbook with exact code: ~/apps/james-ai-library/docs/aurora-migration-runbook.md
Live CLAUDE.md (architecture reference): ~/apps/james-ai-library/CLAUDE.md
Cluster: books-jameshurst-com-cluster in us-west-2
Migration window: ~2 weeks across builds 327–349 (May 2026)