What: GET /api/v1/deliveries always sorts by created_at DESC for pagination and optionally filters by date range, but created_at isn't indexed, and the filter implementation additionally prevents index use even if one were added naively.
Where:
database/migrations/2025_08_25_100544_create_deliveries_table.php:26-28 — only (event_id,status), (endpoint_id,status), and (status,next_retry_at) are indexed; no index covers created_at.app/Http/Controllers/Api/DeliveryController.php:39-48:
if ($request->has('from_date')) {
$query->whereDate('created_at', '>=', $request->input('from_date'));
}
if ($request->has('to_date')) {
$query->whereDate('created_at', '<=', $request->input('to_date'));
}
$deliveries = $query->orderBy('created_at', 'desc')->paginate(20);Update (2026-09-05): commit 6777a57 (#213) touched this same controller method, but only to add request validation for status/from_date/to_date before the query runs (fixing #57 — a malformed value like from_date=banana no longer 500s). It did not change the query itself: whereDate('created_at', ...) is still used, and no migration adding a created_at index has merged. The performance/index concern described below is still fully open.
Why it matters: Per CLAUDE.md's architecture, deliveries is the highest-volume table in the system — one row per endpoint per triggered event, growing continuously. Every listing call forces a sort (and optionally a range filter) over the full table with no supporting index. Even with a plain index on created_at, whereDate('created_at', ...) wraps the column in a function (DATE(created_at)), which prevents Postgres from using a plain B-tree index for the range filter — only a functional index on DATE(created_at), or rewriting the comparison, would help. As delivery volume grows this becomes a full table scan on every dashboard/API listing call.
Suggested fix: Add an index on created_at (or a composite (status, created_at) matching common filter combos), and replace whereDate() with direct timestamp range comparisons (>= Carbon::parse($from)->startOfDay(), < Carbon::parse($to)->endOfDay()) so the index is actually usable.
What:
GET /api/v1/deliveriesalways sorts bycreated_at DESCfor pagination and optionally filters by date range, butcreated_atisn't indexed, and the filter implementation additionally prevents index use even if one were added naively.Where:
database/migrations/2025_08_25_100544_create_deliveries_table.php:26-28— only(event_id,status),(endpoint_id,status), and(status,next_retry_at)are indexed; no index coverscreated_at.app/Http/Controllers/Api/DeliveryController.php:39-48:Update (2026-09-05): commit
6777a57(#213) touched this same controller method, but only to add request validation forstatus/from_date/to_datebefore the query runs (fixing #57 — a malformed value likefrom_date=bananano longer 500s). It did not change the query itself:whereDate('created_at', ...)is still used, and no migration adding acreated_atindex has merged. The performance/index concern described below is still fully open.Why it matters: Per CLAUDE.md's architecture,
deliveriesis the highest-volume table in the system — one row per endpoint per triggered event, growing continuously. Every listing call forces a sort (and optionally a range filter) over the full table with no supporting index. Even with a plain index oncreated_at,whereDate('created_at', ...)wraps the column in a function (DATE(created_at)), which prevents Postgres from using a plain B-tree index for the range filter — only a functional index onDATE(created_at), or rewriting the comparison, would help. As delivery volume grows this becomes a full table scan on every dashboard/API listing call.Suggested fix: Add an index on
created_at(or a composite(status, created_at)matching common filter combos), and replacewhereDate()with direct timestamp range comparisons (>= Carbon::parse($from)->startOfDay(),< Carbon::parse($to)->endOfDay()) so the index is actually usable.