# Login Query Agent — Ontology MCP & Knowledge Graph

A POC that uses an **OWL/SHACL/SKOS Knowledge Graph** + **two MCP servers**
to route login-diagnosis queries across SQL Server and MongoDB, with conditional New Relic escalation.

---

## Architecture at a Glance

```
User prompt (VS Code Copilot)
        │
        ▼  LLM classifies category natively — no tool call
        │
  ontology-mcp  ──► Fuseki KG (SPARQL)
        │              get_diagnosis_plan(category)
        │              returns: capability_id, required_entities,
        │                       validation_sequence, newrelic_tool
        ▼
  data-mcp  ──► SQL Server  (UM_Users, UM_UserPartnermapping,
        │                    UM_UserMobileNumberVerified)
        ├──────► MongoDB     (users collection — 9 projected fields)
        ├──────► SHACL Validator  (shapes read from KG shacl graph, evaluated in sequence order)
        └──────► New Relic   (only when all_shapes_pass=true — 2-step NRQL)
```

---

## Services Overview

| Service | Type | Who starts it | Required for |
|---|---|---|---|
| Apache Jena Fuseki | Local process | **You (manual)** | ontology-mcp KG queries |
| `ontology-mcp` | stdio child process | VS Code auto-spawns | Diagnosis planning |
| `data-mcp` | stdio child process | VS Code auto-spawns | DB queries + validation |
| SQL Server | Remote/LocalDB | Already running | Data queries |
| MongoDB | Remote server | Already running | Data queries |
| New Relic | Cloud service | Always available | Escalation (all shapes pass) |

> Only **Fuseki** requires a manual start. Both MCP servers are auto-spawned by VS Code.

---

## Prerequisites

### 1. Java 11+
```powershell
java -version
```

### 2. Apache Jena Fuseki JAR
The JAR is excluded from git (54 MB). Download from [jena.apache.org](https://jena.apache.org/download/) and place at:
```
infra/fuseki/fuseki-server.jar
```

### 3. Python 3.12+
```powershell
python --version
```

### 4. Python dependencies
```powershell
cd c:\Ontology
python -m pip install -r requirements.txt
```

### 5. ODBC Driver for SQL Server
Download **ODBC Driver 17 or 18 for SQL Server** from Microsoft if not already installed.

### 6. VS Code with GitHub Copilot (Agent mode)
VS Code 1.99+ with the GitHub Copilot extension.

---

## Step-by-Step Local Startup

### Step 1 — Start Fuseki
```powershell
cd c:\Ontology
java -jar infra\fuseki\fuseki-server.jar --config infra\fuseki\config\login-kg.ttl
```
Keep this terminal open. Verify at [http://localhost:3030](http://localhost:3030).

### Step 2 — Load the Knowledge Graph
> Required on first run or after any schema/artifact change.

```powershell
$env:PYTHONIOENCODING = "utf-8"
python scripts/generate/generate.py --schema login --version 1.0.0
python scripts/kg/load_kg.py        --schema login --version 1.0.0
python scripts/kg/promote.py        --schema login --version 1.0.0
```

### Step 3 — Configure secrets
Copy `.env.example` to `.env` and fill in your values:
```
SQL_SERVER_HOST=your-server
SQL_SERVER_DATABASE=your-database
SQL_SERVER_TRUSTED_CONNECTION=yes
SQL_SERVER_ENCRYPT=yes
SQL_SERVER_TRUST_CERT=yes

MONGODB_URI=mongodb://your-host:27017
MONGODB_DATABASE=your-database

NEW_RELIC_API_KEY=NRAK-xxxxxxxxxxxxxxxxxxxx
NEW_RELIC_ACCOUNT_ID=your-account-id
NEW_RELIC_REGION=US

APP_ENV=prod
```

### Step 4 — Register both MCP servers
Create `.vscode/mcp.json` in the workspace root:

```json
{
  "servers": {
    "ontology-mcp": {
      "type": "stdio",
      "command": "python",
      "args": ["-m", "mcp_server.server"],
      "cwd": "c:\\Ontology",
      "env": {
        "PYTHONPATH": "c:\\Ontology\\src",
        "PYTHONIOENCODING": "utf-8"
      }
    },
    "data-mcp": {
      "type": "stdio",
      "command": "python",
      "args": ["-m", "mcp_server.diagnostic_server"],
      "cwd": "c:\\Ontology",
      "env": {
        "PYTHONPATH": "c:\\Ontology\\src",
        "PYTHONIOENCODING": "utf-8"
      }
    }
  }
}
```

Reload VS Code (`Ctrl+Shift+P` → `Developer: Reload Window`).

---

## Full Diagnostic Flow

```
User: "testgdpr1235@gep.com can't reset password"
        │
        │  LLM classifies: category = "password_reset"  (no tool call)
        │
        ▼
① ontology-mcp / get_diagnosis_plan(category="password_reset")
     Reads x_capability_registry from login.yaml (no Fuseki needed for this step)
     Returns: capability_id, required_entities, validation_sequence, newrelic_tool
        │
        ▼  (agent extracts username from user message; asks if missing)
        │
② data-mcp / query_sql_user(username, capability_id)
     SELECT from UM_Users → islocked, isactive, isdeleted, usertype, emailaddress, ...
        │
③ data-mcp / query_sql_mobile_verification(username, capability_id)
     SELECT from UM_UserMobileNumberVerified → ismobilenumberverified
        │
④ data-mcp / query_sql_partner_mappings(username, capability_id)
     SELECT from UM_UserPartnermapping → bpc, partnercode, isactive, contactcode
        │
⑤ data-mcp / query_mongo_user(username, capability_id)
     db.users.find_one({...}, { 9 diagnostic fields }) → MongoDB document
        │
⑥ data-mcp / validate_login_shapes(username, capability_id, validation_sequence)
     Runs only the shapes in validation_sequence (plan-scoped)
     Returns: per-shape PASS/FAIL, all_shapes_pass, advisories (e.g. dr_012)
        │
   ┌────┴──────────────────────────┐
violations found              all_shapes_pass = true
   │                               │
report per shape              ⑦a data-mcp / query_newrelic_login_mfa(username, capability_id)
with mapped rule                   OR
dr_003..dr_008                ⑦b data-mcp / query_newrelic_reset_password(username, capability_id)
                                    → Transaction → Log per traceId (max 7 days)
```

> Only entities listed in `required_entities` are fetched. Steps ②–⑤ are skipped for
> categories that don't need them (e.g. `account_locked` skips partner + mobile queries).

---

## MCP Tools Reference

### ontology-mcp — Knowledge Graph planning tools (3 tools)

| Tool | Step | Input | Returns |
|---|---|---|---|
| `get_diagnosis_plan` | 0 — **mandatory first call** | `category`, `schema` | `capability_id`, `required_entities`, `validation_sequence`, `newrelic_tool`, `required_parameters`, `datasources`, `additional_checks` |
| `list_capabilities` | fallback only | `schema` | All 8 categories with `id`, `description`, `covers` |
| `get_entity_descriptor` | on demand | `class_name`, `schema` | Full column/field mapping from KG descriptors graph |

> `get_diagnosis_plan` reads the capability registry **directly from `login.yaml`** — no Fuseki call needed.
> `get_entity_descriptor` queries the Fuseki descriptors graph — requires Fuseki running.

### data-mcp — Live data tools (7 tools)

All 7 tools require `capability_id` from `get_diagnosis_plan`. Calling without it returns a structured error.

| Tool | Step | Source | Returns |
|---|---|---|---|
| `query_sql_user` | 1a | `UM_Users` | userid, username, emailaddress, usertype, authenticationtype, islocked, isactive, isdeleted, issystemuser, mobileno |
| `query_sql_mobile_verification` | 1b | `UM_UserMobileNumberVerified` | ismobilenumberverified + SQL executed |
| `query_sql_partner_mappings` | 1c | `UM_UserPartnermapping` | All mapping rows, total count, active count |
| `query_mongo_user` | 1d | `users` collection | 9 projected fields + query executed |
| `validate_login_shapes` | 2 | SQL + MongoDB | Per-shape PASS/FAIL, `all_shapes_pass`, `advisories`, `next_step` |
| `query_newrelic_login_mfa` | 3a | New Relic NerdGraph | Transaction + Log for `/Account/Login` (dr_010) |
| `query_newrelic_reset_password` | 3b | New Relic NerdGraph | Transaction + Log for 3 reset URIs (dr_011) |

---

## Diagnostic Categories (8)

| Category | Triggers when |
|---|---|
| `login_failure` | Cannot login / authenticate / access the app, SSO failure, credentials rejected |
| `password_reset` | Reset link or forgot-password email not received |
| `otp_email` | OTP email not received during reset |
| `sms_otp` | SMS OTP not received (mobile is verified) |
| `account_state` | Account deactivated / inactive / suspended / disabled |
| `account_locked` | Account locked after multiple failed attempts |
| `partner_mapping` | Missing / inactive partner (BPC) mapping |
| `data_sync` | SQL vs MongoDB field mismatch |

---

## SHACL Shapes (8, evaluated in sequence order)

| # | Shape | Condition | Rule |
|---|---|---|---|
| 1 | `LoginBlockShape` | isLocked=1 OR isActive=0 OR isDeleted=1 | dr_003 |
| 2 | `SystemUserShape` | isSystemUser=1 | dr_005 |
| 3 | `BuyerSSOShape` | userType=Buyer AND authenticationType=SSO | dr_006 |
| 4 | `PartnerMappingShape` | No active partner mapping row | dr_004 |
| 5 | `SupplierPartnerMappingShape` | Supplier with no active non-zero BPC | dr_007 |
| 6 | `EmailVerificationShape` | No valid registered email address (reset/OTP flows) | — |
| 7 | `MobileConsistencyShape` | SQL vs MongoDB isMobileNumberVerified mismatch | dr_002 |
| 8 | `PartnerMappingDataSyncShape` | SQL vs MongoDB partner mapping fields mismatch | dr_008 |

> Each category's `validation_sequence` runs only the relevant subset of these shapes.
> `advisories` (e.g. `dr_012` email mismatch) are returned alongside shapes but do **not** affect `all_shapes_pass`.

---

## New Relic Query Structure (2-step)

```
Step 1: Transaction table (max 7 days lookback, filtered by APP_ENV)
  /Account/Login            → LoginUserName, traceId, RequiresTwoFactor, TwoFactorDetails
  /Account/RecoverPassword  → traceId, errorMessage, RecoveryUserName, RecoveryEmail
  /Account/PreResetPassword → traceId, errorMessage, PreResetUserName
  /Account/ResetPassword    → LoginUserName, traceId, errorMessage

Step 2: Log table (per traceId from Step 1)
  SELECT * FROM Log WHERE `trace.id` = '{traceId}' SINCE {transaction_timestamp}
```

---

## Knowledge Graph — Named Graphs

The KG stores **6 named graphs** per version + 1 meta graph:

| Named Graph IRI | Content | Queried by |
|---|---|---|
| `urn:kg:login:v1.0.0:capabilities` | Diagnosis playbooks — 8 categories, required entities, validation sequences | `get_diagnosis_plan` (Step 0) |
| `urn:kg:login:v1.0.0:descriptors` | Entity column/field mappings | `get_entity_descriptor` + `validate_login_shapes` (materialization) |
| `urn:kg:login:v1.0.0:rules` | Decision rules (dr_001..dr_012) | **`validate_login_shapes`** — shape→rule mapping read at runtime |
| `urn:kg:login:v1.0.0:shacl` | SHACL node shapes + constraints | **`validate_login_shapes`** — shapes read + executed at runtime (KG-driven) |
| `urn:kg:login:v1.0.0:ontology` | OWL classes + properties | Available for inspection |
| `urn:kg:login:v1.0.0:skos` | SKOS concept scheme + labels | Available for inspection |
| `urn:kg:login:meta` | Active version pointer | Every Fuseki query (graph discovery) |

**Fuseki is queried at two stages of every diagnosis:**
1. `get_diagnosis_plan` (Step 0) — `get_active_graphs` (meta graph) + `get_capability_plan` (capabilities graph) → the full diagnosis playbook
2. `validate_login_shapes` (Step 2) — reads the **shacl** graph (shapes), **descriptors** graph (field/type mapping for materialization), and **rules** graph (shape→rule) — the validator is KG-driven

Fallbacks (each logs a warning): if Fuseki is unreachable, `get_diagnosis_plan` reads `x_capability_registry` from `login.yaml`, and `validate_login_shapes` falls back to the programmatic `shacl_validator.py`.

---

## Artifact Regeneration

When any YAML schema file changes:
```powershell
$env:PYTHONIOENCODING = "utf-8"
python scripts/generate/generate.py --schema login --version 1.0.0
python scripts/kg/load_kg.py        --schema login --version 1.0.0
python scripts/kg/promote.py        --schema login --version 1.0.0
```

---

## Project Structure

```
c:\Ontology\
├── src/
│   └── mcp_server/                        # PYTHONPATH=c:\Ontology\src
│       ├── server.py                      # ontology-mcp entrypoint (KG planning tools)
│       ├── diagnostic_server.py           # data-mcp entrypoint (DB/NR tools)
│       ├── tool_meta.py                   # loads config/tool_descriptions.yaml
│       ├── connectors/
│       │   ├── sql_connector.py           # pyodbc — UM_Users, UM_UserPartnermapping, ...
│       │   ├── mongo_connector.py         # pymongo — users collection (projected)
│       │   └── newrelic_connector.py      # NerdGraph GraphQL — 2-step NRQL
│       ├── diagnostics/
│       │   ├── data_fetcher.py            # orchestrates SQL + MongoDB fetch
│       │   ├── kg_shacl_validator.py      # KG-driven SHACL interpreter (PRIMARY)
│       │   └── shacl_validator.py         # programmatic evaluation (Fuseki-down fallback)
│       ├── tools/
│       │   ├── get_diagnosis_plan.py      # ontology-mcp: reads x_capability_registry
│       │   ├── list_capabilities.py       # ontology-mcp: lists all 8 categories
│       │   ├── get_descriptor.py          # ontology-mcp: SPARQL descriptors graph
│       │   ├── fetch_user_data.py         # data-mcp: 4 individual SQL/Mongo queries
│       │   ├── validate_shapes.py         # data-mcp: shape evaluation + advisories
│       │   └── query_newrelic.py          # data-mcp: NR login + reset handlers
│       ├── kg/
│       │   └── sparql_client.py           # Fuseki HTTP client + graph discovery
│       └── registry/
│           └── schema_registry.py         # registry.yaml + load_capability_registry()
│
├── ontology/
│   ├── schemas/
│   │   ├── registry.yaml
│   │   └── login/v1.0.0/
│   │       ├── login.yaml                 # root: x_capability_registry + x_shacl_rules + x_decision_rules
│   │       ├── shared/types.yaml
│   │       ├── shared/enums.yaml          # AuthenticationTypeEnum, UserTypeEnum
│   │       ├── shared/subsets.yaml
│   │       └── entities/
│   │           ├── abstract_user.yaml
│   │           ├── user.yaml              # SQL UM_Users
│   │           ├── partner_mapping.yaml   # SQL UM_UserPartnermapping
│   │           ├── mobile_verification.yaml # SQL UM_UserMobileNumberVerified
│   │           └── user_document.yaml     # MongoDB users collection
│   └── sparql/
│       ├── get_entity_descriptor.sparql
│       └── get_decision_rules.sparql
│
├── artifacts/login/v1.0.0/
│   ├── owl/login.owl.ttl
│   ├── shacl/login.shacl.ttl
│   ├── skos/login.skos.ttl
│   ├── rules/login.rules.ttl
│   ├── descriptors/login.descriptors.json
│   └── jsonld/login.context.jsonld + login.agent_template.json
│
├── scripts/
│   ├── generate/generate.py + gen_*.py + _yaml_loader.py
│   └── kg/load_kg.py + promote.py
│
├── config/
│   └── tool_descriptions.yaml             # single source of truth for all MCP tool descriptions
│
├── infra/fuseki/
│   ├── fuseki-server.jar                  # not committed — download separately
│   ├── config/login-kg.ttl
│   └── data/                              # TDB2 storage — gitignored
│
├── .github/copilot-instructions.md        # Copilot workspace instructions (auto-loaded)
├── CLAUDE.md                              # Claude Code workspace instructions (auto-loaded)
├── .vscode/mcp.json                       # MCP server registration (2 servers)
├── .env / .env.example                    # secrets — .env never committed to git
└── requirements.txt
```

---

## Troubleshooting

| Error | Cause | Fix |
|---|---|---|
| `sparql_failed` | Fuseki not running | Start Fuseki (Step 1) |
| `capability_id_required` | Agent skipped `get_diagnosis_plan` | Restart conversation; `CLAUDE.md` / `copilot-instructions.md` enforce the sequence |
| `schema_not_found` | `registry.yaml` missing the schema entry | Check `ontology/schemas/registry.yaml` |
| `registry_load_failed` | `login.yaml` missing `x_capability_registry` | Verify `login.yaml` has the block |
| `SQL Server connection error` | Wrong host/credentials in `.env` | Check `SQL_SERVER_HOST`, `TRUSTED_CONNECTION` |
| `No module named 'pyodbc'` | Missing dependency | `pip install pyodbc` |
| `UnicodeEncodeError` | Windows console encoding | Add `$env:PYTHONIOENCODING = "utf-8"` |
| Fuseki graphs empty | Fresh Fuseki start after restart | Run `load_kg.py` + `promote.py` |

---

## Daily Workflow

```powershell
# 1. Start Fuseki
java -jar infra\fuseki\fuseki-server.jar --config infra\fuseki\config\login-kg.ttl

# 2. Load KG (only after schema or artifact changes)
$env:PYTHONIOENCODING = "utf-8"
python scripts/kg/load_kg.py --schema login --version 1.0.0
python scripts/kg/promote.py --schema login --version 1.0.0

# 3. Open VS Code — both MCP servers start automatically
```

## Extending the Schema

### Add a new entity (new SQL table or MongoDB collection)
1. Create `ontology/schemas/login/v1.0.0/entities/new_entity.yaml`
2. Add `- entities/new_entity` to `login.yaml` imports
3. Run generate + load + promote

### Add or change a diagnostic category
1. Edit `x_capability_registry` in `login.yaml`
2. Add/update the matching shape in `x_shacl_rules` (`login.yaml`) — the KG-driven
   validator reads it from the `shacl` graph; **no Python edit needed** for
   `sh_in`/`sh_property`/`sparql`/`cross_source` shapes
3. Run generate + load + promote (so the new shape/rule enters the KG)
4. Restart the MCP servers

### Add or change a SHACL shape
Shapes are executed from the KG, not code. Edit `x_shacl_rules` in `login.yaml`,
then regenerate + reload. `kg_shacl_validator.py` (the generic engine) needs no
change unless you introduce a brand-new constraint *type*.

### Add a new schema version
1. Copy `ontology/schemas/login/v1.0.0/` → `v1.1.0/`
2. Edit entity files in `v1.1.0/`
3. Run generate + load + promote for `v1.1.0`

Both versions coexist in the KG — rollback is always available via `promote.py`.