Tamper-evident audit logs with hash chains in Postgres
An audit log in a table anybody can write to proves nothing. Whoever could do the thing you are auditing can usually also edit the row that records it, and an auditor asking "how do you know this is complete" has no answer.
A hash chain changes that. Each entry includes the hash of the previous one, so altering or deleting any entry invalidates every entry after it, and the mismatch is detectable. It is about forty lines, and the subtleties are in verification, keying and pruning rather than in the chaining.
17 min read. Commands and output last run against Grit v3.560.0.
What you will have at the end
An append-only log whose integrity you can check with a command.
A keyed hash, so somebody with write access cannot simply recompute the chain.
A pruning strategy that does not destroy the property you built it for.
Tamper-evident, not tamper-proof
Be precise about the claim, because it is the first thing a reviewer will test. A hash chain does not prevent anybody from changing the data. Somebody with database access can rewrite any row they like. What they cannot do is leave the chain consistent while doing it, unless they also hold the key and recompute everything after it.
So the property is: a change is detectable by anybody who runs the verification.
- It defends against: a quiet
UPDATEto make an action look different, aDELETEof an inconvenient entry, and an insert backdated into the middle of the history. - It does not defend against: an attacker who holds the key and has time to recompute the whole chain, nor against something that was never written down in the first place.
- It is worth having anyway, because it converts "we trust the table" into "here is a check that passes", which is the difference between an assertion and evidence.
The table
CREATE TABLE audit_entries (id bigserial PRIMARY KEY,occurred_at timestamptz NOT NULL DEFAULT clock_timestamp(),actor_type text NOT NULL, -- user | system | api_keyactor_id bigint,actor_label text, -- who this was AT THE TIMEaction text NOT NULL, -- invoice.voidentity_type text NOT NULL,entity_id bigint,org_id bigint,changes jsonb, -- before and after, redactedip inet,request_id text,prev_hash bytea NOT NULL,hash bytea NOT NULL);CREATE INDEX ON audit_entries (entity_type, entity_id, id DESC);CREATE INDEX ON audit_entries (actor_id, id DESC);CREATE INDEX ON audit_entries (org_id, occurred_at DESC);
actor_label is denormalised on purpose
Joining to users to render the log shows who that person is now. The audit question is who they were then: their name, their role, their email at the moment of the action. A user renamed or deleted since must not change the history, so the label is copied in and never updated.
Writing a chained entry
func (a *Auditor) Write(ctx context.Context, tx *gorm.DB, e Entry) error {// The write takes the caller's transaction, so the audit entry and// the thing it describes commit together. An audit log written// outside the transaction records actions that were rolled back,// and misses actions that succeeded after it failed.// SELECT ... FOR UPDATE serialises appenders. Without it, two// concurrent writes read the same prev_hash and the chain forks.var prev []byteerr := tx.Raw(`SELECT hash FROM audit_entries ORDER BY id DESC LIMIT 1 FOR UPDATE`).Scan(&prev).Errorif err != nil {return fmt.Errorf("read chain head: %w", err)}if prev == nil {prev = genesis // a fixed, documented value for the first entry}row := models.AuditEntry{ /* ... */ PrevHash: prev}row.Hash = a.digest(prev, row)if err := tx.Create(&row).Error; err != nil {return fmt.Errorf("append audit entry: %w", err)}return nil}
// HMAC, not a bare hash. A plain SHA-256 chain can be recomputed by// anybody who can read the rows, so somebody with write access can// alter an entry and rewrite every hash after it. With a key they do// not have, they cannot.//// The key lives wherever your other secrets live, and must NOT be in// the same database as the log: a single compromise should not yield// both the rows and the means to rewrite them.func (a *Auditor) digest(prev []byte, e models.AuditEntry) []byte {mac := hmac.New(sha256.New, a.key)mac.Write(prev)// A canonical, unambiguous encoding. Concatenating fields without// separators lets two different entries produce the same input:// ("ab", "c") and ("a", "bc") are the same bytes. Length-prefixing// or a delimiter that cannot appear in the values prevents that.writeField(mac, e.OccurredAt.UTC().Format(time.RFC3339Nano))writeField(mac, e.ActorType)writeField(mac, strconv.FormatInt(e.ActorID, 10))writeField(mac, e.Action)writeField(mac, e.EntityType)writeField(mac, strconv.FormatInt(e.EntityID, 10))writeField(mac, string(canonicalJSON(e.Changes)))return mac.Sum(nil)}func writeField(w io.Writer, s string) {binary.Write(w, binary.BigEndian, uint32(len(s)))io.WriteString(w, s)}
Hash what the database will store, not what you have in memory
Postgres stores timestamptz at microsecond precision and MySQL at millisecond. A Go time.Time has nanoseconds. Hash the nanosecond value, read the row back, recompute, and the hash differs, so the chain fails verification on data nobody touched. Round or truncate to what the column stores before hashing, and verify on the database you actually run rather than on SQLite.
Verifying
func (a *Auditor) Verify(ctx context.Context, from, to int64) (*Report, error) {var prev []byterep := &Report{}rows, err := a.db.WithContext(ctx).Model(&models.AuditEntry{}).Where("id BETWEEN ? AND ?", from, to).Order("id").Rows()if err != nil {return nil, fmt.Errorf("scan entries: %w", err)}defer rows.Close()for rows.Next() {var e models.AuditEntryif err := a.db.ScanRows(rows, &e); err != nil {return nil, fmt.Errorf("scan entry: %w", err)}if prev != nil && !bytes.Equal(e.PrevHash, prev) {// A gap: an entry was deleted, or one was inserted here.rep.Breaks = append(rep.Breaks, Break{ID: e.ID, Kind: "chain"})}if !hmac.Equal(a.digest(e.PrevHash, e), e.Hash) {// The row's own contents were altered.rep.Breaks = append(rep.Breaks, Break{ID: e.ID, Kind: "content"})}prev = e.Hashrep.Checked++}return rep, rows.Err()}
Two kinds of break, and they mean different things. A content break says this entry was edited. A chain break says something was removed or inserted between two entries. Reporting them separately is what makes the output actionable rather than just alarming.
Verifying at scale
Forty million entries is not a verification you run on every request. Three things make it practical:
- Checkpoints. Every ten thousand entries, store the hash in a separate table. Verification then runs from the last checkpoint rather than from the beginning.
- Nightly, incremental. Verify only what has been added since the last run, and keep the result.
- Publish the head. Write the current head hash somewhere outside the system: a log sent to a different provider, or an email to the compliance inbox. An attacker who controls your database cannot alter what already left it, which upgrades the claim considerably.
Making the table append-only
The chain detects changes. Stopping them in the first place is a separate, cheap control.
-- The application's role may insert and read, and nothing else.REVOKE UPDATE, DELETE, TRUNCATE ON audit_entries FROM app_user;GRANT INSERT, SELECT ON audit_entries TO app_user;-- And a trigger, which also catches a superuser doing it by hand.CREATE OR REPLACE FUNCTION audit_is_append_only() RETURNS trigger AS $$BEGINRAISE EXCEPTION 'audit_entries is append-only';END;$$ LANGUAGE plpgsql;CREATE TRIGGER audit_no_update BEFORE UPDATE OR DELETE ON audit_entriesFOR EACH ROW EXECUTE FUNCTION audit_is_append_only();
Together these are better than either alone: the grants stop the application, the trigger stops an interactive session, and the chain detects anybody who got past both by dropping the trigger.
What to record, and what not to
- Record: authentication events including failures, permission denials, role and permission changes, every write to anything financial or personal, exports and bulk downloads, administrative impersonation, and configuration changes.
- Record reads too, selectively. "Who looked at this customer's record" is a question that gets asked, usually at the worst moment, and it cannot be answered retroactively. Auditing every read is too much; auditing reads of the sensitive entities is not.
- Never record: passwords, tokens, session identifiers, full card numbers, or anything you would have to delete later for a subject access request.
Redact before hashing, not after
If a value must be removable, it must never enter the hash, because removing it later breaks the chain. Hash a redacted form from the start, or hash a reference to the value rather than the value. Designing this in is easy; retrofitting it means choosing between the regulation and the chain.
Pruning without breaking it
Retention policies and deletion requests collide with a chain by construction: removing an entry breaks every hash after it.
Segment the chain
One chain per period, each sealed when the period ends:2026-09 [genesis → ... → head] sealed, head published2026-10 [genesis → ... → head] sealed, head published2026-11 [genesis → ... → current]Dropping all of 2026-09 removes a whole segment and leaves every othersegment verifiable. The cost: you can no longer prove that the Septembersegment ever existed, which is exactly what deleting it means.
Tombstone rather than delete
For a personal-data erasure request, replace the fields with a marker and record the erasure as its own audit entry. The chain stays intact because the entry still exists, the personal data is gone, and the fact of the erasure is itself auditable, which is usually what the regulation wants.
This only works if the hash covered the redacted form from the beginning, which is the previous section's point and the reason it is worth deciding early.
What an auditor actually asks
The technical property is the easy part. These are the questions that follow, and a hash chain only answers the first one.
- "How do you know the log is complete?" The chain, plus a published head.
- "Who can modify it?" The grants and the trigger. Show that the application role cannot.
- "How would you know if somebody did?" The nightly verification and what it alerts to. An unmonitored verification is not a control.
- "How long is it kept, and how is it disposed of?" The segmentation, and the job that enforces it.
- "Show me every action by this user last quarter." Which is a question about indexes and a screen, and is the one that actually gets asked most often.
Generated
# The chain is scaffolded into every project. Verification is an# endpoint rather than a command, so a scheduled job or a monitor can# call it:## GET /admin/activity/integrity$grit start
The table, the HMAC-keyed chain, the append-only grants and trigger, the verification endpoint, the checkpoints and the admin screen are all scaffolded, with the audit write taking the caller's transaction so an entry cannot exist for a rolled-back action.
The key is read from the environment and is deliberately not stored alongside the log, for the reason above: the chain is only worth something if altering it requires something the database does not contain.
The audit log reference covers the generated schema, the verification and retention.
Frequently asked questions
- Is this a blockchain?
- It is the one idea blockchains borrowed, a chain of hashes, without the part that makes them blockchains, which is distributed consensus about what the chain contains. You have one writer and trust your own database, so consensus solves a problem you do not have. Calling it a hash chain keeps the conversation honest.
- Why HMAC rather than a plain SHA-256?
- Because a plain hash can be recomputed by anybody who can read the table. An attacker with write access edits an entry and rewrites every hash after it, and the verification passes. With a key they do not hold, they cannot produce valid hashes, so the tampering is detectable. Keep the key outside the database.
- Does the chain hurt write throughput?
- Yes, by design: appending requires reading the current head under a lock, so audit writes serialise. In practice a few thousand per second on Postgres, which is far more than most applications audit. If it becomes a limit, segment per tenant so each tenant has its own chain and the lock is per tenant.
- Should the audit log live in a separate database?
- It is stronger, and it costs you the ability to write the entry in the same transaction as the action, which reintroduces the dual-write problem: an action committed with no audit entry, or an entry for something that rolled back. The usual compromise is the same database, with the restricted role and the trigger, plus shipping entries to a separate store asynchronously for the defence-in-depth copy.
Next steps
- Policies and row rulesThe permission denials worth recording, and why they are worth recording.
- The OWASP Top 10 in GoLogging and monitoring failures, which is this subject on the list.
- Audit log referenceThe generated schema, verification command and retention.
- Append-only tablesThe grants and triggers, as generated.
