Kpa-clawbot/CoreScope v2.9.0

v2.9.0 — Database Optimization & Developer Experience

on GitHub

🗜️ Database Optimization

The observations table has been completely rearchitected for efficiency. Existing instances migrate automatically on startup — no manual action needed.

• 70% smaller database — 478MB → 141MB on a real-world instance with 950K observations
• Observer IDs stored as integer foreign keys instead of 64-char hex strings repeated per row
• Redundant columns removed (hash, observer_name, created_at)
• Timestamps stored as epoch integers instead of ISO strings
• In-memory dedup Set prevents expensive unique index lookups during ingestion

Migration Safety

• Timestamped backup created automatically before migration (never overwrites previous backups)
• Detects already-migrated databases (safe to restart mid-migration)
• Falls back gracefully if migration fails — original data preserved
• packets_v view maintains full backward compatibility — no frontend changes needed

WAL Management

• Daily TRUNCATE checkpoint at 2:00 AM UTC reclaims WAL file space
• VACUUM + TRUNCATE checkpoint runs after migration completes

📊 SQLite Observability

New section in the Performance Dashboard (#/perf) showing:

• DB file size, WAL size, freelist (wasted space)
• Row counts for all tables
• WAL busy pages — shows if checkpointing is keeping up
• Color-coded thresholds (green/yellow/red)

🐛 Bug Fixes

• disambiguateHops: Restored known field that was dropped during a refactor — hop resolution in analytics now correctly reports whether a prefix was resolved
• E2E tests: Fixed channel message queries for #hashtag channels (missing URL encoding)
• MQTT in tests: Tests no longer connect to real MQTT brokers — NODE_ENV=test skips MQTT setup

🏗️ Setup & Operations

• manage.sh — new idempotent setup wizard for Docker deployments
• Backup/restore now includes config, Caddyfile, and theme
• Deployment guide rewritten for beginners with Mermaid diagrams
• Live page packet rendering unified into renderPacketTree()

🧪 Tests

839 tests across 9 suites, 0 failures:

• 30 new migration tests (schema upgrade, idempotency, backup safety, crash recovery)
• 19 new v3 schema tests (ingestion, dedup, view compatibility)
• All pre-existing test suites updated and passing