SIMPLE_SQL_AUDIT

Automatic audit trail for database changes. Generates triggers to log all INSERT, UPDATE, DELETE operations with JSON diffs.

View Source

Audit Log Schema

CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY,
    table_name TEXT NOT NULL,
    row_id INTEGER NOT NULL,
    operation TEXT NOT NULL,     -- 'INSERT', 'UPDATE', 'DELETE'
    old_values TEXT,             -- JSON of old row (UPDATE, DELETE)
    new_values TEXT,             -- JSON of new row (INSERT, UPDATE)
    changed_fields TEXT,         -- JSON array of modified fields (UPDATE)
    timestamp TEXT DEFAULT (datetime('now')),
    user_id TEXT                 -- Optional user tracking
)

Examples

Enable Auditing

local
    audit: SIMPLE_SQL_AUDIT
do
    create audit.make (db)

    -- Create audit log table
    audit.create_audit_table

    -- Enable auditing for tables
    audit.enable_audit ("documents")
    audit.enable_audit ("users")

    -- Now all changes to these tables are logged automatically
end

Query Change History

-- Get history for specific document
result := audit.history ("documents", 42)

across result as entry loop
    print (entry.string_value ("operation") + " at " + entry.string_value ("timestamp") + "%N")
    if attached entry.string_value_or_void ("changed_fields") as fields then
        print ("  Changed: " + fields + "%N")
    end
end

View Recent Changes

-- Get all changes in last 24 hours
result := audit.history_since ("documents", "datetime('now', '-24 hours')")

print ("Recent changes: " + result.count.out + "%N")

Examine JSON Diff

-- The old_values and new_values columns contain full JSON snapshots
-- For an UPDATE, you can see exactly what changed:

result := db.query ("SELECT * FROM audit_log WHERE table_name = 'users' AND operation = 'UPDATE' ORDER BY timestamp DESC LIMIT 1")

if attached result.first as entry then
    print ("Before: " + entry.string_value ("old_values") + "%N")
    print ("After: " + entry.string_value ("new_values") + "%N")
    print ("Changed: " + entry.string_value ("changed_fields") + "%N")
end