Routing Guard - SAP Master Data Validation Workspace

Routing Guard is a read-only full-stack application for validating SAP routing and BOM (Bill of Materials) master data exported from Databricks. Business users can author, run, and govern SQL data-quality checks described in plain English - without writing SQL, querying databases directly, or touching SAP production systems.

The system is built around three guarantees:

Read-only by construction - every generated or user-typed query passes through a SQLGlot SELECT-only safety gate before execution. Write/DDL statements are rejected.
Catalog-grounded - generated SQL may only reference tables and columns that exist in the live schema catalog, which blocks SQL injection and LLM hallucination.
Swappable data layer - the same code runs against a local DuckDB "mock Databricks" or a real Databricks SQL Warehouse. Go-live is a config change, not a code change.

Architecture

Same shape as the seminar paper's solution diagram (docs/architecture.png, reproduced below): UI surface -> User Interface API -> Query Engine -> governed access to data sources -> LLM, with the answer flowing back up to the user. The ASCII block below maps each paper box to the actual module that implements it in this repo.

   webapp (built)          ┌───────────────────────────────────────────────────┐
   chat (planned)  ──────> │ User Interface API  =  FastAPI (backend/main.py)   │
   excel plugin (planned)  │                                                     │
                           │   Query Engine  =  agents/                          │
                           │   ┌────────────────┐   ┌──────────────────────┐    │
                           │   │ spec_extractor /│──>│ SQL Safety Gate      │    │
                           │   │ ask_agent       │   │ (SQLGlot SELECT-only)│    │
                           │   │ (NL -> spec/SQL)│   └─────────┬────────────┘    │
                           │   └────────────────┘             │                 │
                           │        "RAG" = catalog grounding  ▼                 │
                           │        ("MCP" = access control,  ┌────────────────┐ │
                           │         Phase 3, not built) ---> │ DataSource     │ │
                           │                                  │ (Protocol)     │ │
                           │                                  └───┬────────┬───┘ │
                           └──────────────────────────────────────┼────────┼─────┘
                                                                  ▼        ▼
                                                       DuckDB (mock)   Databricks REST
                                                       data/*.xlsx     (DATA_SOURCE=databricks)
                                                            │                │
                                                            └───────┬────────┘
                                                                    ▼
                                                          OpenRouter LLM (agents/llm.py)
                                                          -> plain-English answer -> user

Paper vs. repo, so the diagrams read as the same system:

Paper box (`docs/architecture.png`)	This repo
`webapp`	React SPA, `frontend/` - built
`chat`, `excel plugin`	Not built - see Roadmap
`User Interface API`	FastAPI, `backend/main.py`
`Query Engine`	`backend/agents/` (spec_extractor, rule_compiler, ask_agent)
`MCP or RAG + access control`	RAG half = catalog grounding (`/catalog`, `rule_compiler.py`). MCP/access-control half = Phase 3, not built.
`DataBricks`, `Files`	`DataSource` Protocol -> DuckDB mock (`data/*.xlsx`) or live Databricks REST
`MySQL`, `Data Catalog (metadata)`	Not separate services here - schema/business-description metadata lives in `backend/tableinfos/` and is served via `/catalog`, not a standalone MySQL store
`LLM`	OpenRouter, `backend/agents/llm.py`

How it works

Natural-language → SQL pipeline (never LLM-writes-SQL directly for rules):

A user describes a check in plain English ("Flag BOM components where the component quantity is zero or missing").
agents/spec_extractor.py asks the OpenRouter LLM to fill a small structured JSON spec - the model never emits SQL. With no API key, a deterministic keyword extractor is used instead.
agents/rule_compiler.py validates every field in the spec against the live catalog and compiles it into templated, parameterized, read-only SQL. A hallucinated column is rejected here, before any execution.
The SQL passes the SELECT-only safety gate, runs through the DataSource, and the rule is saved as a Draft (or auto-activated if RULE_AUTO_ACTIVATE=true).

Ask Anything (/ask) uses a similar flow: the model writes a single grounded SELECT, which is gated and executed; a second model call phrases a one-line answer from the returned rows.

DataSource abstraction (backend/datasource.py) - everything above this layer talks to a DataSource Protocol, never to a database. Both implementations return the same result envelope shaped like the Databricks SQL Statement Execution REST API:

Implementation	Mode	Notes
`DuckDBDataSource`	`DATA_SOURCE=mock` (default)	Loads `data/*.xlsx` into a local DuckDB file. Fully offline.
`DatabricksRestDataSource`	`DATA_SOURCE=databricks`	Calls the real Databricks REST API over httpx.

Screenshots

Captured live against npm run dev with the bundled sample data.

Validation Dashboard - 25 built-in + 8 NL rules, 26 issues (7 critical / 19 warning), "Challenge fit" summary cards.

Ask Anything - model picker, suggested questions, free-text input with CSV/JSON export.

Admin console (/admin) - live run history, rule counters, and log stream over SSE.

Roadmap

This repo is Phase 1 of a four-phase plan. RAG-grounded NL→SQL question answering (/ask) and NL→rule compilation are working end-to-end against real Wieland routing/BOM exports.

Phase	Scope	Status
1	NL question -> grounded SQL -> plain-English answer (`/ask`). Proof of concept.	Done (this repo)
2	Save/schedule validation checks as Rules; web UI to create, activate, run, export.	Done (this repo)
3	Governance layer for controlled write-back (approval workflow, audit trail, role-based access via e.g. MCP) so validated fixes can be pushed, not just reported.	Not started
4	Generalize the agent + RAG pipeline to other data domains (sales, inventory, quality).	Not started

Not yet built

No persistent approval workflow beyond Draft -> Active/Inactive rule states already in /governance.
No write path to SAP/Databricks - the app is read-only by construction (see Security section).
Not load-tested at production row counts; large-table execution should be pushed down into Databricks rather than pulled into DuckDB.
DatabricksRestDataSource is implemented but has not been run against a live warehouse in this project - only DATA_SOURCE=mock has been exercised end-to-end.

Sample Report Output

example_report.json shows the shape returned by GET /report and GET /report/export?format=json - one object per detected anomaly, each carrying the offending material/routing/BOM key, the wrong value, the source SAP table/field, a business explanation, and a recommended action. Use it as a reference when building a downstream consumer (e.g. a scheduled export into a shared drive or ticketing system).

Project Structure

bvc/
├── backend/
│   ├── main.py              # FastAPI app: all HTTP endpoints, run engine, admin console
│   ├── datasource.py        # Swappable DuckDB / Databricks DataSource (Protocol)
│   ├── admin.html           # Server-rendered admin/monitoring console (/admin)
│   ├── requirements.txt     # Python dependencies
│   ├── agents/
│   │   ├── llm.py           # OpenRouter LLM client (OpenAI-compatible chat API)
│   │   ├── spec_extractor.py# NL → structured rule spec (LLM or keyword fallback)
│   │   ├── rule_compiler.py # Structured spec → safe templated SQL (catalog-validated)
│   │   ├── rule_generator.py# Built-in system rules derived from the schema
│   │   ├── rule_quality.py  # Duplicate / overlap / contradiction audits (SQLGlot)
│   │   └── ask_agent.py     # Ask Anything NL Q&A over the data
│   ├── tableinfos/          # Column → business-description metadata (schema catalog)
│   └── tests/               # pytest suite
├── frontend/
│   ├── index.html
│   └── src/
│       ├── main.jsx         # React single-page app (all workspaces)
│       └── styles.css
├── data/                    # Sample SAP exports (CA03 routing, CS03 BOM)
├── .env.example             # Configuration template
├── package.json             # Root scripts: run backend + frontend together
├── THIRD_PARTY_NOTICES.md
└── validation.duckdb        # Local analytical DB (regenerated from data/*.xlsx on startup)

Technology Stack

Layer	Technology
Backend API	FastAPI + Uvicorn (Python 3.9+)
Data engine	DuckDB (mock) / Databricks SQL Warehouse (production)
SQL safety gate	SQLGlot (AST parser, blocks DDL/DML mutation)
LLM gateway	OpenRouter (OpenAI-compatible Chat Completions API)
Frontend	React 19 + Vite
Excel ingest	pandas + openpyxl
Tests	pytest

Configuration

Copy the template and edit as needed. Every variable is optional - defaults run fully offline and free:

cp .env.example .env

Variable	Default	Purpose
`DATA_SOURCE`	`mock`	`mock` (local DuckDB) or `databricks` (cloud REST).
`OPENROUTER_API_KEY`	(empty)	OpenRouter key. Empty → deterministic keyword fallback.
`OPENROUTER_MODEL`	`openai/gpt-oss-120b:free`	Model used for spec extraction / Ask Anything.
`OPENROUTER_TIMEOUT`	`60`	Request timeout (seconds).
`OPENROUTER_RETRIES`	`2`	Retries on 429 / 5xx / empty completion.
`ASK_COLUMNS_PER_TABLE`	`60`	Columns per table sent to the model in Ask Anything.
`APP_API_KEY`	(empty)	Shared secret. When set, all state-changing endpoints require `X-API-Key`.
`RULE_AUTO_ACTIVATE`	`false`	`true` → new rules go live without manual approval.
`MAX_NL_RULES`	`200`	Ceiling on stored business rules.
`RULE_RATE_LIMIT_PER_MIN`	`50`	Per-IP rule-creation rate limit.
`DATABRICKS_HOST` / `DATABRICKS_WAREHOUSE_ID` / `DATABRICKS_TOKEN`	-	Required only in `databricks` mode.
`DATABRICKS_CATALOG` / `DATABRICKS_SCHEMA`	`dev_gold` / `GX_P`	Databricks catalog/schema.

Setup & Running

Prerequisites

Python 3.9+
Node.js 18+

Quick start (one command)

From the project root:

npm run install:all   # pip install backend deps + npm install frontend deps
npm run dev           # runs backend (:8000) and frontend (:5173) concurrently

Then open http://127.0.0.1:5173 (app) and http://127.0.0.1:8000/admin (monitoring console). No login is required for either in mock mode - /admin streams live run history, rule counters, and logs via SSE (/api/admin/stream), useful for watching a Run all execute in real time.

Manual setup

Backend (http://127.0.0.1:8000):

python -m pip install -r backend/requirements.txt
python -m uvicorn backend.main:app --host 127.0.0.1 --port 8000 --reload

On startup the backend loads data/Michael_Ruhland_CA03.xlsx (routing) and data/Michael_Ruhland_CS03.xlsx (BOM) into DuckDB.

Frontend (http://127.0.0.1:5173):

cd frontend
npm install
npm run dev

Tests

cd backend
pytest tests/

API Reference

All state-changing endpoints require an X-API-Key header when APP_API_KEY is set.

Data & schema

Method	Path	Description
`GET`	`/health`	Liveness check.
`GET`	`/schema`	Parsed schema (columns → business descriptions).
`GET`	`/catalog`	Live table/column catalog used to validate generated SQL.
`POST`	`/upload`	Upload routing/BOM Excel files. (auth)
`POST`	`/demo-reset`	Reload bundled sample data. (auth)

Rules & governance

Method	Path	Description
`GET`	`/rules`	List all rules.
`GET`	`/suggested-rules`	Built-in starter rule templates.
`POST`	`/generate-rule-from-text`	NL → spec → SQL → Draft rule. (auth, rate-limited)
`POST`	`/rules/{id}/activate`	Activate a draft rule. (auth)
`POST`	`/rules/{id}/deactivate`	Deactivate a rule. (auth)
`DELETE`	`/rules/{id}`	Soft-delete a rule. (auth)
`GET`	`/governance`	Rule lifecycle history + quality audits.

Execution & reporting

Method	Path	Description
`POST`	`/run-rule/{id}`	Run a single rule. (auth)
`POST`	`/run-all`	Run the full validation suite. (auth)
`GET`	`/status/{job_id}`	Poll an async run.
`GET`	`/report`	Latest validation results.
`GET`	`/report/export`	Export results as CSV / JSON.
`POST`	`/simulate-scheduled-run`	Trigger a simulated scheduled run. (auth)

Ask Anything & LLM

Method	Path	Description
`POST`	`/ask`	NL question → grounded SELECT → answer.
`POST`	`/ask/stream`	Streaming variant of `/ask`.
`GET`	`/llm-status`	OpenRouter connection + active model.
`POST`	`/llm/model`	Switch the active model. (auth)

Admin console

Method	Path	Description
`GET`	`/admin`	Real-time monitoring dashboard (HTML).
`GET`	`/api/admin/stats`	Run history, rule counters, recent logs.
`GET`	`/api/admin/stream`	Server-Sent Events log stream.

Workspaces

Validation Dashboard - health overview: active rules, detected issues, critical alerts, last-run timestamp.
Schema Explorer - maps cryptic SAP column names ([Description]_[Table]_[Field], e.g. Number_of_employees_PLPO_ANZMA) to business descriptions, source tables, and functional areas.
Ask Anything - plain-English Q&A; compiles to a grounded SELECT and returns exact values.
Create Rule - NL-to-SQL compiler; saves a catalog-validated, read-only Draft rule.
Governance - full rule lifecycle (Draft → Active → Inactive → Deleted) with duplicate, overlap, and contradiction audits.
Results - table of detected anomalies (rule, material, routing group, operation, bad value, expected logic, recommended fix), exportable as CSV/JSON.

Security & Safety Gates

SELECT-only guard - all SQL is parsed with SQLGlot; any statement containing INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, or MERGE is rejected with HTTP 400.
Catalog constraints - generated SQL may only reference catalog-known tables/columns.
No SAP write path - the application is strictly read-only against all data sources.
Auth & rate limiting - optional APP_API_KEY gates state-changing endpoints; rule creation is per-IP rate-limited and capped by MAX_NL_RULES.

License & Attribution

No open-source license has been assigned - treat this repository as proprietary / internal MVP unless a LICENSE file is added. Third-party dependency licenses are listed in THIRD_PARTY_NOTICES.md.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Routing Guard - SAP Master Data Validation Workspace

Architecture

How it works

Screenshots

Roadmap

Not yet built

Sample Report Output

Project Structure

Technology Stack

Configuration

Setup & Running

Prerequisites

Quick start (one command)

Manual setup

Tests

API Reference

Data & schema

Rules & governance

Execution & reporting

Ask Anything & LLM

Admin console

Workspaces

Security & Safety Gates

License & Attribution

About

Uh oh!

Contributors

Uh oh!

Languages

Name		Name	Last commit message	Last commit date
Latest commit History 40 Commits
backend		backend
data		data
docs		docs
frontend		frontend
node_modules		node_modules
.env.example		.env.example
.gitignore		.gitignore
README.md		README.md
THIRD_PARTY_NOTICES.md		THIRD_PARTY_NOTICES.md
example_report.json		example_report.json
package-lock.json		package-lock.json
package.json		package.json

Folders and files

Latest commit

History

Repository files navigation

Routing Guard - SAP Master Data Validation Workspace

Architecture

How it works

Screenshots

Roadmap

Not yet built

Sample Report Output

Project Structure

Technology Stack

Configuration

Setup & Running

Prerequisites

Quick start (one command)

Manual setup

Tests

API Reference

Data & schema

Rules & governance

Execution & reporting

Ask Anything & LLM

Admin console

Workspaces

Security & Safety Gates

License & Attribution

About

Topics

Resources

Uh oh!

Stars

Watchers

Forks

Contributors

Uh oh!

Languages