Implement Authentication for a Next.js SaaS

-- migrations/001_auth.sql — Auth tables
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(200) NOT NULL,
  password_hash VARCHAR(255),              -- null for OAuth-only users
  avatar_url TEXT,
  role VARCHAR(20) DEFAULT 'member',       -- admin, member, viewer
  email_verified BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE oauth_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  provider VARCHAR(50) NOT NULL,           -- google, github
  provider_account_id VARCHAR(255) NOT NULL,
  UNIQUE(provider, provider_account_id)
);

CREATE TABLE refresh_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  token_hash VARCHAR(255) NOT NULL,        -- hashed, not plaintext
  expires_at TIMESTAMPTZ NOT NULL,
  revoked BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

// lib/tokens.ts — JWT access tokens + DB-backed refresh tokens
import { SignJWT, jwtVerify } from 'jose'
import crypto from 'crypto'
import { db } from './db'

const secret = new TextEncoder().encode(process.env.JWT_SECRET)

export async function createTokenPair(user: { id: string; role: string }) {
  // Access token: 15 min, stateless
  const accessToken = await new SignJWT({
    sub: user.id,
    role: user.role,
  })
    .setProtectedHeader({ alg: 'HS256' })
    .setIssuedAt()
    .setExpirationTime('15m')
    .sign(secret)

  // Refresh token: 30 days, stored in DB (revocable)
  const refreshToken = crypto.randomBytes(32).toString('hex')
  const tokenHash = crypto.createHash('sha256').update(refreshToken).digest('hex')

  await db.refreshTokens.create({
    userId: user.id,
    tokenHash,
    expiresAt: new Date(Date.now() + 30 * 86400000),    // 30 days
  })

  return { accessToken, refreshToken }
}

export async function verifyAccessToken(token: string) {
  const { payload } = await jwtVerify(token, secret)
  return { userId: payload.sub as string, role: payload.role as string }
}

export async function rotateRefreshToken(oldToken: string) {
  const hash = crypto.createHash('sha256').update(oldToken).digest('hex')
  const stored = await db.refreshTokens.findByHash(hash)

  if (!stored || stored.revoked || stored.expiresAt < new Date()) {
    // If token was already used (revoked), revoke ALL tokens for this user
    // This detects refresh token theft
    if (stored?.revoked) {
      await db.refreshTokens.revokeAllForUser(stored.userId)
    }
    throw new Error('Invalid refresh token')
  }

  // Revoke the old token
  await db.refreshTokens.revoke(stored.id)

  // Issue new pair
  const user = await db.users.findById(stored.userId)
  return createTokenPair(user)
}

// app/api/auth/google/route.ts — Start OAuth flow
export async function GET() {
  const params = new URLSearchParams({
    client_id: process.env.GOOGLE_CLIENT_ID!,
    redirect_uri: `${process.env.APP_URL}/api/auth/google/callback`,
    response_type: 'code',
    scope: 'openid email profile',
    access_type: 'offline',
    prompt: 'consent',
  })

  return Response.redirect(`https://accounts.google.com/o/oauth2/v2/auth?${params}`)
}

// app/api/auth/google/callback/route.ts — Handle callback
export async function GET(req: Request) {
  const { searchParams } = new URL(req.url)
  const code = searchParams.get('code')

  // Exchange code for tokens
  const tokenRes = await fetch('https://oauth2.googleapis.com/token', {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body: new URLSearchParams({
      code: code!,
      client_id: process.env.GOOGLE_CLIENT_ID!,
      client_secret: process.env.GOOGLE_CLIENT_SECRET!,
      redirect_uri: `${process.env.APP_URL}/api/auth/google/callback`,
      grant_type: 'authorization_code',
    }),
  })
  const tokens = await tokenRes.json()

  // Get user info
  const userInfo = await fetch('https://www.googleapis.com/oauth2/v2/userinfo', {
    headers: { Authorization: `Bearer ${tokens.access_token}` },
  }).then(r => r.json())

  // Find or create user
  let user = await db.users.findByOAuth('google', userInfo.id)
  if (!user) {
    user = await db.users.create({
      email: userInfo.email,
      name: userInfo.name,
      avatarUrl: userInfo.picture,
      emailVerified: true,
    })
    await db.oauthAccounts.create({
      userId: user.id,
      provider: 'google',
      providerAccountId: userInfo.id,
    })
  }

  // Create session + tokens
  const { accessToken, refreshToken } = await createTokenPair(user)
  const session = await getSession()
  session.userId = user.id
  session.role = user.role
  session.isLoggedIn = true
  await session.save()

  // Set refresh token as httpOnly cookie
  const response = Response.redirect(`${process.env.APP_URL}/dashboard`)
  response.headers.set('Set-Cookie',
    `refresh_token=${refreshToken}; HttpOnly; Secure; SameSite=Lax; Path=/; Max-Age=${30 * 86400}`)

  return response
}

// middleware/api-auth.ts — Protect API routes
import { verifyAccessToken } from '@/lib/tokens'

export async function withAuth(req: Request) {
  const authHeader = req.headers.get('authorization')
  if (!authHeader?.startsWith('Bearer ')) {
    return Response.json({ error: 'Unauthorized' }, { status: 401 })
  }

  try {
    return await verifyAccessToken(authHeader.slice(7))
  } catch (err) {
    return Response.json(
      { error: 'Token expired', code: 'TOKEN_EXPIRED' },
      { status: 401 }
    )
  }
}

// Usage in API route
export async function GET(req: Request) {
  const auth = await withAuth(req)
  if (auth instanceof Response) return auth    // error response

  const documents = await db.documents.findByUser(auth.userId)
  return Response.json(documents)
}