Foreign keys between the webhooks tables, with an orphan-safe upgrade #17

2026-10-14T21:15:39Z

KofTwentyTwo commented

2026-10-14 21:15:39 +00:00

(Migrated from github.com)

What

Every join and table possible-value field between the five webhooks_ tables now has a named foreign key (fk_<table>_<column>) in docs/ddl/webhooks-postgres.sql and the H2 test schema.

Child column	Parent	ON DELETE	Why
`webhooks_endpoint_secret.endpoint_id`	endpoint	CASCADE (was NO ACTION)	Secrets are the endpoint's own configuration; with NO ACTION an admin could never delete an endpoint that had a secret.
`webhooks_subscription.endpoint_id`	endpoint	CASCADE (was NO ACTION)	Same: subscriptions are the endpoint's configuration.
`webhooks_delivery.endpoint_id`	endpoint	RESTRICT	Delivery history keeps its endpoint. Cancel and run Delete Deliveries first.
`webhooks_delivery_attempt.endpoint_id`	endpoint	RESTRICT	Same.
`webhooks_delivery.subscription_id`	subscription	SET NULL	A delivery outlives the subscription that queued it.
`webhooks_delivery_attempt.delivery_id`	delivery	CASCADE	Attempts belong to their delivery (`Retention.deleteDeliveries` deletes attempts first anyway).

New index webhooks_delivery_subscription_ix; every other FK column already led an index.

docs/ddl/webhooks-foreign-keys-upgrade.sql upgrades an existing database. It records orphan rows in qqq_fk_quarantine first: it NULLs nullable references and deletes rows whose required parent is missing, keeping each one as JSON in row_data. Then it replaces any FK on the column that has a different ON DELETE and adds the named one. Running it a second time does nothing.

webhooks_delivery.source_table / source_record_id is polymorphic, so it cannot have an FK.

Tests

ForeignKeyGuardrailTest (Testcontainers, PostgreSQL 17; fails in CI without Docker, skips locally) covers three things:

Every relation declared in metadata must have an FK or an entry in the exemption list, and the ON DELETE rules must match the table above. Against the old DDL it fails with the four missing keys.
The upgrade script quarantines and fixes orphans, and is idempotent.
Endpoint delete cascades to secrets and subscriptions, but is refused while the endpoint has deliveries.

./mvnw verify: 153 tests green.

🤖 Generated with Claude Code

## What Every join and table possible-value field between the five `webhooks_` tables now has a named foreign key (`fk_<table>_<column>`) in `docs/ddl/webhooks-postgres.sql` and the H2 test schema. | Child column | Parent | ON DELETE | Why | |---|---|---|---| | `webhooks_endpoint_secret.endpoint_id` | endpoint | CASCADE (was NO ACTION) | Secrets are the endpoint's own configuration; with NO ACTION an admin could never delete an endpoint that had a secret. | | `webhooks_subscription.endpoint_id` | endpoint | CASCADE (was NO ACTION) | Same: subscriptions are the endpoint's configuration. | | `webhooks_delivery.endpoint_id` | endpoint | RESTRICT | Delivery history keeps its endpoint. Cancel and run Delete Deliveries first. | | `webhooks_delivery_attempt.endpoint_id` | endpoint | RESTRICT | Same. | | `webhooks_delivery.subscription_id` | subscription | SET NULL | A delivery outlives the subscription that queued it. | | `webhooks_delivery_attempt.delivery_id` | delivery | CASCADE | Attempts belong to their delivery (`Retention.deleteDeliveries` deletes attempts first anyway). | New index `webhooks_delivery_subscription_ix`; every other FK column already led an index. `docs/ddl/webhooks-foreign-keys-upgrade.sql` upgrades an existing database. It records orphan rows in `qqq_fk_quarantine` first: it NULLs nullable references and deletes rows whose required parent is missing, keeping each one as JSON in `row_data`. Then it replaces any FK on the column that has a different ON DELETE and adds the named one. Running it a second time does nothing. `webhooks_delivery.source_table` / `source_record_id` is polymorphic, so it cannot have an FK. ## Tests `ForeignKeyGuardrailTest` (Testcontainers, PostgreSQL 17; fails in CI without Docker, skips locally) covers three things: - Every relation declared in metadata must have an FK or an entry in the exemption list, and the ON DELETE rules must match the table above. Against the old DDL it fails with the four missing keys. - The upgrade script quarantines and fixes orphans, and is idempotent. - Endpoint delete cascades to secrets and subscriptions, but is refused while the endpoint has deliveries. `./mvnw verify`: 153 tests green. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: QRun-IO/qbit-webhooks#17