HN Explorer

Ask HN: How do you implement audit trail for a product?

16 points by _448 ↗ HN
Say I want to log every change to the database, how does one go about implementing that when using open source DBs like Postgres or CouchDB? Are there open source, on-premise solutions available for this?

Also, what other factors need to be considered when implementing audit trail?

17 comments

[ 2.9 ms ] story [ 55.2 ms ] thread
bradwood
I think the best pattern for this is Change Data Capture (CDC). Checkout out something like Debezium.

In short, IIRC, it looks into the RDBMSs redo log, and turns it into a message stream. You can then stream this into some kind of messaging platform (Kakfa is typical) and then process the data any way you want. Log it, aggregate it, archive it, stick it in another database, or object store, etc...

emptysea
How would CDC include information like the user making the change?
shaeqahmed
A CDC alone wouldn't. You would need to track and store that metadata in an additional database along with transaction ID, so it can be joined with the raw change stream to form a complete audit log.
wizwit999
Audit trails have several considerations. For example, you need to them to be immutable, ensure guaranteed delivery, and be able to support extended data retention (on the order of years).

Because audit trails record activity, I would generally recommend linking the audit trail to the API call site rather than the database, as you should include information such as the actor (who made the call/request) and context (e.g. what IP address and user agent the call came from). An additional good practice is to add the request and response of the API call being recorded. You should also redact any sensitive or PII fields.

If you're looking for a managed service that takes care of all of this, and delivering it to your customers for you, check out https://apptrail.com (Disclaimer: founder).

adithyak
At my previous company, we implemented basic audit trails using postgres triggers that recorded the before & after state of a row on upsert / delete. The triggers had to be set individually for each of the tables (same trigger function worked for all), and an added convenience view to see the diff of the changes helped. This worked pretty well us, since we didn't need details of the actor / IP address etc.
specialist
INSERT only, no UPDATEs. Queries return most recent record. Periodically groom older entries.
moralestapia
Just wanted to x2 this, in several projects we end up with this setup and gets the work done.

If you want to get just a bit more fancy, add a hash that depends on previous entries, like some sort of proto-blockchain.

andrei_says_
Wouldn’t grooming older entries eliminate the trail?
romeoblade
You generally dump them into a data warehouse after a certain amount of time so they are not in the live database anymore. You still have the records and can still reference them for reports and compliance reasons.
specialist
Yes and: Most of our queries (current patients) only needed the most recent records. For our clients, it was ok that older data took a bit longer to retrieve, because those patients weren't present (eg on the phone vs not currently in the emergency room).
andrei_says_
Possibly a tangential question but how would one deal with data erasure requests in such a scenario
beagle3
This works well for the audit part - have done this myself more than once - but makes it hard to impossible to get good query performance for some kinds of queries.

You need to have a trigger that keeps an "up-to-date" view of only the latest version -- or alternatively, have a trigger that keeps the "insert only" table based on one you treat as usual (delete/insert/update)

specialist
SQL Server has temporal indices. Our queries seemed fast enough. Sorry, I don't know Postgres, but would expect there's some kind of solution.

We stored medical records. Datafeeds were very chatty. Updates would arrive out of order. Resolving "single best record" (aka "source of truth") was trivial via queries. The norm seems to be to adjudicate as data comes in. So my solution really upset a lot of people.

To your point, I did have to change the schema for scripts and allergies. To get the indices arranged just right so that GROUP BY queries were performant.

Further: UPDATE only strategy enables temporal testing; just specify time range. Saves a lot of time, effort. Versus norm of reloading the data from scratch every test run.

kkirsche
Temporal tables where possible or insert only structures with a deleted “state” representation
TheColorYellow
A lot of the database solutions don't seem to account for full security or full verification of the transaction context. You can certainly create an audit trail for all database activity, but auditing other aspects of the product requires some additional work.

The App Trail comment provides a good example of the necessary user context you may want. Additionally, you may also need to require verification that activity was logged, for example for a transaction to complete you want redundancies to ensure the logging occurred correctly.

Essentially it boils down to recreating user authentication and authorisation type functionality, but for all of your middleware pieces and components. You can capture the logs into a single database, do some hashing, and maybe include signatures from devices and users and you should have pretty coverage.