Articles/Integrations

⬡ Integrations/2026-07-13Advanced

Pour Your Unattended Run Logs into SQLite and Count Failures Across Every Run

Running Antigravity CLI on an unattended schedule leaves a growing pile of JSON Lines logs. Instead of grepping files, I pour them into a tiny SQLite database and answer 'which step fails, and when' with a single query. Here is the ingest script, six diagnostic queries, and six weeks of real numbers.

antigravity⁴³¹ sqlite observability¹⁹ logging³ unattended operations²⁴

✦ Premium Article

An Antigravity CLI task I run unattended had been quietly doing nothing for three days before I noticed. Each run wrote its own JSON Lines log, and the logs/ directory held more than 200 files named by date and task. When I got suspicious and ran grep -l error logs/*.jsonl, all it told me was which files contained the word "error." Which step failed, how often, and at what time of day — to learn that, I still had to open the files one by one and read them with my eyes.

I had kept the logs. I just could not read them. The problem was on the reading side, not the storage side. When you have a few dozen runs, grep is enough. But once you are an indie developer handing chores across several apps to agents, runs climb into the hundreds per week. At that scale you do not need search — you need counting. I was slow to make that switch, and for a while I was melting thirty minutes each morning into reading logs.

Scattered JSON Lines gets harder to read as it grows

JSON Lines — one event per line — is a good format: append-friendly and hard to corrupt. But storing it as "one file per run" cannot answer questions that cut across runs. "Has the dependency-install step gotten flakier since last week?" "Do failures cluster in one time window?" You cannot even phrase those questions at the file level.

grep returns lines; it does not count. grep -c counts occurrences within a single file, but it cannot build a "failures ranked by step" table spanning 200 files. You can get close by stacking awk and sort, but you end up rewriting a one-liner every time the question changes, and it always ends as a throwaway.

What you want is a foundation where you can change the question as many times as you like. Structure each event once, gather them in one place, and then swap only the query to count from a different angle. For that foundation I chose SQLite, precisely because it means standing up no extra service.

The destination is one small SQLite file

"Observability platform" makes people picture an external analytics service, but the logs from runs an indie developer schedules mostly fit inside a single SQLite file. Six weeks, 214 runs, and roughly 19,000 events came out to about 18 MB. No server, no migration machinery — just sqlite3 to read and write.

Split it into two tables: runs, which holds the run-level facts (when, which task, success or failure), and steps, which holds the result of each step inside a run. Keeping the grains separate lets you count run-level and step-level failure rates independently.

Table	Key columns	Role
runs	run_id / task / started_at / status / duration_ms	One row per run. The anchor for aggregation
steps	run_id / seq / step / status / duration_ms / error	Each step in a run. Used to pinpoint where it failed

That is the whole schema. Join the two tables on run_id, and add composite indexes on the task and status columns you will filter on most.

CREATE TABLE IF NOT EXISTS runs (
  run_id      TEXT PRIMARY KEY,
  task        TEXT NOT NULL,
  started_at  TEXT NOT NULL,      -- ISO8601 (UTC)
  status      TEXT NOT NULL,      -- success / failure
  duration_ms INTEGER
);
 
CREATE TABLE IF NOT EXISTS steps (
  run_id      TEXT NOT NULL,
  seq         INTEGER NOT NULL,
  step        TEXT NOT NULL,
  status      TEXT NOT NULL,
  duration_ms INTEGER,
  error       TEXT,
  PRIMARY KEY (run_id, seq)
);
 
CREATE INDEX IF NOT EXISTS idx_runs_task_status  ON runs (task, status);
CREATE INDEX IF NOT EXISTS idx_steps_step_status ON steps (step, status);

✦

Thank you for reading this far.

Continue Reading

What follows includes implementation code, benchmarks, and practical content we hope you'll find useful. This site runs without ads — server and development costs are supported entirely by members like you. If it's been helpful, we'd be truly grateful for your support.

WHAT YOU'LL LEARN

✦A 40-line shell ingest that folds scattered JSON Lines logs into two tables, runs and steps

✦Six diagnostic SQL queries that count which task, which step, and which hour fails — with six weeks of real results

✦Operating rules to grow the database safely: append-only, composite indexes, and a weekly VACUUM

Secure payment via Stripe · Cancel anytime

✦

Unlock This Article

Get full access to the rest of this article. Buy once, read anytime. This site is ad-free — your support goes directly toward keeping it running.

Unlock all articles with Membership →

Fold JSON Lines into runs and steps

Ingest is append-only. Read only the log files you have not ingested yet, and use run_id as a primary key to reject duplicates. This assumes each log is a stream of events shaped like {"event": "...", "step": "...", "status": "...", "ts": "...", "ms": 123, "error": "..."}, including run-start and run-end events. Map the actual field names to your own wrapper — the point is simply "fold an event stream into one runs row and N steps rows."

Fold events with jq, and load through a transaction rather than sqlite3's .import. Making it one file = one transaction means a half-written run never survives a mid-way failure.

#!/usr/bin/env bash
# ingest.sh — load logs/*.jsonl into runs.db (already-ingested files are skipped)
set -euo pipefail
DB="runs.db"
LOG_DIR="logs"
 
sqlite3 "$DB" < schema.sql   # the schema above; IF NOT EXISTS, safe to re-run
 
for f in "$LOG_DIR"/*.jsonl; do
  run_id="$(jq -r 'select(.event=="run_start") | .run_id' "$f" | head -1)"
  [ -z "$run_id" ] && continue
 
  # skip if already ingested (idempotent append)
  exists="$(sqlite3 "$DB" "SELECT 1 FROM runs WHERE run_id='$run_id' LIMIT 1;")"
  [ -n "$exists" ] && continue
 
  # assemble the single run row
  read -r task started status ms < <(jq -rs '
    (map(select(.event=="run_start"))|first) as $s
    | (map(select(.event=="run_end"))|first)  as $e
    | [$s.task, $s.ts, ($e.status // "failure"), ($e.ms // 0)] | @tsv' "$f")
 
  # emit step rows as TSV
  steps_tsv="$(jq -rs '
    [ .[] | select(.event=="step") ]
    | to_entries[]
    | [.value.step, .value.status, (.value.ms // 0), (.value.error // "")] | @tsv' "$f")
 
  {
    echo "BEGIN;"
    echo "INSERT INTO runs VALUES('$run_id','$task','$started','$status',$ms);"
    seq=0
    while IFS=$'\t' read -r step st sms serr; do
      [ -z "$step" ] && continue
      esc="${serr//\'/\'\'}"   # escape single quotes
      echo "INSERT INTO steps VALUES('$run_id',$seq,'$step','$st',$sms,'$esc');"
      seq=$((seq+1))
    done <<< "$steps_tsv"
    echo "COMMIT;"
  } | sqlite3 "$DB"
done
 
echo "ingested: $(sqlite3 "$DB" 'SELECT COUNT(*) FROM runs;') runs"

About 40 lines, and with that the "scattered logs" become "countable data." ingest.sh is idempotent, so calling it at the end of every unattended schedule quietly stacks the day's runs on top.

Count the weak spots with one query

With the foundation in place, all that is left is translating questions into queries. Here are the six I actually run first thing in the morning. Each returns one answer to a one-line question.

First, failure rate by task — a glance at which tasks are unstable.

SELECT task,
       COUNT(*)                                             AS runs,
       SUM(status='failure')                                AS fails,
       ROUND(100.0 * SUM(status='failure') / COUNT(*), 1)   AS fail_pct
FROM runs
GROUP BY task
ORDER BY fail_pct DESC;

Next, among failed runs, rank which step was to blame. This is the aggregation grep could never build.

SELECT step,
       COUNT(*) AS fail_count
FROM steps
WHERE status = 'failure'
GROUP BY step
ORDER BY fail_count DESC
LIMIT 10;

Time-of-day skew falls out in one shot too. Pull the UTC hour into a histogram and you can see whether you are colliding with a rate limit or a third-party maintenance window.

SELECT strftime('%H', started_at) AS hour_utc,
       COUNT(*)                    AS runs,
       SUM(status='failure')       AS fails
FROM runs
GROUP BY hour_utc
ORDER BY hour_utc;

The remaining three are: average duration by step (AVG(duration_ms) grouped by step on steps), the failure-rate delta between the last 7 days and the prior week (split started_at on a week boundary and compare), and the frequency of error strings for the same step (GROUP BY error, most common first). The questions change; the tables do not. You just add one more query.

Three weak spots I actually found

Ingesting 214 runs over six weeks surfaced three things that had been invisible in the grep era.

Weak spot found	Number	Fix
Failures concentrated in the dependency-install step	18 of 31 total failures (58%)	Added a retry and a pinned version to that step
Failures skewed to a specific time window	71% of failures fell in UTC 17:00–19:00	Moved that task's trigger out of the window
The same error string bled into other tasks	An identical 429 message across 3 tasks	Consolidated the shared rate limit into one budget

The second one especially I could never have caught with grep. At the file level it only looked like "fails sometimes"; the skew only rose to the surface once I grouped by hour. With a foundation for counting, "vaguely unstable" turns into "71% between 17:00 and 19:00." That single sentence alone made the next morning's fix land straight.

The compass I keep is less the failure rate itself and more "whether the failures are concentrated in one place or scattered." If they are concentrated, I can fix them. If they are scattered, I take it as a sign that my observation grain is still too coarse.

Operating rules to keep it from breaking

Even a small SQLite file needs a little discipline once you write to it unattended every day. I hold to three rules.

First, stay append-only. Make ingest INSERT-only; never UPDATE or DELETE past runs. Logs are a record of what happened, so rewriting them later erodes trust in the cross-run counts. When retention drops old runs, I do not delete from runs.db itself — I archive to a separate file each quarter and then DETACH.

Second, add the composite indexes from the start. Just having (task, status) and (step, status) keeps GROUP BYs spanning hundreds of runs from feeling like a wait. It works without them while the data is small, but forgetting to add them and slowing down later is the worse trouble.

Third, run VACUUM and PRAGMA optimize weekly. Repeated inserts and deletes fragment the pages. Adding one line to the weekend slot of your unattended schedule keeps the file from bloating needlessly. I write about the storage side of log design separately in "Keeping unattended agent run logs without overflowing the disk," and the pitfalls of reading JSON Lines without breaking on partial lines in "Reading JSON Lines without breaking on partial lines."

# weekly maintenance (add to the weekend slot of your unattended schedule)
sqlite3 runs.db 'PRAGMA optimize; VACUUM;'

Wrapping up

Start by running the ingest.sh above once against the logs/*.jsonl you already have. When it finishes, fire the failure-rate-by-task query. That alone turns unattended runs that were "vaguely unstable" into something you can count.

For me, once I turned logs into aggregates, the morning log-reading all but disappeared. I am still in the middle of improving this, but if it helps someone else who has been sitting on a pile of unattended run logs, I would be glad. Thank you for reading.

Thank You for Reading

Antigravity Lab is ad-free, supported entirely by members like you. We publish practical guides daily with implementation code, benchmarks, and production-ready patterns. If you've found it useful, we'd love to have you on board.