도로공사ITS

테이블 (50)

ai_inference

  • infer_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • inferred_at — DATETIME — NOT NULL
  • input_ref — VARCHAR(300)
  • model_id — BIGINT — 외래 키 → ai_model.model_id — NOT NULL
  • result_label — VARCHAR(100)
  • score — DECIMAL(10,6)

ai_model

  • model_id — BIGINT
  • purpose — VARCHAR(100) — NOT NULL
  • version — VARCHAR(50) — NOT NULL
  • deployed_date — DATE

ai_review

  • review_id — BIGINT
  • infer_id — BIGINT — 외래 키 → ai_inference.infer_id — NOT NULL
  • reviewed_by_user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • reviewed_at — DATETIME — NOT NULL
  • corrected_label — VARCHAR(100)
  • correction_detail — TEXT

availability_daily

  • avail_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • base_date — DATE — NOT NULL
  • up_seconds — INT — NOT NULL
  • down_seconds — INT — NOT NULL
  • availability_rate — DECIMAL(5,2) — NOT NULL
  • calc_source — VARCHAR(100)

collection_source

  • source_id — BIGINT
  • facility_type — VARCHAR(50)
  • upstream_system — VARCHAR(100)
  • collect_interval_sec — INT
  • protocol — VARCHAR(50)
  • version — VARCHAR(50)

control_action

  • action_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • cmd_id — BIGINT — 외래 키 → control_command.cmd_id — NOT NULL
  • requested_by_user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • exec_path — VARCHAR(100)
  • result_status — VARCHAR(30)
  • result_received_at — DATETIME
  • result_payload — TEXT

control_command

  • cmd_id — BIGINT
  • facility_type — VARCHAR(50) — NOT NULL
  • command_type — VARCHAR(50) — NOT NULL
  • opcode — VARCHAR(50)
  • description — VARCHAR(500)

core_allocation

  • core_alloc_id — BIGINT
  • drawing_id — BIGINT — 외래 키 → fiber_drawing.drawing_id — NOT NULL
  • section_desc — VARCHAR(300)
  • core_info_json — JSON — NOT NULL

cut_report

  • report_id — BIGINT
  • inc_id — BIGINT — 외래 키 → fiber_incident.inc_id — NOT NULL
  • auto_fields_json — JSON
  • user_fields_json — JSON
  • saved_at — DATETIME — NOT NULL

daily_log

  • log_id — BIGINT
  • written_by_user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • written_date — DATE — NOT NULL
  • content — TEXT — NOT NULL
  • checked_by_user_id — BIGINT — 외래 키 → user.user_id
  • checked_at — DATETIME

error_code_def

  • err_def_id — BIGINT
  • facility_type — VARCHAR(50) — NOT NULL
  • component — VARCHAR(100)
  • code_value — VARCHAR(50) — NOT NULL
  • code_name — VARCHAR(200)
  • description — TEXT
  • severity — VARCHAR(20)

error_event

  • err_evt_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • occurred_at — DATETIME — NOT NULL
  • err_def_id — BIGINT — 외래 키 → error_code_def.err_def_id — NOT NULL
  • source_id — BIGINT — 외래 키 → collection_source.source_id
  • raw_payload — TEXT
  • analysis_result — TEXT

event

  • event_id — BIGINT
  • event_type — VARCHAR(50) — NOT NULL
  • occurred_at — DATETIME — NOT NULL
  • facility_id — BIGINT — 외래 키 → facility.facility_id
  • summary — VARCHAR(300)
  • detail — TEXT
  • status — VARCHAR(30)
  • created_by_type — VARCHAR(30)
  • created_by_id — BIGINT

event_target

  • target_type — VARCHAR(20) — 기본 키 — NOT NULL
  • target_id — BIGINT — 기본 키 — NOT NULL
  • event_id — BIGINT — 외래 키 → event.event_id — NOT NULL
  • channel — VARCHAR(30)

facility

  • facility_id — BIGINT
  • facility_type — VARCHAR(50) — NOT NULL
  • org_unit_id — BIGINT — 외래 키 → org_unit.org_unit_id — NOT NULL
  • location_text — VARCHAR(500)
  • geo_point — VARCHAR(100)
  • install_date — DATE
  • vendor — VARCHAR(100)
  • model — VARCHAR(100)
  • op_status — VARCHAR(50)

facility_history

  • hist_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • changed_at — DATETIME — NOT NULL
  • change_type — VARCHAR(50) — NOT NULL
  • change_detail — TEXT

fault_decision

  • decision_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • decided_at — DATETIME — NOT NULL
  • decision_result — VARCHAR(20) — NOT NULL
  • used_metrics — VARCHAR(200)
  • rule_version — VARCHAR(50)
  • is_confirmed — TINYINT(1) — NOT NULL
  • confirmed_by_user_id — BIGINT — 외래 키 → user.user_id

fault_record

  • fault_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • occurred_at — DATETIME — NOT NULL
  • cause — TEXT
  • action — TEXT
  • completed_at — DATETIME

fiber_drawing

  • drawing_id — BIGINT
  • file_ref — VARCHAR(300) — NOT NULL
  • section_desc — VARCHAR(300)
  • registered_at — DATETIME — NOT NULL

fiber_facility

  • fiber_fac_id — BIGINT
  • fiber_fac_type — VARCHAR(50) — NOT NULL
  • location_text — VARCHAR(500)
  • geometry — TEXT
  • org_unit_id — BIGINT — 외래 키 → org_unit.org_unit_id — NOT NULL
  • props_json — JSON

fiber_facility_history

  • fiber_hist_id — BIGINT
  • fiber_fac_id — BIGINT — 외래 키 → fiber_facility.fiber_fac_id — NOT NULL
  • changed_at — DATETIME — NOT NULL
  • change_detail — TEXT

fiber_incident

  • inc_id — BIGINT
  • occurred_at — DATETIME — NOT NULL
  • summary — VARCHAR(300)
  • location_json — JSON
  • work_id — BIGINT — 외래 키 → work_info.work_id
  • nms_evt_id — BIGINT — 외래 키 → nms_event.nms_evt_id
  • fm_evt_id — BIGINT — 외래 키 → fiber_mon_event.fm_evt_id

fiber_mon_device

  • fm_dev_id — BIGINT
  • vendor — VARCHAR(100)
  • protocol — VARCHAR(50)
  • install_location — VARCHAR(500)
  • registered_at — DATETIME — NOT NULL
  • is_active — TINYINT(1) — NOT NULL

fiber_mon_event

  • fm_evt_id — BIGINT
  • received_at — DATETIME — NOT NULL
  • event_type — VARCHAR(100)
  • mile_to_geo_result — TEXT
  • location_json — JSON
  • source_system — VARCHAR(100)

geocode_cache

  • geo_id — BIGINT
  • address_text — VARCHAR(500) — NOT NULL
  • geo_point — VARCHAR(100)
  • reverse_geocode_json — JSON
  • cached_at — DATETIME — NOT NULL

gis_layer

  • layer_id — BIGINT
  • layer_name — VARCHAR(100) — NOT NULL
  • style_json — JSON

gis_object

  • obj_id — BIGINT
  • layer_id — BIGINT — 외래 키 → gis_layer.layer_id — NOT NULL
  • facility_id — BIGINT — 외래 키 → facility.facility_id
  • object_type — VARCHAR(50) — NOT NULL
  • geometry — TEXT — NOT NULL
  • props_json — JSON

inspection

  • insp_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • inspected_at — DATETIME — NOT NULL
  • inspector_user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • result — VARCHAR(100)
  • action_detail — TEXT

login_audit

  • audit_id — BIGINT
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • login_at — DATETIME — NOT NULL
  • login_ip — VARCHAR(64)
  • channel — VARCHAR(50)

nms_event

  • nms_evt_id — BIGINT
  • received_at — DATETIME — NOT NULL
  • event_item — VARCHAR(200)
  • extracted_fault — TEXT
  • location_json — JSON
  • source_system — VARCHAR(100)

oper_def

  • oper_def_id — BIGINT
  • facility_type — VARCHAR(50) — NOT NULL
  • item_name — VARCHAR(100) — NOT NULL
  • unit — VARCHAR(20)

oper_event

  • oper_evt_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • collected_at — DATETIME — NOT NULL
  • oper_def_id — BIGINT — 외래 키 → oper_def.oper_def_id — NOT NULL
  • value_num — DECIMAL(18,4)
  • value_text — VARCHAR(200)
  • source_id — BIGINT — 외래 키 → collection_source.source_id

org_unit

  • org_unit_id — BIGINT
  • parent_org_unit_id — BIGINT
  • org_name — VARCHAR(200) — NOT NULL
  • org_type — VARCHAR(50) — NOT NULL
  • sort_order — INT
  • is_active — TINYINT(1) — NOT NULL

otdr_measure

  • otdr_id — BIGINT
  • fm_dev_id — BIGINT — 외래 키 → fiber_mon_device.fm_dev_id — NOT NULL
  • measured_at — DATETIME — NOT NULL
  • result_file_ref — VARCHAR(300)
  • summary_json — JSON

power_control_action

  • pwr_act_id — BIGINT
  • pwr_dev_id — BIGINT — 외래 키 → power_device.pwr_dev_id — NOT NULL
  • control_type — VARCHAR(20) — NOT NULL
  • requested_by_user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • result_status — VARCHAR(30)
  • result_at — DATETIME
  • result_payload — TEXT

power_device

  • pwr_dev_id — BIGINT
  • install_ref_key — VARCHAR(100)
  • location_text — VARCHAR(500)
  • network_type — VARCHAR(30)
  • registered_at — DATETIME — NOT NULL
  • is_active — TINYINT(1) — NOT NULL

power_status_event

  • pwr_stat_id — BIGINT
  • pwr_dev_id — BIGINT — 외래 키 → power_device.pwr_dev_id — NOT NULL
  • collected_at — DATETIME — NOT NULL
  • item_name — VARCHAR(100) — NOT NULL
  • value_num — DECIMAL(18,4)
  • value_text — VARCHAR(200)

rental_asset

  • rental_asset_id — BIGINT
  • fiber_fac_id — BIGINT — 외래 키 → fiber_facility.fiber_fac_id — NOT NULL
  • carrier_info — TEXT
  • contract_info — TEXT
  • status — VARCHAR(50)

role

  • role_id — BIGINT
  • role_name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(500)

sms_contact

  • contact_id — BIGINT
  • name — VARCHAR(100)
  • phone — VARCHAR(30) — NOT NULL
  • org_text — VARCHAR(200)

sms_group

  • group_id — BIGINT
  • group_name — VARCHAR(200) — NOT NULL

sms_group_member

  • group_id — BIGINT — 외래 키 → sms_group.group_id — NOT NULL
  • contact_id — BIGINT — 외래 키 → sms_contact.contact_id — NOT NULL

sms_message

  • sms_id — BIGINT
  • create_mode — VARCHAR(30) — NOT NULL
  • body — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • created_by_user_id — BIGINT — 외래 키 → user.user_id
  • event_id — BIGINT — 외래 키 → event.event_id
  • tmpl_id — BIGINT — 외래 키 → sms_template.tmpl_id

sms_send_log

  • send_id — BIGINT
  • sms_id — BIGINT — 외래 키 → sms_message.sms_id — NOT NULL
  • usm_insert_sql — TEXT
  • send_status — VARCHAR(20)
  • status_updated_at — DATETIME

sms_template

  • tmpl_id — BIGINT
  • title — VARCHAR(200)
  • body — TEXT — NOT NULL
  • variables_json — JSON

status_def

  • status_def_id — BIGINT
  • facility_type — VARCHAR(50) — NOT NULL
  • item_name — VARCHAR(100) — NOT NULL
  • unit — VARCHAR(20)
  • normal_rule — VARCHAR(500)

status_event

  • status_evt_id — BIGINT
  • facility_id — BIGINT — 외래 키 → facility.facility_id — NOT NULL
  • collected_at — DATETIME — NOT NULL
  • status_def_id — BIGINT — 외래 키 → status_def.status_def_id — NOT NULL
  • value_num — DECIMAL(18,4)
  • value_text — VARCHAR(200)
  • source_id — BIGINT — 외래 키 → collection_source.source_id

user

  • user_id — BIGINT
  • org_unit_id — BIGINT — 외래 키 → org_unit.org_unit_id — NOT NULL
  • full_name — VARCHAR(100) — NOT NULL
  • employee_no — VARCHAR(50)
  • job_type — VARCHAR(50)
  • email — VARCHAR(200)
  • phone — VARCHAR(30)
  • is_active — TINYINT(1) — NOT NULL

user_role

  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • role_id — BIGINT — 외래 키 → role.role_id — NOT NULL
  • scope_type — VARCHAR(50)
  • scope_org_unit_id — BIGINT — 외래 키 → org_unit.org_unit_id

work_info

  • work_id — BIGINT
  • received_at — DATETIME — NOT NULL
  • section_desc — VARCHAR(300)
  • work_type — VARCHAR(50)
  • source_system — VARCHAR(100)
  • location_json — JSON