Care Management Guide

-- Care Plan
CREATE TABLE care_plan (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id            UUID NOT NULL,
  
  -- Plan Details
  plan_name             VARCHAR(255),
  plan_type             VARCHAR(100),              -- chronic_care, transitional, behavioral
  status                VARCHAR(50),               -- active, completed, suspended
  
  -- Risk Stratification
  risk_level            VARCHAR(20),               -- high, medium, low
  risk_score            DECIMAL(5,2),
  stratification_date   DATE,
  
  -- Goals
  primary_goal          TEXT,
  secondary_goals       TEXT[],
  target_outcomes       JSONB,
  
  -- Care Team
  care_manager_id       UUID,
  primary_physician_id  UUID,
  care_team_members     JSONB,                     -- Array of team member details
  
  -- Dates
  start_date            DATE NOT NULL,
  end_date              DATE,
  next_review_date      DATE,
  
  -- Consent
  patient_consent       BOOLEAN DEFAULT FALSE,
  consent_date          DATE,
  
  created_at            TIMESTAMP DEFAULT NOW(),
  updated_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_careplan_patient ON care_plan(patient_id);
CREATE INDEX idx_careplan_status ON care_plan(status);
CREATE INDEX idx_careplan_risk ON care_plan(risk_level);

-- Care Plan Interventions
CREATE TABLE care_intervention (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  care_plan_id          UUID NOT NULL REFERENCES care_plan(id),
  
  -- Intervention Details
  intervention_type     VARCHAR(100),              -- medication_review, education, referral
  intervention_name     VARCHAR(255),
  description           TEXT,
  
  -- Assignment
  assigned_to           UUID,
  assigned_role         VARCHAR(100),              -- care_manager, nurse, social_worker
  
  -- Scheduling
  frequency             VARCHAR(50),               -- weekly, monthly, as_needed
  scheduled_date        DATE,
  completed_date        DATE,
  
  -- Status
  status                VARCHAR(50),               -- planned, in_progress, completed, cancelled
  
  -- Outcomes
  outcome_notes         TEXT,
  barriers_identified   TEXT[],
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_intervention_plan ON care_intervention(care_plan_id);
CREATE INDEX idx_intervention_assigned ON care_intervention(assigned_to);

-- Patient Touchpoints
CREATE TABLE care_touchpoint (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id            UUID NOT NULL,
  care_plan_id          UUID REFERENCES care_plan(id),
  
  -- Contact Details
  contact_type          VARCHAR(50),               -- phone, video, home_visit, message
  contact_date          TIMESTAMP NOT NULL,
  duration_minutes      INTEGER,
  
  -- Staff
  staff_id              UUID,
  staff_role            VARCHAR(100),
  
  -- Content
  contact_reason        VARCHAR(255),
  clinical_notes        TEXT,
  patient_concerns      TEXT[],
  action_items          TEXT[],
  
  -- Follow-up
  follow_up_needed      BOOLEAN DEFAULT FALSE,
  follow_up_date        DATE,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_touchpoint_patient ON care_touchpoint(patient_id);
CREATE INDEX idx_touchpoint_date ON care_touchpoint(contact_date);

-- Care Gaps
CREATE TABLE care_gap (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id            UUID NOT NULL,
  
  -- Gap Details
  gap_type              VARCHAR(100),              -- medication_adherence, screening_overdue, follow_up_missed
  gap_description       TEXT,
  measure_name          VARCHAR(255),              -- e.g., HbA1c screening, colonoscopy
  
  -- Clinical Context
  condition_related     VARCHAR(100),
  severity              VARCHAR(20),               -- high, medium, low
  
  -- Timeline
  identified_date       DATE NOT NULL,
  due_date              DATE,
  closed_date           DATE,
  
  -- Status
  status                VARCHAR(50),               -- open, in_progress, closed
  closure_reason        TEXT,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_gap_patient ON care_gap(patient_id);
CREATE INDEX idx_gap_status ON care_gap(status);

-- Patient Stratification History
CREATE TABLE patient_stratification (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id            UUID NOT NULL,
  
  -- Risk Assessment
  risk_level            VARCHAR(20),               -- high, medium, low
  risk_score            DECIMAL(5,2),
  
  -- Contributing Factors
  chronic_conditions    TEXT[],
  recent_utilization    JSONB,                     -- ER visits, hospitalizations
  medication_count      INTEGER,
  social_determinants   TEXT[],
  
  -- Assessment Details
  assessment_date       DATE NOT NULL,
  assessed_by           UUID,
  model_version         VARCHAR(50),
  
  -- Next Review
  next_assessment_date  DATE,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_strat_patient ON patient_stratification(patient_id);
CREATE INDEX idx_strat_date ON patient_stratification(assessment_date DESC);