Large tables use the delete table. Small tables use the deleted_at column.
Surprised you never got a request to undelete something. It’s such a common customer request in b2b saas.
Surprised you never got a request to undelete something. It’s such a common customer request in b2b saas.
Soft delete is a necessity in SaaS. And then combine it with hard delete either triggered manually (empty recycle bin) or time based (after a month, etc.)
If you don’t have high value clients I can see how it’s unnecessary I suppose.
- Last round hour
- Last round day
- Last round week
These periods can be adjusted to your/client needs. If you need something that was deleted, you restore the snapshot into a new live db and copy whatever you need. You could charge for solving "I just shot myself in the foot" support requests based on the cost of restoring those snapshots.
From my experience, this is much cleaner than having a spread of soft delete columns all over the codebase.
It seems in the vast majority of organizations I talk to, this is not how things work at all. To get a db restore on a different box you have to have at least 2 different teams involved. One to provide a machine/vm/aws instance to restore to, and another to provide the snapshot/backup.
With a soft delete all you need is the application administrator that has SQL access, which is typically far closer to the person that needs the data restored than the operations team is, hence it gets 'fixed' in a more timely fashion.
Let's compare both in either best-scenario or worst-scenario:
Snapshots on good team with no bureaucracy: Everything is automated. Even retrieval is automated via individual, commited, retrieval commands. Setup is done once and requires little maintenance, retrievals can be reused if fallen in same category (restore account, restore conversation, etc).
Snapshots on shitty team with lots of bureaucracy: Everything is manual, AWS-panel operated and only a few people have access, you have to escalate to initiate the proccess.
Soft deletes on good team with no bureaucracy: Everyones respect the soft deletion, no one queries it or do funny stuff unless for recovery purposes. The soft delete related columns, structures and tools are standardized.
Soft deletes on shitty team with lots of bureaucracy: There might be multiple, competing standards for soft delete column naming and structure. Other teams ignore the soft delete and query hidden data either knowingly (tricky hacks) or unknowingly (data team unaware of soft delete model), you can't do shit about it. When it breaks your soft deleted data, you're the one who has to fix it.
If we put external stuff in the mix (teams, people, org structures) we can spin it the way we want, but that doesn't reveal much information about the technical issue itself (having some kind of reasonable data recovery).