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`)	Birthdays, due dates
`Timestamp`	A point in time, normalised to UTC on write (ms precision)	Event times, logs, orders
`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.

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.

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.
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.

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