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 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:
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):
{ "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. 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:
-- the database the agents branch from
CREATE DATABASE app;
-- 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);
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 theCREATEROLEattribute; any other role is refused with42501,permission denied to create role. A database the call pins withbranch, or the default database when it names none, must be one the caller owns (42501,must be owner of database name); withclone_fromthe branch is made as the caller, so the rules ofCREATE DATABASE ... CLONEapply to it too.scram.agent_token(CREATE AGENT TOKEN),scram.agent_token_revoke(DROP AGENT TOKENS) andscram.agent_retire(DROP AGENT) are for a superuser, the agent's creator or a member ofpg_agent_admin, and never for an agent. Any other role is refused with42501.scram.agent_applyis run by the server alone, to apply a change of the agent registry on every node of a cluster. Any caller is refused with42501,This function is run by the server only.
Provisioning can hide data from the agent on its branch and cap the branch's size:
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:
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
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.
batchcarries 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.profileruns 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.
validatenames the specific broken object.