Soft Deletion Changes Every Uniqueness and Reference Rule

Soft deletion is a lifecycle state, not a cheaper DELETE. Once a row remains in the primary table, every uniqueness rule, foreign key, query, aggregate, cache, restore flow, and retention job must define whether deleted rows participate. Use it only when recovery or audit needs justify that permanent complexity.

Make lifecycle state explicit

A nullable deleted_at distinguishes active and deleted rows, but many domains need more states: active, suspended, archived, deletion_requested, retained, and erased. Model allowed transitions and who can perform them. Record deletion actor and reason in an audit system rather than overloading one timestamp with every meaning.

Default ORM scopes are convenient and dangerous. A missing WHERE deleted_at IS NULL can expose records; an automatic scope can hide rows from uniqueness checks, support tools, or background jobs that must process them. Put active-record access behind named repository methods and test generated SQL. Metrics and billing aggregates need explicit state rules too.

Uniqueness is the first trap. If usernames may be reused after deletion, a normal unique constraint blocks the new account. PostgreSQL documents that a partial unique index can enforce uniqueness only for rows matching a predicate. An index on normalized email where deleted_at IS NULL permits reuse while keeping active rows unique. The query predicate must match the index predicate closely enough for the planner to use it.

Identifier reuse changes restoration. If a deleted account’s email has been claimed, restoration cannot simply clear deleted_at. Decide whether restoration fails, chooses a new identifier, or requires conflict resolution. Security-sensitive identifiers may need a reuse delay to prevent old links, audit entries, or external integrations from being associated with a new owner.

Preserve referential integrity deliberately

Foreign keys still reference soft-deleted parents because the row exists. A child can remain active while its parent is invisible, creating an orphan in application semantics. PostgreSQL’s constraint documentation explains that foreign keys maintain references to existing rows, not to rows matching an application lifecycle predicate. Enforce “active child requires active parent” in the service transaction or redesign the lifecycle; a normal foreign key cannot express that condition.

Choose cascade semantics per relationship. Deleting a project may archive its tasks, detach reusable templates, and retain immutable invoices. A single generic callback cannot encode these differences safely. Run transitions in a transaction when the affected set is bounded. For large trees, create a durable deletion operation with checkpoints, idempotency, and a visible intermediate state that denies new writes.

Separate recovery from erasure

Soft deletion does not satisfy a requirement to erase personal data. Define a retention deadline and a purge job that performs physical deletion or irreversible anonymization across primary rows, replicas, search indexes, object storage, caches, analytics, and downstream processors according to policy. Preserve only the minimum audit evidence allowed. Backups need documented expiry and restore procedures that do not resurrect purged records into active service.

If indefinite audit history is the goal, an archive table or append-only domain event may be clearer than teaching every live query about deletion. If records never return and references should cascade normally, physical deletion can be simpler. If state transitions affect business rules, use an explicit lifecycle column rather than pretending all inactive states are deletion.

Test disappearance and return

Test direct reads, lists, search, counts, unique reuse, parent deletion, child creation, restore after conflict, concurrent delete and update, repeated deletion, cache invalidation, and purge. Query the database independently to verify constraints and indexes, then test the application projections.

The next step is to take one soft-deleted table and list every unique constraint and inbound foreign key, assigning reuse, restore, and purge behavior to each. Review by 2026-12-05 or when retention requirements change.