Status: accepted Date: 2026-07-31
The processor needs durable storage for two things: per-group checkpoints (offset plus serialized detector state) and the alert log. The deployment unit is a single-node agent that runs next to the data source, often on a fab-floor edge box with no operations team nearby.
Options considered: SQLite (stdlib), Redis (AOF persistence), Postgres.
One SQLite database file holds both the checkpoint table and the alert table, in WAL mode. Deliberately boring.
- The exactly-once mechanism needs the alert batch and the checkpoint that covers it to commit atomically. In SQLite that is a single transaction on one file. With Redis or Postgres the store and the offset commit live in different failure domains, which resurrects the distributed atomic-write problem this design exists to avoid.
- Zero operational surface: no server process, no credentials to rotate, no port open on an OT network segment, backup is copying one file. Redis with AOF and fsync-per-write is no faster than SQLite in WAL mode for this write pattern and adds a process to babysit.
- Throughput is nowhere near SQLite's ceiling: measured checkpoint cadence is one transaction per 500 events, about 30 transactions/second at the measured 15,700 events/sec, and the worst observed commit stall was under half a second.
- Reads are trivial (alert export, one checkpoint row per group), so Postgres query power buys nothing here.
- Single-writer: this store binds one processor to one database file. That is exactly the single-node agent model, and horizontal scale is handled one layer up, not by swapping the store: partition the telemetry topic by tool, run one agent per partition group, each with its own SQLite file. The Kafka path (broker/kafka.py plus docker-compose.yml) exists for that topology.
- Move to Postgres only when a requirement genuinely crosses node boundaries: a fleet-wide alert view with concurrent writers, retention and access-control policies on alerts, or checkpoint state that must survive the loss of the node itself. Move to Redis approximately never; it solves a latency problem this system does not have.