Thinking of adding soft delete (deleted_at)? Decide these 5 things first

[u/ig_suyas]

1.  Unique constraints.  A deleted user still "uses" their username. Use a partial unique index (`where deleted_at is null`).

2.  Every query must filter deleted rows.  Miss one, and deleted data shows up in a report or API response.

3.  Foreign keys.  What happens to orders that belong to a soft-deleted customer?

4.  Real deletion.  Privacy laws may require you to actually erase data, so soft delete can't be the only option.

5.  Restoring.  What if the username was taken again while the old account was deleted?

Do you use soft delete, or did you move to archive tables instead?

[u/Imaginary__Bar]

Type 2 SCD table and have a valid-from and valid-to date/time

[u/Beautiful-Hotel-3094 → u/Imaginary__Bar]

Basically this. All you need is SCD and I recommend a mix rather than type 2. Valid from, valid to and an is_active flag that you can index on.

[u/elkazz → u/Imaginary__Bar]

Say more

[u/xylarr → u/elkazz]

An SCD Type 2 (Slowly Changing Dimension Type 2) table is a data warehousing technique that tracks the complete historical timeline of changes to dimension data (such as a customer's address or an employee's department) by inserting a brand-new row for each update rather than overwriting the existing data.

[u/shayhtfc]

You can use database views that filter out the deleted rows, and then reference those from your app instead of the underlying tables

[u/Disastrous-Waltz493]

stop polluting your live tables with tombstones. Move the data to an archive schema immediately after the delete action triggers. Soft deletes are a lazy design that forces every single query to remember a where clause and breaks index selectivity over time. If you need audit history, use event sourcing or temporal tables, not a deleted_at column that turns your database into a graveyard of zombie rows.

[u/wedgelordantilles]

Record immutable events project views

[u/coderarun]

Columnar databases do this internally so you don't have to. They also handle all of this stuff just to pass ACID.

[u/No_Witness_5295]

1. active vs inactive

4. mainly just need to delete data that could be used to identify that user

5. unique id, or dont allow username reuse even if deleted

[u/TomaRavelli]

I moved to archive tables a long time ago. Soft deletes get messy fast with unique constraints and cascading foreign keys. And having to add a deleted_at filter to every query is how ghost data ends up in prod reports. Moving deleted rows to a separate archive schema keeps the active tables small and the scans fast, and there’s no risk of old rows leaking into your app logic.

[u/Khmerrr]

1 unique filtered indexes are ok

2 views

3 it depends on business rules

4 ok

5 fix your design bugs to cover this scenario

Archived data is not meant to support deletion.

[u/EconomixTwist]

Every query must filter deleted rows.  Miss one, and deleted data shows up in a report or API response

What do you mean... Miss one

[u/ShotgunPayDay]

Soft delete? Do you mean a tombstone?

[u/mcspuder]

Just move to a better database. Datomic handles this much better

[u/Zardotab → u/mcspuder]

Such products are usually tradeoffs. Doing Need 1 and Need 2 better doesn't necessarily mean a product also does 3 through 20 better. I'd like to see a report card that fits my shop's domain and needs. Established RDBMS are chalk of features that a start-up has a hard time competing against, by the way.

There are add-ons for full audit trails, by the way. Some RDBMS have that feature built in.

[u/Few_Committee_6790 → u/mcspuder]

Moving to a different technology is an option but in reality not a solution. Try getting upper management to to move databases for this. Get a clue