Helpdesk / support on Next.js 16 (App Router), Postgres (Neon) and Auth.js (NextAuth) with Resend

Next.js 16 App Router: file-based routing, server components, and the Edge proxy (Next 16's renamed middleware).

Postgres on Neon via Drizzle ORM and the postgres-js driver.

Auth.js (NextAuth v5): self-hosted OAuth running inside your app against your database (Drizzle adapter).

Customer support desk: tickets with status/priority queues, threaded messages with internal notes, agent profiles, and seed-managed SLA policies.

Resend: transactional email (welcome/notification always; verify/reset when auth is self-hosted).

Database client

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

// Neon pooled endpoint = PgBouncer transaction mode → prepared statements off.
// ponytail: single module-level client; the serverless runtime + PgBouncer do
// the pooling, so no custom pool/globalThis singleton dance needed.
const client = postgres(process.env.DATABASE_URL!, { prepare: false });

export const db = drizzle({ client });

the core support request with open/pending/solved/closed status and low/normal/high/urgent priority, indexed for queue views

append-only thread rows on each ticket; is_internal hides agent-only notes from the requester

one agent profile per Better Auth user (unique on user_id), with an optional team field for queue segmentation

seed-managed response and resolution targets in integer minutes, keyed by priority tier

Auth runs in proxy.ts (Next 16's renamed middleware) on the Edge runtime: it gates on the session cookie's presence only — full session validation happens in Server Components and route handlers, not in the proxy.

prepare: false is mandatory — Neon's pooled endpoint is PgBouncer in transaction mode, where server-side prepared statements break across the pool.

Drizzle is paired here (not Prisma): Prisma's prepared-statement reliance is incompatible with transaction-mode pooling.

Self-hosted + OAuth-first: NextAuth owns the user/account/session/verificationToken tables (Auth.js Drizzle schema). This stack emits them (db/auth-schema.ts) and hands them to the Drizzle adapter, so app-type schemas can foreign-key `user` directly.

OAuth (GitHub) is wired by default — sign-in creates the account, so there is no separate email-signup step. Swap or add providers in src/lib/auth.ts.

Status and priority are text + CHECK (not pgEnum), so adding a new value (e.g. 'escalated') ships without an ALTER TYPE migration — consistent with the house style in packages/db/src/schema.ts.

agents carries a unique constraint on user_id (one profile per user) and sla_policies carries no unique on priority, allowing multiple named policies at the same priority tier for different customer tiers.

Helpdesk / support

A support desk is a queue with a conversation attached, and these four tables are arranged around exactly that. A ticket is a row in tickets: a requester_id pointing at Better Auth's user.id as text, a subject, and two graded columns. There is no separate customer entity — the person who opens a ticket and the person who answers it are rows in the same identity table, distinguished only by whether an agents row exists for them. status walks open, pending, solved, closed; priority runs low, normal, high, urgent. Both are text under named CHECK constraints (tickets_status_check, tickets_priority_check) rather than pgEnum, so introducing an 'escalated' state replaces a constraint instead of altering a live type. Two indexes carry the reads, and they point in opposite directions.

idx_ticket_status_priority on (status, priority) is the queue: filtering to open tickets uses the leading column, and ranking urgent work inside that band falls out of the same structure. idx_ticket_requester on requester_id is the customer's own history. ticket_messages holds the thread — body plus an is_internal boolean that separates a public reply from an agent-only note — indexed on (ticket_id, created_at) so a conversation loads oldest-first from one range, with is_internal applied as a filter over that small result rather than as its own index. agents is deliberately thin: one row per Better Auth user, an optional free-text team, and agents_user_unique on user_id holding it to one profile per person. Note what is absent. There is no assignee column on tickets and no ticket-to-agent join table.

Staffing here means who works the desk, not who owns which ticket; adding round-robin ownership means adding a column, and the queue index will not cover it. sla_policies is seed data rather than transactional rows: a name, a priority tier, and two integer minute budgets, first_response_mins and resolve_mins. Nothing foreign-keys it to a ticket, and no unique constrains priority, so two named policies can both sit at urgent for different customer tiers and your resolution rule decides which applies. Minutes as integers keep the deadline arithmetic exact — a target is created_at plus an interval, with no float rounding in the middle.

Next.js 16 (App Router)

Next 16's App Router keeps the whole application under src/: the scaffold runs create-next-app with --src-dir and --import-alias @/*, so `@/` resolves to src/* and the verified files land on top of a stock project rather than replacing it. initCode writes the database client to src/lib/db.ts, then appends the auth fragment's own files — identity tables at src/db/auth-schema.ts, the auth instance at src/lib/auth.ts, a catch-all handler under src/app/api/auth/, and src/proxy.ts. Each file has exactly one owner; the framework never re-emits the ones auth brought. Server code has two shapes here and they are not interchangeable. A Server Component runs on the server and imports { db } from "@/lib/db" directly, so a page can await a Drizzle query with no API route in between.

Anything that needs a URL — an OAuth callback, a Polar or Clerk webhook, a mutation posted from the client — is a route handler at src/app/api/<path>/route.ts exporting GET or POST. Session checking is split across two tiers on purpose. src/proxy.ts (Next 16's rename of middleware.ts; under --src-dir it must sit beside src/app or Next silently ignores it) runs on the Edge runtime with a config.matcher listing the guarded prefixes — /dashboard/:path* and /settings/:path* out of the box. It only asks whether a session cookie exists and redirects to /sign-in when it does not: no database round trip at the edge. The authoritative check is auth.api.getSession() inside the Server Component or route handler that actually reads rows.

What that means when you build on it: widening the protected surface is a one-line change to the matcher array, but the proxy is not the security boundary — a request carrying any session cookie reaches the page, and the page decides. Keep the real check next to the data. The UI overlay follows the same split, with auth screens under src/app/(auth)/ and the shell and dashboard under src/app/(app)/.

Postgres (Neon)

Postgres here is Neon reached through postgres-js, with Drizzle's pg-core dialect on top: drizzle({ client }) over a single module-level postgres(DATABASE_URL, { prepare: false }). That flag is not a preference. Neon's pooled (-pooler) endpoint is PgBouncer in transaction mode, where a backend is handed to a different session between statements, so server-side prepared statements break across the pool — and the same constraint is why this axis pairs with Drizzle rather than Prisma. One client per module is enough: PgBouncer and the runtime do the pooling, so there is no globalThis singleton dance. The schemas built on this dialect make three recurring type decisions. Primary keys are uuid(...).primaryKey().defaultRandom(), so ids come from the database. Timestamps are timestamp(..., { withTimezone: true }).defaultNow() — timestamptz, an absolute instant.

Closed value sets are text plus a CHECK constraint rather than pgEnum, so shipping a new role or subscription status is an ordinary constraint change instead of an ALTER TYPE migration. Counters are bigint({ mode: "number" }), and Better Auth's text user.id is referenced as text by the app tables rather than recast. Operationally, transaction-mode pooling forbids anything that spans statements on one backend: LISTEN/NOTIFY, session-scoped SET, advisory-lock sessions, WITH HOLD cursors. Those paths use Neon's direct endpoint instead. The connection client also changes with the deploy target — max: 1 per short-lived serverless instance, a real reused pool (max 10, idle_timeout 20) in a long-running Node process, and on Cloudflare Workers postgres-js is replaced outright by @neondatabase/serverless over HTTP, because Workers have no TCP sockets.

The capability that exists only on this side of the matrix is row-level security. Multi-tenant schemas ship ENABLE plus FORCE ROW LEVEL SECURITY with policies keyed on current_setting('app.current_org_id', true), which withTenant() sets per transaction — unset context yields no rows, so isolation fails closed inside the database rather than in application code. It requires a dedicated NOBYPASSRLS role: Neon's default neondb_owner carries BYPASSRLS, and connecting as it makes every policy silently inert.

Auth.js (NextAuth)

Auth.js v5 — still shipped as the next-auth package — runs inside your app and stores identity in your own database, but the shape it assumes is OAuth-first, and that decides most of what this cell looks like. No password is stored anywhere in the emitted schema. Sign-up and sign-in are the same act: the vendored screen is one "Continue with GitHub" button calling signIn("github", { redirectTo: "/dashboard" }), and the first successful callback is what creates the row. The identity tables are the canonical @auth/drizzle-adapter set, authored into db/auth-schema.ts as user, account, session and verificationToken. Their keys are worth reading before building on them.

account has no id of its own; its primary key is the composite (provider, providerAccountId), so one user row can hold several provider accounts, one per pair, each cascading from user.id. session is keyed by sessionToken and carries an expires timestamp — this is a database-session setup, not a JWT one, so signing out deletes a row and revocation is a DELETE you can run yourself. user.id is a text (varchar(255) on MySQL) primary key filled by $defaultFn(() => crypto.randomUUID()), and app-type schemas foreign-key it directly. src/lib/auth.ts hands all four tables to DrizzleAdapter explicitly through usersTable / accountsTable / sessionsTable / verificationTokensTable, so the adapter writes this schema rather than looking for its own default table names.

NextAuth's factory returns handlers, auth, signIn and signOut in a single destructure, and each of those lands somewhere specific in the emitted files. handlers is re-exported as GET and POST from src/app/api/auth/[...nextauth]/route.ts, which is where sign-in, callback, session and CSRF requests all terminate. auth is used twice at different strengths: wrapped as the proxy in src/proxy.ts, where a null req.auth redirects traffic off /dashboard and /settings, and called directly inside requireUser() in src/lib/session.ts for the real per-render check. <SessionProvider> in the root layout is what makes useSession() and signOut() work in the sidebar's nav-user. Two constraints follow. This adapter is pinned to Next through supportedFrameworks, because next-auth is Next-specific — React Router and Nuxt would need @auth/core instead.

And because GitHub already verified the address, there is no verify-email or reset-password flow here to wire: composing an email fragment gets you events.createUser calling sendWelcome once the adapter has persisted the user, and nothing more. Adding email or magic-link sign-in means adding a provider yourself, which changes the sign-in surface.

Resend

Resend covers two jobs: the transactional templates and the module that sends them. `emailTemplates` branches on `framework.slug` — Next.js and React Router get React Email components under `emails/` (`welcome.tsx`, `notification.tsx`, plus `verify-email.tsx` and `reset-password.tsx` when the auth is library-kind), Nuxt gets the same four as `.mjml` files. Both serializations read subject, preview, heading, body lines and CTA label from the shared `EMAIL_COPY` map, so the words are identical and only the markup differs. Every template closes with the `BRAND_ADDRESS` mailing address and an Unsubscribe link, and the React path sets `lang="en"`, a non-empty `<title>` and an explicit `<meta charSet="utf-8">`. `emailSend` emits `lib/email.ts`.

The provider client is built lazily inside `resendClient()` (`client ??= new Resend(process.env.RESEND_API_KEY)`) because `new Resend(undefined)` throws "Missing API key" — at module scope that throw fires at import time, which is exactly when a framework build imports this file to collect page data, so `next build` would fail on any machine holding only runtime secrets. `FROM` reads `EMAIL_FROM` and falls back to Resend's sandbox sender. On Next and React Router each sender awaits `render(WelcomeEmail(props))` from `@react-email/render` — async in the installed 2.1.0 — then calls `resendClient().emails.send({ from, to, subject, html })`.

On Nuxt the templates are inlined as string constants instead, `fill()` substitutes their `{{token}}` placeholders, `mjml2html(src, { validationLevel: "soft" })` compiles them and throws on any returned `errors`, and an ambient `mjml.d.ts` ships alongside because mjml@5 carries no type declarations. Which senders exist is gated on `auth.kind`. Hosted auth gets `sendWelcome` and `sendNotification` only. A library auth additionally gets `sendVerifyEmail` and `sendResetPassword` — the two functions its own config imports back out of this module to fill Better Auth's `emailAndPassword.sendResetPassword` and `emailVerification.sendVerificationEmail` hooks. A hosted provider sends those mails from its own infrastructure.