Database Patterns

Database Patterns

Devices Table — Write-Path Inventory

Before implementing any feature that reads or writes the Devices table, audit ALL write paths. The table is modified from many locations — missing one path is a correctness bug.

Known production write paths:

Key insight: Most scan functions use sql.executemany() — there is no per-row Python state available. Python hooks before/after executemany require a pre-fetch+diff pattern that is expensive and error-prone.

*Source Fields — Attribution System

The FIELD_SOURCE_MAP in server/db/authoritative_handler.py defines 10 fields that carry write attribution via paired *Source columns:

FIELD_SOURCE_MAP = {
    "devMac":           "devMacSource",
    "devName":          "devNameSource",
    "devFQDN":          "devFQDNSource",
    "devLastIP":        "devLastIPSource",
    "devVendor":        "devVendorSource",
    "devSSID":          "devSSIDSource",
    "devParentMAC":     "devParentMACSource",
    "devParentPort":    "devParentPortSource",
    "devParentRelType": "devParentRelTypeSource",
    "devVlan":          "devVlanSource",
}

*Source values: 'USER', 'LOCKED', 'NEWDEV', or a plugin prefix (e.g., 'ARPSCAN', 'NSLOOKUP').

These fields are updated in the same transaction as the primary field. A SQLite AFTER UPDATE trigger can read NEW.devNameSource to obtain correct attribution without any extra context-passing.

Attribution rules for features that need changedBy:

Cross-Cutting Concerns — Prefer SQLite Triggers Over Python Hooks

When a feature needs to intercept every write to the Devices table (audit logging, computed columns, cascading logic), prefer a SQLite AFTER UPDATE / AFTER INSERT trigger over Python-layer hooks.

Why:

• Triggers catch all 14+ write paths automatically, including executemany() bulk updates
• Zero modifications to existing write-path functions (DRY)
• Self-healing: future write paths are automatically covered
• Attribution is available via NEW.*Source fields (see above)

When Python hooks are still appropriate:

• The logic needs access to Python objects, settings, or services not available in SQL
• The feature only fires from one or two known write paths
• The logic is too complex to express in SQL (multi-table joins with app-layer business logic)

Trigger Performance Pattern

CREATE TRIGGER trg_example
AFTER UPDATE ON Devices
FOR EACH ROW
-- Guard: short-circuit entire body when feature is disabled (zero cost)
WHEN (SELECT CAST(setValue AS INTEGER) FROM Settings WHERE setKey = 'FEATURE_ENABLED') > 0
BEGIN
  -- Per-field conditional insert
  INSERT INTO SomeTable (devGUID, column, oldVal, newVal, changedBy, ts)
  SELECT NEW.devGUID, 'devName', OLD.devName, NEW.devName,
         COALESCE(NULLIF(NEW.devNameSource, ''), 'system'),
         datetime('now', 'utc')
  WHERE OLD.devName IS NOT NEW.devName
    AND instr(',' || (SELECT setValue FROM Settings WHERE setKey = 'TRACKED_FIELDS') || ',', ',devName,') > 0;
  -- Repeat for each tracked field...
END;

Performance: The Settings table is tiny (~100 rows) and stays in SQLite's page cache. Per-row Settings reads inside triggers are effectively in-memory lookups. The WHEN guard makes the disabled state zero-cost.

Snapshot vs Event-Sourced Audit Logging

When implementing change history, always use event-sourced (per-field rows) not snapshots (full row copies).

At 1000 devices, 5-min scan interval, 14-day retention: snapshot storage ≈ 280 MB/day. Event-sourced storage for the same workload is typically <1 MB/day (most scans produce no tracked field changes).

DevicesHistory Table — Reference Schema

CREATE TABLE IF NOT EXISTS DevicesHistory (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    devGUID      TEXT NOT NULL,
    timestamp    DATETIME DEFAULT CURRENT_TIMESTAMP,
    changedBy    TEXT NOT NULL,
    changedColumn TEXT NOT NULL,
    oldValue     TEXT,
    newValue     TEXT,
    FOREIGN KEY (devGUID) REFERENCES Devices(devGUID) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_devhist_guid_column ON DevicesHistory(devGUID, changedColumn);
CREATE INDEX IF NOT EXISTS idx_devhist_timestamp   ON DevicesHistory(timestamp);