LAPI: GET /v1/alerts stuck in sqlite3_step for hours, pinning one CPU core (v1.7.8, SQLite/WAL)

Start with FindAlerts in pkg/apiserver/controllers/v1/alerts.go and QueryAlertWithFilter in pkg/database/alerts.go; trace how the decisions edge is loaded, using the reported GET /v1/alerts?limit=0&since=24h request and SQLite setup as a reproduction guide. The issue asks for bounded query work and/or a server-side statement timeout. Done means the request no longer allows unbounded work to pin the database connection, with regression coverage for the behavior.

Summary

On a LAPI running v1.7.8 with the SQLite backend (WAL enabled), a single GET /v1/alerts request can get stuck inside sqlite3_step() indefinitely (3h+ and counting in my case), pinning one CPU core at 100%. Because the work happens in the cgo SQLite call, it is invisible to the Go CPU profiler (/debug/pprof/profile returns an essentially empty profile) and does not show as a running goroutine in a goroutine dump — it appears as a goroutine parked in [syscall]. The stuck query holds the connection and the DB stops being written to, so the LAPI effectively wedges while looking "healthy" (heartbeats and /health keep responding on other connections).

This is distinct from #4464 (which is a steady ~2x baseline CPU increase from the 1.7.8 decisions-stream change, scaling with bouncer count and visible in pprof). This report is about a single query that never returns and burns a whole core in native SQLite code.

Environment

• CrowdSec v1.7.8-63227459 (docker), Go 1.26.3, libre2 (cscli version confirms)
• Deployed via the official Helm chart on k3s (LAPI as a separate pod from agents)
• DB backend: type: sqlite, db_path: /var/lib/crowdsec/data/crowdsec.db, use_wal: true, flush max_items: 5000, max_age: 7d
• DB file size: 11.5 MB (small)
• A handful of bouncers (traefik plugin) + 3 agents + a web UI/dashboard client polling /v1/alerts
• CAPI enabled — cscli alerts list shows community-blocklist alerts each carrying very large decision sets (e.g. +2397/-0 IPs, +900/-0 IPs)

Symptom

• One core pinned at ~100% (cgroup CPU jumped from ~0.004 cores to a flat 1.00 core) starting at a precise instant and never recovering.
• The hot OS thread sits in R state and has accumulated ~96 min of CPU time and rising.
• DB file mtime froze at the exact moment the spin began; cscli decisions list returns nothing while the query holds the engine.
• Event throughput is unchanged/idle (~20 log lines/min) — the spin is not correlated with parsing/acquisition load.
• A 15-second curl /debug/pprof/profile returns only ~540 bytes (negligible samples) despite the core being saturated — consistent with time being spent in the cgo SQLite call, which SIGPROF cannot interrupt.

Evidence — goroutine dump (/debug/pprof/goroutine?debug=2)

The handler goroutine, blocked in rows.Next() for 182 minutes, with the full ent → SQLite path showing the source is FindAlerts / QueryAlertWithFilter:

goroutine 8974 [select, 182 minutes]:
github.com/mattn/go-sqlite3.(*SQLiteRows).Next(...)
        github.com/mattn/[email protected]/sqlite3.go:2535
database/sql.(*Rows).Next(...)
entgo.io/ent/dialect/sql/sqlgraph.(*query).nodes(...)
        entgo.io/[email protected]/dialect/sql/sqlgraph/graph.go:1012
github.com/crowdsecurity/crowdsec/pkg/database/ent.(*AlertQuery).sqlAll(...)
        github.com/crowdsecurity/crowdsec/pkg/database/ent/alert_query.go:509
github.com/crowdsecurity/crowdsec/pkg/database/ent.(*AlertQuery).All(...)
        github.com/crowdsecurity/crowdsec/pkg/database/ent/alert_query.go:266
github.com/crowdsecurity/crowdsec/pkg/database.(*Client).QueryAlertWithFilter(...)
        github.com/crowdsecurity/crowdsec/pkg/database/alerts.go:855
github.com/crowdsecurity/crowdsec/pkg/apiserver/controllers/v1.(*Controller).FindAlerts(...)
        github.com/crowdsecurity/crowdsec/pkg/apiserver/controllers/v1/alerts.go:299
... gin middleware chain ...

The actual cgo call, parked in [syscall] for the same 182 minutes (this is the thread burning the core):

goroutine 69682 [syscall, 182 minutes]:
github.com/mattn/go-sqlite3._Cfunc__sqlite3_step_internal(...)
        _cgo_gotypes.go:443
github.com/mattn/go-sqlite3.(*SQLiteRows).nextSyncLocked(...)
        github.com/mattn/[email protected]/sqlite3.go:2552
created by github.com/mattn/go-sqlite3.(*SQLiteRows).Next in goroutine 8974
        github.com/mattn/[email protected]/sqlite3.go:2531

All parser/bucket/output routines were idle in [select] the whole time, confirming the LAPI is otherwise doing nothing.

Likely trigger

The request is a recurring GET /v1/alerts?limit=0&since=24h poll (from a dashboard/web-UI client). My strong suspicion is that QueryAlertWithFilter eager-loads the decisions edge for matched alerts, and with CAPI community-blocklist alerts that each contain thousands of decisions, the ent-generated SQLite query degenerates into a pathological plan. A full scan of an 11.5 MB DB should take milliseconds; 3 hours of CPU implies effectively quadratic/exponential behavior (e.g. a large IN (...) edge load), not slow I/O (the thread is CPU-bound R, never in D).

Why this is a bug (not just "a slow query")

• 3h+ of CPU on an 11.5 MB database is not a tuning problem — the plan is degenerate.
• There is no statement/query timeout, so a single API request can wedge the LAPI DB connection indefinitely and silently (the request never logs because it never completes).
• The condition is reachable by a normal, repeating read endpoint that ships with the product and is hit by every dashboard/web-UI.

Workaround

Restart the LAPI pod — CPU immediately drops back to baseline. (Reproduces again once the offending /v1/alerts poll hits the degenerate plan.)

Asks

1. Bound QueryAlertWithFilter / FindAlerts (avoid unbounded eager-load of the decisions edge; paginate or cap edge loading).
2. Add a server-side statement timeout for LAPI DB queries so one request can't pin a core forever.

Happy to provide the full goroutine dump or test a patched build.