`db/migrations/0008_household_medications.sql`

66 lines
-- 0008_household_medications.sql
--   person             — household members (self, spouse, dependents…)
--   medication         — meds a person takes (self-entered; health data — see AGENTS.md gate)
--   medication_recall  — FDA (openFDA) drug-enforcement matches for a medication
-- Idempotent. Safe to re-run.

BEGIN;

CREATE TABLE IF NOT EXISTS person (
  id             text PRIMARY KEY,
  user_id        text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  relation       text NOT NULL DEFAULT 'other',   -- self|spouse|partner|child|dependent|parent|other
  full_name      text NOT NULL,
  nickname       text,
  dob            date,
  phone          text,
  email          text,
  notes          text,
  metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS person_user_idx ON person (user_id, relation);

CREATE TABLE IF NOT EXISTS medication (
  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,   -- who takes it (null = account owner)
  name           text NOT NULL,                                   -- brand/name as taken
  generic_name   text,
  dosage         text,                                            -- e.g. "20 mg"
  frequency      text,                                            -- e.g. "once daily", "2x/day"
  form           text,                                            -- tablet|capsule|liquid|injection|...
  prescriber     text,
  pharmacy       text,
  rx_number      text,
  start_date     date,
  is_active      boolean NOT NULL DEFAULT true,
  notes          text,
  metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS medication_user_idx ON medication (user_id, is_active);
CREATE INDEX IF NOT EXISTS medication_person_idx ON medication (person_id);

CREATE TABLE IF NOT EXISTS medication_recall (
  id                     text PRIMARY KEY,
  user_id                text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  medication_id          text NOT NULL REFERENCES medication(id) ON DELETE CASCADE,
  source                 text NOT NULL DEFAULT 'openFDA',
  recall_number          text,
  classification         text,                     -- Class I / II / III
  status                 text,                      -- Ongoing / Completed / Terminated
  reason                 text,
  product_description     text,
  recall_initiation_date date,
  url                    text,
  raw_jsonb              jsonb,
  matched_at             timestamptz NOT NULL DEFAULT now(),
  UNIQUE (medication_id, recall_number)
);
CREATE INDEX IF NOT EXISTS medication_recall_med_idx ON medication_recall (medication_id);

COMMIT;