| name | db-conventions |
| description | Data-layer invariants for the collector — SQLite patterns, transactions, time keys, backendId isolation, ClickHouse read/write topology. Use when writing queries, touching repositories, or debugging data drift. |
Data Layer Conventions (apps/collector)
Invariants (breaking any of these has bitten us before)
- One write path. All traffic stats go through
batchUpdateTrafficStats (database/repositories/traffic-writer.repository.ts); the whole flush is a single outer transaction (inner db.transaction() calls degrade to savepoints). Never commit table groups independently — a mid-flush failure plus buffer retry double-counts.
- Rule name = policy group. For multi-hop chains the aggregation key is the last chain hop (the user's policy group), produced only by
buildRuleName (shared/utils/rule-name.ts). Rule type + payload (RuleSet(foo)) is the key only for single-hop DIRECT/REJECT chains. The web flow graph and gateway-rule matching depend on this.
- Delimiter-aware CSV dedup. Comma-list columns dedupe with
INSTR(',' || col || ',', ',' || @v || ',') — bare INSTR substring-matches (a.com inside aa.com) and silently drops values. Lists are capped at LENGTH > 4000.
- UTC time keys. Minute
YYYY-MM-DDTHH:MM:00, hour YYYY-MM-DDTHH:00:00, via toMinuteKey/toHourKey (base.repository.ts). No local-time truncation.
- backendId isolation everywhere. Every stats table, query, and cache key is scoped by
backend_id. PRAGMA foreign_keys is OFF — deleting a backend relies on the explicit table list in deleteBackendData; new tables must be added there.
- Range routing. Range queries route by span in
resolveFactTable (base.repository.ts): ≤6h → minute_dim_stats, >6h → hourly_dim_stats. Retention must respect this: hourly dim tables follow hourlyStatsDays, minute tables follow connectionLogsDays.
SQLite usage
- better-sqlite3, synchronous — long scans and big transactions block the event loop shared with WS ingest. Cap query result sets (
LIMIT), use Map merges instead of nested findIndex, and prefer prepared statements cached on the instance for hot paths.
- WAL mode with tuned pragmas is set in
modules/db/db.ts init() (env-tunable: SQLITE_CACHE_MB, SQLITE_WAL_AUTOCHECKPOINT_PAGES, SQLITE_BUSY_TIMEOUT_MS).
- Migrations are ad-hoc in
init() — idempotent (IF NOT EXISTS / try-catch ALTER / app_config flags for one-time data migrations, see database/rule-name-cleanup.ts as the reference pattern).
ClickHouse topology
Read/write behavior is controlled by env flags — keep them coherent:
| Flag | Meaning |
|---|
CH_WRITE_ENABLED | Write traffic batches to ClickHouse |
STATS_QUERY_SOURCE | sqlite (default) / clickhouse / auto — read routing |
CH_ONLY_MODE=1 | Skip SQLite stats writes entirely (requires healthy CH writer) |
CH_STRICT_STATS=1 | Fail instead of silently falling back to SQLite reads |
Guard rails already enforced in code (modules/stats/stats-write-mode.ts): reduced SQLite writes require reads to actually prefer ClickHouse — don't bypass shouldReduceSqliteWrites/shouldSkipSqliteStatsWrites with ad-hoc env checks. When adding a range query, implement BOTH the SQLite repository method and the clickhouse.reader.ts counterpart with identical semantics, and route via a *WithRouting method in stats.service.ts.
Partial-failure contract
BatchBuffer.flush returns per-group outcomes (detailOk/aggOk); the realtime store is only cleared for the groups that durably persisted. When touching flush/clear logic, preserve this contract — it is the data-loss protection added in v1.3.9.
