Postgres Auditing in 150 lines of SQL

We have a new business system we are trying to build that has a <i>lot</i> of very granular auditing and versioning requirements throughout, and this is where I started looking.<p>After a while of screwing with this kind of approach, I realized that the number of different types of objects involved was going to cause an explosion in the number of related auditing and versioning tables. Keeping track of not only who changed something but also the exact facts they changed (such that you could revert as needed) requires a lot of additional boilerplate in each instance.<p>So, I made a pretty big gamble and went all-in on an event-sourced abstraction where everything is written to 1 gigantic append-only log of events. Turns out, this was worth it in our case. As long as everything has a timestamp and you have a way to refer to specific log entries (i.e. by offset), you basically have a complete solution. The hard parts are handled by a JSON serializer and gzip. We use in-memory indexes that are simply long&#x2F;long dictionaries that map an object identity to a physical log offset.<p>The only downside with what I have proposed is that there is no garbage collection or cleanup possible. At this time, this is actually a highly-desirable property of the system. Disk space is cheap. Losing 3 weeks of work to an accidental button click is not.

Love the detail and approach here, though (and it is mentioned in the bottom of the article) this shouldn&#x27;t be used at any very large scale... the estimate of at about 1k transactions per second or less seems about right. For any larger scale you want to look at something like pg_audit which can scale much much further and be used with other extensions to ensure you have a full audit log that can&#x27;t be modified (when dealing with compliance).

I&#x27;ve battled this problem many times over my career in many different ways - this approach looks very solid to me. I particularly like the way the schema design incorporates a record ID, old record ID, updated record and old record.<p>You do end up storing a lot of extra data, but it&#x27;s very thorough - it&#x27;s not going to miss anything, and figuring out what happened later on should be very easy.

I didn&#x27;t see this mentioned in the article but with JSONB you can leverage operators to know what data changed.<p><pre><code> &gt; -- record = {&quot;id&quot;: 1, &quot;name&quot;: &quot;bar&quot;}, old_record = {&quot;id&quot;: 1, &quot;name&quot;: &quot;foo&quot;} &gt; select record - old_record where id=3; name =&gt; bar &gt;</code></pre>

The post leads to a good Supabase Postgres auditing tool as a PG extension named supa_audit:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;supabase&#x2F;supa_audit" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;supabase&#x2F;supa_audit</a>

I really like this approach, however I have a use case where the application user id that made the operation must be saved in the audit table, unfortunately, I cannot see how to do that with a pure SQL solution.<p>Has anyone done something similar with SQL only?

author here (of the blog post and <a href="https:&#x2F;&#x2F;github.com&#x2F;supabase&#x2F;supa_audit" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;supabase&#x2F;supa_audit</a>)<p>happy to answer any questions

At 42Floors, we experienced a similar need for auditing. Initial we did a similar approach as described in the post. Over time the audit tables began to be incorporated into every aspect of the product, which over time caused the table to become quite large, and ended up causing a lot of downtime. Our initial implementation only audited what was inserted by our Rails application. If a change was made by another application or manually, it wouldn’t appear in the table.<p>Over the course of a few months we extracted the system and built a standalone application that today has become [changebase.io](<a href="https:&#x2F;&#x2F;changebase.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;changebase.io&#x2F;</a>). We now use CDC, change data capture, to capture all events in the database and a metadata table to capture the context of why the change occurred and who made the change. The changes are then structured to be easily queryable.<p>Initially, we encoded the data as JSON, but over time have opted for encoding the data in the database’s native format. This creates a more complex query system, but at the end of the day we don’t have to worry about type conversions and querying is done in the same format the database that was replicated. Teams have benefited from the system by being able to recover data that was accidentally deleted, or deleted maliciously. Notifications have also been used for high value datasets where someone on a sales team wants to immediately know of a change in the system.

This looks like it works pretty well to just &#x27;dump&#x27; data changes, and if needs be to visualize them somewhere, but the data is unstructured and long-term there may no longer be any relationship to older data.<p>Which may be fine for your use case, don&#x27;t change anything if it works for you.<p>In my use case, I also have a requirement that a user needs to be able to revert changes - an undo function, for edits and a &#x27;deleted&#x27; state. I&#x27;ve been looking into Temporal Tables for that, where every table has a sibling &#x27;history&#x27; table with a pair of timestamps - valid from and valid to. This allows you to query the database - including related rows in other tables - at a certain point in time, and to revert to a previous state without losing any intermediate steps (copy rows at timestamp X, insert as the &#x27;current&#x27; version).<p>This is a built-in feature in a lot of modern enterprisey databases, but I&#x27;m constrained to sqlite. But, using triggers I&#x27;ve been able to make it work in a POC.<p>Whether it&#x27;ll work in my &#x27;real&#x27; application is yet to be determined, I&#x27;ve got a lot of work to do still - I need to get rid of my ORM and redo my data access to something more manual, unfortunately.

Ok, my SQL skills might be a bit lacking, but from the article:<p>&gt; select audit.enable_tracking(&#x27;public.members&#x27;);<p>So you can actually have functions that have side effects in a select statement? I guess nothing prevents it, so it is allowed. But somehow I have the impression that select statements don&#x27;t change anything.

Bitemporal Database: <a href="https:&#x2F;&#x2F;xtdb.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;xtdb.com&#x2F;</a><p>Datomic: <a href="https:&#x2F;&#x2F;www.datomic.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.datomic.com&#x2F;</a>

I&#x27;d argue that&#x27;s still a lot of work to manually do. However, great work and detail, thanks a lot :-)<p>I&#x27;m working on a database system[1] in my spare time, which automatically retains all revisions and assignes revision timestamps during commits (single timestamp in the RevisionRootPage). Furthermore, it is tamper proof and the whole storage can be verified by comparing a single UberPage hash as in ZFS.<p>Basically it is a persistent trie-based revision index (plus document and secondary indexes) mapped to durable storage, a simple log-structured append-only file. A second file tracks revision offsets to provide binary search on an in-memory map of timestamps. As the root of the tree is atomically swapped it does not need a WAL, which basically is another data file and can be omitted in this case.<p>Besides versioning the data itself in a binary encoding similar to BSON it tracks changes and writes simple JSON diff files for each new revision.<p>The data pages are furthermore not simply copied on write, but a sliding snapshot algorithm makes sure, that only changed records mainly have to be written. Before the page fragments are written on durable storage they are furthermore compressed and in the future might be encrypted.<p>[1] <a href="https:&#x2F;&#x2F;sirix.io" rel="nofollow">https:&#x2F;&#x2F;sirix.io</a> | <a href="https:&#x2F;&#x2F;github.com&#x2F;sirixdb&#x2F;sirix" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;sirixdb&#x2F;sirix</a>

This is a great technique, we used to have it setup very similarly at a previous startup and worked wonders. Thanks for sharing a generic way of doing it @oliverrice !

Is there something like Datomic for Postgres? Having your team learn clojure, datalog, deploying Datomic was a huge pain for us. Not the road we recommend since all we really needed were just immutable append only log of all transactions.

I did a similar thing with SQL Server using XML. I created views that would convert the XML value back to the correct SQL data type when querying the audit table. The view also Unions the audit data with the current data so that you could add the trigger at any time and the view would return the current data (the audit table only stores updates and deletes). One nice addition was saving the current user ID with all audit records, which I was able to get in the trigger by adding it to the db connection using CONTEXT_INFO().

Debezium can be used for this in embedded mode if you are on the JVM.

I&#x27;ve been using my own audit trigger for 3 years now.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;cmabastar&#x2F;audit-trigger" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;cmabastar&#x2F;audit-trigger</a><p>It uses the old and tested <a href="http:&#x2F;&#x2F;wiki.postgresql.org&#x2F;wiki&#x2F;Audit_trigger_91plus" rel="nofollow">http:&#x2F;&#x2F;wiki.postgresql.org&#x2F;wiki&#x2F;Audit_trigger_91plus</a><p>but with JSONB instead of HSTORE, and automatically creates partitioned tables by month (Requires PG11+).

I am afraid data audit is not just about data changes. The real challenge is how to audit the data read which people should not do.<p>Since there is no redo logs generated for data read, CDC could not help in this case. It will reply on the audit traces, the SQL capture and cache, etc. But it&#x27;s costly.

I’d +1 the usage of pgAudit when higher throughput is needed. If you’re on AWS, RDS can automatically ship these logs to S3 where you can then run Athena queries against them.

This is a great article! Fantastic read.

Is there something similar for sqlite?