DEV Community

Nic Flemmer
Nic Flemmer

Posted on

An append-only, hash-chained audit log in SQLite, and a script to check it

When a coding agent can ask for production access, the question afterwards is simple: who touched production, with what, and why? Styx answers it with an audit log on your machine. This post is about how that log is built, what it does and doesn't protect against, and a short script you can run to verify it.

What goes in

Every time an agent asks for access, and every time access is granted, used, denied, revoked or expires, one row is written. Connecting or removing a target, changing a policy and opening a pull request are rows too. Each row carries:

  • the actor: you, the system (a policy or a timer), or the agent by name
  • the target, the scope and the duration
  • the agent session and the worktree it came from
  • what triggered it: for a use, the command itself, like $ vercel deploy --prod
  • the policy that decided it, if one did
  • detail such as the agent's reason, and whether you verified with Touch ID, Windows Hello or your password

Before a row is written, anything shaped like a secret is scrubbed out of commands, reasons and labels, so a token an agent pasted into a command line doesn't end up in the log.

Append-only means the database says no

The log is a table in the local SQLite database. Triggers on the table abort any update or delete. Nothing in the app edits a row, and rows are only written through one function, AuditService.append. When a grant is revoked, Styx doesn't change the 'granted' row; it adds a 'revoked' row.

The chain

Each row has a sequence number, the hash of the row before it (prev_hash) and its own hash: a SHA-256 of the previous hash and the row's contents. Change any field of any row, or remove one from the middle, and the chain breaks from that point on.

Verifying it yourself

The app has no verify button yet, but the data is just a table. Export it read-only with sqlite3 (3.33 or later, for -json) and pipe it into Node:

sqlite3 -readonly -json ~/Library/Application\ Support/Styx/styx.db \
  "SELECT * FROM audit_entries ORDER BY seq" | node verify-audit.mjs
Enter fullscreen mode Exit fullscreen mode

With this saved as verify-audit.mjs:

import { createHash } from 'node:crypto';

const chunks = [];
for await (const chunk of process.stdin) chunks.push(chunk);
const rows = JSON.parse(Buffer.concat(chunks).toString('utf8') || '[]');
const camel = (k) => k.replace(/_([a-z])/g, (_, c) => c.toUpperCase());

let prev = '';
for (const r of rows) {
  const row = {};
  for (const [k, v] of Object.entries(r)) if (k !== 'hash') row[camel(k)] = v;
  const canonical = JSON.stringify(row, Object.keys(row).sort());
  const hash = createHash('sha256').update(prev).update('\n').update(canonical).digest('hex');
  if (row.prevHash !== prev || hash !== r.hash) {
    console.log(`Chain broken at entry ${row.seq}`);
    process.exit(1);
  }
  prev = r.hash;
}
console.log(`Chain intact: ${rows.length} entries`);
Enter fullscreen mode Exit fullscreen mode

It prints Chain intact with the number of entries, or the first row where the chain breaks. The database is at ~/Library/Application Support/Styx/styx.db on a Mac, %APPDATA%\Styx\styx.db on Windows and ~/.config/Styx/styx.db on Linux.

What the chain can't do

The chain shows that rows weren't edited one at a time after the fact. It doesn't stop someone who can write to your files from rewriting the whole log and recomputing every hash. If that matters to you, copy the latest hash somewhere else now and then; a later rewrite of the history before it won't match.

It's also a record, not a barrier. Styx is a guardrail, not a sandbox: agents run as you, and one that deliberately goes around Styx's tools isn't stopped by the log or by anything else in it. What the log gives you is a trustworthy account of everything that did go through Styx.

The code is Apache-2.0 at github.com/NicholasFlemmer/styx-app.

Top comments (1)

Collapse
 
peterbuildssecure profile image
Peter •

The verification script is a nice touch, since a chain nobody can check is just a column. Two gaps worth closing. First, a hash chain proves no row was edited or removed from the middle, but it can't detect truncation: delete the last N rows and the remaining chain still verifies, and the script prints "Chain intact" with a smaller count. Copying the head hash somewhere else now and then fixes it, but only if someone remembers, so I'd make it automatic. On every write, or every few minutes, publish (seq, hash) somewhere the app and the agent can't rewrite: a git remote with push-only credentials, an RFC 3161 timestamp, even an email to yourself. Verification then compares the count against the last anchored head, not just the internal links.

Second, the row says "verified with Touch ID", but that field is written by the same process being audited, so it's an assertion. Where the platform allows, have the Secure Enclave or TPM sign the row hash with a key that requires user presence, and store the signature in the row. A process running as you can still write rows, but it can't produce that signature without the biometric prompt, so the field becomes evidence that a second party, the hardware, vouched for it. That's the same split you draw at the end between a record and a barrier, applied to the approval itself.