Fitness tracker on Next.js 16 (App Router), Postgres (Neon) and Supabase Auth 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.

Supabase Auth: hosted identity (GoTrue) via the @supabase/ssr cookie client; email+password + OAuth.

Fitness tracker: user-owned workout plans, a shared exercise catalog, append-only workout logs, and per-set reps/weight rows.

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

user-owned training plans that group and label a series of workout sessions

shared, user-agnostic library of exercises keyed by unique name and optional muscle group

append-only workout log rows with child log_sets recording per-set reps and weight

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: Supabase owns identity in its managed auth.users. This stack emits a LOCAL `user` mirror (db/auth-schema.ts) so app-type schemas can foreign-key `user` directly — keep it in sync with a Supabase trigger on auth.users (insert/update → public.user). The drizzle migration only owns the mirror table's shape, not the trigger.

Sessions are cookie-based (@supabase/ssr): the proxy refreshes them on every request; Server Components read the user via supabase.auth.getUser().

exercises.name carries a unique constraint — the exercise catalog is shared across all users, so duplicate names are a schema error, not a soft collision.

workout_logs.plan_id is set null on plan deletion, not cascaded — historical logs are preserved even when the originating plan is removed.

Fitness tracker

Three of the four tables are user-scoped and one deliberately is not. workout_plans and workout_logs both foreign-key the auth fragment's user, and log_sets inherits that scoping through its parent log, but exercises is a shared catalog: no owner column, name carrying a unique constraint. A back squat is one row for the whole install, and every set anyone logs points at that same id, which is what makes cross-user aggregates possible at all. It is also the constraint you inherit — a private custom exercise needs a new column or a new table, because the catalog namespace is global.

log_sets is where the volume lives: one row per set, with set_number, reps, and a nullable weight_kg held as exact numeric rather than a float, so 2.5 kg plate increments accumulate without drift and a bodyweight set simply leaves the column null. Its exercise_id reference is the one FK in the schema with no ON DELETE clause, so the catalog is protected by default: the database refuses to delete an exercise any logged set still cites, and history cannot be hollowed out by a catalog cleanup. performed_at on workout_logs has no defaultNow, and that is the point — the column records when the session happened, not when the row was written, so entering Tuesday's workout on Thursday is the normal path rather than a correction.

idx_log_user_time on (user_id, performed_at) turns a month of training into a range scan already in date order. plan_id is nullable and ON DELETE SET NULL: an unplanned session is a valid log, and deleting a plan detaches its history instead of erasing it, which is the exact counterpart to user_id cascading and taking plans, logs and sets with it. The gap to plan around is per-exercise history. log_sets is indexed on log_id only, so the query behind a personal-record chart — every bench press this athlete has ever pressed — walks the user's logs through idx_log_user_time first and joins sets from there.

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.

Supabase Auth

Supabase Auth is a hosted service — GoTrue — that your app reaches over cookies rather than an SDK session object. Credentials and the canonical user records live in Supabase's managed auth.users schema. What lands in your own database is a mirror: db/auth-schema.ts declares user keyed by the Supabase auth uid (text; varchar(255) on MySQL) with email, full name, avatar URL and timestamps, so app-type schemas can foreign-key user exactly as they would under a self-hosted auth. Unlike the Clerk fragment, no sync webhook is emitted here, because Supabase's own trigger mechanism is the intended path: a trigger on auth.users writing into public.user lives in the Supabase project, not in the drizzle migration.

The migration owns the mirror's column shape and nothing else, so wiring that trigger is a step you take before those foreign keys mean anything. The mechanic that actually shapes this adapter is cookie refresh. @supabase/ssr rotates the auth token, and a rotated cookie only reaches the browser if something writes it onto the outgoing response — which is why every framework branch is built around the same getAll/setAll pair, wired to whatever that framework calls a cookie jar. Next's src/proxy.ts rebuilds the NextResponse inside setAll before calling getUser().

React Router has no middleware layer, so app/lib/supabase/server.ts constructs the client per request and returns { supabase, headers }, and the protected layout route attaches those headers to both exits — the redirect and the pass-through — so a refresh that happened during a guard is not lost. Nuxt's server/utils/supabase.ts binds the client to the h3 event and writes through setCookie, with a Nitro middleware doing the guard. Every decision point calls supabase.auth.getUser(), never getSession(): getSession reads whatever the cookie claims, getUser revalidates it against Supabase. Because a browser client is emitted alongside the server one, the auth screens are real forms calling supabase.auth.signInWithPassword rather than a hosted widget, and the sidebar's user menu subscribes to onAuthStateChange. Two consequences to plan around.

The anon key is public by design and is inlined into the client bundle under whichever prefix the framework demands (NEXT_PUBLIC_, VITE_, NUXT_PUBLIC_), so protection has to come from row-level security on Supabase's side, not from keeping the key quiet. And every guard is a network call to Supabase, not a local query — cheap, but not free, and on the path of every protected request.

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.