Project management on React Router v8, Postgres (Neon) and Clerk

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.

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

Project-management layer: user-owned projects, kanban tasks with status/priority, many-to-many assignees, per-project labels, and comment threads.

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 the publishable + secret keys in the env (React Router v8
// reads VITE_CLERK_PUBLISHABLE_KEY client-side; CLERK_SECRET_KEY server-side). ClerkProvider +
// rootAuthLoader — wired in app/root.tsx by the shadcn UI shell — pick them up automatically.

top-level containers keyed to a Better Auth user via owner_id FK, with active/archived status

the unit of work scoped to a project, with a composite (project_id, status) index driving board-column queries

task↔user assignment join (unique per pair) and a project-scoped label catalog with a task↔label join table

append-only comment rows keyed to a task and a Better Auth author, indexed on (task_id, created_at) for chronological feeds

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.

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.

task_assignees carries a composite unique on (task_id, assignee_id) — a user can be assigned to a task at most once; the per-user index on assignee_id backs the 'tasks assigned to me' feed.

Status and priority are stored as text + CHECK (not pgEnum) so new values like 'blocked' or 'critical' ship without an ALTER TYPE migration dance.

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.

Project management

Six tables sit on top of Better Auth's identity layer, and their shape is a tracker rather than a tenant. projects is the root, tasks hang off a project, and assignments, labels and comments all hang off a task. Ownership is one owner_id column on projects referencing user.id as text — text because that is what Better Auth issues — and there is no organization or membership table anywhere in the schema. A project has exactly one owner; participation is expressed by task_assignees rows, not by joining a team. tasks carries the state a board reads: status (todo, in_progress, done), priority (low, medium, high, urgent) and a nullable due_date.

Both graded columns are text guarded by named CHECKs, tasks_status_check and tasks_priority_check, instead of pgEnum, so adding a 'blocked' column is a constraint swap on a live table rather than an ALTER TYPE. The one composite index, idx_task_project_status on (project_id, status), is the board itself: a single kanban column is one range on that index, and a project-wide read uses the same index by prefix. Both many-to-many edges are modelled as sets, not logs. task_assignees is unique on (task_id, assignee_id), so re-assigning the same person is idempotent instead of duplicating a row, and idx_assignee_user on assignee_id is the only path into work that does not start from a project — it is what makes a cross-project 'assigned to me' view cheap.

task_labels repeats that shape against the per-project labels catalog, unique on (task_id, label_id) with idx_task_label_label covering the reverse read from a label back to its tasks. task_comments is append-only and indexed on (task_id, created_at), which supplies the filter and the ordering out of one structure. Every foreign key cascades downward on delete: removing a project takes its tasks, and each task takes its assignments, label links and comments with it in one statement. What is not indexed is due_date and priority, so an overdue-everywhere report scans — add that index before you build the screen, not after it starts timing out.

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.

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.