schema.sql

-- Network state schema (SQLite dialect, demo)
-- Migrates cleanly to Postgres later: SERIAL -> INTEGER PRIMARY KEY AUTOINCREMENT already
-- SQLite-compatible; swap to Postgres types when you move off the demo DB.

CREATE TABLE IF NOT EXISTS devices (
    device_id      INTEGER PRIMARY KEY AUTOINCREMENT,
    hostname       TEXT,
    ip_address     TEXT UNIQUE,
    device_type    TEXT,   -- router, switch, firewall, host
    vendor         TEXT,
    location       TEXT,
    status         TEXT,   -- up, down, degraded
    last_seen      TEXT    -- ISO8601 timestamp
);

CREATE TABLE IF NOT EXISTS interfaces (
    interface_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id      INTEGER REFERENCES devices(device_id),
    name           TEXT,   -- eth0, Gi0/1, etc.
    status         TEXT,   -- up, down
    speed_mbps     INTEGER
);

CREATE TABLE IF NOT EXISTS traffic_flows (
    flow_id        INTEGER PRIMARY KEY AUTOINCREMENT,
    ts             TEXT,   -- ISO8601 timestamp
    src_ip         TEXT,
    dst_ip         TEXT,
    src_port       INTEGER,
    dst_port       INTEGER,
    protocol       TEXT,
    bytes          INTEGER,
    packets        INTEGER,
    duration_ms    INTEGER,
    device_id      INTEGER REFERENCES devices(device_id),
    label          TEXT    -- benign / attack type
);

CREATE TABLE IF NOT EXISTS alerts (
    alert_id       INTEGER PRIMARY KEY AUTOINCREMENT,
    ts             TEXT,
    device_id      INTEGER REFERENCES devices(device_id),
    severity       TEXT,   -- info, warning, critical
    alert_type     TEXT,   -- port_scan, dos, malware, link_down
    description    TEXT,
    resolved       INTEGER DEFAULT 0  -- 0/1 boolean
);

CREATE TABLE IF NOT EXISTS metrics_timeseries (
    metric_id      INTEGER PRIMARY KEY AUTOINCREMENT,
    ts             TEXT,
    device_id      INTEGER REFERENCES devices(device_id),
    interface_id   INTEGER REFERENCES interfaces(interface_id),
    metric_name    TEXT,   -- latency_ms, packet_loss_pct, bandwidth_util_pct
    value           REAL
);

CREATE INDEX IF NOT EXISTS idx_flows_device_ts ON traffic_flows(device_id, ts);
CREATE INDEX IF NOT EXISTS idx_alerts_device_ts ON alerts(device_id, ts);
CREATE INDEX IF NOT EXISTS idx_metrics_device_ts ON metrics_timeseries(device_id, ts);