feat(observability): SQLite write-lock wait/hold instrumentation per component — make starvation detectable

[Kpa-clawbot]

Summary

The #1339 root cause (neighbor-builder write-lock starvation) was invisible to operators for ~3 days. We need first-class instrumentation for SQLite write-lock acquisition latency + duration so the next regression of this class is detectable from operator metrics.

Why this matters now

• refactor(db): finish #1283 — Option 4: ingestor owns neighbor-graph + schema migrations; server is read-only (fixes #1287) #1289 added a periodic builder that silently held the write lock for 2+ minutes
• No metric exposed lock-wait time, lock-hold time, or contention count
• The symptom (low ingest rate) looked like "broker not sending" until DB-level investigation
• paho's silent drops + 5s busy_timeout swallowed the actual SQLite contention

What to add

Wrap every Exec/tx.Begin on the writer connection in instrumentation:

1. wait_ms histogram: time spent waiting for the write lock before the query actually starts
2. hold_ms histogram: time the writer holds the lock during the query
3. contention_total counter: count of writes that had to wait > 100ms
4. slow_writer_log: log writer name + duration whenever hold_ms > 500ms (configurable)

Expose via the existing /api/perf endpoint structure (already has per-component metrics from #1123).

Per-writer attribution

The infra to distinguish writers exists in #1123's per-component IO metrics. Use the same component tags:

• neighbor_builder
• mqtt_handler (transmission/observation inserts)
• prune_packets, prune_observers, prune_metrics
• mbcap_persist
• vacuum

Acceptance

• /api/perf includes db.writer.wait_ms_p50/p95/p99 and db.writer.hold_ms_p50/p95/p99 per component tag
• Log on hold_ms > 500ms: [db-slow-writer] component=X duration=Y query=<truncated>
• Test: synthetic 60s-blocking query → wait_ms > 50000 for subsequent writes; log line emitted
• Mutation: revert the instrumentation → metrics gone, test fails
• CI gate: add a smoke test that asserts neighbor-builder hold_ms < 1000ms on a fixture with 100k observations

Out of scope

• Replacing SQLite with a multi-writer DB
• Per-statement timing (too granular)
• Cross-process tracing

References

• bug(ingestor): neighbor-builder full-table scan every 60s holds SQLite write lock → 97% packet drop #1339 (the bug that motivated this)
• bug(db): vacuumOnStartup fails with SQLITE_BUSY when ingestor + server share DB (auto_vacuum migration #919 broken in single-container topology) #1283/followup #1283: 13 remaining cachedRW write sites in cmd/server/ — schema migrations, backfills, neighbor persistence #1287/refactor(db): finish #1283 — Option 4: ingestor owns neighbor-graph + schema migrations; server is read-only (fixes #1287) #1289 server-readonly refactor — introduced the new ingestor write paths
• feat(perf): per-component disk I/O + write source metrics on Perf page (#1120) #1123 per-component disk IO metrics
• cmd/ingestor/db.go cachedRW / Exec call sites