Claire erd ver2

Tables (16)

audit_event

  • event_id — BIGINT — Primary key — NOT NULL
  • trace_id — VARCHAR(36) — Foreign key → audit_trace.trace_id
  • ts — TIMESTAMP
  • event_type — VARCHAR(100)
  • patient_id — VARCHAR(64)
  • drug_code — VARCHAR(50)
  • rule_name — VARCHAR(100)
  • outcome — VARCHAR(100)
  • latency_ms — INTEGER
  • error_code — VARCHAR(100)
  • message — TEXT
  • payload_json — TEXT

audit_trace

  • trace_id — VARCHAR(36) — Primary key — NOT NULL
  • created_at — TIMESTAMP
  • patient_id — VARCHAR(64)
  • patient_prescription_drug_id — VARCHAR(200)
  • prescription_id — VARCHAR(128)
  • prescription_date — DATE
  • drug_code — VARCHAR(50)
  • category — VARCHAR(50)
  • issued_at — TIMESTAMP
  • status — VARCHAR(50)
  • completed_at — TIMESTAMP

case_order_snapshot

  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • order_id — VARCHAR(128)
  • order_date — DATE
  • order_name — VARCHAR(200)
  • prescription_id — VARCHAR(128)
  • prescription_date — DATE
  • prescription_name — VARCHAR(200)
  • drug_code — VARCHAR(50)
  • suga_code — VARCHAR(50)
  • edi_code — VARCHAR(50)
  • procedure_code — VARCHAR(50)
  • order_qty — NUMERIC(12,2)
  • payload — TEXT

case_patient_snapshot

  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • patient_id — VARCHAR(64) — NOT NULL
  • patient_name — VARCHAR(100)
  • sex — VARCHAR(10)
  • birth_date — DATE
  • age — INTEGER
  • department — VARCHAR(100)
  • payload — TEXT

case_raw_payload

  • raw_id — BIGINT — Primary key — NOT NULL
  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • source — VARCHAR(50)
  • source_endpoint — VARCHAR(200)
  • source_screen — VARCHAR(200)
  • payload — TEXT — NOT NULL
  • received_at — TIMESTAMP

dementia_review_context

  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • dementia_procedure_code — VARCHAR(50)
  • dementia_disease_code — VARCHAR(50)
  • exam_date — DATE
  • dose_mg — NUMERIC(10,2)
  • mmse_score — INTEGER
  • mmse_date — DATE
  • cdr_score — NUMERIC(3,1)
  • cdr_date — DATE
  • gds_score — INTEGER
  • gds_date — DATE
  • long_term_care_grade — VARCHAR(50)
  • reevaluation_period — VARCHAR(100)
  • payload — TEXT

hepatitis_review_context

  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • test_code — VARCHAR(50)
  • test_name — VARCHAR(200)
  • hbsag_positive — BOOLEAN
  • alt — NUMERIC(10,2)
  • ast — NUMERIC(10,2)
  • hbv_dna_iu_ml — NUMERIC(18,2)
  • is_screening — BOOLEAN
  • self_pay_ratio — NUMERIC(5,2)
  • payload — TEXT

osteoporosis_review_context

  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • osteo_disease_code — VARCHAR(50)
  • osteo_diagnosis_codes — TEXT
  • fracture_yn — BOOLEAN
  • fracture_site — VARCHAR(100)
  • bmd_order_date — DATE
  • bmd_verify_result — VARCHAR(100)
  • bmd_results_summary — TEXT
  • bmd_t_score — NUMERIC(5,2)
  • bmd_bone_density — NUMERIC(10,3)
  • bmd_exam_site — VARCHAR(100)
  • dxa_order_date — DATE
  • dxa_verify_result — VARCHAR(100)
  • dxa_results_summary — TEXT
  • dxa_t_score — NUMERIC(5,2)
  • qct_order_date — DATE
  • qct_verify_result — VARCHAR(100)
  • qct_results_summary — TEXT
  • checkup_date — DATE
  • checkup_t_score — NUMERIC(5,2)
  • months_since_last — INTEGER
  • benefit_months — INTEGER
  • latest_remark — TEXT
  • medication_history_json — TEXT
  • payload — TEXT

pregnancy_review_context

  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • visit_date — DATE
  • review_base_date — DATE
  • target_start_date — DATE
  • target_end_date — DATE
  • pregnancy_diagnosis_code — VARCHAR(50)
  • gestational_week — INTEGER
  • trimester — VARCHAR(20)
  • claimed_dx_code — VARCHAR(50)
  • expected_dx_code — VARCHAR(50)
  • dx_mismatch — BOOLEAN
  • ultrasound_type — VARCHAR(50)
  • pregnancy_targets_json — TEXT
  • payload — TEXT

review_case

  • review_case_id — BIGINT — Primary key — NOT NULL
  • patient_prescription_drug_id — VARCHAR(200) — NOT NULL
  • trace_id — VARCHAR(36) — NOT NULL
  • patient_id — VARCHAR(64) — NOT NULL
  • prescription_id — VARCHAR(128)
  • review_item_id — BIGINT — Foreign key → review_item_master.review_item_id
  • review_category — VARCHAR(50) — NOT NULL
  • review_flow_type — VARCHAR(50) — NOT NULL
  • care_setting — VARCHAR(30)
  • review_base_date — DATE — NOT NULL
  • prescription_date — DATE
  • review_requested_at — TIMESTAMP
  • target_start_date — DATE
  • target_end_date — DATE
  • issued_at — TIMESTAMP
  • drug_code — VARCHAR(50)
  • suga_code — VARCHAR(50)
  • edi_code — VARCHAR(50)
  • rule_name — VARCHAR(100)
  • department — VARCHAR(100)
  • collection_status — VARCHAR(30)
  • data_status — TEXT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

review_item_master

  • review_item_id — BIGINT — Primary key — NOT NULL
  • review_category — VARCHAR(50) — NOT NULL
  • review_item_name — VARCHAR(200) — NOT NULL
  • review_flow_type — VARCHAR(50)
  • care_setting — VARCHAR(30)
  • drug_code — VARCHAR(50)
  • suga_code — VARCHAR(50)
  • edi_code — VARCHAR(50)
  • procedure_code — VARCHAR(50)
  • entry_rule_code — VARCHAR(100)
  • writeback_type — VARCHAR(100)
  • active_yn — CHAR(1)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

rule_context_flat

  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • patient_prescription_drug_id — VARCHAR(200) — NOT NULL
  • trace_id — VARCHAR(36) — NOT NULL
  • patient_id — VARCHAR(64) — NOT NULL
  • prescription_id — VARCHAR(128)
  • prescription_date — DATE
  • review_base_date — DATE
  • drug_code — VARCHAR(50)
  • suga_code — VARCHAR(50)
  • edi_code — VARCHAR(50)
  • category — VARCHAR(50)
  • rule_name — VARCHAR(100)
  • collection_status — VARCHAR(30)
  • data_status — TEXT
  • patient_name — VARCHAR(100)
  • sex — VARCHAR(10)
  • birth_date — DATE
  • age — INTEGER
  • department — VARCHAR(100)
  • prescription_name — VARCHAR(200)
  • procedure_code — VARCHAR(50)
  • osteo_disease_code — VARCHAR(50)
  • osteo_diagnosis_codes — TEXT
  • fracture_yn — BOOLEAN
  • fracture_site — VARCHAR(100)
  • bmd_order_date — DATE
  • bmd_verify_result — VARCHAR(100)
  • bmd_results_summary — TEXT
  • bmd_t_score — NUMERIC(5,2)
  • bmd_bone_density — NUMERIC(10,3)
  • bmd_exam_site — VARCHAR(100)
  • dxa_order_date — DATE
  • dxa_verify_result — VARCHAR(100)
  • dxa_results_summary — TEXT
  • dxa_t_score — NUMERIC(5,2)
  • qct_order_date — DATE
  • qct_verify_result — VARCHAR(100)
  • qct_results_summary — TEXT
  • checkup_date — DATE
  • checkup_t_score — NUMERIC(5,2)
  • months_since_last — INTEGER
  • benefit_months — INTEGER
  • latest_remark — TEXT
  • medication_history_json — TEXT
  • dementia_disease_code — VARCHAR(50)
  • mmse_score — INTEGER
  • mmse_date — DATE
  • cdr_score — NUMERIC(3,1)
  • cdr_date — DATE
  • gds_score — INTEGER
  • gds_date — DATE
  • dose_mg — NUMERIC(10,2)
  • reevaluation_period — VARCHAR(100)
  • hepatitis_test_code — VARCHAR(50)
  • hepatitis_test_name — VARCHAR(200)
  • hbsag_positive — BOOLEAN
  • alt — NUMERIC(10,2)
  • ast — NUMERIC(10,2)
  • hbv_dna_iu_ml — NUMERIC(18,2)
  • is_screening — BOOLEAN
  • … +22

rule_result

  • rule_result_id — BIGINT — Primary key — NOT NULL
  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • patient_prescription_drug_id — VARCHAR(200) — NOT NULL
  • trace_id — VARCHAR(36) — NOT NULL
  • rule_name — VARCHAR(100) — NOT NULL
  • rule_version — VARCHAR(50)
  • processed_at — TIMESTAMP
  • eligibility_result — VARCHAR(50) — NOT NULL
  • review_reason — TEXT
  • missing_data — TEXT
  • rule_input_snapshot — TEXT
  • result_payload — TEXT
  • callback_payload — TEXT
  • writeback_required — BOOLEAN
  • writeback_type — VARCHAR(100)
  • writeback_target — TEXT
  • writeback_payload — TEXT
  • writeback_status — VARCHAR(50)
  • writeback_response — TEXT
  • writeback_error — TEXT
  • writeback_at — TIMESTAMP

wdress_bundle_detail

  • bundle_id — BIGINT — Primary key — NOT NULL
  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • line_no — INTEGER
  • benefit_flag — VARCHAR(10)
  • color_type — VARCHAR(20)
  • procedure_code — VARCHAR(50)
  • prescription_date — DATE
  • total_qty — INTEGER
  • payload — TEXT

wdress_review_context

  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • wdress_prescription_date — DATE
  • wdress_procedure_code — VARCHAR(50)
  • target_start_date — DATE
  • target_end_date — DATE
  • benefit_flag — VARCHAR(10)
  • admission_days — INTEGER
  • wound_stage — VARCHAR(50)
  • dressing_used — VARCHAR(100)
  • total_qty — INTEGER
  • max_benefit_qty — INTEGER
  • is_collagen — BOOLEAN
  • collagen_eligible — BOOLEAN
  • collagen_reason — VARCHAR(200)
  • bundle_list_json — TEXT
  • payload — TEXT

writeback_log

  • writeback_id — BIGINT — Primary key — NOT NULL
  • rule_result_id — BIGINT — Foreign key → rule_result.rule_result_id
  • review_case_id — BIGINT — Foreign key → review_case.review_case_id — NOT NULL
  • patient_prescription_drug_id — VARCHAR(200)
  • trace_id — VARCHAR(36)
  • writeback_type — VARCHAR(100)
  • patient_id — VARCHAR(64)
  • prescription_id — VARCHAR(128)
  • request_payload — TEXT
  • before_payload — TEXT
  • after_payload — TEXT
  • response_payload — TEXT
  • success — BOOLEAN
  • error_message — TEXT
  • created_at — TIMESTAMP