> ## Documentation Index
> Fetch the complete documentation index at: https://schemabrain.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# ADR 0010 — Knowledge-graph projection backend (populate + GET /api/graph)

> PR-16 lands the backend half of the knowledge-graph surface: a persisted denormalised graph_nodes/graph_edges projection of entities + canonical joins, a public canonical-path builder that reuses the resolver's BFS with no metric dependency, and a read-only GET /api/graph. This ADR records the load-bearing decisions: where the projection is rebuilt (the semantic-write commands, NOT `index`-as-sole-hook), how the catastrophic floor stays consistent with the PII matrix (live overlay in the route), how the single canonical path is chosen honestly (longest deterministic shortest path), and the edge-evidence vocabulary (declared FK / log-mined / inferred — never inspected SQL).

# ADR 0010 — Knowledge-graph projection backend

* **Status:** Accepted
* **Date:** 2026-06-05

## Context

The marketed-vision launch (`docs/internal/marketed_vision_launch_plan_2026_06_01.md`)
makes the knowledge graph the signature surface. PR-16 is its **backend
half**: `wsGRAPH-populate-on-index`, `wsGRAPH-api-route`,
`wsGRAPH-canonical-path-builder`, `wsGRAPH-web-types-and-fetch`. The
reactflow surface itself is PR-17.

The v15 migration already created two **empty** tables
(`graph_nodes`, `graph_edges`; `core/store.py`) in `_DDL_STATEMENTS` — a
denormalised read-model of `entities` + `canonical_joins`. No code
populates or reads them yet. PR-16 is the first writer + reader.

Four questions had no obvious answer and would be one-way doors if
guessed wrong:

1. **Where is the projection rebuilt?** The original plan said "at index
   time." But `schemabrain index` (`indexer.py:219`, `cli.py:_cmd_index`)
   indexes the *physical* schema (tables/columns/PII/embeddings/row-counts)
   and writes **no entities and no joins** — those come from `entities apply`,
   `joins apply`, `apply-project`, the init wizard, and dbt import. Hooking
   `index` alone would persist an empty graph.
2. **How does the catastrophic floor stay consistent with the PII matrix?**
   `graph_nodes.is_catastrophic` must never disagree with `/api/entities` /
   the PII matrix, which derive "catastrophic" live from
   `CATASTROPHIC_LEAK_CATEGORIES` (`pii/categories.py`). PII tags can change
   (`policy tag override`, re-`index`) without the graph's *structure*
   changing.
3. **What is "the" canonical path of an arbitrary schema?** The design
   handoff renders a hardcoded `order_item → order → user → tenant`. There is
   no honest way to fabricate a single spine by a fuzzy "find the root"
   heuristic.
4. **What vocabulary describes an edge's provenance?** The honesty charter
   (ADR 0001, launch §8) forbids any phrasing implying the engine inspects
   agent-authored SQL.

## Decision

### 1. The projection is a persisted read-model, rebuilt by the semantic-write commands.

`graph_nodes` / `graph_edges` stay a **persisted** projection (not
compute-on-read): the route reads a flat shape; it never re-walks the FK
graph per request. The projection is recomputed by an idempotent
`store.write_graph_projection(...)` (DELETE-by-source + `executemany`
INSERT in one `with conn:` transaction — mirrors `write_column_pii_tags`).

A single CLI helper `_refresh_graph_projection(store, source_id)` is
called at the **success path** of the commands that change a projection
input that is *served from the persisted shape*:

* `_cmd_index` — refreshes `row_count` + the at-rest catastrophic snapshot.
* `_cmd_entities_apply` — entity set / `group`.
* `_cmd_joins_apply` — edges.

`apply-project` is covered transitively (it delegates to
`_cmd_entities_apply` → `_cmd_joins_apply`).

**Deferred to a documented fast-follow (cause structural staleness only —
a new entity/join is not projected until the next `index`/`apply`, never a
floor inconsistency):** `entities suggest --apply`, `joins suggest --apply`,
`import dbt`, and the **init wizard** (the wizard's graph hand-off is owned
by `wsINIT-graph-payoff`, PR-22, which must add the rebuild there).

### 2. The catastrophic floor is a LIVE overlay in the route — never stale across surfaces.

The route computes each node's `catastrophic` boolean **live** from the
current column PII tags, via the same `_entity_pii_level` /
`CATASTROPHIC_LEAK_CATEGORIES` path the PII matrix and `/api/entities` use.
This makes the floor consistent with `/pii` **with zero staleness window**,
even after a bare `policy tag override` that no projection rebuild saw.

The persisted `graph_nodes.is_catastrophic` is still written by the
projection as the **at-rest read-model snapshot** (DDL coherence; consumed
by PR-17's server seed and any direct-table reader). The route's live
recompute is authoritative for the served response. This is deliberate
defence-in-depth on the one cross-surface invariant that must never break,
not a contradiction: a structural-only hook set cannot, by construction,
leave the served floor inconsistent with the matrix.

`row_count` and `group` are served from the persisted projection (refreshed
on `index`/`apply`); they are structural/cheap and carry no safety claim.

### 3. The single canonical path = the longest deterministic shortest path.

`canonical_path` is the **graph's longest shortest path** (its diameter):
honest ("the longest canonical-join chain in your schema"), deterministic,
and computed by reusing the resolver's BFS. For the SaaS demo this is
exactly `order_item → order → user → tenant`. Ties break lexicographically
on the entity-node-name sequence. Empty when the graph has \< 2 connected
entities. The diameter scan is bounded by the entity count (the longest a
simple path can be), **not** the metric resolver's 6-hop default — a deep
schema's spine is never silently truncated.

* `build_canonical_path(*, joins, anchor, target)` is the public, reusable,
  **metric-free**, **pure** primitive (`semantic/compiler/resolve.py`,
  alongside the helpers it reuses). It takes an in-memory `list[CanonicalJoin]`
  (the caller resolves it from the store) and runs `_build_join_graph` →
  `_structural_shortest_paths` over it — it **never** touches a store and
  **never** routes through `resolve_metric_plan` / `store.get_metric`. Unlike
  the metric compiler it **never raises**: it returns an empty path when the
  endpoints are absent / equal / unreachable (the read-model has no `via=`
  disambiguation surface, so parallel canonicals on a hop collapse to the
  first-alphabetical name — mirroring `_render_structural_path_as_canonical_sequence`).
* `longest_canonical_path(*, joins)` picks the diameter endpoints
  deterministically and returns that path. `rebuild_graph_projection`
  (`semantic/graph_projection.py`) reads `store.list_canonical_joins` once and
  hands the list to it — the store read lives in the projection layer, not the
  pure builder.
* The projection writes `canonical_path_rank = 1` on the edges of the chosen
  path; all other edges keep the default `0`. Rank `2` (alternate paths) is
  reserved for PR-17 and intentionally **not** emitted yet (the `CHECK
  (0,1,2)` already permits it). The route reconstructs the ordered
  `canonical_path` from the **rank-1** edge set only (a trivial
  degree-1-endpoint walk — not a re-walk of the full graph); rank-2 edges are
  excluded so a future alternate never folds the primary path into a cycle.

### 4. Edge evidence vocabulary: declared FK / log-mined / inferred — never "inspected SQL".

`graph_edges.edge_origin` (`CHECK IN ('declared','log_mined','inferred')`)
is projected from `CanonicalJoin.inference_method`:

* `fk_constraint` → `declared`
* `observed_in_query_log` → `log_mined` (mirrors the existing
  `is_log_mined` test at `sidecar.py`)
* everything else (`manually_authored`, `llm_suggested`, `dbt_import`) →
  `inferred`

The wire/`GraphResponse` carries `evidence` verbatim as that closed union;
honest display labels are "declared FK" / "log-mined from query logs" /
"inferred". Never "inspected SQL" — the engine is credential-less and
store-only; FK edges are DB-declared and log-mined edges come from
query-log mining only.

## Wire contract (`GET /api/graph`, `DASHBOARD_SCHEMA_VERSION` 1.5 → 1.6)

```jsonc theme={null}
{
  "source_connection_id": "<credential-safe label>",
  "nodes": [
    { "id": "order_item", "label": "order_item", "group": "activity",
      "catastrophic": false, "row_count": 481923 }
  ],
  "edges": [
    { "id": "order_item_to_order", "source": "order_item", "target": "order",
      "evidence": "declared", "canonical_path_rank": 1 }
  ],
  "canonical_path": { "nodes": ["order_item","order","user","tenant"],
                      "edges": ["order_item_to_order","order_to_user","user_to_tenant"],
                      "hops": 3 }
}
```

* GET-only (passes `assert_route_table_is_read_only`); charter `1.2` +
  `X-Schemabrain-Dashboard-Schema` headers stamped by middleware (not the
  handler).
* No resolvable source → **409** (reuses `_resolve_source`). A source that
  exists but has an empty projection → **200** with empty arrays.
* The resolved source is always passed through `_credential_safe_source_label`
  before it reaches the body — a URL-shaped override never leaks a password.

## Consequences

* **Positive:** the route is a clean pure-read of a persisted shape; the
  catastrophic floor can never disagree with the matrix; the canonical path
  is honest and deterministic; no new dependency; no schema migration
  (`SCHEMA_VERSION` stays `"15"` — the tables already exist).
* **Negative / accepted:** the persisted `is_catastrophic` snapshot is
  recomputed live by the route (justified defence-in-depth, above).
  Structural staleness after the deferred commands (`suggest --apply`, dbt
  import, init wizard) is a known gap closed by the next `index`/`apply` and
  by PR-22 for the wizard; it is documented, not silent.
* **Follow-up:** PR-17 consumes the persisted projection (server seed),
  emits rank-2 alternate paths, and renders the reactflow surface. The
  deferred rebuild hooks are tracked for a fast-follow.