Distributed Audit Logs
Dolt is the world's first and only version controlled SQL database. As part of Dolt's version control functionality, you get a database-enforced, queryable audit log of every cell in your database going back to inception. This functionality replaces Slowly Changing Dimension methods which are error prone and complicate your schema. Dolt is a great database to use if you need to know who changed what and when.
As we've gotten deeper into our journey, multiple users have asked about a distributed audit log use case. Users want to be able to produce an audit log across multiple distributed Dolt databases. Like Git, Dolt has clone, push, and pull so Dolt can natively track changes across clones if changes are pushed to the same remote. However, this use case requires all clones have access to all data and history.
What if you want certain clones to only have a subset of the data and/or history? Can Dolt still produce a distributed audit log? The answer is yes but it requires some manual assembly on a node with access to all copies. This article will discuss and provide a detailed example.
Use Case
Let's get a little more specific on the use case.
You have a highly privileged database with strong access controls. This database contains your entire dataset, knowledge base, or configuration. It is highly confidential and only a few people and systems can access it. You want full audit log capability on this database for writes. Let's call this database parent.
You want to subset the parent database and give that subset looser access controls. Maybe you want to use a subset of the data for training a model or to test a change. You also want to be able to audit changes to this database. Let's call this database subset child.
When necessary, you want to be able to assemble a full audit log of changes across both parent and child. Necessarily, this audit log can only be constructed in the high privilege environment because you are accessing privileged parent data to build the audit log.
Example
Let's show how you can achieve this use case using Dolt via an illustrative example.
We start with a new directory. We make three sub-directories to simulate three isolated machines, parent, child, and remote. parent is the highly privileged database. child is the subset with looser permissions. remote is a third machine where the parent machine has clone, push and pull access but will only ever push and pull the child database. child has full access to remote as well. We use dolt init to create the appropriate databases.
$ cd ~/dolthub/dolt/
$ mkdir multidb_audit_log
$ cd multidb_audit_log
$ mkdir parent
$ mkdir child
$ mkdir remote
$ cd child; dolt init; cd -
Successfully initialized dolt data repository.
~/dolthub/dolt/multidb_audit_log
$ cd parent; dolt init; cd -
Successfully initialized dolt data repository.
~/dolthub/dolt/multidb_audit_logNext we start the parent sql-server on port 11111 in the parent directory.
$ cd parent
$ dolt sql-server -port 11111Let's create some data in the parent. Connect a sql shell to the running database.
$ dolt --host localhost --port 11111 --no-tls sql Now use SQL to create a table and some sample data.
parent/main> create table t (id int primary key auto_increment, word varchar(40));
Empty set (0.01 sec)
parent/main*> insert into t(word) values ('these'), ('are'), ('secret'), ('words');
Empty set (0.01 sec)
parent/main*> select * from t;
+----+--------+
| id | word |
+----+--------+
| 1 | these |
| 2 | are |
| 3 | secret |
| 4 | words |
+----+--------+
4 rows in set (0.00 sec)
parent/main*> insert into t(word) values ('these'), ('are'), ('public'), ('words');
Empty set (0.01 sec)
parent/main*> select * from t;
+----+--------+
| id | word |
+----+--------+
| 1 | these |
| 2 | are |
| 3 | secret |
| 4 | words |
| 5 | these |
| 6 | are |
| 7 | public |
| 8 | words |
+----+--------+
8 rows in set (0.00 sec) As you may be able to tell, ids 1 through 4 are secret. ids 5 through 8 are public. Note, distributed audit log functionality requires tables with primary keys, in our case the id column. Dolt tracks changes across versions based on primary keys.
Now let's make a Dolt commit to preserve our table and rows.
parent/main*> call dolt_commit('-Am', 'Created table and initialized sample data');
+----------------------------------+
| hash |
+----------------------------------+
| 4tgceckmgjovuttmpndcqm0vc67v46sv |
+----------------------------------+
1 row in set (0.02 sec) Now, we start a child server. This will be empty. We will push the empty database so parent can clone it and insert a subset of the data.
$ cd child
$ dolt sql-server -port 11112We connect another shell to child. We set up a remote and push.
$ dolt --host localhost --port 11112 --no-tls sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit. "\help" for help.
> use child;
Database Changed
child/main> call dolt_remote('add','origin','file:///Users/timsehn/dolthub/dolt/multidb_audit_log/remote');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.01 sec)
child/main> call dolt_push('origin', 'main');
+--------+----------------------------------------------------------------+
| status | message |
+--------+----------------------------------------------------------------+
| 0 | To file:///Users/timsehn/dolthub/dolt/multidb_audit_log/remote |
| | * [new branch] main -> main |
+--------+----------------------------------------------------------------+
1 row in set (0.03 sec) Back in the parent, we clone the child from remote to modify it. This will create a copy of child accessible as a separate database on the parent server. Note, child needs to know nothing of parent. remote acts as an intermediary.
parent/main> call dolt_clone('file:///Users/timsehn/dolthub/dolt/multidb_audit_log/remote', 'child');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.17 sec)
parent/main> show databases;
+--------------------+
| Database |
+--------------------+
| child |
| information_schema |
| mysql |
| parent |
+--------------------+
4 rows in set (0.00 sec) Now that we have child cloned, we create a replica of our table in child using create table like. Then we use insert select to insert the non-secret rows with ids between 5 and 8.
use child;
Database Changed
child/main> create table t like parent.t;
Empty set (0.01 sec)
child/main*> show create table t;
+-------+------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------+
| t | CREATE TABLE `t` ( |
| | `id` int NOT NULL AUTO_INCREMENT, |
| | `word` varchar(40), |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+------------------------------------------------------------------+
1 row in set (0.01 sec)
child/main*> insert into t select * from parent.t where id >= 5 and id <= 8;
Empty set (0.01 sec)
child/main*> select * from t;
+----+--------+
| id | word |
+----+--------+
| 5 | these |
| 6 | are |
| 7 | public |
| 8 | words |
+----+--------+
4 rows in set (0.00 sec) We then create a Dolt commit on child and push to our remote. This enables child to pull the changes made on the parent machine when it is ready.
child/main*> call dolt_commit('-Am', 'Created table and inserted public data');
+----------------------------------+
| hash |
+----------------------------------+
| ntk8r68maeuc8t1j55sjki1055vju18g |
+----------------------------------+
1 row in set (0.01 sec)
child/main> call dolt_push('origin', 'main');
+--------+----------------------------------------------------------------+
| status | message |
+--------+----------------------------------------------------------------+
| 0 | To file:///Users/timsehn/dolthub/dolt/multidb_audit_log/remote |
| | * [new branch] main -> main |
+--------+----------------------------------------------------------------+
1 row in set (0.02 sec) Before we go, we can see the combined audit log of both databases using a union of dolt_diff_t tables, annotating the database name in the select query. Dolt exposes full queryable audit logs via the dolt_diff_<table> and dolt_history_<table> system tables.
child/main> select 'parent' as db,
to_id, to_word, to_commit, to_commit_date,
from_id, from_word, from_commit, from_commit_date,
diff_type
from parent.dolt_diff_t
union
select 'child' as db,
to_id, to_word, to_commit, to_commit_date,
from_id, from_word, from_commit, from_commit_date, diff_type
from child.dolt_diff_t;
+--------+-------+---------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
| db | to_id | to_word | to_commit | to_commit_date | from_id | from_word | from_commit | from_commit_date | diff_type |
+--------+-------+---------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
| parent | 1 | these | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 2 | are | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 3 | secret | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 4 | words | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 5 | these | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 6 | are | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 7 | public | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 8 | words | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| child | 5 | these | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| child | 6 | are | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| child | 7 | public | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| child | 8 | words | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
+--------+-------+---------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
12 rows in set (0.00 sec) Now, back on child, let's pull to get the changes. This simulates a release of the public data to child. Note, child never has direct access to parent, the transfer is intermediated by remote.
child/main> call dolt_pull('origin', 'main');
+--------------+-----------+------------------+
| fast_forward | conflicts | message |
+--------------+-----------+------------------+
| 1 | 0 | merge successful |
+--------------+-----------+------------------+
1 row in set (0.05 sec)
child/main> select * from t;
+----+--------+
| id | word |
+----+--------+
| 5 | these |
| 6 | are |
| 7 | public |
| 8 | words |
+----+--------+
4 rows in set (0.00 sec)
child/main> select * from dolt_diff_t;
+-------+---------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
| to_id | to_word | to_commit | to_commit_date | from_id | from_word | from_commit | from_commit_date | diff_type |
+-------+---------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
| 5 | these | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| 6 | are | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| 7 | public | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| 8 | words | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
+-------+---------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
4 rows in set (0.00 sec)
As you can see, I have no private records or audit logs of private records.
Now, to show off what would happen if child modified rows so we can produce an interesting distributed audit log, let's make a modification.
child/main> update t set word='make' where id=5;
Empty set (0.01 sec)
child/main*> update t set word='these' where id=6;
Empty set (0.01 sec)
child/main*> update t set word='words' where id=7;
Empty set (0.01 sec)
child/main*> update t set word='different' where id=8;
Empty set (0.01 sec)
child/main*> select * from t;
+----+-----------+
| id | word |
+----+-----------+
| 5 | make |
| 6 | these |
| 7 | words |
| 8 | different |
+----+-----------+
4 rows in set (0.00 sec) We can see these changes in the audit log locally.
child/main*> select * from dolt_diff_t;
+-------+-----------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
| to_id | to_word | to_commit | to_commit_date | from_id | from_word | from_commit | from_commit_date | diff_type |
+-------+-----------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
| 5 | make | WORKING | NULL | 5 | these | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | modified |
| 6 | these | WORKING | NULL | 6 | are | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | modified |
| 7 | words | WORKING | NULL | 7 | public | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | modified |
| 8 | different | WORKING | NULL | 8 | words | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | modified |
| 5 | these | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| 6 | are | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| 7 | public | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| 8 | words | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
+-------+-----------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+WORKING in to_commit means the changes are not Dolt committed, they're still in the "working set". We commit and push these changes like we did before, which will make them available to parent and be part of our combined audit log.
child/main*> call dolt_commit('-am', 'changes to words in child');
+----------------------------------+
| hash |
+----------------------------------+
| v9spltvhgfmtbs4r9rccmok5f04a4nnc |
+----------------------------------+
1 row in set (0.01 sec)
child/main> call dolt_push('origin', 'main');
+--------+----------------------------------------------------------------+
| status | message |
+--------+----------------------------------------------------------------+
| 0 | To file:///Users/timsehn/dolthub/dolt/multidb_audit_log/remote |
| | * [new branch] main -> main |
+--------+----------------------------------------------------------------+
1 row in set (0.03 sec) Now back on the parent, we pull the changes to child, giving us all the data we need to make a full distributed audit log across both parent and child databases.
child/main> call dolt_pull('origin', 'main');
+--------------+-----------+------------------+
| fast_forward | conflicts | message |
+--------------+-----------+------------------+
| 1 | 0 | merge successful |
+--------------+-----------+------------------+
1 row in set (0.15 sec) Finally, the combined audit log is updated. I use the same union on the dolt_diff_t from both databases to produce the combined audit log. It shows the changes done on the child copy.
child/main> select 'parent' as db,
to_id, to_word, to_commit, to_commit_date,
from_id, from_word, from_commit, from_commit_date,
diff_type
from parent.dolt_diff_t
union
select 'child' as db,
to_id, to_word, to_commit, to_commit_date,
from_id, from_word, from_commit, from_commit_date, diff_type
from child.dolt_diff_t;
+--------+-------+-----------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
| db | to_id | to_word | to_commit | to_commit_date | from_id | from_word | from_commit | from_commit_date | diff_type |
+--------+-------+-----------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
| parent | 1 | these | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 2 | are | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 3 | secret | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 4 | words | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 5 | these | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 6 | are | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 7 | public | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| parent | 8 | words | 4tgceckmgjovuttmpndcqm0vc67v46sv | 2025-07-15 22:40:30.247 | NULL | NULL | nphhdh79mfpkqkvqleml6g5baa6n6uku | 2025-07-15 22:25:38.27 | added |
| child | 5 | make | v9spltvhgfmtbs4r9rccmok5f04a4nnc | 2025-07-15 23:15:16.639 | 5 | these | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | modified |
| child | 6 | these | v9spltvhgfmtbs4r9rccmok5f04a4nnc | 2025-07-15 23:15:16.639 | 6 | are | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | modified |
| child | 7 | words | v9spltvhgfmtbs4r9rccmok5f04a4nnc | 2025-07-15 23:15:16.639 | 7 | public | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | modified |
| child | 8 | different | v9spltvhgfmtbs4r9rccmok5f04a4nnc | 2025-07-15 23:15:16.639 | 8 | words | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | modified |
| child | 5 | these | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| child | 6 | are | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| child | 7 | public | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
| child | 8 | words | ntk8r68maeuc8t1j55sjki1055vju18g | 2025-07-15 22:58:58.393 | NULL | NULL | iuh0frcci6rkseqev8g6b9sh4qf6gbrn | 2025-07-15 22:25:29.557 | added |
+--------+-------+-----------+----------------------------------+-------------------------+---------+-----------+----------------------------------+-------------------------+-----------+
16 rows in set (0.01 sec) Summary
As you can see from this example, you can achieve the distributed audit log use case with Dolt primitives. Assembling the joint audit log requires a union query against the dolt_diff_<table> tables in multiple Dolt databases. This necessarily must be done in the highly privileged environment. This is a relatively simple approach and accomplishes the use case.
Pros
- Maintains access control levels.
- Distributed audit log created using a simple SQL query.
- Audit log enforced by the database, not the application.
Cons
- This approach is less resilient to primary key changes. You can produce and audit log but it is harder.
- If you make changes to
parentafter the initial copy you must manually merge changes fromparenttochild.
Conclusion
Distributed audit logs are a common ask from multiple customers. This article shows through example how to achieve a distributed audit log in a multi-permissioned environment. Questions or extension to this use case? Just come by our Discord and ask. We're always happy to help.