Your coding agent keeps a diary
Sign in<br>Subscribe
TL/DR: OpenCode writes every session, message, and token count to a local SQLite file. A small dlt pipeline moves that into DuckDB, and a marimo notebook tells me what my AI habit actually costs.<br>On Monday, 2026-07-06, Alena Astrakhantseva and Alexey Grigorev did a DataTalksClub session on ingesting agent traces with dlt: pulling the structured logs an AI agent emits into a local DuckDB or cloud lakehouse so you can query them. They pulled from local Claude JSON files and a hosted traces API. My coding agent doesn't have a usage API, but it also keeps a diary on disk. Why not point dlt at that?<br>Where OpenCode hides its traces<br>OpenCode is my terminal coding agent.<br>OpenCode: a terminal coding agent without vendor lock‑in<br>Bring your own model, ditch the subscription.<br>sfrtMartin Seifert
It stores metadata in a local SQLite database, on Windows at %LOCALAPPDATA%/opencode/opencode.db. Four tables carry the interesting stuff:<br>session: one row per session: title, model, agent, tokens, cost, timestamps<br>message: one row per message, with a data JSON blob<br>part: one row per message part (tool calls, text, reasoning)<br>todo: the agent's own todo items per session<br>That looks like a trace: Every prompt, every tool call, every token billed. It just sits there in a format nobody wants to query by hand 😅<br>The dlt pipeline runs locally, not on dltHub: The source is a SQLite file on my machine and the cloud runtime cannot reach my disk without some major VPN setup. I could of course trigger the pipeline automatically (for example on startup) and mirror my local data in some cloud lakehouse, but since I am the only person interested in my traces, I'll keep everything local.
The pipeline<br>The whole thing is a dlt source with four resources, one per table, using a small factory _t because sql_table sources don't accept source-level defaults without a @dlt.resource() wrapper (which I don't really need for this small pipeline):<br>@dlt.source(name="opencode_logs")<br>def opencode_logs_source(db_path: str = DB_PATH):<br>credentials = f"sqlite:///{db_path}"
def _t(name: str, primary_key):<br>return sql_table(<br>credentials=credentials,<br>table=name,<br>write_disposition="replace",<br>primary_key=primary_key,
yield _t("session", "id")<br>yield _t("message", "id")<br>yield _t("part", "id")<br>yield _t("todo", ("session_id", "position"))Each table becomes a dlt resource with write_disposition="replace". Every run is a full refresh: no incremental bookkeeping, no state to corrupt. For a local log I regenerate on demand, replace is the honest choice.<br>Then point the pipeline at a DuckDB file and run:<br>pipeline = dlt.pipeline(<br>pipeline_name="opencode_logs",<br>destination=dlt.destinations.duckdb(DUCKDB_PATH),<br>dataset_name="logs",
load_info = pipeline.run(opencode_logs_source())Why dlt instead of the DuckDB extension sqlite? Because dlt handles schema inference, type coercion, and the SQLite-to-DuckDB hop for free. I describe four tables, dlt deals with the plumbing. When opencode adds a column in the next release, the pipeline picks it up without me touching the code.<br>What the traces say<br>With the data in DuckDB, a marimo notebook reads it directly. marimo is a reactive Python notebook: change a filter, every dependent cell recomputes. The connection is read-only, so the dashboard can never corrupt the load:<br>con = duckdb.connect(db_path, read_only=True)<br>raw_session = con.execute(<br>"""<br>SELECT id, title, agent, model,<br>time_created, cost,<br>tokens_input, tokens_output,<br>tokens_cache_read, tokens_cache_write<br>FROM logs.session<br>WHERE time_created IS NOT NULL<br>"""<br>).df()From there the KPIs write themselves: total cost, session count, input vs output tokens, and cache-read share. Cached tokens are far cheaper than fresh input, so the higher that share, the less each session costs me:<br>cache_pct = (<br>100.0 * total_cache_r / (total_input + total_cache_r)<br>if (total_input + total_cache_r) > 0<br>else 0.0<br>)The charts cover daily cost, a stacked daily token mix (input, cache read, cache write, output), sessions-and-tokens on a dual axis, top models by cost, and the top 15 sessions by cost. That last one is the guilty-pleasure table: which single conversation burned the most money? And was it worth it?<br>Timestamps in opencode are epoch milliseconds. Divide by 1000 before handing them to pandas, or every session lands in the year 56000-something.
Why bother tracing my own agent<br>Two reasons. The obvious one is cost: an AI coding agent bills per token, and without a dashboard I have no idea whether last week cost five dollars or fifty. The second is behavioural. The part and todo tables record how the agent actually worked: which tools it reached for, how it broke tasks down, where it looped: you cannot improve what you cannot see.<br>The difference to Alena's and Alexey's session on Monday is scale: They built for a hosted, multi-user traces API, I built for one developer (me) and one SQLite file. The dlt pipeline barely changes between the two. Swap...