[MEDIUM] Resource management: DB read pool unlimited, no automated WAL/VACUUM, ~35 handlers missing rows.Err()

[physercoe]

Summary

1. Read pool has no connection limit

db.go:129 — s.db has no SetMaxOpenConns set. Go default is unlimited. Under sustained high throughput (especially with SSE streams holding connections), this can exhaust file descriptors.

2. No automated WAL checkpoint or VACUUM

• The only online WAL checkpoint is PRAGMA wal_checkpoint(PASSIVE) in handlers_hub_stats.go:118 — passive, may never complete under write load
• VACUUM is CLI-only (hub-server db vacuum) or admin-endpoint-only
• WAL file can grow unbounded (tens of GB reported) between manual operations

3. ~35 handlers missing rows.Err() after iteration

defer rows.Close() is present, but rows.Err() is never checked after rows.Next() loops. Iteration errors (truncation, disk corruption) go silently undetected. This is pervasive across handlers_*.go files.

4. Host-runner HTTP transport has low connection pool

hostrunner/client.go:40 — uses http.DefaultTransport with MaxIdleConnsPerHost=2. Under concurrent agent management, this causes connection churn (TCP handshake overhead for every request beyond 2 concurrent connections).

5. Manual rows.Close() without defer in several files

handlers_insights.go, otlp_export.go, phase_completion_gate.go, handlers_criteria.go, handlers_search_sessions.go, handlers_agent_events.go, loop_hooks.go — use manual close. Fragile against future early-return code changes.