moode_ERD

Tables (12)

hazard_clip

  • clip_id — BIGINT — Primary key — NOT NULL
  • hazard_event_id — BIGINT — NOT NULL — UNIQUE. 이벤트당 최대 1건(1:0..1)
  • storage_type — ENUM('S3', 'LOCAL') — NOT NULL
  • bucket — VARCHAR(64) — LOCAL 이면 NULL
  • object_key — VARCHAR(512) — NOT NULL — 공개 URL 이 아니라 객체 키. 조회 시 pre-signed URL 발급
  • 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
  • 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
  • clip_cctv_id — VARCHAR(20)
  • clip_s3_url — VARCHAR(500)
  • clip_thumbnail_s3_url — VARCHAR(500)
  • clip_duration_sec — INT
  • 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 — NOT NULL
  • action_type — VARCHAR(20) — NOT NULL — SEND_WARNING / BLOCK_ZONE / RESOLVE
  • performed_by — BIGINT
  • 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 — INDEX. 한 사건의 여러 메시지를 묶어보기 위함
  • event_type — VARCHAR(24) — NOT NULL — HAZARD_CONFIRMED / CLIP_READY
  • 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