A Single-File Log Database: Pipe Logs In, Query Them With SQL, Hand the File to Anyone
The incident is over, the postmortem is Thursday, and someone wants the logs from the bad three hours. You can attach a gzip nobody will open, paste a screenshot of a grep, or stand up a search cluster to answer one question. The question is usually “which paths returned 500, and when did it start”, which has a GROUP BY hiding in it, and a GROUP BY is where grep runs out. A full observability stack answers it, but that’s a heavy answer for a team that doesn’t already run one.
The gap worth filling is an artifact. One file, compressed and indexed, that carries its own schema and a dictionary of its own message templates, answers SQL on any machine, and attaches to a ticket the way a PDF does. The command that writes it and the command that reads it are the same small static binary: one binary, one file, no daemon. It’s the trade people make when they pick a static site generator over a CMS, with nothing to keep running. The command name below is a placeholder, because this is a proposal and not a product.
Parts of it exist already. lnav reads log files, detects their formats and lets you run SQL over the lines through SQLite. angle-grinder aggregates on the command line with a pipeline query language. For JSON lines, DuckDB queries the file directly and sqlite-utils loads it into SQLite with one command. VictoriaLogs is a lean log database that ships as a single binary, while Loki and Quickwit are lighter than Elasticsearch but remain services you run. Each is good at its job.
What none of them hands you is the portable object. lnav works on your raw files in place, so you end up sending the raw files, and the servers keep their data in their own storage layout, which doesn’t attach to a ticket. That’s a narrow gap, and a newcomer should be honest about how narrow.
What Goes Inside the File
Ingest starts with format detection. Buffer the first couple of hundred lines of stdin, try each known parser, and keep the one that matches the most. Each format is a small declarative parser (a pattern with named groups and a type for each), so adding one is a config entry. The first release needs five: nginx combined, JSON lines, logfmt, and syslog in its RFC 3164 and RFC 5424 shapes. Lines that match nothing go into a raw column tagged unparsed. Dropping a line silently is the one thing the tool must never do.
Each format maps onto typed columns: status and bytes as integers, path as a string, ts as UTC nanoseconds. The free-text message goes through a template step. Mask the variable tokens, look the result up in a dictionary of templates (a Drain-style tree does this online, and the post on reducing logs before shipping them covers the mechanism), and store a template ID plus the variable values instead of the line. A million copies of payment gateway timeout after <num>ms host=<str> become a million small integers and a column of numbers, which compress very well. The Logs part of Precomputing uses the same building block: it learns templates with the Drain algorithm and keeps raw lines locally for 48 hours.
Rows go into segments of a fixed row count. Inside a segment every column is its own zstd-compressed block, so a query that touches status and path never reads the message column. Each segment carries a zone map: minimum and maximum timestamp, minimum and maximum of each numeric column, and the set of template IDs it contains. The footer at the end of the file holds the schema, the template dictionary and the segment directory.
Queries get cheap from there. A time range prunes segments through the zone maps. A predicate on message text, like message LIKE '%timeout%', runs against the template dictionary, which is tiny next to the lines, and turns into a set of template IDs, so no line is scanned as text. Only what survives gets decompressed.
$ cat access.log.1 access.log | logdb ingest incident.logdb
format nginx_combined (99.98% of lines matched)
unparsed 412 lines, kept raw
lines 2,481,903 templates 38 span 2026-10-04T22:00:01Z .. 2026-10-05T01:00:00Z
wrote incident.logdb
$ logdb query incident.logdb "SELECT path, count(*) AS hits, min(ts) AS first_seen
> FROM logs WHERE status >= 500 GROUP BY path ORDER BY hits DESC LIMIT 3"
path hits first_seen
/api/v2/checkout 3114 2026-10-04T23:41:07Z
/api/v2/cart 1207 2026-10-04T23:41:09Z
/healthz 61 2026-10-05T00:12:55Z
That output is mocked up. tail and stats (templates by count) finish the command set.
An obvious objection is that a plain SQLite file would open with sqlite3 anywhere, and the recipient wouldn’t need your binary at all. That’s a real advantage, and the cheap version of this tool is exactly that, with a template table and whole-file zstd for transport. What it gives up is size and scan speed, since row storage with the indexes you’d want on time and status tends to come out bigger and slower than columnar blocks, and logs are written once and read in bulk. So the custom format should win on size and speed, and it loses on reach. Cover the reach with an export --sqlite command and a single static download.
Where Log Files Fight Back
Multiline entries come first. A Java stack trace can run to dozens of lines that belong to one entry; line-at-a-time ingest shreds it into dozens of rows and separates the ERROR line from its Caused by. Each format needs an entry-start rule (a line that begins with a timestamp starts a new entry, anything else continues the last one), and unknown formats need a heuristic: lines that start with whitespace, at , Caused by: or Traceback continue the previous entry. Cap the entry size, because one unterminated entry will otherwise eat the file.
Timestamps are the most dangerous field. Classic syslog lines have no year and no time zone, so you infer the year from the file’s modification time and roll it forward when the month number goes backwards, as it does across New Year. Application logs in local time carry no offset, and when clocks go back an hour repeats, so the same wall-clock time appears twice and the file stops being monotonic. Store UTC, take a --tz option for sources with no offset, print what was inferred at ingest, and keep the original string whenever the inference was a guess. Refusing to guess silently is most of the feature.
Mixed formats are next. A Docker stream or a journal export interleaves them, and the first 200 lines don’t speak for the rest, so fall back to per-line detection, tag each row with its format and report the mix at the end of ingest.
Someone will run a query while ingest is still writing, because that’s what incident rooms do. Append a new segment, then a new footer with a checksum, and readers use the last footer whose checksum verifies. A crash mid-append leaves an unreferenced tail that does no harm.
The SQL subset is where scope creeps. Joins, subqueries and window functions are each a week of work and a stream of bug reports. Pick a core (SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, five aggregates, a time-bucket function) and make every other error useful: “joins aren’t supported; run export --sqlite and use any SQLite client”. The escape hatch to a full engine is what lets the subset stay small.
Last, version the format from the first byte: a magic number, a format version, a list of required features and a list of optional ones. A reader refuses a file with a required feature it doesn’t know and ignores unknown optional ones. Keep a golden file from every release in the test suite. SQLite’s project says it intends to support its file format through 2050; you won’t promise that, but an incident file attached to a ticket should still open in two years.
Version 0.1 Does Less Than You Want
Five formats, stdin and file ingest, one output file, the small SQL subset, template dedupe on the message column, zstd blocks and an export --sqlite escape hatch.
It refuses a server, alerting, a network listener for syslog, distributed search and a web UI. If you catch yourself adding a daemon, you’ve started building one of the products above, and those exist. A closed incident file is worth keeping as a file, but data you want for a year belongs in rollups, which is the job of a telemetry database that forgets on purpose.
If the next person can query the file without asking you anything, it works.