Comment by specialist | Hacker News Reader

Comment by specialist | Hacker News Reader

Generally, IT systems cannot be inspected or audited. Because they use the wrong data architecture.

One of the core problems is the notion of single source of truth, meaning database records are updated in place. IT needs to adopt an accountant's bookkeeping worldview.

The Correct Answer is to model organizational behavior as events, capturing state changes in an event log. What accountants call ledgers.

Oversimplifying:

Ban use of SQL UPDATE, only permit SQL INSERT. (Translate to equivalent operations for your persistence engine.)

Do not use separate "Truth" and "Historical" (aka Audit) tables. Unify them into a single table.

Do query (SELECT) for what is assumed to be the single best record (SBR). Rather, query for all relevant events (records) and reduce to determine correct answer at this time.

--

Forgive me for not articulating these ideas very well. My team worked through all these issues in the healthcare space. What seemed obvious to us, like banning SQL UPDATE, was considered radical by pretty much every one else.

If any one else is writing about these ideas, please share.

And, sorry, no, the new cupcake frosting Event Modeling (Event Sourcing?) is not what I'm talking about.

All good advice. A hybrid can also be useful.

In some accounting systems they have the ledger table (a list of every transaction) and also a balance table (a running total)

The balance table gives you fast access to a total (by account, branch etc) without having to add up the ledger (which may be very large), and upon inserting the ledger entry it is UPDATEd in place by the same amount. If this is done inside the same transaction it should be safe.

There is then a periodic audit process to lock the transaction ledger, add it up and check it against the balance.

This gives the user a full sight of the transaction history and the fast response of totals.

So the balance table is like a cache. I'm okay with that strategy.

On my To Do list is mash up a JDBC frontend for ehcache.

Yes, mostly up to date, mostly correct, fast to access, easy to prove, not the source of the truth.

A pretty good analogy. In paper days you would add up your various ledgers (perhaps each in its own book, sales, purchases, cash etc) at the end of each day and check that the whole thing balanced. You didn't get to go home until it did!

The other thing you can take from accountancy is double- entry...

A toy example. Money was spent on x, so it gets charged to x account and removed from the bank. More correctly debit x-expense, credit bank. Credits are by convention negative numbers. Now no matter what happens your accounts always need to balance, ie the entire set of ledgers add up to zero. Audit checks are now trivial, if either your balances table or your transactions table do not sum to zero, alert somebody.

It has been working for accountants for 2000 years! https://en.m.wikipedia.org/wiki/Double-entry_bookkeeping_sys...

> The Correct Answer is to model organizational behavior as events, capturing state changes in an event log. What accountants call ledgers.

That's one step. Another one is to sign the relevant data, and make it available for auditory.

Somebody already talked about a blockchain... Well, the chain isn't actually necessary, as long as all interested parties have a receipt. It also adds nothing if all the signatures stay with a single party.

As I understand it, part of the problem is that this was a ledger-based system - just a really terribly implemented one which gave the sub-postmasters who were liable for any shortfalls terrible visibility into what the transaction history was, why corrections were being made, whether their own staff could be defrauding them, etc.

Well darn.

Technology can't fix governance problems.

Very interesting. I'm not much of a DB person, but it sounds similar to the idea of "stateless". In other domains this is very important, eg. pure functional programming. It's just good programming practice in general to keep state to a minimum.

Its more you have a table like so:

    ID VALUE CHANGED
     1     1  date
     2     2  date+n

etc...

Where you never actually update id 1 in place and setup views etc... to wrap over the table(s) that ignore the history.

I use this all the time in postgresql, you'd be shocked how often having the history of a specific entry getting updated comes in handy. Even for application logic (like, slow down, why is this one table getting updated 1000 times a second? lets reject updates for a bit cause something has gone haywire)

Highly recommend it, BUT if you implement it you need to also deal with the history accumulation. I just setup window functions to limit the history to N in size generally or setup some cleanup job to clean out really old entries at a certain point.

I don't work in healthcare but like the GP I consider UPDATE in place a generally bad idea as well. You can turn on audit logging but the issue with that is the logs are always in another place so nobody ever looks at it until they need to, and only then do you find out the audit logs weren't setup right/etc....

But yes just like functional programming, don't mutate in place, append immutable values to the end. Same idea.

Thanks. Exactly. With your simple example, I now don't know why I've had such a hard time articulating it.

Since "events" arrived whenever, we also added a "received" timestamp. Great for debugging. Necessary for compliance. ("What did you know and when did you know it?")

We had some clever indices on the timestamps, so queries remained performant. (Sorry, I'd have to lookup the details, names of things.) Not being a DBA, I eventually learned the trick was to properly normalize to avoid GROUP BY, and so forth.

Thanks again.

What's the key difference between what you write about and event sourcing? Is just that you object to having any kind of reified application state outside of the event records?

As far as I can see, the GP is describing event sourcing. In a way that people can easily understand why it's important.

So how is what you describe different from Event Sourcing?

Ya, I should probably: show by example, not dismiss Event Sourcing so readily, not get sucked into a No True Scotsman argument, or all the above.

Fowler's high level overview is https://martinfowler.com/eaaDev/EventSourcing.html

Which doesn't cover the persistence, the actual data model. In particular, I disagree with the notion of a separate Audit Log.

More recently, on recent projects, anyone using a message queue or bus has called their goo Event Sourcing. Which is mostly just shuttling around analytics. Not modeling organizational behavior as events. Not considering how to design the event sinks (RDMBS tables).

Someday I'll write up what I did for healthcare records. Sorry I haven't already.

Thankfully big actors seem to have taken an interest in blockchains, which seem to be that right data architecture !

At the time, I just called it logging. I did investigate tamper evident logs (sign records with rolling hashes) for compliance and legal use cases.

Today, I'd consider blockchains for shared ledgers (logs). eg Across organizational boundaries.

Reply on news.ycombinator.com