Every table in this schema soft-deletes with a deleted_at column. A year in, what has that cost you?

Several different meanings hide behind one column

deleted_at is usually introduced to answer one question — "the user pressed Delete, but we might want it back" — and then inherits three or four unrelated jobs. It comes to mean hidden from the interface, no longer billable, retained for audit, superseded by a newer record, and legally erased, all through the same nullable timestamp. Those are different states with different rules about who may read the row, whether it still participates in totals, and how long it survives, and one column cannot express them.

So the first move in this conversation is to ask what deletion means here. If the answer is "an admin can undo it within thirty days", you are modelling a recoverable state with a retention window. If the answer is "finance still needs it for seven years", you are modelling an archive. If it is "the customer asked us to remove their data", soft deletion is not deletion at all, because the data is still in the table, still in every index, and still in last night's backup. Choosing the mechanism before naming the requirement is what produces the schema that costs you a year later.

Uniqueness is the first thing to break

The cost that surfaces earliest is that natural keys stop being enforceable. A user deletes their account and signs up again with the same address; the unique index on email rejects the new row because the old one is still physically present. The usual reaction is to drop the constraint, which trades a clear error today for duplicate live rows forever.

In PostgreSQL the honest fix is a partial unique index, so the constraint applies to live rows only.

-- Uniqueness scoped to the rows that are still live. Two deleted rows
-- may share an address; two live rows may not.
CREATE UNIQUE INDEX users_email_live
  ON users (email)
  WHERE deleted_at IS NULL;

MySQL has no filtered index of this kind, so the same intent has to be encoded in the key itself. The workable shape is a non-null discriminator: give live rows a fixed sentinel value and deleted rows their deletion timestamp, then put uniqueness on the pair. Live rows collide with each other because they share the sentinel, deleted rows do not because their timestamps differ. It works, and the cost is that a sentinel value now leaks into every query and every report that touches the column, which is exactly the kind of detail nobody writes down.

Note what the partial index also tells you about the rest of the schema. If uniqueness has to be scoped to live rows, so does almost every other rule — one active subscription per customer, one primary address per account, one open cart per session. Each of those is a constraint that has quietly become conditional.

Every query is now a query plus a predicate

The second cost is distributed across the codebase. Once rows can be logically absent, every read is wrong by default, and the failure mode is not an error but a slightly overstated number. A dashboard counts deleted orders, a mailing goes to a closed account, a per-tenant limit is computed against rows nobody can see. Nothing throws, so nothing is caught until somebody notices the totals disagree.

Discipline is not a mechanism, and "we all remember to add the filter" fails on the first join written by somebody new. What works is making the default safe. A view that already carries the predicate, with the base table reserved for the few places that genuinely need the dead rows, moves the decision from every author to one definition. PostgreSQL row-level security can express the same thing as a policy the database enforces regardless of what the application sends. An ORM's default scope helps for simple reads, but be clear about its limits: it typically applies to the entity being loaded and not to tables you reach through a hand-written join, which is precisely where the omission hurts.

Foreign keys stop meaning what they say

The referential integrity you thought you had is now approximate. A foreign key checks that the referenced row exists physically, and a soft-deleted row does. Nothing stops a new order referencing a deleted customer, and no cascade fires when the parent is soft-deleted, so children stay live under a parent that the application considers gone.

That leaves a decision the database can no longer make for you: does deletion propagate? If deleting a customer should close their subscriptions, that is now application logic, and it must be transactional and it must be idempotent, because it will be re-run. If it should not propagate, say so explicitly, because the alternative is discovering the rule by finding orphans in a report.

Modelling the lifecycle instead

The general improvement is to stop treating the end of a row's life as an absence and to model it as a state the domain already has words for. An account_status column with a small closed set of values — active, suspended, closed — plus a status_changed_at says what happened and when, supports transitions that are checked rather than implied, and does not pretend that "closed" and "erased" are the same event. Where the history itself is the requirement, an append-only table of state changes gives you the audit trail people were really asking for when they suggested a flag.

Where the requirement is retention rather than recoverability, moving the row is often better than marking it. A delete that writes the old row into an archive table inside the same transaction keeps the live table small, keeps its constraints unconditional, keeps its indexes free of rows nobody queries, and leaves the retained copy somewhere access can be granted separately. The cost is that undelete becomes an explicit operation rather than an UPDATE, which is usually a fair price and sometimes a benefit.

And be precise about erasure. If an obligation exists to remove personal data, a deleted_at timestamp does not satisfy it, because the values remain in the heap, the indexes and the backups. The designs that survive that requirement separate the identifying columns from the transactional ones, so the facts a business must keep — an invoice total, an order date — can outlive the personal detail that has to be overwritten.

Decide what deletion means before you decide how to represent it. A nullable timestamp added for convenience converts every constraint into a conditional one and every query into a query somebody has to remember to filter.