We build governance software — tools that decide whether an infrastructure change should be allowed to deploy. Every decision gets logged locally so you can go back later and figure out why something was allowed, or who signed off on it.
Here's a question that logging by itself doesn't answer though: how do you actually know the log hasn't been quietly edited after the fact?
Turns out lots of systems log events. Way fewer make those logs something you can actually trust.
What we started with
Nothing fancy. One table for decisions. Then separate tables for overrides, approvals, and outcomes, because those felt like different things at the time.
It worked fine for a while, honestly.
Where it started falling apart
The problem showed up once we needed to add a new kind of event. Three tables meant three schemas to touch every time. Drift detection? New table. Evidence re-verification? Another one. It wasn't unworkable, just annoying in a way that kept getting worse.
Nothing stopped a row from being edited directly. If someone flipped an approved flag from false to true straight in the database, nothing would notice or care. The log recorded what happened. It didn't protect any of it.
The queries on top of it had real bugs. A LEFT JOIN between decisions and their follow-up events meant a decision with more than one follow-up got counted twice inside a SUM() — we actually saw a denial-rate report print 101.2%, which, obviously, isn't a real number. Separately, a migration that added a few new columns via ALTER TABLE left every row that existed before that migration with NULL in them, because ALTER TABLE ADD COLUMN doesn't backfill anything. Old records just quietly dropped out of reports that depended on those columns. No error. Just wrong numbers sitting there looking normal.
None of this was exotic. It's what happens to a table that grows one feature at a time without anyone stepping back to look at the shape of it.
What we changed
We merged the three event tables into one append-only history table. Every subsequent thing that happens to a decision — someone requesting an override, someone approving it, drift getting detected — becomes a new row. Nothing ever gets updated in place.
CREATE TABLE governance_history (
history_id TEXT PRIMARY KEY,
record_id TEXT NOT NULL,
history_category TEXT NOT NULL, -- override | approval | outcome | drift
history_action TEXT NOT NULL, -- requested | approved | denied | detected
history_hash TEXT NOT NULL,
prev_history_hash TEXT, -- chains to the previous entry
created_at TEXT NOT NULL
);
Category plus action, instead of one flat event-type string. Every new event kind we've added since — drift, evidence, whatever comes next — just fits into those two columns. Querying "every override-related thing that ever happened" is WHERE history_category = 'override'. No growing list of special-cased event names to keep updating.
Every row hashes to the row before it. history_hash is a SHA-256 of that row's data, and prev_history_hash points at the previous row's hash for the same record — basically the same trick git uses for commits. Touch an old row, and every hash after it stops lining up.
We've been careful to call this tamper-evident, not cryptographically signed, because those are genuinely different claims. What we built tells you whether something changed after the fact. It doesn't prove who was allowed to change it, and it's not a signature. For a governance tool specifically, that distinction felt worth being precise about.
Why any of this matters
If a decision in your audit trail can be edited with nobody the wiser, it's not really evidence anymore — it's just something someone typed. The whole point of a trail like this is that someone with zero context can look at it later and trust it without having to trust whoever ran the system in the first place. A log you can quietly rewrite doesn't get you that. An append-only, hash-chained one does.
A few things we'd tell ourselves earlier
ALTER TABLE ADD COLUMN does not backfill anything. Every row that existed before you ran that migration has NULL in the new column, forever, unless you go write a backfill pass yourself. We found this the annoying way — a report quietly excluding forty-some records and nobody noticing until the numbers looked slightly off.
Watch out for LEFT JOIN combined with SUM(CASE WHEN...). Any join that can multiply rows will inflate a sum sitting on top of it. COUNT(DISTINCT ...) on the actual thing you're counting is usually what you want instead.
A running list of schema migrations needs its own discipline. We added a _MIGRATIONS list early on and then just... forgot to add to it the next few times we touched the schema, because nothing forced us to remember. Cheap fix. The habit was the actual problem.
Say exactly what you built. Tamper-evident and cryptographically signed sound similar and aren't. One tells you something changed. The other tells you who was allowed to change it. Worth being precise, especially if anyone's going to rely on the claim later.
Curious if anyone else has built something like this without pulling in a full ledger/blockchain dependency. Did you land on hash-chaining too, or take a different path entirely?
Top comments (0)