01-projects/longevity

IMPLEMENTATION NOTES 2026 05 21 health export worker

2026-05-21·implementation-notes·status: d1-online

Implementation Notes — Health Auto Export Worker (rdco-health-export)

Context

First scaffold of the longevity/ project in the vault. Founder wants to start ingesting iPhone Health Auto Export (HAE) data into a queryable home for future longevity analysis. This worker is the ingestion endpoint — a dedicated single-purpose subdomain that HAE posts to on schedule.

Spec (frozen by parent — NOT renegotiable)

Decisions & deviations

Decision 1: Wrangler auth via env vars from 1Password

Decision 2: Date handling for R2 key

Decision 3: HAE payload shape — single vs batched

Decision 4: D1 schema simplifications

Decision 5: Session-ID handling

Decision 6: Compatibility date

Decision 7: TypeScript vs JavaScript

Open questions / BLOCKERS

BLOCKER 1: D1 database creation blocked by token permissions

Worker behavior in current "r2-only mode"

Decision 8: Custom domain pattern fix

Audit trail

Provisioning

Smoke tests (all PASS)

# Test Expected Actual
1 No X-API-Key header 401 401 {"error":"unauthorized"}
2 Wrong key 401 401 {"error":"unauthorized"}
3 Right key, empty body {} 400 400 {"error":"missing_data_key", ...}
4 Right key, malformed JSON 400 400 {"error":"malformed_json"}
5 Right key, valid HAE payload 200 ok 200 {"status":"ok", "session_id":"test-session-1", "metrics_count":1, ...}
6 Replay same session-id 200 duplicate 200 {"status":"duplicate", ...}

Resource verification

Files

HAE app configuration (relay to founder)

  1. Open Health Auto Export app on iPhone
  2. Automations → New Automation
  3. Mode: REST API
  4. URL: https://health-export.raydata.co/auto-export
  5. Custom Headers: X-API-Key = (full hex value from 1Password "HAE API Key (Health Auto Export)" credential field, or /tmp/hae-key-2026-05-21.txt)
  6. Format: JSON, batch mode = on (recommended)
  7. Schedule: every X hours (start with hourly; tune later)
  8. Metrics: select all (Worker filters server-side)
  9. Tap "Manual Export Now" to test first push
  10. Watch HAE Activity Log for 200 OK confirmation

Worker logs are visible at: Cloudflare Dashboard → Workers → rdco-health-export → Logs (observability enabled).

Post-deployment event: Key rotation (2026-05-21 10:32 ET)

Trigger: Harness security warning on sub-agent return — the original key value was printed to sub-agent return-value transcript. Per defensive hygiene, rotated the secret immediately rather than relay the exposed value.

Actions taken:

  1. Generated new 256-bit hex key via openssl rand -hex 32 (saved temp at /tmp/hae-key-rotated-2026-05-21.txt, will delete after relay to founder via iMessage)
  2. Uploaded new key as Wrangler Secret HAE_API_KEY (CLOUDFLARE_API_TOKEN sourced from 1Password op://Ray Agent/Cloudflare Ray Account API Key/credential)
  3. Updated 1Password item "HAE API Key (Health Auto Export)" credential field with rotated value
  4. Smoke-tested:
    • Old key → 401 ✓ (rotation effective)
    • New key + empty body → 400 ✓
    • New key + valid HAE payload → 200 ✓ (r2-only mode noted)

Post-rotation surface trail of the new key: 1Password (canonical) + iMessage (delivery to founder for HAE app config) + Worker secret store (encrypted). Old key exposed in sub-agent transcript + this session's earlier output is now functionally dead.

Post-deployment event: D1 unblock + schema growth (2026-05-21 ~11:00 ET)

Trigger: Founder granted D1:Edit permission on the "Cloudflare Ray Account API Key" token.

Actions taken (in order):

  1. D1 database created: rdco-health in region ENAM, database_id 9001e5ae-ad32-4a51-ae81-b158b62d6d88
  2. Schema extended: Added symptoms and medications tables per founder's 2026-05-21 decision. Both use INTEGER PRIMARY KEY AUTOINCREMENT (no natural unique key in HAE entries — payload-level dedupe via R2 idempotency covers retransmits). Both carry a raw_json column as safety-net storage of the original HAE entry. Indexes on date for both.
  3. raw_ingest_log extended: Added symptoms_count + medications_count columns to the audit log so per-payload routing summary is queryable.
  4. Schema applied to remote D1: 6 tables total (daily_summary, workouts, medical_records, symptoms, medications, raw_ingest_log) + 5 indexes. 23 rows written (table + index creation), 0.09 MB.
  5. wrangler.toml updated: D1 binding HEALTH_DB re-enabled with the new database_id.
  6. Worker code rewritten with type-routing across 5 payload shapes:
    • data.metrics → daily_summary (existing METRIC_COL map)
    • data.workouts → workouts (existing)
    • data.symptoms → symptoms (NEW; defensive field parsing; raw_json preserved)
    • data.medications → medications (NEW; defensive dosage coercion; raw_json preserved)
    • data.medical_records → medical_records (NEW; composed PK source:date:category:hash(value) for idempotent upsert; routes Tampa General / Labcorp data when source field is set)
  7. R2-first, D1-best-effort write order codified: R2 write happens BEFORE D1 statements; if D1 batch fails, log + return 200 with notes:"d1_failed:..." rather than 500. R2 archive is source of truth, backfill recovers via the script.
  8. Response shape extended to {status, session_id, written_at, r2_key, d1_tables_written:[...], metrics_count, workouts_count, symptoms_count, medications_count}.
  9. Worker deployed: Version ID ad7df773-fceb-479c-90b9-c1786e328b18. Both bindings active: env.HEALTH_DB (rdco-health D1), env.HEALTH_RAW (rdco-health-raw R2).
  10. Backfill scripts written (NOT YET RUN):
    • ~/Projects/rdco-health-export-worker/scripts/backfill-r2-to-d1.sh — MODE=replay default, re-POSTs every R2 object back through the Worker (idempotent via R2 head check, but r2-only-mode objects short-circuit to duplicate WITHOUT D1 write).
    • ~/Projects/rdco-health-export-worker/scripts/backfill-r2-to-d1.ts — MODE=force; downloads R2 objects via API, applies D1 INSERTs directly via wrangler d1 execute (bypasses R2 dedupe; right mode for the r2-only-mode period). Idempotent on re-run (upserts + delete-then-insert for AUTOINCREMENT tables).

Smoke tests (all PASS)

# Test Expected Actual
7 Health Metrics (HRV) payload 200 + d1_tables_written:["daily_summary","raw_ingest_log"] ✓
8 Symptoms payload 200 + d1_tables_written:["symptoms","raw_ingest_log"] ✓
9 Medications payload 200 + d1_tables_written:["medications","raw_ingest_log"] ✓
10 Combined metrics+symptoms payload 200 + d1_tables_written:["daily_summary","symptoms","raw_ingest_log"] ✓

D1 row counts after smoke tests + real-HAE traffic

Table Count
raw_ingest_log 6 (4 smoke tests + 2 real HAE POSTs)
daily_summary 3
workouts 0
symptoms 2
medications 1
medical_records 0

REAL HAE TRAFFIC OBSERVED (significant finding)

While completing this dispatch, two real HAE POSTs from founder's iPhone landed cleanly during the deploy window:

Both have 12 metrics, but all 12 metric names are flagged as unknown_metric in the audit log:

Implication: The current METRIC_COL map in the Worker covers only 7 metric names (resting_heart_rate, heart_rate_variability, sleep_analysis, step_count, vo2_max, weight_body_mass, body_fat_percentage). The founder's iPhone is sending a wider set. The R2 archive has all 12 metrics' raw JSON preserved — nothing lost — but daily_summary has 0 rows from those real POSTs because none of the names matched.

Decision (deferred to next dispatch): extend METRIC_COL to cover Apple-watch standard metrics. Reasonable extensions:

Also notable: payload size 25-28 MB suggests HAE is sending full history per push, not incremental. This will need attention before too many POSTs accumulate (R2 storage is cheap, but Worker request bandwidth + D1 batch latency will matter). Founder may want to tune the HAE in-app schedule + history window.

Open follow-ups (current snapshot)

Continuation dispatch 2026-05-21 ~15:35 ET — clinical-records + Option B schema

Spec (frozen by parent — NOT renegotiable)

Two parallel builds:

  1. Clinical Records one-time ingest from "Health Record Export" iPhone app (FHIR-shaped JSON, 1.2 MB, 385 records spanning 2018-10-24 → 2025-03-18 across allergies / clinical_vitals / conditions / lab_results / medications categories).
  2. Option B schema restructure for HAE: replace per-metric-column daily_summary with generic daily_metrics table that captures EVERY data-point for every metric the iPhone sends, then backfill all 254+ existing R2 payloads through the new shape.

Build 1 — Clinical Records ingest

Decisions:

Result:

category rows
lab_results 325
clinical_vitals 37
medications 15
conditions 7
allergies 1
TOTAL 385

Build 2 — Option B daily_metrics schema + Worker rewrite + R2 backfill

Decisions:

Backfill (scripts/backfill-metrics-r2-to-d1.py):

Backfill result:

Per-metric breakdown (top 20):

metric_name points earliest latest
step_count 1,392,389 2020-01-01 2026-05-21
walking_running_distance 909,571 2020-01-01 2026-05-21
basal_energy_burned 819,332 2020-01-01 2020-01-10
active_energy 436,392 2020-01-01 2026-05-21
apple_stand_time 165,600 2020-01-01 2026-05-21
flights_climbed 60,138 2020-01-01 2026-05-21
apple_exercise_time 35,620 2020-01-01 2026-05-21
heart_rate 4,353 2020-01-01 2026-05-21
walking_speed 2,424 2024-01-01 2026-05-21
walking_step_length 2,424 2024-01-01 2026-05-21
walking_double_support_percentage 2,169 2024-01-01 2026-05-21
walking_asymmetry_percentage 790 2024-01-01 2026-05-21
sleep_analysis 534 2023-12-31 2026-05-21
apple_stand_hour 141 2020-01-01 2026-05-21
headphone_audio_exposure 64 2024-01-02 2026-01-20
heart_rate_variability 45 2020-01-01 2026-05-21
resting_heart_rate 33 2020-01-01 2026-05-21
blood_oxygen_saturation 22 2026-01-02 2026-05-21
respiratory_rate 22 2026-01-02 2026-05-21
walking_heart_rate_average 10 2020-01-01 2020-01-10

Anomaly to note: basal_energy_burned has 819K points all from 2020-01-01 to 2020-01-10 — that's an unusual concentration (suggests an HAE batch dump of older Apple Watch data that only covered a 10-day historical window). Worth investigating; could be a "select all + history window=oldest-10-days" config in the iPhone app. Founder-decision item.

Files

Open follow-ups