Schema Design (v2, Beta)

This guide explains how to design schemas for the v2 (rep2 / "collection") engine. v2 is in beta; 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

Type	Description	Use case
`String`	Categorical text	IDs, categories, tags
`Boolean`	True/false	Flags, outcomes
`Int`	32-bit integer	Counts, small ids
`Long`	64-bit integer	Large ids, timestamps
`Decimal`	Floating point	Prices, scores, measurements
`Text`	Analyzed text (needs an analyzer)	Descriptions, names
`Date`	Calendar date (ISO-8601, `YYYY-MM-DD`)	Timestamps, events
`Json`	Flexible nested JSON	Dynamic / irregular data
`Vector`	Fixed-length dense float vector	Embeddings for similarity search

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.

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.

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.

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 (Beta)

⚠️ Early access. Views are new and minimal. Two kinds exist today — the union-merge view below and the link-join view — both are materialised snapshots (rebuild to refresh) over v2 collections. Expect the surface to grow.

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 stores it. An inline $text union composes the sources' token streams per query (no re-tokenisation, since the sources are already Text).

The view is a materialised snapshot. After you add rows to a source, refresh it — either POST /api/v2/schema/search_index/_refresh (no body; rebuilds from the stored definition) or re-PUT the same view schema:

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.

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