Database Schema

DefenSys uses SQLite (desktop) and optionally PostgreSQL (web API). This page documents the desktop SQLite schema.

Location

Database file: db/defensys.db

Tables

alerts

CREATE TABLE alerts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  rule_id TEXT,
  rule_name TEXT NOT NULL,
  feed_source TEXT,
  mitre_techniques TEXT,
  severity TEXT NOT NULL,
  confidence INTEGER DEFAULT 50,
  src_ip TEXT NOT NULL,
  dst_ip TEXT NOT NULL,
  src_port INTEGER,
  dst_port INTEGER,
  protocol TEXT,
  raw_payload_snippet TEXT,
  geolocation TEXT,
  false_positive BOOLEAN DEFAULT 0,
  acknowledged BOOLEAN DEFAULT 0,
  timestamp DATETIME,
  created_at DATETIME
);

traffic_logs

CREATE TABLE traffic_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp DATETIME,
  src_ip TEXT NOT NULL,
  dst_ip TEXT NOT NULL,
  src_port INTEGER,
  dst_port INTEGER,
  protocol TEXT,
  size INTEGER,
  payload_snippet TEXT,
  interface TEXT,
  created_at DATETIME
);

detection_rules

CREATE TABLE detection_rules (
  rule_id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  pattern TEXT,
  enabled BOOLEAN DEFAULT 1,
  type TEXT DEFAULT 'signature',
  severity TEXT DEFAULT 'medium',
  description TEXT,
  mitre_techniques TEXT,
  created_at DATETIME,
  updated_at DATETIME
);

historical_threats

Stores threat records with severity, IPs, ports, ML score, blocked status, etc.

ip_reputation

Risk score, threat count, first/last seen per IP.

attack_patterns

Pattern type, source IP, target ports, occurrences, severity.

system_settings

CREATE TABLE system_settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  description TEXT,
  updated_at DATETIME
);

Indexes

• idx_traffic_logs_timestamp, idx_traffic_logs_src_ip, idx_traffic_logs_dst_ip
• idx_alerts_timestamp, idx_alerts_severity, idx_alerts_src_ip, idx_alerts_dst_ip