How to Add Tamper-Evidence to an Existing Audit Table

You have an audit log in production. It is a database table the application writes to. The next compliance audit will ask whether the table can be trusted, and the answer right now is no, because anyone with database access could edit any row. This guide walks through adding tamper-evidence to that table without breaking the application or the existing data.

The honest caveat up front: this gets you tamper-evidence from the moment you turn it on, not for any data that was already in the table. The historical entries cannot be retroactively made trustworthy. What you build today proves the integrity of the log going forward.

Photo by Ron Lach on Pexels

Step One: Add Two Columns

The first move is a schema change. Add two columns to the existing audit log table:

ALTER TABLE audit_log
  ADD COLUMN prev_hash CHAR(64),
  ADD COLUMN entry_hash CHAR(64);

prev_hash will hold the SHA-256 of the previous entry's entry_hash. entry_hash will hold the SHA-256 of the current entry's full content. Both are 64-character hex strings.

For existing rows, leave both columns NULL. They will not be retroactively populated. Only new entries from this point forward will have hashes.

Step Two: Compute the Hash at Write Time

When the application writes a new audit entry, it now needs to:

1. Read the most recent entry_hash from the audit_log table.
2. Compute the SHA-256 of the new entry's content (timestamp, actor, action, resource, metadata).
3. Compute the SHA-256 of the previous hash plus the current entry hash to form the new entry_hash.
4. Insert the entry with both hashes populated.

In application code, this looks roughly like:

prev = db.select("SELECT entry_hash FROM audit_log ORDER BY id DESC LIMIT 1")
prev_hash = prev["entry_hash"] if prev else "0" * 64

entry_content = json.dumps({
  "timestamp": now,
  "actor": actor_id,
  "action": action,
  "resource": resource,
  "metadata": metadata,
}, sort_keys=True)

entry_hash = sha256((prev_hash + entry_content).encode()).hexdigest()

db.insert("audit_log", {
  ...existing_fields,
  "prev_hash": prev_hash,
  "entry_hash": entry_hash,
})

The sort_keys=True is important. Without it, the same logical entry can serialize to different JSON depending on the order in which keys were added, and the hashes will not be reproducible.

Step Three: Handle Concurrent Writes

If two writes happen at the same time, both read the same prev_hash, both compute different entry_hash values, and both insert. The chain is now branched, which is the same as broken.

There are two ways to handle this. The simpler one is to take a row-level lock on the most recent entry while reading the hash. In Postgres, that looks like:

SELECT entry_hash FROM audit_log ORDER BY id DESC LIMIT 1 FOR UPDATE

This blocks the second writer until the first one commits. It is correct, and for most audit log volumes it is plenty fast.

The other approach is to serialize all audit writes through a single worker (which is the transactional outbox pattern in disguise). The application writes events to an outbox table, and a single worker drains the outbox in order. The worker handles the hash chain. The microservices.io transactional outbox page covers this design.

For audit log throughputs under a few hundred events per second, the row-level lock approach is simpler and faster to implement. For higher throughputs, the outbox is the right design.

Step Four: Build a Verification Job

Tamper-evidence is useless if nobody verifies the chain. Set up a periodic job (daily or weekly) that walks the audit log in order, recomputes each hash, and confirms that the chain is intact.

prev_hash = "0" * 64
for entry in db.select("SELECT * FROM audit_log WHERE entry_hash IS NOT NULL ORDER BY id"):
  expected = sha256((prev_hash + serialize(entry)).encode()).hexdigest()
  if expected != entry["entry_hash"]:
    alert("audit log tampered at id %s" % entry["id"])
    return
  prev_hash = entry["entry_hash"]

This job catches any modification to a historical entry. If a row's content was edited, the recomputed hash will not match the stored hash. If a row was deleted, the chain breaks at the next entry. If a row was inserted, the chain breaks at the inserted row.

The verification job runs against the live audit log. Schedule it for off-peak hours to avoid contention with active writes. The OWASP logging cheat sheet recommends running these verifications at least daily.

Step Five: Move the Hash Storage Off the Application Database

The chain detects modifications, but only if the verification job can be trusted. If an attacker who edits an audit row also edits the verification job's expected hash, the modification goes undetected.

The next step is to store the chain's tip (the most recent entry_hash) in a separate system that the application cannot easily modify. Options:

• A separate database with strict read-only access from the application and write access only from the verification job.
• An object store with versioning. Each verification run writes the current tip to a new versioned object. Older versions cannot be deleted without admin privileges.
• A managed audit service that handles the immutable storage for you. AWS CloudTrail and Datadog Audit Trail both work for this.

This step is what turns the chained hash from a detection mechanism into an evidence mechanism. With the tip stored externally, you can prove the audit log at any point in time was in a specific state, and any subsequent modification is detectable.

Step Six: Document the Process

Compliance auditors want to see written documentation of how the audit log is protected and verified. Write down:

• The schema (the two new columns and what they hold)
• The hash algorithm (SHA-256)
• The serialization format (sorted-key JSON)
• The verification schedule and process
• The external storage of the chain tip
• The escalation process if verification fails

The documentation should live in a place that the audit team can access (a wiki, a security policy document, an internal compliance portal). The Wikipedia article on SHA-2 is a useful external reference if the auditor asks about the hash algorithm.

What This Approach Still Does Not Solve

A few honest limitations to flag.

Historical entries from before the chain started are not protected. They are still as editable as they always were. The chain only proves integrity from the day it was enabled.

PII inside the audit entries is still there. If the audit log contains personal data, GDPR or CCPA deletion requests will require modifying the log, which breaks the chain. The longer-term move is to keep PII in the application database and only hashes/references in the audit log, but that is a bigger refactor than just adding two columns.

The application database is still a single point of trust. If an attacker compromises the database and the verification job, they can bypass the chain. The full design (separate evidence log in immutable storage) is described in the longer guide at 137foundry.com. The two-column retrofit gets you most of the value with much less work, but it is a stepping stone toward the cleaner design, not a substitute for it.

When the Retrofit Is Enough

For internal compliance and basic security investigation, the two-column retrofit is usually enough. For regulated industries (healthcare, finance, government) the full evidence-log design is what auditors will eventually require.

The retrofit is also the right move when you have a few months until the first audit and not enough time to rebuild the whole audit infrastructure. Adding the columns, the verification job, and the external chain tip can be done in a sprint. The full evidence log architecture is a quarter of work or more.

Either way, the discipline of treating the audit log as an evidence artifact rather than an activity feed starts the moment you decide to add the hashes. From there, the design evolves toward whatever the compliance requirements demand.