AEGIS

Tables (13)

admin_actions

  • id — BIGINT
  • admin_id — BIGINT — Foreign key → users.id — NOT NULL
  • action — VARCHAR(255)
  • target_table — VARCHAR(50)
  • target_id — BIGINT
  • created_at — DATETIME

ai_response_rules

  • id — BIGINT
  • event_type — VARCHAR(50)
  • severity_threshold — INT
  • actions — JSON
  • enabled — BOOLEAN

ai_responses

  • id — BIGINT
  • event_id — BIGINT — Foreign key → events.id — NOT NULL
  • action_type — VARCHAR(50)
  • status — ENUM('pending','success','failed')
  • result — TEXT
  • error_message — TEXT
  • executed_at — DATETIME

auth_tokens

  • id — BIGINT
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • access_token — VARCHAR(500) — NOT NULL
  • refresh_token — VARCHAR(500) — NOT NULL
  • expires_at — DATETIME — NOT NULL
  • created_at — DATETIME

camera_ptz_logs

  • id — BIGINT
  • camera_id — BIGINT — Foreign key → cameras.id — NOT NULL
  • action — ENUM('left','right','up','down','zoom_in','zoom_out') — NOT NULL
  • created_at — DATETIME

camera_uptime_stats

  • id — BIGINT
  • camera_id — BIGINT — Foreign key → cameras.id — NOT NULL
  • period — ENUM('1d','7d','30d')
  • uptime_percentage — DECIMAL(5,2)
  • total_time — BIGINT
  • online_time — BIGINT

cameras

  • id — BIGINT
  • name — VARCHAR(100) — NOT NULL
  • location — VARCHAR(255)
  • rtsp_url — VARCHAR(500)
  • status — ENUM('online','offline','alert')
  • thumbnail_url — VARCHAR(500)
  • created_at — DATETIME
  • uptime — BIGINT
  • approved — BOOLEAN

event_acknowledgements

  • id — BIGINT
  • event_id — BIGINT — Foreign key → events.id — NOT NULL
  • acknowledged_by — BIGINT — Foreign key → users.id — NOT NULL
  • note — TEXT
  • acknowledged_at — DATETIME

event_feedback

  • id — BIGINT
  • event_id — BIGINT — Foreign key → events.id — NOT NULL
  • is_correct — BOOLEAN — NOT NULL
  • comment — TEXT — EX
  • created_at — DATETIME

events

  • id — BIGINT
  • camera_id — BIGINT — Foreign key → cameras.id
  • type — VARCHAR(50)
  • severity — INT
  • timestamp — DATETIME
  • status — ENUM('new','in_progress','resolved','false_positive')
  • ai_response — TEXT
  • snapshot_url — VARCHAR(500)
  • video_url — VARCHAR(500)

notification_settings

  • id — BIGINT
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • email_enabled — BOOLEAN
  • push_enabled — BOOLEAN
  • event_types — JSON

notifications

  • id — BIGINT
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • message — TEXT
  • type — VARCHAR(50)
  • is_read — BOOLEAN
  • created_at — DATETIME

users

  • id — BIGINT
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • profile_image — VARCHAR(255)
  • role — ENUM('user', 'admin')
  • approved — BOOLEAN
  • created_at — DATETIME
  • last_login — DATETIME
  • is_deleted — BOOLEAN