Guardian

Tables (13)

admin

  • id — BIGINT — Primary key — NOT NULL
  • login_id (username) — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

case_timelines

  • id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → cases.id — NOT NULL
  • case_status — ENUM('DETECTED','APPROACHING','PROTECTING','RESOLVED','CANCELLED') — NOT NULL
  • event_time — TIMESTAMP — NOT NULL

cases

  • id — BIGINT — Primary key — NOT NULL
  • robot_id — BIGINT — Foreign key → robots.id — NOT NULL
  • child_name — VARCHAR(50)
  • child_age — INT
  • child_gender — ENUM('MALE','FEMALE','UNKNOWN')
  • clothing_top_color — VARCHAR(30)
  • clothing_bottom_color — VARCHAR(30)
  • case_status — ENUM('DETECTED','APPROACHING','PROTECTING','RESOLVED','CANCELLED') — NOT NULL
  • confidence_score — FLOAT — NOT NULL
  • last_seen_floor — INT — NOT NULL
  • last_seen_zone — INT — NOT NULL
  • detected_floor — INT
  • detected_zone — INT
  • detected_position_x — DOUBLE
  • detected_position_y — DOULBE
  • description — VARCHAR(255)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • resolved_at — TIMESTAMP

matchings

  • id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → cases.id — NOT NULL
  • report_id — BIGINT — Foreign key → reports.id — NOT NULL
  • snapshot_image_id — BIGINT — Foreign key → snapshot_images.id — NOT NULL
  • is_confirmed — BOOLEAN
  • similarity_score — FLOAT — NOT NULL
  • matched_at — TIMESTAMP — NOT NULL
  • confirmed_at — TIMESTAMP

montage_images

  • id — BIGINT — Primary key — NOT NULL
  • report_id — BIGINT — Foreign key → reports.id — NOT NULL
  • image_url — VARCHAR(500)
  • is_primary — BOOLEAN
  • uploaded_at — TIMESTAMP

parent_messages

  • id — BIGINT — Primary key — NOT NULL
  • report_id — BIGINT — Foreign key → reports.id — NOT NULL
  • type — ENUM('VOICE','ARRIVAL') — NOT NULL
  • content_url — VARCHAR(500) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

prediction_attractions

  • id — BIGINT — Primary key — NOT NULL
  • floor — INT — NOT NULL
  • zone — VARCHAR(50) — NOT NULL
  • store — VARCHAR(100)
  • probability — FLOAT — NOT NULL

prediction_ranges

  • id — BIGINT — Primary key — NOT NULL
  • age — INT — NOT NULL
  • minutes_elapsed — INT — NOT NULL
  • radius_meter — INT — NOT NULL

reports

  • id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → cases.id — NOT NULL
  • access_key — VARCHAR(100)
  • reporter_type — ENUM('PARENT','WITNESS','STAFF') — NOT NULL
  • reporter_name — VARCHAR(50)
  • phone_number — VARCHAR(20)
  • report_status — ENUM('REGISTERED','SEARCHING','FOUND','CONFIRMED','RESOLVED','CANCELLED') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

robot_commands

  • id — BIGINT — Primary key — NOT NULL
  • robot_id — BIGINT — Foreign key → robots.id — NOT NULL
  • command_type — ENUM('GOTO','STOP','PATROL','SEARCH','APPROACH','PROTECT') — NOT NULL
  • payload — JSON — NOT NULL
  • command_status — ENUM('PENDING','ACCEOTED','EXECUTED','FAILED') — NOT NULL
  • issued_at — TIMESTAMP — NOT NULL

robot_status_logs

  • id — BIGINT — Primary key — NOT NULL
  • robot_id — BIGINT — Foreign key → robots.id — NOT NULL
  • safety_mode — ENUM('PATROL','APPROACHING','PROTECTING','IDLE','OFFLINE')
  • operation_mode — ENUM('MANUAL)CONTROL','NAVIGATE_TO_TARGET','AUTONOMOUS')4
  • behavior_mode — ENUM('IDLE','PATROL','SEARCH','CHILD_MONITOR','APPROACH','PROTECT_MODE')
  • battery_level — INT — NOT NULL
  • position_x — DOUBLE
  • position_y — DOUBLE
  • event_time — TIMESTAMP — NOT NULL

robots

  • id — BIGINT — Primary key — NOT NULL
  • serial_number — VARCHAR(50) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • safety_mode — ENUM('PATROL','APPROACHING','PROTECTING','IDLE','OFFLINE') — NOT NULL
  • operation_mode — ENUM('MANUAL)CONTROL','NAVIGATE_TO_TARGET','AUTONOMOUS')
  • behavior_mode — ENUM('IDLE','PATROL','SEARCH','CHILD_MONITOR','APPROACH','PROTECT_MODE')
  • battery_level — INT — NOT NULL
  • is_online — BOOLEAN — NOT NULL
  • position_x — DOUBLE
  • position_y — DOUBLE
  • floor — INT
  • zone — INT
  • last_connected_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

snapshot_images (case_images)

  • id — BIGINT — Primary key — NOT NULL
  • case_id — BIGINT — Foreign key → cases.id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • captured_at — TIMESTAMP — NOT NULL