Social network on Nuxt 4, MySQL 8 and Supabase Auth with Resend

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.

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

Social network: public profiles with @-handles, a directed follow graph, an authored-post feed, and per-post like edges.

Resend: transactional email (welcome/notification always; verify/reset when auth is self-hosted).

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

one public profile per Better Auth user, with a unique @-handle and optional bio/avatar

directed follower→following edges with a composite unique that prevents duplicate follows

authored feed posts keyed to a Better Auth user, indexed per-author for profile timeline queries

post↔user like edges with a composite unique enforcing one like per user per post

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

Both the follow edge (follower_id + following_id composite PK + unique) and the like edge (post_id + user_id composite PK + unique) carry explicit unique constraints — the constraint is the identity, not just a performance index.

Better Auth's user.id is text, not uuid; all four tables match that type on their FK columns rather than recasting, so joins never cross a type boundary.

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.

Social network

Better Auth's user row is the account; profiles is the face that account shows. The split is deliberate: profiles.user_id is a unique text FK, so the mapping is strictly one-to-one, but posts.author_id, follows.follower_id, follows.following_id and post_likes.user_id all reference user.id rather than profiles.id. Authorship is keyed to the account, while the handle, bio and avatar are a separate row you join in for display. Every one of those FKs cascades on delete, so removing an account clears its profile, both directions of its follow edges, its posts and its likes in a single statement — there is no orphan sweep to write. The graph itself is two edge tables, and both make the edge its own identity.

follows has no surrogate key: (follower_id, following_id) is the primary key, and follows_follower_following_unique restates the same rule. You cannot follow the same account twice, and direction matters — following back inserts a second, separate row. post_likes is shaped identically, with (post_id, user_id) as the composite primary key plus post_likes_post_user_unique. Neither table caches a count; follower totals and like totals are aggregates over the edges themselves. The cost of that shape is worth knowing before you build on it. A home feed is fan-out-on-read: take the follower-side prefix of the follows primary key to get everyone a viewer follows, then read each author through idx_post_author.

posts carries only idx_post_author — no (author_id, created_at) composite and no index on created_at alone — so newest-first ordering is a sort on top of the index hit, which is fine at seed scale and the first thing to revisit under load. Likes read better: (post_id, user_id) leads with post_id, so counting a post's likes and answering whether the current viewer already liked it both ride the same key, and idx_post_likes_user runs the relation backwards for a liked-posts page. handle is unique and separately covered by idx_profile_handle, which is what keeps an /@name route a single-row lookup.

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.

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.

Resend

Resend covers two jobs: the transactional templates and the module that sends them. `emailTemplates` branches on `framework.slug` — Next.js and React Router get React Email components under `emails/` (`welcome.tsx`, `notification.tsx`, plus `verify-email.tsx` and `reset-password.tsx` when the auth is library-kind), Nuxt gets the same four as `.mjml` files. Both serializations read subject, preview, heading, body lines and CTA label from the shared `EMAIL_COPY` map, so the words are identical and only the markup differs. Every template closes with the `BRAND_ADDRESS` mailing address and an Unsubscribe link, and the React path sets `lang="en"`, a non-empty `<title>` and an explicit `<meta charSet="utf-8">`. `emailSend` emits `lib/email.ts`.

The provider client is built lazily inside `resendClient()` (`client ??= new Resend(process.env.RESEND_API_KEY)`) because `new Resend(undefined)` throws "Missing API key" — at module scope that throw fires at import time, which is exactly when a framework build imports this file to collect page data, so `next build` would fail on any machine holding only runtime secrets. `FROM` reads `EMAIL_FROM` and falls back to Resend's sandbox sender. On Next and React Router each sender awaits `render(WelcomeEmail(props))` from `@react-email/render` — async in the installed 2.1.0 — then calls `resendClient().emails.send({ from, to, subject, html })`.

On Nuxt the templates are inlined as string constants instead, `fill()` substitutes their `{{token}}` placeholders, `mjml2html(src, { validationLevel: "soft" })` compiles them and throws on any returned `errors`, and an ambient `mjml.d.ts` ships alongside because mjml@5 carries no type declarations. Which senders exist is gated on `auth.kind`. Hosted auth gets `sendWelcome` and `sendNotification` only. A library auth additionally gets `sendVerifyEmail` and `sendResetPassword` — the two functions its own config imports back out of this module to fill Better Auth's `emailAndPassword.sendResetPassword` and `emailVerification.sendVerificationEmail` hooks. A hosted provider sends those mails from its own infrastructure.