9 ms·
In almost every database-backed application I've ever built someone, at some point, inevitable asks for the ability to see what changes were made when and by wh
by simonw 6y ago
In almost every database-backed application I've ever built someone, at some point, inevitable asks for the ability to see what changes were made when and by whom.
My current preferred strategy for dealing with this (at least for any table smaller than a few GBs) is to dump the entire table contents to a git repository on a schedule.
I've run this for a few toy projects and it seems to work really well. I'm ready to try it with something production-scale the next time the opportunity presents itself.
- sedeki 6y agoSo each commit is exactly one change to a relevant table? I guess you can't just plug this into a common system of rotating logs easily, as there might be several changes beteeen the log rotation. Also, I guess you'd need a user friendlier interface to actually display who made the change from the git repo. Anyway, interesting solution.
- lisper 6y ago> My current preferred strategy for dealing with this (at least for any table smaller than a few GBs) is to dump the entire table contents to a git repository on a schedule. How does that solve the problem? All this would do is give you snapshots of the state of the DB at particular points in time. The only way to know who changed what when is to keep track of those changes as they are made, and the best way to do that is to stick the changes in an eventlog table.
- zikzak 6y agoA developer joined a team I was on once. The boss was infamous for hiring anyone with a pulse and assigning a small client project. Sink or swim. As the senior dev it was my job to provide info as needed and subtly assess the new, potential rockstar code ninjas. Anyway, this new guy says "I store all my dates in sql as text in iso format so I can easily sort them".
- com2kid 6y agoThat is... almost not horrible? If he had said anything other than "in sql" it would be the correct answer. "I store all dates in spreadsheets in ISO format" "I prepend ISO dates to marketing campaign names so I can easily sort them in the horrible web frontends that doesn't have a sort by date option" "I start all my blog entries with ISO dates..." I mean, just... so close!
- collyw 6y agoIt's actually a decent hiring process providing the new hires are made aware of it in advance.
- zikzak 6y agoHe was a radical honestly type guy. It was all on the table and we had lots of success stories. We would give anyone a chance which let a lot of self taught, entry level people get work. I got a ton of experience mentoring people, as well (difficult people, too). Unfortunately, he got screwed over by a shotgun clause in a business agreement and lost the business that to an ex-partner. That's what led me to my current job, though, where I've been happy for over 10 years and it's much more stable.
- dragonwriter 6y ago> "I store all dates in spreadsheets in ISO format" As in SQL, the application’s native date format (which is usually a number with appropriate format) would be the natural choice and a YYYY-MM-DD string would be a distant second. ISO (presumably, 8601) format is too broad, and admits of a mix of formats that are not simply sortable (quick, which is first 2021-W14-2 or 2021-04-07?).
- com2kid 6y agoEh, when people say ISO they mostly mean YYYY-MM-DD. If a date is going to be string prepended to something else, or if the spreadsheet is going to be exported to CSV, or parsed by some tool, then storing dates in YYYY-MM-DD can be a very justifiable thing to do in a spreadsheet. Also Excel likes to format dates using the system locale, which is, well, stupid. So I have to enter YYYY-MM-DD as a custom format anyway, or change the locale on the column to China. (Or Cherokee, who apparently also properly format their dates!) (Also Excel is smart enough if you set a column as YYYY-MM-DD custom format, it'll let you use all the date based pivot table stuff as expected)
- acidbaseextract 6y agoCompletely agreed on the inevitability of that ask. At risk of extreme overkill the other way, something like Debezium [1] doing change monitoring dumping into S3 might be a viable industrial strength approach. I haven't used it in production, but have been looking for appropriate time to try it. [1] https://debezium.io/ https://debezium.io/
- irrational 6y agoSo you commit to Git. Sally makes a change to column 1 of record 1. Billy makes a change to column 2 of record 1 a nanosecond later. You commit to Git again. Your boss wants to know who changed column 1 of record 1. You report it was Billy. Billy is fired.
- TeeMassive 6y agoYour boss let changes go through the production DB directly Your boss decides to fire people as a solution Your boss should be fired
- livvelupp 6y agoYour boss doesn't micro manage You should be taking care of this, not Billy
- TeeMassive 6y agoFiring a team member for a team mistake is micromanagement.
- irrational 6y agoI’m assuming Billy was fired because Sally made an unethical or illegal change.
- antonvs 6y agoBilly did nothing wrong!
- irrational 6y agoI’m assuming the change Sally made was an unethical or illegal change.
- simonw 6y agoYou report it could have been Sally or Billy
- Aqueous 6y agousually businesses use auditing tables to track this, usually by setting up triggers to insert a row into the auditing table any time a delete, update or insert occurs on the original
- swiley 6y agoITT HN collectively learns what an "audit log" is.
- antonvs 6y agoI would imagine the functional programming advocates here already understand this. For the rest, audit logs are at least a baby step towards that. Progress!
- harperlee 6y agoThat reminds me of https://www.enterpriseready.io/ https://www.enterpriseready.io/ where you can find a guide of which features, such as an audit log, you may not have though about when building your side project, B2C startup, etc. that will facilitate its uptake by companies. For example, for audit logs: https://www.enterpriseready.io/features/audit-log/ https://www.enterpriseready.io/features/audit-log/
- simonw 6y agoSince this is being picked apart a bit in the comments, I should clarify that I don't think this is the BEST solution to this problem. It's the solution that gives you 90% if the value with 5% of the work.
- dragonwriter 6y ago> It's the solution that gives you 90% if the value with 5% of the work. It’s not, though. History tables don’t provide only 111% of the value of this, and don’t take 20× the effort. They have a deterministic relation to base tables (so generating the schema from that of base tables, and the code necesaary for maintenance, is a scriptable mechanical transform if you have the base table schema.) OTOH, lots of places I've seen approach this by haphazard addition of some of what I think of as “audit theater” columns to base tables (usually some or all of “created”, “created_at”, “last_updated”, “updated_by”), so its also not the worst approach I’ve seen.
- gjvnq 6y agoWhy not have a history table and use SQL triggers to insert the new data as JSON everytime someone changes something?
- coolgeek 6y agoIndeed. I have on multiple occasions written scripts to automatically generate the triggers and audit tables
- vishnugupta 6y agoA running joke at Uber is that every single configuration management system will end up re-implementing a half ass version of Git.
- bitwize 6y agoWere their exact words "an ad hoc, informally specified, bug-ridden, slow implementation of half of Git"?
- vishnugupta 6y agoHeh something to that effect. I’m not at Uber anymore so can’t reference the article which makes this assertion. It is quite well written, starts off with a super simple version of configuration management system and describes how it progressively evolves to encompass 80% of Git features.