Booking / scheduling on React Router v8, MySQL 8 and Supabase 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.

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

Calendar-scoped booking: bookable resources with capacity, time-windowed availability slots, party-size reservations, and per-reservation payment settlement.

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

bookable things (rooms, seats, staff) owned by a Better Auth user, each carrying an integer capacity cap

time windows a resource publishes, indexed by (resourceId, startsAt) for calendar range queries

holds and confirmations against a slot, consuming partySize units and walking held → confirmed → cancelled via CHECK

one payment record per reservation, storing amountCents as integer and an opaque providerPaymentId for Stripe/etc.

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.

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

Capacity is on the resource, not the slot: a reservation consumes partySize units of the slot's capacity, so multiple parties can share one slot up to its cap.

availabilitySlots carries an isOpen boolean so an owner can close a window without deleting it (and its child reservations); the cascade is intentionally one-way downward (slot → reservation → payment).

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.

Booking / scheduling

Each level of this schema narrows time. A resources row is the thing being booked, an availability_slots row is a window that resource publishes, a reservations row is a claim on that window, and a booking_payments row settles the claim. Ownership stops at the top: resources.owner_id references Better Auth's user.id, while the guest appears three tables down as reservations.booked_by, so publisher and booker are two different columns aimed at the same identity table and no role column separates them. Capacity lives on the resource, not on the slot. resources.capacity is an integer defaulting to 1, and every reservation consumes party_size units of it, so a table for six is one resource with capacity 6 carrying several overlapping reservations, while a barber's chair is capacity 1 and effectively exclusive.

Nothing in SQL enforces that arithmetic: there is no exclusion constraint, no unique on slot_id, and no trigger summing party_size. Overbooking is the one invariant the migration hands you unguarded, and it belongs inside a transaction in your own code. availability_slots is similarly permissive — starts_at and ends_at are plain notNull timestamps with no CHECK that the window runs forwards. The indexes are shaped for the three screens this schema exists to draw. idx_slot_resource_time on (resource_id, starts_at) is the calendar: equality on the resource plus a range on the start time resolves in one index, already in chronological order. idx_reservation_user on booked_by is the guest's own list of bookings.

idx_payment_reservation on reservation_id gathers every settlement attempt against a booking, and amount_cents is an integer so summing captured money is exact rather than approximate. Both lifecycle columns are text under named CHECKs — reservations_status_check for held, confirmed and cancelled, booking_payments_status_check for pending, paid and refunded. Deletes cascade one way only, downward, which is why is_open exists on a slot: an owner withdrawing a window flips a boolean and the reservations beneath it survive, whereas deleting the slot would take those reservations and their payment rows with it. The read left uncovered is availability itself — counting party_size against a slot has no index on reservations.slot_id behind it.

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.

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.