SSP

Tables (12)

business_info

  • biz_id — int — Primary key — NOT NULL — 사업장 PK
  • biz_name — varchar(100) — NOT NULL — 사업장명

device_alert_state

  • assign_id — int — Foreign key → device_assignment.assign_id — NOT NULL — 할당 PK, 최신 알림 1건
  • last_state — tinyint
  • last_distance — tinyint
  • last_direction — int
  • last_event_id — int — Foreign key → event_info.event_id
  • last_occurred_at — bigint — NOT NULL
  • last_received_at — bigint — NOT NULL
  • created_at — bigint — NOT NULL
  • updated_at — bigint

device_assignment

  • assign_id — int — Primary key — NOT NULL — 할당 PK
  • device_id — int — Foreign key → device_info.device_id — NOT NULL — 기기 ID
  • site_id — int — Foreign key → site_info.site_id — NOT NULL — 작업장 ID
  • user_id — int — Foreign key → user_info.user_id — NOT NULL — 작업자 (ks_name → user_info)
  • device_alias — varchar(30) — NOT NULL — 별명
  • started_at — bigint — NOT NULL — 할당 시작
  • ended_at — bigint — 종료 NULL=현재
  • created_at — bigint — NOT NULL
  • updated_at — bigint

device_info

  • device_id — int — Primary key — NOT NULL — 기기 PK
  • biz_id — int — Foreign key → business_info.biz_id — NOT NULL — 사업장 ID
  • device_type — tinyint — NOT NULL — 1:KS 2:KF 3:KSH 4:K-Finder
  • device_name — varchar(30) — NOT NULL — 기기명 / type4는 kf_name(맵 안 위치)
  • user_id — int — Foreign key → user_info.user_id — NOT NULL — 등록자
  • mac_addr — varchar(64) — NOT NULL — MAC (ks_id, kf_id, RTDB MAC 통합)
  • serial_no — varchar(64) — NOT NULL — 시리얼
  • fmw_ver — varchar(64) — 펌웨어
  • is_use — tinyint — NOT NULL — 사용 여부
  • location_id — int — Foreign key → location_category.location_id — 소속 부모 맵 (신규, Firebase category_seq / ks_ids)
  • created_at — bigint — NOT NULL
  • updated_at — bigint

device_link_log

  • log_id — int — Primary key — NOT NULL — 로그 PK
  • location_id — int — Foreign key → location_category.location_id — NOT NULL — 부모 맵 (경로 lc001)
  • ks_device_id — int — Foreign key → device_info.device_id — NOT NULL — 경로 MAC → device_info
  • finder_device_id — int — Foreign key → device_info.device_id — NOT NULL — kf_id → type4 device_info
  • connected_at — bigint — NOT NULL — timestamp

device_status

  • device_id — int — Foreign key → device_info.device_id — NOT NULL — 기기 PK
  • assign_id — int — Foreign key → device_assignment.assign_id — NOT NULL — 활성 할당
  • user_id — int — Foreign key → user_info.user_id — NOT NULL — 작업자
  • device_stat — tinyint — NOT NULL — 0 비활성 1 활성
  • protection_mode — tinyint — NOT NULL — 0 미착용 1 착용
  • battery_level — int — 배터리 %
  • last_seen_at — bigint — 마지막 통신 (패킷)
  • updated_at — bigint — NOT NULL
  • pos_x — double — kf_position_x (도면픽셀 또는 GPS)
  • pos_y — double — kf_position_y
  • finder_device_id — int — Foreign key → device_info.device_id — 지금 붙은 K-Finder (type4)

district_info

  • district_id — int — Primary key — NOT NULL — 구역 PK
  • site_id — int — Foreign key → site_info.site_id — NOT NULL — 작업장 ID
  • location_id — int — Foreign key → location_category.location_id — 부모 맵 (신규)
  • level_id — int — Foreign key → site_level.level_id — 위치 단계
  • district_name — varchar(100) — NOT NULL — 세분 구역명 (kf_name 아님)
  • is_active — tinyint — NOT NULL
  • created_at — bigint — NOT NULL
  • updated_at — bigint

event_info

  • event_id — int — Primary key — NOT NULL — 이벤트 PK
  • biz_id — int — Foreign key → business_info.biz_id — NOT NULL
  • assign_id — int — Foreign key → device_assignment.assign_id
  • occurred_at — bigint — NOT NULL

location_category

  • location_id — int — Primary key — NOT NULL — 위치 PK
  • site_id — int — Foreign key → site_info.site_id — NOT NULL — 현장 ID
  • location_code — varchar(32) — NOT NULL — 현장코드 lc008, lc010
  • location_name — varchar(100) — NOT NULL — 부모 위치명 (금성백조, 대전오월드)
  • location_type — varchar(16) — NOT NULL — indoor | outdoor
  • area_width — int — 실내 도면 너비
  • area_height — int — 실내 도면 높이
  • site_map_image — varchar(512) — 실내 맵 이미지 URL
  • location_lat — decimal(11, 8) — 야외 중심 위도
  • location_lng — decimal(11, 8) — 야외 중심 경도
  • restricted_zone — tinyint — NOT NULL — 제한구역 0/1
  • created_at — bigint — NOT NULL — 생성일시
  • updated_at — bigint — 수정일시

site_info

  • site_id — int — Primary key — NOT NULL — 작업장 PK
  • biz_id — int — Foreign key → business_info.biz_id — NOT NULL — 사업장 ID
  • site_name — varchar(100) — NOT NULL — 작업장명

site_level

  • level_id — int — Primary key — NOT NULL — 위치 단계 PK
  • site_id — int — Foreign key → site_info.site_id — NOT NULL

user_info

  • user_id — int — Primary key — NOT NULL — 회원 PK
  • login_id — varchar(50) — NOT NULL — 로그인ID
  • user_name — varchar(50) — NOT NULL — 회원이름