경우시스테크

테이블 (29)

tb_breakdown_report

  • report_id — BIGSERIAL
  • company_id — BIGINT — 외래 키 → tb_company.company_id — NOT NULL
  • driver_id — BIGINT — 외래 키 → tb_user_account.user_id — NOT NULL
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id — NOT NULL
  • location_address — VARCHAR(255)
  • location_detail — VARCHAR(255)
  • location_point — VARCHAR(100)
  • breakdown_type — BIGINT — 외래 키 → tb_code_detail.code_id
  • description — TEXT
  • address_input_method — VARCHAR(20)
  • status — VARCHAR(20)
  • reported_at — TIMESTAMP
  • updated_at — TIMESTAMP

tb_code_detail

  • code_id — BIGSERIAL
  • group_code_id — BIGINT — 외래 키 → tb_code_group.group_code_id — NOT NULL
  • code — VARCHAR(20) — NOT NULL
  • code_name — VARCHAR(100) — NOT NULL
  • ref_value — VARCHAR(100)
  • description — VARCHAR(255)
  • sort_id — BIGINT
  • is_active — BOOLEAN
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_code_group

  • group_code_id — BIGSERIAL
  • group_code — VARCHAR(20) — NOT NULL
  • group_name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(255)
  • ref_code — VARCHAR(20)
  • is_syscode — BOOLEAN
  • is_active — BOOLEAN
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_command_log

  • sent_at — TIMESTAMP — 기본 키 — NOT NULL
  • command_id — BIGSERIAL
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id
  • device_id — BIGINT
  • user_id — BIGINT — 외래 키 → tb_user_account.user_id
  • control_command_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • control_method — VARCHAR(10)
  • is_success — BOOLEAN
  • control_fail_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • result_message — VARCHAR(200)
  • received_at — TIMESTAMP

tb_company

  • company_id — BIGSERIAL
  • company_name — VARCHAR(100) — NOT NULL
  • company_name_en — VARCHAR(100)
  • company_code — VARCHAR(6) — NOT NULL
  • business_number — VARCHAR(10)
  • zipcode — VARCHAR(6)
  • address1 — VARCHAR(255)
  • address2 — VARCHAR(255)
  • manager_id — BIGINT
  • manager_name — VARCHAR(100)
  • manager_email — VARCHAR(100)
  • manager_country_code — VARCHAR(5)
  • manager_phone — VARCHAR(30)
  • url_path — VARCHAR(20) — NOT NULL
  • send_ctrl_photo — BOOLEAN
  • logo_image — VARCHAR(255)
  • invite_sent_at — TIMESTAMP
  • company_type_code_id — BIGINT
  • is_active — BOOLEAN
  • is_partner — BOOLEAN
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_consumable

  • consumable_id — BIGSERIAL
  • vehicle_kind_id — BIGINT — 외래 키 → tb_vehicle_kind.vehicle_kind_id
  • fuel_type_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • consumable_type_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • measure_item_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • check_criteria — NUMERIC
  • check_unit_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • judge_criteria — VARCHAR(10)
  • warn_threshold_pct — INTEGER
  • caution_threshold_pct — INTEGER
  • normal_threshold_pct — INTEGER
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_consumable_history

  • history_id — BIGSERIAL
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id — NOT NULL
  • consumable_type_id — BIGINT — 외래 키 → tb_code_detail.code_id — NOT NULL
  • replaced_at — DATE — NOT NULL
  • odometer — BIGINT
  • memo — TEXT
  • part_code — VARCHAR(10)
  • created_by — BIGINT — 외래 키 → tb_user_account.user_id
  • created_at — TIMESTAMP

tb_consumable_status

  • status_id — BIGSERIAL
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id — NOT NULL
  • consumable_type_id — BIGINT — 외래 키 → tb_code_detail.code_id — NOT NULL
  • judge_criteria — VARCHAR(10) — NOT NULL
  • baseline_date — DATE
  • baseline_odometer — BIGINT
  • last_odometer — BIGINT
  • last_observed_at — TIMESTAMP
  • progress_pct — NUMERIC(6,2)
  • status_code — VARCHAR(10) — NOT NULL
  • last_measured_value — NUMERIC
  • last_measured_unit_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • detail — TEXT
  • calculated_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP

tb_device

  • device_id — BIGSERIAL
  • device_type_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id
  • serial_no — VARCHAR(50) — NOT NULL
  • imei — VARCHAR(20)
  • device_tel — VARCHAR(50)
  • firmware_id — BIGINT
  • device_status — BIGINT — 외래 키 → tb_code_detail.code_id
  • out_date — TIMESTAMP
  • in_date — TIMESTAMP
  • reg_date — TIMESTAMP
  • install_date — TIMESTAMP
  • is_installed — BOOLEAN
  • installer_id — BIGINT — 외래 키 → tb_user_account.user_id
  • proof1 — BIGINT
  • proof2 — BIGINT
  • proof3 — BIGINT
  • proof4 — BIGINT
  • proof5 — BIGINT
  • return_status — BIGINT — 외래 키 → tb_code_detail.code_id
  • in_reason — VARCHAR(1000)
  • proc_reason — VARCHAR(1000)
  • reg_method_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • created_at — TIMESTAMP

tb_device_history

  • history_id — BIGSERIAL
  • device_id — BIGINT — 외래 키 → tb_device.device_id
  • processor_id — BIGINT — 외래 키 → tb_user_account.user_id
  • processor_name — VARCHAR(50)
  • prev_status — BIGINT — 외래 키 → tb_code_detail.code_id
  • curr_status — BIGINT — 외래 키 → tb_code_detail.code_id
  • proc_reason — VARCHAR(1000)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

tb_driver_safety_score

  • stat_date — TIMESTAMP — 기본 키 — NOT NULL
  • driver_id — BIGINT — 외래 키 → tb_user_account.user_id — NOT NULL
  • score_id — BIGSERIAL
  • company_id — BIGINT — 외래 키 → tb_company.company_id — NOT NULL
  • total_distance_km — DOUBLE
  • weighted_event_count — DOUBLE
  • event_rate — DOUBLE
  • group_avg_rate — DOUBLE
  • group_std_dev — DOUBLE
  • safety_score — DOUBLE
  • created_at — TIMESTAMP

tb_event_log

  • event_id — BIGSERIAL
  • event_code — VARCHAR(30)
  • occurred_at — TIMESTAMP — NOT NULL
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id
  • user_id — BIGINT — 외래 키 → tb_user_account.user_id

tb_firmware

  • firmware_id — BIGSERIAL
  • device_type_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • firmware_version — VARCHAR(100) — NOT NULL
  • firmware_file_id — BIGINT — NOT NULL
  • patch_note — TEXT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP

tb_firmware_vehicle_kind

  • firmware_vehicle_kind_id — BIGSERIAL
  • firmware_id — BIGINT — 외래 키 → tb_firmware.firmware_id — NOT NULL
  • vehicle_kind_id — BIGINT — 외래 키 → tb_vehicle_kind.vehicle_kind_id — NOT NULL

tb_garage

  • garage_id — BIGSERIAL
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • garage_name — VARCHAR(100) — NOT NULL
  • contact — VARCHAR(30)
  • address — VARCHAR(255)
  • location — VARCHAR(100)
  • radius — INTEGER
  • is_representative — BOOLEAN
  • description — VARCHAR(1000)
  • is_active — BOOLEAN
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_push_history

  • history_id — BIGSERIAL
  • category_code — VARCHAR(50)
  • app_type — VARCHAR(20)
  • target_user_id — BIGINT — 외래 키 → tb_user_account.user_id
  • target_company_id — BIGINT — 외래 키 → tb_company.company_id
  • ref_table — VARCHAR(50)
  • ref_id — BIGINT
  • title — VARCHAR(200) — NOT NULL
  • body — TEXT
  • data — TEXT
  • target_count — INTEGER
  • success_count — INTEGER
  • fail_count — INTEGER
  • send_status — VARCHAR(20)
  • scheduled_at — TIMESTAMP
  • sent_at — TIMESTAMP
  • result_msg — TEXT
  • created_at — TIMESTAMP
  • send_cycle — VARCHAR(10)
  • send_param — INTEGER
  • send_hour — INTEGER

tb_push_history_detail

  • detail_id — BIGSERIAL
  • history_id — BIGINT — 외래 키 → tb_push_history.history_id — NOT NULL
  • app_type — VARCHAR(20) — NOT NULL
  • user_id — BIGINT — 외래 키 → tb_user_account.user_id — NOT NULL
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • device_id — VARCHAR(100)
  • data — TEXT
  • send_status — VARCHAR(20) — NOT NULL
  • fail_reason — VARCHAR(200)
  • sent_at — TIMESTAMP

tb_push_setting

  • push_setting_id — BIGSERIAL
  • push_category_id — BIGINT — 외래 키 → tb_code_detail.code_id — NOT NULL
  • push_send_type_id — BIGINT — 외래 키 → tb_code_detail.code_id — NOT NULL
  • push_title — VARCHAR(200) — NOT NULL
  • push_content — TEXT — NOT NULL
  • send_cycle — VARCHAR(10)
  • send_param — INTEGER
  • send_hour — INTEGER
  • is_active — BOOLEAN
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_refuel_history

  • history_id — BIGSERIAL
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id
  • driver_id — BIGINT — 외래 키 → tb_user_account.user_id
  • fuel_eff — DOUBLE
  • prev_fuel — DOUBLE
  • curr_fuel — DOUBLE
  • prev_fuel_liter — DOUBLE
  • curr_fuel_liter — DOUBLE
  • refueled_amount — DOUBLE
  • fuel_volume — DOUBLE
  • std_eff — DOUBLE
  • distance — DOUBLE
  • odometer — DOUBLE
  • is_short_distance — BOOLEAN
  • is_low_eff — BOOLEAN
  • short_dist_rule_ratio — DOUBLE
  • short_dist_result_ratio — DOUBLE
  • low_eff_rule_count — INTEGER
  • low_eff_rule_ratio — DOUBLE
  • low_eff_result_count — INTEGER
  • low_eff_result_avg — DOUBLE
  • start_time — TIMESTAMP
  • end_time — TIMESTAMP
  • location — VARCHAR(100)
  • address — VARCHAR(255)

tb_refuel_receipt

  • receipt_id — BIGSERIAL
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id
  • driver_id — BIGINT — 외래 키 → tb_user_account.user_id
  • amount — INTEGER
  • pay_method_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • reg_method_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • refueled_at — TIMESTAMP
  • history_id — BIGINT — 외래 키 → tb_refuel_history.history_id
  • is_fraud — BOOLEAN
  • min_fuel — INTEGER
  • max_fuel — INTEGER
  • match_ratio — DOUBLE
  • curr_fuel_liter — DOUBLE
  • odometer_km — DOUBLE
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_refuel_rule

  • rule_id — BIGSERIAL
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • insufficient_mileage_ratio — INTEGER
  • low_efficiency_count — INTEGER
  • low_efficiency_ratio — INTEGER
  • tolerance — INTEGER
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • created_by — BIGINT

tb_safety_event

  • event_source — VARCHAR(20)
  • event_code — VARCHAR(20)
  • event_name — VARCHAR(100) — NOT NULL

tb_safety_event_weight

  • weight_id — BIGSERIAL
  • event_code — VARCHAR(20) — 외래 키 → tb_safety_event.event_code — NOT NULL
  • company_id — BIGINT — 외래 키 → tb_company.company_id — NOT NULL
  • weight_score — DOUBLE — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

tb_user_account

  • user_id — BIGSERIAL
  • user_name — VARCHAR(50)
  • login_id — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255)
  • country_code — VARCHAR(5)
  • phone — VARCHAR(30)
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • email — VARCHAR(100)
  • emp_no — VARCHAR(50)
  • driver_license_no — VARCHAR(20)
  • guardian_id — BIGINT
  • guardian_name — VARCHAR(50)
  • guardian_phone — VARCHAR(30)
  • last_login_at — TIMESTAMP
  • failed_login_count — INTEGER
  • lock_until — TIMESTAMP
  • account_stats — VARCHAR(20) — NOT NULL
  • account_stats_reason — VARCHAR(255)
  • last_updated_at — TIMESTAMP
  • invite_code — VARCHAR(255)
  • invite_sent_at — TIMESTAMP
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • is_active — BOOLEAN

tb_user_leave

  • leave_id — BIGSERIAL
  • user_id — BIGINT — 외래 키 → tb_user_account.user_id — NOT NULL
  • leave_type_id — BIGINT — 외래 키 → tb_code_detail.code_id — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • reason — VARCHAR(255)
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_vehicle

  • vehicle_id — BIGSERIAL
  • company_id — BIGINT — 외래 키 → tb_company.company_id
  • vehicle_num — VARCHAR(30) — NOT NULL
  • vehicle_serial — VARCHAR(50)
  • vehicle_kind_id — BIGINT — 외래 키 → tb_vehicle_kind.vehicle_kind_id
  • fuel_type_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • vehicle_year — INTEGER
  • official_eff — NUMERIC(6,2)
  • door_ctrl — VARCHAR(50)
  • vehicle_status — BIGINT — 외래 키 → tb_code_detail.code_id
  • vehicle_state — BIGINT — 외래 키 → tb_code_detail.code_id
  • is_active — BOOLEAN
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_vehicle_assignment

  • assignment_id — BIGSERIAL
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id — NOT NULL
  • driver_id — BIGINT — 외래 키 → tb_user_account.user_id — NOT NULL
  • start_date — TIMESTAMP — NOT NULL
  • end_date — TIMESTAMP
  • assign_status — BOOLEAN
  • report_id — BIGINT — 외래 키 → tb_breakdown_report.report_id
  • assignment_reason_type — VARCHAR(20)
  • assignment_reason — VARCHAR(1000)
  • send_navigation — BOOLEAN
  • assign_garage_id — BIGINT — 외래 키 → tb_garage.garage_id
  • created_at — TIMESTAMP
  • created_by — BIGINT
  • updated_at — TIMESTAMP
  • updated_by — BIGINT

tb_vehicle_kind

  • vehicle_kind_id — BIGSERIAL
  • vehicle_kind_name — VARCHAR(50) — NOT NULL
  • brand_code_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • kind_code_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • project_code_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • vehicle_year — INTEGER
  • fuel_type_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • fuel_volume — NUMERIC(5,2)
  • mcu_cable — VARCHAR(50)
  • std_eff — NUMERIC(5,2)
  • can_type1_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • can_baud1 — INTEGER
  • can_type2_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • can_baud2 — INTEGER
  • is_active — BOOLEAN
  • created_by — BIGINT
  • created_at — TIMESTAMP
  • updated_by — BIGINT
  • updated_at — TIMESTAMP

tb_vehicle_photo

  • photo_id — BIGSERIAL
  • vehicle_id — BIGINT — 외래 키 → tb_vehicle.vehicle_id
  • front_file_id — BIGINT
  • rear_file_id — BIGINT
  • left_file_id — BIGINT
  • right_file_id — BIGINT
  • driver_id — BIGINT — 외래 키 → tb_user_account.user_id
  • inspection_result_id — BIGINT — 외래 키 → tb_code_detail.code_id
  • inspection_date — TIMESTAMP
  • memo — VARCHAR(255)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP