Skip to main content

Using Graphs

By the end of this page you will be able to model a graph as ordinary tables, traverse it with recursive SQL, and combine a graph hop with a similarity or full-text score from the bundled packages, all in one query, all on the same live data as the rest of your application.

ScramDB has no separate graph database and does not need one for most agent workloads: WITH RECURSIVE is a real, working iterative fixpoint executor, not a parser-only stub, so graph-shaped traversal is ordinary SQL over ordinary tables.

Model nodes and edges as tables​

CREATE TABLE entities (
    id    BIGINT PRIMARY KEY,
    kind  TEXT NOT NULL,
    label TEXT NOT NULL
);

CREATE TABLE relationships (
    id          BIGINT PRIMARY KEY,
    src_id      BIGINT NOT NULL REFERENCES entities(id),
    dst_id      BIGINT NOT NULL REFERENCES entities(id),
    rel_type    TEXT NOT NULL,
    weight      DOUBLE PRECISION DEFAULT 1.0
);
CREATE TABLE documents (id BIGINT PRIMARY KEY, title TEXT, embedding TEXT);
INSERT INTO documents VALUES (101, 'Billing FAQ', '[0.10, 0.20, 0.30]');

CREATE INDEX ON relationships (src_id);
CREATE INDEX ON relationships (dst_id);

Foreign key constraints are enforced, so a relationships row cannot reference an entities id that doesn't exist. Indexing both src_id and dst_id is the standard advice for any join-heavy traversal, graph or not; section 3 below says more about why it matters here specifically.

Traverse with recursive CTEs​

WITH RECURSIVE runs an anchor query once, then repeatedly runs a recursive term against the CTE's own accumulated rows until either the recursive term stops producing new rows or a configured iteration cap is hit (max_recursive_iterations, default 1000). A plain UNION (not UNION ALL) also deduplicates every previously produced row on every iteration, which doubles as a real cycle guard: a UNION recursive CTE cannot loop forever revisiting the same row, because a repeated row is filtered out before the next iteration runs.

Before you run these in production: the four queries below are composed from confirmed-real primitives (WITH RECURSIVE, its UNION/UNION ALL dedup behavior, and the iteration cap) but were written, not executed, against a live server for this page. Smoke-test each one against your own instance before relying on it.

N-hop neighborhood​

Everyone within 3 hops of entity 1:

WITH RECURSIVE neighborhood(id, depth) AS (
    SELECT id, 0 FROM entities WHERE id = 1
    UNION
    SELECT r.dst_id, n.depth + 1
    FROM neighborhood n
    JOIN relationships r ON r.src_id = n.id
    WHERE n.depth < 3
)
SELECT DISTINCT id, MIN(depth) AS depth
FROM neighborhood
GROUP BY id
ORDER BY depth, id;

This uses UNION, so the executor's own dedup pass doubles as a cycle guard on top of the depth < 3 bound. Keep the explicit depth bound anyway: it is the real, load-bearing termination condition for the traversal you actually want, not just a safety net.

Shortest path by hop count​

From entity 1 to entity 7:

WITH RECURSIVE paths(id, hops) AS (
    SELECT id, 0 FROM entities WHERE id = 1
    UNION
    SELECT r.dst_id, p.hops + 1
    FROM paths p
    JOIN relationships r ON r.src_id = p.id
    WHERE p.hops < 10
)
SELECT MIN(hops) AS shortest_hops
FROM paths
WHERE id = 7;

Ancestor walk​

Walking upward through a lineage or dependency graph, following dst_id -> src_id, starting from entity 42:

WITH RECURSIVE ancestors(id, depth) AS (
    SELECT src_id, 1 FROM relationships WHERE dst_id = 42
    UNION
    SELECT r.src_id, a.depth + 1
    FROM ancestors a
    JOIN relationships r ON r.dst_id = a.id
    WHERE a.depth < 20
)
SELECT DISTINCT id FROM ancestors;

A descendant walk is the mirror image: swap which side of the edge you start from and which side you join on.

Cycle-safe traversal with an explicit visited set​

Use this shape when the path itself matters, not just which nodes are reachable, for example listing every distinct path rather than every distinct node. UNION ALL is required here because the recursive term accumulates a growing path array, so the engine's own row-level dedup guard does not apply (two different paths can legitimately reach the same node). The query supplies its own visited-set check instead:

WITH RECURSIVE walk(id, path, depth) AS (
    SELECT id, ARRAY[id], 0 FROM entities WHERE id = 1
    UNION ALL
    SELECT r.dst_id, walk.path || r.dst_id, walk.depth + 1
    FROM walk
    JOIN relationships r ON r.src_id = walk.id
    WHERE walk.depth < 6
      AND NOT (r.dst_id = ANY(walk.path))
)
SELECT * FROM walk;

NOT (r.dst_id = ANY(walk.path)) stops the walk from re-entering a node already on its current path. The depth < 6 bound is defense in depth on top of that, not a substitute for it.

Practical scale​

  • Index both src_id and dst_id on the edge table, as shown above. This is ordinary join advice, not a special graph feature, but a traversal is nothing but joins, so it matters more here than most places.
  • Always carry an explicit depth or hop bound in the recursive term, as every query above does, in addition to relying on the server's max_recursive_iterations cap (default 1000). The cap is a safety net against a genuinely runaway query; it is not a substitute for a bound matched to the traversal you actually want.
  • The graph lives in the same live copy as the transactional data it describes. If your entities rows are also rows in a real orders, users, or documents table, a traversal can JOIN straight into that transactional data in the same query. There is no export to a separate graph store and no sync lag between the graph and the source of truth it models.

Agent angle end to end​

A single example that builds a small graph, traverses it, and ranks the result by similarity, in one query:

CREATE TABLE agent_documents (id BIGINT PRIMARY KEY, entity_id BIGINT, body TEXT, embedding TEXT);

INSERT INTO entities VALUES
    (1, 'user', 'Alice'),
    (2, 'topic', 'billing'),
    (3, 'topic', 'refunds'),
    (4, 'document', 'doc-101');

INSERT INTO relationships (id, src_id, dst_id, rel_type) VALUES
    (1, 1, 2, 'interested_in'),
    (2, 2, 3, 'related_to'),
    (3, 3, 4, 'discussed_in');

INSERT INTO agent_documents VALUES
    (101, 3, 'How refunds are processed end to end.', '[0.11, -0.02, 0.88]'),
    (102, 4, 'doc-101 full text: refund policy details.', '[0.13, -0.05, 0.90]');

WITH RECURSIVE context(id, depth) AS (
    SELECT id, 0 FROM entities WHERE id = 1
    UNION
    SELECT r.dst_id, c.depth + 1
    FROM context c
    JOIN relationships r ON r.src_id = c.id
    WHERE c.depth < 2
)
SELECT d.id, d.body
FROM context c
JOIN agent_documents d ON d.entity_id = c.id
ORDER BY embeddings_cosine_similarity(d.embedding, '[0.12, -0.04, 0.91]') DESC
LIMIT 5;

The context CTE assembles every entity within 2 hops of Alice, a graph hop. Joining that against agent_documents and ordering by embeddings_cosine_similarity (installed as shown in Building Agent Features) turns the result into a semantic ranking. Retrieval is both structural (the graph hop decides what's in scope) and semantic (the similarity score decides what's best), in one statement.

Tie to branching​

An agent can build and mutate a graph entirely on a throwaway branch created per Branching for Agents: populate entities and relationships with speculative or exploratory edges, such as entity-resolution candidates or a hypothesis graph, traverse and query them freely with the recursive patterns above, and DROP DATABASE the branch when done. None of it carries any risk to the real graph or the transactional data it was built next to on the source database.