Easy, alternative soft deletion: `deleted_record

Re: Easy, alternative soft deletion: `deleted_record_insert`

#41

Post by doctor_eval » Sun, Jan 01, 2023, 9:45 AM UTC

hdra wrote: post #27

rubyfan wrote: post #8

I did something similar to this many years ago but didn’t limit to deleted records and instead created an audit log with a very similar approach. It worked nicely and provided a view into how data was changing by our users.

I considered this for an audit log as well, but ran into a roadblock in terms of associating the operation with a logged in user who triggered the change.. how did solve that particular issue? or was it not a requirement in your case?

I have experimented with mapping application users 1:1 to individual database users, and using SET ROLE at the start of every transaction.

I got it working in a PoC, and with the right configuration of roles, this pattern would give you user identity and audit right down to the database itself.

Sadly I haven’t pulled it off in production environment yet, but if I ever get the chance to work at this level in an enterprise app again, I’ll definitely do it.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#42

Post by xupybd » Sun, Jan 01, 2023, 9:47 AM UTC

blondin wrote: post #2

oh nice. how do you recover deleted records?

I think the author is suggesting that you often can't do it with soft deletes anyway.

https://brandur.org/soft-deletion#undelete

Re: Easy, alternative soft deletion: `deleted_record_insert`

#43

Post by eitland » Sun, Jan 01, 2023, 10:00 AM UTC

piyh wrote: post #21

JSONB seems like something I need to put a few nights into understanding, but my time is short and filled with diapers and bottles.

Good to see you have your priorities sorted.

Sincerely, a dad with 5 kids.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#44

Post by bennyp101 » Sun, Jan 01, 2023, 10:20 AM UTC

duskwuff wrote: post #11

There's at least one factor which I don't see consideration for here. Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation. So, if you're going to have a bunch of "deleted" records hanging…

Something I’ve been meaning to play with is setting a “PII” comment on a table/field, then scanning over the schema if I need to find places to check for encryption etc

Perhaps something here that could mark it with a uuid so if it needed to be fully deleted, it could be found easily and removed or overwritten

Re: Easy, alternative soft deletion: `deleted_record_insert`

#45

Post by andretti1977 » Sun, Jan 01, 2023, 10:28 AM UTC

username1407 wrote: post #39

duskwuff wrote: post #11

There's at least one factor which I don't see consideration for here. Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation. So, if you're going to have a bunch of "deleted" records hanging…

Can't you use json operators [1] for this? -- Permanently delete message sent by jon DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon'; [1] https://www.postgresql.org/docs/15/functions-json.html

The deletion table is a schemaless table, it may become hard to anonimize the data

Re: Easy, alternative soft deletion: `deleted_record_insert`

#46

Post by ibbtown » Sun, Jan 01, 2023, 10:54 AM UTC

Sounds like a DSGVO/GDPR nightmare. And I really don't want to explain why the deleted data are part of some sold stolen data blob. There might be some technical use cases, but in general it has many culprit

Re: Easy, alternative soft deletion: `deleted_record_insert`

#47

Post by username1407 » Sun, Jan 01, 2023, 10:59 AM UTC

duskwuff wrote: post #40

username1407 wrote: post #39

Earlier quoted context omitted.

Can't you use json operators [1] for this? -- Permanently delete message sent by jon DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon'; [1] https://www.postgresql.org/docs/15/functions-json.html

That assumes that all of a user's data is a single foreign key relationship away from the user, which often isn't the case. It also requires you to scan the entire table and decode JSON for every row you've ever deleted, which may become prohibitively expensive for large databases.

That's true.

To give an example for your 1st point: If you have the schema users->messages->message_attachments and message_attachments does not store the user_id, you'll have trouble finding all attachments from a specific user.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#48

Post by simonw » Sun, Jan 01, 2023, 11:01 AM UTC

jl6 wrote: post #34

This is another solution to such a common problem that one might be surprised at there being no solution baked into the SQL standard. Instead, we have some vendor specific features like automatic audit tables and time travel, and a huge array of bespoked techniques like in the article: everything from adding a deleted_at column through to re-architecting your system around event-sourcing. Why such diversity of soluti…

The SQL:2011 standard does describe a mechanism for solving this called System Versioned Tables, but the only database I've encountered that implements it so far is MariaDB: https://mariadb.com/kb/en/system-versioned-tables/

https://en.wikipedia.org/wiki/Temporal_database lists a few more - apparently there are versions of this in Oracle, DB2 and SQL Server now.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#49

Post by ocimbote » Sun, Jan 01, 2023, 11:05 AM UTC

ibbtown wrote: post #46

Sounds like a DSGVO/GDPR nightmare. And I really don't want to explain why the deleted data are part of some sold stolen data blob. There might be some technical use cases, but in general it has many culprit

If this ils well documented, I don't see how this would be a problem for GDPR. Can you elaborate?

Re: Easy, alternative soft deletion: `deleted_record_insert`

#50

Post by simonw » Sun, Jan 01, 2023, 11:06 AM UTC

ibbtown wrote: post #46

Sounds like a DSGVO/GDPR nightmare. And I really don't want to explain why the deleted data are part of some sold stolen data blob. There might be some technical use cases, but in general it has many culprit

Compliance there feels to me like it would be quite straightforward to implement: if there is a legal requirement for the deleted data to be fully disposed of there's a very obvious single place to delete it from.