Add Supabase Authentication

Problem this prompt solves

Supabase Auth tutorials often stop at “client.signIn works in a sandbox.” In App Router, the hard parts are cookie-based SSR sessions, middleware that refreshes tokens without breaking Server Components, correct redirect URLs on Vercel, and bootstrapping a profiles row without race conditions. Without those, you get flicker, infinite redirects, or “logged in” UI that fails on the server.

When to use it

• You have a Next.js App Router project and need real sign-up / sign-in before building product features.
• Auth “works locally” but Server Components still see a null user, or middleware fights the login redirect.
• You are adding Google (or another) OAuth alongside email/password or magic link.
• You need a profiles table and first-login bootstrap tied to auth.users.

Add Supabase Authentication to this Next.js App Router (TypeScript) project. Use @supabase/ssr for cookie-based sessions suitable for Server Components and Route Handlers.

Goals:
1) Email/password auth (default). If the repo already prefers magic link or Google OAuth, implement that instead and say why.
2) Browser client, server client (cookies), and a clear rule: service role never runs in the browser.
3) Middleware that refreshes the session and protects routes under /app (and any API routes that require a user).
4) Pages: /login, /signup, /auth/callback (for OAuth/code exchange), and a minimal /app home that greets the signed-in user.
5) profiles table: id UUID PK references auth.users(id) on delete cascade; email; display_name; created_at. Create the row on first authenticated request (upsert) or via a Supabase trigger — pick one and document it.
6) RLS on profiles: users can select/update only their own row.
7) Sign-out that clears the session and redirects to /.

Implementation requirements:
- Inspect the existing project structure and match its patterns (app/ vs src/app/, existing UI components).
- Use environment variables: NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY. Document them in .env.example.
- Handle auth errors in the UI with readable messages (invalid credentials, email not confirmed).
- Configure and document redirect URLs for local (http://localhost:3000) and Vercel preview/production.
- Do not weaken RLS “temporarily.” Do not store passwords or JWTs in localStorage.

Deliverables:
- Code changes for clients, middleware, auth pages, and profile bootstrap
- SQL for profiles + RLS (migration file or SQL block)
- Short README section: enable Email provider in Supabase, set redirect URLs, run SQL, test the flow
- Manual test checklist for email confirmation on/off cases

Expected result

Working login/signup/logout with SSR-aware Supabase sessions, middleware-protected /app routes, a profiles row per user under RLS, documented redirect URLs, and no service-role usage in client code.

Implementation notes

• Install and use @supabase/ssr — the older auth-helpers patterns break easily on newer Next.js cookie APIs.
• Keep middleware lean: refresh session + redirect unauthenticated users; do not put business authorization logic only in middleware.
• Turn off “Confirm email” in Supabase for local speed, but document the production setting so you do not ship an open sign-up surprise.
• For OAuth, the /auth/callback route must exchange the code on the server and then redirect into /app.
• If you use a DB trigger for profiles, still handle the “profile missing” case in app code for resilience.
• Align Site URL and Additional Redirect URLs in the Supabase dashboard with every Vercel domain you use.

Testing checklist

• Sign up with a new email; confirm you land in /app and a profiles row exists.
• Sign out; confirm /app redirects to /login and server components no longer see a user.
• Sign in with wrong password — UI shows a controlled error.
• With confirm-email enabled, verify unconfirmed users cannot use the app until they confirm.
• Refresh a protected page — session should persist without forcing a new login.
• If OAuth is enabled, complete Google login on localhost and on a Vercel preview URL.
• Attempt to select another user’s profile with the anon key in the SQL editor or a script — RLS must block it.

Common mistakes

• Creating only a browser client and fetching the user in Client Components — Server Components and Route Handlers stay blind.
• Forgetting the auth callback route when enabling OAuth — redirects fail with opaque errors.
• Putting SUPABASE_SERVICE_ROLE_KEY in NEXT_PUBLIC_ variables.
• Protecting pages only in the UI (hide buttons) without middleware or server checks.
• Mismatch between Supabase redirect allow-list and the deployed URL — login works locally only.
• Creating profiles without RLS, leaving PII readable with the anon key.