mood:e

Tables (12)

hazard_clip

  • clip_id — BIGINT — Primary key — NOT NULL
  • hazard_event_id — BIGINT — Foreign key → hazard_event.event_id — NOT NULL
  • storage_type — ENUM('S3','LOCAL') — NOT NULL
  • bucket — VARCHAR(64)
  • object_key — VARCHAR(512) — NOT NULL
  • duration_seconds — DECIMAL(6,2) — NOT NULL
  • frame_count — INT — NOT NULL
  • marked_offset_seconds — DECIMAL(6,2)

hazard_event

  • event_id — BIGINT — Primary key — NOT NULL
  • event_uid — CHAR(32) — NOT NULL — AI 서버가 만든 사건 식별자. UNIQUE 아님
  • helmet_id — BIGINT — Foreign key → helmet.helmet_id — NOT NULL — 수신 시점 배정 스냅샷
  • worker_id — BIGINT — Foreign key → worker.worker_id — 미배정 헬멧이면 NULL
  • zone_id — BIGINT — Foreign key → zone.zone_id
  • event_category — ENUM('EXTERNAL', 'HEALTH', 'FALL') — NOT NULL
  • hazard_type — VARCHAR(32) — NOT NULL
  • severity — ENUM('WARNING', 'DANGER') — NOT NULL
  • confidence — DECIMAL(5, 4)
  • source — ENUM('VISION_AI', 'IMU_HEALTH', 'MANUAL') — NOT NULL
  • description — VARCHAR(255)
  • location_detail — VARCHAR(100)
  • frame_width — INT
  • frame_height — INT
  • detections_json — TEXT
  • occurred_at — DATETIME(3) — NOT NULL
  • status — ENUM('UNHANDLED', 'IN_PROGRESS', 'RESOLVED') — NOT NULL
  • handled_by — BIGINT — Foreign key → manager_account.admin_id
  • handled_at — DATETIME(3)
  • cause_primary — VARCHAR(100)
  • cause_secondary — VARCHAR(100)
  • cause_contributing — VARCHAR(200)
  • cause_final_estimated — VARCHAR(200)
  • ai_report_text — TEXT
  • created_at — DATETIME — NOT NULL

hazard_event_action

  • action_id — BIGINT — Primary key — NOT NULL
  • hazard_event_id — BIGINT — Foreign key → hazard_event.event_id — NOT NULL
  • action_type — VARCHAR(20) — NOT NULL
  • performed_by — BIGINT — Foreign key → manager_account.admin_id
  • performed_at — DATETIME — NOT NULL
  • notified_count — INT

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

inbound_message

  • idempotency_key — CHAR(32) — Primary key — NOT NULL
  • event_uid — CHAR(32) — NOT NULL
  • event_type — VARCHAR(24) — NOT NULL
  • received_at — DATETIME(3) — 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

sensor_data

  • id — BIGINT — Primary key — NOT NULL
  • device_id — VARCHAR(255)
  • ax — DOUBLE
  • ay — DOUBLE
  • az — DOUBLE
  • gx — DOUBLE
  • gy — DOUBLE
  • gz — DOUBLE
  • heart_rate — DOUBLE
  • spo2 — DOUBLE
  • health_abnormal — BIT(1)
  • fall_state — VARCHAR(255)
  • health_state — VARCHAR(255)
  • fall_confidence — DOUBLE
  • posture — VARCHAR(255)
  • zone_id — VARCHAR(255)
  • recorded_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
  • pos_x — DECIMAL(8,2)
  • pos_y — DECIMAL(8,2)
  • nearest_beacon_id — VARCHAR(30)
  • rssi — SMALLINT
  • position_updated_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • 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