`db/migrations/0002_recalls.sql`

51 lines
-- 0002_recalls.sql — recall_event + recall_match tables
-- Apply: psql -d abrams_os -f db/migrations/0002_recalls.sql

BEGIN;

CREATE TABLE IF NOT EXISTS recall_event (
  id              text PRIMARY KEY,
  authority       text NOT NULL,             -- 'CPSC', 'NHTSA', 'FDA_drug', 'FDA_device', etc.
  external_id     text NOT NULL,             -- e.g. CPSC RecallID
  published_at    timestamptz,
  title           text,
  hazard          text,
  remedy          text,
  url             text,
  product_keys_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb, -- gtins, models, brand, serial_ranges, udi_di
  raw_jsonb       jsonb,                     -- full upstream payload for re-parsing
  ingested_at     timestamptz NOT NULL DEFAULT now(),
  UNIQUE (authority, external_id)
);
CREATE INDEX IF NOT EXISTS recall_event_published_idx ON recall_event (published_at DESC);
CREATE INDEX IF NOT EXISTS recall_event_authority_idx ON recall_event (authority, published_at DESC);
CREATE INDEX IF NOT EXISTS recall_event_keys_idx ON recall_event USING gin (product_keys_jsonb);

CREATE TABLE IF NOT EXISTS recall_match (
  id              text PRIMARY KEY,
  recall_id       text NOT NULL REFERENCES recall_event(id) ON DELETE CASCADE,
  asset_table     text NOT NULL,             -- 'purchase' | 'purchase_item' (later: medical_asset)
  asset_id        text NOT NULL,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  confidence      numeric(3,2) NOT NULL,
  status          text NOT NULL DEFAULT 'pending_review', -- pending_review | confirmed | dismissed
  matched_at      timestamptz NOT NULL DEFAULT now(),
  reviewed_at     timestamptz,
  metadata_jsonb  jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE (recall_id, asset_table, asset_id)
);
CREATE INDEX IF NOT EXISTS recall_match_user_idx ON recall_match (user_id, matched_at DESC);
CREATE INDEX IF NOT EXISTS recall_match_status_idx ON recall_match (status, matched_at DESC);

CREATE TABLE IF NOT EXISTS recall_pull_log (
  id              bigserial PRIMARY KEY,
  authority       text NOT NULL,
  pulled_at       timestamptz NOT NULL DEFAULT now(),
  fetched         integer NOT NULL,
  inserted        integer NOT NULL,
  updated         integer NOT NULL,
  error           text
);

COMMIT;