From JSONL to dashboard with zero infra

JSONL is where you start. It is also where you stop — until you cannot. The upgrade path is staged, with each stage triggered by a clear signal, and each stage reusing what came before.

The four stages: grep + awk → jq + cron → DuckDB → Metabase. The tools are documented at DuckDB, jq, SQLite, and Metabase. The data shape is consistent with the OpenTelemetry logs spec at every stage.

Where this comes from: Marvin’s 2026-07-20 original. Sources verified 2026-07-22 against DuckDB, jq, SQLite, Metabase, and OpenTelemetry docs.

Before you start

You need:

• A JSONL ledger being written by something (A2 — the 1-line observability hook — is the canonical source).
• A working awk, jq, and shell.
• A writable path for the cron summary output (Telegram, Slack, email, file — your choice).

No database server required. No SaaS. No vendor account.

🤖 Let your agent do it (Hermes path): ask Hermes to set up the Stage 1 daily summary.

write a daily cron that reads my agent observability JSONL ledger, posts a 5-line summary to my Telegram chat, and uses jq + awk for the parsing

Hermes knows jq and the Telegram gateway. Verify the cron fires and the summary arrives in Telegram.

Stage 0 — grep + awk

When: < 100 MB of JSONL, < 5 questions/day.

You already have this. From A2:

# slowest call
awk -F'"latency_ms":' '/"kind":"model"/{if ($2+0 > max) {max=$2+0; line=$0}} END{print line}' ~/.cache/agent-obs.jsonl

# today's spend
awk -F'"tokens_in":' '/"kind":"model"/{split($2,a,","); in+=a[1]} END{print "tokens_in: " in}' ~/.cache/agent-obs.jsonl

You need nothing else. cat | grep | wc -l answers 90% of operator questions at this scale.

Stage 1 — jq + a daily cron summary

When: you want one Telegram digest per day, or you are answering the same questions repeatedly.

Add a small script that runs from cron and posts a 5-line summary:

#!/usr/bin/env bash
LEDGER=~/.cache/agent-obs.jsonl
echo "Today:"
echo "  calls:   $(jq -c 'select(.ts|startswith("'$(date -u +%Y-%m-%d)'"))' $LEDGER | wc -l)"
echo "  errors:  $(jq -c 'select(.ts|startswith("'$(date -u +%Y-%m-%d)'")) | select(.ok==false)' $LEDGER | wc -l)"
echo "  tokens:  $(jq -r 'select(.ts|startswith("'$(date -u +%Y-%m-%d)'")) | .tokens_in // 0' $LEDGER | awk '{s+=$1} END{print s}')"
echo "  cost \$:  $(jq -r 'select(.ts|startswith("'$(date -u +%Y-%m-%d)'")) | .cost_usd // 0' $LEDGER | awk '{s+=$1} END{printf "%.4f", s}')"

This is the “no-infra dashboard” pattern: parse JSONL with jq, summarize with awk, post to your messaging gateway (Telegram / Slack / email). Zero infrastructure. Free. Runs forever on cron.

Stage 2 — DuckDB

When: > 1 GB of JSONL, or grep is slow, or you want real SQL queries (“group by model, day”).

DuckDB reads JSONL directly — no import step:

import duckdb
con = duckdb.connect("agent.duckdb")
con.execute("""
    CREATE TABLE obs AS
    SELECT * FROM read_json_auto('~/.cache/agent-obs.jsonl')
""")

# total cost by model, last 7 days
print(con.execute("""
    SELECT model, sum(cost_usd) AS total
    FROM obs
    WHERE ts >= now() - INTERVAL 7 DAY
    GROUP BY model
    ORDER BY total DESC
""").fetchall())

DuckDB is embedded — no server, no daemon, no separate credentials. The .duckdb file is the dashboard.

Stage 3 — SQLite

When: you want concurrent writes (multiple agents appending) and a single queryable file.

SQLite is the canonical answer for “embedded SQL database”:

import sqlite3
con = sqlite3.connect("agent.db")
con.execute("""
    CREATE TABLE IF NOT EXISTS obs (
        ts TEXT,
        run_id TEXT,
        kind TEXT,
        name TEXT,
        ok INTEGER,
        latency_ms INTEGER,
        tokens_in INTEGER,
        tokens_out INTEGER,
        model TEXT,
        cost_usd REAL
    )
""")

# write each event as it happens
con.execute("INSERT INTO obs VALUES (?,?,?,?,?,?,?,?,?,?)", (...))
con.commit()

SQLite handles concurrent writers, indexes, and any analyst tool that speaks SQL. It is the long-term home for your log.

Stage 4 — Metabase

When: non-engineers want to read the dashboard.

Metabase is an open-source dashboard tool that talks to DuckDB, SQLite, Postgres, and friends. Self-hosted or cloud.

# docker run
docker run -d -p 3000:3000 \
  --name metabase \
  -e MB_DB_TYPE=sqlite \
  -e MB_DB_DBNAME=agent.db \
  -v /path/to/agent.db:/agent.db \
  metabase/metabase

Point it at your SQLite file. Build dashboards with a few clicks. Share with the team.

When to STOP upgrading

Stop at the stage that answers your questions. Most agents never need Stage 3, and almost no small agent needs Stage 4. If you find yourself at Stage 4 and your dashboard is mostly empty, drop back to Stage 1.

The graduation rule is simple:

Stage 0 → 1:  "I'm answering the same question 5 times this week"
Stage 1 → 2:  "grep takes > 5 seconds"
Stage 2 → 3:  "I have > 2 writers (multiple agents, multiple processes)"
Stage 3 → 4:  "someone other than me wants to read this"

The dashboard you actually want

Three panels, max:

1. Spend per day, last 14 days                  (line chart)
2. Error rate by tool, last 7 days             (bar chart)
3. Top 5 slowest model calls, last 24 hours     (table)

That is enough. Everything else is a feature you will not use.

Per-platform service mechanism

Stage	Default mechanism
Stage 1 (daily cron summary)	macOS: launchd user agent under ~/Library/LaunchAgents/. Linux: systemd user unit + timer. Docker: no init — restart: unless-stopped in compose + external cron. Termux: termux-services. Native Windows: Task Scheduler. WSL2: treat as Linux.
Stage 2 (DuckDB)	Embedded — no service. Just run the script.
Stage 3 (SQLite)	Embedded — no service. Open/close per write.
Stage 4 (Metabase)	Docker container (metabase/metabase); host it behind a tunnel or VPN.

How to know it is working

The pipeline is working when:

1. Stage 0: awk returns the answer to “what did I spend today” in <1 second on a fresh ledger.
2. Stage 1: the daily summary arrives in Telegram/Slack/email at the cron time and contains five non-empty lines.
3. Stage 2: duckdb agent.duckdb "SELECT count(*) FROM obs" returns the row count.
4. Stage 3: SQLite agent.db opens with no database is locked errors under concurrent writes.
5. Stage 4: Metabase at http://localhost:3000 shows your three panels and shares them with a teammate.

The pipeline is not working if Stage 1’s summary is always empty (your date -u +%Y-%m-%d filter doesn’t match your JSONL ts shape) or if Stage 2 hangs on a multi-GB JSONL load (you need read_json_auto with explicit format='newline_delimited').

Common failures and fixes

Failure	Cause	Fix
Stage 1 summary is always empty	ts shape mismatch (UTC vs ISO vs epoch) or path mismatch.	Print the first JSONL row first: head -1 $LEDGER. Confirm ts is ISO-8601 UTC.
Stage 2 takes 30+ seconds	DuckDB re-parses the full JSONL every query.	Materialize once: keep the .duckdb file, re-run queries against it.
Stage 3 has database is locked	Concurrent writes without WAL mode.	Enable WAL: PRAGMA journal_mode=WAL; after connect.
Stage 4 (Metabase) won’t connect to SQLite	Path inside the container doesn’t match the bind mount.	Bind-mount to the same path inside the container; or use the env vars to point at a Postgres / DuckDB instead.
All stages simultaneously fail	The JSONL is not being written.	Re-check A2 — the hook might not be installed or might not be called.

When not to use this pipeline

• You have <10 MB of JSONL. Stage 0 (awk + grep) is enough; this guide is over-engineering for you.
• You need real-time alerting. This pipeline is post-hoc; add a tail-based alerting daemon or push to OpenTelemetry for real-time.
• You have >100 GB/day. Switch to a proper streaming pipeline (Kafka + ClickHouse, or a managed observability vendor).
• You need cross-process tracing across services. This pipeline is per-process; OpenTelemetry with a context propagator is the right tool.

Updates and breaking changes

Re-run the smoke test after any of these:

• jq major version bump (currently 1.7+). The select and date filter syntax have shifted in the past.
• DuckDB major version bump (currently 1.x). The read_json_auto() function has had minor signature changes.
• Metabase major version bump (currently 0.5x). The Docker env-var names (MB_DB_TYPE, MB_DB_DBNAME) have changed in the past; check the current install guide.
• SQLite upgrade (currently 3.4x). The WAL-mode pragma is stable; the JSON1 extension name has changed in the past.

What you will have at the end

• A four-stage upgrade path with the exact tool for each.
• The graduation rule for moving between stages.
• A three-panel dashboard design that is the actual end-state.
• Sample code for jq, DuckDB, SQLite, and Metabase.

Where to get unstuck

• DuckDB: docs, GitHub issues.
• jq: manual, GitHub issues.
• SQLite: docs, forum.
• Metabase: docs, community forum.
• OpenTelemetry logs spec (the schema your JSONL should mirror when you graduate): spec.

Sources

• DuckDB documentation — used for: the embedded OLAP database, read_json_auto() JSONL loader. Verified 2026-07-22.
• jq manual — used for: the JSON processor, the daily cron summary script. Verified 2026-07-22.
• SQLite documentation — used for: the embedded SQL database, concurrent-write pattern. Verified 2026-07-22.
• Metabase docs — used for: the open-source dashboard, the docker run command, the SQLite connection env vars. Verified 2026-07-22.
• OpenTelemetry logs spec — used for: the event-attribute schema that DuckDB / SQLite / Metabase queries assume. Verified 2026-07-22.

Last verified: 2026-07-22. Adapted from Marvin’s original “A3. From JSONL to Dashboard with Zero Infra” (2026-07-20). Drafter and source-notes author are the same model (MiniMax-M3). Review lineage: pending independent subagent review per the v2.4.0 skill rule for Playbook-category guides — review transcript will be added to the source-notes page when complete.