# OpsPilot MCP Server

An AI Operations Investigation MCP server that enables LLMs (Claude, ChatGPT, etc.) to investigate order fulfillment incidents by correlating data across independent services — and to conclude every investigation with a meaningful human-review escalation ticket.

OpsPilot reads operational data from a **PostgreSQL** database. The synthetic scenario files remain the authoring format, but at runtime the data is seeded into PostgreSQL and served through SQL-backed repositories. The MCP tools never read scenario files directly.

## Problem

Order fulfillment spans independent services: order, payment, inventory, shipment, and event logs. When an order fails (e.g., payment captured but never shipped), the root cause lives across those services — no single system holds the answer. Teams today diagnose by manually stitching together logs and dashboards, which is slow, inconsistent, and dependent on tribal knowledge. The operational problem is not a lack of data but a lack of correlation: given an order ID, what happened, why, and who should act?

OpsPilot gives an AI assistant a safe, structured way to gather all evidence for an order, reason about the root cause, and end with a human-review escalation the responsible team can act on.

## Target User

- **Operations and support analysts** who field order-failure tickets and need a fast, consistent first-pass diagnosis.
- **On-call engineers** who want a bounded investigation before diving into the services themselves.
- **Platform teams building AI assistants for ops** who want a read-only, safely deployable way for LLMs to correlate production data without granting write access.

The direct consumer of the server is an LLM client over MCP; the end beneficiary is the human team that resolves the order.

## Product Workflow

Every incident follows the same journey: **Incident → AI Investigation → Diagnosis → Human Review Escalation**.

```mermaid
graph LR
    A[Incident] -->|investigateOrder| B[AI Investigation]
    B -->|reasoning| C[Diagnosis]
    C -->|createEscalation| D[Human Review Escalation]
    D -->|persisted| E[PostgreSQL]
```

1. **Incident** — an order fails fulfillment: stuck, blocked, or flagged (e.g., payment captured, no shipment).
2. **AI Investigation** — the LLM calls `investigateOrder(orderId)` to gather order, payment, inventory, shipment, events, and incident metadata in one structured response, or uses the targeted `get*` tools to verify a hypothesis. Each tool reads its data from PostgreSQL and returns a human-readable operations summary alongside the structured data.
3. **Diagnosis** — the LLM correlates the evidence to identify the root cause, severity, and whether it is retryable.
4. **Human Review Escalation** — the LLM calls `createEscalation(orderId)` to raise a structured review ticket for the responsible team. The ticket is persisted in PostgreSQL. This is the terminal step: no high-risk mutation (cancel, refund, restock, reship) is ever taken automatically.

The escalation step is **idempotent**: calling `createEscalation` twice for the same order returns the same existing escalation instead of creating a duplicate.

## Architecture

```mermaid
graph TD
    User -->|prompt| LLM
    LLM -->|tool call| Tools[MCP Tools]
    Tools -->|SQL query| Repos[Repositories]
    Repos -->|SQL query| PG[(PostgreSQL)]

    subgraph Boot[Startup]
        S[Scenario Files] -->|authoring format| Seed[Seed Process]
        Seed -->|INSERT| PG
        Migrations -->|CREATE schema| PG
    end
```

**Data flow:**

```
Scenario files  →  Database seed process  →  PostgreSQL  →  Repositories  →  MCP Tools
```

- **Scenario files** (`src/scenarios/`) are the authoring format for representative, synthetic incident data. They are *not* the runtime data source.
- A **database seed process** (`src/db/seed.ts`) reads every scenario on startup and inserts the orders, payments, inventory reservations, shipments, events, and incident metadata into PostgreSQL.
- **Migrations** (`src/db/migration.ts`) create the schema (tables, foreign keys, indexes) before seeding.
- **Repositories** query PostgreSQL during runtime. They never read scenario arrays directly.
- **MCP tools** call repositories. They never read scenario files directly.

**Key principle:** Scenarios remain the authoring format. Every new incident is a new scenario file; after seeding, repositories and tools automatically support the new scenario without modification. PostgreSQL is the runtime source of truth.

## Product Decisions

### Why MCP Is Central

The consumer is an LLM, so the interface must be one the model can discover and call reliably. MCP is the emerging standard for giving LLMs structured, typed, self-describing capabilities: tools declare their input schemas, return machine-parseable `structuredContent`, and describe intent rather than endpoints. Claude, ChatGPT, or any MCP-capable client can investigate orders with zero per-client integration.

### Why the System Is Read-Only

Investigation is a read problem. The moment a tool can cancel, refund, restock, or reship, a misbehaving model can damage business state. Every capability here only reads data from PostgreSQL. The one exception, `createEscalation`, writes a review record — never business state — so the server stays safe to deploy even with an unconstrained LLM.

### Why Investigation Ends with Escalation

A diagnosis alone is not a workflow outcome. Without a terminal step, an investigation produces an answer with no owner and no action. Escalation gives the workflow a bounded, safe, traceable conclusion: a structured ticket assigned to the responsible human team, with evidence references attached. It keeps every operational decision with humans while still delivering a complete loop.

### Why Representative Scenarios Are Used

Real production data is sensitive and unavailable in a demonstration. Representative scenarios encode realistic cross-service failure patterns (warehouse timeout, payment failure, out of stock, courier outage, fraud review) in one place. They are the seed source for PostgreSQL, which makes the authoring principle provable: add a scenario file, register it, and after seeding every repository and tool supports the new incident without modification.

### Why PostgreSQL Is the Runtime Source of Truth

In-memory scenario arrays cannot survive restarts, cannot be shared across instances, and cannot guarantee durable workflow state. With PostgreSQL, seeded data and created escalations persist. Escalations in particular need durability (they are workflow state a human team depends on), atomicity (a partially-created escalation must never exist), and idempotency (a repeated call must not duplicate a ticket) — all of which a relational store provides.

## Why This Is AI-Native

The server is built for an LLM as its primary consumer, not for a human clicking endpoints. All reasoning is delegated to the MCP client: the server exposes structured capabilities, not answers. The LLM decides which tools to call, in what order, and how to interpret the evidence. MCP provides the pieces that make this work:

- **Discoverable schemas** — the model knows each tool's arguments and purpose without documentation.
- **StructuredContent** — evidence returns in a typed, machine-parseable shape the model can reason over reliably.
- **Intent-shaped tools** — `investigateOrder`, `createEscalation`, and the `get*` tools read like capabilities the model composes fluently.
- **Dashboard-style summaries** — every tool also returns a concise, deterministic human-readable summary (root cause, key evidence, recommended next step), so the same capability reads well for a human reviewer or during a demo.

The workflow itself (when to investigate, when to escalate) is an LLM decision made legible by the structured capabilities. This is AI-native by design, not a REST API with an LLM bolted on.

## Assumptions

- The MCP client can follow instructions and reason over structured evidence.
- Each order is uniquely identifiable and maps to exactly one incident.
- Scenario data faithfully models realistic failure patterns; a production deployment would source the same shapes from live services and event streams.
- Escalations are append-only review records; humans resolve and close them out-of-band.
- A PostgreSQL instance is reachable at `DATABASE_URL` (migrations and seed run automatically at startup).
- Request volume is low; no auth, tenancy, or rate limiting is required.
- The consumer is an LLM client over MCP, not a human-facing UI.

## In Scope

- Read-only investigation across order, payment, inventory, shipment, and event data.
- Root-cause diagnosis via incident metadata (root cause code, severity, retryability).
- Escalation creation as a structured human-review ticket with evidence references, persisted in PostgreSQL.
- Scenario-driven extensibility: new incident types with no changes to tools or repositories.
- HTTP transport usable from the MCP Inspector.

## Out of Scope

- Any mutation of business data: cancel, refund, restock, reship, or payment changes.
- Live monitoring or webhook ingestion; live data source integration.
- Auto-remediation or auto-resolution of incidents.
- Escalation lifecycle: assignment, SLA, comments, close/reopen.
- Authentication, authorization, tenancy, and rate limiting.
- A human-facing UI or dashboard.

## Tradeoffs

- **Representative scenarios over live services.** Deterministic, testable, and safe to demo; the tradeoff is that data is not real-time.
- **PostgreSQL-backed persistence over in-memory objects.** Durable, restart-safe, and testable against a real store; the tradeoff is that a database must be provisioned (handled automatically by Railway).
- **Escalation over auto-fix.** Keeps high-risk decisions with humans and makes every workflow safe; the tradeoff is that resolution requires a human step and is slower.
- **Granular tools plus an orchestrator over one mega-tool.** The LLM can query targeted domains or gather everything at once; the tradeoff is more tool calls and a larger surface area.
- **SQL repositories over scenario-array reads.** Repositories are shaped like a real data layer and query PostgreSQL at runtime; the tradeoff is that scenario changes require a re-seed to take effect.
- **Evidence references over raw data copies.** Escalations stay small and avoid leaking sensitive payloads; the tradeoff is that humans must look up referenced records.
- **Intentionally not built:** real data sources, auth, escalation lifecycle, and auto-remediation. Keeping these out of scope preserves focus on the product pattern and keeps the system safe.

## Prerequisites

- Node.js 20+
- A running PostgreSQL instance. Locally, you can start one with Docker:

```bash
docker run -d --name opspilot-pg -p 5432:5432 \
  -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=opspilot \
  postgres:16-alpine
```

## Quick Start

```bash
npm install

# DATABASE_URL is required — point it at a PostgreSQL instance
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/opspilot"

# Development
npm run dev

# Build & production
npm run build
npm start
```

On startup the server automatically:

1. Runs SQL migrations (`src/db/migration.ts`) to create the schema.
2. Runs the seed process (`src/db/seed.ts`) to load the synthetic scenarios into PostgreSQL.
3. Starts listening on `http://localhost:3000`.

Connect via the MCP Inspector:

```bash
npx @modelcontextprotocol/inspector --transport http http://localhost:3000/mcp
```

Run the tests with `npm test`. Tests run against a real PostgreSQL database (no mocks) and reset the database between tests.

## Example Prompt

> "Investigate ORD-1003, tell me the root cause, then escalate it for human review."

## Representative Investigation

### ORD-1003 — Inventory Out of Stock

The LLM calls `investigateOrder({ orderId: "ORD-1003" })`. The tool reads order, payment, inventory, shipment, events, and incident data from PostgreSQL and returns both `structuredContent` and a dashboard-style summary:

```
Investigation completed for order ORD-1003.

Root Cause:
Requested SKU is out of stock in the assigned warehouse.

Evidence:
• Payment captured successfully.
• Inventory reservation failed: SKU-MACBOOK16 is out of stock in warehouse WH-02.
• Shipment was not created.
• Inventory Service reported (ERROR): Inventory reservation failed for ORD-1003: SKU-MACBOOK16 is out of stock in warehouse WH-02.

Recommended next step:
Create a human-review escalation.
```

The LLM reports root cause `INVENTORY_OUT_OF_STOCK` (HIGH, not retryable), then calls `createEscalation({ orderId: "ORD-1003" })`, producing escalation **ESC-1003** for the **Inventory Team**, status `OPEN`, with evidence references to the order, payment, SKU, event IDs, and root cause code.

## Escalations

`createEscalation({ orderId })` is the final step of every investigation. It retrieves the full investigation context through the repositories and produces a structured escalation record persisted in PostgreSQL:

| Field | Description |
|---|---|
| `escalationId` | Auto-generated, sequential (`ESC-1001`, `ESC-1002`, ...) |
| `team` | Responsible human team, derived from the incident metadata |
| `priority` | Mapped from the incident severity (`LOW` / `MEDIUM` / `HIGH` / `CRITICAL`) |
| `reason` | Why the order needs human review |
| `rootCause` | Root cause code (e.g., `WAREHOUSE_TIMEOUT`) |
| `summary` | Concise incident summary |
| `evidence` | References to Order, Payment, Inventory, Shipment, Events, and Incident — never copies of raw data |
| `status` | Always `OPEN` |
| `createdAt` | ISO timestamp |

Scenario-to-team mapping:

| Scenario | Assigned Team |
|---|---|
| Payment Failed | Payments Team |
| Warehouse Timeout / Courier Outage | Fulfillment Team |
| Inventory Out of Stock | Inventory Team |
| Fraud Review | Fraud Team |

Escalation IDs are generated sequentially from a database sequence (`ESC-1001`, `ESC-1002`, ...). The exact number depends on how many escalations already exist, so examples in this document use readable IDs for illustration.

The tool returns `structuredContent` with the escalation, investigation summary, assigned team, priority, status, reason, and evidence references, plus a natural-language confirmation, e.g.:

```
Human-review escalation created successfully.

Escalation ID: ESC-1003
Assigned Team: Inventory Team
Priority: HIGH
Status: OPEN

The complete investigation context has been attached for review.
```

**Idempotency:** calling `createEscalation` again for the same order does not create a second ticket. The tool returns the existing escalation, and the database enforces a single escalation per order.

`createEscalation` is **atomic** (the ticket is written inside a single transaction), **durable** (it is persisted to PostgreSQL and survives restarts), and **idempotent** (repeated requests return the existing escalation, and concurrent requests all receive that same escalation).

## Concurrent Idempotency

Concurrent requests are a concern because two parallel `createEscalation` calls for the same order must never yield two tickets. PostgreSQL guarantees this at the database level: `escalations.order_id` is UNIQUE, and the insert uses `ON CONFLICT (order_id) DO NOTHING RETURNING`. Exactly one request wins; every other transaction is skipped by PostgreSQL, waits for the winner to settle, and reads back the same committed row. The result is a single escalation per order, identical for every concurrent caller, and duplicate-key errors are never exposed to callers. This behavior is verified by PostgreSQL-backed concurrent tests that fire 50 parallel requests at one order.

## Supported Scenarios

| Scenario | Root Cause Code | Severity | Retryable |
|---|---|---|---|
| Warehouse Timeout | WAREHOUSE_TIMEOUT | HIGH | Yes |
| Payment Failed | PAYMENT_FAILED | HIGH | Yes |
| Inventory Out of Stock | INVENTORY_OUT_OF_STOCK | HIGH | No |
| Courier Outage | COURIER_OUTAGE | CRITICAL | Yes |
| Fraud Review | FRAUD_REVIEW | MEDIUM | No |

## Folder Structure

```
src/
├── index.ts                   # Express entry point; runs migrations + seed, then listens
├── server/                    # MCP server setup & HTTP transport
├── tools/                     # MCP tools: investigateOrder, createEscalation, get* lookups, ping
│   └── messages.ts            # Deterministic human-readable summaries for each tool
├── repositories/              # SQL-backed read/data access against PostgreSQL
├── scenarios/                 # One file per incident type — authoring format for synthetic data
├── db/
│   ├── client.ts              # pg Pool (DATABASE_URL) — shared connection manager
│   ├── migration.ts           # SQL migrations — creates tables, FKs, indexes
│   ├── seed.ts                # Reads scenarios and inserts them into PostgreSQL
│   └── bootstrap.ts           # Runs migrations + seed at startup
├── types/                     # Shared immutable domain types
└── __tests__/                 # PostgreSQL-backed repository and tool tests
```

### The database layer

| File | Purpose |
|---|---|
| `src/db/client.ts` | Owns the shared `pg` Pool. Requires `DATABASE_URL` (no default). Exposes `getPool`, `closePool`, and `resetPool` (used in tests to simulate a restart). |
| `src/db/migration.ts` | Raw SQL migrations. Creates the tables (`orders`, `payments`, `inventory_reservations`, `shipments`, `system_events`, `incidents`, `escalations`), foreign keys, indexes, and the escalation ID sequence. Tracks applied migrations in `schema_migrations`. |
| `src/db/seed.ts` | Reads every `InvestigationScenario` and inserts its order, payment, inventory reservation, shipment, events, and incident metadata into PostgreSQL inside a single transaction. Idempotent (`ON CONFLICT DO NOTHING`). |
| `src/db/bootstrap.ts` | `initializeDatabase()` — runs migrations then seed. Called on startup before the HTTP server listens. |

## Running Tests Locally

Tests run against a **real PostgreSQL database** — no production credentials are required. Point `DATABASE_URL` at a local PostgreSQL instance, then run the suite:

```bash
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/opspilot"
npm test
```

Migrations run automatically before the suite, and synthetic scenario data is seeded automatically between tests. (Refer to the [Prerequisites](#prerequisites) section for a one-line Docker command that starts a local instance.)

## Testing

All tests run against a **real PostgreSQL database** (no mocks, no in-memory objects). The vitest setup runs migrations, then truncates and re-seeds the database between tests:

- `src/__tests__/repositories.test.ts` — repository tests: orders, payments, shipments, inventory, events, and incidents are retrieved from the database.
- `src/__tests__/tools.test.ts` — MCP tool tests: tool contracts, `structuredContent`, and human-readable responses.
- `src/__tests__/escalationRepository.test.ts` — escalation tests: creation, team/priority mapping, idempotency, and persistence across a simulated restart.
- `src/__tests__/concurrentEscalation.test.ts` — concurrent idempotency tests (50 parallel requests per order), database constraint tests (UNIQUE and foreign-key), and transaction rollback tests.
- `src/__tests__/investigationSummary.test.ts` — verifies the deterministic investigation summaries.

## Deployment (Railway)

The repository ships with `railway.json`, which:

1. **Provisions PostgreSQL** as a service (`opspilot-postgres`).
2. **Configures `DATABASE_URL`** — the app service references the database service's connection string automatically.
3. The app container runs **migrations** and the **seed script** automatically on startup before serving requests.

Deploy the repository to Railway and the app uses PostgreSQL automatically. This is a fully PostgreSQL-backed deployment: migrations run and the seed script runs automatically at boot, and all workflow state (seeded data and created escalations) is durable in PostgreSQL. For manual deployments, set `DATABASE_URL` and run the server; migrations and seeding happen at boot.

## Quick Start for Reviewers

The server ships with five synthetic orders, pre-seeded into PostgreSQL on startup. Each represents a distinct incident pattern:

| Order | Scenario | Root Cause Code |
|---|---|---|
| `ORD-1001` | Warehouse Timeout | WAREHOUSE_TIMEOUT |
| `ORD-1002` | Payment Failed | PAYMENT_FAILED |
| `ORD-1003` | Inventory Out of Stock | INVENTORY_OUT_OF_STOCK |
| `ORD-1004` | Courier Outage | COURIER_OUTAGE |
| `ORD-1005` | Fraud Review | FRAUD_REVIEW |

### Example session

1. Start the server (see [Quick Start](#quick-start)) and connect the MCP Inspector.
2. Prompt the LLM:
   > Investigate order ORD-1001.
   > Then create a human-review escalation.
3. **Expected outcome:** the LLM calls `investigateOrder({ orderId: "ORD-1001" })`, which returns a structured investigation plus a dashboard-style summary (`Root Cause: WAREHOUSE_TIMEOUT`, evidence bullets, recommended next step). The LLM then calls `createEscalation({ orderId: "ORD-1001" })`, which persists an escalation (e.g., `ESC-1001`) for the Fulfillment Team with status `OPEN` and confirms: "Human-review escalation created successfully."
4. Calling `createEscalation` again for `ORD-1001` returns the same escalation — no duplicate ticket is created.

The manual verification flow is:

```
Investigate ORD-1001
↓
Create Escalation
↓
Create Escalation again
↓
Same escalation returned
```

The [concurrent idempotency tests](#concurrent-idempotency) (50 parallel requests per order) provide automated verification of this behavior.

## Future Improvements

- Additional scenario patterns (e.g., address validation failures, carrier damage claims)
- Scenario probability scoring based on event patterns
- Paginated event retrieval for high-volume orders
- Webhook-based scenario injection from external monitoring systems

## Documentation

See [docs/architecture.md](docs/architecture.md) for detailed architecture explanation and [docs/investigation-flow.md](docs/investigation-flow.md) for investigation workflow.