Product analytics on React Router v8, MySQL 8 and Better Auth

React Router v8 (framework mode): SSR, config/file routes under app/, loaders/actions, and resource routes for API endpoints.

MySQL 8 via Drizzle ORM and the mysql2 driver.

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

Product-analytics schema: tracked apps with unique ingest keys, an append-only event stream, session windows, and saved funnels, all hung off Better Auth's user table.

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

owner-scoped app registrations, each with a unique api_key used to authenticate inbound event writes

the raw, immutable event stream keyed by app and timestamp, with freeform JSON properties and an optional anonymous visitor id

visitor session windows (start/end timestamps) correlated to events via anonymous_id, with nullable ended_at for open sessions

saved multi-step funnel definitions stored as JSON steps arrays, scoped per tracked app

Framework mode (not data/library mode): routes live under app/, declared in app/routes.ts. API endpoints are resource routes (a route module exporting loader/action but no default component).

Data flows through loaders (run on the server before render) and actions (mutations); components read it with useLoaderData / useActionData. There are no React Server Components — every server-rendered route is a loader plus a client component.

Auth gates in the loader, not in middleware: a protected route's loader calls requireAuth(request), which throws a redirect Response that React Router short-circuits on — so a logged-out user never reaches the protected data or renders the page.

The `@/` import alias maps to app/ (this framework's source root), so shared modules like @/lib/auth resolve under app/ — the one path prefix that differs from Next's src/, which is why the auth slice's mount code is framework-specific.

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: 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.

api_key on tracked_apps carries a unique constraint: each app gets exactly one ingest key, and the key is the authentication surface for the event ingestion endpoint.

events is append-only (no update path, no status column) — the composite index on (app_id, occurred_at) is the only query handle; roll-up queries must scan that index, not mutate rows.

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.

Product analytics

Ingest is the constraint everything else bends around. A write arrives carrying an api_key, and that column on tracked_apps is UNIQUE so the lookup is a single index probe returning an app_id — no join to identity on the write path, even though tracked_apps.owner_id foreign-keys Better Auth's user table for the dashboard side. Every other table hangs off that app id: events, sessions and funnels all reference tracked_apps.id with ON DELETE cascade, which makes an app registration the unit of scoping and the unit of deletion at once. events.occurred_at is notNull with no default, and that is the schema's most consequential line. The caller stamps the event; the database does not.

An SDK buffering through a dead network replays honestly because the backlog keeps its real instants — and there is no received-at column anywhere, so nothing records how late a batch showed up. Properties travel in a JSON column with no declared shape and no index, so a filter on a property value runs inside whatever slice idx_event_app_time (app_id, occurred_at) has already narrowed. sessions is not the parent of events. Nothing foreign-keys the two together; the correlation is anonymous_id, which is notNull on sessions, nullable on events, and indexed on neither, while idx_session_app covers app_id alone. Per-app, per-window questions are therefore cheap and stitching one visitor's path is not: bound by app and time first, then match ids inside that slice.

If visitor-level replay is the product rather than a report, add an index on (app_id, anonymous_id, occurred_at) before the event table gets large, because the schema will not do it for you. funnels stores definitions, not results — a name and a JSON steps array per app, with no index declared beyond the primary key. Evaluating a three-step funnel is three bounded scans of idx_event_app_time with a name filter applied inside each, and nothing caches the answer. That keeps funnel maintenance off the write path entirely and puts the whole cost on read, which is the right trade while an app's volume still fits a range scan and the wrong one once it does not.

React Router v8

React Router v8 in framework mode puts everything under app/, and `@/` maps to that root instead of Next's src/ — the one prefix that differs, which is why shared modules like @/lib/auth and @/db/schema stay byte-identical to their Next counterparts. initCode writes app/lib/db.ts, then the auth fragment adds app/lib/auth.ts, the resource route app/routes/api.auth.$.ts, and app/lib/require-auth.ts. The route table itself is app/routes.ts: routes are declared configuration, and a file becomes a URL because that table says so. There are no React Server Components here. Every server-rendered route is a loader plus an ordinary client component: the loader runs on the server before render, the component reads its result with useLoaderData, and mutations go through an action read back with useActionData.

An API endpoint is the same module minus the default export — a resource route, named with the flat dotted convention (app/routes/api.auth.$.ts for the auth splat, app/routes/webhooks.polar.ts for a webhook POST). Auth gates in the loader rather than in a middleware layer. A protected route awaits requireAuth(request) from app/lib/require-auth.ts, which calls auth.api.getSession({ headers: request.headers }) — a real server-side validation, not a cookie peek — and throws redirect("/sign-in") when there is no session. React Router treats a thrown Response as the route's outcome, so the loader short-circuits and neither the protected query nor the component ever runs. The trade that follows: there is no matcher array to widen and no edge tier to keep honest, but protection is per-route discipline.

A new route is protected because its loader calls requireAuth; forget the call and the page is public. In return, every gate sits one function call away from the data it guards, the session is already in hand when the loader queries db, and the same request-in / Response-out contract covers pages, API endpoints and the auth mount alike.

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.

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.