# Sflight Mcp

> SAP Flight data model as a CAP CDS app with an MCP server for AI tool integration.

- **Type:** MCP server
- **Install:** `agentstack add mcp-mindsetconsulting-sflight-mcp`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [MindsetConsulting](https://agentstack.voostack.com/s/mindsetconsulting)
- **Installs:** 0
- **Category:** [Integrations](https://agentstack.voostack.com/c/integrations)
- **Latest version:** 0.1.0
- **License:** Unlicense
- **Upstream author:** [MindsetConsulting](https://github.com/MindsetConsulting)
- **Source:** https://github.com/MindsetConsulting/sflight-mcp
- **Website:** https://mindsetconsulting.com

## Install

```sh
agentstack add mcp-mindsetconsulting-sflight-mcp
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# SAP Flight Model — BTP CAP CDS

The classic SAP SFLIGHT data model — airlines, flights, bookings, passengers — reimagined as a modern **SAP BTP CAP CDS** application with an **AI-powered MCP server** built right in.

> Fork this repo, deploy to your BTP account, and start asking your data questions in plain English through Claude Code. No prior AI experience needed.

---

## What's Inside

| Layer | What It Does |
|-------|-------------|
| **26 Entities + 4 Views** | Airlines, flights, connections, bookings, customers, airports, meals, currencies — the complete SFLIGHT model in CDS |
| **59 Navigation Properties** | 5 compositions + 54 associations — fully navigable data model |
| **24 CSV Seed Data Files** | ~5,000 flights, 10,000+ bookings, 24 airlines, 15 destination cities (2023-2028) — ready to query immediately |
| **OData V4 Service** | REST API at `/odata/v4/flights` with full metadata, filtering, and expansion |
| **19-Tool MCP Server** | AI assistant that understands your data model, runs SQL queries, inspects schemas, and answers questions in plain English |
| **BTP-Ready Deployment** | MTA descriptor with XSUAA security, HANA Cloud HDI, and managed approuter |

---

## Platform Support

Works on **macOS**, **Linux**, and **Windows**. The OData server and MCP server build are fully cross-platform. Claude Code CLI requires macOS, Linux, or WSL2 on Windows.

| Component | macOS | Linux | Windows | Windows (WSL2) |
|-----------|:-----:|:-----:|:-------:|:--------------:|
| CAP server / OData | Yes | Yes | Yes | Yes |
| MCP server build | Yes | Yes | Yes | Yes |
| Claude Code CLI | Yes | Yes | No | Yes |
| Claude for Excel | Yes | N/A | Yes | N/A |

---

## Quick Start (2 Minutes)

**macOS / Linux / WSL2:**

```bash
git clone https://github.com/MindsetConsulting/sflight-mcp.git
cd sflight-mcp
bash scripts/setup.sh   # installs deps, builds MCP server, creates .mcp.json
cds watch
```

**Windows (PowerShell):**

```powershell
git clone https://github.com/MindsetConsulting/sflight-mcp.git
cd sflight-mcp
powershell -ExecutionPolicy Bypass -File scripts\setup.ps1
cds watch
```

**Manual steps (any platform):**

```bash
git clone https://github.com/MindsetConsulting/sflight-mcp.git
cd sflight-mcp
npm install
cd mcp-server && npm install && npm run build && cd ..
cds watch
```

Open [http://localhost:4004](http://localhost:4004) — you'll see the service running with all 26 entities and 4 views. That's it. The built-in SQLite database loads all 24 CSV files automatically.

**Try these URLs:**

| URL | What You'll See |
|-----|----------------|
| `/odata/v4/flights/$metadata` | Full OData metadata document |
| `/odata/v4/flights/Carriers` | All 24 airlines |
| `/odata/v4/flights/Flights?$top=5` | First 5 flight records |
| `/odata/v4/flights/Flights?$filter=CITYTO eq 'NEW YORK'` | Flights to New York |
| `/odata/v4/flights/getFlightsOnDate(flightDate=2025-03-15)` | Flights on a specific date |

---

## Setting Up AI-Powered Data Exploration

This is where it gets exciting. The built-in MCP server lets you ask questions about your flight data in plain English through **Claude Code** (Anthropic's AI coding assistant). Instead of writing SQL or OData queries yourself, you simply ask:

> *"Which airlines fly to London?"*
> *"What's the total passenger occupancy across all airlines?"*
> *"Show me the top 5 routes by number of bookings"*

Claude uses the 17 MCP tools behind the scenes to inspect the data model, run SQL queries, and give you formatted answers.

### What is MCP?

**MCP (Model Context Protocol)** is an open standard that lets AI assistants use tools. Think of it like a USB port for AI — it's a standard way to plug capabilities (tools) into an AI assistant. Instead of the AI guessing, it can actually look at your database schema, run real queries, and give you accurate answers.

### Step 1: Install Claude Code

Claude Code is a command-line AI assistant. Install it once:

```bash
npm install -g @anthropic-ai/claude-code
```

> You'll need an [Anthropic API key](https://console.anthropic.com/) or a Claude subscription.

### Step 2: Build the MCP Server

The MCP server lives inside this project at `mcp-server/`. Build it:

```bash
cd mcp-server
npm install
npm run build
cd ..
```

### Step 3: Configure Claude Code

Create a file called `.mcp.json` in the project root:

```json
{
  "mcpServers": {
    "cap-tools": {
      "command": "node",
      "args": ["/sflights-mcp/mcp-server/build/index.js"]
    }
  }
}
```

> Replace `` with the actual path on your machine.
> Example on Mac: `/Users/yourname/projects/sflights-mcp/mcp-server/build/index.js`
> Example on Windows: `C:/Users/yourname/projects/sflights-mcp/mcp-server/build/index.js`

### Step 4: Start Asking Questions

Open a terminal in the project folder and launch Claude Code:

```bash
claude
```

Now just ask questions in plain English:

```
You: Which airlines have the most passengers?
You: Show me all flights from Frankfurt to New York
You: What's the average ticket price by airline?
You: How many bookings were made in 2025?
```

Claude will use the MCP tools to query your data and give you formatted answers with actual numbers.

**Business users on Excel?** Claude for Excel can connect to this same data via MCP connectors. See [Claude for Excel Integration](docs/claude-for-excel.md).

### Example Questions (with SQL Patterns)

Not sure what to ask? Here are 10 real questions that the MCP server can answer, ordered by complexity:

| # | Question | SQL Pattern | Key Tables |
|---|----------|------------|------------|
| 1 | *"List all airlines and their currency"* | `SELECT CARRID, CARRNAME, CURRCODE FROM flights_Carriers` | Carriers |
| 2 | *"What currencies do airlines use?"* | `SELECT DISTINCT CURRCODE` | Carriers |
| 3 | *"Which airlines fly to New York?"* | 3-table JOIN: Carriers → Connections → Flights | Carriers, Connections, Flights |
| 4 | *"Average seat occupancy rate per airline"* | `JOIN + CAST(SEATSOCC AS FLOAT)/SEATSMAX*100 + AVG` | Flights, Carriers |
| 5 | *"Top 5 busiest routes by booking count"* | 4-table JOIN + `GROUP BY CITYFROM, CITYTO` + `ORDER BY COUNT(*) DESC` | Connections, Flights, Bookings, Carriers |
| 6 | *"Revenue estimate per airline"* | `SUM(PRICE * SEATSOCC)` grouped by carrier | Flights, Carriers |
| 7 | *"Flights with >80% occupancy"* | Derived column: `SEATSOCC*100.0/SEATSMAX > 80` | Flights, Carriers, Connections |
| 8 | *"Which travel agencies book the most?"* | `JOIN + COUNT + ORDER BY DESC` | Bookings, TravelAgencies |
| 9 | *"Monthly booking trends"* | `strftime('%Y-%m', FLDATE) + GROUP BY` | Bookings, Flights |
| 10 | *"Multi-hop route analysis (connecting flights)"* | Self-join on Connections: `c1.CITYTO = c2.CITYFROM` | Connections, Carriers |

> These questions are tested in `test/mcp-test-questions.sh`. The full 20-question test suite is in `test/mcp-questions.http`.

---

## MCP Server — 19 Tools

The MCP server provides 19 tools, all prefixed with `cap_`:

### Model Introspection
| Tool | What It Does |
|------|-------------|
| `cap_entities` | Lists all 26 entities + 4 views with key fields, associations, and compositions |
| `cap_entity_detail` | Full definition of any entity — all fields, types, and relationships |
| `cap_associations` | All 59 navigation properties with cardinality and target entities |
| `cap_nav_map` | Complete navigation graph with SQL JOIN ON clauses — essential for multi-table queries |
| `cap_services` | Service definitions with entity counts and function/action counts |

### Schema & Compilation
| Tool | What It Does |
|------|-------------|
| `cap_compile` | Compiles CDS to json, edmx, sql, hdbcds, hdbtable, or yaml |
| `cap_edm` | Generates OData V4 EDMX metadata document |

### Data Queries (SQL)
| Tool | What It Does |
|------|-------------|
| `cap_cql_query` | Runs any SQL query against in-memory SQLite with all seed data loaded |
| `cap_data_stats` | Row counts for every entity — see data volume at a glance |
| `cap_sample_data` | Preview rows from any entity — see actual values and column names |
| `cap_db_schema` | Complete database schema with column names, types, and keys |

### Analytics
| Tool | What It Does |
|------|-------------|
| `cap_data_distribution` | Group any entity by any column and see counts + percentages — flights by year, bookings by class, etc. |
| `cap_yoy_growth` | Year-over-year growth analysis with % change — flight trends, booking trends, revenue growth |

### Project Management
| Tool | What It Does |
|------|-------------|
| `cap_csv_inspect` | Browse CSV seed data files and preview contents |
| `cap_project_info` | Package.json, CDS config, and all dependencies |
| `cap_mta_info` | MTA deployment descriptor details |
| `cap_build` | Run CDS build (development or production) |
| `cap_hana_mapping` | CDS entity to HANA artifact name mapping |
| `cap_query` | OData query against a running CAP service (requires `cds watch`) |

### Which MCP Server Should I Use?

| Scenario | MCP Server | Why |
|----------|-----------|-----|
| Local development (no database) | Built-in cap-tools (19 tools) | SQLite + CSV seed data, zero setup |
| Local with HANA Cloud | cap-tools + hana-cli | Both configured in `.mcp.json` |
| Deployed on BTP (API consumers) | OData V4 endpoint | Standard REST/OData at `/odata/v4/flights` |
| **Excel — local, macOS/Linux (Path A)** | supergateway + cap-tools | `bash scripts/start-mcp-sse.sh` → `localhost:8080` |
| **Excel — local, Windows (Path A)** | supergateway + cap-tools | `scripts\start-mcp-sse.ps1` → `localhost:8080` |
| **Excel — BTP deployed (Path B)** | gavdi/cap-mcp plugin | HTTP/SSE at `/mcp`, no admin needed (see note below) |
| Quick PoC, no code changes | odata_mcp_go | Auto-discovers from `$metadata`, single binary |

> **No org admin? No problem.** Register the MCP URL as a *personal connector* in your own `claude.ai` Settings → Integrations. No organization admin needed for personal testing.
> See the full guide: [Testing Without an Org Admin](docs/claude-for-excel.md#testing-without-an-org-admin--two-paths).

> For detailed setup instructions, see [External Integration Paths](docs/integration-paths.md) and [Claude for Excel Integration](docs/claude-for-excel.md).

---

## How MCP Works — Architecture Diagrams

### 1. MCP Protocol — How AI Talks to Tools

This is the general pattern for any MCP server in JavaScript/TypeScript. The AI assistant (Claude Code) communicates with your tools through a standardized protocol over stdin/stdout:

```mermaid
sequenceDiagram
    participant User as You (Terminal)
    participant CC as Claude Code
    participant MCP as MCP Server (Node.js)
    participant Tool as Tool Handler

    User->>CC: "Which airlines fly to London?"
    activate CC
    Note over CC: AI decides which tool to call

    CC->>MCP: {"method": "tools/call", "params": {"name": "cap_cql_query", "arguments": {"sql": "SELECT ..."}}}
    activate MCP
    Note over CC,MCP: JSON-RPC over stdin/stdout

    MCP->>Tool: Execute cap_cql_query handler
    activate Tool
    Tool->>Tool: Boot CDS + SQLite in-memory
    Tool->>Tool: Load 24 CSV seed files
    Tool->>Tool: Run SQL query
    Tool-->>MCP: {rows: [...], count: 5}
    deactivate Tool

    MCP-->>CC: {"result": {"content": [{"type": "text", "text": "## Query Results\n..."}]}}
    deactivate MCP

    Note over CC: AI formats the answer in plain English
    CC-->>User: "5 airlines fly to London: Lufthansa, British Airways, ..."
    deactivate CC
```

**Key concept:** The AI doesn't guess — it actually runs real queries against your real data and gives you accurate answers.

### 2. hana-cli MCP Server — Direct Database Access

The `hana-cli` MCP server (a separate npm package) connects directly to SAP HANA Cloud and provides low-level database operations:

```mermaid
sequenceDiagram
    participant CC as Claude Code
    participant HM as hana-cli MCP Server
    participant HANA as SAP HANA Cloud

    CC->>HM: tools/call: "tables" (list all tables)
    activate HM
    HM->>HANA: SQL: SELECT * FROM SYS.TABLES
    HANA-->>HM: Table list
    HM-->>CC: Formatted table list
    deactivate HM

    CC->>HM: tools/call: "inspectTable" (show columns)
    activate HM
    HM->>HANA: SQL: SELECT * FROM SYS.TABLE_COLUMNS WHERE TABLE_NAME = '...'
    HANA-->>HM: Column definitions
    HM-->>CC: Column details with types and keys
    deactivate HM

    CC->>HM: tools/call: "querySimple" (run SQL)
    activate HM
    HM->>HANA: User's SQL query
    HANA-->>HM: Result set
    HM-->>CC: Query results as markdown
    deactivate HM

    Note over CC,HANA: hana-cli requires HANA Cloud credentials(default-env.json or CF service binding)
```

### 3. CAP MCP Server — Application-Level Intelligence

Our embedded CAP MCP server adds **application-level understanding** on top of raw database access. It knows about CDS entities, associations, OData services, and seed data — not just tables and columns:

```mermaid
sequenceDiagram
    participant User as You
    participant CC as Claude Code
    participant CAP as CAP MCP Server(this project)
    participant CDS as CDS Compiler
    participant SQLite as In-Memory SQLite
    participant CSV as 24 CSV Files

    User->>CC: "What's the busiest route?"

    Note over CC: AI has 16 cap_ tools available+ hana-cli tools if configured

    CC->>CAP: cap_db_schema (understand table structure)
    activate CAP
    CAP->>CDS: cds.load('*') — compile CDS model
    CDS-->>CAP: Compiled model (CSN)
    CAP->>SQLite: Deploy model to in-memory DB
    CAP->>CSV: Load all 24 seed data files
    CSV-->>SQLite: 5,000 flights, 10K bookings, ...
    SQLite-->>CAP: Schema with column names and types
    CAP-->>CC: Schema details
    deactivate CAP

    CC->>CAP: cap_cql_query (run the actual query)
    activate CAP
    Note over CAP: Uses cached model (0ms)
    CAP->>SQLite: SELECT CITYFROM, CITYTO, COUNT(*)FROM flights_ConnectionsGROUP BY CITYFROM, CITYTOORDER BY COUNT(*) DESC
    SQLite-->>CAP: Top routes by flight count
    CAP-->>CC: Formatted results
    deactivate CAP

    CC-->>User: "The busiest route is Frankfurt → New Yorkwith 47 connections across 8 airlines"

    Note over CAP,SQLite: No HANA needed — works offlinewith SQLite + CSV seed data
```

**Why both servers?** Use them together for the best experience:
- **CAP MCP Server** — works offline, understands CDS models, great for development and data exploration
- **hana-cli MCP Server** — connects to live HANA Cloud, needed for production data and database administration

---

## Integration Guides

| Guide | Description |
|-------|------------|
| [Claude for Excel](docs/claude-for-excel.md) | Connect flight data tools to Claude's Excel add-in. Covers **Path A** (local, Windows + macOS, no admin) and **Path B** (BTP deployed, no admin). Includes architecture diagrams, per-platform scripts, and how to promote to full team access. |
| [External Integration Paths](docs/integration-paths.md) | Three ways to connect external MCP servers: gavdi/cap-mcp (merged plugin), CData OData (standalone Java), odata_mcp_go (standalone Go) — architecture comparison and step-by-step setup |

---

## Deploying to SAP BTP Cloud Foundry

This section walks you through deploying the application to your own BTP account. You'll end up with a live OData service backed by HANA Cloud.

### Prerequisites

| What | Why |
|------|-----|
| SAP BTP account | [Get a free trial](https://www.sap.com/products/technology-platform/trial.html) or use your company's account |
| Cloud Foundry CLI | `brew install cloudfoundry/tap/cf-cli@8` (Mac) or [download here](https://github.com/cloudfoundry/cli/releases) |
| MTA Build Tool | `npm install -g mbt` |
| HANA Cloud instance | Needed for the database — provision one in BTP Cockpit |

### Step-by-Step Deployment

**1. Login to Cloud Foundry**

```bash
cf login -a https://api.cf..hana.ondemand.com
# Example regions: us10, eu10, ap21
# Select your org and space when prompted
`

…

## Source & license

This open-source MCP server is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [MindsetConsulting](https://github.com/MindsetConsulting)
- **Source:** [MindsetConsulting/sflight-mcp](https://github.com/MindsetConsulting/sflight-mcp)
- **License:** Unlicense
- **Homepage:** https://mindsetconsulting.com

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/mcp-mindsetconsulting-sflight-mcp
- Seller: https://agentstack.voostack.com/s/mindsetconsulting
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.