Audite: instant change feeds for SQLite

Audite uses SQL triggers to add a transactional change feed to any SQLite database. It gives you a totally-ordered audit history of all changes to your data, without touching your application code or running an extra process.

Use cases

• Track who changed what, when
• Restore previous versions of changed rows
• Replicate data to external systems by streaming the change feed

Quick start

Let's add a changefeed to todo.db, a SQLite database with the following schema:

sqlite3 todo.db "CREATE TABLE project (id INTEGER PRIMARY KEY, name TEXT)"
sqlite3 todo.db "CREATE TABLE task (
    name TEXT PRIMARY KEY,
    project_id INTEGER REFERENCES project (project_id),
    done BOOLEAN NOT NULL DEFAULT FALSE)"

1. Install audite on your sytem

   python3 -m pip install audite

2. Enable audite on your database

   python3 -m audite todo.db

Done! From now on, any process can INSERT, UPDATE, and DELETE from your database as usual, and audite's triggers will store the results as change events in the audite_changefeed table. All (and only) committed transactions will appear in the change feed.

Modfying data and querying the change feed

We'll add a project and two tasks...

sqlite3 todo.db "INSERT INTO project (id, name) VALUES (1, 'goals')"
sqlite3 todo.db "INSERT INTO task (project_id, name) VALUES (1, 'try audite'), (1, 'profit')"

cross one task off the list...

sqlite3 todo.db "UPDATE task SET done = TRUE WHERE name = 'try audite'"

and cancel the other:

sqlite3 todo.db "DELETE FROM task WHERE name = 'profit'"

Now let's see what changed:

sqlite3 todo.db "SELECT * FROM audite_changefeed ORDER BY id"

You should get back something like this:

id  source   subject     type             time        specversion  data                                                                                                     
--  -------  ----------  ---------------  ----------  -----------  ---------------------------------------------------------------------------------------------------------
1   project  1           project.created  1669730365  1.0          {"new":{"name":"goals","id":1}}                                                                          
2   task     try audite  task.created     1669730374  1.0          {"new":{"project_id":1,"done":0,"name":"try audite"}}                                                    
3   task     profit      task.created     1669730374  1.0          {"new":{"project_id":1,"done":0,"name":"profit"}}                                                        
4   task     try audite  task.updated     1669730381  1.0          {"new":{"project_id":1,"done":1,"name":"try audite"},"old":{"project_id":1,"done":0,"name":"try audite"}}
5   task     profit      task.deleted     1669730386  1.0          {"old":{"project_id":1,"done":0,"name":"profit"}}                                                        

Event Schema

The event schema follows the CloudEvents spec so that other systems can easily handle events from yours.

• id uniquely identifies the event with a monotonically increasing integer.
• source is name of the database table that changed.
• subject is the primary key of the database row that changed.
• type describes the type of change: *.created, *.updated, or *.deleted.
• time is the Unix time when the change was committed.
• specversion is the verion of the CloudEvents spec in use, currently 1.0.
• data is a JSON snapshot of the row that changed. The data.new object holds the post-change values and is present for *.created and *.updated events. The data.old object holds pre-change values and is present for *.updated and *.deleted events.

Note: Audite stores id and time as integers so that SQLite can store and sort them efficiently, but the CloudEvents spec mandates strings. To query events that conform exactly to the CloudEvents JSON spec, select from the audite_cloudevent view instead of the underlying audite_changefeed table:

sqlite3 todo.db "SELECT cloudevent FROM audite_cloudevent ORDER BY id"

cloudevent                                                                                                                                                                                                                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"id":"1","sequence":"00000000000000000001","source":"project","subject":"1","type":"project.created","time":"2022-11-29T13:59:25+00:00","specversion":"1.0","datacontenttype":"application/json","data":{"new":{"name":"goals","id":1}}}                                                                             
{"id":"2","sequence":"00000000000000000002","source":"task","subject":"try audite","type":"task.created","time":"2022-11-29T13:59:34+00:00","specversion":"1.0","datacontenttype":"application/json","data":{"new":{"project_id":1,"done":0,"name":"try audite"}}}                                                    
{"id":"3","sequence":"00000000000000000003","source":"task","subject":"profit","type":"task.created","time":"2022-11-29T13:59:34+00:00","specversion":"1.0","datacontenttype":"application/json","data":{"new":{"project_id":1,"done":0,"name":"profit"}}}                                                            
{"id":"4","sequence":"00000000000000000004","source":"task","subject":"try audite","type":"task.updated","time":"2022-11-29T13:59:41+00:00","specversion":"1.0","datacontenttype":"application/json","data":{"new":{"project_id":1,"done":1,"name":"try audite"},"old":{"project_id":1,"done":0,"name":"try audite"}}}
{"id":"5","sequence":"00000000000000000005","source":"task","subject":"profit","type":"task.deleted","time":"2022-11-29T13:59:46+00:00","specversion":"1.0","datacontenttype":"application/json","data":{"old":{"project_id":1,"done":0,"name":"profit"}}}                                                            

Handling database schema changes

When your database schema changes, you need to re-run audite for the triggers to pick up the latest fields. It's safe to re-run audite multiple times, including as part of your schema migration scripts or even on app startup.

When your database schema hasn't changed, then re-running audite does nothing. When your schema has changed, then re-running audite rebuilds the triggers to write to the change feed with the latest schema.

Auditing only particular tables

By default, audite tracks all tables in the target database. But you can specify tables to track via the --table argument:

python3 -m audite blog.db --table post --table comment

Dependencies

Audite is a python package with no dependencies. You need Python >= 3.7 to enable audite on a database, but because "enable audite on a database" just means "add some SQL triggers," you don't need Python after the triggers are installed.

Prior Art

• litestream by @benbjohnson makes a convincing case for using SQLite in production.
• supa_audit by @supabase demonstrates how easy change feeds can be in Postgres.
• marmot by @maxpert uses schema introspection and triggers that directly inspired the approach here.