Replace Supabase Auth with a self-hosted auth layer (email/password + Google SSO)

[AlaskanTuna]

Supabase Auth cannot be retained in isolation — Auth lives inside the project that pauses, so keeping it means keeping a project that will idle-pause again in seven days. The good news from inspection: the coupling is thin and well-localised. The backend has exactly one seam (requireAuth in backend/src/middleware/auth.ts calls supabase.auth.getUser(token)), and the frontend never touches Supabase tables at all — only supabase.auth.* in useAuth.tsx plus getSession() in api/client.ts and useChat.ts. The profiles RLS policy is therefore defence-in-depth only; quota is already enforced server-side by checkQuota. The genuinely hard part is identity preservation: Project.userId foreign-keys Supabase UUIDs, and 2FA-free email+password users carry bcrypt hashes that should be re-homed so existing testers are not locked out.

Scope: backend/src/middleware/auth.ts, backend/src/config/supabase.ts, backend/src/services/userService.ts, backend/src/routes/, backend/src/config/env.ts, prisma/schema.prisma, frontend/src/lib/supabase.ts, frontend/src/hooks/useAuth.tsx, frontend/src/api/client.ts, frontend/src/hooks/useChat.ts, frontend/src/components/auth/GoogleSignInButton.tsx, frontend/src/pages/SignInPage.tsx, frontend/src/pages/SignUpPage.tsx, .env.example

Depends on: #3, #4

Checklist

• Add the auth provider's Prisma models to prisma/schema.prisma and generate a migration against Neon
• Import the staged auth.users rows from task 5 into the new user table preserving each id UUID verbatim, mapping email, email_confirmed_at, and the bcrypt encrypted_password into the provider's expected columns
• Confirm the bcrypt hash format is accepted as-is; if it is not, plan a forced password-reset email for existing accounts rather than silently breaking sign-in, and say so in the issue
• Replace the body of requireAuth in backend/src/middleware/auth.ts with the new session verification, keeping the req.user = { id, email } contract byte-identical so no route handler changes
• Mount the auth handler routes on the Express app; keep enable_confirmations-equivalent behaviour ON (email verification required before sign-in) to match today's supabase/config.toml
• Wire Google OAuth using the existing GOOGLE_OAUTH_CLIENT_ID / GOOGLE_OAUTH_SECRET — and add the new callback URL to the authorized redirect URIs in Google Cloud Console; the current entry points at https://<project>.supabase.co/auth/v1/callback and will 400 otherwise
• Preserve OAuth identity linking so a user who signed up by email and later uses Google lands on the same account (today's enable_manual_linking = false behaviour)
• Rewrite frontend/src/lib/supabase.ts into a thin auth client and update the six call sites in frontend/src/hooks/useAuth.tsx (getSession, onAuthStateChange, signInWithPassword, signUp, signInWithOAuth, signOut) plus getSession() in api/client.ts and useChat.ts
• Preserve the OAuth error-toast handling added on 05/05/26 in useAuth.tsx (identity_already_exists, access_denied, server_error) — map the new provider's error codes onto the same toasts
• Preserve the sign-out cache clear that fixed the cross-user project-leak regression (Phase 11 §5 Test 3 exists specifically to guard it)
• Preserve profiles / user_tier quota reads through userService.ts; RLS is gone, so confirm every read path goes through the backend and none was relying on the client's own row-level filter
• Update frontend/src/pages/__tests__/SignInPage.test.tsx and SignUpPage.test.tsx mocks, plus backend/src/services/__tests__/userService.test.ts
• Drop the temporary auth.users staging table from task 5
• verify: pnpm test green and pnpm typecheck clean
• verify: a rescued existing user signs in with their original password and sees their original projects — this is the identity-preservation proof
• verify: a brand-new signup receives a verification email, cannot sign in before verifying, and can after
• verify: Google SSO completes end-to-end and lands on the dashboard
• verify: signing in as user A, signing out, then signing in as user B shows zero of A's projects
• verify: quota enforcement still returns 429 with the full summary JSON when the daily tier limit is hit

---

_{Priority: Critical · Phase 12 task 7 of the maintainer's migration plan.}

[AlaskanTuna]

Code complete and merged in #24. Keeping this open until the last live checks pass, since three of them genuinely depend on issue #7 landing real email.

Done and verified live against Neon

• Better Auth mounted; req.user = { id, email } unchanged, so zero route handlers touched
• Signup → tier: FREE; sign-in before verification → 403 EMAIL_NOT_VERIFIED; verify → sign-in → session cookie
• Protected route: 401 without cookie, 200 with
• Google redirect URIs confirmed with a negative control
• Cross-database tier bug fixed and demonstrated (FREE/5 → PRO/20)
• useLocale cross-device language sync preserved via a locale field

Still unverified

1. Google SSO interactive login — everything up to the consent screen is confirmed; the final hop needs real Google credentials.
2. User A/B project isolation — regression test covers it, but the live check needs two verified accounts, which is painful while verification links are only written to the server log.
3. Quota 429 with the full summary JSON — needs six real projects created as a FREE user.

Items 2 and 3 become straightforward once #7 sends real email, so they'll be verified there rather than faked here.

Superseded by the discard-users decision: the auth.users import, bcrypt-hash compatibility, the staging-table drop, and the "rescued user signs in with their original password" proof. All 16 UAT users were discarded with the PO's authorisation; public.profiles and the auth schema are gone.

[AlaskanTuna]

Remaining live checks now pass — VPN was blocking Postgres

Both outstanding items verified against real Neon with two real accounts.

User A/B project isolation ✅

Created alice and bob, verified both, signed in with separate cookie jars, and had alice create a project:

Check	Result
alice GET /api/projects	1 project — Alice Private Roof
bob GET /api/projects	0 projects
bob GET /api/projects/<alice's id>	404
alice GET /api/projects/<own id>	200

The direct-id probe is the stronger test — list filtering alone wouldn't catch a missing ownership check. It returns 404 rather than 403, which is the better behaviour: it doesn't leak that the project exists.

Quota 429 with full summary ✅

As a FREE user (limit 5), creating projects 2–6:

project 2 -> 201    project 5 -> 201
project 3 -> 201    project 6 -> 429
project 4 -> 201

Rejection body carries the full summary, not a bare error:

{"error":"Daily project limit reached","tier":"FREE","used":5,"limit":5,"resetsAt":"2026-07-31T00:00:00.000Z"}

Cleanup

Test users, their 5 projects, and 5 quota rows deleted. Preserved data confirmed intact: Location 19, Project 20, TariffConfig 1; zero residual users or sessions.

Still open

Only Google SSO interactive login — everything up to the consent screen is verified with a negative control, but the final hop needs real Google credentials.

[AlaskanTuna]

Google SSO verified end to end — closing

Real interactive login completed and lands on /dashboard signed in. Database state confirmed:

user — emailVerified: true (Better Auth trusts Google's verification, so SSO users correctly skip the email gate), tier: FREE, name and avatar populated from the Google profile.

account — providerId: google, scopes openid email profile, password: null (OAuth-only, as expected), one active session.

The root cause of the earlier 401 was not this application

grafana.exe was running as a Windows service on 0.0.0.0:3001. WSL2 only forwards localhost to Linux when no Windows process holds the port — so the browser (Windows) hit Grafana while curl and the Vite proxy (both inside WSL) reached the real backend. The mystery envelope {"extra":null,"messageId":"auth.unauthorized","traceID":""} is Grafana's error format. Confirmed by running curl.exe from Windows and reproducing it byte for byte.

Two genuine bugs were found while chasing it, both fixed:

• 87db43c — signInWithGoogle passed a relative callbackURL, which Better Auth resolves against the API origin. In dev that is the backend on :3001, which does not serve the SPA, so a successful sign-in landed on a 404. Restored the absolute window.location.origin form the Supabase implementation used.
• 311db16 — auth routes were never logged, because the handler must precede express.json() and therefore sat ahead of requestLogger. Every sign-in and OAuth callback, including failures, was invisible.

Correction on token encryption

The PR described "OAuth tokens encrypted at rest". That is imprecise. account.encryptOAuthTokens encrypts accessToken and refreshToken only — verified in Better Auth's oauth2/link-account.ts. The idToken is stored in plaintext and decodes to sub, email, name, picture.

Left as-is deliberately: those same claims are already stored unencrypted in the user table, and an ID token is a short-lived signed assertion that cannot call Google APIs — unlike the access token, which is materially sensitive and is encrypted. refreshToken is null because offline access was never requested, which is correct here.

All acceptance criteria for this issue are now met.

[AlaskanTuna]

Closed: auth migration complete and verified end to end, including Google SSO.