Building a semantic layer
Three concepts compose SchemaBrain’s semantic layer:
- Entities — a domain name (e.g.
customer, order) bound to one physical table.
- Metrics — aggregations anchored on an entity, with grain (e.g.
total_revenue by month).
- Canonical joins — the persisted answer to “how do entity A and entity B connect?”
All three are agent-visible through dedicated MCP tools and compile to parameterized SQL the agent never sees.
How the agent reaches the semantic layer
| Tool | What the agent asks it |
|---|
find_relevant_entities(query) | ”Which entities match this business concept?” — semantic search over the layer. |
list_entities() | ”What entities exist in this database?” |
describe_entity(name) | ”What does this entity expose? Columns, PII sensitivity, bound table.” |
resolve_join(entity_a, entity_b) | ”Give me the canonical SQL JOIN between these two entities.” |
get_metric(name, by=..., filter=..., via=...) | ”Compute this aggregation. Return rows + the SQL + an audit fingerprint.” |
find_relevant_tables, describe_table, describe_column, suggest_joins, get_example_queries) sit below them. Full reference: the MCP tool reference.
Author from scratch (no LLM)
Every definition is a small YAML file you can write by hand — the suggest commands in the sections below are just an LLM-assisted shortcut for producing the same files. To build a layer manually, follow three steps in order.
1. Index the source first. Entities bind to physical tables, so the store must know the schema before any definition will apply:
schemabrain index --url-env DATABASE_URL --store-path ./schemabrain.db
schemabrain entities apply ./schemabrain/entities --url-env DATABASE_URL --store-path ./schemabrain.db
schemabrain joins apply ./schemabrain/joins --url-env DATABASE_URL --store-path ./schemabrain.db
schemabrain metrics apply ./schemabrain/metrics --url-env DATABASE_URL --store-path ./schemabrain.db
origin: manual marks a hand-authored definition). The
join below connects two entities, so both must be defined — apply the
entities before the join:
# schemabrain/entities/order.yaml
version: 1
name: order
description: A customer order.
binding:
single_table: public.orders
identity: id
origin: manual
# schemabrain/entities/customer.yaml
version: 1
name: customer
description: A customer who places orders.
binding:
single_table: public.customers
identity: id
origin: manual
# schemabrain/metrics/total_revenue.yaml
version: 1
name: total_revenue
description: Sum of order totals per requested grain.
entity: order
measure:
agg: sum
column: total_cents
time_dimension: order.placed_at
time_grains: [day, week, month]
# schemabrain/joins/order_customer.yaml
version: 1
name: order_customer
description: Links each order to the customer who placed it.
source_entity: order
target_entity: customer
"on":
- source: user_id
target: id
cardinality: many_to_one
on columns, an entity’s identity — are validated when a query compiles, not when it applies. So apply accepting a file is not proof the layer works; confirm it end to end:
schemabrain entities list --store-path ./schemabrain.db # are all three present?
schemabrain metrics list --store-path ./schemabrain.db
get_metric) for one real number. A typo in a column name surfaces here as an unknown_name envelope listing the valid columns — not as a silent success at apply time.
Use the same database URL everywhere. The store keys everything by a source_connection_id derived from the connection URL, so schemabrain index and every later apply / serve must pass the same URL — same host, port, and scheme. Index under postgresql://… and apply under postgresql+psycopg://… and they key to different stores: the second command reports an empty / unindexed source even though the tables are right there. Pick one URL form and pass it consistently.
Entities
schemabrain entities suggest --url-env DATABASE_URL --dry-run
| Mode | What it does |
|---|
--dry-run | Print candidates to stdout with confidence + rationale + PII hints. No writes. |
--out-dir ./suggestions | Write one <entity>.yaml per candidate. Edit before applying. |
--apply | Write candidates straight into the store. |
--max-cost-usd (default $1.00) or $SCHEMABRAIN_MAX_LLM_COST_USD. Pair with --top-k N to cap candidate count.
Sample dry-run output:
# confidence: high
# rationale: users has id PK, NOT NULL email, referenced by sessions.user_id
# pii_hints:
# email: pii
version: 1
name: user
description: A workspace member account
binding:
single_table: public.users
identity: id
origin: suggested
-- 3 candidate(s) | model: claude-sonnet-4-6 | cost: $0.0271
list_entities and describe_entity.
Metrics
metrics suggest mirrors entities suggest — same three modes, same cost guards. The LLM picks the measure column (or composite expression), aggregation function, optional time dimension, and grain:
schemabrain metrics suggest --url-env DATABASE_URL --dry-run
schemabrain metrics suggest --url-env DATABASE_URL --out-dir ./metric-candidates
schemabrain metrics list --store-path ./schemabrain.db
metrics suggest refuses with a guided error pointing at entities apply.
Composite-expression measures
A metric’s measure can be either a single column or a composite expression over multiple columns of the same anchor table:
# Single column
measure:
agg: sum
column: amount_cents
# Composite expression (whitelist: identifiers, + - * /, parens, numeric literals)
measure:
agg: sum
expression: unit_price_cents * quantity
column or expression is required. PII propagation walks every column the expression references — touching one PII-tagged column taints the whole metric.
Canonical joins
Where entities suggest infers WHAT to query, joins suggest infers HOW two entities connect. Candidates are mined from FK constraints (always present) and query-log evidence (when schemabrain mine-queries has populated the example_queries table from pg_stat_statements).
schemabrain joins suggest --url-env DATABASE_URL --dry-run
schemabrain joins suggest --url-env DATABASE_URL --out-dir ./join-candidates
schemabrain joins apply ./join-candidates --url-env DATABASE_URL
schemabrain joins list --store-path ./schemabrain.db
resolve_join MCP tool returns the canonical join with a paste-ready JOIN ... ON ... skeleton. Multi-canonical-per-pair (billing vs shipping address, primary vs secondary user) is supported: pass name=<canonical_name> to disambiguate, or get a structured ambiguity refusal listing both.
Multi-hop join paths
get_metric accepts group_by= columns that live on a table reachable through multiple canonical joins from the metric’s anchor. The compiler BFSes the canonical-join graph (default max_hops=6), emits each JOIN against the previous hop’s alias, and returns a topologically chain-ordered MetricPlan.joins. When multiple paths are equally short, the agent gets a structured ambiguous_path refusal — disambiguate via via=(join_name, ...).
Import from dbt
If you already curate models in dbt, point SchemaBrain at your compiled target/manifest.json and import them as entities. Scope today: each dbt model with a single-column primary key becomes a SchemaBrain entity (dbt owns those rows), and dbt metrics import opt-in via --include-metrics. dbt relationships tests import as canonical joins (origin="dbt_import"); FK + query-log mining fills in any joins dbt doesn’t declare. Two entry points:
During init (auto-detected or explicit): the wizard’s stage 1 auto-detects a manifest from $DBT_PROJECT_DIR/target/manifest.json or by walking up from the cwd looking for dbt_project.yml. When found, stages 3 (entities) and 4 (metrics) route through the importer instead of the LLM. Force a specific manifest with --from-dbt PATH:
schemabrain init --url-env DATABASE_URL --from-dbt /path/to/dbt/target/manifest.json
relationships schema tests as canonical joins (each declared FK becomes a source → target join with origin="dbt_import"), and still mines FK constraints + the query log for joins dbt doesn’t declare.
Standalone import: if you want to import without going through the wizard, point the importer directly at a manifest. The importer binds each model to a table in the local index, so the store must already be indexed for the same source URL:
# Step 0 — index the schema first (skip if you already ran `init` against this URL).
schemabrain index --url-env DATABASE_URL --store-path ./schemabrain.db # add --no-enrich for a cost-free index
# Step 1 — import the dbt models as entities.
schemabrain import dbt path/to/target/manifest.json --url-env DATABASE_URL --store-path ./schemabrain.db
import dbt against a store that was never indexed for this URL and it short-circuits with a guided “run schemabrain index first” error rather than failing per-model. The importer also needs the physical tables to exist (run dbt run, not just dbt compile) since it verifies each model against the live schema — even under --dry-run.
Each dbt model with a single-column primary key lands as a SchemaBrain entity with origin="dbt_import", and each relationships test between two imported models lands as a canonical join (origin="dbt_import"). Re-running is idempotent; entities that previously had origin="manual" or "suggested" flip to "dbt_import" (dbt takes ownership), and joins upsert by name. Subsequent manual edits to dbt-owned rows are refused at the store boundary. A relationship whose endpoint model isn’t imported (or resolves to a self-join) is skipped and surfaced in the import summary.
| Flag | Behaviour |
|---|
| (default) | Plan + apply (entities only). |
--include-metrics | Also import type=simple dbt metrics (off by default). |
--dry-run | Compute the plan; write nothing (still connects to the live DB). |
--report report.json | Emit a CI-friendly JSON report. |
schemabrain import dbt $(schemabrain fixture-path ecommerce_manifest.json) \
--url-env DATABASE_URL --dry-run
Audit and repair
The local store can be checked for corruption and repaired:
schemabrain metrics audit # report-only
schemabrain metrics audit --fix # delete metrics with malformed measures
