`db/migrations/0011_health_readings.sql`

38 lines
-- 0011_health_readings.sql
-- Health / Vitals tracking: blood pressure history + Apple Watch / device readings.
--   health_reading — one measurement (BP pair, heart rate, weight, SpO2, glucose, …)
--                    for a household member, from Apple Health export, the Health Auto
--                    Export app (JSON POST), a connected device, or manual entry.
-- Sensitive medical data — same consent/audit posture as the medication tables.
-- Idempotent. Safe to re-run.

BEGIN;

CREATE TABLE IF NOT EXISTS health_reading (
  id            text PRIMARY KEY,
  user_id       text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  person_id     text REFERENCES person(id) ON DELETE SET NULL,   -- null = account owner
  metric        text NOT NULL,        -- blood_pressure|heart_rate|resting_heart_rate|weight|
                                       -- spo2|blood_glucose|steps|hrv|respiratory_rate|body_temperature|vo2max
  systolic      numeric(6,2),         -- BP only
  diastolic     numeric(6,2),         -- BP only
  value         numeric(12,3),        -- single-value metrics (hr, weight, spo2, …)
  unit          text,                 -- mmHg | bpm | lb | kg | % | mg/dL | count | ms | °F …
  category      text,                 -- BP: normal|elevated|hypertension_1|hypertension_2|crisis
  measured_at   timestamptz NOT NULL,
  source        text NOT NULL DEFAULT 'manual',  -- manual|apple-health-export|health-auto-export|device
  device_name   text,                 -- e.g. "Apple Watch", "Omron", "Withings"
  external_id   text,                 -- stable id from the source, for dedup on re-import
  notes         text,
  metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- Dedup: the same source reading never lands twice.
CREATE UNIQUE INDEX IF NOT EXISTS health_reading_ext_idx
  ON health_reading (user_id, external_id) WHERE external_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS health_reading_lookup_idx
  ON health_reading (user_id, person_id, metric, measured_at DESC);

COMMIT;