`db/migrations/0009_prescription_fills.sql`

36 lines
-- 0009_prescription_fills.sql
--   prescription_fill — pharmacy FILL HISTORY (one row per fill), distinct from `medication`
--     (one row per current med). Imported from a pharmacy "PAT PROFILE FOR TAX" summary.
--     Health data: user-supplied record, audit-logged with a medical-consent marker.
-- Idempotent. Safe to re-run.

BEGIN;

CREATE TABLE IF NOT EXISTS prescription_fill (
  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,   -- whose fill (null = account owner)
  fill_date      date,
  drug_name      text NOT NULL,                                   -- as printed, e.g. "FLUOXETINE HCL 20 MG CAPS"
  rx_number      text,
  refill         int,
  quantity       numeric,
  days_supply    int,
  doctor         text,                                            -- prescriber "LAST, FIRST"
  pharmacy       text,
  ndc            text,
  dea            text,
  plan_name      text,                                            -- CARE33 / TRIO ACO HMO
  plan_paid      numeric(12,2),                                   -- what the insurance PLAN paid
  patient_paid   numeric(12,2),                                   -- out-of-pocket (0.00 on this profile)
  source         text,                                            -- provenance tag (idempotent re-load key)
  raw_text       text,                                            -- original OCR line pair (audit trail)
  metadata_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS prescription_fill_user_idx   ON prescription_fill (user_id, fill_date DESC);
CREATE INDEX IF NOT EXISTS prescription_fill_person_idx ON prescription_fill (person_id);
CREATE INDEX IF NOT EXISTS prescription_fill_source_idx ON prescription_fill (source);

COMMIT;