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

2026-10-14T21:30:26Z

KofTwentyTwo commented

2026-10-14 21:30:26 +00:00

(Migrated from github.com)

What

Every join and table possible-value field between the ten email_ tables now has a named foreign key (fk_<table>_<column>), added with ALTER TABLE after all tables exist, in docs/ddl/email-postgres.sql and the H2 test schema. The code-only email_suppression.source_message_id gets one too.

Child column	Parent	ON DELETE	Evidence
`email_provider_sender_address.provider_id`	provider	CASCADE	`ProviderCustomizer.preDelete` deletes them itself
`email_message_attempt.message_id`, `email_event.message_id`	message	CASCADE	Owned rows; `EmailProcesses.retain` deletes events, attempts, then the message
`email_message.provider_id`, `email_message_attempt.provider_id`, `email_event.provider_id`, `email_webhook_inbox.provider_id`	provider	RESTRICT	"Providers with message history cannot be deleted". The preDelete check now covers attempts, events and inbox rows as well as messages, so users see that message, not an FK error
`email_template_version.template_id`	template	RESTRICT	"Templates with versions cannot be deleted" (`EmailTemplateCustomizers.preDelete`)
`email_template.brand_id`	brand	RESTRICT	A NULL brand would silently re-brand a live template (`EmailBrands`)
`email_template.active_version_id`, `approved_version_id`	version	SET NULL	The other side of the cycle. The qbit inserts the template, then the version, then updates the pointer, so the key does not need DEFERRABLE
`email_message.template_id`, `template_version_id`, `brand_id`, `from_address_id`	template / version / brand / sender	SET NULL	The body is rendered at queue time; these pointers are history
`email_suppression.source_message_id`, `source_event_id`	message / event	SET NULL	A suppression must outlive message retention

Ten new indexes cover the key columns that had none.

docs/ddl/email-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. Sender addresses and events with a missing parent are removed before the references to them are checked. Running it a second time does nothing.

email_message.linked_table_name / linked_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 with the ON DELETE above. Against the old DDL it fails with all 16 missing.
The upgrade quarantines orphans and is idempotent.
The retention order and the RESTRICT and CASCADE behavior hold.

EmailHomePolishTest used to insert attempts for a message that did not exist; it now inserts the message first.

./mvnw verify: 290 tests green.

🤖 Generated with Claude Code

## What Every join and table possible-value field between the ten `email_` tables now has a named foreign key (`fk_<table>_<column>`), added with `ALTER TABLE` after all tables exist, in `docs/ddl/email-postgres.sql` and the H2 test schema. The code-only `email_suppression.source_message_id` gets one too. | Child column | Parent | ON DELETE | Evidence | |---|---|---|---| | `email_provider_sender_address.provider_id` | provider | CASCADE | `ProviderCustomizer.preDelete` deletes them itself | | `email_message_attempt.message_id`, `email_event.message_id` | message | CASCADE | Owned rows; `EmailProcesses.retain` deletes events, attempts, then the message | | `email_message.provider_id`, `email_message_attempt.provider_id`, `email_event.provider_id`, `email_webhook_inbox.provider_id` | provider | RESTRICT | "Providers with message history cannot be deleted". The preDelete check now covers attempts, events and inbox rows as well as messages, so users see that message, not an FK error | | `email_template_version.template_id` | template | RESTRICT | "Templates with versions cannot be deleted" (`EmailTemplateCustomizers.preDelete`) | | `email_template.brand_id` | brand | RESTRICT | A NULL brand would silently re-brand a live template (`EmailBrands`) | | `email_template.active_version_id`, `approved_version_id` | version | SET NULL | The other side of the cycle. The qbit inserts the template, then the version, then updates the pointer, so the key does not need DEFERRABLE | | `email_message.template_id`, `template_version_id`, `brand_id`, `from_address_id` | template / version / brand / sender | SET NULL | The body is rendered at queue time; these pointers are history | | `email_suppression.source_message_id`, `source_event_id` | message / event | SET NULL | A suppression must outlive message retention | Ten new indexes cover the key columns that had none. `docs/ddl/email-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. Sender addresses and events with a missing parent are removed before the references to them are checked. Running it a second time does nothing. `email_message.linked_table_name` / `linked_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 with the ON DELETE above. Against the old DDL it fails with all 16 missing. - The upgrade quarantines orphans and is idempotent. - The retention order and the RESTRICT and CASCADE behavior hold. `EmailHomePolishTest` used to insert attempts for a message that did not exist; it now inserts the message first. `./mvnw verify`: 290 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-email#17