Moode

Tables (12)

hazard_analysis

  • analysis_id — BIGINT — Primary key — NOT NULL
  • event_id — BIGINT — Foreign key → hazard_event.event_id — NOT NULL
  • primary_cause — VARCHAR(100)
  • secondary_cause — VARCHAR(100)
  • contributing_factor — VARCHAR(200)
  • final_estimated_cause — VARCHAR(200)
  • ai_report_text — TEXT
  • generated_at — DATETIME — NOT NULL

hazard_event

  • event_id — BIGINT — Primary key — NOT NULL
  • idempotency_key — VARCHAR(64) — NOT NULL
  • worker_id — BIGINT — Foreign key → worker.worker_id — NOT NULL
  • helmet_id — BIGINT — Foreign key → helmet.helmet_id — NOT NULL
  • zone_id — BIGINT — Foreign key → zone.zone_id
  • event_category — ENUM('EXTERNAL','HEALTH','FALL') — NOT NULL
  • hazard_type — VARCHAR(30) — NOT NULL
  • severity — ENUM('WARNING','DANGER') — NOT NULL
  • confidence — DECIMAL(4,3)
  • description — VARCHAR(255)
  • location_detail — VARCHAR(100)
  • occurred_at — DATETIME — NOT NULL
  • process_status — ENUM('UNPROCESSED','IN_PROGRESS','REST_RECOMMENDED','RESOLVED') — NOT NULL
  • source — ENUM('VISION_AI','IMU_HEALTH','MANUAL') — NOT NULL
  • created_at — DATETIME — NOT NULL

hazard_event_clip

  • clip_id — BIGINT — Primary key — NOT NULL
  • event_id — BIGINT — Foreign key → hazard_event.event_id — NOT NULL
  • cctv_id — VARCHAR(20)
  • s3_url — VARCHAR(500) — NOT NULL
  • thumbnail_s3_url — VARCHAR(500)
  • duration_sec — INT — NOT NULL
  • pre_event_sec — INT — NOT NULL
  • post_event_sec — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

health_metric

  • metric_id — BIGINT — Primary key — NOT NULL
  • worker_id — BIGINT — Foreign key → worker.worker_id — NOT NULL
  • helmet_id — BIGINT — Foreign key → helmet.helmet_id — NOT NULL
  • heart_rate — SMALLINT
  • body_temp — DECIMAL(4,1)
  • fatigue_level — TINYINT
  • measured_at — DATETIME — NOT NULL

helmet

  • helmet_id — BIGINT — Primary key — NOT NULL
  • helmet_no — VARCHAR(20) — NOT NULL
  • device_id — VARCHAR(50) — NOT NULL
  • worker_id — BIGINT — Foreign key → worker.worker_id
  • sensor_status — ENUM('CONNECTED','DISCONNECTED') — NOT NULL
  • operational_status — ENUM('IN_USE','STANDBY','DISCONNECTED') — NOT NULL
  • last_communication_at — DATETIME
  • api_key_hash — VARCHAR(255)
  • registered_at — DATETIME — NOT NULL

manager_account

  • admin_id — BIGINT — Primary key — NOT NULL
  • username — VARCHAR(30) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • department — VARCHAR(50)
  • approval_status — ENUM('PENDING','APPROVED','REJECTED') — NOT NULL
  • created_at — DATETIME — NOT NULL

notification

  • notification_id — BIGINT — Primary key — NOT NULL
  • event_id — BIGINT — Foreign key → hazard_event.event_id
  • sos_id — BIGINT — Foreign key → sos_request.sos_id
  • recipient_type — ENUM('WORKER','COWORKER','MANAGER') — NOT NULL
  • recipient_worker_id — BIGINT — Foreign key → worker.worker_id
  • recipient_admin_id — BIGINT — Foreign key → manager_account.admin_id
  • channel — ENUM('PUSH','SMS','IN_APP') — NOT NULL
  • message — VARCHAR(255)
  • status — ENUM('SENT','CONFIRMED','FAILED') — NOT NULL
  • triggered_by — ENUM('SYSTEM','MANAGER') — NOT NULL
  • triggered_by_admin_id — BIGINT — Foreign key → manager_account.admin_id
  • sent_at — DATETIME — NOT NULL
  • confirmed_at — DATETIME

sos_request

  • sos_id — BIGINT — Primary key — NOT NULL
  • worker_id — BIGINT — Foreign key → worker.worker_id — NOT NULL
  • zone_id — BIGINT — Foreign key → zone.zone_id
  • requested_at — DATETIME — NOT NULL
  • status — ENUM('PENDING','ACKNOWLEDGED','RESOLVED','AUTO_119') — NOT NULL
  • auto_dispatch_at — DATETIME
  • resolved_at — DATETIME

work_log

  • work_log_id — BIGINT — Primary key — NOT NULL
  • worker_id — BIGINT — Foreign key → worker.worker_id — NOT NULL
  • work_date — DATE — NOT NULL
  • check_in_at — DATETIME — NOT NULL
  • check_out_at — DATETIME
  • expected_check_out_at — DATETIME
  • break_start_at — DATETIME
  • break_end_at — DATETIME
  • work_zone_id — BIGINT — Foreign key → zone.zone_id
  • total_work_minutes — INT

worker

  • worker_id — BIGINT — Primary key — NOT NULL
  • employee_no — VARCHAR(20) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20)
  • department — VARCHAR(50)
  • status — ENUM('WORKING','BREAK','OFF_DUTY') — NOT NULL
  • current_zone_id — BIGINT — Foreign key → zone.zone_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

worker_position

  • worker_id — BIGINT — Foreign key → worker.worker_id — NOT NULL
  • zone_id — BIGINT — Foreign key → zone.zone_id
  • pos_x — DECIMAL(8,2)
  • pos_y — DECIMAL(8,2)
  • nearest_beacon_id — VARCHAR(30)
  • rssi — SMALLINT
  • updated_at — DATETIME — NOT NULL

zone

  • zone_id — BIGINT — Primary key — NOT NULL
  • zone_code — VARCHAR(20) — NOT NULL
  • zone_name — VARCHAR(50) — NOT NULL
  • floor — VARCHAR(20)
  • risk_level — ENUM('LOW','MEDIUM','HIGH') — NOT NULL
  • access_restricted — BOOLEAN — NOT NULL
  • restricted_by_admin_id — BIGINT — Foreign key → manager_account.admin_id
  • restricted_at — DATETIME
  • created_at — DATETIME — NOT NULL