DEV Community

Alessio
Alessio

Posted on Edited on Originally published at py-forge.com

A Tamper-Evident Audit Log in Python and SQLite: Hash Chains, Verification and Exports

#sqlite #security #compliance #logging

Sooner or later someone asks "who changed this, and can you prove the log wasn't edited?" If your audit trail is a normal table, the honest answer is no. Anyone with write access to the database can UPDATE a row, and nothing will look different afterwards.

A hash chain is the usual way to make edits detectable. Each record stores the SHA-256 of its own contents plus the hash of the record before it. Change any field in any row and its hash no longer matches. Delete a row and the next record points at a hash that isn't there anymore.

This post walks through AuditLog, a single-file Python tool (standard library only) that keeps that kind of chain in SQLite. Everything below was run locally against the shipped file. Only the IDs and timestamps will differ on your machine.

Recording events

Each event has an actor, an action, a resource, a status and arbitrary JSON metadata:

python3 auditlog.py --db ledger.db record \
  --actor user_42 --action document.sign \
  --resource doc_981 --resource-type contract \
  --metadata '{"ip": "10.0.0.7"}'
Enter fullscreen mode Exit fullscreen mode 
{
  "event_id": "9577713c-7243-4e0f-835c-b787e096d48c",
  "timestamp": "2026-10-20T00:21:20.788253+00:00",
  "actor_id": "user_42",
  "actor_type": "user",
  "action": "document.sign",
  "resource_id": "doc_981",
  "resource_type": "contract",
  "status": "SUCCESS",
  "metadata": {"ip": "10.0.0.7"},
  "previous_hash": "0000000000000000000000000000000000000000000000000000000000000000",
  "event_hash": "aa10ac7caf7b5bd18f5af000f4189d899a2d636e8f56672c0042b3d76ff93150"
}
Enter fullscreen mode Exit fullscreen mode

The first record links to an all-zero genesis hash. I added two more: a service creating an invoice, and a denied privilege escalation:

python3 auditlog.py --db ledger.db record --actor svc_billing --actor-type service \
  --action invoice.create --resource inv_2026_114
python3 auditlog.py --db ledger.db record --actor user_7 --action role.grant \
  --resource rbac:admin --status DENIED --metadata '{"reason": "not_in_group"}'
Enter fullscreen mode Exit fullscreen mode

--status accepts SUCCESS, FAILURE or DENIED.

Listing and filtering 

python3 auditlog.py --db ledger.db list
Enter fullscreen mode Exit fullscreen mode 
Retrieved 3 events:
[2026-10-20T00:21:20.788253+00:00] user_42 -> document.sign on doc_981 [SUCCESS] (Hash: aa10ac7caf7b...)
[2026-10-20T00:21:20.843074+00:00] svc_billing -> invoice.create on inv_2026_114 [SUCCESS] (Hash: cb076a346079...)
[2026-10-20T00:21:21.189529+00:00] user_7 -> role.grant on rbac:admin [DENIED] (Hash: bf448e23db4f...)
Enter fullscreen mode Exit fullscreen mode

--actor, --action and --limit narrow it down. For example, list --actor user_7 returns only the denied grant.

Verifying the chain and catching an edit 

python3 auditlog.py --db ledger.db verify; echo "exit $?"
Enter fullscreen mode Exit fullscreen mode 
[PASS] Cryptographic audit chain verified. Zero tampering detected.
exit 0
Enter fullscreen mode Exit fullscreen mode

Now play the insider and make the denied request look approved, straight in SQLite:

UPDATE audit_trail SET status='SUCCESS' WHERE actor_id='user_7';
Enter fullscreen mode Exit fullscreen mode 
[FAIL] Audit chain verification failed! Tampering detected:
  - Tampered Payload at sequence 2 (Event ID: 7039abbf-...). Stored: bf448e23db4f886c..., Computed: 72319ad9d48b1a54...
exit 2
Enter fullscreen mode Exit fullscreen mode

The exit code is the useful part. 0 means intact and 2 means tampered, so verify can run as a nightly cron or CI job that alerts on anything non-zero. Deleting a row from the middle or the start is caught too, and reported as a Broken Link because the next record's previous_hash no longer matches.

How the hash is built

The hash covers a canonical JSON serialisation of every field, including the previous hash:

def compute_hash(self) -> str:
    payload = {
        "event_id": self.event_id, "timestamp": self.timestamp,
        "actor_id": self.actor_id, "actor_type": self.actor_type,
        "action": self.action, "resource_id": self.resource_id,
        "resource_type": self.resource_type, "status": self.status,
        "metadata": self.metadata, "previous_hash": self.previous_hash,
    }
    # serialised with sorted keys, then SHA-256
Enter fullscreen mode Exit fullscreen mode

Sorted keys keep the bytes stable, so the same event always gives the same hash, on any machine.

Appending is done in one SQLite write transaction (BEGIN IMMEDIATE). The tool reads the current tip and inserts the new record before another writer can get in. I ran 8 processes writing 40 events each into the same file at the same time. The result was 320 rows, and verify passed. On this box, 500 sequential writes from Python took about 0.3 s.

Using it from Python 

from auditlog import AuditLogger

audit = AuditLogger("ledger.db")
audit.record("user_42", "user", "invoice.refund", "inv_2026_114", "invoice",
             "SUCCESS", {"amount_cents": 4900})
ok, problems = audit.verify_integrity()
Enter fullscreen mode Exit fullscreen mode

Exporting for a review 

python3 auditlog.py --db ledger.db export --out audit.csv --format csv
python3 auditlog.py --db ledger.db export --out audit.jsonl --format jsonl
Enter fullscreen mode Exit fullscreen mode

Both include previous_hash and event_hash, so a reviewer can re-check the chain without your database.

Limitations

  • Tamper-evident, not tamper-proof. The hashes are plain SHA-256 with no secret key. Someone with write access to the file and this source code could rewrite a row and recompute every hash after it. The standard defence is anchoring: copy the latest event_hash somewhere the database admin can't edit, like a daily line in a separate log, a ticket or an object-lock bucket. Then compare it during review.
  • Deleting the newest records isn't detectable from the chain alone, because nothing points forward to them. Anchoring the tip solves this as well.
  • It's SQLite, so all writers must share one database file on a local disk. It isn't a multi-node log service.
  • There's no built-in date-range filter on list. Use the export, or query the audit_trail table directly.

FAQ

Does this make me SOC 2 / ISO 27001 compliant? No tool does that by itself. It gives you an append-only audit trail with integrity checks and exports, which is evidence auditors commonly ask for.

Can I add my own fields? Put them in metadata. It's part of the hash.

What Python version? Python 3.9+ and the standard library. There's nothing to pip install.

AuditLog is a one-time purchase that includes the source and README: get AuditLog ($29). More details are on the AuditLog product page.

Top comments (0)