convez.ai
MCP ServerDados & AnalyticsAvançado
Shimiyahya

MCP Data Warehouse Server

Shimiyahya/mcp-data-warehouse-server

Query warehouse financeiro sintético em linguagem natural via MCP com segurança read-only

Ver no GitHub
InicialPluginScore · de 0 a 100

Um servidor MCP que expõe um data warehouse financeiro sintético (DuckDB) para LLMs consultarem via SQL natural, com camada de segurança read-only, validação AST e auditoria completa. Permite que modelos como Claude descubram o schema e executem análises multi-tabela complexas de forma segura.

Instalação

$npx convez-ai Shimiyahya/mcp-data-warehouse-server

1 comando no terminal: detecta o formato do repositório (marketplace ou plugin avulso) e instala via Claude Code. Requer Node 18+ e o Claude Code instalado.

Métricas

Estrelas
0
Forks
0
Acompanhando
0
Saúde
28%
Último commit
há 3 meses
Versão
v0.2.0
Issues abertas
0
Contribuidores
1

Casos de uso

  • Análise de dados financeiros e de tesouraria com LLM
  • Prototipagem de agentes que consultam bancos de dados
  • Demonstração segura de consultas SQL complexas e multi-joins
  • Auditoria e rastreamento de queries em ambiente sandbox

Veredito

Implementação sólida de um MCP server com foco em segurança e auditoria para consultas de dados. Ideal para quem quer integrar análise financeira segura com LLMs, mas exige familiaridade com DuckDB, AST parsing e arquitetura MCP.

README

A tradução para o português deste README ainda está na fila.

MCP Data Warehouse Server

Query a synthetic financial data warehouse in natural language — safely — over the Model Context Protocol.

CI Python Ruff License: MIT uv

An MCP server that exposes a small treasury & payments warehouse (DuckDB) to an LLM as MCP resources, tools, and a prompt — so a model like Claude can plan multi-step analytical SQL and reason over the results, while every query is forced through a read-only safety layer.

  • What it is — a read-only MCP server over DuckDB: schema/data-dictionary as resources, plus list_tables / describe_table / query tools.
  • Why it's safe — fully synthetic data, a physically read-only connection, SELECT/WITH-only validation via an AST parser, a per-table allow-list, forced row/byte caps, query timeouts, and a JSONL audit log of every call.
  • What's interesting — the questions require the model to discover the schema and plan 3–4-way joins (invoices ↔ payments ↔ FX ↔ counterparties) entirely on its own.

demo

Claude answering a multi-join treasury question end-to-end via this server (record with vhs/asciinema → docs/demo.gif).

Architecture

flowchart LR
    A["Claude<br/>(Anthropic SDK)"] -- "stdio JSON-RPC" --> B["MCP Server<br/>(FastMCP)"]
    B -- "tools: list_tables · describe_table · query" --> C
    B -- "resources: schema://catalog · table://{name}" --> C
    B -- "prompt: analyze_cashflow" --> A
    C["SQL guard<br/>(sqlglot AST)"] --> D[("DuckDB<br/>READ_ONLY")]
    E["scripts/seed.py<br/>(seed=42)"] -. "regenerates" .-> D
    B -. "every call" .-> F["audit.jsonl<br/>(stderr + file)"]

Quickstart

uv sync                              # create the venv + install deps
uv run python scripts/seed.py        # build warehouse.duckdb deterministically (seed=42)
uv run pytest -q                     # green tests — no API key needed
uv run python demo.py                # the LLM answers a multi-step question (needs ANTHROPIC_API_KEY)

demo.py reads ANTHROPIC_API_KEY from your environment or a local .env (copy .env.example).

Use it as a real MCP server

Claude Desktop — add to claude_desktop_config.json (Windows: %AppData%\Claude\…, macOS: ~/Library/Application Support/Claude/…), then restart:

{
  "mcpServers": {
    "data-warehouse": {
      "command": "uv",
      "args": ["--directory", "/ABSOLUTE/PATH/TO/mcp-data-warehouse-server", "run", "mcp-warehouse"]
    }
  }
}

Claude Code —

claude mcp add data-warehouse -- uv --directory /ABSOLUTE/PATH/TO/mcp-data-warehouse-server run mcp-warehouse

(If uv isn't on PATH for the GUI app, use its absolute path from which uv / where uv.)

The data

A fully synthetic, deterministic warehouse for a fictional company, Northwind Pay Ltd. No real entities, no PII — regenerated byte-identically from scripts/seed.py (random.seed(42)). Money is stored as integer minor units (pennies/cents) to avoid float drift; currencies are ISO-4217; FX is keyed by date so "in GBP" questions are genuinely multi-step.

erDiagram
    counterparty ||--o{ invoice : "bills / billed by"
    counterparty ||--o{ payment : "pays / paid"
    bank_account ||--o{ payment : "via"
    bank_account ||--o{ cash_balance_daily : "daily close"
    invoice ||--o{ payment_allocation : "cleared by"
    payment  ||--o{ payment_allocation : "applies to"
    fx_rate {
        date rate_date
        string base_ccy
        string quote_ccy
        decimal rate
    }
    counterparty {
        int counterparty_id PK
        string legal_name
        string country_code
        string sector
        bool is_internal
    }
    invoice {
        int invoice_id PK
        int counterparty_id FK
        string direction
        date issue_date
        date due_date
        string currency
        bigint amount_minor
        string status
    }
    payment {
        int payment_id PK
        int account_id FK
        int counterparty_id FK
        date value_date
        string currency
        bigint amount_minor
        string direction
        string method
        string status
    }
    payment_allocation {
        int payment_id FK
        int invoice_id FK
        bigint allocated_minor
    }

Eight tables in total: counterparty, bank_account, gl_account, fx_rate, invoice, payment, payment_allocation, cash_balance_daily. Run describe_table (or read the table://{name} resource) for the full column-level data dictionary.

Example questions it can answer

  1. "Top 5 counterparties by net GBP cash outflow in Q3 2024 (convert EUR/USD at the value-date FX rate), and what % of each one's payable invoices are still open past their due date?"
  2. "What was our 7-day rolling minimum cash position across all GBP accounts last quarter, and which day was tightest?"
  3. "SEPA vs SWIFT settlement success rate by month, weighted by EUR-converted value."
  4. "Days-sales-outstanding trend by counterparty sector over the last 6 months."

MCP surface

Tools

ToolArgsBehaviour
list_tables—Lists the allow-listed tables with row-count estimates.
describe_tablenameColumns + types + a small sample + the human data-dictionary for one allow-listed table.
querysql, limit?Runs a read-only SELECT/WITH query through the safety guard; returns structured rows + a markdown preview, with explicit truncation metadata.

Resources

URIReturns
schema://catalogThe full table catalog + data dictionary as JSON.
table://{name}Columns, types, dictionary and a sample for one table.

Prompts

NamePurpose
analyze_cashflowA starter prompt that primes the model to explore the schema and answer a cash-flow question.

Security model

Fintech reviewers read this section first — so here is exactly what protects the database:

  • Synthetic data only. No real PII; the warehouse is generated from a fixed seed.
  • Physically read-only. DuckDB is opened with read_only=True; external access is disabled (enable_external_access=false), extension auto-load is off, and the configuration is locked (lock_configuration=true) so injected SET statements can't re-enable anything.
  • SELECT/WITH only, single statement. Every query is parsed to an AST with sqlglot; anything that isn't a single top-level SELECT/WITH/set-op is rejected — this blocks the stacked-statement class of attack (e.g. … ; DROP …) that has bitten real database MCP servers.
  • Per-table allow-list. Only the eight documented tables are referenceable; information_schema, catalog functions, and any file/network table functions (read_csv, read_parquet, …) are denied.
  • Bounded output. A LIMIT is injected/clamped, results are capped by row and serialized-byte count, and a wall-clock timeout guards runaway queries.
  • Audit trail. Every tool call — allowed or denied — is logged as JSON (to stderr and logs/audit.jsonl) with the submitted SQL, the effective SQL, the decision/reason, row count and latency. Logging goes to stderr, never stdout (stdout is the MCP JSON-RPC channel).

What this does not claim: it is a portfolio demonstration, not a hardened multi-tenant product. The defenses are layered and real, but you should still run any LLM-facing database with least-privilege OS/DB credentials and in a sandbox.

Design decisions

  • DuckDB — an embedded analytical (columnar/OLAP) engine: zero infra, fast aggregations, and a genuine "warehouse" feel for financial analytics.
  • Official mcp SDK + FastMCP + stdio — the canonical, dependency-light way to build an MCP server; drops straight into Claude Desktop / Claude Code.
  • Integer minor units for money — avoids floating-point drift in sums.
  • Deterministic seed — makes the data auditable, the demo repeatable, and the tests reliable; the .duckdb binary is regenerated rather than committed.

Roadmap / limitations

  • DuckDB has no native statement timeout; the wall-clock guard runs the query on a worker thread and interrupts it (best-effort).
  • Cursor/keyset pagination is sketched for large result sets; current default is forced LIMIT + explicit truncation.
  • Not production-hardened (see security note above).

Development

make setup   # uv sync
make data    # regenerate warehouse.duckdb
make test    # pytest
make lint    # ruff check + format --check
make demo    # run the live LLM demo

License

MIT — see LICENSE. Data is synthetic and free to use.

Ver o original no GitHub

Bom para

Relacionados