E-commerce store on Next.js 16 (App Router), Postgres (Neon) and Clerk

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.

Clerk: hosted identity (sign-in UI, sessions, user management) mounted via middleware + provider.

E-commerce storefront: product catalog with per-SKU variants, guest-compatible carts, and price-snapshotting orders.

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

// ponytail: Clerk is hosted — set NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY and
// CLERK_SECRET_KEY in the env. The publishable key is read client-side by
// <ClerkProvider>; the secret key is read server-side by clerkMiddleware().
// Both are picked up from the environment automatically — no wiring needed.

products (display unit) and product_variants (buyable SKUs carrying price_cents and inventory_qty)

carts (nullable user_id for guest shoppers) and cart_items (one row per variant per cart, quantity-bumped on re-add)

orders (captured totalCents + status walk) and order_items (frozen sku + unitPriceCents snapshot, variantId set-null on delete)

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.

Hosted: Clerk owns identity and does NOT create a local `user` table. Store `clerk_user_id` as text without a foreign key, or sync Clerk users into a local table via webhook before relying on FKs to `user`.

Route protection is clerkMiddleware() + auth.protect() in the Next proxy (getAuth() in a React Router loader, or event.context.auth() on Nuxt) — logged-out users bounce to Clerk's hosted sign-in, so there are no self-hosted auth pages to build or maintain.

Keeping the local mirror in sync is a webhook job: a svix-verified webhook route replays user.created / user.updated / user.deleted idempotently into the local `user` row, so app-type foreign keys to `user` resolve even though Clerk is the source of truth.

ClerkProvider (client) wraps the app so the hosted <SignIn/> / <UserButton/> components and hooks work; the publishable key is read client-side, while the secret key is only ever read server-side by clerkMiddleware.

Money is stored as integer cents on product_variants (price_cents) and snapshotted onto order_items (unit_price_cents) at checkout — repricing or deleting a variant never rewrites order history.

cart_items carries a composite unique on (cart_id, variant_id) so the app bumps quantity rather than inserting duplicate rows; orders.status is a text + CHECK column (pending/paid/shipped/cancelled) to avoid ALTER TYPE migrations.

Clerk is a hosted identity provider and does not create a local `user` table. This schema's foreign keys to `user` assume a local identity table (as Better Auth provides). With Clerk, store `clerk_user_id` as a text column without a foreign key, or sync Clerk users into a local `users` table via webhook before relying on these FKs.

E-commerce store

Price and stock do not live on a product here. products holds the display unit — a unique slug (indexed as idx_product_slug), a name, a description — while product_variants holds what a shopper can actually buy: a unique sku, price_cents, inventory_qty, one row per SKU under its product. Everything downstream points at a variant rather than a product, which is why 'Large / Black is sold out but Medium is not' is representable without a nullable stock column or a JSON blob. Carts are two tables and one constraint.

carts.user_id is text (matching Better Auth's user.id) and nullable, so a guest can fill a basket before an account exists to attach it to; claiming that basket at login is a single UPDATE on carts, because cart_items hangs off cart_id and not off the shopper. cart_items then carries the composite unique (cart_id, variant_id), which turns 'add to cart' into an upsert that bumps quantity instead of an insert that quietly leaves the same SKU on two checkout lines. Orders exist in order to stop being the catalog. order_items does not read price through its variant FK: it stores sku and unit_price_cents copied at checkout, and variant_id is nullable with ON DELETE SET NULL.

Reprice a variant, rename a SKU, discontinue a line — last quarter's orders still total what the customer was charged, and the FK is a convenience for 'show me this product's sales' rather than the source of the money. orders.total_cents is the captured total; provider_payment_id is an opaque Stripe-or-whoever string, deliberately not a foreign key into a payments table this schema does not own; orders.user_id is ON DELETE SET NULL so a closed account does not erase its own sales history. All money is integer cents, never numeric or float, and orders.status is text under orders_status_check (pending, paid, shipped, cancelled) with idx_order_status behind it.

What the schema does not do is reserve inventory: inventory_qty is a plain column with no hold, no reservation table and nothing stopping it going negative, so overselling under concurrency is your checkout transaction's problem.

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.

Clerk

Clerk keeps identity on its own servers. The emitted app has no auth instance, no password column and no session table: the card at /sign-in is Clerk's <SignIn/> component rendered under a [[...sign-in]] catch-all so Clerk can mount its own verification and SSO-callback sub-routes there, and the endpoints behind it belong to Clerk. What does land in your database is a single mirror table. db/auth-schema.ts declares user with id set to the Clerk user id (text; varchar(255) on MySQL, so the app-type FK columns match exactly), plus email, first and last name, image URL, and an updated_at column used purely as a staleness key. It exists so app-type schemas can foreign-key user the way they would under a self-hosted auth.

It is not a source of truth, and application code should never write to it. Filling that mirror is a webhook job, and this fragment emits the whole path. lib/identity/record.ts holds recordClerkEvent; the mount is a Next route handler at src/app/api/webhooks/clerk/route.ts, an action in app/routes/webhooks.clerk.ts on React Router, or a Nitro .post.ts handler on Nuxt. Clerk delivers through svix, so the route verifies the raw body against CLERK_WEBHOOK_SECRET before anything reaches the database, and the record core is written for a delivery channel that retries and reorders: the staleness comparison lives inside the UPDATE's WHERE clause so an older event cannot clobber newer state, the insert path absorbs a concurrent duplicate (onConflictDoNothing on Postgres, an ER_DUP_ENTRY catch on MySQL), and user.deleted removes the row. Session checks never touch your Postgres.

Next's proxy.ts runs clerkMiddleware() and calls auth.protect() for anything matching createRouteMatcher(["/dashboard(.*)", "/settings(.*)"]); Nuxt reads event.context.auth() inside a Nitro middleware that the @clerk/nuxt module populates. Even the package name is framework-specific — @clerk/nextjs, @clerk/react-router, @clerk/nuxt — which upstreamPkgFor resolves per cell. The trade is concrete. You never build, style or maintain auth screens, and breaking changes arrive with Clerk's releases rather than your lockfile. In exchange, your user rows are eventually consistent with someone else's database, and a webhook you never configured is a table of missing foreign-key targets that only shows up when an app-type insert fails.