Audit#

Problem#

  • Do you need to know who changed what, when, why in your SQL database?
  • Do you want an immutable record of changes going back to the inception of your database?
  • Is an audit team asking for this information for compliance purposes?
  • Do you want to be able to query this audit log like any other table in your database?
  • Do you want the data to be efficiently stored so you can trace changes back to inception?

Doltgres solves this by…#

Doltgres provides a built-in, queryable audit log of every cell in your database. Whenever a Dolt commit is created, the user, time, and optional commit message are recorded along with the data that changed. These commits form an immutable log of changes to every cell in your database going back to inception.

Doltgres stores these changes efficiently by sharing data that hasn’t changed between all commits referencing that data. Effectively, only the differences are stored between versions of the data.

The audit log created between commits is queryable via standard SQL using custom system tables in the reserved dolt schema (like dolt.log and dolt.diff) and functions. The results can be filtered and joined using other data in your database.

If you’re not ready to switch your primary database to Doltgres to get its audit capabilities, you can run Postgres as your primary and set Doltgres up as a versioned replica. You lose users and commit messages but you still get a queryable log of every cell in your database.

Doltgres replaces…#

Soft Deletes#

A technique to add audit capability to an existing database is to add soft deletes. Soft delete is the use various techniques to mark data as inactive instead of deleting it. This is strictly worse than a version controlled database for audit purposes. With soft deletes, an operator can still modify data or the application can make mistakes. In Doltgres, every write is part of the audit log. It is far more difficult for an operator to change Doltgres history.

Change Data Capture#

Change Data Capture is another way to add audit capability to an existing database. Some change data capture techniques are similar to soft delete strategies. Modern change data capture tools consume replication logs to audit database changes. Doltgres can consume the same logical replication stream in the versioned Postgres replica use case producing a simpler and thus, more audit-friendly, change data capture solution.

Moreover, if Doltgres is your production database, there is no need for an additional change data capture system. The audit capability is a built-in feature of the production Doltgres database.

Companies Doing This#

These companies use Dolt, Doltgres’s MySQL-compatible cousin, for audit:

Case Studies#

Let us know if you would like us to feature your use of Doltgres for audit here.