Ledgerly: Teaching the Chat to Query Its Own Database

Part one's chat answered every question from two months of category totals stuffed into a single-turn prompt, and it couldn't run one query against the database sitting right there.

Part two gives it ten tools, a hand-rolled MCP server, and permission to take twelve turns instead of one.

Getting there meant a de-risking spike against the real CLI, a server built by hand because of a dependency conflict I hadn't expected, and three review passes that found nineteen things wrong before I'd call it done, and then an epilogue that had nothing to do with any of it.

A Chat With Two Months and No Way to Ask More#

Part one covered the fix for the chat's worst bug: it once told me, with total confidence, that its records only covered July and August, when the database actually held ten months of committed transactions.

A coverage line fixed that, stating the earliest and latest month on record. It didn't fix the underlying design.

The chat's context was still a static blob assembled once per turn: the current month and the prior month's category totals, plus the five biggest merchants, serialized into the prompt before a single-turn call to the local claude CLI.

Every AI call in Ledgerly used --max-turns 1. Part one's adapter shipped that way on purpose, and for column mapping or categorization, one turn is the right answer.

A chat is different. Ask it something the blob didn't happen to contain, "list my recurring expenses" being the question that started this detour, and the honest answer was "I don't have enough detail loaded here," not wrong exactly, just an answer built from a hand-picked slice instead of the actual ledger.

The goal for this pass was narrow on paper and wide in practice: make every question answerable against all of the app's data, not whatever summary happened to land in the prompt that turn.

That meant the chat needed to run its own queries, which meant it needed tools. Something on the other end of those calls had to speak a protocol I hadn't touched before.

A Spike Against the Real CLI, Before Any Real Code#

Before writing anything the app would ship, a planning agent produced a phased plan (docs/plans/chat-tools-plan.md, 382 lines). Phase 0 was a spike: a 60-line stub MCP server against the real claude CLI, version 2.1.228, before committing to an architecture built on what I assumed it did.

Good thing, too. The CLI calls an undocumented server/discover JSON-RPC method before initialize, nowhere in the MCP spec. A server that throws on an unknown method, or exits, never reaches the handshake at all.

Two more findings shaped everything downstream. Without an explicit --tools "", the CLI defers our MCP tool schemas, and the model burns a whole turn calling ToolSearch before it can use anything of ours, real cost and latency for zero benefit in a product where the tool list never changes.

And without --setting-sources "", the CLI loads the author's own global settings: my SessionStart hook fired inside a finance chat and injected text that belonged nowhere near it. That one wasn't theoretical. I watched it happen in the spike output.

The spike also measured cost and caught a three-second stdin stall on every call, both covered shortly.

Phase 0 also produced real NDJSON streams, captured from the CLI and committed as test fixtures: tool-events.ndjson, a clean run, terminal subtype: "success", exit code 0, and max-turns-error.ndjson, capturing a hard turn limit on the wire, subtype: "error_max_turns", exit code 1. I wrote the stream parser against those fixtures, not against memory of how I assumed the protocol worked.

Why I Hand-Rolled an MCP Server Instead of Reaching for the SDK#

The obvious next step was the official @modelcontextprotocol/sdk. Why not just use it?

The SDK pins zod v3, and Ledgerly's validation layer, all the way through the import pipeline from part one, runs on zod 4. Pulling in a second major version of the same library for one feature wasn't a trade I wanted to make.

Especially once I looked at what the CLI, acting as the MCP client, actually needed: initialize, tools/list, tools/call. Three methods and a discover handshake, not the full surface a general-purpose SDK is built to cover.

So I hand-rolled the server myself, and kept it small: roughly 250 lines across four files. I kept src/lib/mcp/jsonrpc.ts narrow: it handles newline-delimited JSON-RPC 2.0 framing, one request per line, and knows nothing about MCP semantics.

src/lib/mcp/protocol.ts is the method dispatcher, negotiating a protocol version across the three the CLI understands and defaulting to the latest, 2025-06-18. src/lib/mcp/tools.ts is the tool registry: zod validation, JSON serialization, a payload cap.

I wired src/lib/mcp/db.ts to open a read-only connection. An entry point at scripts/mcp-server.ts, spawned through tsx, requires LEDGERLY_DB_PATH and fails loudly without it.

Two processes now touch the same database file during a chat turn: the Next.js dev server holds its own long-lived WAL-mode connection, and each MCP server invocation opens and closes a short-lived connection of its own.

I didn't rely on an OS-level readonly flag for that connection. db.ts opens a normal handle and sets query_only = ON at the SQLite level instead, alongside a 5000ms busy_timeout so a moment of lock contention doesn't turn into an immediate failure. foreign_keys = ON matches how the rest of the app connects.

I also made it fail fast if the file it's pointed at doesn't look like a migrated Ledgerly database, checking that the transactions table exists before returning a connection at all. The message says as much: this server never migrates a database, it only reads one that's already been migrated.

Since stdout on a stdio server carries the JSON-RPC stream and nothing else, I sent every diagnostic here to stderr instead. A stray log line on stdout would corrupt every message after it, silently.

The comment at the top of the dispatcher is worth quoting whole, because it's the clearest record of why the server behaves the way it does:

 * Two behaviors here are not guesses; they come from Phase 0's capture of the real
 * claude CLI 2.1.228:
 *
 * - The client calls `server/discover` BEFORE `initialize`. That method is not in the
 *   MCP spec, so it lands in the unknown-method path and must answer -32601 politely.
 *   A server that throws or exits on it never reaches the handshake at all.

Ten Tools, and the One Nobody Had Written Yet#

Every tool the server exposes is read-only, and all ten wrap query functions that already existed: list_categories, list_accounts, get_activity_bounds, get_monthly_summary, get_trend, get_budget_status, get_goal_progress, get_top_merchants, get_recurring_merchants, query_transactions. I built nine of those as thin wrappers around the shared aggregates module from part one.

What actually answers "list my recurring expenses"? The tenth tool, and it didn't exist before this pass.

I put get_recurring_merchants in a new module, src/lib/analytics/recurring.ts, 128 lines, and unlike everything else in the app I didn't scope it to a month. It groups by normalized merchant across the full history, counts the distinct months each merchant shows up in, and returns anything seen in at least minMonths of them:

const monthExpr = sql<string>`substr(${transactions.date}, 1, 7)`;
const monthsSeenExpr = sql<number>`COUNT(DISTINCT ${monthExpr})`;
const netSpendExpr = sql<number>`-SUM(${transactions.amountCents})`;

with a having(monthsSeenExpr >= minMonths) and an orderBy(desc(monthsSeenExpr), desc(netSpendExpr)) on top. A merchant charging every month for a year looks completely different from one that showed up once and never came back, and counting distinct months instead of rows is what tells them apart.

Every tool argument is zod-validated, same as the rest of the app. The more interesting discipline is what I put in the descriptions the model actually reads. Amounts are integer cents everywhere in Ledgerly's schema, and instead of hoping the model remembers that, the unit notes get embedded directly in the tool description text:

export const CENTS_NOTE = "All amounts are integer cents, so 1599 means $15.99.";
export const NET_SPEND_NOTE =
  "Spend figures are positive net cents (charges minus refunds), so a larger number means more was spent. A negative value means refunds exceeded charges in the period.";
export const RAW_SIGN_NOTE =
  "Raw transaction amounts keep their stored sign: expenses are negative, income is positive.";
export const SCOPE_NOTE =
  "Covers committed, non-excluded transactions only, with the transfers category excluded.";

I capped results at 200KB per tool call once they serialize to JSON. Past that, the response gets cut and a note takes its place instead of a silently truncated blob:

export const MAX_TOOL_PAYLOAD_BYTES = 200 * 1024;

const TRUNCATION_NOTE =
  '\n\n[truncated: this result exceeded 200 KB. Narrow the query (a smaller pageSize, a single month, or a search term) or use an aggregate tool instead.]';

That note does double duty: it tells the model what happened, and it tells the model what to do about it.

The Flags That Confine the Model and the Flags That Pay for Themselves#

Getting the chat from single-turn to genuinely multi-turn meant raising --max-turns to 12 and setting a 300-second timeout (CHAT_TIMEOUT_MS = 300_000), enough room for a few tool calls and a real answer without leaving a hung request open forever.

It also meant the CLI now had actual capability during a turn, which is exactly when I needed to be deliberate about what it was allowed to do.

The whole thing spawns as an argv array, no shell involved. Here's the relevant slice of src/lib/ai/cli-args.ts I wrote, comments included, because the comments are doing as much work as the code:

return [
  ...base,
  "--mcp-config",
  mcp.mcpConfigJson,
  // Without this the user's own globally configured MCP servers load too.
  "--strict-mcp-config",
  ...toolListFlag("--allowedTools", mcp.allowedTools),
  ...toolListFlag("--disallowedTools", mcp.disallowedTools),
  // An explicit empty --tools removes the CLI's built-in tool set entirely. Two things
  // measured in the Phase 0 spike make this worth more than the --disallowedTools deny
  // list above (which stays as defense in depth): with built-ins present the model is
  // handed deferred tool schemas and burns a whole turn on a tool search before it can
  // call anything of ours, and a deny list only refuses named tools while this leaves
  // nothing but the MCP server to call.
  "--tools",
  "",
  // Likewise empty: the CLI otherwise loads the user's own settings sources, injecting
  // their global hooks, skills, and CLAUDE.md into a chat prompt that has nothing to do
  // with them. Observed contaminating answers, and dropping it cut measured cost ~13x.
  "--setting-sources",
  "",
];

I gave it a single --allowedTools entry, mcp__ledgerly, which the CLI treats as a grant covering every tool that server exposes.

I kept the deny list underneath it anyway: Bash, Edit, Write, NotebookEdit, WebFetch, WebSearch, Read, Glob, Grep, Task, named explicitly and refused. That's redundant by design, and I'd rather keep the second control than assume the first one is perfect.

So why did two empty flags cut the measured cost by roughly 13x? The spike's numbers, for one trivial tool-calling turn: $0.5715 as a baseline, $0.0866 with just --setting-sources "", $0.0438 with both flags together.

The setting-sources flag is the bigger of the two. It isn't skipping a few hook invocations, it's the difference between a prompt carrying just the product's system prompt and one carrying an entire personal Claude Code configuration nobody asked to load into a finance chat.

The --tools "" flag adds a smaller but real cut on top, by removing the turn the model would otherwise spend discovering deferred tool schemas.

I build the --mcp-config document itself inline, no config file touching disk, every path resolved absolute, the database path handed over through an environment variable:

mcpServers: {
  [MCP_SERVER_NAME]: {
    command: process.execPath,
    args: [
      path.join(repoRoot, "node_modules", "tsx", "dist", "cli.mjs"),
      path.join(repoRoot, "scripts", "mcp-server.ts"),
    ],
    env: { LEDGERLY_DB_PATH: resolveDbPath() },
  },
}

Three Seconds, On Every Single Call#

Somewhere in the Phase 0 output was a line I almost skimmed past: Warning: no stdin data received in 3s. The CLI was warning me, in plain text, about a bug I hadn't written yet.

Every claude spawn opened stdin as a pipe, the Node.js default, and nothing in the codebase ever wrote to it, so the CLI waited three seconds for input that was never coming.

Measured against a real call: first output landed at 3934ms before the fix, 331ms after. The fix was one line, changing the stdio array so stdin gets closed instead of piped:

/**
 * The stdio layout every `claude` spawn uses: stdin closed (/dev/null), stdout and
 * stderr piped as before. No code path ever writes to the CLI's stdin, and leaving it
 * an open pipe costs three seconds per call while the CLI waits for input that is never
 * coming ("Warning: no stdin data received in 3s").
 */
export const CLAUDE_STDIO: readonly ("ignore" | "pipe")[] = ["ignore", "pipe", "pipe"];

This lives in the one shared spawn helper every AI call in the app goes through, so the fix wasn't scoped to chat. Part one's column mapping and categorization calls, both --max-turns 1 calls, got faster the same day too.

Sessions as the Context Boundary#

Multi-turn tool calling also meant the chat needed somewhere to put a conversation that spans more than one exchange, which part one's design never had to think about. Two new tables cover it: chat_sessions and chat_tool_calls.

The context-boundary decision is the one I'd point to first. Prompts only ever read the current session's messages, never anything from an earlier one, so "End chat" releases context by construction rather than by a rule someone has to remember.

An ended session rejects new messages outright, HTTP 409, and the history window feeding each turn is capped at HISTORY_TURN_LIMIT = 12 no matter how long the session has run.

Getting there required a real migration, not just a couple of new columns. drizzle/0003_chat_sessions.sql, 52 lines, rebuilds the chat_messages table rather than altering it in place:

-- Legacy backfill: only if any messages exist, park them in one ended session
-- so the table-rebuild below always has a session to point old rows at.
INSERT INTO `chat_sessions` (`title`, `status`, `ended_at`)
SELECT 'Earlier conversation', 'ended', (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
WHERE EXISTS (SELECT 1 FROM `chat_messages`);

Session titles come from the first user message, but only while the title is still the default "New chat."

That one condition makes the naming call safe to run unconditionally on every message instead of separately tracking whether this is the first one. If the title has already moved past the default, the code does nothing.

Tool calls persist with the assistant message that produced them in one transaction, appendAssistantTurn. The UI renders them as activity chips while the turn is still streaming, a live trace of which tool ran rather than a mystery gap while the model works.

An Answer You Can Actually Hand Someone#

Any answer in the chat can come back out as a file: an xlsx workbook with an Answer sheet plus one sheet per tool call. Or a printable page rendered to PDF through the headless Chromium singleton the app already runs for report exports.

Both paths go through one shared module, src/lib/export/chat-tool-sheets.ts, 459 lines, which validates each tool result's shape exactly once. It hands back a PLANNERS record keyed by tool name, so Excel and the print rendering can never disagree about what a get_trend result looks like.

When a shape doesn't match what a planner expects, the fallback isn't a crash, it's a plain key/value dump of whatever came back.

Excel has its own hard limit worth knowing: 32,767 characters per cell. Going over that doesn't clip the string, it makes the whole workbook refuse to open.

Oversized tool results get cut with a note before they ever reach a cell, for the same reason the 200KB tool payload cap exists two layers up.

Three Reviews and the Bug Only the Fixtures Could See#

The whole thing landed in one commit, 785a17b: 76 files changed, 10,850 insertions, 439 deletions. None of it got built in one pass.

A planning agent produced the phased plan first. The Phase 0 spike and the schema-and-sessions work ran in parallel once the plan existed.

Three implementation tracks then ran in parallel too, MCP server, stream protocol, and export, each with strict file-ownership boundaries so they couldn't step on each other. Route and prompt integration ran serially after that, since it touched what all three tracks had built, and the UI phase came last.

TDD the whole way through. The test count went from 802 to 1,099 over the course of the session, and typecheck, lint, coverage thresholds, and the production build were all green before any of it got committed.

Three review passes ran before the commit, and each one caught something the others wouldn't have.

The security review came back clean, no exploitable issues, but it didn't take that on faith. The obvious worry with an Excel export built from arbitrary tool output is formula injection, a cell value starting with = that a spreadsheet application executes on open.

The review checked it empirically: exceljs types string cells structurally rather than writing raw text into the sheet XML, so a value like =cmd serializes as a shared string, not a formula. Verified by unzipping the actual workbook and reading the XML, not by reading exceljs's documentation and trusting it.

The finance-domain review went after the prompt and the tool descriptions specifically, and found nine issues, all fixed before the commit.

The prompt originally had one blanket sentence claiming all figures are net, true for aggregates and flatly wrong for raw transaction rows, which keep their stored sign. The fix states both conventions, verbatim:

Aggregate spend figures (summaries, trends, budgets, merchants) are net, refunds already subtracted, so a negative aggregate means refunds exceeded charges in that period. Individual transaction rows from query_transactions are different: they keep their raw stored sign, expenses negative and income positive, so a negative row is an ordinary charge, not a refund.

The other: avgMonthlyCents divides by months with activity, not by elapsed months, so an annual charge reads back as a monthly price unless something warns the model first. That fix landed in the tool description text, not the prompt, because that's the only place the model reliably reads it.

The adversarial review, finder agents paired with verifier agents, confirmed ten findings, and this is the one I keep thinking about.

The CLI namespaces every tool name on the wire: a call to get_trend shows up as mcp__ledgerly__get_trend. Every consumer downstream, though, was keyed on the bare name: the chat UI's activity labels, the tool_name column in chat_tool_calls, the Excel sheet planners. Every one of them.

So why did every unit test pass while the real thing was broken? Because the mocks in those tests fed bare tool names straight in, the same assumption baked into the code under test, so nothing ever exercised the actual wire format. The bug was invisible to the entire suite by construction.

It only surfaced because the Phase 0 fixtures were captured from the real CLI, namespace prefix and all, and something downstream of the fixture-driven parser choked on a tool name it didn't recognize.

The fix is one line at one boundary, and the comment explains why that's the right amount of code:

 * Drops the CLI's `mcp__ledgerly__` namespace from a tool name. Everything downstream
 * keys on the bare name -- the chat UI's activity labels, the tool_name column of
 * chat_tool_calls, and the export sheet planners all say `get_trend` -- so stripping it
 * once here, at the single boundary where the namespacing enters the system, is what
 * spares those three each having their own copy of this rule.

Two more bugs came out of that pass. A session ended mid-stream, "End chat" clicked while a turn that can run for minutes is still running, could still receive the assistant message that turn produced after the session was already closed.

The fix re-reads session status at the moment of persistence rather than only checking once before the turn starts. If the session ended in between, the answer that already streamed stays ephemeral, and the done chunk goes out with no messageId, telling the client there's no stored answer to build an export link from.

And stdout was being decoded per chunk instead of as a stream. That sounds like a nitpick until you work out what it does to a multi-byte UTF-8 character straddling a 64KiB pipe boundary: it gets replaced by U+FFFD, permanently, in the browser, in chat_messages, and in every export of that answer.

A TextDecoder running in stream mode fixed it, decoding across chunk boundaries instead of resetting state on every event.

With the namespacing fix and the other nine in place, a real turn against real data made two tool calls, answered the original recurring-expenses question, and cost about $0.29. The answer exported cleanly to both a workbook and a PDF. That's when the epilogue happened.

The Chat Noticed Something Nobody Asked About#

The chat mentioned it in passing, not as the answer to anything I'd asked: a recurring mortgage payment was "marked excluded in your ledger." That wasn't the question. The chat just noticed, because now it actually could.

Tracing it went straight back to part one. The one and only import Ledgerly has ever run was that Copilot Finance export, 10,613 transactions, and Copilot's export carries a per-row excluded flag of its own. Ledgerly's import pipeline had faithfully preserved that column, exactly as designed.

Faithful preservation was the bug. Whatever Copilot had decided to exclude, for reasons that had nothing to do with how I actually wanted my own ledger to read, came in excluded and stayed excluded, silently, for every report and every chat answer since.

A full audit of all 3,056 excluded rows, out of 10,613 total, sorted them into three buckets:

The middle bucket was its own small surprise, a duplication bug in the source app I'd never had reason to go looking for.

The last bucket is the one that mattered: over a thousand real transactions, spanning years, quietly outside every total the app had ever shown me, because a column I'd never audited said they should be.

Fixing it wasn't a single UPDATE statement I trusted on faith. A dry-run classification ran first, against the full 3,056 rows, with nothing written yet, just a report of which bucket each row would land in.

Only after reading that output did the real fix run, as one transaction, with an audit CSV written beforehand recording exactly which rows were about to change and why.

Before any of that, I took a proper backup: sqlite3 .backup, not a file copy. I'd have reached for cp without thinking twice if I hadn't already known better, and that instinct would have been wrong in a way I wouldn't have caught until it was too late to matter.

The database runs in WAL mode, so recently written data can sit in a separate -wal file that a raw file copy never touches. A cp of the main database file alone, taken at the wrong moment, silently omits everything sitting in that WAL.

.backup doesn't have that gap. It goes through SQLite's own backup API instead of the filesystem.

A passing mention of a mortgage payment doesn't have much to do, structurally, with a tool-calling chat, a hand-rolled MCP server, or an adversarial code review. It's the reason the whole project existed anyway: the chat could finally see all of the data, and it noticed something nobody had thought to ask about.

Where This Leaves the Backlog#

Part one ended with a chat that couldn't see its own database. Part two ends with one that reads all of it, runs its own queries, and found a data problem nobody had gone looking for.

The pattern from part one held here too: the bug that gets reported is rarely the bug that's actually there, and the review pass that's most annoying to run, the adversarial one, is usually the one that catches what the others would have shipped.

The 1,054 restored rows are live in my real ledger now, not sitting in an audit CSV waiting on a decision. Whatever the chat notices next, about that or anything else, is where the next post in this series picks up.