WiFi/firmware:
- SSID updated to botnet in both node and ble_node config.h
- Stale coordinator comment removed from node/config.h
Coordinator fixes:
- BLE-only nodes now visible in dashboard sidebar and node detail
(build_nodes was querying beacon_events only; BLE nodes have no beacons)
- ble_events and heartbeat_events added to pruning cycle
- ble_events table added to ensure_schema() in dashboard
- confidence column dropped from beacon_events (always 'high', never queried)
- Ingestor commits batched per packet instead of per store call
- wal_autocheckpoint=500 added to ingestor DB connection (writer was missing it)
- python3 -u added to both service ExecStart lines (stdout was buffered,
logs not appearing in journalctl)
Repo hygiene:
- .gitignore added (events.db, __pycache__, build artifacts)
- events.db removed from git tracking
- README updated: new apartment, node table, active investigations,
BLE observations labelled as previous-location data, status checklist
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Root cause: SQLite's default PASSIVE checkpoint mode cannot complete
under sustained write load (4 nodes, hundreds of events/min) because
active readers always block it. The WAL grows unbounded and eventually
corrupts the main DB file.
- Add dedicated checkpoint_wal() using PRAGMA wal_checkpoint(RESTART),
which waits for readers to finish then checkpoints fully and resets
the WAL back to the start.
- Add background checkpointer task running every 30 minutes, separate
from the 6-hour prune cycle.
- Remove PASSIVE checkpoint from prune_old_events(), replace with call
to checkpoint_wal() after pruning completes.
- Set wal_autocheckpoint=500 on every connection (~2MB threshold) so
SQLite's own auto-checkpoint also triggers more frequently.
- Reduce HOT_DAYS from 30 to 7: at current data rate (~500k beacons/day)
30-day retention would grow to several GB before first prune runs.
7 days keeps the DB under ~200MB and queries fast permanently.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Wrap all build_* route calls in asyncio.to_thread() so SQLite queries
run in a thread pool instead of blocking the event loop. Fixes tab
switch freezes and SSE stream stalls.
- Add 60s TTL caches to alerts, sessions, presence, and cross-node
endpoints (previously uncached, queried on every request).
- Scope alerts queries (top targets, devices, reason breakdown, heatmap)
to last 7 days instead of all-time full table scans.
- Add composite indexes: (bssid, received_at) on beacon_events and
deauth_events, (src_mac, received_at) on probe_events.
- Add background pruning task: runs every 6 hours, deletes events older
than HOT_DAYS from all tables, followed by WAL checkpoint.
- Document the full diagnosis and fixes in README.md.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
bot