Schema Design (v2)

This guide explains how to design schemas for the v2 (rep2 / "collection") engine. The modelling principles are the same as v1 schema design, but v2 adds schema-flexible JSON ingest and a few type refinements. A well-designed schema is what makes prediction, recommendation and relation analysis work well.

Creating a collection

v2 tables come in two types — collection (the rep2 engine: schema-flexible, columnar, the focus of v2) and the legacy table (rep1, still the default). See CollectionDb for the full comparison and when to use each. For a designed schema, create a collection explicitly:

PUT /api/v2/schema/products
{
  "type": "collection",
  "columns": {
    "name":     { "type": "String" },
    "price":    { "type": "Decimal" },
    "category": { "type": "String" }
  }
}

Schema-flexible ingest

Unlike v1, a v2 collection can infer and evolve its schema from the data:

• Import JSON directly — POST /api/v2/data/{table}/import accepts an array of objects and creates the table, inferring column types:

  POST /api/v2/data/products/import
  [
    { "name": "Aito Mug", "price": 12.0, "category": "merch" },
    { "name": "Aito Tee", "price": 25.0, "category": "merch" }
  ]
  

• Incremental evolution — appending rows with new fields extends the schema; existing rows treat the new column as absent (null).

This makes v2 well suited to event/log data whose shape grows over time. You can still define the schema up front when you want strict typing.

Evolving a schema

A v2 collection's schema is not frozen. Beyond the automatic inference above, you can change it deliberately — add or alter columns in place, or converge a whole table to a target schema declaratively (_plan / _apply, like terraform plan / terraform apply). Those operations re-index and compact data, so they have their own guide: see Schema Migration & Maintenance for the full treatment — declarative plan/apply, add/alter a single column, bulk backfill, and optimize.

Data types

Beyond these there is one experimental type, x-knowledge, described at the end of this page.

Append [] to any of these for an array column (String[], Int[], Date[], …) — useful for tag lists, baskets, or multi-value attributes, which you can then match with $has.

Date is first-class, not just stored: queries can filter and predict on its components — $year, $month, $day, $weekday, $dayOfYear (see the Query Operator Reference), so "weekday vs weekend" or "end-of-quarter" becomes usable evidence.

Timestamp goes further. Because every value normalises to a UTC instant on write, a mixed-offset import can never mis-order a range filter — the thing that silently goes wrong when timestamps are kept as strings. Beyond $hour, $minute and $quarter (on top of the date components above), declaring a Timestamp column materialises its derived parts as real columns — ts.weekday, ts.hour, ts.month, ts.weekOfMonth, … — which predict and relate then use as ordinary features and targets. Cyclic structure ("orders spike on Saturday afternoons", "this job fails Saturday night") is learned out of the box, instead of being hand-bucketed at load time.

Numeric quantity flag

Numbers play two different roles, and v2 lets you say which:

• A quantity (price, age, score) — nearby values are similar and should share statistical evidence. These bin by default, so price 209 and price 219 inform each other.
• An identifier (a user id, a product code) — each value is its own entity; id 4 should not borrow anything from id 5.

Defaults: Decimal is a quantity, Int/Long are identifiers. Override with the quantity flag when the default is wrong:

{
  "type": "collection",
  "columns": {
    "price":  { "type": "Decimal" },
    "userId": { "type": "Int" },
    "age":    { "type": "Int", "quantity": true },
    "score":  { "type": "Decimal", "quantity": false }
  }
}

A quantity column's values are smoothed toward their neighbours during inference (the same mechanism as the $numeric operator), which is what makes a numeric feature useful as evidence.

Nullable columns

Mark a column nullable to allow missing values. v2 stores a true defined-mask for scalar columns (the null/value distinction round-trips through persistence), and you can query it with $exists.

"shippedAt": { "type": "Long", "nullable": true }

Vector columns

A Vector column stores a fixed-length dense array of numbers — a pre-computed embedding — for similarity search with the $nearest operator. Two fields are required:

• dimensions — the vector length. Every value must have exactly this many components; a mismatch is rejected on ingest.
• similarity — how vectors are compared: cosine (direction; magnitude ignored) or dotProduct (raw inner product).

{
  "type": "collection",
  "columns": {
    "id":        { "type": "Int" },
    "embedding": { "type": "Vector", "dimensions": 384, "similarity": "cosine" }
  }
}

For a cosine column, values are unit-normalised on ingest (a zero vector has no direction and is rejected), so query-time scoring is an exact dot product and $similarity is the cosine in [-1, 1]. dotProduct stores values as given. Aito does not generate embeddings — you supply the vectors (from your own model) as an array of numbers per row. Mark the column nullable to allow rows with no vector; those rows are skipped by $nearest rather than scored.

Primary keys & identity columns

By default a v2 collection is an observation log: every inserted row is kept, and re-uploading the same row genuinely adds a second observation (two identical purchase rows really do raise P(purchase)). That is the right default for event data, but wrong when a table is an entity store keyed by an id. Two optional, SQL-faithful knobs let you say which you mean. Both are a rep2 capability — declared on a collection (or a table with engine: "v2"); declaring either on a rep1 table is rejected.

primaryKey — a unique, not-null key

Add a table-level primaryKey to declare that a column (or a tuple of columns) uniquely identifies a row. Semantics are pure SQL — unique and not-null:

PUT /api/v2/schema/customers
{
  "type": "collection",
  "columns": {
    "email": { "type": "String" },
    "name":  { "type": "String" },
    "org":   { "type": "String" }
  },
  "primaryKey": "email"
}

• The value is a column-name string, or an array for a composite key ("primaryKey": ["org", "email"]) — a row collides only when it matches every key column.
• A key column must be one of the declared columns (a typo'd key name is rejected up front), and every inserted row must supply a value for each key column — a missing or null key value is an error, not a silently-stored null.
• On insert, a row whose key already exists is rejected by default rather than appended (no silent duplicate). To replace or skip instead of failing, use the batch ?on_conflict parameter.

Dedup only ever happens against this declared key — Aito does not dedup on an arbitrary column with no constraint.

identity: true — a server-assigned surrogate id

Mark a single Int or Long column identity: true to have Aito assign it an auto-incrementing surrogate id whenever an inserted row omits it:

PUT /api/v2/schema/orders
{
  "type": "collection",
  "columns": {
    "id":     { "type": "Int", "identity": true },
    "amount": { "type": "Decimal" }
  },
  "primaryKey": "id"
}

• The id is server-assigned and persisted, and never reused — it keeps climbing across deletes, optimize, and reopen, so an id always refers to at most one row for the life of the table.
• A row that supplies the column explicitly is passed through untouched (and does not advance the sequence) — so you can mix imported rows that carry ids with new rows that let the server allocate them.
• The flag only applies to an Int/Long column, and a table may declare at most one identity column.
• Identity (a clean handle for CRUD) and primaryKey (uniqueness) are independent: an identity column may also be the primaryKey (the Mongo/ES _id case, above), or you can keep a surrogate id for addressing rows and a separate business key (email) as the primaryKey.

Linking tables

Links turn a column into a cross-table reference, so a query can navigate from one table to another and use the linked table's fields as evidence. Use dotted paths in queries (e.g. processor.role).

PUT /api/v2/schema/invoices
{
  "type": "collection",
  "columns": {
    "amount":    { "type": "Decimal" },
    "processor": { "type": "String", "link": "employees.id" }
  }
}

Now a predict on invoices can draw on employees fields via basedOn (see the Inference guide). Cross-table predict / recommend / relate are first-class in v2.

Links traverse multiple hops. v1 resolved links to a single hop — processor.company worked, but processor.company.name did not. v2 follows the chain, so a where, select, or predict can reach processor.company.name and deeper. You no longer have to flatten the join in your application.

Links decide what a predict can answer

When you predict a column, the candidates are that column's value set — for a link, every row of the linked table; for a plain column, every value that occurs in it. The where of the predict is evidence: it changes each candidate's probability but never removes candidates, so values that never occurred with the evidence come back with $f: 0 and a small $p (the full rule is in Inference → Candidates, evidence, and $f).

That makes the link design a scoping decision when one database serves many customers:

• A value table per tenant. Link each tenant's rows to a table that holds only that tenant's values — its own chart of accounts, its own employees. A predict can then only ever answer with that tenant's values, and a value added to the table is a candidate before its first use.
• One shared value table. Link every tenant to the same table and scope in two steps: first ask for the tenant's own values (a predict whose where holds only the tenant, with "having": { "$f": { "$gte": 1 } }), then run the real predict and keep only hits in that set. A nested from scopes the base rates to the tenant but not a link target's candidates — see Inference → Scoping candidates per customer.

If tenants must never see each other's values, choose the first design (or a database per tenant); having shapes what a query returns, it is not an access control. Either way the candidates are only half of it: which rows the statistics come from is decided by the population, not the links. See Multi-tenant isolation for the patterns that keep one tenant's rows from changing another's answers, per query type.

A worked example

A minimal e-commerce setup — products, and impressions that link to them:

PUT /api/v2/schema/products
{ "type": "collection",
  "columns": {
    "id":       { "type": "String" },
    "name":     { "type": "Text", "analyzer": "english" },
    "price":    { "type": "Decimal" },
    "category": { "type": "String" } } }

PUT /api/v2/schema/impressions
{ "type": "collection",
  "columns": {
    "user":    { "type": "String" },
    "product": { "type": "String", "link": "products.id" },
    "click":   { "type": "Boolean" } } }

With this, you can recommend products to a user, rank them by P(click), or relate clicks to product category — all covered in the Inference guide.

Views — derived, queryable relations (early access)

This is the canonical home for what a view is and how to create one; the Query Reference lists the from forms tersely, and Relationships, Links & Joins covers how link-joins behave in a query.

A view is a derived, queryable relation you define in the schema. The union-merge view stacks rows from several collections into one, projecting each source's fields into a common schema — most usefully a single content text field, giving one full-text index across all of them. Typical use: search customers, deals, knowledge articles, and R&D items from one query.

curl -X PUT https://$AITO_INSTANCE/api/v2/schema/search_index \
  -H "x-api-key: $AITO_API_KEY" -H "Content-Type: application/json" -d '{
    "type": "view",
    "as": { "union": [
      { "from": "customers", "select": {
          "content":   { "$text": ["name", "notes"] },
          "source":    { "$const": "customer" },
          "source_id": "id" } },
      { "from": "deals", "select": {
          "content":   { "$text": ["title", "description"] },
          "source":    { "$const": "deal" },
          "source_id": "id" } }
    ] }
  }'

The view body goes under as (as in SQL's CREATE VIEW … AS): it is a relation expression — the same one you can drop into a query's from inline (see Relation from). A view just gives it a name and materialises it.

Each branch's select maps source columns into the view's columns:

• "id" — pass a source column straight through.
• { "$text": ["a", "b"] } — concatenate the named source columns into one Text column (space-joined), so it carries a real BM25 index. The named source columns must themselves be Text (tokenised at ingest); $text combines their token streams rather than re-tokenising raw strings. A non-Text source is rejected — declare the field Text in the source schema first.
• { "$const": "customer" } — a literal, the same on every row of that branch (here a source discriminator; add a source_id passthrough to trace back).
• { "$multiply": ["price", "qty"] } — an arithmetic column ($sum/$subtract/ $multiply/$divide/$pow, nesting allowed) — the same value-expression vocabulary as select/let.

Every branch must project the same columns with matching types (the columns are merged, so one type per column). Then query or full-text-search the view like any collection:

# full-text search across customers AND deals at once
curl ... /api/v2/_query -d '{
  "from": "search_index",
  "where": { "content": { "$match": "dairy" } },
  "select": ["source", "source_id"] }'
# → the customer and the deal that mention "dairy"

It is also from-able over SQL: SELECT source, source_id FROM search_index.

One-off query, no stored index? You can union the same collections inline, without defining a view — { "from": { "union": [ { "from": "customers", "select": { … } }, … ] }, "where": { … } } (see Relation from). A stored view is worth it when the search is hot: it builds the shared BM25 index once and keeps it, and queries use it for as long as no source has changed. It also gives the relation a name, so queries, SQL, and _predict can say from: "search_index" instead of repeating the union. An inline union composes the sources' token streams per query instead (no re-tokenisation — the sources are already Text).

A union view is never stale. Its materialised rows are kept as a cache keyed on the sources' versions: while none has changed the view answers from that copy, and as soon as one does it answers from the sources directly. Either way you see the current data, so you do not have to refresh it.

_refresh is therefore an optimisation, not a correctness step — it rebuilds the copy so later queries take the fast path again. It never changes what a query returns.

Two shapes are still materialised, and stay stale until refreshed: a view whose branch carries a baked where, and one with an arithmetic column. If your view has either, keep refreshing it after a source changes.

_refresh remains available for those, and is harmless on any view — it rebuilds from the stored definition and reports unchanged when no source has moved:

curl -X POST https://$AITO_INSTANCE/api/v2/schema/search_index/_refresh \
  -H "x-api-key: $AITO_API_KEY"

_refresh is incremental: it records each source's version, so if nothing has changed it returns {"status":"unchanged"} and does no work — cheap to call on a schedule. It rebuilds ({"status":"refreshed"}) only when a source actually changed.

Link-join views — expose a column as a navigable link

A link-join view turns a base collection's foreign-key column into a navigable link to another collection — even if that column was declared as a plain Int/String, not a link. No content is copied; the view just reinterprets the column as a link.

curl -X PUT https://$AITO_INSTANCE/api/v2/schema/enriched_orders \
  -H "x-api-key: $AITO_API_KEY" -H "Content-Type: application/json" -d '{
    "type": "view",
    "as": { "from": "orders",
            "join": { "table": "products", "on": { "$=": ["product", "id"] } } }
  }'

Now orders.product navigates to the product through the view — in the JSON query, {"from":"enriched_orders","select":["id","product.name","product.category"]}, and in SQL, SELECT id, product.name FROM enriched_orders — and predictions can span both tables. on is {"$=": ["baseColumn", "targetColumn"]} (a column-to-column equality); as optionally names the link column (default: the base column becomes the link). Like other views it is a snapshot: _refresh (or re-PUT) after the base changes.

You can also expose a link inline for a single query — { "from": { "from": "orders", "join": { "table": "products", "on": { "$=": ["product", "id"] } } } } — without a stored view (see Relation from). The inline join is lazy and copies nothing, so there's no snapshot to refresh.

Checklist

• Pick collection for the v2 engine; table only where you need v1 parity.
• Use Text + an analyzer for free text; String for categories.
• Set the quantity flag deliberately on Int/Long that are really measurements.
• Add link to model cross-table relationships you want to predict across.
• Mark genuinely-optional columns nullable and query them with $exists.
• Declare a primaryKey when a table is an entity store (so re-uploads replace, not duplicate); add an identity: true column when you want server-assigned row ids.

Experimental: x-knowledge

An experiment, not part of the schema to reach for by default: a text column that learns its own structure.

An x-knowledge column stores text like a Text column and answers the same text questions — $match, search and predict — but it also learns a compact grammar over its own values when they are written, including the phrases they are commonly written in (top up, direct debit). That lets it answer a question an ordinary text column cannot: how unusual is this value, compared with everything else in the column?

{ "type": "collection", "columns": {
    "id":   { "type": "String" },
    "body": { "type": "x-knowledge" },
    "note": { "type": "x-knowledge", "nullable": true } } }

$surprisal selects the rows whose value costs at least the given number of bits per token under the column's learned grammar — the values least like the rest:

{ "from": "tickets", "where": { "body": { "$surprisal": 5.0 } } }

What to know before using it:

• It is for spotting outliers, not for search quality. For ranking by relevance, a Text column is the right choice; x-knowledge answers the same questions but tokenises with its own analyzer, so its results can differ.

• The analyzer is fixed. An x-knowledge column always uses the standard analyzer — there is no analyzer option yet.

• $surprisal needs a single learned grammar. A column written in several batches holds one grammar per batch, and their scores are not comparable, so $surprisal is refused until the collection is optimized. Other queries answer throughout.

• A value is scored against the rest of the column. The vocabulary and the phrases are learned from every value, and a value's cost leaves its own occurrences out: a word that appears only in that value counts as never seen, so a value made of words that appear nowhere else ranks as highly surprising.

  Available since: v2.11.4. v2.11.3 and earlier learn the vocabulary from a sample of the values and count a value's own words when scoring it.

• It spots unusual words, not unusual orderings of ordinary words. Common phrases make ordinary values cheap, which lifts a value built from everyday words in a strange order above the most ordinary ones, but not into the top results: a value that repeats a common word (card card card card) is still a run of individually common choices. Use $surprisal to find values whose content is out of place.

• Nullable columns, updates and deletes are supported, including through optimize.

Phrases as evidence in predict

When a predict conditions on an x-knowledge column ("where": { "body": "…" }), the value is read through the phrases the column learned, not only as separate words. A learned phrase in the value (can i change my pin, bank card) becomes one piece of evidence with its own rows — the rows whose text contains that phrase — and the words it covers are not counted a second time. A phrase seen in few rows falls back to what its words say, so a rare phrase is never weaker evidence than its words alone.

This is where the column's learning pays off in accuracy. Measured on banking77 (10 003 training messages, 1 000 held out, 77 intents), the same data as Text and as x-knowledge:

On text without recurring phrases — near-unique product codes, or descriptions whose words come in no fixed order — no phrase is learned and the column predicts exactly as Text does.

A phrase shows up in $why as { "body": { "$phrase": "bank card" } } (its analysed words, separated by spaces).

A word of the value that stands outside every phrase, but that the column has also seen inside a learned phrase, is read as the word on its own: the rows where it stands alone, not every row that contains it. In $why it shows up as { "body": { "$symbol": "card" } }. This keeps one vendor's name — corning inc ny, learned as a phrase — from lending its history to a new value that merely shares the word inc.

Every $why factor is itself a proposition: $phrase and $symbol can be sent back as a where on the same column, and select exactly the rows the factor was scored on. $symbol takes one word; a phrase is written $phrase. A Text column learns no phrases, so it refuses both.

The phrases are a property of the column's rows, not of how they were written: a collection written in several batches reads its values through the same phrases as one written at once.

Related

• v2 Introduction · Inference (v2) · Query Operator Reference (v2)
• v1 Schema Design — the fuller treatment of patterns and analyzers.