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