Tracking SQLite table history using a JSON audit log#

I continue to collect ways of tracking the history of a table of data stored in SQLite - see sqlite-history for previous experiments.

Today I decided to try a slightly different approach, using JSON to store changes to a table in an audit log.

I wanted an audit table design that looked something like this:

id	timestamp	row_id	previous_values
1	2023-04-15 12:00:00	1	{“name”: “Old Name”, “age”: 42}
2	2023-04-15 12:00:01	1	{“age”: 37}

Any time a table is updated I want to record a JSON object that contains just the columns that were changed, reflecting their previous values.

I didn’t think it was possible to construct such a JSON object using just the SQL dialect supported by SQLite triggers… but with a lot of assistance from ChatGPT Code Interpreter (which could prototype things for me while I was out walking the dog) I found a combination of SQL function calls that works.

Here’s what I ended up with:

1
CREATE TABLE demo (
2
    id INTEGER PRIMARY KEY,
3
    text_col TEXT,
4
    int_col INTEGER,
5
    float_col FLOAT,
6
    blob_col BLOB
7
);
8
CREATE TABLE audit_log (
9
    id INTEGER PRIMARY KEY,
10
    timestamp TEXT,
11
    row_id INTEGER,
12
    previous_values TEXT
13
);
14
CREATE TRIGGER audit_log_trigger
15
AFTER UPDATE ON demo
16
BEGIN
17
    INSERT INTO audit_log (timestamp, row_id, previous_values) VALUES (
18
        datetime('now'),
19
        NEW.id,
20
        json_patch(
21
            json_patch(
22
                json_patch(
23
                    json_patch(
24
                        '{}',
25
                        CASE
26
                            WHEN OLD.text_col IS NOT NEW.text_col THEN
27
                                CASE
28
                                    WHEN OLD.text_col IS NULL THEN json_object('text_col', json_object('null', 1))
29
                                    ELSE json_object('text_col', OLD.text_col)
30
                                END
31
                            ELSE '{}'
32
                        END
33
                    ),
34
                    CASE
35
                        WHEN OLD.int_col IS NOT NEW.int_col THEN
36
                            CASE
37
                                WHEN OLD.int_col IS NULL THEN json_object('int_col', json_object('null', 1))
38
                                ELSE json_object('int_col', OLD.int_col)
39
                            END
40
                        ELSE '{}'
41
                    END
42
                ),
43
                CASE
44
                    WHEN OLD.float_col IS NOT NEW.float_col THEN
45
                        CASE
46
                            WHEN OLD.float_col IS NULL THEN json_object('float_col', json_object('null', 1))
47
                            ELSE json_object('float_col', OLD.float_col)
48
                        END
49
                    ELSE '{}'
50
                END
51
            ),
52
            CASE
53
                WHEN OLD.blob_col IS NOT NEW.blob_col THEN
54
                    CASE
55
                        WHEN OLD.blob_col IS NULL THEN json_object('blob_col', json_object('null', 1))
56
                        ELSE json_object('blob_col', json_object('hex', hex(OLD.blob_col)))
57
                    END
58
                ELSE '{}'
59
            END
60
        )
61
    );
62
END;
63

64
-- Trying that out
65
INSERT INTO demo (
66
    text_col, int_col, float_col, blob_col
67
) VALUES (
68
    'Hello', 42, 3.14, x'0102030405060708'
69
);
70
UPDATE demo SET text_col = 'World', int_col = 37;
71
UPDATE demo SET float_col = null;
72
UPDATE demo SET blob_col = x'0807060504030201';
73
UPDATE demo SET float_col = 2.71828;

And now:

1
select * from audit_log;

id	timestamp	row_id	previous_values
1	2024-02-27 01:34:50	1	`{"text_col": "Hello", "int_col": 42}`
2	2024-02-27 01:34:50	1	`{"float_col": 3.14}`
3	2024-02-27 01:34:50	1	`{"blob_col": {"hex": "0102030405060708"}}`
4	2024-02-27 01:34:50	1	`{"float_col": {"null": 1}}`

There’s a lot of stuff going on here!

I had to change the design of the previous_values column a bit. Firstly, it turns out you can’t store a binary value in a JSON document - so I invented the following syntax for that instead:

1
{"blob_col": {"hex": "0102030405060708"}}

This means that the blob_col column was changed, and its previous value was the binary value that can be represented in hex as 0102030405060708.

I needed special handling for null values as well, which I did like this:

1
{"float_col": {"null": 1}}

This means that the float_col column was changed, and its previous value was null. I needed to do this because of how the json_patch() function worked, described below.

Building a JSON object representing the changes#

The bulk of the complexity in the SQL above relates to how the JSON string that shows which values were changed is constructed.

It works using a nested sequence of calls to the json_patch() function, which is a SQLite built-in that can combine two JSON objects together.

Let’s consider the inner-most nested call:

1
json_patch(
2
    '{}',
3
    CASE
4
        WHEN OLD.text_col IS NOT NEW.text_col THEN
5
            CASE
6
                WHEN OLD.text_col IS NULL THEN json_object('text_col', json_object('null', 1))
7
                ELSE json_object('text_col', OLD.text_col)
8
            END
9
        ELSE '{}'
10
    END
11
)

This takes the empty object, {}, and merges it with a second object which is either {} (if the old and new values were the same), or a JSON object containing the old value of the text_col column.

There’s a nested CASE statement there too to handle the null case: if the previous value is null we need to store {"text_col": {"null": 1}} instead of {"text_col": null}.

That’s because json_patch() treats null as a value that means the key should not be included - which isn’t what we want here.

This json_patch() unit means we can compare the old and new values and return either an empty object for no-changes or an object containing the column name and the old value.

Nesting those json_patch() calls together is the only way I found of building an object that catches changes made to any of the columns in the table.

An alternative: record the new values#

My first design recorded the old values every time a row was updated. This saves a tiny bit of space - a row that is inserted but never updated doesn’t need any audit log entries - but makes the act of restoring a previous state more complex. You have to start at the most recent state and then apply all the changes in reverse order. Also, if you only have a copy of the audit table it’s not enough information to restore the original table.

Here’s an alternative approach - this time recording the new values every time a row is inserted or updated. This requires an insert trigger in addition to an update trigger:

1
CREATE TABLE demo (
2
    id INTEGER PRIMARY KEY,
3
    text_col TEXT,
4
    int_col INTEGER,
5
    float_col FLOAT,
6
    blob_col BLOB
7
);
8
CREATE TABLE audit_log (
9
    id INTEGER PRIMARY KEY,
10
    timestamp TEXT,
11
    row_id INTEGER,
12
    updated_values TEXT
13
);
14
CREATE TRIGGER audit_log_trigger
15
AFTER UPDATE ON demo
16
BEGIN
17
    INSERT INTO audit_log (timestamp, row_id, updated_values) VALUES (
18
        datetime('now'),
19
        NEW.id,
20
        json_patch(
21
            json_patch(
22
                json_patch(
23
                    json_patch(
24
                        '{}',
25
                        CASE
26
                            WHEN OLD.text_col IS NOT NEW.text_col THEN
27
                                CASE
28
                                    WHEN NEW.text_col IS NULL THEN json_object('text_col', json_object('null', 1))
29
                                    ELSE json_object('text_col', NEW.text_col)
30
                                END
31
                            ELSE '{}'
32
                        END
33
                    ),
34
                    CASE
35
                        WHEN OLD.int_col IS NOT NEW.int_col THEN
36
                            CASE
37
                                WHEN NEW.int_col IS NULL THEN json_object('int_col', json_object('null', 1))
38
                                ELSE json_object('int_col', NEW.int_col)
39
                            END
40
                        ELSE '{}'
41
                    END
42
                ),
43
                CASE
44
                    WHEN OLD.float_col IS NOT NEW.float_col THEN
45
                        CASE
46
                            WHEN NEW.float_col IS NULL THEN json_object('float_col', json_object('null', 1))
47
                            ELSE json_object('float_col', NEW.float_col)
48
                        END
49
                    ELSE '{}'
50
                END
51
            ),
52
            CASE
53
                WHEN OLD.blob_col IS NOT NEW.blob_col THEN
54
                    CASE
55
                        WHEN NEW.blob_col IS NULL THEN json_object('blob_col', json_object('null', 1))
56
                        ELSE json_object('blob_col', json_object('hex', hex(NEW.blob_col)))
57
                    END
58
                ELSE '{}'
59
            END
60
        )
61
    );
62
END;
63

64
CREATE TRIGGER audit_log_insert_trigger
65
AFTER INSERT ON demo
66
BEGIN
67
    INSERT INTO audit_log (timestamp, row_id, updated_values) VALUES (
68
        datetime('now'),
69
        NEW.id,
70
        json_object(
71
            'text_col',
72
            CASE WHEN NEW.text_col IS NULL THEN json_object('null', 1) ELSE NEW.text_col END,
73
            'int_col',
74
            CASE WHEN NEW.int_col IS NULL THEN json_object('null', 1) ELSE NEW.int_col END,
75
            'float_col',
76
            CASE WHEN NEW.float_col IS NULL THEN json_object('null', 1) ELSE NEW.float_col END,
77
            'blob_col',
78
            CASE WHEN NEW.blob_col IS NULL THEN json_object('null', 1) ELSE json_object('hex', hex(NEW.blob_col)) END
79
        )
80
    );
81
END;
82

83
-- Trying that out
84
INSERT INTO demo (
85
    text_col, int_col, float_col, blob_col
86
) VALUES (
87
    'Hello', 42, 3.14, x'0102030405060708'
88
);
89
UPDATE demo SET text_col = 'World', int_col = 37;
90
UPDATE demo SET float_col = null;
91
UPDATE demo SET blob_col = x'0807060504030201';
92
UPDATE demo SET float_col = 2.71828;

The result is an audit_log table containing the following:

id	timestamp	row_id	updated_values
1	2024-02-27 01:51:44	1	`{"text_col": "Hello", "int_col": 42, "float_col": 3.14, "blob_col": {"hex": "0102030405060708"}}`
2	2024-02-27 01:51:54	1	`{"text_col": "World", "int_col": 37}`
3	2024-02-27 01:51:54	1	`{"float_col": {"null": 1}}`
4	2024-02-27 01:51:54	1	`{"blob_col": {"hex": "0807060504030201"}}`
5	2024-02-27 01:51:54	1	`{"float_col": 2.71828}`

Next step: automate it with Python#

The biggest problem with this approach is that writing those SQL triggers is extremely hazardous - I fought through so many not-specific-enough SQL syntax errors getting the above to work.

To make this system workable, the process of writing the triggers needs to be automated. I’ve done that once before for sqlite-history, so I imagine code to build the nested json_patch() triggers here would look somewhat similar.