Skip to content

Tier 4 Pre-Migration Audit — Onset (T-0 / SPRINT-6)

Status: T-0 complete. T-1 complete — STOP for Ryan T-2 authorization.
Date: 2026-08-28
Scope: Planning only (T-0). No provisioning, dump, deploy, DNS, or teardown executed.
Product: Onset — patient iOS app + B2B clinic portal + marketing web
Muster item: migrate-onset-clinic-supabase-to-r940
Parent sequencing: TIER_RESEQUENCING_2026-08-28.md · SPRINT-3_CLOSEOUT.md
Pattern reference: RealOutcomes Tier 3 / SPRINT-1–5 (TIER_3_REALOUTCOMES_MIGRATION_AUDIT.md)

Executive summary

Tier 4 is Onset full-stack sovereignty: move Railway clinic compute, hosted Supabase (emxrmjysvxeboavhatry prod + lzpyfohgfdohrtlamzek staging), 24 Edge Functions, pg_cron, Storage, and dependent Cloudflare Pages / iOS client config off vendor hosting onto R940.

Critical difference from RealOutcomes: Onset carries clinical / PHI-adjacent data (protocols, labs, practitioner–patient links, encrypted PII fields) and iOS + LastVet integration complexity, but user volume is pre-user (see §4.1 correction). Cutover still requires gate discipline, low-usage window scheduling, and methodical auth staging — not because of a large active user base, but because of PHI and multi-surface risk.

Scale (prod Supabase, 2026-08-28 MCP):

Metric Value
Database size ~20 MB
auth.users 8
public tables 55
Formal FKs → auth.users 32
Edge Functions (deployed) 24
pg_cron jobs 3 (+ vault-driven HTTP to clinic portal)
Practitioners (public.practitioners) 2

Recommended calendar: 2–3 weeks wall-clock with explicit Ryan gates (vs RealOutcomes compressed sprint). Operator verification + any confirmed external accounts before production DNS cutover or hosted teardown.

Top pre-flight risks:

  1. Auth migration — product-local GoTrue today; steady state is auth.last1.id + @last1id/sdk. 32 FKs to auth.users.id must survive UUID remap (id_token.sub lesson from RealOutcomes Gate D/F).
  2. Multi-surface cutover — iOS App Store build, onset-web CF Pages env, clinic.theonset.app Railway, pg_cron vault URLs, Stripe/Fullscript/RevenueCat webhooks must move in lockstep.
  3. Pre-user but PHI-bearing — 5 non-demo auth.users rows (mostly dormant / operator); encrypted patient PII in DB; sessions invalidated on auth change unless carefully staged.
  4. Edge Function replatform — 24 functions including Anthropic-backed scan/lab paths; secrets rotation in BWS at cutover.
  5. Staging project — separate hosted Supabase (Onset-staging); decide prod-first vs dual migration in T-1.

1. Repo location and tech stack

Onset repos are not checked out under /home/ryan/code/last1/ today. Audit used hosted Supabase MCP, production HTTP probes, and mirrored snapshots in last1-internal-docs/.tmp-sync/.

Repo (GitHub) Role Stack Railway?
Last-1-Enterprises/onset-ios Patient iOS app (App Store id6761314183) SwiftUI, SwiftData, HealthKit, RevenueCat No
Last-1-Enterprises/onset-web Marketing + Pages Functions Cloudflare Pages, wrangler No (stays on CF)
Last-1-Enterprises/clinic.theonset.app B2B practitioner portal Next.js 14, Docker, Stripe, Fullscript Yes

Internal docs mirror: last1-internal-docs/content/entities/onset/
Bundle ID: com.last1enterprises.Onset

1.1 Production domains (verified 2026-08-28)

Domain Purpose Hosting Notes
https://clinic.theonset.app Clinic portal Railway (x-railway-edge: lax1) Tier 4 cutover target for compute
https://theonset.app Marketing, protocol pages, webhooks Cloudflare Pages Stays on CF; Supabase URL env must update at cutover
https://develop.onset-web.pages.dev Staging marketing Cloudflare Pages Branch develop
Staging clinic clinic-theonset-app-staging-staging.up.railway.app Railway staging Branch develop

Not in Tier 4 DNS scope: admin.last1.enterprises (Tier 6, paused).

2. Current Railway deployment

Item Finding
Project disciplined-beauty (same org as legacy RealOutcomes/n8n)
Prod service clinic-theonset-app (branch main)
Staging service clinic-theonset-app-staging (branch develop)
Build Dockerfile · railway.toml · node server.js (Next standalone)
Verified prod HTTP 200 · x-railway-edge: lax1 · Next.js

T-1 action: Railway GraphQL env audit (service IDs, env key inventory) — same pattern as RealOutcomes S3-8 archive. Do not delete Railway until T-5 after real-user E2E on R940.

onset-web: no Railway service. Marketing cron uses CF Pages Functions / worker (onset-marketing-dispatch-cron).

3. Hosted Supabase

3.1 Production — Onset (emxrmjysvxeboavhatry)

Field Value
Region us-east-1
Postgres 17.6.1.084
Status ACTIVE_HEALTHY
Database size ~20 MB
Public tables 55
Migrations (repo mirror) 60+ files under onset-ios/supabase/migrations/ (canonical)

Largest public tables (live row estimates):

Table ~Rows Sensitivity
product_events 244 Analytics
provider_access_events 216 Audit / PHI-adjacent
data_share_events 25 Consent / sharing
shared_checkins 23 Patient data
practitioner_consent_events 14 Consent
onset_protocol_*, shared_lab_results, onset_lab_results 5–10 each Clinical

3.2 Staging — Onset-staging (lzpyfohgfdohrtlamzek)

Field Value
Region us-west-2
Postgres 17.6.1.127
auth.users 5
Role Clinic staging + iOS staging xcconfig

T-1 decision: Migrate prod stack first (RealOutcomes pattern); staging hosted project teardown can trail prod or run in parallel on separate R940 VMs.

3.3 Edge Functions (prod, MCP list_edge_functions)

24 active functions (combined iOS + clinic deploy surface):

Function verify_jwt Category
daily-insight false AI / cron
admin-dashboard false Ops
generate-protocol true AI
client-event false Telemetry
scan-supplement-label, scan-supplement, search-supplement false AI / Anthropic
resolve-branded-product, popular-branded-products false Catalog
simulate-protocol-lab, scan-label, lab-intelligence, parse-lab true/false Lab / PHI-adjacent
apple-link, google-link, delete-account false Auth helpers
last1-link, last1-unlink, last1-connect-assertion false Last1 ID bridge
practitioner-notifications true Clinic
auth-send-email false GoTrue hook
protocol-import, protocol-extract, fitness-extract false Clinic

T-2 gate: Prove self-hosted Edge Runtime replatform (RealOutcomes T3-2 pattern) before Gate B dump restore sign-off.

3.4 pg_cron (prod)

jobid Schedule (UTC) Command
2 Mon 13:00 trigger_clinic_weekly_summary() → hardcoded https://clinic.theonset.app/api/cron/weekly-summary
3 hourly :00 trigger_clinic_protocol_deliveries() → vault clinic_portal_base_url + /api/cron/protocol-deliveries
4 hourly :15 trigger_clinic_fullscript_sync() → vault + /api/cron/fullscript-sync

Vault secrets: clinic_cron_secret, clinic_portal_base_url — must update at clinic cutover.

4. Real user inventory

4.1 auth.users (production)

Count Category
8 Total
3 Demo seed (@demo.theonset.app, never signed in)
5 Non-demo (auth.users; not @demo.theonset.app)

Auth providers (auth.identities): email 4 · apple 3 · google 1

Activity (production, 2026-08-28):

Segment Count Notes
Operator / test (Ryan) 2 curryrg@gmail.com (practitioner), hello@unicornryda.com (test workflow)
Promo expired / never engaged 3 Null Apple, jrdelacruz.a@gmail.com, Apple relay
Demo accounts 3 Safe to exclude from cutover comms
Practitioners (public.practitioners) 2 Clinic portal operators

User reality (final, 2026-08-28): Pre-user. Promo codes expired (ASC). No cutover comms. See T-0 sub-audits and T-1 report.

4.2 Last1 ID linkage

Table public.patient_last1_identities: columns user_id, last1_identity_id, linked_at. Optional bridge for LastVet / Last1 ID — not primary IdP today.

OAuth client app_id: onset already registered on auth.last1.id (migration in last1.id repo). SPRINT-6 auth phase should extend RealOutcomes pattern: id_token.sub → auth.users.id for FK stability.

4.3 Real-user care flags (carry through all phases)

Phase User impact Ryan gate
T-2 parallel R940 stack None (hosted prod unchanged) Gate B restore rehearsal
T-3 auth unification Session invalidation if cutover mishandled Per-gate browser + iOS test (operator + any confirmed external accounts)
T-4 DNS cutover Production downtime if rollback fails Sun 03:00–07:00 PDT (Ryan locked); notify any confirmed external users
T-5 teardown Irreversible E2E PASS + archive + confirmed user inventory

5. FK dependency mapping (auth.users.id)

32 formal PostgreSQL FK constraints reference auth.users(id) (prod query 2026-08-28).

5.1 Auth schema (Supabase internal)

auth.identities, auth.sessions, auth.mfa_factors, auth.one_time_tokens, auth.oauth_*, auth.webauthn_* — all ON DELETE CASCADE where applicable.

5.2 Public schema — patient app data

Table Column ON DELETE
ai_insights user_id CASCADE
apple_identities user_id CASCADE
google_identities user_id CASCADE
connection_code_attempts user_id CASCADE
onset_protocol_items user_id CASCADE
onset_protocol_completions user_id CASCADE
onset_lab_results user_id CASCADE
onset_user_preferences user_id CASCADE
onset_performance_windows user_id CASCADE
weekly_adherence_preferences user_id CASCADE
protocol_generations user_id CASCADE
product_events user_id CASCADE
scan_logs user_id (default)
security_events user_id SET NULL
public_protocols creator_user_id SET NULL
public_fitness_protocols creator_user_id SET NULL
patient_last1_identities user_id CASCADE
patient_shares patient_user_id (FK)

5.3 Public schema — clinic / practitioner / shared care

Table Column(s) Notes
practitioners user_id Clinic staff login
clinics portal_agreement_accepted_by, deleted_by Admin actions
clinic_support_tickets submitted_by_user_id CASCADE
shared_checkins patient_app_user_id CASCADE
patient_aggregator_connections patient_app_user_id CASCADE
data_share_events patient_app_user_id CASCADE

Additional UUID columns (practitioner_id, patient_id, patient_app_user_id) link care graphs; many reference practitioners / patient records without direct FK to auth.users — remap plan must cover both direct FKs and RPC assumptions in T-1.

Migration lesson (RealOutcomes Gate D/F): After OIDC cutover, ensure id_token.sub equals auth.users.id (or run controlled FK remap SQL). Wrong UUID mapping breaks practitioner–patient links silently.

6. Auth architecture today

Surface Primary auth Notes
iOS app Sign in with Apple → Supabase GoTrue Optional Last1 ID link (Last1IDLinkService); RevenueCat entitlements
Clinic portal Email/password + Google → Supabase GoTrue Owner MFA (TOTP); invite-only
onset-web Public + webhook endpoints Uses service role for admin/marketing APIs
auth.last1.id OAuth client onset exists Not primary IdP today

Target steady state (R940_PRODUCT_ISOLATION_PRINCIPLES_v1.md): auth.last1.id + @last1id/sdk; retire product-local GoTrue on Supabase VM; Kong/PostgREST/Storage/Edge Runtime remain.

Clinic portal caveat: Google OAuth + MFA flows must be revalidated on sovereign stack (RealOutcomes retired GoTrue on VM 117; Onset clinic may still need email provider via auth-send-email edge function until fully on Last1 ID).

7. External SaaS dependencies

Service Used by Cutover notes
Stripe Clinic portal (Solo/Practice pricing, trials) Webhook URL + live keys in BWS; rotate at T-2/T-5
SendGrid Clinic notifications, onset-web marketing Domain theonset.app; RealSend batch track separate
Fullscript Clinic dispensary integration OAuth + webhooks + hourly pg_cron sync
Anthropic Edge Functions (scan, lab, protocol AI) ANTHROPIC_API_KEY in Supabase secrets
RevenueCat iOS subscriptions + onset-web webhook App Store billing; webhook on theonset.app
Apple Sign-In iOS + clinic Redirect URIs / Services ID updates at auth migration
Last1 ID Optional patient link OAuth client exists; expand to primary in T-3
LastVet bridge last1-connect-assertion BWS LAST1_PLATFORM_SHARED_ONSET_IDENTITY_ASSERTION_SECRET
Cloudflare theonset.app, clinic DNS onset-edge tunnel (new) for clinic cutover

Not found in clinic .env.example: Twilio (no Onset-specific GG Twilio incidents in D-refinement).

Patient PII encryption: PATIENT_PII_ENCRYPTION_KEY or split PATIENT_PRIVACY_SERVICE_* — must migrate unchanged or re-encrypt under Ryan-approved plan (high risk).

8. GitGuardian D-live / Onset incidents

From GITGUARDIAN_CATEGORY_D_REFINEMENT_2026-08-27.md and generated audit:

ID Detector Repo Validity Rec. action
29340466 Supabase JWT onset-ios (Production.xcconfig) no_checker B-defer-T6 → rotate at R940 cutover
34103515 Supabase JWT onset-ios + last1-security no_checker Staging xcconfig — rotate at cutover
34103516 Postgres onset-ios docs invalid Close after teardown / doc scrub
34103517 Postgres onset-ios docs invalid Close after teardown / doc scrub

T-1 action: Expand audit to Railway env and Supabase dashboard secrets (Stripe, SendGrid, Fullscript, Anthropic) — not fully enumerated in GG export. Plan D-live rotations into ONSET_APP_PROD_* BWS prefix at T-2.

No clinic-theonset.app or onset-web GG incidents in current generated audit (runtime secrets may still exist in Railway BWS).

9. Downstream integrations (unchanged until Tier 6)

Consumer Integration Tier 4 impact
last1-admin /api/admin/onset/* direct Supabase BFF Stays on Railway + hosted keys until Tier 6
last1-admin ONSET_SUPABASE_* in BWS Update deferred to Tier 6 reconnect
onset-web Supabase anon + service role Must update CF env at Supabase cutover
iOS app Production.xcconfig Supabase URL/anon App Store release or remote config — Ryan decision in T-1

Constraint honored: Do not touch admin (paused), lastvet-edge, last1id-edge, or realoutcomes-edge.

10. R940 footprint (T-1 approved)

Following RealOutcomes VM 116/117 pattern (R940_PRODUCT_ISOLATION_PRINCIPLES_v1.md):

VM VMID Hostname LAN IP Role
App 118 onset-app 192.168.1.101 Next.js clinic Docker :3000, onset-edge cloudflared
Supabase 119 onset-db 192.168.1.102 Self-hosted stack, pg_cron, 24 Edge Functions, LAN Kong :8000

Detail: TIER_4_ONSET_T-1_REPORT.md §2.

11. SPRINT-6 phase map (Ryan-approved sequence)

Sprint phase RealOutcomes analog Onset scope STOP
T-0 T3-0 audit This document + sub-audits Ryan review
T-1 T3-1 planning T-1 report + migration plan Ryan T-2 go
T-2 T3-2 + infra VMs, Supabase deploy, Gate B dump/restore, BWS + D-live rotation Per gate
T-3 SPRINT-2 auth @last1id/sdk, OIDC client, FK remap, GoTrue retirement Per gate + real users
T-4 SPRINT-3 edge onset-edge, DNS, browser E2E + iOS + clinic Ryan DNS + user window
T-5 S3-8 teardown Railway + hosted Supabase delete, GG closure, archive After E2E PASS

Pacing: Methodical gates (PHI + iOS + multi-surface). Pre-user operational volume, but same gate discipline as a live-user migration.

12. Ryan decisions and remaining inputs

Approved at T-0 (2026-08-28)

# Topic Decision
Q3 iOS delivery TestFlight first; operator verify, then App Store
Q4 Auth strategy Clinic-first, then iOS; local GoTrue until App Store ships
Q5 VMIDs 118 onset-app, 119 onset-db

Sub-audit + T-1 decisions (2026-08-28)

# Topic Decision
Q1 User inventory Closed — pre-user; promo expired; no comms
Q2 Cutover window LOCKED: Sun 03:00–07:00 PDT
Q6 PHI encryption LOCKED: Migrate-as-is + Gate B decrypt smoke

T-1 complete

Deliverables: TIER_4_ONSET_T-1_REPORT.md, ONSET_MIGRATION_PLAN.md

Staging: Prod-first; hosted Onset-staging teardown at T-5 with prod.

Awaiting T-2

  • Confirm onset BWS project UUID at T-2 kickoff
  • Ryan authorize T-2 (VM 118/119 + Gate B + PHI smoke)

Explicit gates: T-2 → T-3 clinic OIDC → T-4 DNS (Sun window) → TestFlight → App Store → T-5 teardown.

13. Verdict (T-0 + T-1)

Check Result
Repo + stack documented PASS
Railway + Supabase inventory PASS
Auth.users inventory PASS — pre-user final
FK map to auth.users PASS (32 constraints)
Sub-audits Q1/Q2/Q6 PASS
T-1 planning PASS
Execution blocked Yes — awaiting Ryan T-2 authorization

STOP. Do not provision VMs or run pg_dump until Ryan authorizes T-2.