icerti_proto

Tables (15)

AI_EVENT

  • event_id — BIGINT — Primary key — NOT NULL
  • ingest_source — VARCHAR(50) — NOT NULL
  • external_event_id — VARCHAR(100)
  • camera_id — BIGINT — Foreign key → CAMERA.camera_id — NOT NULL
  • scenario_type_id — BIGINT — Foreign key → SCENARIO_TYPE.scenario_type_id — NOT NULL
  • scenario_id — BIGINT — Foreign key → SCENARIO.scenario_id — 시나리오 삭제 시 NULL. 판정 근거는 스냅샷이 보존
  • roi_label — VARCHAR(100)
  • config_version — INT — NOT NULL — CAMERA.config_version 보다 작으면 구버전 설정 기준 이벤트
  • detection_snapshot_json — JSON — 판정 시점 영역 좌표·종류 + parameters_json
  • severity_code — VARCHAR(20) — NOT NULL
  • current_status — VARCHAR(30) — NOT NULL
  • conf_score — DECIMAL(5, 4) — NOT NULL
  • detected_at — DATETIME(6) — NOT NULL — AI 서버가 판정한 시각
  • received_at — DATETIME(6) — NOT NULL
  • handled_at — DATETIME(6)
  • event_json — JSON — NOT NULL
  • bbox_json — JSON
  • created_at — DATETIME(6) — NOT NULL

APP_USER

  • user_id — BIGINT — Primary key — NOT NULL
  • login_id — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — BCrypt 해시. 미등록 상태에서는 NULL
  • password_state — VARCHAR(30) — NOT NULL
  • password_registered_at — DATETIME(6)
  • user_enabled — BOOL — NOT NULL
  • user_name — VARCHAR(100)
  • phone_number — VARCHAR(100)
  • email — VARCHAR(100)
  • role_id — BIGINT — Foreign key → USER_ROLE.role_id — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)
  • updated_at — DATETIME(6) — NOT NULL

CAMERA

  • camera_id — BIGINT — Primary key — NOT NULL
  • camera_group_id — BIGINT — Foreign key → CAMERA_GROUP.camera_group_id — NOT NULL
  • camera_code — VARCHAR(100) — NOT NULL
  • camera_name — VARCHAR(100) — NOT NULL
  • rec_server_id — VARCHAR(50) — REC 서버 식별자. 설정 파일의 서버 키를 가리킨다
  • rec_camera_id — VARCHAR(32) — REC 발급 카메라 식별자. NULL 이면 REC 미등록 상태
  • ip_address — VARCHAR(45) — IPv6 표기 고려해 45
  • http_port — INT
  • device_id — VARCHAR(100)
  • device_pw — VARCHAR(255) — PoC 단계 평문. 운영 전환 시 암호화 필요
  • use_onvif — BOOL — NOT NULL
  • camera_enabled — BOOL — NOT NULL
  • live_enabled — BOOL — NOT NULL
  • record_enabled — BOOL — NOT NULL
  • analysis_enabled — BOOL — NOT NULL
  • sort_order — INT — NOT NULL
  • config_version — INT — NOT NULL — 시나리오 설정 저장 시마다 증가. 이벤트 stale 판정 기준
  • config_status — VARCHAR(20) — NOT NULL
  • config_applied_at — DATETIME(6)
  • config_error_code — VARCHAR(100)
  • created_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)
  • updated_at — DATETIME(6) — NOT NULL

CAMERA_ENDPOINT

  • camera_endpoint_id — BIGINT — Primary key — NOT NULL
  • camera_id — BIGINT — Foreign key → CAMERA.camera_id — NOT NULL
  • endpoint_type — VARCHAR(100) — NOT NULL
  • channel_number — INT
  • endpoint_url_encrypted — TEXT — NOT NULL — 컬럼명과 달리 평문이다
  • credential_ref — TEXT — 미사용. PoC 가 채우지 않는다
  • last_status — VARCHAR(100) — NOT NULL
  • last_message — VARCHAR(100)
  • created_at — DATETIME(6) — NOT NULL

CAMERA_GROUP

  • camera_group_id — BIGINT — Primary key — NOT NULL
  • group_name — VARCHAR(100) — NOT NULL
  • parent_group_id — BIGINT — Foreign key → CAMERA_GROUP.camera_group_id — 상위 그룹. 최상위는 NULL
  • sort_order — INT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

CAMERA_TRACK

  • camera_track_id — BIGINT — Primary key — NOT NULL
  • camera_id — BIGINT — Foreign key → CAMERA.camera_id — NOT NULL
  • track_type — INT — NOT NULL
  • rtsp_port — INT — NOT NULL
  • rtsp_path — VARCHAR(255) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

ENVIRONMENT_SETTING

  • singleton_id — TINYINT — Primary key — NOT NULL
  • organization_name — VARCHAR(100) — NOT NULL
  • dashboard_refresh_seconds — INT — NOT NULL
  • map_show_today_summary — BOOL — NOT NULL
  • map_auto_rotate_enabled — BOOL — NOT NULL
  • map_rotate_interval_seconds — INT
  • video_encryption_mode — VARCHAR(20) — NOT NULL
  • video_secret_ciphertext — TEXT
  • video_secret_iv — VARBINARY(12)
  • video_secret_key_version — VARCHAR(100)
  • video_key_server_url — VARCHAR(500)
  • video_key_server_account — VARCHAR(100)
  • updated_by_user_id — BIGINT — Foreign key → APP_USER.user_id
  • updated_at — DATETIME(6) — NOT NULL

EVENT_HISTORY

  • event_history_id — BIGINT — Primary key — NOT NULL
  • event_id — BIGINT — Foreign key → AI_EVENT.event_id — NOT NULL
  • user_id — BIGINT — Foreign key → APP_USER.user_id
  • handler_name — VARCHAR(100) — 처리자 이름 스냅샷
  • event_status — VARCHAR(30) — NOT NULL
  • memo — TEXT
  • handled_at — DATETIME(6) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)
  • updated_at — DATETIME(6) — NOT NULL

MACHINE_TOKEN

  • machine_token_id — BIGINT — Primary key — NOT NULL
  • token_hash — CHAR(64) — NOT NULL
  • subject — VARCHAR(100) — NOT NULL — 예: manager-01, ai-01
  • active — BOOL — NOT NULL
  • issued_at — DATETIME(6) — NOT NULL
  • revoked_at — DATETIME(6)
  • revoke_notified_at — DATETIME(6)

MAP_CAMERA_PLACEMENT

  • map_camera_placement_id — BIGINT — Primary key — NOT NULL
  • map_layout_id — BIGINT — Foreign key → MAP_LAYOUT.map_layout_id — NOT NULL
  • camera_id — BIGINT — Foreign key → CAMERA.camera_id — NOT NULL
  • position_x — DECIMAL(10, 7) — NOT NULL — 정규화 좌표 0~1
  • position_y — DECIMAL(10, 7) — NOT NULL — 정규화 좌표 0~1
  • direction_degree — DECIMAL(6, 2) — NOT NULL
  • show_camera_name — BOOL — NOT NULL
  • label_offset_x — DECIMAL(10, 7) — NOT NULL
  • label_offset_y — DECIMAL(10, 7) — NOT NULL
  • z_index — INT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

MAP_LAYOUT

  • map_layout_id — BIGINT — Primary key — NOT NULL
  • camera_group_id — BIGINT — Foreign key → CAMERA_GROUP.camera_group_id — NOT NULL
  • map_name — VARCHAR(100) — NOT NULL
  • image_path — VARCHAR(500) — NOT NULL
  • image_width — INT — NOT NULL
  • image_height — INT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

SCENARIO

  • scenario_id — BIGINT — Primary key — NOT NULL
  • camera_id — BIGINT — Foreign key → CAMERA.camera_id — NOT NULL
  • scenario_type_id — BIGINT — Foreign key → SCENARIO_TYPE.scenario_type_id — NOT NULL
  • parameters_json — JSON — NOT NULL — 유형별 파라미터. 스키마는 scenario_code 에 종속
  • scenario_enabled — BOOL — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

SCENARIO_ROI

  • scenario_roi_id — BIGINT — Primary key — NOT NULL
  • scenario_id — BIGINT — Foreign key → SCENARIO.scenario_id — NOT NULL
  • roi_label — VARCHAR(100) — NOT NULL — 영역 이름. 예: 정문 진입로
  • roi_kind — VARCHAR(20) — NOT NULL
  • shape — VARCHAR(20) — NOT NULL — POLYGON | RECT
  • points_json — JSON — NOT NULL
  • sort_order — INT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

SCENARIO_TYPE

  • scenario_type_id — BIGINT — Primary key — NOT NULL
  • scenario_code — VARCHAR(100) — NOT NULL
  • scenario_name — VARCHAR(100) — NOT NULL — 화면 출력용 표시명

USER_ROLE

  • role_id — BIGINT — Primary key — NOT NULL
  • role_code — VARCHAR(100) — NOT NULL
  • role_name — VARCHAR(100) — NOT NULL
  • page_json — JSON — NOT NULL — 접근 허용 페이지 코드 배열. 빈 배열 금지
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL