Booking / scheduling on Nuxt 4, MySQL 8 and Clerk

Nuxt 4 (framework mode): full-stack Vue SSR: client under app/ (Vite), server under server/ (Nitro), API as server/api/*.post.ts Nitro route handlers.

MySQL 8 via Drizzle ORM and the mysql2 driver.

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

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.

Client/server split: the DB client, Drizzle schema, records, and webhooks are server-side (server/). The `@/` alias is the client root (app/); server code reaches shared modules via Nuxt's `~~` rootDir alias (e.g. `~~/server/db/schema`).

The API layer is Nitro, Nuxt's server engine: endpoints are server/api/*.post.ts route handlers, and auth mounts as a Nitro catch-all that delegates to the auth library's framework-agnostic web handler.

Nuxt auto-imports components and composables at runtime, but the emitted server code imports h3 helpers (defineEventHandler, toWebRequest) EXPLICITLY — the one deliberate idiom trade so the handlers type-check under standalone tsc instead of relying on the auto-import magic.

Session gating runs in a Nitro server middleware (server/middleware/), which fires on every SSR and API request — the true security boundary, and a real server-side session check rather than a cookie-existence peek.

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.

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

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.

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.

Nuxt 4

Nuxt 4 in framework mode is the one stack here with two roots. Client code lives under app/ and is what `@/` points at (Vite, Vue single-file components); server code lives under server/ and is run by Nitro, Nuxt's server engine. The database layer is server-side, so initCode writes server/lib/db.ts and the schema, record modules and webhooks land under server/db/ and server/api/ — server modules reach each other through Nuxt's `~~` rootDir alias (`~~/server/lib/db`, `~~/server/db/schema`), never through `@/`. That split earns its keep with secrets: the Resend send client belongs to ~~/server/lib/email, and nothing under app/ can import it by accident. The API layer is Nitro rather than a React-shaped route file.

server/api/webhooks/polar.post.ts is a POST endpoint; auth mounts as the catch-all server/api/auth/[...all].ts, which adapts the H3 event with toWebRequest(event) and hands the resulting web Request to the auth library's framework-agnostic handler. Nuxt auto-imports defineEventHandler and its siblings at runtime, but the emitted server files import them from h3 explicitly — one deliberate idiom trade so every handler type-checks under standalone tsc. Session gating is a Nitro server middleware at server/middleware/auth.ts. It fires on every SSR render and every API request, filters on pathname prefixes (/dashboard, /settings), performs the real auth.api.getSession() lookup, and answers with sendRedirect(event, "/sign-in", 302). Because Nitro sits in front of both the rendered page and the endpoints, that is a genuine security boundary rather than a cheap pre-render bounce.

On the client, Vue does its own thing: the auth binding exposes signIn/signUp/useSession as Vue refs, screens are .vue components under app/pages/ (sign-in.vue, dashboard/[id].vue), chrome lives in app/components/ and app/layouts/, and SPA-side guards are app/middleware/*.ts. The design system is shadcn-vue on reka-ui — a real re-port, not the React components wearing new names — and it is checked with vue-tsc, since plain tsc cannot parse an SFC.

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.