3 ms·

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

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.

alganet 3y ago
The way I like to solve this is using database snapshots. Just make sure you have an incremental snapshot setup for: - 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.
- pixl97 3y ago
  I mean, if you have a functional fast acting organization, maybe. 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.
  - alganet 3y ago
    That's unfair. For the snapshot scenario, you described the bureaucratic sad path for doing it. For the soft delete, you used a happy path. 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).
bhouston 3y ago
Always. And sometimes they are a big customer who screwed up something big and if you can not restore it easily, you have to go find a DB backup and write some scripts to bring it back anyhow. 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.)
urdbjtdvbg 3y ago
Exactly. As a “backup” for when a client shoots themselves in the foot (or face!) it’s invaluable. But only if the client is worth enough to bother. If you don’t have high value clients I can see how it’s unnecessary I suppose.

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.

alganet 3y ago

The way I like to solve this is using database snapshots. Just make sure you have an incremental snapshot setup for: - 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.

pixl97 3y ago
I mean, if you have a functional fast acting organization, maybe. 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.
- alganet 3y ago
  That's unfair. For the snapshot scenario, you described the bureaucratic sad path for doing it. For the soft delete, you used a happy path. 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).

bhouston 3y ago

Always. And sometimes they are a big customer who screwed up something big and if you can not restore it easily, you have to go find a DB backup and write some scripts to bring it back anyhow. 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.)

urdbjtdvbg 3y ago

Exactly. As a “backup” for when a client shoots themselves in the foot (or face!) it’s invaluable. But only if the client is worth enough to bother. If you don’t have high value clients I can see how it’s unnecessary I suppose.