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, model} 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
can_answer	Dry-run a request against the compiler WITHOUT executing: {answerable, code, reason, suggestion} - branch on fact, not on predicting a refusal from prose
capabilities	What this deployment supports: engine version, the tool list, limits, and the machine-readable refusal codes an agent can branch on
batch	Up to 16 tool calls in one round trip; each item runs in order as the caller and answers ok with its result or an error with its code
profile	One model's data SHAPE, not just structure: row count, per-dimension distinct and null counts, time-dimension min/max - one bounded aggregate scan as the connected role
explain	Compile a request and ask the engine to EXPLAIN its SQL; answers the plan when the engine provides one, and says so honestly when it cannot

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).

model scopes name resolution on query, sql and can_answer: an unqualified metric or dimension name owned by the scoped model resolves there, and anything else falls through to the ordinary lookup across every model, which still refuses with AMBIGUOUS_NAME when more than one model owns the name.

Both query and sql group by ordinal position (GROUP BY 1, 2, ...), never by name, so an unqualified group key can never be shadowed by its own output alias - the failure mode a time_grain bucket hits when its alias reuses the source column's name. order_by keeps output aliases instead, since an ORDER BY name resolves to the output column first.

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.
• batch carries 1 to 16 tool calls; each one runs as the calling credential, so row-level security and RBAC apply to every sub-call exactly as they would standalone.
• profile runs one bounded aggregate scan and profiles at most 16 dimensions per call; a model with more says so in the answer (dimensions_omitted), never silently.
• A broken model definition refuses only the requests that touch it. Every model's container and skeleton (name grammar, target table, dimension/metric shape) are checked on every call so name resolution stays safe; deep validation of a model's dimension and metric bodies runs only for the fact model and any model a request joins to. validate names the specific broken object.