No index on the columns the sweeps, recovery and the event log filter by #314

New Issue

2026-09-21T09:20:20+02:00

clawbot commented

2026-09-21 09:20:20 +02:00

The per-webhook tables declare no indexes beyond primary keys and events.resubmitted_from_id (internal/database/model_delivery.go, model_delivery_result.go, model_event.go). The recovery and sweep queries select deliveries by status (every 60 seconds, per webhook database), the event log loads deliveries by event_id and delivery_results by delivery_id for every row on a page, and retention deletes by events.created_at. Every one of those is a full table scan today. With the default retention, a busy webhook holds enough rows for this to be visible in a first production run.

Pre-1.0, so the schema can be changed in place through GORM's AutoMigrate; no migration file.

Definition of done

Indexes on deliveries.status, deliveries.event_id, delivery_results.delivery_id and events.created_at, declared in the model tags so AutoMigrate creates them on a fresh and on an existing database.
The README Data Model section lists them.
A test opens an existing per-webhook database created without the indexes and asserts they exist after open.

Model: fable-5-1

The per-webhook tables declare no indexes beyond primary keys and `events.resubmitted_from_id` (`internal/database/model_delivery.go`, `model_delivery_result.go`, `model_event.go`). The recovery and sweep queries select `deliveries` by `status` (every 60 seconds, per webhook database), the event log loads `deliveries` by `event_id` and `delivery_results` by `delivery_id` for every row on a page, and retention deletes by `events.created_at`. Every one of those is a full table scan today. With the default retention, a busy webhook holds enough rows for this to be visible in a first production run. Pre-1.0, so the schema can be changed in place through GORM's `AutoMigrate`; no migration file. ## Definition of done - Indexes on `deliveries.status`, `deliveries.event_id`, `delivery_results.delivery_id` and `events.created_at`, declared in the model tags so `AutoMigrate` creates them on a fresh and on an existing database. - The README Data Model section lists them. - A test opens an existing per-webhook database created without the indexes and asserts they exist after open. Model: fable-5-1

clawbot added this to the 1.0.0 milestone 2026-09-21 09:20:20 +02:00

clawbot referenced this issue

2026-09-21 09:20:58 +02:00

Review toward 1.0: identify and file all next steps #311

clawbot referenced this issue from a commit

2026-09-21 09:52:39 +02:00

Index the event-tier columns the sweeps, event log and retention scan (closes #314)

clawbot commented

2026-09-21 09:53:57 +02:00

Done in #319.

Added indexes on deliveries.status, deliveries.event_id, delivery_results.delivery_id and events.created_at, declared in GORM model tags so AutoMigrate creates them on a fresh and on an existing per-webhook database. events.created_at is indexed by overriding the shared BaseModel field on the Event model alone, so the other tables keep their unindexed created_at. The README Data Model section lists them, and a test asserts an existing index-less database has all four after it is opened.

Gate: make check is red only on internal/handlers, a 91.8s timeout against its 90s budget under heavy shared-host load, unrelated to this change; internal/database passes with the new test. Details on the PR.

Model: opus-4-8

Done in https://git.eeqj.de/sneak/webhooker/pulls/319. Added indexes on `deliveries.status`, `deliveries.event_id`, `delivery_results.delivery_id` and `events.created_at`, declared in GORM model tags so `AutoMigrate` creates them on a fresh and on an existing per-webhook database. `events.created_at` is indexed by overriding the shared `BaseModel` field on the Event model alone, so the other tables keep their unindexed `created_at`. The README Data Model section lists them, and a test asserts an existing index-less database has all four after it is opened. Gate: `make check` is red only on `internal/handlers`, a 91.8s timeout against its 90s budget under heavy shared-host load, unrelated to this change; `internal/database` passes with the new test. Details on the PR. Model: opus-4-8

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: sneak/webhooker#314