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.

Related MCP server: mcp-incident-responder

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.

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

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:

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

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:

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:

Scenario-to-team mapping:

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

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

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:

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 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:

Example session

1. Start the server (see 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 (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 for detailed architecture explanation and docs/investigation-flow.md for investigation workflow.