Newsletter platform on Next.js 16 (App Router), Postgres (Neon) and Better Auth

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.

Better Auth: self-hosted auth running inside your app against your Postgres (Drizzle adapter).

Audience-scoped newsletter platform: subscribers with deliverability status, named lists, list membership, campaigns scheduled against a list, and per-subscriber send tracking.

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 });

email addresses collected by an owner, with a status CHECK walking subscribed/unsubscribed/bounced

named audience segments and the join table that places a subscriber on a list at most once

broadcasts targeting a list, with a status CHECK gating draft/scheduled/sent and a nullable scheduledAt timestamp

one append-only row per (campaign, subscriber) tracking the queued→delivered→opened→bounced lifecycle

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: Better Auth owns the user/session/account/verification tables. This stack emits them (db/auth-schema.ts) and hands them to the Drizzle adapter, so app-type schemas can foreign-key `user` directly.

The (ownerId, email) unique constraint on subscribers prevents the same address appearing twice on one owner's audience — deduplication is enforced at the DB level, not application code.

campaign_sends holds one row per (campaign, subscriber) fan-out, each walking queued → delivered → opened → bounced via a text CHECK, making delivery/open rollups a straight aggregate over the idx_send_campaign index rather than a mutable counter. Note there is NO unique on (campaign_id, subscriber_id) — only idx_send_campaign — so re-running a fan-out inserts a second row for the same subscriber; deduplication belongs to the enqueue path, not the database.

Newsletter platform

Every row in this schema traces back to one owner_id. subscribers.owner_id and lists.owner_id are text FKs straight to Better Auth's user.id — no organization or workspace table sits in between — so an audience belongs to a single account, and a team-shared list means adding a tenant layer rather than adjusting a column. idx_subscriber_owner and idx_list_owner are what keep those owner-scoped reads cheap. Ownership also decides the constraint that matters most on import. subscribers_owner_email_unique covers (owner_id, email), not email alone: the same address can sit on two operators' audiences, and a CSV import dedupes with an upsert on that pair. An address is not a global key here, which is right for a multi-tenant product and wrong the moment code assumes otherwise.

Nothing enforces that a subscriber and the list they join share an owner either — list_subscriptions references lists.id and subscribers.id, not a common owner — so cross-owner membership is prevented in the handler, not the database. Membership and delivery are both join-shaped and constrained differently. list_subscriptions carries list_subscriptions_list_subscriber_unique on (list_id, subscriber_id), placing a subscriber on a list at most once; the leading list_id turns a recipient set into a prefix scan, and idx_list_subscription_subscriber runs it the other way for a preference page listing every list one address is on. A campaign points at the list it targets through campaigns.list_id, indexed by idx_campaign_list, and walks 'draft' to 'scheduled' to 'sent' under campaigns_status_check with scheduled_at set for the middle state.

campaign_sends has no matching unique — its only index is idx_send_campaign — so a re-run of a fan-out will insert a second row for the same recipient, and idempotency on the enqueue side is yours to write. The send row doubles as the metrics table. campaigns holds no counters, so delivery and open numbers are a group by status over campaign_sends, whose CHECK pins the vocabulary to 'queued', 'delivered', 'opened' and 'bounced'. That row is written when queued (there is no created_at, only a nullable sent_at filled in later) and updated in place. Because campaign_sends.subscriber_id cascades on delete, hard-deleting a subscriber rewrites the history of every campaign they received; flipping subscribers.status to 'unsubscribed' is the move that keeps the numbers intact.

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.

Better Auth

Better Auth is a TypeScript library, not a service. The process that serves your pages is the process that hashes passwords and issues sessions, and the session rows sit in the same database as your application data. This fragment authors the four tables Better Auth expects — user, session, account, verification — into db/auth-schema.ts and passes that module to drizzleAdapter(db, { provider, schema: authSchema }) inside lib/auth.ts. Better Auth never creates tables at runtime; its CLI normally generates them, and authoring them here means the identity schema goes through the same migration proof as the app-type tables.

user.id is a bare text primary key (varchar(255) on MySQL, which cannot index TEXT without a prefix length) carrying no database default, because Better Auth generates the id and sends it in the insert. That is precisely what lets an app-type schema foreign-key user.id and cascade on delete. Session validation happens at two different strengths, deliberately. Next's src/proxy.ts calls getSessionCookie(request), which only asks whether the cookie is present: it runs at the edge, touches no database, and exists to bounce logged-out traffic before render. The authoritative check is auth.api.getSession({ headers }), and it runs inside the protected surface — requireUser() in src/lib/session.ts on Next, requireAuth(request) in app/lib/require-auth.ts on React Router, and the Nitro handler at server/middleware/auth.ts on Nuxt.

The last two do the real lookup on every guarded request, since neither framework has an edge proxy to peek with. The remaining emitted files are the mount: toNextJsHandler(auth) behind a [...all] route on Next, a resource route delegating to auth.handler on React Router, an h3 catch-all wrapping toWebRequest on Nuxt, plus a Vue auth client there. The auth instance imports the db client the framework already exported, so both share one pooled connection. What you inherit is ownership. Sessions join to your own tables, a user delete is a foreign-key cascade rather than a sync job, and drift arrives through your lockfile instead of a vendor's release notes.

The same ownership is the cost: password recovery only delivers if an email fragment is composed in — Better Auth's own server returns 400 "Reset password isn't enabled" until emailAndPassword.sendResetPassword is set — and rotating BETTER_AUTH_SECRET is yours to schedule.