Trial Gap Finder

-- Arbitrant core schema (Neon Postgres)

CREATE TABLE indications (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name          text NOT NULL,
  orphan_code   text,
  therapeutic   text,
  patient_burden int,
  created_at    timestamptz DEFAULT now()
);

CREATE TABLE trials (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  nct_id          text UNIQUE NOT NULL,
  indication_id   uuid REFERENCES indications,
  phase           text,
  status          text,
  sponsor         text,
  start_date      date
);

CREATE TABLE gap_scores (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  indication_id   uuid REFERENCES indications,
  score           numeric(5,2),
  trial_density   int,
  patent_prox     numeric(5,2),
  computed_at     timestamptz DEFAULT now()
);

CREATE TABLE designations (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  indication_id   uuid REFERENCES indications,
  type            text, -- orphan | breakthrough | fast_track | rmat
  eligible        boolean,
  precedents      jsonb
);

CREATE TABLE watchlists (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         uuid, -- Supabase auth uid
  indication_id   uuid REFERENCES indications,
  alert_threshold numeric(5,2),
  created_at      timestamptz DEFAULT now()
);

CREATE TABLE clincol_signals (
  indication_id   uuid REFERENCES indications,
  search_volume_7d int, -- aggregated from clincol.org searches
  recorded_at     date
);