Case Management Data Specifications

CREATE TABLE case_reviews (
    case_id                  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id               BIGINT NOT NULL,
    encounter_id             BIGINT NOT NULL,
    case_manager_id          BIGINT,
    department_id            BIGINT,
    facility_id              BIGINT,
    case_status              VARCHAR(20) NOT NULL DEFAULT 'open',
    case_type                VARCHAR(30) NOT NULL DEFAULT 'inpatient_um',
    priority                 VARCHAR(20) NOT NULL DEFAULT 'routine',
    assigned_datetime        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expected_discharge_date  DATE,
    actual_discharge_date    DATE,
    discharge_disposition_code VARCHAR(10),
    readmission_risk_score   NUMERIC(5,2),
    is_readmission_within_30d BOOLEAN NOT NULL DEFAULT FALSE,
    closed_datetime          TIMESTAMP,
    created_by               BIGINT NOT NULL,
    created_datetime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by               BIGINT,
    updated_datetime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_case_reviews_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_case_reviews_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_case_reviews_case_manager
        FOREIGN KEY (case_manager_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_case_reviews_department
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_case_reviews_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_case_reviews_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_case_reviews_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_case_reviews_status
        CHECK (case_status IN ('open','on_hold','closed','cancelled')),
    CONSTRAINT chk_case_reviews_type
        CHECK (case_type IN ('inpatient_um','observation_um','ed_case','care_coordination')),
    CONSTRAINT chk_case_reviews_priority
        CHECK (priority IN ('low','routine','high','critical')),
    CONSTRAINT chk_case_reviews_risk_score
        CHECK (readmission_risk_score IS NULL OR (readmission_risk_score >= 0 AND readmission_risk_score <= 100))
);

CREATE INDEX idx_case_reviews_patient ON case_reviews(patient_id);
CREATE INDEX idx_case_reviews_encounter ON case_reviews(encounter_id);
CREATE INDEX idx_case_reviews_manager_status ON case_reviews(case_manager_id, case_status);
CREATE INDEX idx_case_reviews_expected_discharge ON case_reviews(expected_discharge_date)
    WHERE case_status = 'open';
CREATE INDEX idx_case_reviews_priority ON case_reviews(priority)
    WHERE case_status = 'open';

CREATE TABLE utilization_reviews (
    review_id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    case_id                   BIGINT NOT NULL,
    review_type               VARCHAR(20) NOT NULL,
    review_date               DATE NOT NULL DEFAULT CURRENT_DATE,
    reviewer_id               BIGINT NOT NULL,
    criteria_tool             VARCHAR(50) NOT NULL,
    criteria_version          VARCHAR(20),
    criteria_met              BOOLEAN NOT NULL DEFAULT FALSE,
    clinical_justification    TEXT NOT NULL,
    level_of_care             VARCHAR(30) NOT NULL,
    recommended_action        VARCHAR(30) NOT NULL,
    next_review_date          DATE,
    payer_notification_required BOOLEAN NOT NULL DEFAULT FALSE,
    payer_notified            BOOLEAN NOT NULL DEFAULT FALSE,
    related_csa_id            BIGINT,
    avoidable_days            INT,
    notes                     TEXT,
    created_datetime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                BIGINT NOT NULL,
    updated_datetime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                BIGINT,

    CONSTRAINT fk_ur_case
        FOREIGN KEY (case_id) REFERENCES case_reviews(case_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ur_reviewer
        FOREIGN KEY (reviewer_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_ur_csa
        FOREIGN KEY (related_csa_id) REFERENCES continued_stay_authorizations(csa_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_ur_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_ur_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_ur_type
        CHECK (review_type IN ('admission','continued_stay','retrospective','discharge')),
    CONSTRAINT chk_ur_recommended_action
        CHECK (recommended_action IN ('continue_inpatient','downgrade_observation','discharge_plan','transfer_higher_level')),
    CONSTRAINT chk_ur_avoidable_days
        CHECK (avoidable_days IS NULL OR avoidable_days >= 0)
);

CREATE INDEX idx_ur_case ON utilization_reviews(case_id);
CREATE INDEX idx_ur_type_date ON utilization_reviews(review_type, review_date);
CREATE INDEX idx_ur_reviewer_date ON utilization_reviews(reviewer_id, review_date);
CREATE INDEX idx_ur_next_review ON utilization_reviews(next_review_date)
    WHERE payer_notification_required = TRUE;

CREATE TABLE um_criteria_evaluations (
    eval_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    review_id           BIGINT NOT NULL,
    criteria_category   VARCHAR(50) NOT NULL,
    criteria_item_code  VARCHAR(100) NOT NULL,
    criteria_item_text  TEXT NOT NULL,
    met                 BOOLEAN NOT NULL DEFAULT FALSE,
    evidence            TEXT,
    notes               TEXT,
    created_datetime    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by          BIGINT NOT NULL,

    CONSTRAINT fk_um_eval_review
        FOREIGN KEY (review_id) REFERENCES utilization_reviews(review_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_um_eval_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE INDEX idx_um_eval_review ON um_criteria_evaluations(review_id);
CREATE INDEX idx_um_eval_item ON um_criteria_evaluations(criteria_item_code);

CREATE TABLE discharge_plans (
    plan_id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    case_id                 BIGINT NOT NULL,
    patient_id              BIGINT NOT NULL,
    encounter_id            BIGINT NOT NULL,
    planned_disposition_code VARCHAR(10) NOT NULL,
    barriers_identified     TEXT,
    services_needed         TEXT,
    target_discharge_date   DATE,
    plan_status             VARCHAR(20) NOT NULL DEFAULT 'draft',
    patient_family_agreed   BOOLEAN NOT NULL DEFAULT FALSE,
    education_provided      BOOLEAN NOT NULL DEFAULT FALSE,
    created_by              BIGINT NOT NULL,
    created_datetime        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by              BIGINT,
    updated_datetime        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_dp_case
        FOREIGN KEY (case_id) REFERENCES case_reviews(case_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_dp_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_dp_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_dp_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_dp_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_dp_status
        CHECK (plan_status IN ('draft','in_progress','ready','completed','cancelled'))
);

CREATE INDEX idx_dp_case ON discharge_plans(case_id);
CREATE INDEX idx_dp_patient ON discharge_plans(patient_id);
CREATE INDEX idx_dp_status ON discharge_plans(plan_status);
CREATE INDEX idx_dp_target_date ON discharge_plans(target_discharge_date)
    WHERE plan_status IN ('in_progress','ready');

CREATE TABLE discharge_plan_tasks (
    task_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    plan_id             BIGINT NOT NULL,
    task_description    VARCHAR(200) NOT NULL,
    task_type           VARCHAR(50),
    assigned_to         BIGINT,
    due_date            DATE,
    status              VARCHAR(20) NOT NULL DEFAULT 'pending',
    completed_datetime  TIMESTAMP,
    notes               TEXT,
    created_datetime    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by          BIGINT NOT NULL,

    CONSTRAINT fk_dpt_plan
        FOREIGN KEY (plan_id) REFERENCES discharge_plans(plan_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_dpt_assigned_to
        FOREIGN KEY (assigned_to) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_dpt_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_dpt_status
        CHECK (status IN ('pending','in_progress','completed','cancelled'))
);

CREATE INDEX idx_dpt_plan ON discharge_plan_tasks(plan_id);
CREATE INDEX idx_dpt_assigned_status ON discharge_plan_tasks(assigned_to, status);
CREATE INDEX idx_dpt_due_date ON discharge_plan_tasks(due_date)
    WHERE status IN ('pending','in_progress');

CREATE TABLE care_coordination_notes (
    note_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    case_id           BIGINT NOT NULL,
    patient_id        BIGINT NOT NULL,
    coordinator_id    BIGINT NOT NULL,
    activity_type     VARCHAR(30) NOT NULL,
    description       TEXT NOT NULL,
    contact_made_with VARCHAR(50),
    outcome           VARCHAR(50),
    note_datetime     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    patient_visible   BOOLEAN NOT NULL DEFAULT FALSE,
    created_datetime  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by        BIGINT NOT NULL,

    CONSTRAINT fk_ccn_case
        FOREIGN KEY (case_id) REFERENCES case_reviews(case_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ccn_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_ccn_coordinator
        FOREIGN KEY (coordinator_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_ccn_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE INDEX idx_ccn_case_datetime ON care_coordination_notes(case_id, note_datetime);
CREATE INDEX idx_ccn_patient_datetime ON care_coordination_notes(patient_id, note_datetime);
CREATE INDEX idx_ccn_coordinator ON care_coordination_notes(coordinator_id, note_datetime);

CREATE TABLE continued_stay_authorizations (
    csa_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    case_id             BIGINT NOT NULL,
    payer_id            BIGINT NOT NULL,
    plan_id             BIGINT,
    prior_auth_id       BIGINT,
    auth_number         VARCHAR(50),
    requested_days      INT NOT NULL,
    approved_days       INT,
    request_date        DATE NOT NULL DEFAULT CURRENT_DATE,
    response_date       DATE,
    status              VARCHAR(20) NOT NULL DEFAULT 'pending',
    denial_reason_code  VARCHAR(50),
    appeal_status       VARCHAR(20),
    auth_start_date     DATE,
    auth_end_date       DATE,
    notes               TEXT,
    created_datetime    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by          BIGINT NOT NULL,
    updated_datetime    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by          BIGINT,

    CONSTRAINT fk_csa_case
        FOREIGN KEY (case_id) REFERENCES case_reviews(case_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_csa_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_csa_plan
        FOREIGN KEY (plan_id) REFERENCES insurance_plans(plan_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_csa_prior_auth
        FOREIGN KEY (prior_auth_id) REFERENCES prior_authorizations(auth_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_csa_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_csa_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_csa_status
        CHECK (status IN ('pending','approved','partially_approved','denied','expired')),
    CONSTRAINT chk_csa_appeal_status
        CHECK (appeal_status IS NULL OR appeal_status IN ('none','planned','submitted','upheld','overturned')),
    CONSTRAINT chk_csa_requested_days
        CHECK (requested_days > 0),
    CONSTRAINT chk_csa_approved_days
        CHECK (approved_days IS NULL OR approved_days >= 0),
    CONSTRAINT chk_csa_response_date
        CHECK (response_date IS NULL OR response_date >= request_date),
    CONSTRAINT chk_csa_auth_dates
        CHECK (auth_end_date IS NULL OR auth_start_date IS NULL OR auth_end_date >= auth_start_date)
);

CREATE INDEX idx_csa_case ON continued_stay_authorizations(case_id);
CREATE INDEX idx_csa_payer_status ON continued_stay_authorizations(payer_id, status);
CREATE INDEX idx_csa_auth_period ON continued_stay_authorizations(auth_end_date)
    WHERE status IN ('approved','partially_approved');

CREATE TABLE readmission_risk_assessments (
    assessment_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id            BIGINT NOT NULL,
    encounter_id          BIGINT NOT NULL,
    assessment_datetime   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    risk_score            NUMERIC(5,2) NOT NULL,
    risk_level            VARCHAR(20) NOT NULL,
    score_components_json JSONB NOT NULL,
    interventions_planned TEXT,
    assessed_by           BIGINT NOT NULL,
    created_datetime      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_rra_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rra_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rra_assessed_by
        FOREIGN KEY (assessed_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_rra_risk_score
        CHECK (risk_score >= 0),
    CONSTRAINT chk_rra_risk_level
        CHECK (risk_level IN ('low','medium','high','very_high'))
);

CREATE INDEX idx_rra_patient_datetime ON readmission_risk_assessments(patient_id, assessment_datetime);
CREATE INDEX idx_rra_encounter ON readmission_risk_assessments(encounter_id);
CREATE INDEX idx_rra_risk_level ON readmission_risk_assessments(risk_level);

{
  "los_days": 7,
  "ed_visits_6m": 3,
  "comorbidity_score": 5,
  "acuity": "emergent"
}

CREATE TABLE case_management_assignments (
    assignment_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    case_manager_id    BIGINT NOT NULL,
    department_id      BIGINT,
    service_line       VARCHAR(50),
    assignment_type    VARCHAR(20) NOT NULL DEFAULT 'primary',
    max_caseload       INT NOT NULL DEFAULT 20,
    current_caseload   INT NOT NULL DEFAULT 0,
    is_active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_datetime   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by         BIGINT NOT NULL,
    updated_datetime   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by         BIGINT,

    CONSTRAINT fk_cma_case_manager
        FOREIGN KEY (case_manager_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_cma_department
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_cma_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_cma_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_cma_assignment_type
        CHECK (assignment_type IN ('primary','backup')),
    CONSTRAINT chk_cma_max_caseload
        CHECK (max_caseload > 0),
    CONSTRAINT chk_cma_current_caseload
        CHECK (current_caseload >= 0)
);

CREATE INDEX idx_cma_manager_active ON case_management_assignments(case_manager_id)
    WHERE is_active = TRUE;
CREATE INDEX idx_cma_dept_service ON case_management_assignments(department_id, service_line)
    WHERE is_active = TRUE;