Skip to content

SPRINT-2 Execution Plan — RealOutcomes Auth Unification

Status: COMPLETE — 2026-08-28 (Gate F). SPRINT-3 plan delivered; awaiting Ryan SPRINT-3 go.
Principles: R940_PRODUCT_ISOLATION_PRINCIPLES_v1.md

Executive summary

RealOutcomes today uses product-local Supabase GoTrue on VM 117 (supabase-js in the browser, supabaseAdmin.auth.getUser() on the server). SPRINT-2 replaces that with portfolio OIDC at https://auth.last1.id via @last1id/sdk, matching the provider-portal / LastVet pattern.

Good news from live audit: an app_clients row for realoutcomes already exists on sovereign last1.id (created 2026-03-15). It needs redirect URI + scope updates and a REALOUTCOMES_APP_SECRET env var on auth-last1id. This is not a greenfield registration.

Hard part: the three RealOutcomes public.users UUIDs will not match id_token.sub (last1.id identity_id). One email already exists in both systems with different IDs. A one-time FK remap transaction is required for data preservation.

RLS impact: none for app behavior (confirmed: zero auth.uid() policies; server uses service-role Postgres/Drizzle).

Phase map and Ryan gates

Phase Work Cursor stops for Ryan
S2-A Update app_clients + BWS + auth-last1id env Gate A — review client record + secret rotation plan
S2-B Code refactor (SDK login, JWKS server) Gate B — review PR / diff before deploy
S2-C Deploy to VM 116 + smoke (no user migration yet) Gate C — Ryan confirms OAuth redirect works (login page → auth.last1.id → callback)
S2-D User ID remap + per-user login verification Gate D — each of 3 identities signs in successfully
S2-E Retire VM 117 GoTrue Gate E — Ryan confirms auth still works after GoTrue stop
S2-F SPRINT-2 closeout report Gate F — approve SPRINT-3

1. OIDC client registration on auth.last1.id

1.1 Pattern (same as provider.last.vet / LastVet Tier 2)

LastVet provider portal uses:

Concept Value
Issuer https://auth.last1.id
Flow OAuth 2.0 authorization code + PKCE S256 + OIDC
client_id System app slug (e.g. lastvet) — equals app_clients.app_id
client_secret Env on last1id-app: {APP_ID}_APP_SECRET → LASTVET_APP_SECRET
Secret verify getSystemAppSecret() in last1.id/server/routes/oauth.ts hashes env secret at token exchange
id_token.sub identities.id (identity UUID), not legacy Supabase user id
id_token.aud app_clients.app_id slug (e.g. realoutcomes)
Redirect allow-list app_clients.redirect_uris text array — byte-exact match required

Reference: last.vet-api/docs/api/LAST1_ID_INTEGRATION.md § Sign in with Last1 ID; last.vet-provider/src/lib/last1Auth.ts; last.vet-api/src/lib/last1IdOAuth.ts.

1.2 Current live state (realoutcomes client)

Queried via PostgREST on auth.last1.id (2026-08-28):

Field Current value Action
id (UUID) fd280953-3af8-4328-8028-d152130e1933 Keep
app_id / OAuth client_id realoutcomes Keep
name RealOutcomes Keep
client_classification internal Keep
client_secret_hash null OK — system app uses env secret (same as lastvet)
redirect_uris http://localhost:3000/, https://last1.id/, https://realoutcomes.app/ Update — add proper callback paths
scopes identity:read, credentials:read, app_links:read, trust_events:write Add openid, email, profile, offline_access
REALOUTCOMES_APP_SECRET on VM 114 Missing Create

Compare lastvet client scopes: openid, email, profile, offline_access, identity:read, app_links:read, trust_events:write.

1.3 Target registration (after S2-A)

Item Target value
OAuth client_id realoutcomes
OAuth client_secret New 32+ byte random → REALOUTCOMES_APP_SECRET
redirect_uris (minimum) https://realoutcomes.app/auth/callback
redirect_uris (dev/LAN) http://192.168.1.99:5000/auth/callback (until SPRINT-3 tunnel)
redirect_uris (preserve) Existing localhost / legacy entries only if still needed
scopes openid, email, profile, offline_access, identity:read, app_links:read, trust_events:write (drop credentials:read unless product uses credential API)
GCP Google OAuth No change — Google login happens on auth.last1.id, not product GoTrue

1.4 Where credentials are stored

Secret / config BWS project Key name Runtime location
OAuth client secret Last1.id (cffb320a-5f61-4e1d-85df-b480012151ec) LAST1_ID_R940_PROD_REALOUTCOMES_APP_SECRET last1id-app container env
Issuer URL (app) realoutcomes (788ed6ec-0284-4bbc-b57c-b4840004688e) REALOUTCOMES_APP_PROD_LAST1_ID_ISSUER = https://auth.last1.id VM 116 .env
OAuth client_id (app) realoutcomes BWS REALOUTCOMES_APP_PROD_LAST1_OAUTH_CLIENT_ID = realoutcomes VM 116 .env + Vite build args
OAuth redirect URI (app) realoutcomes BWS REALOUTCOMES_APP_PROD_LAST1_OAUTH_REDIRECT_URI VM 116 .env + Vite
OAuth client secret (app copy) realoutcomes BWS REALOUTCOMES_APP_PROD_LAST1_OAUTH_CLIENT_SECRET VM 116 .env (server token exchange only)

Note: Server-side token exchange needs the secret; browser uses PKCE public client pattern with secret only on /api/auth/last1/callback (mirror LastVet provider).

Existing on last1id-app today: LASTVET_APP_SECRET, ONSET_APP_SECRET, RADIOCHECK_APP_SECRET, LAST1_APP_SECRET.

1.5 S2-A execution steps (Cursor)

  1. Generate REALOUTCOMES_APP_SECRET (openssl rand -base64 32).
  2. PATCH app_clients row (app_id = realoutcomes) via admin API or service-role PostgREST:
  3. Merge redirect URIs (do not drop legacy until Ryan confirms).
  4. Merge OIDC scopes.
  5. Write BWS keys (Last1.id + realoutcomes projects).
  6. Add env to /opt/auth-last1id/.env + restart last1id-app.
  7. Ryan Gate A: confirm redirect URI list + scopes in admin UI or exported JSON.

Rollback S2-A: revert app_clients row from backup JSON; remove env secret; old Supabase login still works until S2-E.

2. RealOutcomes code changes

2.1 Files to change (audit of VM 116 /opt/realoutcomes/src)

File Change
package.json Add @last1id/sdk@^0.3.1, jose (if not present)
client/src/lib/last1Auth.ts New — PKCE + authorize URL + sessionStorage flow (adapt from last.vet-provider/src/lib/last1Auth.ts)
client/src/lib/session.ts New — persist access_token, refresh_token, id_token in sessionStorage (tab-scoped)
client/src/pages/login.tsx Replace Supabase password/OTP/Google with Sign in with Last1 ID (+ optional email/password removal or redirect to auth.last1.id signup)
client/src/pages/auth/callback.tsx Replace Supabase hash parsing with OAuth code exchange via POST /api/auth/last1/callback
client/src/hooks/use-auth.ts Read bearer token from session module, not supabase.auth
client/src/lib/queryClient.ts getAuthHeaders() from session module
client/src/lib/supabase.ts Remove or gut — no client-side Supabase auth after SPRINT-2
server/lib/last1IdOAuth.ts New — port/adapt from last.vet-api/src/lib/last1IdOAuth.ts (JWKS verify, token exchange)
server/auth.ts Replace supabaseAdmin.auth.getUser(token) with verifyLast1AccessToken() or verify id_token from callback; update isAuthenticated, optionalAuth, /api/auth/verify
server/routes.ts or auth routes New POST /api/auth/last1/callback — body: { code, codeVerifier, expectedNonce }
server/index.ts Register new route if needed
.env / BWS render script LAST1_ID_ISSUER, LAST1_OAUTH_CLIENT_ID, LAST1_OAUTH_CLIENT_SECRET, LAST1_OAUTH_REDIRECT_URI, drop Supabase anon JWT usage for auth
render-realoutcomes-env-app.sh New env keys
shared/models/auth.ts Optional: add last1IdentityId field

Files unchanged for auth: Edge Functions, server/fileStorage.ts, Drizzle repos (service role DB), admin gateway HMAC path.

2.2 @last1id/sdk vs @supabase/supabase-js

Today (Supabase) After (Last1 ID)
supabase.auth.signInWithOAuth({ provider: "google" }) createPkcePair() + buildAuthorizeUrl() → redirect to https://auth.last1.id/oauth/authorize?client_id=realoutcomes&...
supabase.auth.signInWithPassword Remove — users authenticate at auth.last1.id (Google/email there)
supabase.auth.signInWithOtp Remove
Callback reads hash #access_token=... Callback reads ?code=...&state=... → server exchanges code with secret
supabase.auth.getSession() for API bearer sessionStorage token pair; optional refresh via SDK refreshToken()
supabaseAdmin.auth.getUser(jwt) server-side jose.jwtVerify against https://auth.last1.id/.well-known/jwks.json; validate iss, aud=realoutcomes, exp

Recommended flow (match provider portal):

Browser                          RealOutcomes API              auth.last1.id
────────                         ────────────────              ─────────────
login.tsx: startLast1OAuthFlow()
  → redirect /oauth/authorize ─────────────────────────────────────────────►
                                                              user signs in (Google)
◄── /auth/callback?code&state ──────────────────────────────────────────────
callback.tsx: POST /api/auth/last1/callback { code, codeVerifier, nonce }
                                 → exchangeCode (SDK) ───────► /oauth/token
                                 ← id_token (sub=identity_id)
                                 → upsert public.users
                                 ← session tokens to browser

2.3 JWT validation refactor

New server module validates access tokens (Bearer on API routes):

Check Expected
Signature RS256 via JWKS from https://auth.last1.id/.well-known/jwks.json
iss https://auth.last1.id
aud realoutcomes
exp not expired
sub last1.id identity_id → maps to public.users.id after migration

/api/auth/last1/callback additionally verifies id_token nonce matches PKCE flow (same as LastVet).

Remove dependency on: VM 117 GoTrue JWT secret, SUPABASE_SERVICE_ROLE_KEY for auth validation (still needed for Storage/Edge/admin).

2.4 RLS policies — does auth.uid() still work?

No RLS change required. No breakage expected.

Audit of VM 117 Postgres (2026-08-28):

Finding Detail
auth.uid() policies 0 rows in pg_policies for public schema
App DB access Express + Drizzle via DATABASE_URL (direct Postgres, service-level creds)
RLS Enabled on tables but policies are service_role / bypass pattern per T3-1
PostgREST user JWT Not used for app queries

Product-local GoTrue JWTs were never consumed by Postgres RLS in this architecture. Unifying to last1.id JWTs does not affect RLS until/unless you intentionally add auth.uid() policies later (out of scope).

2.5 Session management

Aspect Plan
Storage sessionStorage (tab-scoped), key e.g. realoutcomes_last1_session_v1
Contents access_token, refresh_token, optional expires_at
API calls Authorization: Bearer <access_token> via getAuthHeaders()
Refresh On 401, SDK refreshToken() once, persist new pair, retry
Logout Clear sessionStorage; optional POST /oauth/revoke best-effort; redirect /login
Cookies No httpOnly cookie in v1 (match provider portal pattern)
Admin gateway Unchanged — HMAC headers bypass Last1 JWT

3. User migration — 3 Google users

3.1 Current state

RealOutcomes VM 117 (auth.users / public.users):

Email RO public.users.id Profile / data
ryan@last1.org 5e76069d-a14e-4e3a-937c-740c3eff80b8 No profile/watchlist rows
ryan@last1.enterprises d1034389-14ef-4ea5-98f2-f7b453132edb 1 user_profiles (admin), 1 watchlist
rydaunicorn@gmail.com 5660ad84-17a6-4a63-abf5-d9d5609e7459 No profile/watchlist rows

All three: Google-only on product GoTrue (has_password=false).

auth.last1.id (sovereign DB) today:

Email last1.id user identity_id (sub claim)
ryan@last1.enterprises exists 52ce85f9-15f1-46a3-b383-11f8f1abb47b
ryan@last1.org not found created on first OAuth login
rydaunicorn@gmail.com not found created on first OAuth login

Critical: ryan@last1.enterprises RO id d1034389-... ≠ last1.id identity 52ce85f9-.... IDs are not preserved automatically.

  1. User clicks Sign in with Last1 ID on RealOutcomes.
  2. Redirected to auth.last1.id → chooses Google (same Google account as before).
  3. last1.id links Google provider to identity (existing or new).
  4. Authorization code returned → RealOutcomes exchanges → id_token.sub = identity UUID.

No password export. Google re-auth is the migration path.

Option chosen for sprint: email-keyed remap before first unified login (S2-D prep script), then verify on login.

Step Action
1 For each RO user, resolve target identity_id: query last1.id by email or wait for first login to learn sub
2 In single Postgres transaction on VM 117, for each user where old_id != identity_id:
UPDATE public.users SET id = :identity_id WHERE email = :email
UPDATE all dependent tables (12 columns across 6 FK constraints — see §3.4)
3 Delete orphaned row from auth.users (product GoTrue) after verify
4 Insert identity_app_links consent on last1.id if needed (OAuth flow usually creates on first authorize)

For ryan@last1.enterprises: pre-map d1034389-... → 52ce85f9-... using known identity_id before cutover so profile/watchlist survive.

For other two emails: first login creates last1.id identity; capture sub from callback; run remap script if any RO data existed (currently none).

3.4 Tables requiring FK updates if public.users.id changes

FKs reference public.users(id), not auth.users:

Table Column
audit_logs user_id
data_quality_issues resolved_by
saved_evaluations user_id
saved_graph_views user_id
saved_organizations user_id
user_search_history user_id
alert_preferences user_id
alert_queue user_id
alerts user_id
feature_usage user_id
stripe_subscriptions user_id
user_profiles user_id
watchlists user_id

Current row counts tied to users: only ryan@last1.enterprises has non-zero child rows (profile + watchlist).

Migration SQL will use:

BEGIN;
-- example for ryan@last1.enterprises
UPDATE public.users SET id = '52ce85f9-15f1-46a3-b383-11f8f1abb47b' WHERE id = 'd1034389-14ef-4ea5-98f2-f7b453132edb';
UPDATE public.user_profiles SET user_id = '52ce85f9-...' WHERE user_id = 'd1034389-...';
UPDATE public.watchlists SET user_id = '52ce85f9-...' WHERE user_id = 'd1034389-...';
-- repeat for other FK tables if any rows appear
COMMIT;

Script: scripts/realoutcomes/migrate-users-to-last1id.sql (generated per-user after Gate C).

3.5 Rollback if user migration fails

Failure Rollback
Remap transaction error ROLLBACK; keep Supabase auth path (do not deploy S2-B yet)
Login works but wrong user data Restore VM 117 pg_dump snapshot taken immediately before S2-D
OAuth broken Revert VM 116 deploy; keep GoTrue running (S2-E not executed)
Identity wrong account Unlink app in auth.last1.id dashboard; fix email mapping; re-run remap

Pre-S2-D backup (mandatory): pg_dump of VM 117 public + auth schemas to /var/backups/realoutcomes/pre-sprint2-user-remap-*.dump.

3.6 Can Ryan test as each identity?

Identity Who Testable after
ryan@last1.org Ryan Gate D — Ryan signs in with Google account linked to this email
ryan@last1.enterprises Ryan Gate D — same (different Google account or Workspace)
rydaunicorn@gmail.com Second real user Gate D — that person must test, or Ryan if he controls that Google account

Ryan can test all three only if he can complete Google OAuth for each email. Coordinate with the third account owner before Gate D sign-off.

4. VM 117 GoTrue retirement (S2-E)

4.1 What stops

Component Action
auth container (GoTrue) docker compose stop auth + comment out / disable service in docker-compose.yml
GoTrue env Remove from .env: GOTRUE_*, ENABLE_EMAIL_*, Google OAuth vars, ADDITIONAL_REDIRECT_URLS, SITE_URL auth-specific
auth schema Keep data until SPRINT-5 teardown decision; optional truncate auth.users after migration verified

4.2 What stays running

Service Why
db Postgres + IRS data + public.*
rest PostgREST (Edge/cron may use)
kong (api-gw) Routes /rest/v1, /storage/v1, /functions/v1
storage Uploads bucket
functions 8 Edge Functions
supavisor Connection pooler for DATABASE_URL
studio LAN-only ops
imgproxy, meta Supporting

4.3 Kong config changes

Official Supabase Kong routes /auth/v1/* → GoTrue. After GoTrue stop:

Approach Detail
Preferred Edit volumes/api/kong.yml — remove or 404 the auth-v1 service/route
Validate curl http://192.168.1.98:8000/auth/v1/health → 404/502 expected
App paths /rest/v1, /storage/v1, /functions/v1 unchanged

Browser should not call Kong after SPRINT-2 (no supabase-js). LAN Edge Function cron timers use /functions/v1 with service role — unaffected.

4.4 Rollback GoTrue within sprint

  1. docker compose up -d auth with previous compose snippet from git backup.
  2. Re-enable Kong auth route from backup kong.yml.
  3. Revert VM 116 deploy to Supabase-auth build (Gate B git tag).
  4. Restore public.users ids from pre-remap dump if needed.

Keep docker-compose.yml.bak-pre-sprint2-gotrue-* on VM 117.

5. Testing plan

5.1 Step-by-step verification

Step Test Pass criteria
T-1 curl https://auth.last1.id/.well-known/openid-configuration 200, issuer correct
T-2 PostgREST: app_clients row for realoutcomes Callback URI + OIDC scopes present
T-3 last1id-app has REALOUTCOMES_APP_SECRET Container env set
T-4 VM 116 build + deploy (pre-GoTrue retirement) Container healthy
T-5 Visit /login → click sign-in Redirect to auth.last1.id/oauth/authorize?client_id=realoutcomes
T-6 Complete Google login Lands on /auth/callback → / logged in
T-7 GET /api/auth/user with bearer Returns user row with identity_id as id
T-8 ryan@last1.enterprises profile + watchlist Still visible post-remap
T-9 Directory API unauthenticated Still 200 (public data)
T-10 Admin gateway proxy smoke Unchanged 401/200 behavior
T-11 Stop GoTrue T-6 still passes
T-12 curl :8000/auth/v1/health Fails closed (404/502)
T-13 Edge function timer dry-run /functions/v1/... 200 from VM 117 cron

5.2 Per-user login checklist (Gate D)

# User Action Verify
U-1 ryan@last1.org Google login via Last1 ID /api/auth/user email matches; no 500
U-2 ryan@last1.enterprises Google login Profile role admin still set; watchlist Veterans present
U-3 rydaunicorn@gmail.com Google login (account owner) Clean login; no orphan FK errors

5.3 Automated tests (Cursor adds)

Test Scope
last1IdOAuth.test.ts JWKS verify, aud/iss enforcement (mock JWKS)
auth-last1-callback.test.ts Callback route validation
Existing directory/smoke scripts No regression

6. Gate stops within SPRINT-2 (summary)

Gate Trigger Ryan action
A Before code deploy Approve app_clients patch + BWS key names + redirect URI list
B PR ready Review diff (login, callback, server/auth, new OAuth module)
C Deployed to VM 116, GoTrue still running Test OAuth redirect loop yourself (Ryan Google login)
D Before GoTrue stop Confirm 3-user checklist (or approve deferral of user 3)
E After GoTrue stop Confirm login still works; approve SPRINT-2 close
F SPRINT-2 report delivered Reply SPRINT-3 go for realoutcomes-edge tunnel

Cursor will not execute S2-D (user remap) or S2-E (GoTrue stop) without explicit Ryan message after Gate C.

7. Out of scope for SPRINT-2

  • SPRINT-3 realoutcomes-edge tunnel + realoutcomes.app DNS
  • Railway / hosted Supabase teardown (SPRINT-5)
  • Email/password login on RealOutcomes (users auth at auth.last1.id)
  • trust_events pipeline to last1.id (scopes include it; emission optional later)
  • Removing VITE_SUPABASE_* from build entirely (may still be needed for Storage if any client path remains — audit shows auth only today)

8. Risk register

Risk Mitigation
Wrong redirect_uri Gate A byte-exact review; test T-5/T-6 before remap
ID remap breaks FK Transaction + pre-remap dump; only 1 user has child rows
app_clients scopes missing openid Patch before testing
Third user unavailable for test Gate D documents deferral; do not SPRINT-5 until owner verifies
GoTrue retired too early S2-E only after Gate D
Empty app_clients via direct SQL Use PostgREST service role or admin API (RLS hides rows from last1id_app role)

9. References

STOP — SPRINT-2 CLOSED. See SPRINT-2_GATE_F_REPORT.md. Reply SPRINT-3 go for SPRINT-3 plan.

END SPRINT-2 execution plan