4 ms·

I think this is the part they miss. I've never undeleted a user either, but there have been many times I've gone back to look at something. Yeah. As

I think this is the part they miss. I've never undeleted a user either, but there have been many times I've gone back to look at something. Yeah. As far as a user-facing "Undelete" button existing or being used... that's very rare in my experience. What's much more common is a user accidentally deletes some data. They deny they made an error. The developers are blamed. You then have to go on a wild goose chase figuring out if it was possible for the app to actually screw up in that way. There's usually no definitive answer, and even if there is, management can't understand it. And regardless of how any of that plays out, you still probably have to try and recover the data from backups or something. Alternately, maybe it was the app's fault. Still plays out nearly the same! Soft deletes and/or audit trails save you from all of that. Though you really shouldn't be relying on a database for an audit trail. It might help find some issues, but things actually used for security shouldn't be writable so easily. I mean, at some level you need to trust the database right? Been ages since I did it, but it's usually possible to set up a "secure" audit trail with use of database permissions. For example, the application's DB credentials can have SELECT and INSERT permissions on the audit trail table, but no UPDATE or DELETE perms. How would you set up a secure audit trail that didn't rely on the application and/or database at some level? Even if it lives outside of the database, that data came from the database. Not a rhetorical question. Genuinely curious!

gmiller123456 4y ago
How would you set up a secure audit trail that didn't rely on the application and/or database at some level? A database is fine for an audit trail. But the application shouldn't have permissions to cover up a change. Likewise, the audit trail should record that a record was created, deleted, maybe undeleted, etc and when, by who. Relying only on the "deleted" flag only shows you the current state, not what and when was done and who did it.
- sverhagen 4y ago
  Any tools you recommend here? Or just roll your own with triggers in the database?
  - gunnarmorling 4y ago
    Log-based change data capture is a great tool for building audit logs; unlike triggers, it doesn't impact write performance, you don't need to maintain triggers in the first place, and you can have you audit log in a system separate from your main database. Blogged about one possible implementation using Debezium a while ago here: https://debezium.io/blog/2019/10/01/audit-logs-with-change-data-capture-and-stream-processing/ https://debezium.io/blog/2019/10/01/audit-logs-with-change-d... Disclaimer: I work on Debezium
  - gmiller123456 4y ago
    I think you'll find that most operating systems have this built in, syslog for Linux and the event log on Windows. We actually use SPLUNK which has lots of tools for collecting data from apps using different methods, and also tools for viewing and querying the data.
techdragon 4y ago
One of the common ways I've seen it is writing logs in a write only way. Most common way I've seen deployed is having the audit logs setup so they go to files in an AWS S3 bucket owned by a completely separate AWS account with Write Only credentials granted to the application doing the log writing. Its effectively "dumped" there and cannot be seen or touched even by the writing application. Sort of like having a 1 way valve in your logging "pipeline".
jrumbut 4y ago
I've long wished for an RDBMS (or perhaps ORM, but I think it would be extra cool at the database level, see below) that does things that don't hyperscale. So many LOB applications (the part of the iceberg under the water) have modestly sized databases with a relatively consistent number of users. The data is small, but often complex, awkwardly structured, highly relational, etc. The challenges these applications face are different, but real. An example of something that would be incredibly useful here but completely untenable for a Facebook scale system would a rich rewind mechanism. You can go back in time 15 minutes or execute a query like: RESTORE my_db TO LAST POINT WHERE EXISTS (SELECT * FROM ACCOUNTS WHERE ID=1234) How much trouble would that kind of query have saved you in your career? At some point this would be cost prohibitive but I bet 50% or more of all apps never reach that point.
- salzig 4y ago
  CockroachDB has "AS OF SYSTEM TIME", which even allows you to backup the database for that moment. https://www.cockroachlabs.com/docs/stable/as-of-system-time.html https://www.cockroachlabs.com/docs/stable/as-of-system-time....
  - jrumbut 4y ago
    CockroachDB has been a font of cool ideas from the beginning IMO.
  - tornato7 4y ago
    BigQuery has this too, it’s extremely useful.
- remus 4y ago
  MS SQL Server has a fun feature called temporal tables that does some of this https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://docs.microsoft.com/en-us/sql/relational-databases/ta...
  - jrumbut 4y ago
dkarl 4y ago
+1 to investigating user error. It also happens that you have bugs, and it's hard to spot the bugs if you don't have a quick way of filtering out all the cases of user error. > Yeah. As far as a user-facing "Undelete" button existing or being used... that's very rare in my experience. This is pretty common in my experience, but often with slightly different terminology. I've seen "delete"/"undelete" but also "move to trash"/"restore from trash" or "mark for deletion"/"unmark for deletion" and even "archive"/"restore from archive" where deleted/marked/archived objects were excluded from normal business processes and were hidden in the UI unless the user went to a special page where they could search, view, and restore. I've also seen chat functionality where users had the ability to delete messages but admin users had the ability to view the full history of a chat, including deleted messages. (The software was used by businesses to communicate with their customers, who were also businesses, so supervisors being able to read deleted chat messages was appropriate.) In my experience soft delete is basically free if you do it from the start. I've never regretted soft delete but have always regretted the lack of it. However, it's easier if you also have a data retention policy from the start so you're forced to create automated cleanup processes and maintain them as you go along.
- JohnBooty 4y ago
  This is pretty common in my experience, but often with slightly different terminology. [...] I've also seen chat functionality where users had the ability to delete messages but admin users had the ability to view Great points! Slack actually works that way, if I'm not mistaken. Admins can read deleted messages and, I think, the edited versions of messages right? Not sure how they store things internally, but you're right - it's common functionality in many classes of apps. In my experience soft delete is basically free if you do it from the start. This is my experience too.
tornato7 4y ago
This is something that public or private blockchains could be helpful for. Since everything is built on the hash of what came before, you can’t delete something without leaving a trail.

gmiller123456 4y ago

How would you set up a secure audit trail that didn't rely on the application and/or database at some level? A database is fine for an audit trail. But the application shouldn't have permissions to cover up a change. Likewise, the audit trail should record that a record was created, deleted, maybe undeleted, etc and when, by who. Relying only on the "deleted" flag only shows you the current state, not what and when was done and who did it.

sverhagen 4y ago
Any tools you recommend here? Or just roll your own with triggers in the database?
- gunnarmorling 4y ago
  Log-based change data capture is a great tool for building audit logs; unlike triggers, it doesn't impact write performance, you don't need to maintain triggers in the first place, and you can have you audit log in a system separate from your main database. Blogged about one possible implementation using Debezium a while ago here: https://debezium.io/blog/2019/10/01/audit-logs-with-change-data-capture-and-stream-processing/ https://debezium.io/blog/2019/10/01/audit-logs-with-change-d... Disclaimer: I work on Debezium
- gmiller123456 4y ago
  I think you'll find that most operating systems have this built in, syslog for Linux and the event log on Windows. We actually use SPLUNK which has lots of tools for collecting data from apps using different methods, and also tools for viewing and querying the data.

techdragon 4y ago

One of the common ways I've seen it is writing logs in a write only way. Most common way I've seen deployed is having the audit logs setup so they go to files in an AWS S3 bucket owned by a completely separate AWS account with Write Only credentials granted to the application doing the log writing. Its effectively "dumped" there and cannot be seen or touched even by the writing application. Sort of like having a 1 way valve in your logging "pipeline".

jrumbut 4y ago

I've long wished for an RDBMS (or perhaps ORM, but I think it would be extra cool at the database level, see below) that does things that don't hyperscale. So many LOB applications (the part of the iceberg under the water) have modestly sized databases with a relatively consistent number of users. The data is small, but often complex, awkwardly structured, highly relational, etc. The challenges these applications face are different, but real. An example of something that would be incredibly useful here but completely untenable for a Facebook scale system would a rich rewind mechanism. You can go back in time 15 minutes or execute a query like: RESTORE my_db TO LAST POINT WHERE EXISTS (SELECT * FROM ACCOUNTS WHERE ID=1234) How much trouble would that kind of query have saved you in your career? At some point this would be cost prohibitive but I bet 50% or more of all apps never reach that point.

salzig 4y ago
CockroachDB has "AS OF SYSTEM TIME", which even allows you to backup the database for that moment. https://www.cockroachlabs.com/docs/stable/as-of-system-time.html https://www.cockroachlabs.com/docs/stable/as-of-system-time....
- jrumbut 4y ago
  CockroachDB has been a font of cool ideas from the beginning IMO.
- tornato7 4y ago
  BigQuery has this too, it’s extremely useful.
remus 4y ago
MS SQL Server has a fun feature called temporal tables that does some of this https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://docs.microsoft.com/en-us/sql/relational-databases/ta...
- jrumbut 4y ago

dkarl 4y ago

+1 to investigating user error. It also happens that you have bugs, and it's hard to spot the bugs if you don't have a quick way of filtering out all the cases of user error. > Yeah. As far as a user-facing "Undelete" button existing or being used... that's very rare in my experience. This is pretty common in my experience, but often with slightly different terminology. I've seen "delete"/"undelete" but also "move to trash"/"restore from trash" or "mark for deletion"/"unmark for deletion" and even "archive"/"restore from archive" where deleted/marked/archived objects were excluded from normal business processes and were hidden in the UI unless the user went to a special page where they could search, view, and restore. I've also seen chat functionality where users had the ability to delete messages but admin users had the ability to view the full history of a chat, including deleted messages. (The software was used by businesses to communicate with their customers, who were also businesses, so supervisors being able to read deleted chat messages was appropriate.) In my experience soft delete is basically free if you do it from the start. I've never regretted soft delete but have always regretted the lack of it. However, it's easier if you also have a data retention policy from the start so you're forced to create automated cleanup processes and maintain them as you go along.

JohnBooty 4y ago
This is pretty common in my experience, but often with slightly different terminology. [...] I've also seen chat functionality where users had the ability to delete messages but admin users had the ability to view Great points! Slack actually works that way, if I'm not mistaken. Admins can read deleted messages and, I think, the edited versions of messages right? Not sure how they store things internally, but you're right - it's common functionality in many classes of apps. In my experience soft delete is basically free if you do it from the start. This is my experience too.

tornato7 4y ago

This is something that public or private blockchains could be helpful for. Since everything is built on the hash of what came before, you can’t delete something without leaving a trail.