The MCP server said one account had -£2,199.20. The app said £5,989.41. That’s an £8,188 gap. If you can’t trust the numbers, the whole financial visibility tool is decoration.
The Setup
I built an MCP server to query a personal finance app’s SQLite database. The idea: ask Claude “what did I spend on groceries this month?” and get a real answer from real data. No manual exports, no switching apps. Just a question and a number.
What followed was an evening session peeling back four layers of failure.
Layer 1: The Wrong Database
Two databases existed on the system:
| Database | Size | Last Modified |
|---|---|---|
| Old (legacy app identifier) | 594 KB | March 2023 |
| Current (active app identifier) | 25.7 MB | Today |
The MCP server config had "env": {} — empty. No database path specified, so it fell back to whatever default path was hardcoded. That default pointed at the 594 KB relic from 2023. Over two years of financial data, and the server was reading from a database last modified in March 2023.
Fix: Set the explicit path to the 25.7 MB current database in the MCP config.
What I expected this to fix: Everything.
What it actually fixed: The database selection. The server still wouldn’t start.
Layer 2: The Schema Error
PydanticInvalidForJsonSchema: Cannot generate a JsonSchema
for core_schema.PlainValidatorFunctionSchemaThe server used Pydantic models with custom validators for currency amounts. MCP requires tools to declare their schemas as JSON Schema. Pydantic’s custom validators can’t be serialized to JSON Schema. The server could start, but Claude Code couldn’t see any of the tools — they failed to register.
The Pydantic schema error existed independently of the database problem — the server’s tool registration was broken regardless of which database it pointed at. But with the wrong database, the stale data masked the schema issue. Both bugs were present simultaneously; I found them in sequence.
Decision: Rebuild from scratch. The existing codebase was over-engineered with complex Pydantic models. A simpler server using plain Python dicts would dodge the schema problem entirely.
Built a new server with FastMCP — 8 tools, all returning simple dicts and lists. Three bugs during the rebuild:
- FastMCP init parameter:
description=should beinstructions= - Category join column: wrong foreign key name
- Category name column:
ZNAME1should beZNAME2throughout
All 8 tools verified working. Balances returned. Moved on.
Came back later. Checked the numbers against the app. Still wrong.
Layer 3: The Missing Transaction Type
The balance calculation was summing transaction entities 37, 45, 46, and 47 — deposits, transfers, and withdrawals. But the finance app has a fifth type: entity 42, the reconciliation transaction. When you reconcile an account balance in the app, it creates adjustment entries as entity 42.
One account had enough reconciliation entries to swing the balance by £8,188. That’s the exact gap between -£2,199.20 (what the MCP reported) and £5,989.41 (what the app showed).
# Before:
Z_ENT IN (37, 45, 46, 47)
# After:
Z_ENT IN (37, 42, 43, 45, 46, 47)Two missing entity types in a SQL WHERE clause. Eight thousand pounds of discrepancy.
Layer 4: The WAL File
SQLite’s Write-Ahead Log. Recent writes go to a separate -wal file and only merge into the main database during a “checkpoint.” The MCP server was opening the database in read-only mode. Read-only connections don’t see uncommitted WAL data.
Any transaction entered in the app since the last checkpoint was invisible to the MCP server. The numbers could drift throughout the day and re-sync unpredictably whenever SQLite decided to checkpoint.
Fix: Copy the database and WAL files to a temp directory, perform a checkpoint to merge them, connect to the merged copy. Clean up on close.
Verification
Account A: €504.15 ✓ (matches app)
Account B: £5,989.41 ✓ (matches app)
Account C: £126.78 ✓ (matches app)All balances matching. Four layers deep.
The full layer map
| Layer | Error | Root Cause | What It Hid |
|---|---|---|---|
| 1 | Stale data from 2023 | Wrong database path (empty env config) | Schema serialization failure |
| 2 | Tools not registering | Pydantic can’t serialize to JSON Schema | Balance calculation bug |
| 3 | £8,188 discrepancy | Missing reconciliation entity (42) | WAL visibility gap |
| 4 | Balances drift during day | Read-only mode skips WAL file | Nothing — this was the bottom |
The Damage Report
| Metric | Value |
|---|---|
| Expected debugging time | ~10 minutes |
| Actual debugging time | Full evening session |
| Distinct failure layers | 4 |
| Complete server rebuilds | 1 |
| Bugs during rebuild | 3 |
| Balance discrepancy | £8,188 |
| Root cause of discrepancy | 2 missing entity types in SQL WHERE clause |
| Final state | All balances verified matching |
The Pattern
Each fix revealed the next problem. The temptation after fixing layer one is to test, see different output, and assume you’re done. Each layer produced a satisfying moment of “that was it” — wrong database, obviously. Schema error, of course. Missing entity type, got it.
The WAL issue was the worst kind of bug — the balances would be almost right most of the time. Only transactions entered since the last checkpoint would be missing. You could verify at 9 AM, enter a transaction at 10 AM, check at 11 AM, and the number wouldn’t include the 10 AM entry. Then check at 3 PM and it would — because SQLite checkpointed sometime between 11 and 3.
Sometimes debugging is harder than rebuilding. I rebuilt the server and still had two more layers to go.