erp-report-engine

The numbers for Monday's meeting are already sitting in your ERP's database. So who's still writing the report?

A read-only, self-verifying data layer for the SQL database behind your ERP — weekly reports and a guarded MCP server for AI agents. Every query provably read-only: measured against 28 attacks, not promised in prose.

🇹🇷 Türkçesi: README.tr.md

One scheduled run executes 9 audited SELECT statements and delivers a self-contained HTML report: four KPIs against an 8-week baseline, findings with named drivers, a data-quality gate, and row counts reconciled against the source. No BI license, no agent installed on the ERP server, and no writes — enforced in four layers (lexical, parse-tree, a side-effecting-function guard, and a read-only session), not promised in prose.

This exact report was produced by one command against the bundled demo database — including the three data-quality problems deliberately seeded into it, all caught by the gate.

The same guarded engine talks to AI agents, too. erp-report-engine mcp exposes the ERP to an agent through canonical entities (orders, never LG_001_01_ORFICHE) behind the same read-only guard — the layer the MCP ecosystem keeps getting wrong. The reference PostgreSQL MCP server's read-only mode was walked out of with COMMIT; DROP SCHEMA public CASCADE; and archived; Supabase's MCP became the textbook "lethal trifecta". Here the guard checks the functions a statement calls, not just its shape — and you don't have to take the adjective's word for it:

▶ Run the 28-attack trust benchmark · break the guard yourself, in your browser (the real guard.py, via Pyodide, nothing sent anywhere).

The exact guard.py the tests run, executing in the browser — a file read and the read-only-transaction escape refused, a real aggregate allowed. Paste your own attack →

60-second demo (no ERP required)

# install (pipx or uv keep it isolated; plain pip works too)
pipx install erp-report-engine          # or: uv tool install erp-report-engine
# from source instead:  pip install .

erp-report-engine init-demo             # builds demo.db + config.demo.yaml here
erp-report-engine run -c config.demo.yaml

Every command is also available as python -m erp_report_engine …. Add a database driver with the extras: pipx install "erp-report-engine[mssql]" (Logo Tiger / Netsis / Mikro — SQL Server) or [postgres].

Prefer Docker? docker build -t erp-report-engine . then docker run --rm -v "$PWD:/work" erp-report-engine run -c config.demo.yaml. SQLite and PostgreSQL work out of the box; for MSSQL add Microsoft's ODBC driver (see the Dockerfile). Publishing to PyPI is a GitHub Release away — publish.yml builds and uploads via Trusted Publishing (OIDC, no stored token), and CI already builds the wheel and asserts every bundled profile ships.

Open reports/erp_report_<week>.html. You'll see the engine catch a revenue spike and attribute it to one region, flag a two-point on-time decline, list items below two weeks of stock cover — and confess every duplicate and negative row it found on the way.

▶ See a live sample report: gulmezeren2-byte.github.io/erp-report-engine (also committed at docs/sample-report.html).

Or run run --dashboard for the premium Command Center — a dark, modern, self-contained dashboard with animated KPIs and glowing SPC control-band charts (live):

What one run produces

How it works

flowchart LR
    DB[("ERP database<br/>MSSQL · PostgreSQL · SQLite")]
    PROF["Semantic profile<br/>(YAML contract)"]
    GUARD["safe_read<br/>read-only guard + audit"]
    GATE["Data-quality gate<br/>+ source reconciliation"]
    KPI["KPI engine<br/>ISO weeks, 8-wk baseline"]
    INS["Insight rules<br/>driver attribution"]
    HTML["Self-contained<br/>HTML report"]
    STATE[("state.db<br/>run memory")]

    PROF --> GUARD
    DB --> GUARD --> GATE --> KPI --> INS --> HTML
    STATE --- HTML
    GATE --> PBI["Power BI Command Center<br/>(TMDL + PBIR, code-authored)"]

The layer that makes this portable is the semantic profile: a versioned YAML contract that maps one ERP's cryptic schema to three canonical entities — orders, order_lines, inventory — plus an optional receivables entity (open AR, for aging) that a profile maps when the ledger is reachable and everything downstream skips gracefully when it isn't. The engine only ever sees canonical columns. Swap the profile, keep the report.

The security model

Pointing software at a production ERP database is a trust decision. This engine treats it that way — the guarantees are enforced in code and covered by tests:

Read-only is enforced in four layers, so no single mistake makes the engine capable of writing:

Ad-hoc SQL — the agent path — is stricter still. query and guarded_query run in strict mode, which default-denies every function the guard cannot name: sqlglot's function registry is the allowlist, since it knows the portable analytic functions and nothing that reads a file or opens a socket. All four bundled profiles pass it — they call no unrecognised function at all.