Historical top clients/domains report distinct item counts as query totals

[veteranbv]

Versions

• Pi-hole: v6.4.3
• Web: v6.6
• FTL: v6.7

Platform

• OS and version: Debian GNU/Linux 12
• Platform: Proxmox LXC, bare-metal Pi-hole installation

Expected behavior

For these historical statistics endpoints:

• GET /api/stats/database/top_clients
• GET /api/stats/database/top_domains

the OpenAPI schema defines total_queries as the total number of queries and
blocked_queries as the number of blocked queries. For the same from and
until interval, these values should therefore match sum_queries and
sum_blocked from GET /api/stats/database/summary.

This consistency matters to API consumers that calculate blocking rates or
combine ranked clients/domains with the interval totals.

Actual behavior / bug

The top-item rows and their individual count values are correct, but the two
aggregate fields contain distinct entity counts instead of query counts.

Observed over the same 24-hour interval on FTL v6.7:

// /api/stats/database/summary
{
  "sum_queries": 374813,
  "sum_blocked": 86248,
  "percent_blocked": 23.0109,
  "total_clients": 5
}

// /api/stats/database/top_clients?blocked=true&count=5
{
  "total_queries": 5,
  "blocked_queries": 5
}

// /api/stats/database/top_domains?blocked=true&count=5
{
  "total_queries": 3865,
  "blocked_queries": 481
}

The client result means five distinct clients queried during the interval and
all five had at least one blocked query. The domain result similarly represents
distinct total and blocked domains. These are valid statistics, but they do not
match the documented field names or descriptions.

This produces valid-looking JSON that can silently report a 100% blocking rate
(5 / 5) when the actual rate is about 23%. DNS resolution and ranking are not
affected; the impact is incorrect reporting, dashboards, alerts, and downstream
automation.

The source confirms the mismatch in src/api/stats_database.c:

SELECT COUNT(DISTINCT domain) FROM query_storage ...
SELECT COUNT(DISTINCT client) FROM query_storage ...

Those results are serialized as total_queries and blocked_queries. The
equivalent in-memory endpoints use actual query counters, and the OpenAPI schema
also describes query totals.

Steps to reproduce

Use one identical interval for all three requests:

FROM=$(date -d '24 hours ago' +%s)
UNTIL=$(date +%s)

curl -sS -H "X-FTL-SID: $SID" \
  "http://pi.hole/api/stats/database/summary?from=$FROM&until=$UNTIL"

curl -sS -H "X-FTL-SID: $SID" \
  "http://pi.hole/api/stats/database/top_clients?from=$FROM&until=$UNTIL&blocked=true&count=5"

curl -sS -H "X-FTL-SID: $SID" \
  "http://pi.hole/api/stats/database/top_domains?from=$FROM&until=$UNTIL&blocked=true&count=5"

Compare:

• summary.sum_queries with each top endpoint's total_queries
• summary.sum_blocked with each top endpoint's blocked_queries

Proposed fix

Use COUNT(*) for the four aggregate queries while retaining the existing time
and blocked-status filters. Add integration assertions that both historical top
endpoints match the database summary for the same interval.

This preserves ranking, result limits, and row counts while restoring the
documented response contract.

Debug Token

• URL: Not generated. The issue is isolated to deterministic read-only API
  responses and is reproducible directly from the published SQL and API schema.

Additional context

The v6.7 work that corrected historical top-item ordering and the earlier SQL
syntax failure did not change these aggregate queries. The current
development branch still uses the same distinct-entity counts.

I have a focused patch and regression coverage ready and will link the pull
request to this issue.