add-stats-dimension

name	add-stats-dimension
description	Checklist for adding or changing a statistics table/dimension in the collector. Use whenever touching SQLite schema, traffic write paths, retention, ClickHouse parity, or stats query APIs — several places must change together or data silently drifts.

Add / Change a Stats Dimension

The stats pipeline has one write implementation but several coupled registries. Missing any step below produces silent data loss or drift — none of it fails loudly. Work through the checklist in order.

1. Schema (single source of truth)

• Add the CREATE TABLE / CREATE INDEX statements to apps/collector/src/database/schema.ts (SCHEMA + INDEXES). Nothing else creates tables.
• Existing databases only get IF NOT EXISTS DDL from schema.ts. Column additions and index drops need an explicit migration in apps/collector/src/modules/db/db.ts init() (see the ALTER TABLE ... ADD COLUMN and DROP INDEX IF EXISTS precedents there).
• Do NOT add expression indexes on (total_download + total_upload) — hot queries filter by backend_id first, so the planner never uses them (verified with EXPLAIN QUERY PLAN); they only amplify writes.

2. Write path (exactly one)

• All traffic writes go through batchUpdateTrafficStats in apps/collector/src/database/repositories/traffic-writer.repository.ts. updateTrafficStats is a thin wrapper over it — never add a second write implementation.
• Add your aggregation map in the batch loop and the upsert in the appropriate transaction group. The whole flush is one outer transaction; keep it that way (a partial commit + buffer retry double-counts).
• CSV-style association columns (, , , ) must dedupe with the delimiter-aware pattern — bare treats as present when is: