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