Semantic AI

ScramDB ships a semantic layer built for AI agents: an MCP server that gives any agent three things it cannot get from a raw connection - what your metrics mean and how they are calculated, AI hints on when and how to use each one, and instructions for how the agent should behave with your data. The agent asks for revenue by region; ScramDB compiles it into governed SQL, executes it as the caller's role, and returns the rows plus the exact SQL it ran.

Definitions live in ordinary semantic.* tables inside your database: replicated, GRANT-governed, and validated against the live catalog when written - a metric referencing a dropped column fails loud, it never rots the way warehouse-side YAML does.

By the end of this page you will have the server running, a model with hints defined, agent instructions in place, and an agent host connected and answering metric questions.

Five-minute quickstart

Create a table to model (skip if you already have data):

CREATE TABLE orders (id BIGINT PRIMARY KEY, region TEXT, total DOUBLE PRECISION,
                     created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO orders (id, region, total) VALUES
    (1, 'EU', 10.0), (2, 'EU', 15.0), (3, 'NA', 20.0), (4, 'AP', 40.0);

There is nothing to start. The Semantic AI server ships as an installed package that declares [metadata.daemon] autostart = true, so ScramDB starts it in-process at boot, supervises it, and serves MCP on port 9191 from the database itself. If you followed the quickstart, the -p 9191:9191 already in that docker run is the whole setup.

Confirm it is serving:

SELECT package, state, ports, shards_alive, restarts, last_error FROM scram_daemons;

A healthy row reads running with its granted port and a NULL last_error. A package that fails to start is a loud ERROR and a failed row with the reason: boot is never wedged, and a dead daemon is never silently absent. See Daemon packages for the full column list and the restart policy.

The sandbox has not gone anywhere. The daemon runs under the package's own manifold, which is deny-by-default, and [udf.daemon] env_allowlist decides which process environment variables are allowed to reach it at all. Set [udf.daemon] enabled = false to switch the whole seam off.

Point your agent host at it (Claude Code shown; any MCP host that speaks streamable HTTP works the same way):

{ "mcpServers": { "scramdb-semantics": { "url": "http://127.0.0.1:9191/mcp" } } }

Define instructions and a model - through the agent itself ("define a model for orders...") or directly:

curl -X POST http://127.0.0.1:9191/mcp -H 'content-type: application/json' -d '{
  "jsonrpc": "2.0", "id": 1, "method": "tools/call",
  "params": {"name": "define_instructions", "arguments": {
    "name": "ambiguity", "priority": 10,
    "content": "When a request is ambiguous, ask one clarifying question before querying."}}}'

curl -X POST http://127.0.0.1:9191/mcp -H 'content-type: application/json' -d '{
  "jsonrpc": "2.0", "id": 2, "method": "tools/call",
  "params": {"name": "define_model", "arguments": {"name": "orders", "def": {
    "target": "orders",
    "hints": "revenue is net of refunds; prefer monthly grain for trends",
    "primaryTimeDimension": "created",
    "dimensions": {
      "region":  {"expr": "region",  "kind": "categorical", "hints": "EU/NA/AP sales regions"},
      "created": {"expr": "created", "kind": "time"}},
    "metrics": {
      "revenue":  {"agg": "sum",   "arg": "total", "hints": "sum of order totals"},
      "orders_n": {"agg": "count", "arg": "*"}}}}}}'

Now ask the agent: "What is revenue by region?" It discovers the model, reads the hints, receives your instructions before its first tool call, and runs a governed query - never hand-written SQL against raw tables.

The tools

Tool	What it does
describe	Models, dimensions, metrics with result types, AI hints on every object, and the instruction list - one call
query	Compile {metrics, dimensions, filters, time_grain, order_by, limit} and execute as the connected role; returns rows, the exact SQL and params, and an honest truncated flag
sql	Compile only - the SQL and params, nothing executed
get_instructions	The instruction documents, priority-ordered
define_model / drop_model	Author models; every expression validated against the live catalog before anything is written
define_instructions / drop_instructions	Author instruction documents
validate	Re-validate every definition against the catalog (the drift sweep); names each broken object
suggest	Draft a model from a live table's schema - a draft only, nothing written
export / import	The whole layer as JSON or YAML; import validates everything first, one bad entry means zero changes

Filters are structured data ({"field": "region", "op": "eq", "value": "EU"}), never SQL strings - values travel as bound parameters, so injection through the semantic surface is impossible by construction. Ops: eq ne gt gte lt lte in not_in between like is_null not_null relative_time (windows: last_7_days, last_30_days, last_90_days, this_month, this_quarter, this_year).

Writing good hints

Hints are the difference between an agent that computes a number and one that computes the right number. Attach them to the object they describe:

• Model hints: scope and grain guidance - "revenue is net of refunds; prefer monthly grain for trends".
• Dimension hints: value vocabulary - "EU/NA/AP sales regions", "statuses: queued, running, done".
• Metric hints: definition boundaries - "excludes internal test orders", "use orders_n for volume, revenue for value".

describe delivers every hint alongside the definition, so guidance never drifts from the thing it guides.

Writing instructions

Instructions are behavior rules, delivered in the MCP initialize response - the agent has them before its first tool call. Lower priority numbers come first. Three that earn their keep:

• "When a request is ambiguous, ask one clarifying question before querying."
• "Answer with the number first, then one sentence of context."
• "If a metric seems missing, say so and suggest its definition - never approximate with raw SQL."

Multi-user mode

Start with MCP_AUTH=basic and each request's HTTP Basic credentials become the PostgreSQL credentials for that request: every caller queries as their own role, and the engine's RBAC and row-level security govern the rows - two users asking the same question get exactly the rows their policies allow. Wrong credentials answer 401. Definition metadata (models, hints, instructions) is served to every authenticated caller; data queries and authoring run as the caller, so GRANT on the semantic.* tables controls who authors.

Limits (current, tracked)

• The transport is MCP streamable HTTP; stdio transport is pending an upstream runtime capability.
• Ratio metrics answer a loud division by zero on zero denominators (a NULL-on-zero upgrade is tracked against an engine fix).
• Multi-model queries join along declared relationships one hop from the fact model, require bare-column expressions, and keep filters on the fact model; anything outside that subset is refused with a stable error code, never guessed.
• The concurrency envelope is verified to 16 parallel requests.