Marketplace on Next.js 16 (App Router), MySQL 8 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).

MySQL 8 via Drizzle ORM and the mysql2 driver.

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

Two-sided marketplace: seller profiles, a listings catalog, buyer orders with price capture, periodic seller payouts, and per-listing reviews.

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

Database client

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

// ponytail: single module-level pool; the runtime + mysql2's pool handle concurrency,
// so no globalThis singleton dance needed.
const pool = mysql.createPool(process.env.DATABASE_URL!);

export const db = drizzle({ client: pool });

one seller profile per Better Auth user, holding payout-provider account id and an approval status (pending/active/suspended)

seller-owned items with integer priceCents and a draft/active/sold/archived lifecycle

buyer-to-listing purchase records with amount captured at creation and a 6-state fulfillment status

net-amount remittance rows keyed by seller + text period, one per settlement window with a 4-state processing status

one review per reviewer per listing (unique constraint), with a CHECK enforcing rating between 1 and 5

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.

mysql2's pool multiplexes connections; drizzle-orm/mysql2 wraps it. One module-level pool is right for a serverless/edge app — the runtime and the pool handle concurrency.

MySQL has no row-level security: multi-tenant isolation is enforced in application code via the forOrg helper (src/lib/tenant.ts), not by the database. See the tenant-scoping section on SaaS pages.

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.

Order amountCents is captured at purchase time independently of the listing's priceCents — editing a listing price later cannot corrupt historical order records.

Payouts enforce a unique constraint on (sellerId, period), so each seller gets exactly one settlement row per billing window; duplicate payout jobs are rejected at the DB level.

MySQL provides no row-level security. On MySQL, multi-tenant isolation is APP-ENFORCED via the forOrg helper (src/lib/tenant.ts), not database-enforced like Postgres RLS. Every org-scoped query MUST go through forOrg — a missed query leaks across tenants. Postgres cells enforce this in the database itself (RLS), so it holds even for a query that forgets to scope.

Marketplace

A marketplace has two sides and a fee in the middle, and the tables split along exactly that line. sellers is the supply side: one profile per identity, enforced by sellers_user_unique on user_id, holding a display_name, an opaque payout_account_id (Stripe Connect, PayPal — nullable until onboarding finishes) and a status of pending, active or suspended. The demand side has no table of its own; buyers are Better Auth users referenced straight from marketplace_orders.buyer_id and reviews.reviewer_id. That asymmetry is the design: selling is an application somebody approves, buying is just having an account. listings hangs off seller_id with ON DELETE CASCADE and carries price_cents plus a draft → active → sold → archived status.

marketplace_orders references its listing with no delete rule at all, which is deliberate — the database refuses to remove a listing that has been bought, and because listings cascade from sellers, deleting a seller who ever sold anything fails loudly instead of shredding order history. The order's amount_cents is captured at purchase, so a seller editing their price afterwards moves the storefront and never the receipt, and its status walks six values (pending, paid, shipped, completed, refunded, canceled) because a two-sided order can end in a refund as easily as in delivery. payouts is the leg most schemas of this shape get wrong.

It stores the net remitted to a seller — bigint cents, gross minus your fee — for a text period such as '2026-06', under a unique constraint on (seller_id, period). That constraint is the safety rail: a payout job rerunning after a crash collides on insert instead of paying June twice, which is the failure nobody notices until the money is gone. Its own status column (scheduled, processing, paid, failed) with idx_payout_status gives you a queue to drain. reviews is the trust layer, and it is constrained rather than trusted: one row per (listing_id, reviewer_id), and reviews_rating_check pins rating between 1 and 5 in the database rather than in a form validator.

Reviews attach to listings, not to sellers, so seller reputation is an aggregate you compute across a seller's catalog — a join, not a column.

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)/.

MySQL 8

MySQL 8 here is the mysql2 driver under Drizzle's mysql-core dialect: drizzle({ client: pool }) over one module-level mysql.createPool(DATABASE_URL). mysql2's pool multiplexes connections itself, so a single pool per module is the right shape — the runtime and the pool handle concurrency, with no globalThis singleton needed. The framework decides where that file lands (src/lib/db.ts on Next, app/lib/db.ts on React Router, server/lib/db.ts on Nuxt); the client text is the same in all three. The dialect's constraints show up directly in the column types.

MySQL cannot index a TEXT column without a prefix length, so anything that is a primary key, a UNIQUE, an index or a CHECK target is varchar with a declared length: ids are varchar(36) with no database default — the application generates them with crypto.randomUUID(), since there is no uuid type and no defaultRandom() — Better Auth's user.id and every FK pointing at it are varchar(255), an email or slug is varchar(255), a role or status varchar(32), a SHA-256 hex digest varchar(64). Free-form columns nobody indexes stay text. Timestamps are plain timestamp().defaultNow() without the withTimezone flag the Postgres bodies carry, and counters are bigint({ mode: "number" }). The operational difference that matters most: MySQL has no row-level security.

There is no policy layer to fall back on, so multi-tenant isolation is enforced in application code by forOrg(db, orgId) in src/lib/tenant.ts, which wraps each org-owned table's select/update/delete with a where on organization_id and throws on a missing orgId instead of quietly running unscoped. It is a real boundary only while every read and write goes through it — the shared plans catalog sits deliberately outside — and it is app-enforced, not database-enforced. Connections change with the deploy target: connectionLimit 2 per short-lived serverless instance, 10 in a long-running Node process, and on Cloudflare Workers mysql2 cannot run at all — there are no TCP sockets — so the edge client swaps to @planetscale/database over HTTP with drizzle-orm/planetscale-serverless.

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.