GET /v1/alerts?origin=X causes systematic timeout (25s+) while same query without origin= responds in 13ms

[jmrGrav]

Description

Calling GET /v1/alerts?has_active_decision=true&origin=X against the local LAPI hangs and eventually times out (>25s, observed up to 20s+ on every call), while the same request without the origin= parameter completes in ~13ms with HTTP 200. This makes cscli decisions list --origin X unusable in scripts (and breaks any tooling that relies on it, e.g. cron jobs that filter decisions by origin).

The bottleneck is not SQLite — the underlying SQL query completes in <100ms with or without an index on decisions.origin. The hang is somewhere in the Go handler for the origin filter on this route.

Environment

• CrowdSec: v1.7.8-debian-pragmatic-amd64-63227459 (Codename: alphaga, BuildDate: 2026-05-11)
• GoVersion: 1.26.2
• OS: Ubuntu 24.04.4 LTS, kernel 6.17.0-23-generic
• DB backend: SQLite (44 MB, ~44k rows in decisions, ~33k active)
• LAPI binding: 127.0.0.1:8080 (local-only)

Steps to reproduce

# Get a JWT
LOGIN=$(grep "^login:" /etc/crowdsec/local_api_credentials.yaml | awk '{print $2}')
PASS=$(grep "^password:" /etc/crowdsec/local_api_credentials.yaml | awk '{print $2}')
JWT=$(curl -s -X POST http://127.0.0.1:8080/v1/watchers/login \
  -H "Content-Type: application/json" \
  -d "{\"machine_id\":\"$LOGIN\",\"password\":\"$PASS\"}" \
  | jq -r .token)

# Fast — completes in ~13ms
time curl -s -m 25 -H "Authorization: Bearer $JWT" \
  "http://127.0.0.1:8080/v1/alerts?has_active_decision=true&include_capi=false&limit=100" \
  -o /dev/null -w "HTTP %{http_code} time=%{time_total}s\n"

# Hangs — times out (HTTP 000 after 25s)
time curl -s -m 25 -H "Authorization: Bearer $JWT" \
  "http://127.0.0.1:8080/v1/alerts?has_active_decision=true&include_capi=false&limit=100&origin=cscli" \
  -o /dev/null -w "HTTP %{http_code} time=%{time_total}s\n"

Observed output:

HTTP 200 time=0.013825s
HTTP 000 time=25.002199s

Same behaviour through cscli:

$ time cscli decisions list -o json | wc -c           # ~0.3s, returns full payload
$ time cscli decisions list --origin cscli -o json    # always 15s timeout (cscli's internal timeout)
$ time cscli decisions list --origin cscli            # same timeout in table mode
$ time cscli decisions list --value 1.2.3.4 -o json   # ~0.2s, works
$ time cscli decisions list --scope Ip -o json        # ~0.2s, works

Only the origin filter is affected. Reproducible with any value of origin (cscli, crowdsec, lists, CAPI).

Investigation

To rule out the database as the bottleneck:

1. Existing indexes on decisions: decision_value, decision_until, decision_alert_decisions, decision_start_ip_end_ip. No index on origin.
2. Direct SQLite benchmark of the equivalent JOIN (alerts ⨝ decisions filtered by origin and until > now()): 70ms (uses decision_until index).
3. Created CREATE INDEX idx_decision_origin_until ON decisions(origin, until) + ANALYZE → SQL query drops to 21ms with EXPLAIN QUERY PLAN confirming use of the new index.
4. cscli --origin still times out at 20s with the new index in place. Index dropped afterwards.

→ The handler is not SQL-bound. The hang happens elsewhere in the Go path for the origin= query parameter. Possible candidates: a misbehaving filter loop, an N+1 fetch, a deadlock, or unbounded pagination. The TCP connection from cscli/curl stays in ESTABLISHED state during the whole hang, then the client times out.

Expected behavior

GET /v1/alerts?origin=X should return in the same order of magnitude as the unfiltered query (10s–100s of ms), since filtering reduces the result set rather than expanding it.

Workaround

Drop the origin= filter and filter client-side:

# Fetch all active alerts (one call, ~300ms)
result = subprocess.run(
    ["cscli", "decisions", "list", "-o", "json"],
    capture_output=True, text=True, timeout=15,
)
alerts = json.loads(result.stdout)

# Filter by origin in Python
cscli_bans = {
    dec["value"]
    for alert in alerts
    for dec in (alert.get("decisions") or [])
    if dec.get("origin") == "cscli"
    and dec.get("type") == "ban"
    and dec.get("scope", "").lower() == "ip"
}

This is a strict regression for callers that need the filter, but works around the issue completely.

---

Happy to provide additional diagnostics (pprof, strace, journalctl, full schema dump) if useful.

[github-actions]

@jmrGrav: Thanks for opening an issue, it is currently awaiting triage.

In the meantime, you can:

1. Check Crowdsec Documentation to see if your issue can be self resolved.
2. You can also join our Discord.
3. Check Releases to make sure your agent is on the latest version.

Details

I am a bot created to help the crowdsecurity developers manage community feedback and contributions. You can check out my manifest file to understand my behavior and what I can do. If you want to use this for your project, you can check out the BirthdayResearch/oss-governance-bot repository.

[github-actions]

@jmrGrav: There are no 'kind' label on this issue. You need a 'kind' label to start the triage process.

• /kind feature
• /kind enhancement
• /kind refactoring
• /kind bug
• /kind packaging

Details

I am a bot created to help the crowdsecurity developers manage community feedback and contributions. You can check out my manifest file to understand my behavior and what I can do. If you want to use this for your project, you can check out the BirthdayResearch/oss-governance-bot repository.

[jmrGrav]

/kind bug

[jimstrang]

Also working this in #4464. Interesting I found it to be explicitly SQLite performance and not elsewhere in the code. Will investigate your findings as well.

[jmrGrav]

Bug still present in v1.7.8 (May 2026)

Reproducing on freshly updated v1.7.8-debian-pragmatic-amd64-63227459 (BuildDate 2026-05-11), Go 1.26.2, Ubuntu 24.04, SQLite backend (43 MB).

Test results

origin= filter	decisions in DB	alerts (via JOIN)	HTTP	time
(none)	24 589	108	200	22 ms
cscli	102	102	000 (curl timeout)	25.0 s ❌
crowdsec	0	0	200	26 ms
lists	9 487	3	000 (curl timeout)	25.0 s ❌
CAPI	15 000	1	200	22.9 s ⚠️

Query: GET /v1/alerts?has_active_decision=true&include_capi=false&limit=100&origin=<value>

Key observation: scales with child decisions, not alerts

• origin=crowdsec returns 0 rows → fast (no payload to serialize)
• origin=cscli: 102 alerts × 102 decisions → timeout
• origin=lists: only 3 alerts but 9 487 child decisions → timeout
• origin=CAPI: 1 alert but 15 000 child decisions → 22.9 s (just under the 25 s curl timeout)

The limit=100 parameter does not cap response time — origin=CAPI returns a single alert but still takes ~23 s. This strongly suggests the limit is applied after eager-loading child decisions, or that JSON serialization of the eager-loaded entity graph is the bottleneck.

Re @jimstrang's SQLite analysis

Confirmed: the raw SQL is ≤21 ms even on this 43 MB DB. The hang is inside the Go handler, not SQLite. Indexes present on decisions: decision_alert_decisions, decision_start_ip_end_ip, decision_until, decision_value (none on origin column, but this isn't the bottleneck since origin=crowdsec with zero rows is fast).

Production workaround in place

Client-side filter via Python: fetch /v1/alerts?has_active_decision=true (no origin=) — fast — then filter in-process before pushing to Cloudflare. Extra bandwidth, but unblocks crowdsec-cf-sync.py.

Suggested investigation

Profile the alerts handler with origin=lists or origin=CAPI. The fix likely lives in how child decisions are eager-loaded / paginated — applying limit at the alert level before the JOIN to decisions, or returning truncated decision lists, would unblock this.

---

Setup: NUC8i3BEH (Ubuntu 24.04), CrowdSec from packagecloud.io/crowdsec/crowdsec/ubuntu noble main.

[blotus]

Hello,

This is likely a duplicate of #4464.

Could you provide either:

• Some more informations about the number of alerts, the origins, the type of machine you are running the query on, ...: I cannot reproduce this on a DB with around 7k alerts (and almost 200k decisions)
• Did you observe this behaviour before or after the upgrade the 1.7.8 ? (your 2nd comment Bug still present in v1.7.8 makes it a bit unclear)
• If possible, could you send the DB file(s) (/var/lib/crowdsec/data/crowdsec.db*, including the shm and wal files) to support[at]crowdsec.net ? You can truncate the bouncers and machines tables if you want to avoid sending us the hashed credentials of your bouncers/LP.

[jmrGrav]

Hi @blotus, thanks for the quick triage. Confirming it is very likely the same root cause as #4464. Details below.

Environment

• Host: Intel NUC8i3BEH (small home box), Ubuntu 24.04.4, kernel 6.17.0-23-generic
• DB: SQLite, currently 44 MB on disk + 16 MB WAL
• LAPI: local-only (127.0.0.1:8080)
• Version installed today: v1.7.8-debian-pragmatic-amd64-63227459 (BuildDate 2026-05-11), Go 1.26.2
• Repo: packagecloud.io/crowdsec/crowdsec/ubuntu noble main

DB stats (live, today 2026-05-18)

alerts      : 159
decisions   : 31 765

Decisions per origin:

origin	count
CAPI	20 456
lists	11 188
cscli	118
crowdsec	3

Reproduction on this host (live, just now)

Same DB, same machine, two back-to-back runs:

time sudo cscli decisions list                  → real 0m0.289s
time sudo cscli decisions list --origin lists   → real 1m34.195s

So with ~31k active decisions the hang manifests as a ~94 s wait rather than a clean 25 s curl timeout — same symptom shape as in the issue body, scales with the number of child decisions that match the filter.

Before or after 1.7.8?

I want to answer this honestly because my second comment was indeed ambiguous.

apt history on this host:

date (local, Europe/Paris)	event
2026-03-21	crowdsec 1.4.6 installed from Ubuntu archive
2026-03-24 22:56	upgrade 1.4.6 → 1.7.6 (switched to packagecloud)
2026-03-30 18:00	upgrade 1.7.6 → 1.7.7
2026-05-11 16:00	upgrade 1.7.7 → 1.7.8

The bug was first observed and diagnosed on 1.7.8. I cannot confirm whether 1.7.7 was already affected, because my Cloudflare sync script (crowdsec-cf-sync.py) had always pulled the full decision set and filtered by origin client-side — so the --origin server-side path was simply never exercised on 1.7.6/1.7.7 from this host.

I added a code path that used cscli decisions list --origin X on 2026-05-16 (five days after the 1.7.8 upgrade) and the timeout was immediate and reproducible, which led to filing this issue. So the only honest statement is: observed on 1.7.8; unable to determine from available logs whether 1.7.7 was affected.

Workaround currently in production

Single cscli decisions list -o json call (no --origin), then filter by origin in Python before pushing to Cloudflare / AbuseIPDB. Works reliably, ~0.3 s per run. Kept as a comment in the script:

# Évite `--origin X` qui provoque un full-scan SQLite (~110s).

DB sample

Happy to send the file. I've prepared a redacted copy:

• copied /var/lib/crowdsec/data/crowdsec.db{,-shm,-wal}
• DELETE FROM bouncers; DELETE FROM machines; VACUUM;
• result: single crowdsec.db (~15 MB after vacuum), 159 alerts + 31 765 decisions intact, no hashed credentials

I'll send it to support@crowdsec.net with a pointer to this issue. Let me know if you'd prefer the un-vacuumed .db + -shm + -wal triplet instead.

Happy to run pprof / strace / extra journalctl extracts on demand — the box stays up.