Move person-delete FK detach to database-level ON DELETE #684

New Issue

marcel · 2026-05-27T19:58:53+02:00

marcel commented

2026-05-27 19:58:53 +02:00

Context

Follow-up flagged in the review of PR #679 (Phase 5 persons directory, #667) — Markus (architect).

The new DELETE /api/persons/{id} works by detaching sender/receiver references in application code (@Transactional) before deleting the person, to avoid an FK-orphan 500. That's correct for the service path, but the integrity guarantee lives in Java, not the database — a delete via any other path (a future endpoint, a manual psql, a batch job) could still orphan or 500.

Suggested approach

Add a Flyway migration moving the integrity to the DB layer:
- documents.sender_id FK → ON DELETE SET NULL (a deleted sender just clears the reference), and/or
- document_receivers.person_id FK → ON DELETE CASCADE (drop the join rows).
Once the DB enforces it, the application-layer detach in PersonService.deletePerson can be simplified/removed.
Keep the existing delete integration test green; add one asserting the DB-level behavior.

Out of scope / notes

Push integrity to the lowest layer (architecture principle) — the DB should make orphaning impossible regardless of the calling path.
Not done in #679 (presentation-focused, no migration). Small follow-up migration.

## Context Follow-up flagged in the review of PR #679 (Phase 5 persons directory, #667) — Markus (architect). The new `DELETE /api/persons/{id}` works by **detaching sender/receiver references in application code** (`@Transactional`) before deleting the person, to avoid an FK-orphan 500. That's correct for the service path, but the integrity guarantee lives in Java, not the database — a delete via any other path (a future endpoint, a manual `psql`, a batch job) could still orphan or 500. ## Suggested approach - Add a Flyway migration moving the integrity to the DB layer: - `documents.sender_id` FK → `ON DELETE SET NULL` (a deleted sender just clears the reference), and/or - `document_receivers.person_id` FK → `ON DELETE CASCADE` (drop the join rows). - Once the DB enforces it, the application-layer detach in `PersonService.deletePerson` can be simplified/removed. - Keep the existing delete integration test green; add one asserting the DB-level behavior. ## Out of scope / notes - Push integrity to the lowest layer (architecture principle) — the DB should make orphaning impossible regardless of the calling path. - Not done in #679 (presentation-focused, no migration). Small follow-up migration.

marcel added the P3-later refactor labels 2026-05-27 20:12:40 +02:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: marcel/familienarchiv#684