Your Coding Agent Keeps a Diary

OpenCode, a terminal coding agent, stores session metadata in a local SQLite database, and a dlt pipeline moves that data into DuckDB for analytics. The pipeline, built by Alena Astrakhantseva and Alexey Grigorev in a DataTalksClub session, uses four resources for the session, message, part, and todo tables with write_disposition='replace'. A marimo notebook reads the DuckDB file read-only to display KPIs such as total cost, session count, and cache-read share, helping users understand their AI usage costs.

Infrastructure https://www.sfrt.io/tag/infrastructure/ Your coding agent keeps a diary Every opencode session leaves a trail in a local SQLite file. dlt turns it into analytics you actually read. 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. On Monday, 2026-07-06, Alena Astrakhantseva and Alexey Grigorev did a DataTalksClub https://datatalks.club/?ref=sfrt.io 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? Where OpenCode hides its traces OpenCode is my terminal coding agent. It stores metadata in a local SQLite database, on Windows at %LOCALAPPDATA%/opencode/opencode.db . Four tables carry the interesting stuff: session : one row per session: title, model, agent, tokens, cost, timestamps message : one row per message, with a data JSON blob part : one row per message part tool calls, text, reasoning todo : the agent's own todo items per session 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 😅 The pipeline The whole thing is a dlt source https://dlthub.com/docs/general-usage/source?ref=sfrt.io 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 https://dlthub.com/docs/general-usage/resource?ref=sfrt.io which I don't really need for this small pipeline : python @dlt.source name="opencode logs" def opencode logs source db path: str = DB PATH : credentials = f"sqlite:///{db path}" def t name: str, primary key : return sql table credentials=credentials, table=name, write disposition="replace", primary key=primary key, yield t "session", "id" yield t "message", "id" yield t "part", "id" 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. Then point the pipeline at a DuckDB file and run: pipeline = dlt.pipeline pipeline name="opencode logs", destination=dlt.destinations.duckdb DUCKDB PATH , 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. What the traces say With the data in DuckDB, a marimo https://marimo.io/?ref=sfrt.io 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: con = duckdb.connect db path, read only=True raw session = con.execute """ SELECT id, title, agent, model, time created, cost, tokens input, tokens output, tokens cache read, tokens cache write FROM logs.session WHERE time created IS NOT NULL """ .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: cache pct = 100.0 total cache r / total input + total cache r if total input + total cache r 0 else 0.0 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? Why bother tracing my own agent 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. 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 the source, keep the resources, pick a destination. That's the point of dlt 😜 The full dlt pipeline: → ad-hoc: load the local OpenCode SQLite log into DuckDB for analysis """ dlt pipeline: OpenCode logs SQLite at $LOCALAPPDATA/opencode.db → DuckDB Loads the four user-relevant tables from the OpenCode local metadata DB into a local DuckDB file for offline analysis token usage, session history, todos . Tables loaded: - session — one row per session title, model, agent, tokens, cost, time created - message — one row per message session id, time created, data JSON - part — one row per message part message id, session id, data JSON - todo — one row per todo item session id, content, status, position Data flow: $LOCALAPPDATA/opencode.db SQLite → dlt sql database sqlalchemy → ./opencode logs.duckdb Lives in dlt/local/ because the OpenCode SQLite DB is only on the developer's machine, so this pipeline cannot run on dltHub Runtime. Run from any venv that has dlt sql-database,duckdb installed the dltHub venv at dlt/dltHub/.venv works . """ import os import dlt from dlt.sources.sql database import sql table --------------------------------------------------------------------------- Config --------------------------------------------------------------------------- OpenCode stores its DB under %LOCALAPPDATA% on Windows. Override via env var OPENCODE DB if you sync between machines. DEFAULT DB = os.path.join os.environ.get "LOCALAPPDATA", os.path.expanduser "~" , "opencode", "opencode.db", DB PATH = os.environ.get "OPENCODE DB", DEFAULT DB DUCKDB PATH = os.environ.get "OPENCODE DUCKDB PATH", os.path.join os.path.dirname os.path.abspath file , "opencode logs.duckdb" , --------------------------------------------------------------------------- dlt source --------------------------------------------------------------------------- @dlt.source name="opencode logs" def opencode logs source db path: str = DB PATH : if not os.path.exists db path : raise FileNotFoundError f"opencode SQLite DB not found at {db path}. " f"Set OPENCODE DB env var to override." credentials = f"sqlite:///{db path}" sql table streams via SQLAlchemy fetchmany in chunks of chunk size 50k by default , so memory stays flat regardless of source size. def t name: str, primary key : return sql table credentials=credentials, table=name, write disposition="replace", primary key=primary key, yield t "session", "id" yield t "message", "id" yield t "part", "id" yield t "todo", "session id", "position" --------------------------------------------------------------------------- Main --------------------------------------------------------------------------- def load opencode logs - None: pipeline = dlt.pipeline pipeline name="opencode logs", destination=dlt.destinations.duckdb DUCKDB PATH , dataset name="logs", print f"Source: {DB PATH}", flush=True print f"Destination: {DUCKDB PATH}", flush=True load info = pipeline.run opencode logs source print load info with pipeline.sql client as client: print "\nRow counts:", flush=True for t in "session", "message", "part", "todo" : try: with client.execute query f'SELECT count FROM "{t}"' as cur: n = cur.fetchone 0 print f" {t}: {n:,}" except Exception as e: print f" {t}: ERROR {e}" print "Done.", flush=True if name == " main ": load opencode logs For the marimo dashboard, ask you agent to build it for you 😎