Onset Tier 4 — T-0 Sub-Audits (Q1, Q2, Q6)¶
Date: 2026-08-28
Scope: Production Supabase emxrmjysvxeboavhatry (Onset-prod)
Parent: TIER_4_ONSET_MIGRATION_AUDIT.md
Status: Q1/Q2/Q6 closed — T-1 complete. See TIER_4_ONSET_T-1_REPORT.md. STOP before T-2.
Ryan approved T-0 answers for Q3–Q5 (see parent audit §12). User reality: pre-user; promo codes expired; no cutover comms. Only active workflow is Ryan operator testing (hello@unicornryda.com).
Ryan decisions already locked (Q3–Q5)¶
| # | Decision |
|---|---|
| Q3 iOS | TestFlight first; operator verifies new build, then App Store (external cohort TBD after Q1 promo cross-ref) |
| Q4 Auth | Clinic-first, then iOS; local GoTrue stays alive until iOS App Store release ships |
| Q5 VMIDs | 118 onset-app, 119 onset-db |
Explicit gates before teardown: Q1 identity confirmation → Q2 window → cutover comms → TestFlight verification → App Store release → T-5 teardown.
Q1 — Non-demo user identity audit¶
Query: auth.users where email is NULL or not @demo.theonset.app (5 accounts). Demo accounts excluded: jamie.morgan@, alex.rivera@, morgan.lee@ (3).
Framing correction (Ryan, 2026-08-28): The T-0 “5 non-demo = real users” count overcounted. Operational reality: ~0 external active users; dominant activity is Ryan’s test workflow. Three identities remain pending App Store Connect promo + RevenueCat cross-reference before T-1.
Identity table — final classifications (2026-08-28)¶
| # | User ID | Classification | |
|---|---|---|---|
| 1 | (null — Apple Sign-In) | 474ef7c8-… |
PROMO EXPIRED — never engaged |
| 2 | curryrg@gmail.com |
466dcb73-… |
PRACTITIONER (Ryan) |
| 3 | hello@unicornryda.com |
26b42005-… |
TEST (Ryan) — unicorn PC workflow |
| 4 | jrdelacruz.a@gmail.com |
9b3cd379-… |
PROMO EXPIRED — dormant one-time trial |
| 5 | vnmpxwcz4t@privaterelay.appleid.com |
d97ede01-… |
PROMO EXPIRED — never engaged |
ASC check: Promo codes expired; no active redemptions in the wild.
Supporting signals (unchanged from sub-audit query)¶
| # | Created | Last sign-in | Auth provider | Practitioner row |
|---|---|---|---|---|
| 1 | 2026-07-21 | 2026-07-21 | Apple | — |
| 2 | 2026-05-03 | 2026-06-24 | email, google | Ryan Curry, solo |
| 3 | 2026-04-28 | 2026-06-24 | apple | Test Provider, free |
| 4 | 2026-05-01 | 2026-05-01 | apple | — |
| 5 | 2026-04-30 | 2026-04-30 | apple | — |
Practitioner–patient links (practitioner_patients)¶
| Patient app user | Notes | |
|---|---|---|
26b42005-… |
hello@unicornryda.com |
Only non-demo user linked as patient |
| 3 demo users | @demo.theonset.app |
Seeded demo patients |
| 1 row | patient_app_user_id NULL |
Orphan/manual patient row (encrypted PII in DB) |
Cutover comms: None required (pre-user).
Q1 follow-up — completed¶
Promo codes expired per App Store Connect. RevenueCat showed no active external subscribers requiring coordination.
Q2 — Usage patterns and cutover window recommendations¶
Schema note: no appointment model¶
Production has no practitioner–patient appointment or scheduling tables. Care rhythm is inferred from:
shared_checkins(patient daily check-ins)product_events(iOS app telemetry)provider_access_events(clinic portal access)pg_cronjobs calling clinic Railway URLs (protocol delivery, Fullscript sync, weekly summary)
Marketing tables (marketing_broadcasts, marketing_send_queue) have scheduled_at / scheduled_for only; not clinical scheduling.
Last login summary (non-demo)¶
| User | Last auth sign-in | Last product activity |
|---|---|---|
| Apple null email | 2026-07-21 | 2026-07-21 |
hello@unicornryda.com |
2026-06-24 | 2026-07-10 |
curryrg@gmail.com |
2026-06-24 | — |
jrdelacruz.a@gmail.com |
2026-05-01 | 2026-05-29 |
| Apple relay | 2026-04-30 | — |
Overall: Activity is dominated by Ryan’s test account (hello@unicornryda.com). Other non-demo accounts are dormant 2–4 months. Pre-user operational posture for cutover planning.
Activity peaks (historical, UTC → Pacific PDT)¶
iOS product_events (top hours):
| UTC (dow, hour) | Pacific (approx.) | Events |
|---|---|---|
| Wed 01:00 | Tue 18:00 PDT | 21 |
| Tue 19:00 | Tue 12:00 PDT | 18 |
| Wed 22:00 | Wed 15:00 PDT | 13 |
| Thu 00:00 | Wed 17:00 PDT | 11 |
Shared check-ins: 21 of 23 at Thu 22:00 UTC = Thu 15:00 PDT (single user habit).
Clinic provider_access_events (top hours):
| UTC (dow, hour) | Pacific (approx.) | Events |
|---|---|---|
| Fri 16:00 | Fri 09:00 PDT | 37 |
| Mon 03:00 | Sun 20:00 PDT | 20 |
| Tue 18:00 | Tue 11:00 PDT | 19 |
| Wed 21:00 | Wed 14:00 PDT | 15 |
Automated jobs (cron.job):
| Job | Schedule (UTC) | Pacific | Impact if clinic URL wrong |
|---|---|---|---|
clinic_protocol_deliveries |
:00 every hour |
top of hour PDT | Missed protocol emails |
clinic_fullscript_sync |
:15 every hour |
:15 PDT | Fullscript drift |
clinic_weekly_summary |
Mon 13:00 | Mon 06:00 PDT | Weekly digest failure |
Recommended cutover windows (clinic DNS / Railway → R940)¶
Assume cutover completes in ≤2 hours with cron paused or vault URLs pre-staged. Ranked by projected user impact given current dormancy:
| Rank | Window (UTC) | Pacific (PDT) | Rationale |
|---|---|---|---|
| 1 (preferred) | Sun 10:00–14:00 | Sun 03:00–07:00 | Lowest combined mobile + clinic peaks; finish before Mon 06:00 weekly summary if cron enabled on R940 |
| 2 | Tue 10:00–14:00 | Tue 03:00–07:00 | Avoids Tue 11:00 clinic peak and Wed afternoon/evening mobile peaks |
| 3 | Thu 08:00–12:00 | Thu 01:00–05:00 | Quiet gap before Thu 15:00 check-in habit and Fri 09:00 clinic peak |
Ryan decision (2026-08-28): OPTION 1 — Sun 03:00–07:00 PDT (UTC Sun 10:00–14:00). Quietest window; finish before Mon 06:00 PDT weekly summary cron. Matches methodical sprint pacing.
Avoid: Fri 09:00–12:00 PDT (clinic portal peak), Wed 12:00–18:00 PDT (mobile events), Mon 05:00–07:00 PDT unless weekly summary cron is disabled during cutover.
Q6 — PHI encryption key architecture audit¶
How PHI is encrypted today¶
| Layer | Mechanism |
|---|---|
| Database at rest | Hosted Supabase / Postgres provider encryption (standard TDE); not app-managed |
| Application PHI | Per-field AES-256-GCM in clinic app + Edge Functions |
| Key material | PATIENT_PII_ENCRYPTION_KEY — base64url-encoded 32-byte key in Railway env (and Edge Function secrets) |
| Ciphertext format | enc:v1:{iv}:{tag}:{ciphertext} |
| Fields encrypted (prod) | practitioner_patients.patient_name (5/5), practitioner_patients.patient_email (5/5) |
| Blind index | patient_email_hash = SHA-256 of normalized email (plaintext hex in DB for lookup/uniqueness) |
| Related crypto | Fullscript OAuth tokens use fsenc:v1: with FULLSCRIPT_TOKEN_ENCRYPTION_KEY or fallback to same PATIENT_PII_ENCRYPTION_KEY |
Code references: clinic.theonset.app/src/lib/patient-privacy.ts, scripts/backfill-patient-pii.mjs, supabase/functions/practitioner-notifications/index.ts (local decrypt path).
Production evidence: All 5 practitioner_patients rows store enc:v1: for both name and email → local-key mode is live; privacy-service split is not active in prod DB shape.
What is the "privacy-service split"?¶
Optional architecture documented in clinic.theonset.app/privacy-vault/:
- Deploy standalone Cloudflare Worker (
privacy-vault/worker.ts) holdingPATIENT_PII_ENCRYPTION_KEY - Clinic app holds only
PATIENT_PRIVACY_SERVICE_URL+PATIENT_PRIVACY_SERVICE_TOKEN - App calls vault for
encrypt/decrypt/batchDecrypt/hashinstead of local crypto - Split custody: DB admins see ciphertext; clinic deploy admins never see raw key
If PATIENT_PRIVACY_SERVICE_URL is set, patient-privacy.ts routes all PII ops to HTTP; if unset, local key path is used (current prod behavior).
Migration implications¶
| Approach | Pros | Cons |
|---|---|---|
| Migrate-as-is (recommended) | Zero re-encryption; Gate B restore + same key = immediate decrypt; Edge Functions already expect local key | Key remains in clinic + EF env on R940 until a later custody hardening sprint |
| Validate-first (same key, extra Gate B test) | Same as migrate-as-is plus explicit decrypt smoke test on R940 before DNS | Slightly longer T-2; no architectural change |
| Switch to privacy-service during migration | Stronger custody post-cutover | Requires deploying vault, re-pointing clinic env, verifying Edge Function paths (EF use local decrypt today); high risk during migration |
Ryan decision (2026-08-28): MIGRATE-AS-IS with mandatory Gate B decrypt smoke test. Do not scope-creep to privacy-service validation during migration.
Post-migration hardening (optional future gate): deploy privacy-vault Worker, migrate env to service URL, audit Edge Functions for local-key assumptions.
Sub-audit verdict¶
| Sub-audit | Status | Ryan input |
|---|---|---|
| Q1 Identity | CLOSED | Pre-user; promo expired |
| Q2 Cutover window | LOCKED | Option 1: Sun 03:00–07:00 PDT |
| Q6 PHI keys | LOCKED | Migrate-as-is + mandatory Gate B decrypt smoke test |
T-1 complete. T-2 blocked until Ryan authorizes VM provision + Gate B.
Related documents¶
- TIER_4_ONSET_MIGRATION_AUDIT.md
- Clinic privacy vault:
last1-internal-docs/.tmp-sync/Last-1-Enterprises-clinic.theonset.app/privacy-vault/README.md