Building an MCP Server on 31 Million Rows of Financial Data

Shibui Finance has built an MCP server that gives Claude direct SQL access to 64 years of US stock market data, including 31 million daily price records and 6.4 million SEC filings. The system uses a Python ETL pipeline to ingest data into PostgreSQL, transforms it with dbt, and exports to DuckDB for fast read-only queries. Pre-computing 56 technical indicators reduced query time from minutes to milliseconds.

This is the architecture of Shibui Finance https://shibui.finance , an MCP server that gives Claude direct SQL access to 64 years of US stock market data. About 10,000 symbols, 31 million daily price records, quarterly financials back to 1990, 56 pre-computed technical indicators, and 6.4 million SEC filing records. Free to use. Stack: Python, PostgreSQL, dbt, DuckDB, FastMCP, Caddy. Runs on a single VPS. Three stages: ingest into PostgreSQL, transform with dbt, export to DuckDB. Data APIs / SEC EDGAR / FRED | Python ETL Polars, ADBC | PostgreSQL clean schemas ~50 raw tables | dbt 27 models staging - integration schema 17 analytical tables | DuckDB export daily, ~14 GB file | FastMCP server read-only, streamable-http | Caddy TLS - mcp.shibui.finance Multiple sources feed the pipeline: commercial data APIs for prices, fundamentals, valuations, and estimates. SEC EDGAR for filing metadata and insider transactions bulk historical + a 5-minute Atom feed for near-real-time . FRED for FX rates to normalize non-USD fundamentals. Public registries for ticker classification. The ETL is a Python CLI organized by data source. Each module has its own fetcher, loader, and CLI. A single all command runs everything in fixed sequence. You can't refresh 10,000 tickers daily without hitting rate limits, so the ETL rotates: each run refreshes the stalest 5% of tickers. Full universe cycles in about 20 runs. Recent prices always refresh on every run. Every table write is a single transaction. DROP + CREATE inside a transaction, rollback on failure. The database never serves partial data, and dbt always sees complete tables even when ingest jobs overlap. 27 models in two tiers. The process layer handles standardization: enriching symbols with security types and exchange mappings, linking SEC amendment filings to their originals, repairing filer date typos. The integration layer produces the 17 tables that Claude actually queries. This is where raw normalized tables get flattened into analytical views. Insider transactions, for example, collapse 6 normalized ownership tables into one flat layer with boolean signal flags is this a 10b5-1 plan trade, a tax withholding, a gift, etc. derived from SEC filing footnotes. All integration models enforce dbt contracts with uniqueness tests on natural keys. My first version computed indicators like RSI and Bollinger Bands with SQL window functions at query time. Against 31 million rows, that took minutes. Now all 56 indicators are pre-computed during ETL and stored alongside price data. The indicators table has a 1:1 relationship to the quotes table on symbol + date. Query time went from minutes to milliseconds. They're computed in 6 batches trend, momentum, volatility, volume, candlestick patterns, statistical to control memory, then consolidated by dbt into a single table. One data quality detail worth mentioning: rows with zero OHLC values get filtered out before computation. Bad data corrupts rolling windows and produces wrong indicator values for all subsequent rows in that ticker's series. The MCP server doesn't write data. It only reads. DuckDB is ideal for this: a single file, no daemon, fast columnar scans, in-process. The export uses DuckDB's PostgreSQL scanner to copy tables directly: con = duckdb.connect str output path con.execute 'INSTALL postgres; LOAD postgres;' con.execute f"ATTACH '{database uri}' AS pg TYPE POSTGRES, READ ONLY ;" for table in TABLES: con.execute f'CREATE TABLE shibui.{table} AS FROM pg.shibui.{table};' Why PostgreSQL in the middle? I started with PostgreSQL and only later added DuckDB. But the split turned out to be the right architecture. PostgreSQL gives me transactions for writes, dbt always sees complete tables. DuckDB gives me fast analytical reads without a connection pool or daemon. When a fresh export lands, the ETL POSTs to a /reload endpoint. The server opens a new DuckDB connection, swaps it in, waits for in-flight queries to drain, then closes the old connection. No restart, no downtime. Built with FastMCP, served over streamable-http. 11 tools, but the architecture boils down to three layers: Query execution. Accepts SQL, runs EXPLAIN first to catch errors before touching data, then executes with a hard row cap. Every query is logged with timing and the user's original prompt. await backend.validate query EXPLAIN catches column/syntax errors result = await backend.fetch query Execute validated query The EXPLAIN-before-execute pattern matters because Claude generates the SQL. Bad queries should fail fast, not after scanning millions of rows. Schema delivery. The schema tool returns a Jinja2 template rendered at startup with live database stats: row counts, date ranges, value distributions. When the DuckDB file reloads, the template re-renders. Claude always gets accurate numbers, not a stale static file. Domain workflows. Seven workflow loaders inject domain-specific instructions on demand screening, backtesting, technical analysis, etc. . Claude loads only what's relevant. This keeps context focused. This took the most iteration. The schema alone isn't enough. Claude needs explicit rules about conventions, edge cases, and performance traps. The server instructions contain 23 rules. A few that matter most: Pre-filter before window functions. 31 million rows. If Claude writes ROW NUMBER OVER ... without a date filter first, it computes a window function over the entire history. Accounting conventions. Some financial values are stored as negatives dividends paid, for example . Without an explicit rule to use ABS , every dividend screen returns zero results. Chronological vs. extreme values. MIN close returns the lowest price, not the first price. For "price at start of year" you need an ordered subquery, not an aggregate. This caused wrong results until I added an explicit rule. Consistent naming. One symbol format across all 17 tables CODE.EXCHANGE . One consistent convention means Claude never has to guess how to join tables. These rules are delivered as tool output, not baked into system prompts. They load when needed and can be updated without redeploying. Docker Compose on a VPS. Daily ETL pipeline, SEC filing feed every 5 minutes. Marginal cost per query: effectively zero because DuckDB reads are local and in-process. The server is live at shibui.finance https://shibui.finance . Add the connector URL in Claude's settings, start asking questions. If you're building MCP servers: structure your data cleanly, give the model enough schema context to write correct queries, and handle edge cases in server instructions rather than hoping it figures them out. The server instructions are where most of the real work lives. Source: shibui.finance https://shibui.finance | @shibui finance https://x.com/shibui finance