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.
Update (2026-09-16): commit e05b362 (PR #217, "apply event_name and date-range filters on the deliveries dashboard page") extended the identical unindexed pattern to a second call site: DashboardController's deliveries listing now also applies whereDate('created_at', ...) date-range filtering with no supporting index, mirroring Api\DeliveryController. The problem this issue describes is no longer confined to the API controller — it now needs to be fixed in both places (or centralized) to avoid two unindexed full-table-scan code paths. No index on created_at has been added anywhere, and no commit has replaced whereDate() with a sargable range comparison in either controller.
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, and as of 2026-09-16 that now applies to two listing call sites instead of one.
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()) in both Api\DeliveryController and DashboardController 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.Update (2026-09-16): commit
e05b362(PR #217, "apply event_name and date-range filters on the deliveries dashboard page") extended the identical unindexed pattern to a second call site:DashboardController's deliveries listing now also applieswhereDate('created_at', ...)date-range filtering with no supporting index, mirroringApi\DeliveryController. The problem this issue describes is no longer confined to the API controller — it now needs to be fixed in both places (or centralized) to avoid two unindexed full-table-scan code paths. No index oncreated_athas been added anywhere, and no commit has replacedwhereDate()with a sargable range comparison in either controller.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, and as of 2026-09-16 that now applies to two listing call sites instead of one.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()) in bothApi\DeliveryControllerandDashboardControllerso the index is actually usable.