An Ontology You Can Query: From a Blank Canvas to a Knowledge Graph

There is a version of “semantic layer” that ends in a triple store, a SPARQL endpoint, and a year of nobody using it. We did not build that one.

We built the other one. The schema — the concepts, their attributes, the typed edges between them — lives in Postgres. The actual data never moves. It stays in Iceberg, where it already was, and gets queried on demand through SQE/Trino. The ontology is virtual. There are no materialized triples, no graph database, no JVM sidecar to babysit.

That single decision shapes everything downstream. And there is a second decision that matters just as much: there is exactly one query translator, and everything else is a façade over it. MCP tools, a GraphQL endpoint, the SQL views that BI tools read — all of them compile down to the same JSON DSL and the same SQL generator. One place to get filters right. One place to instrument. One place to fix when it’s wrong.

This post walks the whole pipeline. Designing an ontology, mapping it to physical tables, publishing it into queryable views, exposing it to agents over MCP and GraphQL, and finally asking it questions and getting a chart back. I’ll be honest about the parts that don’t work yet, because there are several, and a couple of them will bite you silently if you don’t know they’re there.

  author                map                 publish              query                 show
 ────────             ───────              ─────────            ───────              ────────

 import ┐                                  generator           MCP tools ┐           insights canvas
 wizard ┼─▶ DRAFT ──▶ mappings ──▶ VALIDATE ──▶ ontology.* ──▶ GraphQL   ┼─▶ agent ─▶ (charts)
 manual ┘   (Postgres)  (entity→table)      views (SQE/Iceberg) run_sql  ┘
                                                                  │
                                              browser ──▶ /browse ┴──▶ Cytoscape (graph)

The shape of the thing

Before any of the machinery makes sense, you need the data model. It’s a property graph, not RDF. Four concepts do the work:

• Entity types — the nouns. Customer, Contract, Sale. Each has a snake_case name unique within a version, a display_name, and an optional iri for things imported from a real vocabulary.
• Attributes — the properties of a noun. data_type is one of seven values: string, number, integer, boolean, date, datetime, ref. At least one attribute per concrete entity has to be flagged is_identifier before you can publish.
• Relation types — the typed, directed edges. A relation has a source entity, a target entity, and a cardinality (one_to_one, one_to_many, many_to_one, many_to_many).
• Mappings — the binding from a virtual concept to physical storage. This is where the abstraction meets the table.

All of it is versioned. Every entity, attribute, relation, and mapping carries a version_id, and a version moves through a state machine: draft → validated → publishing → published → archived, with failed as the off-ramp. At most one version is published at a time, enforced by a Postgres partial unique index on a constant expression — a trick that says “at most one row where status = published” without needing a singleton table.

Why property graph and not RDF? Because the consumers are LLMs, BI tools, and humans, and all three read Customer.email more readily than they read an OWL class expression. RDF still round-trips — every node carries an optional iri and namespace, and there are OWL import/export adapters — but it’s an interop format, not the internal model. Palantir Foundry made the same call. So did Microsoft Fabric. The only platform that went native-RDF was a Labs project.

Dead end: inheritance. The schema has a parent_entity_type_id column. You cannot use it. The edit validator rejects any attempt to set it with ontology.inheritance_not_supported, because the view generator only emits a child’s own attributes — inherited columns never reach the published view. The column is there; the feature is not. A Person who is both a Customer and an Employee is a problem we deferred on purpose.

Three ways to author, one draft

You author an ontology three ways, and you can mix them freely because they all write to the same draft through the same service layer.

Manual. The Designer is a force-directed canvas. Drop an entity, name it, add attributes inline. Drag from one node’s handle to another to create a relation. Edits autosave on blur. Node positions persist to localStorage per version, so your layout survives a refresh. It’s the path you reach for when you know exactly what you want.

Import. Point it at an OWL/RDF file — Turtle, RDF/XML, JSON-LD, or N3 — or pick a bundled pack. The parser walks the graph in phases: collect owl:Class nodes into entity types, owl:DatatypeProperty into attributes (mapping XSD ranges to our seven data types through a 28-entry table), owl:ObjectProperty into relations with cardinality inferred from FunctionalProperty/InverseFunctionalProperty characteristics. Then it runs a heuristic to guess an identifier attribute, because OWL has no universal primary-key marker and a freshly-imported draft with no identifier fails to publish.

The preview is stateless — parse, transform, show you the result, write nothing. You uncheck what you don’t want. Commit runs a sequential loop with per-item outcomes, so one name collision doesn’t abort the whole import; it comes back as skipped_already_exists and the rest proceeds.

The spec lied about the packs. The design doc promised FIBO, schema.org, IOF, and CDISC. What ships is example_business, foaf, schema_org_core, and dcat. FIBO and the others are large and have licensing to sort out. They’re follow-ups, not features.

Dead end: IRI-based diffing. The spec described a differ.py that diffs an import against the current draft by IRI, with skip/merge/rename conflict resolution. That file doesn’t exist. Import does name-based collision detection only. Re-importing an updated vocabulary can’t selectively update the classes that changed. If you need that, it isn’t here yet.

LLM wizard. This is the highest-leverage path and the one with the most guardrails. You scope it to some catalogs and schemas, optionally add a domain hint, and the wizard pulls table schemas from Polaris and column lineage edges from the lineage module. It assembles a prompt — user free-text wrapped in <<<UNTRUSTED ... UNTRUSTED>>> fences so a crafted hint can’t forge instructions — and asks the model for entities, attributes, and candidate mappings, each with a confidence score and a sentence of evidence.

The model never writes. It returns structured JSON. You review it in a grid, set a confidence threshold, accept the rows you trust, and the frontend applies them through the normal CRUD endpoints. The lineage edges matter here: they give the model actual data-flow evidence for foreign-key relations instead of guessing from column names. Temperature is 0.1. We are not looking for creativity.

Mapping concepts to columns

A concept is useless until it points at a table. That’s what a mapping is, and there are three shapes:

The interesting one is join_sql. We store it as a raw SQL string with {source} and {target} placeholders, expanded at query time. Structured join models — equality-pair ASTs — don’t survive contact with reality. Real ontologies need conditions structured models can’t express. So we took the raw-string approach and built a hard wall around it instead.

The wall is a regex allowlist. Each AND-clause of a join_sql must match {source}.<col> = {target}.<col> with lowercase snake_case columns and nothing else. Semicolons, comment markers, function calls, subselects — all rejected before the string is ever stored. filter_sql gets the same treatment: comparisons against literals, nothing more. And the validation runs twice. Once when you save the mapping, and again at query-compile time, because the generator never trusts a value just because it came out of the database.

The view generator used to interpolate catalog, schema, table, and column names straight into CREATE VIEW DDL, unquoted. An ontology editor could set a table name to t UNION SELECT secret FROM admin-- and have it run against Trino under the platform service account at publish time. That was a real finding on main. The fix was a shared quote_identifier helper — double-quote everything, double embedded quotes, reject control characters — applied everywhere identifiers reach SQL. The join_sql allowlist was already doing this right; the DDL generator was the one place that wasn’t.

There’s a wizard for mappings too, separate from the entity wizard. It starts from existing entities and proposes column bindings, then post-filters its own output: any entity mapping whose table isn’t in the scanned catalog gets dropped, any attribute referencing a non-existent column gets dropped, any join_sql that fails the allowlist gets dropped. The model hallucinates table names; the filter catches them.

Dead end: many-to-many through a junction table. The FOAF demo wants foaf:knows — Person to Person through a friendships table. You can’t map it. The join_sql allowlist only accepts direct {source}.col = {target}.col equality, and a junction table needs two joins. The friendships table is seeded in the demo so the mapping can be added the day junction support lands. Today it’s a documented gap.

Publish: mappings become views

Publishing is where the virtual model becomes something a SQL engine can read. The generator turns each mapped entity into a CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW "ontology"."customer" AS
SELECT
  src."customer_id"    AS "id",
  src."email_address"  AS "email",
  src."customer_id"    AS "customer_id"
FROM "iceberg"."raw"."customers" src
WHERE src.is_deleted = false

Note the third column. It isn’t an attribute — it’s a foreign-key column that some relation’s join_sql needs, injected into the view so the join can resolve later. The generator walks both outgoing and incoming relations to find these columns and exposes them even when nobody declared them as attributes.

Note also the view schema: "ontology", two parts, catalog-relative. This is the scar tissue from a bug that’s worth telling in full.

Dead end, then a fix: iceberg.ontology. The original generator hardcoded the view schema as iceberg.ontology. The problem: SQE registers the Polaris warehouse under whatever TRINO_CATALOG is set to — in our deployment, main_warehouse. There is no catalog called iceberg. So the views targeted a catalog that didn’t exist, the DDL silently went nowhere, and a circuit breaker around the Trino client masked the failure. The views were never created and nothing said so. The fix is config.py: address the schema catalog-relative as just ontology, and let the engine resolve it against the session catalog the backend already sets. ontology.person becomes main_warehouse.ontology.person without the ontology code ever knowing the catalog name. Overridable via ONTOLOGY_VIEW_SCHEMA for deployments that need something fully qualified.

The publish flow itself is careful in Postgres and best-effort in Trino, because Trino doesn’t do DDL transactions:

1. Validate. Structural checks, identifier checks, mapping resolvability. Errors abort.
2. Generate all the view DDLs in memory.
3. Mark the version publishing in a Postgres transaction.
4. Ensure the schema exists, apply each DDL, then smoke-query every view with a SELECT COUNT(*).
5. If anything in step 4 throws — drop the views we managed to create, mark the version failed, raise.
6. Atomic flip in one Postgres transaction: archive the old published version, promote this one.
7. Invalidate the in-memory cache and fire the callback that rebuilds the per-entity MCP tools. If the callback fails, the views are still live; we record a warning in the audit log rather than reverting.

The Postgres state machine is transactional. The view application is not. A failure at view three of five leaves a mixed set until rollback cleans it up, and rollback is best-effort — it swallows individual drop failures because it’s already on the error path. The design doc described a staging schema with an atomic rename to avoid this. We shipped CREATE OR REPLACE in place instead. It’s simpler and it depends on no Trino features we might not have. The cost is that publish isn’t view-atomic.

The silent no-op that will get you. If the publisher is built with no executor — which happens whenever Trino isn’t reachable at startup, including most dev and test runs — the entire DDL stage is skipped. if self._executor is not None: guards all of it. The version still moves to published in Postgres. The audit log still says success. And zero views exist. There is no error, no warning, no failed status. In production this is a silent data gap: the ontology says it’s published, the BI tool finds no tables, and nothing in the system disagrees. Know this one.

After publish, a background job checks for drift every hour. It asks Polaris whether each mapped table still exists and whether each mapped column is still there with a compatible type. Missing table is hard drift (broken); missing column is soft drift (missing_columns); the status writes back to the mapping so the UI can show a badge. Type compatibility is deliberately loose — string accepts varchar, char, text, uuid; an unknown Iceberg type is treated as compatible — because a catalog introspection hiccup shouldn’t flip a healthy mapping to broken. The drift detector only checks the source side of a relation join today. The target side is a v1.1 item.

The query layer: one DSL, two façades

Here’s the payoff of the second big decision. Everything that queries the ontology compiles to the same JSON DSL and the same SQL generator. There are two façades over it that matter: MCP and GraphQL.

MCP, in two layers

The MCP server is a FastMCP app mounted at /api/platform/v1/ontology/mcp, speaking SSE. It exposes tools in two layers.

Layer 1 is static — six tools that exist from boot and survive any republish:

• list_entity_types() — the published nouns, with attribute and relation counts.
• describe_entity_type(name) — full metadata for one. On a miss it returns {error, available} so the model can recover.
• list_relations(), search_ontology(text) — discovery.
• get_query_dsl_schema() — the JSON Schema for the query DSL, so an agent can learn the filter operators before calling anything.
• run_sql(sql, limit) — the escape hatch.

Layer 2 is dynamic. On every publish, the server tears down the old per-entity tools and generates four fresh ones for each entity type: query_<entity>, get_<entity>, traverse_<entity>, aggregate_<entity>. The point is typing. An agent calling aggregate_sale sees the actual attributes of Sale in the tool schema, not a generic select: string. That typed surface is the single biggest accuracy win over a generic SQL API — the model can’t ask for a column that doesn’t exist because the schema won’t let it.

run_sql is the dangerous one, so it’s bounded hard. The SQL is parsed with sqlglot. The root has to be a SELECT or a set operation of selects — anything else is rejected. Every table reference is checked against an allowlist that contains exactly one schema: the ontology view schema. A join to hive.secrets is rejected. Then the whole query is wrapped in SELECT * FROM (<your sql>) LIMIT <capped>, where the cap maxes out at 10,000. The legacy iceberg.ontology is explicitly not in the allowlist, which doubles as a regression test for the catalog fix.

GraphQL, and the query it doesn’t run

The GraphQL endpoint builds its schema dynamically from the published ontology. Each entity type becomes an object type, each attribute a scalar field, each relation a nested object field — a list for to-many, a bare object for to-one. Cyclic relations work because the field maps are built through thunks. Depth is capped before any SQL runs; an over-deep query is rejected, not executed.

The interesting part is how a GraphQL traversal becomes SQL, because it isn’t the way the spec drew it.

Dead end: the single nested query. The design called for compiling each GraphQL query into one nested SQL statement with array_agg and json_object. The Phase-0 spike said no: SQE/DataFusion can’t run the correlated subqueries that approach needs. So the shipped executor is level-by-level batched. Fetch the root rows. Collect the foreign keys. Fetch all the children for those keys in one WHERE ... IN (...) with a ROW_NUMBER() window to cap per-parent. Recurse. A three-level traversal is three SQL queries total, regardless of how many rows are at each level — no N+1, but multiple round trips instead of one. The graphql/__init__.py docstring still claims it compiles to “one nested SQL statement.” That docstring is wrong. The code is right.

One more thing GraphQL gets right that’s easy to miss: it runs as the caller. The user’s token is forwarded all the way to SQE, so per-user catalog policy is enforced end to end. A missing token fails closed with a 401 — there’s no silent fall-back to the service identity for GraphQL the way there is for run_sql.

A worked example, grounded in what actually runs. Ask aggregate_sale to sum revenue by region:

aggregate_sale(
    group_by=["region"],
    metrics=[{"op": "sum", "field": "revenue"}],
)

The compiler emits:

SELECT c.region, SUM(c.revenue) AS sum_revenue
FROM ontology.sale c
GROUP BY c.region
LIMIT 1000

Catalog-relative schema. Alias is sum_revenue — {op}_{field}. No SQL touched the model.

The spec’s worked example does not run today. It reads FROM iceberg.ontology.contract c JOIN iceberg.ontology.customer cu ... WHERE cu.country = 'FR'. Two problems. iceberg.ontology is the catalog bug. And cu.country is a cross-relation dotted filter, which filter.py rejects at compile time — filtering across a relation isn’t supported (issue #300). If you copy that example out of the design doc, you ship something that throws. Ground your examples in the compiler, not the spec.

One footnote on safety, because it’s a real boundary: Trino’s REST API has no bound parameters. The compiler builds $1, $2 placeholders and then the engine inlines them as escaped literals before sending the query. String escaping is quote-doubling. That means the safety model rests entirely on the field-name regex, the join_sql allowlist, and the literal escaper — not on real parameterization. It holds, but it’s worth knowing where the boundary actually is.

Asking questions, and the agent that draws a chart

This is the part people see. A page at /insights with a report on top and a single chat box pinned to the bottom. You type a question, you get a report back — text, a KPI card, a bar chart. The report is the response.

Behind it is a Langflow flow. And here’s where I have to correct the question this post started from, because the request asked about “the subagents that create graphs,” and that’s not what’s there.

There are no subagents. The flow is four nodes and three edges, flat:

ChatInput ──▶ Agent ──▶ ChatOutput
                ▲
DataPlatformMCPTools

One agent. It gets its tools from one DataPlatformMCPTools component, which connects to the ontology MCP SSE endpoint and auto-discovers every tool — the list_entity_types, the aggregate_*, and the render tools. That’s the whole topology.

And the agent draws charts, not graphs. This distinction matters, so let me be precise: the agent produces recharts chart specs. It does not produce knowledge-graph visualizations. The knowledge graph is a different surface entirely, and I’ll get to it.

The way a chart gets made is the cleverest piece of the design. The render tools — render_text, render_kpi, render_chart — are near-no-ops. They take their arguments, ignore them, mint a block_id, and return {block_id, status: "ok"}. Nothing else.

async def render_chart(chart_type, data, x, series, title=None, ...):
    return {"block_id": _new_block_id(), "status": "ok"}

Why throw the arguments away? Because Langflow feeds every tool result back into the agent’s context on the next reasoning step. If render_chart returned the full chart spec, the agent would re-read a wall of JSON it just produced, burning tokens and getting confused. So the tool returns nothing useful to the agent. The actual chart gets built somewhere else.

That somewhere else is the SSE proxy. As the flow streams events through the backend, the proxy watches for tool calls to render_*. When it sees one, it reads the call’s arguments — which the agent produced anyway, as part of making the call — and builds the chart spec server-side. build_chart_spec validates the chart type, confirms the data is a list of rows, checks that the x and series columns exist, and emits an append_block event down the same SSE stream to the browser. The browser renders it with recharts.

The agent’s job, end to end: call aggregate_sale to get tidy rows, then call render_chart with those rows and the column names. It never builds chart config. The server does, deterministically, in under a millisecond, with no second LLM call.

The spec described a cheap-model fallback — Haiku reshaping free-form data that doesn’t fit the aggregate pattern. It wasn’t built. build_chart_spec is deterministic-only. Pass it data it can’t parse and it raises, and the block degrades to an error text block — “Could not render chart” — without breaking the stream. A bad chart never takes down the report.

There’s a smaller, equally deliberate trick on the prompts. The “what can I ask” suggestions are filtered against the live ontology, and the browser does that filtering by being an MCP client itself. The frontend imports the MCP SDK, opens an SSE connection to the same endpoint the agent uses, calls list_entity_types, and keeps only the suggested prompts whose required entities actually exist. If the MCP call fails — network, auth, parse — it falls back to the one prompt that requires nothing: “What can I ask?” The page degrades to a single safe suggestion instead of a broken list. No bespoke REST endpoint was added for this. MCP is the data surface, even for the UI.

The batched-rendering gotcha. Langflow 1.3 agent flows return JSON, not a token stream, even with streaming requested. So all the tool calls and their arguments arrive at the end of the run. The canvas fills all at once when the agent finishes, not block by block as it reasons. It looks like a pause and then a complete report. That’s accepted for now; progressive rendering needs a deeper Langflow integration than we have.

The other graph

The knowledge graph is real. It’s just not the agent’s doing.

KnowledgeGraphBrowser is a separate page, driven by REST /browse/* endpoints, rendered with Cytoscape and a force-directed cola layout. No Langflow, no MCP, no agent. You search for an instance, it becomes the center node, and you expand one relation at a time — each expansion is a /browse/{entity}/{id}/relations/{relation} call that fetches up to 50 neighbors and draws the edges. Nodes are colored by entity type. The selected node’s neighborhood stays bright; everything else dims to 25%. Re-expanding the same relation is idempotent because the instances live in a Map keyed by entity:id.

We chose Cytoscape over the React Flow library the designer uses, because a knowledge-graph view is a hundred small dot-nodes and a canvas pipeline handles that more smoothly than React Flow’s SVG cards. Same force-directed idea, different tool for a different node count.

So there are two “show me a graph” surfaces, and the request conflated them. The insights canvas, where an agent answers a question with charts. And the browser, where a human walks the graph by hand. They share the ontology and nothing else. Worth keeping straight, because they fail differently and they’re owned by different code.

The honest ledger

The system works, and the design has held up. But “extensive” should mean honest, so here is what I’d watch, roughly in order of how much it would surprise an operator:

• Executor-None publishes succeed silently. No views, no error, published status. The highest-severity footgun in the system. It needs at minimum a loud warning, ideally a refusal to mark published when no executor is present.
• Non-atomic view publishing. CREATE OR REPLACE in place, best-effort rollback. A mid-publish failure leaves a mixed view set. The staging-and-rename design would fix it; we didn’t ship it.
• The GraphQL schema rebuilds on every request. build_graphql_schema runs per POST. It should be cached per version and invalidated on publish.
• The windowed batch-fetch is implemented three times — once in the MCP server, once in the dataloader, once in the GraphQL executor — with subtly different parameter layouts. That’s three places for the same bug. A shared helper is overdue.
• No type casting in the views. The generator emits src."col" AS "attr" with no CAST. The GraphQL date scalars assume the engine returns proper types from uncast columns. The spec’s casting phase wasn’t built.
• Cross-relation filters are rejected (issue #300), GraphiQL is gated off behind an env flag, and MCP has no per-tool RBAC — any authenticated user can call any tool, including run_sql, against every view.
• The docstrings lie in two places — the GraphQL __init__ still claims single-nested-query compilation, and a couple of generator comments still say iceberg.ontology. Read the code, not the comments.

None of these are reasons not to use it. They’re the map of where the edges are. The core bet — virtual ontology, one translator, many façades — is the right one, and you can feel it every time a new consumer turns out to be a thin shim over compile_query instead of a rewrite. The façades are additive. That was the whole point.

The thing I’d build next isn’t on this list. It’s the staging-rename publish, because the silent no-op and the non-atomic apply are the same wound, and closing it once closes both.