Semantic Layer
Overview
A query model declares the metrics, dimensions, filters, and defaults for a ClickHouse table, view, or materialized view. Once defined, the same model can be projected into three interfaces:
buildQuery— type-safe fluent builder for REST endpoints and server-side codecreateModelTool— generates a Zod schema and request builder for Vercel AI SDK tool callsregisterModelTools— registers query models as MCP tools on an MCP server
Adding or changing a metric in the query model updates all three interfaces without touching any other file.
Define a query model
A query model wraps an OlapTable, view, or materialized view and constrains how consumers can query it. Use defineQueryModel to declare dimensions, metrics, filters, sort fields, and defaults.
import { defineQueryModel, sql, count, countDistinct, sum, timeDimensions,} from "@514labs/moose-lib";import { VisitsTable } from "../datamodels/visits"; export const visitMetrics = defineQueryModel({ // Name becomes the tool name in MCP and AI SDK registrations name: "query_visits", // Description becomes the tool description LLMs read to decide when to call it description: "Visit and attendance metrics: bookings, check-ins, no-shows, " + "cancellations. Always requires a studio_id.", table: VisitsTable, // Columns consumers can GROUP BY dimensions: { status: { column: "status" }, locationId: { column: "location_id" }, // timeDimensions generates day, month, week from a date column ...timeDimensions(VisitsTable.columns.start_date), }, // Aggregate computations metrics: { totalVisits: { agg: count(), as: "totalVisits" }, uniqueClients: { agg: countDistinct(VisitsTable.columns.user_id), as: "uniqueClients" }, attended: { agg: sum(sql`CASE WHEN status = 1 THEN 1 ELSE 0 END`), as: "attended" }, noShows: { agg: sum(sql`CASE WHEN status = 2 THEN 1 ELSE 0 END`), as: "noShows" }, lateCancels: { agg: sum(sql`CASE WHEN status = 3 THEN 1 ELSE 0 END`), as: "lateCancels" }, }, // Which columns can be filtered and with which operators filters: { studioId: { column: "studio_id", operators: ["eq"] as const, required: true }, startDate: { column: "start_date", operators: ["gte", "lte"] as const }, status: { column: "status", operators: ["eq", "in"] as const }, }, // Whitelist of fields that can appear in ORDER BY sortable: ["totalVisits", "uniqueClients", "day", "month", "week"] as const, // What happens when a consumer doesn't specify something defaults: { dimensions: ["day"], metrics: ["totalVisits"], orderBy: [["day", "DESC"]], limit: 100, maxLimit: 1000, },});Query model reference
| Field | Purpose | Effect |
|---|---|---|
name | Identifier | Becomes the tool name in MCP and AI SDK registrations |
description | What this model queries | Used as the tool description for LLMs |
table | Source table or view | The ClickHouse table, view, or materialized view queries run against |
dimensions | Allowed GROUP BY columns | timeDimensions() generates day, month, week from a date column |
metrics | Aggregate computations | Appear in SELECT alongside requested dimensions |
filters | Allowed WHERE clauses with operators | required: true enforces mandatory filtering (e.g., tenant isolation) |
sortable | Allowed ORDER BY fields | Consumers can only sort by fields in this list |
defaults | Fallback values | Applied when a consumer omits dimensions, metrics, limit, or ordering |
REST APIs
buildQuery takes a query model and returns a type-safe fluent builder. It validates dimensions, metrics, and filter operators at compile time and generates ClickHouse SQL at runtime. .filter() skips undefined values, so optional query params don't require branching.
import { Api, buildQuery } from "@514labs/moose-lib";import { visitMetrics } from "../query-layer/visits-model"; interface VisitQueryParams { studio_id: number; dimensions?: string; metrics?: string; start_date_gte?: string; start_date_lte?: string; limit?: number;} export default new Api<VisitQueryParams, any>("visits", async ({ studio_id, dimensions, metrics, start_date_gte, start_date_lte, limit }, { client }) => { const results = await buildQuery(visitMetrics) .dimensions(dimensions ? dimensions.split(",") : ["day"]) .metrics(metrics ? metrics.split(",") : ["totalVisits"]) .filter("studioId", "eq", studio_id) .filter("startDate", "gte", start_date_gte) .filter("startDate", "lte", start_date_lte) .limit(limit || 100) .execute(client.query); return results; });A request like:
GET /api/visits?studio_id=5&dimensions=day&metrics=noShows&start_date_gte=2025-01-01Produces:
SELECT toDate(visits.start_date) AS day, sum(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS noShowsFROM visitsWHERE visits.studio_id = 5 AND visits.start_date >= '2025-01-01'GROUP BY toDate(visits.start_date)ORDER BY day DESCLIMIT 100createModelTool: AI SDK tool calls
createModelTool reads a query model and returns:
- A flat Zod schema — filters are flattened to
start_date_gte,status_in; dimensions and metrics become enum arrays - A
buildRequestfunction that converts flat params back into a nested query request
This plugs directly into the Vercel AI SDK's tool().
import { createModelTool } from "@514labs/moose-lib";import { tool } from "ai";import { visitMetrics } from "../query-layer/visits-model";import { client } from "../db"; // your ClickHouse client instance const { schema, buildRequest } = createModelTool(visitMetrics); export const queryVisitsTool = tool({ description: visitMetrics.description, parameters: schema, execute: async (params) => { const request = buildRequest(params); const sql = visitMetrics.toSql(request); const results = await client.query(sql); return results; },});Wire the tool into a chat route. The query model handles parameter validation and SQL generation; the route handles system prompt, model selection, and streaming:
import { streamText } from "ai";import { anthropic } from "@ai-sdk/anthropic";import { queryVisitsTool } from "../ai/tools"; export async function POST(req) { const { messages } = await req.json(); const result = streamText({ model: anthropic("claude-sonnet-4-5-20250514"), system: "You are an analytics assistant for a fitness studio platform. " + "Status codes: 1=attended, 2=no-show, 3=late-cancel, 4=early-cancel. " + "Always ask for a studio ID before querying.", messages, tools: { query_visits: queryVisitsTool }, toolChoice: "auto", }); return result.toDataStreamResponse();}The query model's required: true filters (e.g., studioId) carry through to the generated Zod schema, so the LLM must provide them. The description from the query model becomes the tool description the LLM uses to decide when to call it.
MCP server
registerModelTools calls createModelTool internally for each query model and registers the resulting tools on an MCP server. You provide the models and a callback that executes the query. Tool names, descriptions, and parameter schemas are derived from the model definitions.
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";import { StreamableHTTPServerTransport } from "@modelcontextprotocol/sdk/server/streamableHttp.js";import { registerModelTools } from "@514labs/moose-lib";import { visitMetrics } from "../query-layer/visits-model";import { revenueMetrics } from "../query-layer/revenue-model";import { clientMetrics } from "../query-layer/client-model";import { client } from "../db"; // your ClickHouse client instance export default async function handler(req, res) { const server = new McpServer({ name: "my-analytics", version: "1.0.0", }); registerModelTools( server, [visitMetrics, revenueMetrics, clientMetrics], async (model, request, limit) => { const sql = model.toSql({ ...request, limit }); const rows = await client.query(sql); return { content: [{ type: "text", text: JSON.stringify(rows) }], }; }, ); const transport = new StreamableHTTPServerTransport({ sessionIdGenerator: undefined, }); await server.connect(transport); await transport.handleRequest(req, res, req.body);}Any MCP client (Claude Desktop, Cursor, Claude Code) connects to the endpoint and discovers the registered tools:
claude mcp add --transport http analytics https://my-app.com/mcpChoosing an interface
| Interface | Function | Use when |
|---|---|---|
| REST API | buildQuery | Consumer is code — dashboards, reports, cron jobs, integrations |
| AI SDK | createModelTool | You need control over system prompt, rendering, and error handling |
| MCP | registerModelTools | You want to expose tools to any MCP-compatible client without building a UI |
All three can be used together. They read from the same query model.
Adding a metric
Add earlyCancels to the query model:
metrics: { // ...existing metrics... earlyCancels: { agg: sum(sql`CASE WHEN status = 4 THEN 1 ELSE 0 END`), as: "earlyCancels", },},The new metric is available as a metrics parameter in the REST API, an enum option in the AI tool's Zod schema, and a discoverable parameter in MCP. No other files change.