This is the architecture of 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.
Data pipeline
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.
The dbt layer
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.
Pre-computing technical indicators
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.
DuckDB as the query layer
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.
The MCP server
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.
Making Claude write correct SQL
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.
Deployment
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.
Limitations
- Daily bars only, no intraday
- No options, derivatives, or crypto
- US exchanges only
- Mid-tier data, not a primary exchange feed
- Roughly 1-day lag on prices
Try it
The server is live at 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 | @shibui_finance
Top comments (0)