Helpdesk / support on React Router v8, Postgres (Neon) and Better Auth

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

Postgres on Neon via Drizzle ORM and the postgres-js driver.

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

Customer support desk: tickets with status/priority queues, threaded messages with internal notes, agent profiles, and seed-managed SLA policies.

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

the core support request with open/pending/solved/closed status and low/normal/high/urgent priority, indexed for queue views

append-only thread rows on each ticket; is_internal hides agent-only notes from the requester

one agent profile per Better Auth user (unique on user_id), with an optional team field for queue segmentation

seed-managed response and resolution targets in integer minutes, keyed by priority tier

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.

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.

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.

Status and priority are text + CHECK (not pgEnum), so adding a new value (e.g. 'escalated') ships without an ALTER TYPE migration — consistent with the house style in packages/db/src/schema.ts.

agents carries a unique constraint on user_id (one profile per user) and sla_policies carries no unique on priority, allowing multiple named policies at the same priority tier for different customer tiers.

Helpdesk / support

A support desk is a queue with a conversation attached, and these four tables are arranged around exactly that. A ticket is a row in tickets: a requester_id pointing at Better Auth's user.id as text, a subject, and two graded columns. There is no separate customer entity — the person who opens a ticket and the person who answers it are rows in the same identity table, distinguished only by whether an agents row exists for them. status walks open, pending, solved, closed; priority runs low, normal, high, urgent. Both are text under named CHECK constraints (tickets_status_check, tickets_priority_check) rather than pgEnum, so introducing an 'escalated' state replaces a constraint instead of altering a live type. Two indexes carry the reads, and they point in opposite directions.

idx_ticket_status_priority on (status, priority) is the queue: filtering to open tickets uses the leading column, and ranking urgent work inside that band falls out of the same structure. idx_ticket_requester on requester_id is the customer's own history. ticket_messages holds the thread — body plus an is_internal boolean that separates a public reply from an agent-only note — indexed on (ticket_id, created_at) so a conversation loads oldest-first from one range, with is_internal applied as a filter over that small result rather than as its own index. agents is deliberately thin: one row per Better Auth user, an optional free-text team, and agents_user_unique on user_id holding it to one profile per person. Note what is absent. There is no assignee column on tickets and no ticket-to-agent join table.

Staffing here means who works the desk, not who owns which ticket; adding round-robin ownership means adding a column, and the queue index will not cover it. sla_policies is seed data rather than transactional rows: a name, a priority tier, and two integer minute budgets, first_response_mins and resolve_mins. Nothing foreign-keys it to a ticket, and no unique constrains priority, so two named policies can both sit at urgent for different customer tiers and your resolution rule decides which applies. Minutes as integers keep the deadline arithmetic exact — a target is created_at plus an interval, with no float rounding in the middle.

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.

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.

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.