Ask Your Store: Build an MCP Sales Analyst with DEVUP AI

A store owner asks a straightforward question:

“Compare the last two weeks and show me the three products that sold the most.”

Before an assistant can answer, the application needs to resolve several details. Which dates define each week? Do cancelled orders count? Does “most” mean units or merchandise value? What happens when the previous period contains no sales?

These decisions belong in a small, explicit analytics layer. Once that layer exists, an agent can select tools, retrieve the results, and explain them in the language the user prefers.

In this tutorial, we will build that system with TypeScript, SQLite, a local Model Context Protocol server, and an optional DEVUP AI agent.

The companion project contains a working MCP server, a reproducible fixture, automated tests, and the figures in this article. The local demonstration launches a real server and retrieves real SQL aggregates from synthetic data. It runs without an API key. The live DEVUP AI path is included separately; no live model result is claimed here.

Source code: DEVUP MCP Sales Analytics on GitHub.

What we are building

Our server exposes four tools:

Tool	Responsibility
describe_dataset	Explain coverage, currency, synthetic status and the metric definition
sales_summary	Return a total, distinct order count, units and average order value
compare_periods	Compare two explicit windows and calculate the percentage change
top_products	Rank a bounded number of products by merchandise value

The agent has no SQL tool. The server implements the queries, binds arguments, and opens the database through a read-only connection. The model chooses among the available operations.

The SDK runs MCP calls locally. Questions and aggregate results are sent to the DEVUP AI API during live runs; the database file stays on the machine.

This is a local stdio integration. We do not deploy an HTTP MCP service or ask the remote model endpoint to connect to our laptop. The agent host launches a child process, discovers its tools, and exchanges MCP messages over stdin and stdout.

1. Start with a reproducible run

Use Node.js 24.19.0, the runtime used for validation, and npm. Clone the companion project and open a terminal in its root directory.

git clone https://github.com/mohamed-bal/devup-mcp-sales-analytics.git
cd devup-mcp-sales-analytics
npm ci
npm test
npm run typecheck
npm run setup
npm run demo

npm run setup creates data/store.sqlite from fixtures/store.json. It refuses to replace an existing file. On later runs, keep the database and run the demo directly.

The demo writes out/demo.json. A captured copy is shipped as examples/demo.json, so you can compare your output with the article before making any model requests.

The project pins MCP client and server packages to 2.3.1, the Agents SDK to 0.21.0, and Zod to 4.6.7. Keep package-lock.json: the package split and schema interfaces matter when reproducing an SDK tutorial.

Node runs the .mts files directly. Type stripping does not type-check the application; npm run typecheck is a separate step.

The source is deliberately small:

File	Purpose
src/contracts.mts	Dates, windows, schemas and integer formatting
src/setup.mts	Validate the teaching fixture and create the database
src/analytics.mts	Implement the predefined queries
src/server.mts	Publish four MCP tools
src/client.mts	Launch and connect to the server
src/demo.mts	Exercise MCP without a model
src/live-agent.mts	Connect the agent, tools and DEVUP AI
tests/	Check calculations, protocol behavior and SDK integration

2. Give “sales” a precise meaning

Our metric is called delivered_merchandise_total:

Sum(quantity × unit price)
for orders whose snapshot status is delivered
within the requested order-date window.

Prices are stored in integer centimes. Each DZD contains 100 centimes. The fixture has no tax, shipping or discount fields. Cancelled and returned orders are excluded entirely.

This is a teaching metric for a fixed snapshot. It does not describe cash collected or profit. If an order placed on October 1 was delivered on October 5, our query groups it by its order date. The fixture has no delivery-event history to reconstruct a delivery-date report.

A real commerce application with partial refunds, discounts, multiple currencies or evolving fulfillment statuses needs a richer contract and schema.

The fixture declares complete coverage from 2026-10-01 inclusive to 2026-10-15 exclusive. Within that coverage, an empty day can legitimately return zero. Outside that coverage, the tool returns an error. This distinction prevents missing data from being reported as poor performance.

The two comparison windows are:

• Previous: October 1 through October 7, represented as [2026-10-01, 2026-10-08).
• Current: October 8 through October 14, represented as [2026-10-08, 2026-10-15).

Adjacent windows share a boundary without counting an order twice. Dates are calendar-date strings; this lab does not infer a timezone from timestamps.

3. Validate requests before querying

The server accepts a strict object for each operation. A summary request has two fields:

export const windowSchema = z.strictObject({
  start: date,
  end: date,
});

The date validator checks both the YYYY-MM-DD shape and the actual calendar date. A regular expression alone would accept 2026-02-30.

The analytics layer also checks the meaning of the window:

export function validWindow(w: Window, coverage: Window) {
  windowSchema.parse(w);
  if (days(w) < 1 || days(w) > 31) {
    throw new Error('Window must contain 1–31 days');
  }
  if (w.start < coverage.start || w.end > coverage.end) {
    throw new Error('Window is outside dataset coverage');
  }
  return w;
}

The comparison tool requires equal-length windows, with the previous window ending before or at the current window's start. The ranking tool requires an integer limit between 1 and 10.

These checks run in local code. An instruction asking the model to “use sensible dates” can improve interaction, but the tool still validates every request it receives.

4. Keep queries fixed and money exact

The analytics connection opens the existing SQLite file in read-only mode:

this.#db = new DatabaseSync(path, { readOnly: true });
this.#db.exec('PRAGMA query_only=ON');

The database handle remains private to the analytics class. MCP tools receive dates and limits; they do not receive a connection, filename selector or free-form SQL argument.

The summary query is a predefined statement:

SELECT
  COUNT(DISTINCT o.id) AS orders,
  COALESCE(SUM(l.quantity), 0) AS units,
  COALESCE(SUM(l.quantity * l.unit_minor), 0) AS minor
FROM orders o
JOIN lines l ON l.order_id = o.id
WHERE o.status = 'delivered'
  AND o.order_date >= ?
  AND o.order_date < ?

COUNT(DISTINCT o.id) matters. One order can contain several lines; counting joined rows would inflate the order count and distort the average order value.

The SQL parameters are bound through the statement API. Request text never becomes part of the query syntax.

We read integer results as bigint and serialize monetary values as strings. For example:

{
  "totalMinor": "1700000",
  "totalDzd": "17000.00",
  "averageOrderDzd": "5666.67"
}

The fixture loader bounds the number of orders, line quantities and unit prices. Those bounds also keep this fixture's SQLite integer sums within range. For a production importer, establish equivalent bounds or choose an appropriate wider aggregation strategy.

Average order value is rounded to a centime. Percentage change is calculated locally with integer arithmetic and rounded to two decimal places. When the previous total is zero, changePercent is null; the percentage is undefined.

The ranking query uses ORDER BY minor DESC, p.id ASC. The secondary key gives equal totals a stable order. We also return units so readers can see how the value ranking relates to volume.

5. Publish the analytics layer through MCP

The MCP server uses the split TypeScript SDK packages. The entry point imports the server and its stdio helper:

import { McpServer } from '@modelcontextprotocol/server';
import { serveStdio } from '@modelcontextprotocol/server/stdio';

A tool registration connects a schema to one local operation. This excerpt shows the essential structure; the complete file also registers the other three tools and handles shutdown.

const annotations = {
  readOnlyHint: true,
  destructiveHint: false,
  idempotentHint: true,
  openWorldHint: false,
};

const handle = serveStdio(() => {
  const server = new McpServer({
    name: 'devup-sales-lab',
    version: '1.0.0',
  });

  server.registerTool(
    'sales_summary',
    {
      description:
        'Delivered merchandise total, distinct orders and units. ' +
        'Start inclusive, end exclusive. Within dataset coverage, ' +
        'at most 31 days.',
      inputSchema: windowSchema,
      annotations,
    },
    async (args) => checked(() => analytics.summary(args)),
  );

  return server;
});

The checked helper returns either JSON text content or an MCP tool result marked isError: true. It avoids returning raw database exceptions to the model. Schema validation can also produce tool errors before the handler runs.

The annotations communicate intended behavior to clients. The restrictions come from the registered operations, fixed queries and read-only connection. A hint does not grant or revoke filesystem permissions.

Keep stdout reserved for protocol messages. The server uses stderr for transport diagnostics; the demo client prints human-readable results from its own process.

6. Verify the tools through an actual client

The local demo creates a client and launches the server using the current Node executable and an absolute script path:

const client = new Client({
  name: 'sales-lab-client',
  version: '1.0.0',
});

const transport = new StdioClientTransport({
  command: process.execPath,
  args: [serverPath],
  cwd: root,
  env: childEnv(),
  stderr: 'inherit',
});

await client.connect(transport);

The server process gets an explicit small environment and no DEVUP API key. Its executable and arguments are chosen by the application. Tool arguments cannot change the child command or select a different database path.

The demo calls listTools(), then describe_dataset, compare_periods and top_products. It closes the connection in a finally block.

These are the reproduced results:

Metric	Previous window	Current window
Delivered merchandise total	9,000.00 DZD	17,000.00 DZD
Distinct delivered orders	2	3
Delivered units	4	7
Average order value	4,500.00 DZD	5,666.67 DZD

The absolute increase is 8,000.00 DZD and the rounded percentage change is 88.89%.

Original visualization of examples/demo.json. These are synthetic fixture results retrieved through MCP.

The current-window ranking is:

Product	Units	Merchandise total
Desk lamp	3	7,500.00 DZD
Office chair	1	5,000.00 DZD
Notebook set	3	4,500.00 DZD

The office chair ranks second by value despite having fewer units. This is why the tool name, description and result should make the ranking basis explicit.

7. Add the DEVUP AI agent

Once the local tools work, configure the optional model path. Copy .env.example to .env and set:

DEVUP_API_KEY=YOUR_PRIVATE_DEVUP_KEY
DEVUP_MODEL=YOUR_EXACT_MODEL_ID

Choose a current catalog model that supports tool calling through the Responses API. Keep the key private and preserve the exact model ID. The repository ignores .env.

The client configuration uses the DEVUP AI endpoint and disables SDK trace export:

setTracingDisabled(true);

setDefaultOpenAIClient(
  new OpenAI({
    apiKey,
    baseURL: 'https://api.devupai.com/v1',
    maxRetries: 0,
    timeout: 45000,
  }),
);

The SDK's MCPServerStdio connects the same local server to an agent:

const server = new MCPServerStdio({
  name: 'local-sales',
  command: process.execPath,
  args: [serverPath],
  cwd: root,
  env: childEnv(),
  cacheToolsList: true,
  timeout: 10000,
});

In live-agent.mts, the application connects the server before running the agent and closes it afterward, including on failure. The agent configuration includes:

modelSettings: {
  store: false,
  maxTokens: 2000,
  parallelToolCalls: false,
  retry: { maxRetries: 0 },
}

Its instructions ask it to inspect dataset metadata first, use tool results for numeric claims, request clarification for ambiguous dates, and state the metric and exclusions. Product labels are treated as data. The agent must not infer causes for changes that the dataset cannot establish.

Run the default question with:

npm run live

Or provide an explicit question:

npm run live -- "Compare October 1–7, 2026 with October 8–14, 2026. Rank the top 3 products by merchandise value in the second window."

You can also ask in Arabic or French; the quality of that interaction depends on the selected model and remains something to evaluate.

The run has a six-turn limit and a 90-second abort signal. These constrain work; they do not guarantee a particular bill. Requests already processed can consume API balance. The live endpoint/model combination has not been exercised for this article.

store: false is a request setting, not a complete retention policy. During a live run, the user question, tool descriptions and returned aggregates—including product names—cross the API boundary. Review that data flow before adapting the example to private business data.

8. Test each layer separately

The package passes 38 automated tests on Node.js 24.19.0:

Layer	What the checks establish
Analytics	Exact totals, distinct orders, status exclusions, ranking, rounding and empty windows
Input contracts	Real dates, coverage, duration, overlap, limits and unexpected fields
Local MCP	Child-process discovery, successful calls, invalid inputs, unavailable write tools and unchanged database bytes
Agent integration	The real SDK completes a tool loop through the real MCP server with mocked model HTTP responses

The SDK integration test is useful because it checks more than a hand-written imitation of a tool call. It inspects serialized model requests, exercises the MCP server, and confirms that computed aggregates return to the next model turn.

Its model responses are scripted fixtures. The test establishes application wiring; it does not measure whether a live model will select the right tools or write an accurate explanation.

For live evaluation, keep the fixture fixed and record the exact model ID, date, question, selected tools, returned values and final answer. Include ambiguous questions, unavailable dates and zero-baseline comparisons. Check numeric fidelity and unsupported causal claims independently.

Where to take this next

A useful extension is an application-controlled reporting path: a user selects two date windows, the application retrieves the complete aggregates, and the model writes an explanation from that fixed report. That approach makes coverage and required calculations visible even when the model would otherwise omit a tool.

For a real multi-tenant store service, move tenant selection into authenticated application context, enforce tenant filters in every query, define snapshot freshness and refunds, and restrict process permissions. A public HTTP MCP deployment also needs its own authentication, authorization and transport design; those capabilities are outside this local lab.

The reusable part of this project is the contract between conversation and calculation: a small vocabulary of validated operations, precise outputs, and results that can be checked independently of generated prose.

Companion files: setup and project guide, captured MCP output, and tests.

Official references

• MCP TypeScript SDK: first server
• MCP TypeScript SDK: first client
• OpenAI Agents SDK: MCP integration
• DEVUP AI: Agents SDK for TypeScript

Built by Mohamed Bal for DEVUP AI. Code and original figures are included under the MIT license.