Skip to content
Ideony

ADR 0018: Soft Delete on User Model (FK Safety)

ADR 0018: Soft Delete on User Model (FK Safety)

Section titled “ADR 0018: Soft Delete on User Model (FK Safety)”
  • Status: Accepted
  • Date: 2026-04-14
  • Deciders: Ideony team

Context

Section titled “Context”

The User model is the root of the Ideony data graph: Bookings, Reviews, ChatMessages, ConsumerProfile, ProfessionalProfile, Credentials, SOSRequests, and PaymentIntents all reference userId via foreign keys. Hard-deleting a User row would cascade-delete or orphan all related records — a destructive, irreversible operation that conflicts with Italian consumer-data retention obligations and audit trail requirements.

Decision

Section titled “Decision”

Apply soft delete on the User model: add deletedAt DateTime? field. DELETE /users/:id sets deletedAt = now() rather than issuing a SQL DELETE. All user-facing queries filter WHERE deletedAt IS NULL. The Prisma middleware (or query extension) appends this filter globally so services cannot accidentally return deleted users.

Consequences

Section titled “Consequences”
  • FK integrity preserved: Bookings, Reviews, ChatMessages remain intact after user “deletion” — no orphaned records, no cascade surprises.
  • Audit trail: deleted user data retained for legal/compliance hold period; deletedAt timestamp is the deletion record.
  • Reversible: account reinstatement (e.g., accidental deletion, GDPR erasure request review) is a single field update.
  • Consistent pattern: same soft-delete approach can be applied to ProfessionalProfile and other root entities.
  • Queries must always filter deletedAt IS NULL; missing this filter in a new query is a latent data-leak bug. Prisma middleware mitigates but requires discipline.
  • DB storage grows over time (deleted users never physically removed); periodic hard-purge job needed post-MVP for GDPR “right to erasure” compliance.
  • UNIQUE constraints on email / clerkId must account for soft-deleted rows to allow re-registration with same email; requires partial index or application-layer check.

Alternatives considered

Section titled “Alternatives considered”
  • Hard delete with cascade — rejected: destroys booking history, review data, payment audit trail; irreversible; violates data retention obligations.
  • Hard delete + archiving to separate table — rejected: doubles migration complexity; join-based archive queries awkward; soft delete achieves same goal more simply.
  • No delete (disable only) — considered: isActive flag achieves similar goal but doesn’t capture deletion timestamp; soft delete with deletedAt is strictly more informative.