도로공사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