IoT telemetry on React Router v8, MySQL 8 and Clerk

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.

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

IoT telemetry: user-owned devices, append-only sensor readings, threshold alert rules, and fired alert instances.

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

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

user-owned device registry with a unique device_key and an online/offline status CHECK

append-only rows of metric + numeric value per device, indexed for time-range rollups

per-device threshold rules (gt/lt/gte/lte comparator CHECK) and the alert instances they fire, with firing/resolved lifecycle

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

devices.device_key carries a unique constraint — it is the physical device's identity token and must be generated once at provisioning time, never regenerated.

sensor_readings is append-only (no update path, composite index on device_id + recorded_at) — roll up by device + time window for dashboards rather than mutating any running aggregate.

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.

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.

IoT telemetry

The device stamps the time, not the server: sensor_readings.recorded_at is notNull with no default, so whatever the firmware puts on the wire is what lands. A gateway buffering through an outage therefore replays honestly, its backlog keeping true instants instead of collapsing onto the moment it reconnected — and readings arrive out of order as a matter of course, so the latest reading for a device means ordering by recorded_at, never taking a max of anything else. Readings are a narrow long table: device_id, a free-text metric, a value, and that timestamp. On Postgres value is numeric, which is arbitrary precision; on MySQL it is decimal(20,6), because a bare mysql decimal is (10,0) and would silently round every temperature to a whole degree.

Six fractional digits is the ceiling on that side. Devices are the scoping root. device_key is UNIQUE and serves as the hardware's identity at ingest — issued once at provisioning, and the only credential this schema knows about. owner_id foreign-keys Better Auth's user, with idx_device_owner behind the fleet list. status is a stored flag held to 'online' or 'offline' by devices_status_check; it is not derived from readings, so a heartbeat job or the ingest handler has to write it, and it stays wrong until something does. The alerting half is two tables rather than one because a declaration and a firing have different lifetimes.

alert_rules is a per-device threshold — a metric, a comparator held by alert_rules_comparator_check to gt, lt, gte or lte, and a threshold in the same numeric type as value — and nothing stops several rules covering one metric. alerts is the instance: status guarded to 'firing' or 'resolved', triggered_at defaulted, resolved_at nullable until something closes it. It stores both rule_id and device_id even though the device is reachable through the rule, and that redundancy is the point — idx_alert_device answers what is firing on this device with no join. Cascades run the whole way down from devices, so decommissioning one deletes its readings, its rules and its alert history in a single statement. If the telemetry has to outlive the hardware, keep the device row and stop writing to 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.

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.