How We Designed KeyDog's Tamper-Evident Audit Trail

If you have spent any time around compliance auditors, you know the conversation about audit logs goes one of two ways. Either the log is a first-class part of the system and the auditor moves on to the next item on the list, or the log is an afterthought and the next several hours of your life are about to be very uncomfortable.

When we set out to build KeyDog, the audit trail was not a feature we added at the end. It was the spine of the data model. Everything else — the keys, the doors, the staff records, the fob profiles — is a derived view of an underlying event stream. I want to walk through why we chose that approach and what it looks like in the implementation.

The constraint

The audit log has to do three things at the same time, and these three things are in tension with each other.

1. It has to be honest. Once an event is written, no human user of the application — including system administrators — should be able to modify or delete it without leaving a trace.
2. It has to be queryable at speed. "Who had access to the server room in Q2" is the kind of question facilities directors actually ask, and the answer has to come back in a second, not in a batch job overnight.
3. It has to be exportable in a format an auditor will accept. That usually means CSV with stable column ordering and human-readable values.

The naive ways of doing this all fall apart. A pure append-only blockchain ledger satisfies the first constraint but makes the second one slow and the third one painful. A traditional mutable table is fast and easy to query but does not satisfy the honesty constraint at all. We needed something in the middle.

What we ended up with

The data model has three layers.

Layer one is the events table. Every meaningful state change in the system writes one row here. A row contains: a UUID, a sequence number assigned by a Postgres sequence, the acting user ID, a timestamp from the database (not the application server), the resource type, the resource ID, the action verb, a JSONB payload of the before-and-after delta, and a SHA-256 hash that combines the row's fields with the previous row's hash.

The events table is in a Postgres schema that the application's normal database role does not have UPDATE or DELETE privileges on. The role can INSERT and SELECT. That is it. There is no API path in KeyDog that calls anything other than INSERT against this table, and there is no permission level that lets an end user call anything else either.

CREATE TABLE audit.events (
  id              uuid PRIMARY KEY,
  seq             bigint NOT NULL,
  occurred_at     timestamptz NOT NULL DEFAULT now(),
  actor_user_id   uuid NOT NULL,
  resource_type   text NOT NULL,
  resource_id     uuid NOT NULL,
  action          text NOT NULL,
  payload         jsonb NOT NULL,
  prev_hash       text NOT NULL,
  hash            text NOT NULL
);

REVOKE UPDATE, DELETE ON audit.events FROM keydog_app;
GRANT INSERT, SELECT ON audit.events TO keydog_app;

Layer two is the projected views. The application's normal pages — the list of keys, the door records, the fob profile assignments — are projections built by replaying the event stream into a current-state cache. This cache is in regular tables that can be queried at the speed any normal web request needs. If the cache is ever wrong, it can be rebuilt from the events table without any data being lost, because the events are the source of truth.

Layer three is the daily anchor. Every night, a job computes the hash of the most recent event in the chain and writes it to a separate anchor table that has its own append-only constraint. The anchor is timestamped and signed. If anyone were to somehow tamper with the events table directly — for example, by getting database-level access outside the application — the hash chain would diverge from the anchored history, and any auditor running the verification job would see exactly which event was the first to fail.

This is what "tamper-evident" means. We do not claim to make tampering impossible. We claim that any tampering will be visible to a third party who has the anchor history. That is a defensible posture in front of an auditor, and it does not require us to run our own blockchain.

Why not just use a blockchain

People ask this. The short answer is that a blockchain solves a problem we do not have. A blockchain is useful when there is no trusted party who can hold the anchor. In our case, the customer holds the anchor for their own instance, and they are the trusted party for their own data. Adding a distributed consensus layer would add cost, latency, and operational complexity for no security benefit that is specific to our threat model.

The longer answer is that auditors do not know what to do with a blockchain. They know what to do with a hash chain, a signed anchor, and a CSV export. The hash chain is mathematically equivalent for our purposes and is much easier to explain in a compliance review.

What this gets you as a customer

The practical result of this design is that every facilities team using KeyDog has, by default, an audit log that holds up in a real review. You do not have to enable it. You do not have to configure retention separately. The default is the secure path.

When an event lands in the log, it has:

• The authenticated user who took the action — not a service account, not a "system" placeholder.
• The exact resource that was affected, by stable ID.
• The before-and-after state of the change, captured at the moment of the change rather than reconstructed later.
• A timestamp that comes from the database server, not the user's browser, which removes a whole class of client-side spoofing concerns.

When you go to export the log — for a SOC 2 review, an insurance audit, a board report, or just an internal investigation — you get a CSV with stable columns, in chronological order, with all the supporting context. The export is a query against the events table directly, not a derived report, so the format is identical to what is in the database.

Retention is set per plan. Starter customers get one year. Campus customers get five. Enterprise customers can specify their own retention up to seven years. After retention expires, events are still hash-anchored — the row is dropped, but the hash chain remains continuous because the next row's prev_hash was computed before the deletion. This matters for long-term compliance with statutes that require evidence of unbroken record-keeping.

What we did not build

A few things came up in design that we decided against, and I want to be honest about them.

We do not currently sign exports with a private key from a hardware security module. We sign them with a per-instance Ed25519 key that lives in the application's secrets store. For the threat models our customers face, this is the right tradeoff. If a customer specifically needs HSM-backed export signing, that is a custom Enterprise conversation.

We do not stream events to a third-party SIEM out of the box. We have an API endpoint that lets a customer pull events for ingestion into their own system, and customers using Splunk, Datadog, or similar tools are doing this. We have not built first-party connectors yet because the API is simple enough that the integrations are usually written in an afternoon.

What is next

The audit log is the area of the product I work on most. The road map for next year includes finer-grained retention policies, a saved-search feature for common compliance queries, and a built-in verification job that customers can run themselves to confirm their hash chain is intact. None of these change the core design — they make it easier to live with day to day.

If you are evaluating KeyDog and you want to see the audit pane in detail, the live demo has a fully populated audit trail for a synthetic campus. Or if you have specific compliance requirements you want to talk through, we are happy to.