Relationships, Links & Joins (v2, Beta)

In Aito, related rows are connected by links, not copied together by joins. A link is a foreign key a column declares ("link": "products.id"). Once declared, you address a linked row's fields by a dotted path (product.name) and project, filter, sort, and predict through it — there is no row-multiplying cartesian product. A join is a path projection: nothing is copied.

That one mechanism has four spellings, and they all lower to the same link-path projection in the engine:

Spelling	Where	What it does
Dotted path — `product.name`	any `where` / `select` / `orderBy`	follow a forward link and read a field
SQL `JOIN … ON`	`_sql`	the same forward link, in SQL syntax
Link-join view	Schema Design	a saved link exposed as a navigable column
Inline join `from`	Query Reference	an ad-hoc link declared in the query

This guide covers the forward direction (links and joins), the inverse direction ($refs — reading a row from the rows that point at it), and the knowledge-graph patterns those two unlock.

Links are joins

Say orders has a product column linking to products.id. To read each order's product name and category, follow the link with a dotted path — no join clause, no duplicated rows:

{
  "from": "orders",
  "select": ["id", "product.name", "product.category"]
}

product.name resolves each order's linked products row and reads its name. The identical result in SQL is a join along that link:

SELECT o.id, p.name, p.category
FROM orders o JOIN products p ON o.product = p.id

Both lower to the same projection — the SQL JOIN is a thin skin over the link path, not a cartesian product (see SQL & Postgres → Joins). Filter and sort through links the same way (where { "product.category": "dairy" }, orderBy "product.price"). To expose a link as a first-class navigable column without repeating the path in every query, save it as a link-join view (see Schema Design → Link-join views).

Knowledge graphs: store flat, present grouped

Everything above is the forward direction. The rest of this guide reads a link the other way — and that is what turns a set of links into a queryable graph. Aito models graphs without a graph database: a graph is just two collections and a back-link — entities, the edges between them, and the $refs operator that reads an edge from the entity it points at. The payoff is a knowledge graph that is probabilistic, explainable, and incrementally writable — predict missing edges, classify a node from its neighborhood, and ground every answer in the edges that support it, all without training a model.

Store the graph as flat triples in an edges collection, with the nodes in an entities collection:

{
  "entities": {
    "type": "collection",
    "columns": {
      "id": { "type": "String" },
      "kind": { "type": "String" },
      "name": { "type": "Text" }
    }
  },
  "edges": {
    "type": "collection",
    "columns": {
      "id": { "type": "Int" },
      "subject": { "type": "String", "link": "entities.id" },
      "relation": { "type": "String" },
      "target": { "type": "String", "link": "entities.id" },
      "source": { "type": "String" }
    }
  }
}

One row per edge (subject —relation→ target). source records where the edge came from (for provenance). This "store flat, present grouped" model is the whole design — no adjacency lists, no nested sets, no graph type. A relation is just a string, so the vocabulary is open: add new relation kinds without a schema change.

Traversing forward

A forward link goes from a row to the row it points at. From edges you read the endpoints and their attributes with a dotted path:

{
  "from": "edges",
  "where": { "subject": "acme", "relation": "uses" },
  "select": ["target", "target.kind"]
}

target.kind resolves each edge's target entity and reads its kind — a join, expressed as a path.

Inverse links

The new piece is going the other way — from an entity to the edges that point at it. $refs.edges.subject on an entity is the set of edges whose subject is that entity: its neighborhood. (See the Query Reference for the full $refs surface.)

Does this entity have any edges?

{
  "from": "entities",
  "where": { "$refs.edges.subject": { "$exists": true } }
}

Which entities have an engaged_with edge? — filter the referring edges:

{
  "from": "entities",
  "where": { "$refs.edges.subject": { "$exists": { "relation": "engaged_with" } } }
}

Project the neighborhood — each entity's relations and targets as an array:

{
  "from": "entities",
  "where": { "kind": "prospect" },
  "select": [
    "name",
    { "relations": "$refs.edges.subject.relation" },
    { "targets": "$refs.edges.subject.target" }
  ]
}

// → { "name": "Alice", "relations": ["engaged_with", "works_at"], "targets": ["analytics", "acme"] }

Nothing is copied onto the entity row — the neighborhood is derived from the back-links. A query reads the same whether you had stored the edges on the row or discovered them via $refs.

Two hops

$refs composes with forward links for two-hop reach. Filter referring edges by a linked attribute of their target (one hop past the edge):

{
  "from": "entities",
  "where": { "$refs.edges.subject": { "$exists": { "target.kind": "product" } } }
}

Or resolve a forward link first and then its inverse links — <link>.$refs.…. On an emails collection whose sender links to an entity:

{
  "from": "emails",
  "where": { "sender.$refs.edges.subject": { "$exists": true } }
}

That last one returns emails whose sender has at least one edge. Deeper paths (three-plus hops, reachability) are composed by the caller across queries — an agent loops. There is no native recursive path query yet.

Typed edges

To ask for one edge that matches several attributes at once — e.g. an is-a → CTO edge, not "any is-a edge and (separately) any CTO edge" — give $exists an object of conditions. They all hold on the same referring edge:

{
  "from": "entities",
  "where": { "$refs.edges.subject": { "$exists": { "relation": "is-a", "target": "CTO" } } }
}

This is the query typed graphs need: it matches an entity only if it has a single edge that is both is-a and points at CTO. $has works in place of $exists, and this is the same shape a stored member-link set uses ({"items": {"$has": {…}}}) — so "same-member" reads identically whether the edges are discovered via $refs or stored as a set.

Predicting over the graph

Two prediction modes fall out of the same storage.

Node classification — predict an entity attribute from its neighborhood. Put a $refs condition in the where as evidence, then predict:

{
  "from": "entities",
  "where": { "$refs.edges.subject": { "$exists": { "relation": "engaged_with" } } },
  "predict": "segment"
}

The reverse-linked edges act as evidence with no data copied onto the entity — Aito weighs "has an engagement" toward the segment, with a $why explanation.

Edge prediction — predict the missing endpoint of a relation. Because an edge is a row with linked endpoints, predicting target given subject + relation is link prediction, and recommend over the target link ranks candidate endpoints:

{
  "from": "edges",
  "where": { "subject.role": "tech", "relation": "uses" },
  "predict": "target"
}

Condition on the subject's attributes (here subject.role), not just its id: that is what lets prediction generalize to a new entity that has no edges yet. Edge-level prediction is the newer of the two modes.

Provenance and explanations

source on each edge is provenance: filter by it, and count corroboration as the number of sources for a (subject, relation, target) — no stored confidence, the support is the signal. Retraction is deleting the sourcing edge. Combined with $why on a prediction (which names the contributing evidence), an agent can ground an answer in which edges, from what source, drove it.

Worked example

entities (companies, prospects, products, projects), edges (engaged_with, uses, sponsors, works_at), and emails (linked to a sender) is enough to:

classify — is a prospect engaged? where {$refs.edges.subject: {$exists: {relation: engaged_with}}};
prioritize — predict an email's priority from whether its sender is engaged (sender.$refs.edges… as evidence);
associate — from an entity, project its edges' targets to find related companies, products, and projects.

This is exactly the shape an agent maintaining a CRM-style knowledge graph needs: retrieve a neighborhood, predict the next best action, and cite the supporting edges.

Stored member-link sets (baskets)

The other way to relate rows is to store the links as a set on the row — a Set/Array of foreign keys — instead of discovering them via $refs. A basket of products, a person's tags, a document's authors:

{
  "baskets": {
    "type": "collection",
    "columns": {
      "id": { "type": "Int" },
      "items": { "type": "Int[]", "link": "products.id" },
      "label": { "type": "String" }
    }
  }
}

Each member is a real products row, so you query the set's linked attributes with a dotted path — and the query reads the same as the $refs surface, because a member-link set and a discovered $refs set are the same thing.

Match a member by its attributes — products.name is projected across the members and matched by token, so a shared word finds different brands:

{
  "from": "baskets",
  "where": { "items.name": { "$has": "banana" } }
}

This matches a basket whose products include "pirkka banana" or "chiquita banana" — the token banana is shared across brands (query a token, not the whole phrase). select ["items.name"] still returns the whole names.

Same-member conjunction — for one member that matches several attributes at once (not "some member is A and some other is B"), give $has an object:

{
  "from": "people",
  "where": { "items": { "$has": { "a": "fire", "b": "fighter" } } }
}

Matches a person who has a single item that is both fire and fighter — the same operator as the $refs typed-edge query above.

Predict a member — predict "items.$feature" scores each candidate member independently (non-exclusive): "given this basket, which product is most likely also present" — the link-prediction shape for stored sets.

What's not yet ergonomic

$refs makes graphs usable today; a few advanced conveniences are still to come (tracked in the predictive-graphs roadmap):

basedOn a neighborhood — predicting an attribute based on the whole set of reverse-linked edges as one named input (rather than a where condition) is not yet wired; the where-evidence form above covers the capability meanwhile.
Native multi-hop / path queries — two hops are built (above); N-hop reachability (A →*→ B) is composed by the caller across queries (an agent loops). There is no native recursive path query yet.
Graph aggregates — degree, neighbour counts, and weighted corroboration (number of sources for an edge) as first-class selectable/evidence features are a follow-on; today you count them by projecting and aggregating in the caller.