Fintech ledger on Next.js 16 (App Router), MySQL 8 and Supabase Auth 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.

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

Double-entry fintech ledger: chart of accounts, immutable journal entries, and debit/credit lines with integer-cent amounts.

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

per-owner accounts typed to one of five standard categories (asset/liability/equity/revenue/expense)

immutable header rows tying a description and posted timestamp to a creator

the individual debit/credit lines linking each journal entry to an account with an integer-cent amount

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.

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

The accounts table constrains type to the five canonical categories (asset/liability/equity/revenue/expense) via a DB CHECK — adding a new account class requires a migration, not just app code.

Debit/credit balance (sum of debits == sum of credits per entry) is enforced in application logic, not at the DB level; the schema's CHECK only guards direction values ('debit'/'credit'), so the invariant can be violated if bypassed.

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.

Fintech ledger

No table here has a balance column, and that absence is the design. An account's balance is a sum over journal_lines — grouped by direction, one side netted against the other — served by idx_line_account on account_id. Nothing is cached, so nothing can quietly fall out of agreement with the postings that produced it; the price is that every balance read is an aggregate, and a busy account grows a longer scan every month it stays open. accounts is the chart: a name, a currency defaulting to USD, and a type held to the five canonical classes by accounts_type_check — asset, liability, equity, revenue, expense. A sixth account class is a migration, not a config change.

journal_entries is the header, carrying a description, a creator_id into Better Auth's user, and posted_at behind idx_entry_posted. Notice what that index is not: it keys on time alone with no owner in it, so closing a period reads the whole book's window cheaply while a single owner's statement takes the other route entirely — accounts via idx_account_owner, then lines via idx_line_account. journal_lines is where the money sits. Each line points at an entry and an account, both indexed by idx_line_entry and idx_line_account, and carries a direction held to debit or credit by journal_lines_direction_check plus an amount_cents bigint. Integers, never floats — and the sign is carried by direction rather than by the number, though nothing constrains amount_cents to be positive, so that convention is yours to hold.

The invariant that defines double-entry, debits equalling credits within an entry, has no representation in this schema at all: no trigger, no deferred constraint, no CHECK spanning rows. Both sides go in one transaction and the application asserts the sum before commit, because a half-written entry is a perfectly valid row set as far as the database is concerned. Currency compounds that — it lives on accounts and not on lines, so an entry touching a USD account and a EUR account has nowhere to record a rate, and a single-currency book is the assumed case. Deletes cascade from user through accounts and entries, taking every line with them: this is a live ledger, not an archive.

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.

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.