Skip to main content

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

sql
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 another program holds port 9191, scram_daemons shows the daemon failed with address already in use; set listen_port under [udf.daemon] (see the configuration reference) to move it, and MCP_PORT is set for the server to match. If you followed the quickstart, the -p 9191:9191 already in that docker run is the whole setup.

Confirm it is serving:

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

scram_daemons is read by a superuser or a member of pg_read_all_stats; it is empty for any other role. 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):

json
{ "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:

sh
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​

ToolWhat it does
describeModels, dimensions, metrics with result types, AI hints on every object, and the instruction list - one call
queryCompile {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
sqlCompile only - the SQL and params, nothing executed
get_instructionsThe instruction documents, priority-ordered
define_model / drop_modelAuthor models; every expression validated against the live catalog before anything is written
define_instructions / drop_instructionsAuthor instruction documents
validateRe-validate every definition against the catalog (the drift sweep); names each broken object
suggestDraft a model from a live table's schema - a draft only, nothing written
export / importThe whole layer as JSON or YAML; import validates everything first, one bad entry means zero changes
can_answerDry-run a request against the compiler WITHOUT executing: {answerable, code, reason, suggestion} - branch on fact, not on predicting a refusal from prose
capabilitiesWhat this deployment supports: engine version, the tool list, limits, and the machine-readable refusal codes an agent can branch on
batchUp 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
profileOne 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
explainCompile 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. The published image sets MCP_AUTH=basic. A node started with --pg-no-auth checks no password, so a client sends the name of a role that exists and any password.

The server binds 127.0.0.1 unless MCP_HOST is set. Bound beyond loopback (for example MCP_HOST=0.0.0.0) with no MCP_AUTH, it answers every request 401 and logs one line that says to set MCP_AUTH=basic or MCP_AUTH=bearer. Bound to loopback with no MCP_AUTH, it runs every request as one service identity. 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.

With MCP_AUTH=bearer an AI agent authenticates with a token instead: the server reads Authorization: Bearer sat.<role>.<secret>, connects as <role> with the token as its credential, and answers 401 with WWW-Authenticate: Bearer for a missing, malformed or rejected token. A token is minted in SQL by a superuser, the agent's creator or a member of pg_agent_admin, is shown once, and is stored only as a salted hash:

sql
-- the database the agents branch from
CREATE DATABASE app;
sql
-- connected to app: the tables the examples below hide from an agent
CREATE TABLE salaries (id int PRIMARY KEY, amount numeric);
CREATE TABLE orders (id int PRIMARY KEY, region text);
sql
SELECT * FROM scram.agent_provision('reporter', '{"clone_from": "app"}'); -- the role agent_reporter, pinned to its branch
SELECT * FROM scram.agent_token('reporter', '1 day'); -- token, expires_at
SELECT * FROM scram.agent_token_revoke('reporter');

An agent role connects only to its own branch database, may not mint tokens, and cannot read the token records.

Who may run the agent functions, each by its function and by its statement:

  • scram.agent_provision (CREATE AGENT) is for a superuser or a role with the CREATEROLE attribute; any other role is refused with 42501, permission denied to create role. A database the call pins with branch, or the default database when it names none, must be one the caller owns (42501, must be owner of database name); with clone_from the branch is made as the caller, so the rules of CREATE DATABASE ... CLONE apply to it too.
  • scram.agent_token (CREATE AGENT TOKEN), scram.agent_token_revoke (DROP AGENT TOKENS) and scram.agent_retire (DROP AGENT) are for a superuser, the agent's creator or a member of pg_agent_admin, and never for an agent. Any other role is refused with 42501.
  • scram.agent_apply is run by the server alone, to apply a change of the agent registry on every node of a cluster. Any caller is refused with 42501, This function is run by the server only.

Provisioning can hide data from the agent on its branch and cap the branch's size:

sql
SELECT * FROM scram.agent_provision('auditor', '{
"clone_from": "app",
"exclude": [{"table": "public.salaries"}, {"table": "public.orders", "rows": "region = ''eu''"}],
"quota_bytes": 1073741824
}');

The same operations are statements too, each the function above under another spelling:

sql
CREATE AGENT analyst WITH (clone_from = 'app', quota_bytes = '1 GB',
exclude = 'public.salaries', exclude = 'public.orders WHERE region = ''eu''');
CREATE AGENT TOKEN FOR analyst VALID FOR '1 day'; -- token, expires_at (shown once)
SHOW AGENTS;
DROP AGENT TOKENS FOR analyst; -- every token of the agent stops working
DROP AGENT IF EXISTS analyst; -- retires the agent
sql
REQUEST MERGE OF DATABASE agent_reporter; -- the agent asks, connected to its branch
APPROVE MERGE REQUEST '<request id>'; -- a person approves: the one-time token
APPLY MERGE REQUEST '<request id>' USING TOKEN '<token>' [ALLOW DESTRUCTIVE];
UNDO MERGE REQUEST '<request id>' [WITH (on_conflict = force)];
SHOW MERGE REQUESTS;

An excluded table is unreadable to the agent and a row predicate hides the rows it matches, through row level security on the branch only; other roles keep exactly the access they had. A predicate that does not parse is refused (42601) before anything is made. Once the branch owns quota_bytes (or [agent] branch_size_quota), a statement that would grow it is refused with 53100; a DELETE is always allowed. Point the agent host at the server with the header, for example { "mcpServers": { "scramdb-semantics": { "url": "http://127.0.0.1:9191/mcp", "headers": { "Authorization": "Bearer sat.agent_reporter...." } } } }. A token crosses the wire as a password, so serve it over TLS.

Limits (current, tracked)​

  • The transport is MCP streamable HTTP; stdio transport is pending an upstream runtime capability.
  • 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.