Small business SaaS platforms want to offer AI-powered BI features, but building a secure, multi-tenant server that exposes database tools to agents is complex and easy to get wrong.
A complete, working implementation of this recipe — downloadable as a zip or browsable file by file. Generated by our build pipeline; tested with full coverage before publishing.
This recipe builds a multi-tenant MCP (Model Context Protocol) server that lets AI agents query business intelligence dashboards securely. You’ll wire together Express 5, Postgres, Redis, and Azure AI Foundry with a set of REAA packages that handle multi-tenant middleware, tool orchestration, OpenTelemetry tracing, and metrics — all without writing bespoke multi-tenancy code. When you’re done, you’ll have a gateway that serves tenant-scoped BI data through MCP tools, with per-client rate limits, cost tracking, and visibility filters.
An Azure OpenAI resource with a deployed model (e.g. gpt-5-2)
Basic familiarity with TypeScript and Express
Step 1: Scaffold the project and install dependencies
Start by creating a new project directory with TypeScript, ESLint, and Next.js build tooling, then install all dependencies. The dependencies split into three groups: the REAA MCP ecosystem packages, the Azure AI SDK, and the data-layer packages.
terminal
mkdir azure-ai-mcp-bi && cd azure-ai-mcp-bi
Create package.json with these exact pinned versions:
The getDb() function returns a lazy singleton tagged-template client. Connection pooling uses 10 max connections and a 20-second idle timeout. The resetDb() helper closes the pool — useful in tests where you need to tear down connections between suites.
Expected output: The file compiles cleanly. sql is initialized only on first call, and errors from sql.end() are swallowed to avoid unhandled rejections during cleanup.
Step 3: Create the Redis client
The Redis client powers rate limiting and can store session state. Create src/lib/redis.ts:
ts
import { Redis } from 'ioredis';const REDIS_URL: string | undefined = process.env.REDIS_URL;let redis: Redis | null = null;export function redisRetryStrategy(times: number): number | null { if (times > 10) return null; return Math.min(times * 50, 2000);}export function getRedis(): Redis { if (!redis) { redis = new Redis(REDIS_URL ?? '', { maxRetriesPerRequest: 1, retryStrategy: redisRetryStrategy, lazyConnect: true, }); } return redis;}export async function closeRedis(): Promise<void> { if (redis) { await redis.quit(); redis = null; }}
The retryStrategy backs off linearly (times * 50 ms, max 2000 ms) and gives up after 10 retries by returning null. Setting lazyConnect: true avoids connecting until the first command runs.
Expected output:getRedis() returns the same singleton across calls without connecting until the first actual command.
Step 4: Configure the Azure AI client
Azure AI Foundry (formerly Azure OpenAI Service) provides the LLM backend. Create src/lib/azure-ai.ts:
ts
import "@azure/openai";import { AzureOpenAI } from "openai";let client: AzureOpenAI | null = null;interface AzureOpenAIConfig { endpoint: string; apiKey: string; deployment: string; apiVersion: string;}function readEnvOrThrow(name: string): string { const value = process.env[name]; if (!value) { throw new Error(`${name} is not set`); } return value;}export function getAzureOpenAIConfig(): AzureOpenAIConfig { return { endpoint: readEnvOrThrow("AZURE_OPENAI_ENDPOINT"), apiKey: readEnvOrThrow("AZURE_OPENAI_API_KEY"), deployment: readEnvOrThrow("AZURE_OPENAI_DEPLOYMENT"), apiVersion: process.env.AZURE_OPENAI_API_VERSION ?? "2024-10-21", };}export function getAzureOpenAIClient(): AzureOpenAI { if (!client) { const { endpoint, apiKey, deployment, apiVersion } = getAzureOpenAIConfig(); client = new AzureOpenAI({ endpoint, apiKey, deployment, apiVersion, }); } return client;}export function resetAzureOpenAIClient(): void { client = null;}
getAzureOpenAIConfig() reads four environment variables and throws on missing required ones (AZURE_OPENAI_ENDPOINT, AZURE_OPENAI_API_KEY, AZURE_OPENAI_DEPLOYMENT). getAzureOpenAIClient() returns a singleton AzureOpenAI instance. The import "@azure/openai" side-effect import registers the Azure-specific type augmentations on the OpenAI SDK.
Expected output:getAzureOpenAIClient() returns an AzureOpenAI instance configured with your endpoint and deployment. resetAzureOpenAIClient() clears the singleton for test isolation.
Step 5: Build the multi-tenant middleware
This is the heart of the recipe. You’ll create a middleware layer that resolves tenants from HTTP headers, enforces Redis-backed rate limits at 100 RPM per tenant, filters tool visibility by pricing tier (basic vs premium), tracks per-tenant cost, and logs usage events.
Create src/middleware/tenant.ts:
ts
import { AsyncLocalStorage } from 'node:async_hooks';import { createMultiTenantMiddleware } from '@reaatech/multi-tenant-mcp-middleware';import type { TenantContextStore } from '@reaatech/multi-tenant-mcp-tenant-resolver';import type { Server } from '@modelcontextprotocol/sdk/server/index.js';import { getRedis } from '../lib/redis.js';import { logger } from '@reaatech/mcp-server-observability';const DEFAULT_RPM = 100;interface TenantContext { readonly tenantId: string; readonly metadata: Record<string, unknown>;
Key design decisions in this module:
createTenantContextStore wraps AsyncLocalStorage so tenant context flows through async chains without explicit parameter passing.
resolveTenantFromJwt reads the x-tenant-id header. In production you’d verify a real JWT; this stub extracts the claim from a header that an upstream auth gateway has already validated.
createRedisRateLimiter uses INCR + EXPIRE for a 60-second sliding window. First request sets the TTL; subsequent requests increment and compare against the RPM threshold.
toolVisibility defines two tiers: basic gets only listSales and getRevenueSummary; premium gets all four tools. The middleware automatically filters tools/list responses.
costCalculator assigns a per-call cost in dollars per tool invocation.
adaptPinoToTenantLogger wraps the Pino logger from @reaatech/mcp-server-observability into the interface the middleware expects.
Expected output:configureTenantMiddleware(server) returns { middleware, store } where middleware has a handle(server, method, handler) method.
Step 6: Define the BI MCP tools
Now you’ll define the four BI tools that MCP client agents can call. Each tool performs a tenant-scoped Postgres query and returns results as JSON text content blocks.
Create src/tools/bi.ts:
ts
import { textContent, errorResponse } from '@reaatech/mcp-server-core';import { createMcpServer } from '@reaatech/mcp-server-engine';import { type ToolDefinition as ToolsToolDefinition } from '@reaatech/mcp-server-tools';import { type MultiTenantMiddleware } from '@reaatech/multi-tenant-mcp-middleware';import type { Server } from '@modelcontextprotocol/sdk/server/index.js';import { getDb } from '../lib/db.js';interface ToolHandlerParams { name: string; arguments?: Record<string, unknown>;}interface
Each handler follows the same pattern:
Validate that tenantId exists and is a non-empty string — return errorResponse if not.
Call getDb() to get the Postgres client.
Execute a tenant-scoped SQL query using the postgres tagged template.
Wrap the result rows in textContent(JSON.stringify(rows)) for the MCP response.
Catch errors and return errorResponse with the error message.
The registerBiTools function uses the multi-tenant middleware’s handle method to register two MCP methods: tools/list (returns tool metadata) and tools/call (dispatches to the correct tool handler via a switch statement).
Expected output:registerBiTools(middleware, server) registers four tool definitions and their handler functions under tools/list and tools/call.
Step 7: Bootstrap the server
Create src/server.ts to wire everything together:
ts
import { createApp, startServer } from '@reaatech/mcp-server-engine';import { initObservability, logger } from '@reaatech/mcp-server-observability';import { Server } from '@modelcontextprotocol/sdk/server/index.js';import { StreamableHTTPServerTransport } from '@modelcontextprotocol/sdk/server/streamableHttp.js';import { configureTenantMiddleware } from './middleware/tenant.js';import { registerBiTools } from './tools/bi.js';export async function bootstrap() { await initObservability(); const app = await createApp(); const transport = new StreamableHTTPServerTransport(); const server = new Server( { name: 'azure-ai-mcp-bi', version: '1.0.0' }, { capabilities: { tools: {} } }, ); const { middleware } = configureTenantMiddleware(server); registerBiTools(middleware, server); logger.info({ port: process.env.PORT ?? '8080' }, 'MCP server ready'); await server.connect(transport); app.post('/api/mcp', (req, res, next) => { transport.handleRequest(req, res, req.body).catch(next); }); return { app, server };}export { startServer };
bootstrap() runs the full initialization sequence:
initObservability() — initializes OpenTelemetry tracing and metrics (no-op if OTEL_EXPORTER_OTLP_ENDPOINT isn’t set).
createApp() — returns a pre-configured Express 5 app with Helmet security headers, CORS, JSON body parsing, health endpoints (/health, /ready, /live), and graceful shutdown.
A Server instance from @modelcontextprotocol/sdk is created with tool capabilities.
configureTenantMiddleware(server) wires up the multi-tenant pipeline.
registerBiTools(middleware, server) registers the four BI tool handlers.
A StreamableHTTPServerTransport connects to the SDK server and is mounted as Express middleware at POST /api/mcp.
The startServer import is re-exported so the entry point can call it without importing @reaatech/mcp-server-engine directly.
Expected output:bootstrap() resolves to { app, server } where app is a configured Express instance with a POST /api/mcp route.
Step 8: Wire up the entry point
Create src/index.ts as the server entry point:
ts
import { bootstrap, startServer } from './server.js';import { logger } from '@reaatech/mcp-server-observability';import { envConfig } from '@reaatech/mcp-server-core';export async function main() { await bootstrap(); void startServer(); logger.info({ port: envConfig.PORT, service: 'azure-ai-mcp-bi' }, 'MCP server listening');}export async function start() { try { await main(); } catch (err: unknown) { console.error('Failed to start server:', err); process.exit(1); }}if (typeof process !== 'undefined' && process.env.NODE_ENV !== 'test') { void start();}
The auto-execute guard (NODE_ENV !== 'test') prevents the server from starting during vitest runs. The start() function wraps main() in a try-catch that logs the error and exits with code 1 on failure.
Expected output: Running pnpm dev boots the server. The console shows MCP server ready and MCP server listening log lines.
Step 9: Configure environment variables
Create .env.example at the project root with these placeholders:
env
# Env vars used by azure-ai-mcp-server-for-smb-business-intelligence.# The builder adds entries here as it wires up each integration.# Keep placeholders only — never commit real values.NODE_ENV=developmentPORT=8080DATABASE_URL=postgres://user:***@localhost:5432/bidbREDIS_URL=redis://localhost:6379AZURE_OPENAI_ENDPOINT=https://<resource-name>.openai.azure.comAZURE_OPENAI_API_KEY=<your-azure-api-key>AZURE_OPENAI_DEPLOYMENT=gpt-5-2AZURE_OPENAI_API_VERSION=2024-10-21CORS_ORIGIN=*API_KEY=<your-mcp-api-key>AUTH_MODE=api-keyRATE_LIMIT_RPM=100LOG_LEVEL=infoOTEL_EXPORTER_OTLP_ENDPOINT=<your-otel-collector-url>OTEL_SERVICE_NAME=azure-ai-mcp-bi
Copy it to .env and fill in your real credentials:
terminal
cp .env.example .env
Expected output:cat .env shows the same keys with your actual values filled in. The server reads all 16 environment variables at startup — missing required Azure keys will cause a clear error on boot.
Step 10: Write and run the tests
The test suite validates every module using vitest with mocked external dependencies. You’ll create test files that mirror the source structure.
Expected output: All 77 tests pass (numFailedTests: 0), and all four coverage metrics (lines, branches, functions, statements) are at 90% or higher.
Step 11: Start the server
With .env configured and all tests passing, start the server:
terminal
pnpm dev
Expected output: The server boots on port 8080. You’ll see log output showing MCP server ready and the server listening confirmation. The Express app serves:
POST /api/mcp — MCP Streamable HTTP transport for tool invocation
GET /health — Full health diagnostics
GET /ready — Readiness check
GET /live — Liveness check
To list the available tools, send an MCP request to the server:
Expected output: A JSON response with sales data scoped to the acme-corp tenant, or an empty result if your Postgres database has no rows yet.
Next steps
Add real JWT verification — Replace the x-tenant-id header stub with a proper JWT validation function that verifies tokens against your auth provider’s public keys.
Add more BI tools — Extend src/tools/bi.ts with tools for inventory analysis, customer churn prediction, or cohort retention reports.
Build a web dashboard — Add a frontend that calls the MCP server internally and renders charts with a library like Recharts.
Implement tenant provisioning — Build an admin API endpoint that creates new tenants, seeds their Postgres schema, and configures their visibility tier and rate limit.
Wire up Azure Monitor — Set OTEL_EXPORTER_OTLP_ENDPOINT to your Azure Monitor OTLP ingestion endpoint and deploy the server to see traces and metrics in Azure.
