Launch a multi‑tenant MCP server that lets your small business customers ask analytics questions in natural language – powered by Vertex AI and your own BigQuery datasets.
Small business owners can’t afford a data team. They have data in spreadsheets, a CRM, or a simple database, but translating a question like ‘Which product had the highest margin last month?’ into SQL is beyond their self‑serve abilities.
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 tutorial builds a multi-tenant MCP (Model Context Protocol) server that lets your small business customers ask analytics questions in natural language. The server uses Vertex AI to turn questions into SQL, runs the queries against BigQuery, and explains the results back in plain English. It includes per-tenant budget tracking, a spreadsheet upload tool, and an admin dashboard for cost monitoring.
This tutorial is for developers who want to offer AI-powered analytics as a SaaS feature without building a data team for every customer. You’ll use TypeScript, Express, Next.js App Router, and Google Cloud services (Vertex AI and BigQuery).
Prerequisites
Node.js 22+ and pnpm 10 installed on your machine
A Google Cloud project with the BigQuery and Vertex AI APIs enabled
A service account key JSON file downloaded from Google Cloud IAM & Admin
The Vertex AI API and BigQuery API enabled in your GCP project
At least one BigQuery dataset with some data
Basic familiarity with TypeScript, Express, and the MCP protocol
The setup assumes you have a Next.js 16 project bootstrapped with TypeScript, Vitest, and the dependencies listed in the package.json. All dependencies are pinned to exact versions.
Step 1: Configure environment variables
Copy the example environment file and fill in your Google Cloud project details.
terminal
cp .env.example .env
Open and replace the placeholders with your values:
Expected output: No errors when you run pnpm typecheck. The file exports interfaces for tenant configs, analytics requests/responses, and Zod schemas for input validation.
Step 3: Create the BigQuery client
Create src/lib/bigquery.ts to wrap the @google-cloud/bigquery library.
Expected output:pnpm typecheck passes. The BigQueryClient class wraps four operations: running arbitrary SQL, validating the connection, listing tables in a dataset, and fetching table schemas. All methods throw BigQueryError on failure.
Step 4: Create the Vertex AI client
Create src/lib/vertex-client.ts to wrap the @google-cloud/vertexai library and the Gemini model.
ts
import { VertexAI, HarmBlockThreshold, HarmCategory,} from "@google-cloud/vertexai";export class VertexAIError extends Error { constructor(message: string, cause?: unknown) { super(message, { cause }); this.name = "VertexAIError"; }}export class VertexAIClient { private vertexAI: VertexAI; private generativeModel; constructor(project: string, location: string) { this.vertexAI = new VertexAI({ project, location }); this.generativeModel = this.vertexAI.getGenerativeModel({ model: "gemini-2.5-flash", }); } static buildSqlPrompt(question: string, schema: string): string { return ( `You are a BigQuery SQL expert. Given the following schema description, ` + `generate a valid BigQuery Standard SQL query to answer the user's question.\n\n` + `Schema:\n${schema}\n\n` + `Question: ${question}\n\n` + `Output ONLY the raw SQL query. No markdown, no backticks, no explanation.` ); } async generateSql( question: string, schemaDescription: string, ): Promise<string> { try { const result = await this.generativeModel.generateContent({ contents: [ { role: "user", parts: [ { text: VertexAIClient.buildSqlPrompt(question, schemaDescription) }, ], }, ], safetySettings: [ { category: HarmCategory.HARM_CATEGORY_DANGEROUS_CONTENT, threshold: HarmBlockThreshold.BLOCK_ONLY_HIGH, }, ], }); const text = result.response.candidates?.[0]?.content?.parts?.[0]?.text ?? ""; if (!text) { throw new VertexAIError("Empty response from model"); } return text; } catch (err) { if (err instanceof VertexAIError) throw err; throw new VertexAIError( `Vertex AI generateSql failed: ${String(err)}`, err, ); } } async explainResults( question: string, sql: string, resultJson: string, ): Promise<string> { try { const prompt = `The user asked: "${question}"\n\n` + `I ran this SQL:\n${sql}\n\n` + `And got these results:\n${resultJson}\n\n` + `Explain what these results mean in plain language for a business user.`; const result = await this.generativeModel.generateContent({ contents: [{ role: "user", parts: [{ text: prompt }] }], }); const text = result.response.candidates?.[0]?.content?.parts?.[0]?.text ?? ""; if (!text) { throw new VertexAIError("Empty explanation from model"); } return text; } catch (err) { if (err instanceof VertexAIError) throw err; throw new VertexAIError( `Vertex AI explainResults failed: ${String(err)}`, err, ); } }}
Expected output:pnpm typecheck passes. The VertexAIClient provides two core methods: generateSql (turns a question into SQL) and explainResults (explains query results in plain language). Both use the Gemini model and throw VertexAIError on failure.
Step 5: Create the budget middleware
Create src/middleware/budget.ts to track per-tenant usage and enforce daily budget limits.
Expected output:pnpm typecheck passes. The budget middleware tracks usage with a cost calculator that charges per-call and per-token fees with volume-based discount tiers. checkBudget returns whether a tenant is within their daily limit, and recordUsage records a new usage event and returns the updated total cost.
Step 6: Create the analytics MCP tool
Create src/tools/analytics.ts — the main MCP tool that connects Vertex AI to BigQuery.
ts
import { defineTool, registerTool } from "@reaatech/mcp-server-tools";import { textContent, errorResponse } from "@reaatech/mcp-server-core";import { z } from "zod";import { BigQueryClient } from "../lib/bigquery.js";import { VertexAIClient } from "../lib/vertex-client.js";import { createBudgetTracker, checkBudget, recordUsage, type BudgetResources } from "../middleware/budget.js";import type { ToolResponse } from "@reaatech/mcp-server-core";export function createAnalyticsTool( bigQuery: BigQueryClient, vertexAI: VertexAIClient, budget: BudgetResources,) { return defineTool({ name: "analytics_query", description: "Ask a natural-language analytics question about your business data. Generates and runs SQL against BigQuery, then explains the results.", inputSchema: z.object({ query: z.string().describe("Natural language analytics question"), tenantId: z.string().describe("Your tenant ID for cost tracking"), }), handler: async (args, _context) => { void _context; try { const input = z .object({ query: z.string(), tenantId: z.string() }) .parse(args); const dailyLimit = Number(process.env.DEFAULT_DAILY_BUDGET) || 10; const { allowed } = checkBudget(input.tenantId, budget.tracker, dailyLimit); if (!allowed) { return errorResponse("Budget exceeded for tenant " + input.tenantId); } const tables = await bigQuery.listTables(input.tenantId); const schemaDescription = "Tables in dataset " + input.tenantId + ": " + tables.join(", "); const sql = await vertexAI.generateSql(input.query, schemaDescription); if (!sql) { return errorResponse("Failed to generate SQL"); } const rows = await bigQuery.query(sql); const explanation = await vertexAI.explainResults( input.query, sql, JSON.stringify(rows), ); const cost = await recordUsage( input.tenantId, "analytics_query", 500, 200, budget.tracker, budget.emitter, ); return { content: [ textContent( JSON.stringify({ sqlQuery: sql, resultRows: rows, explanation, costUsd: cost, }), ), ], } satisfies ToolResponse; } catch (err) { return errorResponse(String(err)); } }, });}export function registerAnalyticsTools() { const bigQuery = new BigQueryClient( process.env.GOOGLE_CLOUD_PROJECT ?? "default-project", ); const vertexAI = new VertexAIClient( process.env.GOOGLE_CLOUD_PROJECT ?? "default-project", process.env.GOOGLE_CLOUD_LOCATION ?? "us-central1", ); const budget = createBudgetTracker(); const tool = createAnalyticsTool(bigQuery, vertexAI, budget); registerTool(tool);}
Expected output:pnpm typecheck passes. This tool wraps a full pipeline: check the budget, fetch table schemas from BigQuery, ask Vertex AI to generate SQL, run that SQL, ask Vertex AI to explain the results, record the usage cost, and return everything as a JSON response.
Step 7: Create the spreadsheet upload tool
Create src/tools/spreadsheet.ts to let users upload spreadsheet data as a second MCP tool.
ts
import * as XLSX from "xlsx/xlsx.mjs";import { defineTool, registerTool } from "@reaatech/mcp-server-tools";import { textContent, errorResponse } from "@reaatech/mcp-server-core";import { z } from "zod";import { BigQueryClient } from "../lib/bigquery.js";import type { ToolResponse } from "@reaatech/mcp-server-core";export function createSpreadsheetTool(bigQuery: BigQueryClient) { return defineTool({ name: "upload_spreadsheet", description: "Upload a spreadsheet (XLSX) and store its data in BigQuery.", inputSchema: z.object({ data: z.string().describe("Base64-encoded XLSX file contents"), tenantId: z.string().describe("Your tenant ID for data routing"), }), handler: async (args, _context) => { void _context; const input = z .object({ data: z.string(), tenantId: z.string() }) .parse(args); try { const workbook = XLSX.read( Buffer.from(input.data, "base64"), { type: "buffer" }, ); const sheetName = workbook.SheetNames[0]; const rows: Record<string, unknown>[] = XLSX.utils.sheet_to_json( workbook.Sheets[sheetName], ); const validatedRows = await bigQuery.validateConnection(); if (!validatedRows) { return errorResponse("BigQuery connection failed"); } if (rows.length > 0) { const columns = Object.keys(rows[0]); const values = rows.map((row: Record<string, unknown>) => { const vals = columns.map((c) => JSON.stringify(row[c] ?? null)); return "(" + vals.join(",") + ")"; }).join(","); const sql = "BEGIN TEMP TABLE temp_data (" + columns.map((c) => c + " STRING").join(",") + "); INSERT INTO temp_data VALUES " + values + "; END;"; try { await bigQuery.query(sql); } catch { return errorResponse("Failed to insert data into BigQuery"); } } const message = "Uploaded " + String(rows.length) + " rows"; return { content: [textContent(message)], } satisfies ToolResponse; } catch (err) { return errorResponse( "Failed to parse spreadsheet: " + String(err), ); } }, });}export function registerSpreadsheetTool() { const bigQuery = new BigQueryClient( process.env.GOOGLE_CLOUD_PROJECT ?? "default-project", ); const tool = createSpreadsheetTool(bigQuery); registerTool(tool);}
Expected output:pnpm typecheck passes. The spreadsheet tool accepts base64-encoded XLSX content, parses it with the xlsx library, and inserts the data into BigQuery using a temporary table.
Step 8: Create the admin routes
Create src/routes/admin.ts with Express route handlers for budget management and health checks.
Expected output:pnpm typecheck passes. The server creates an Express app, registers the analytics tools, mounts admin routes under /admin, attaches the MCP server over Streamable HTTP at /mcp, and exports handleListTools and handleCallTool for MCP request handling.
Step 10: Create instrumentation and configure Next.js
Create src/instrumentation.ts to start the Express server when Next.js boots, and update next.config.ts to enable the instrumentation hook.
ts
export async function register() { if (process.env.NEXT_RUNTIME === "nodejs") { const { startServer } = await import("./server.js"); void startServer(); }}
Expected output:pnpm typecheck passes. The instrumentationHook: true flag is required — without it, the register() function is dead code and the Express server never starts.
Step 11: Create the health check route
Create app/api/health/route.ts for a Next.js App Router health endpoint that’s separate from the Express admin routes.
ts
import { type NextRequest, NextResponse } from "next/server";export function GET(_req: NextRequest) { void _req; return NextResponse.json({ status: "healthy", version: "0.1.0", uptime: process.uptime(), timestamp: new Date().toISOString(), });}
Expected output:pnpm typecheck passes. The route exports a named GET function (default exports don’t work with App Router) and returns a JSON response with NextResponse.json(), not bare new Response(JSON.stringify(...)).
Step 12: Run the tests
Run the full test suite with coverage to verify everything works.
terminal
pnpm test
This runs the project’s test script which invokes vitest with the JSON reporter. The same command expanded fully would be: vitest run --coverage --reporter=json --outputFile=vitest-report.json.
Expected output: All 65 tests pass (numFailedTests: 0), and code coverage is above 90% for lines, branches, functions, and statements.
After the tests pass, verify type correctness and linting:
terminal
pnpm typecheckpnpm lint
Expected output:pnpm typecheck exits with no errors. pnpm lint passes with no ESLint violations.
Next steps
Deploy to production: Wrap the server in a Docker container and deploy to Cloud Run or Google Kubernetes Engine. The MCP Streamable HTTP transport is designed for cloud-hosted deployments.
Add persistent storage: Replace InMemoryCostTracker with a database-backed store (Firestore, Cloud SQL, or Spanner) so budget data survives server restarts.
Extend the schema discovery: Instead of listing table names, fetch the full column schemas with getTableSchema() and pass them to Vertex AI for more accurate SQL generation.
Add authentication: Implement API key validation or OAuth 2.0 middleware to protect the MCP endpoint, then wire the tenant ID from the authenticated session.
Build a frontend dashboard: Add Next.js pages that visualize the admin cost summary, show per-tenant query history, and let operators adjust budget limits interactively.
