5 ms·
Ask HN: How do you implement audit trail for a product?
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?
- bradwood 5y agoI 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 5y agoHow would CDC include information like the user making the change?
- shaeqahmed 5y agoA 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.
- gunnarmorling 5y agoWrote about this very approach on the Debezium blog some time ago: 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.... It uses Kafka Streams for enriching actual change events with that transaction-scoped metadata.
- wizwit999 5y agoAudit 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 https://apptrail.com (Disclaimer: founder).
- adithyak 5y agoAt 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 5y agoINSERT only, no UPDATEs. Queries return most recent record. Periodically groom older entries.
- moralestapia 5y agoJust 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_ 5y agoWouldn’t grooming older entries eliminate the trail?
- romeoblade 5y agoYou 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 5y agoYes 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_ 5y agoPossibly a tangential question but how would one deal with data erasure requests in such a scenario
- beagle3 5y agoThis 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)
- cmrajan 5y agoA basic audit-trail can be implemented by using Postresql triggers [1]. It logs client IP, statement, before & after changes and can be enabled on selected tables. [1] https://wiki.postgresql.org/wiki/Audit_trigger_91plus https://wiki.postgresql.org/wiki/Audit_trigger_91plus
- kkirsche 5y agoTemporal tables where possible or insert only structures with a deleted “state” representation
- TheColorYellow 5y agoA 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.