Why We Chose JSONL Over SQLite for the Audit Log

When building the local daemon for Iddio, we need a robust way to record every AI agent operation. The default instinct for any structured data is to reach for a database. SQLite is the industry standard for embedded, local-first applications. It offers transactions, indexes, and a powerful query language.

We completely ignore it. For the Iddio audit log (~/.iddio/audit.jsonl), we use best-effort append-only JSON Lines (JSONL).

This decision trades relational query power for portability, write performance, and extreme simplicity. Here is why we embrace the file system over a database for audit logging.

Portability First

Audit logs rarely stay on the machine where they originate. In a real deployment, these logs ship to a SIEM, an S3 bucket, or a centralized logging platform.

If the audit log lives in a local SQLite file, exporting it requires an active process. You have to write an exporter that queries the database, formats the output, and pushes it over the network.

With JSONL, the log is inherently portable. You can ship it anywhere using standard system tools like rsyslog, fluentbit, or simple scripts, without database drivers or custom exporters. The data format is universally understood.

Write Performance Over Read Complexity

Audit logs are heavily skewed toward writes. An active AI agent generates a rapid stream of requests, and the daemon must record each one without introducing latency into the critical path.

Writing an append-only JSONL file is exceptionally fast. We open the file, append a line, and call fsync. There are no indexes to update, no B-trees to rebalance, and no transaction logs to coordinate. The daemon is the sole writer of the file — the CLI never touches it directly, routing writes through the daemon over IPC instead — so an in-process mutex is enough to serialize concurrent appends from different goroutines without interleaving them, and the actual I/O operation is as fast as the disk allows.

The Simplicity of cat and jq

A local daemon should be simple to debug. When an operator wants to inspect what an agent just did, they do not want to connect to a local database socket and write SQL queries.

JSONL makes the Unix pipeline the query engine. cat ~/.iddio/audit.jsonl | jq is a complete query interface. Need to find all break-glass operations? A simple grep or jq filter does the job. There is no ORM, no schema migrations, and no local tooling required beyond standard Unix utilities.

Identity: Random 128-bit Hashes

Every row in the audit log carries a hash field. This is not a content hash. It is a random 128-bit value.

This distinction is deliberate. If we use a content hash, two identical operations occurring within the same millisecond produce a hash collision. By generating a random 128-bit ID, we guarantee uniqueness.

This random identifier serves a specific operational purpose: keyset pagination and stream deduplication. When paginating the log, we use a composite cursor of (timestamp_ms, hash). This stable identifier allows clients to consume the append-only stream reliably.

Accepting the Trade-offs: No Tamper Evidence

This architecture makes explicit trade-offs.

First, there is no prev_hash chaining. The log is append-only by convention, not cryptographic proof. The daemon does not detect or prevent after-the-fact edits to the file. Tamper-evident, verifiable audit is a difficult problem, but it is not how the current system operates. We accept that the local log is best-effort.

Second, JSONL is undeniably harder to query than a database at scale. If you want to aggregate and run complex analytical queries over millions of rows, a flat file performs poorly. But for our single-developer local-daemon model, this is the correct trade-off. Aggregating audit records into a queryable store is a problem for future enterprise architectures, not the shipped local product.

By choosing JSONL over SQLite, we prioritize the realities of how audit logs are generated and moved. We embrace simplicity, maximize write performance, and rely on the file system to do what it does best: store append-only data.