Skip to content

Database

SQLite Schema

CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    timestamp TEXT,
    src_ip TEXT,
    src_port INTEGER,
    dest_ip TEXT,
    dest_port INTEGER,
    protocol TEXT,
    app_proto TEXT,
    json_data TEXT          # Full original eve.json line
);
CREATE INDEX idx_event_type ON events(event_type);
CREATE INDEX idx_timestamp ON events(timestamp);
CREATE INDEX idx_event_type_timestamp ON events(event_type, timestamp);

-- Composite indexes for Sankey/aggregation GROUP BY queries. event_type
-- leads every one so a single index serves both a specific-type-filtered
-- query and the merged "all events" view (see db.py's _ensure_ip_port_indexes)
CREATE INDEX idx_src_dest_ip ON events(event_type, src_ip, dest_ip);
CREATE INDEX idx_dest_ip_port ON events(event_type, dest_ip, dest_port);
CREATE INDEX idx_dest_port ON events(event_type, dest_port);
CREATE INDEX idx_src_port ON events(event_type, src_port);

-- Expression indexes for flow's JSON-extracted aggregation columns
-- (Pkts/Bytes/State/Alerted) - built dynamically from the same SQL
-- expression the query itself uses, so they can never drift out of sync
-- (see db.py's _ensure_flow_json_indexes). CAST(...AS TEXT) is required
-- for SQLite to treat the index as covering - a bare json_extract() has
-- no stable type affinity and gets re-verified against the row anyway.
CREATE INDEX idx_flow_pkts_toserver ON events(event_type, CAST(COALESCE(json_extract(json_data, '$.flow.pkts_toserver'), 0) AS TEXT));
CREATE INDEX idx_flow_pkts_toclient ON events(event_type, CAST(COALESCE(json_extract(json_data, '$.flow.pkts_toclient'), 0) AS TEXT));
CREATE INDEX idx_flow_bytes_toserver ON events(event_type, CAST(COALESCE(json_extract(json_data, '$.flow.bytes_toserver'), 0) AS TEXT));
CREATE INDEX idx_flow_bytes_toclient ON events(event_type, CAST(COALESCE(json_extract(json_data, '$.flow.bytes_toclient'), 0) AS TEXT));
CREATE INDEX idx_flow_state ON events(event_type, CAST(json_extract(json_data, '$.flow.state') AS TEXT));
CREATE INDEX idx_flow_alerted ON events(event_type, CASE WHEN CAST(json_extract(json_data, '$.flow.alerted') AS TEXT) THEN 'Yes' ELSE 'No' END);

-- FTS5 virtual table for full-text search (created when FTS5 is available)
CREATE VIRTUAL TABLE events_fts USING fts5(
    json_data,
    content='events',
    content_rowid='id'
);

-- Performance pragmas
PRAGMA journal_mode = WAL;      -- Better concurrency between readers and writers
PRAGMA synchronous = NORMAL;    -- Faster writes with WAL crash safety
PRAGMA busy_timeout = 30000;    -- Retry for 30s when database is locked
PRAGMA optimize;                -- Gather stats for query planner after bulk load

The json_data column stores the complete original event, allowing the server to return full eve.json objects without re-parsing the source file. An optional events_fts virtual table enables fast full-text search over all event data. If FTS5 is unavailable, searches fall back to json_data LIKE '%term%'.

The composite and expression indexes above are backfilled lazily (CREATE INDEX IF NOT EXISTS) onto pre-existing databases the first time get_sankey_data_sqlite/get_aggregation_data_sqlite runs against them, so upgrading never requires a migration step. Each backfill also re-runs PRAGMA optimize to keep the query planner's statistics current - without it, a planner working from stale/missing stats can pick an unhelpful index even for unrelated queries once several indexes share event_type as a leading column.

Query Performance

get_sankey_data_sqlite and get_aggregation_data_sqlite (db.py) run their independent GROUP BY queries concurrently via ThreadPoolExecutor - one SQLite connection per worker (connections aren't thread-safe to share), safe under WAL mode's multiple-simultaneous-readers guarantee.

Unfiltered (no search query) results from both functions are cached in-memory in socrates.py (_SANKEY_CACHE/_AGGREGATION_CACHE, keyed by (md5, event_type), guarded by a lock), since the underlying data never changes once an analysis finishes ingesting. The cache is evicted on /api/delete-analysis//api/reanalyze and cleared entirely on /api/delete-all-analyses. Search-filtered requests are never cached.

Known Limitations / Future Work

  • Arbitrary JSON-blob field filter/sort/aggregation scaling. Fast server-side sort/filter only has real indexed-column support for event_type, timestamp, IP, port, and severity. Every other field (e.g. alert.category, dns.rrname, http.url) lives inside the shared json_data blob column and is only reachable via json_extract() at query time, which doesn't scale the way an index does as row counts grow. A per-field fix (generated/indexed columns added one at a time) compounds in migration complexity with each field added; the architecturally correct fix is splitting events into separate, properly-typed tables per event type, but that's a real migration touching ingestion (suricata_analyzer.py, sigma_analyzer.py, db.py), every query function, and every event type's column mapping — a multi-week project in its own right, not undertaken without a concrete use case driving it.
  • JSON-column sort tiebreaker on near-single-valued fields. Sorting by a field with very low value diversity (e.g. flow.state, which is almost always the same handful of values) stays slow even after PRAGMA optimize, because the secondary timestamp tiebreaker still forces SQLite to sort the tied rows directly rather than use an index. This showed up against synthetic test data (1M rows, State column); it's unconfirmed whether real Suricata captures have enough value diversity in these fields for it to matter in practice. Not worth addressing without real-data evidence that it does.