SQLite/DuckDB endpoint-chart parity: top-5 ranked on unfiltered percentiles, and the method filter silently became case-sensitive

[FrameAutomata]

Two defects in c7a5dcae, both in the endpoint filtering/charting path. Found during review of #309 / #312 (unrelated to that work). Both verified by reading the code on c7a5dcae; neither is reproduced against a live dataset yet.

---

1. SQLite chart top-5 is ranked on unfiltered percentiles (backend parity break)

backend/app/repositories/telemetry/sqlite/endpoint.repository.go:929

getTopEndpointsByMetric filters the candidate list with filterClause:

epRows, err := lit.SelectNamed[distinctEndpointRow](db.TelemetryDB,
    `SELECT DISTINCT endpoint FROM endpoints WHERE project_id = :project_id AND recorded_at >= :from AND recorded_at <= :to AND is_stream = 0`+filterClause,
    params)

…but then ranks each candidate with fetchSortedDurations, whose query (:844) carries no is_root, method, or search predicate:

"SELECT duration FROM endpoints WHERE project_id = :project_id AND endpoint = :endpoint AND recorded_at >= :from AND recorded_at <= :to AND is_stream = 0 ORDER BY duration ASC"

So candidates are selected under the filter but ordered by an unfiltered percentile, while getStackedChartWithPercentiles plots only the filtered rows.

Failure scenario. POST /api/endpoints/chart with metricType="p95" and rootFilter="root" on a dual-SQLite deployment. An endpoint whose non-root rows are slow but whose root rows are fast is ranked into the top 5 (or above a genuinely slower endpoint), yet the series plotted for it contains only root rows.

Parity impact. The DuckDB twin does not have this bug — its ranking query carries filterClause inline. The two embedded backends therefore return different top-5 lists for identical data, which is the class of divergence transactional/parity_test.go exists to prevent (though it covers the transactional axis, not this one).

---

2. LIKE → SUBSTR(...) = made the method filter case-sensitive

backend/app/repositories/telemetry/shared/sqlfilter.go:31

Before c7a5dcae:

return " AND " + qualifiedCol + " LIKE :method"

After:

return fmt.Sprintf(" AND SUBSTR(%s, 1, %d) = :method", qualifiedCol, len(prefix)), prefix

SQLite's LIKE folds ASCII case by default. Equality on SUBSTR is BINARY-collated — the endpoints table declares no COLLATE NOCASE — so the filter is now case-sensitive against a bound param that is always strings.ToUpper(method) + " ".

Why rows can be lowercase. otelcontrollers/trace_converter.go:462 getHTTPEndpoint reads http.request.method / http.method and concatenates it verbatim, with no ToUpper:

method := getStringAttribute(attrs, "http.request.method")
if method == "" {
    method = getStringAttribute(attrs, "http.method")
}
...
return method + " " + route

Any instrumentation reporting a lowercase method (the legacy http.method attribute is not constrained the way semconv constrains http.request.method) produces rows stored as get /api/users.

Failure scenario. Selecting GET in the endpoints filter on the default self-hosted backend returns zero rows for those endpoints where it previously returned them — silently, with no error.

The new test at endpoint_repository_test.go:214 codifies the new behaviour, so the regression for already-ingested data isn't called out anywhere.

Two candidate fixes, depending on intent:

• normalise at ingest (ToUpper in getHTTPEndpoint) — doesn't help rows already stored
• make the comparison case-insensitive (COLLATE NOCASE on the predicate, or UPPER(SUBSTR(...))) — keeps the index-friendly prefix form while restoring the old semantics

---

Not included

The same review flagged several lower-confidence items in c7a5dcae I did not verify and am not asserting: a discarded bind-arg list in the ClickHouse FindGroupedByEndpoint, an $effect race on /organization, the endpoints page applying unsubmitted filters to the chart on metric change, gofmt having interleaved project imports into stdlib blocks in three files, and gofmt/go vet running only in the SQLite CI job. Worth a look, but they need confirming before anyone acts on them.

[FrameAutomata]

Part 1 is fixed in #318, and it turned out to be one call site wider than described here — FindGroupedByEndpoint has the same split, so the endpoints list was showing a filtered count next to unfiltered P50/P95/P99 (and impact, which derives from P99). Reproduced live against a running backend, not just in tests; details in the PR.

Part 2 I did not change, because I don't think it's a defect as written. The history:

	embedded (SQLite/DuckDB)	ClickHouse
before #290	no method filter	no method filter
#290 (89a3e4dc)	LIKE 'GET %' — case-insensitive	startsWith(endpoint, 'GET ') — case-sensitive
c7a5dcae	SUBSTR(...) = 'GET ' — case-sensitive	unchanged

So the ~24 hours between #290 and c7a5dcae were the parity break, and c7a5dcae closed it by aligning the embedded backends onto ClickHouse's behaviour. It also shipped TestEndpointRepository_FindGroupedByEndpoint_MethodFilter, which inserts get /api/lowercase and asserts it is excluded — the exclusion is deliberate and tested, not incidental. SUBSTR over LIKE is independently worth keeping, too: methodFilter is user input, and LIKE would let % and _ through as wildcards.

The framing I'd push back on is "silently became case-sensitive." Behaviour did change on the embedded backends, but toward consistency, and all three backends now agree.

What's genuinely still open is a product question rather than a bug: lowercase-stored methods are unreachable from the UI. getHTTPEndpoint (trace_converter.go:462) concatenates http.request.method verbatim with no ToUpper, so get /api/lowercase rows really can exist; METHOD_OPTIONS only offers the 7 canonical values, which normalizeMethodFilter uppercases; so no dropdown selection can ever match such a row. Three ways to resolve it:

1. Leave it. RFC 9110 says method tokens are case-sensitive, and the list already groups get /x and GET /x as two separate endpoints — so the filter treating them as distinct is internally consistent.
2. Case-insensitive filter — UPPER(SUBSTR(...)) = :method on embedded, startsWith(upper(...), ?) on CH. Keeps the no-wildcard property, matches how search already behaves in the same clause builder (INSTR(LOWER(...), LOWER(:search))), and reaches existing rows. Costs the deliberate test above.
3. Normalize at ingest — ToUpper the method in getHTTPEndpoint. Canonical going forward, but doesn't reach rows already stored, and merges two currently-distinct endpoint groups.

Happy to do (2) or (3) if you want either — just didn't want to delete a day-old intentional test on my own read of it.

One unrelated find while in there: the same unfiltered-percentile defect exists in the SQLite tasks and ai_traces repositories. fetchSortedTaskDurations (task.repository.go:528) and the inline per-trace_name duration query (ai_trace.repository.go:274) both ignore rootFilter while their group queries apply it. Both DuckDB twins compute quantile_cont inline in the filtered query and are correct, so it's the same SQLite-only parity break. Not touched in #318 to keep it scoped — say the word and I'll extend it or open a follow-up.

[FrameAutomata]

Splitting this so it can close cleanly.

Part 1 — the percentile/filter split — is resolved: 95a65d24 fixed the endpoints case on main, and #319 fixes the same defect in tasks and AI traces (it turned out to be three repositories, not one) plus adds the chart-ranking test 95a65d24 did not include. #319 now carries Closes #313.

Part 2 — the method filter case-sensitivity — moved to #321. As detailed in my earlier comment, I don't think it is a regression: c7a5dcae closed a parity break by aligning the embedded backends onto ClickHouse's long-standing behaviour, with a deliberate test. What remains is a genuine product question — lowercase-stored methods are unreachable from the UI — and it needs a decision rather than a fix, so it should not hold this issue open.