Tier 4 Onset — T-3 Pre-kickoff Sub-audits (Storage + Cron)

Date: 2026-08-29
Status: COMPLETE (re-validated 2026-08-29 post-NAMING-2). Await Ryan review + explicit T-3 authorization.
Prior: TIER_4_ONSET_GATE_B_REPORT.md (Gate B + PHI PASS)
BWS check: ONSET_BWS_DB_VALIDATION.md — DB prod keys LAN-correct
Network: R940_LAN_NETWORK_INVENTORY.md (.100 = iDRAC)

---

Executive summary

Ryan accepted cron.job and storage.objects Gate B gaps for T-3 progression (code refactor does not require cron or storage parity). Both MUST be fixed before T-4 cutover.

Live R940 confirmation (2026-08-29): onset-db VM 119 — cron.job 0, storage.objects 0 (matches accepted gap; hosted prod has 3 jobs / 20 objects).

These sub-audits inventory hosted production state to inform T-4 fix approach.

Sub-audit	Objects / jobs	Criticality
A — Storage	20 objects, 2 buckets, ~3.2 MB total	Low volume; templates public bucket feeds social/n8n workflow
B — Cron	3 pg_cron jobs	High before T-4; clinic automation stops without them

---

Gap acceptance (Ryan 2026-08-29)

Gap	T-3	T-4
cron.job restore (0 vs 3)	Accepted — T-3 is auth refactor, cron not required	MUST fix
storage.objects restore (0 vs 20)	Accepted — sub-audit informs approach	MUST fix

---

Sub-audit A — Storage inventory

Source: hosted Supabase emxrmjysvxeboavhatry, storage.objects + bucket metadata (2026-08-29).

Bucket summary

Bucket	Public	Objects	Total size	Category
clinic-support-attachments	No	2	1.9 MB	Practitioner ops
templates	Yes	18	1.3 MB	App / marketing assets
Total		20	~3.2 MB

Category totals

Category	Objects	Size	PHI?
PHI files (patient chart / lab / clinical uploads)	0	0	—
Practitioner assets (clinic portal support attachments)	2	1.9 MB	Incidental (portal screenshots; not encrypted chart PHI)
App assets (social templates, renderer HTML)	17	1.3 MB	No
Other (.emptyFolderPlaceholder)	1	0	No

Full object list

clinic-support-attachments (private) — Practitioner assets

Object path	Size	Uploaded (UTC)	Associated user
686c4f57-d09d-4b0e-9524-6d145b50e7c8/abe9bc3a-ae5e-4a66-88c0-6e6b58e28586-portal-screenshot-1782250508316.png	949.5 KB	2026-06-23 21:35	curryrg@gmail.com (practitioner); ticket subject TEST
975e95f7-11c3-4e00-aef8-4cc31a048c26/833b42f5-f88a-4f12-b7c3-0a96e54899bd-portal-screenshot-1782250508316.png	949.5 KB	2026-06-23 21:40	curryrg@gmail.com (practitioner); ticket subject TEST

Path prefix = clinic_support_tickets.id; linked via clinic_support_ticket_attachments.

templates (public) — App assets

Object path	Size	Uploaded (UTC)	Notes
.emptyFolderPlaceholder	0	2026-04-18	Other — Supabase folder stub
onset-social-renderer.html	33.2 KB	2026-04-18	Social post HTML template
posts/1.png	93.4 KB	2026-04-18	Social image
posts/2.png	77.4 KB	2026-04-18	Social image
posts/3.png	86.1 KB	2026-04-18	Social image
posts/4.png	81.6 KB	2026-04-18	Social image
posts/5.png	86.9 KB	2026-04-18	Social image
posts/6.png	87.9 KB	2026-04-18	Social image
posts/7.png	90.6 KB	2026-04-18	Social image
posts/8.png	89.0 KB	2026-04-19	Social image
posts/9.png	73.7 KB	2026-04-19	Social image
posts/10.png	41.1 KB	2026-04-19	Social image
posts/11.png	78.6 KB	2026-04-19	Social image
posts/12.png	90.1 KB	2026-04-19	Social image
posts/13.png	65.2 KB	2026-04-19	Social image
posts/M1776624461942-0.png	90.3 KB	2026-04-19	Social image (batch)
posts/M1776624461942-1.png	81.7 KB	2026-04-19	Social image (batch)
posts/M1776624461942-2.png	57.9 KB	2026-04-19	Social image (batch)

Public URLs: https://emxrmjysvxeboavhatry.supabase.co/storage/v1/object/public/templates/...

T-4 storage fix recommendation

Approach	When	Notes
A. Schema align + re-restore metadata	Preferred if self-hosted storage version can match hosted columns (versioning_status, archived_at, …)	Gate B dump already contains object rows; restore failed on column mismatch
B. API file migration	Fallback	~3.2 MB total; download from hosted public/private buckets → upload to R940 MinIO/S3 backend
C. Selective skip	Not recommended	templates bucket referenced by n8n Onset Daily Publisher workflow

Criticality: Low file count; templates public bucket is operational (social/n8n). clinic-support-attachments is operator test data only (2 screenshots).

---

Sub-audit B — Cron job definitions

Source: hosted cron.job + pg_get_functiondef (2026-08-29).

Vault dependencies

Vault secret	Purpose
clinic_cron_secret	Bearer token for clinic /api/cron/* routes
clinic_portal_base_url	Base URL for protocol + fullscript crons (vault-driven)

Functions restored on R940 via Gate B; jobs were not (table owner permission).

Job table

jobid	jobname	Schedule (UTC)	Pacific	Command
2	clinic_weekly_summary	0 13 * * 1	Mon 06:00 PDT	select public.trigger_clinic_weekly_summary();
3	clinic_protocol_deliveries	0 * * * *	Top of hour	select public.trigger_clinic_protocol_deliveries();
4	clinic_fullscript_sync	15 * * * *	:15 past hour	select public.trigger_clinic_fullscript_sync();

All jobs: database=postgres, username=postgres, active=true.

Trigger function definitions (hosted)

Shared helper — trigger_clinic_cron_route(p_path):

-- Reads vault: clinic_cron_secret, clinic_portal_base_url
-- POST via pg_net: rtrim(base_url,'/') || p_path
-- Headers: Authorization: Bearer <clinic_cron_secret>
-- Timeout: 120000 ms
select public.trigger_clinic_cron_route('/api/cron/...');

Job 3 — protocol deliveries:

CREATE OR REPLACE FUNCTION public.trigger_clinic_protocol_deliveries()
 RETURNS bigint LANGUAGE sql SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  select public.trigger_clinic_cron_route('/api/cron/protocol-deliveries');
$function$;

Job 4 — Fullscript sync:

CREATE OR REPLACE FUNCTION public.trigger_clinic_fullscript_sync()
 RETURNS bigint LANGUAGE sql SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  select public.trigger_clinic_cron_route('/api/cron/fullscript-sync');
$function$;

Job 2 — weekly summary (hardcoded prod clinic URL, not vault base_url):

CREATE OR REPLACE FUNCTION public.trigger_clinic_weekly_summary()
 RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER
 SET search_path TO 'extensions', 'vault', 'public'
AS $function$
declare
  request_id bigint;
  secret text;
begin
  select decrypted_secret into secret
  from vault.decrypted_secrets
  where name = 'clinic_cron_secret';

  if secret is null then
    raise exception 'clinic_cron_secret missing from vault';
  end if;

  select net.http_post(
    url := 'https://clinic.theonset.app/api/cron/weekly-summary',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || secret
    ),
    body := '{}'::jsonb,
    timeout_milliseconds := 30000
  ) into request_id;

  return request_id;
end;
$function$;

T-4 cron recreation SQL (sovereign VM 119)

Run as superuser / table owner after vault secrets point at R940 clinic:

SELECT cron.schedule(
  'clinic_weekly_summary',
  '0 13 * * 1',
  $$ SELECT public.trigger_clinic_weekly_summary(); $$
);

SELECT cron.schedule(
  'clinic_protocol_deliveries',
  '0 * * * *',
  $$ SELECT public.trigger_clinic_protocol_deliveries(); $$
);

SELECT cron.schedule(
  'clinic_fullscript_sync',
  '15 * * * *',
  $$ SELECT public.trigger_clinic_fullscript_sync(); $$
);

Alternative: ALTER TABLE cron.job OWNER TO postgres; then re-run targeted pg_restore --data-only -n cron -t job from Gate B archive.

T-4 vault update (from T-1 plan): Set clinic_portal_base_url to R940 clinic URL; rotate clinic_cron_secret to ONSET_APP_PROD_CRON_SECRET before enabling jobs.

---

T-4 pre-cutover checklist (informational)

Not blocking T-3 (now closed). Required before DNS cutover (T4-0).

Clinic portal (T4-0c)

• [ ] Migrate /patients/new off client supabase.auth.getUser() → Last1 session / service role

Cron

• [ ] Restore or recreate 3 cron.job rows on VM 119 (see SQL above)
• [ ] Confirm vault.secrets: clinic_cron_secret, clinic_portal_base_url
• [ ] Point vault URLs to R940 clinic; manual SELECT trigger_clinic_protocol_deliveries(); → HTTP 200
• [ ] Weekly summary: update hardcoded URL in function OR refactor to use vault base_url
• [ ] Verify Monday 06:00 PDT weekly job after first post-cutover week

Storage

• [ ] Align self-hosted storage.* schema OR API-migrate 20 objects (~3.2 MB)
• [ ] Recreate buckets: templates (public), clinic-support-attachments (private)
• [ ] Verify public templates/posts/*.png URLs resolve via R940 Kong or edge hostname
• [ ] Confirm n8n Onset Daily Publisher workflow still reaches template objects

Gate verification

• [ ] SELECT COUNT(*) FROM cron.job; → 3
• [ ] SELECT COUNT(*) FROM storage.objects; → 20
• [ ] End-to-end clinic cron smoke (all 3 routes)
• [ ] Storage spot-check (1 public + 1 private object)

---

STOP — Next gate

Item	Status
Gate B + PHI	PASS (acknowledged)
Sub-audit A (storage)	COMPLETE
Sub-audit B (cron)	COMPLETE
Network inventory (.100 iDRAC)	Logged
T-3 auth unification	CLOSED — TIER_4_ONSET_T3_GATE_F_REPORT.md
T-4 edge + DNS	BLOCKED — await Ryan T-4 go

Ryan action: Review TIER_4_ONSET_T4_EXECUTION_PLAN.md; reply T-4 go to authorize T4-0 pre-cutover + tunnel work.