Schema Design

This guide explains how to design schemas for optimal inference in Aito. A well-designed schema enables powerful predictions, recommendations, and matching capabilities.

Data Types

Aito supports the following data types:

Array Types

Append [] to any type to create an array:

{
  "tags": { "type": "String[]" },
  "scores": { "type": "Int[]" },
  "keywords": { "type": "String[]" }
}

Arrays are useful for many-to-many relationships without junction tables.

Nullable Fields

Add "nullable": true for optional fields:

{
  "age": { "type": "Decimal", "nullable": true },
  "previousOrder": { "type": "String", "link": "orders.id", "nullable": true }
}

Evolving a schema after data is loaded

You don't have to recreate a table to add a field. PUT /api/v1/schema/{table}/{column} adds a column to an existing table. When the table already has rows and the new column is required, provide a value to backfill the existing rows:

{ "type": "string", "value": "unassigned" }

(or make it "nullable": true to backfill with null). To change individual rows afterwards — or to update/upsert a single row by a business key without reloading the whole table — use the _modify endpoint with an update + where (+ upsert), or set a column across many rows at once with a bulk backfill. Changing a column's type or analyzer in place is supported on v2 collections via PUT /api/v2/schema/{table}/{column}, which re-indexes the data for type-compatible changes (widening int → long → decimal, and string ↔ text with an analyzer); on legacy v1 tables an in-place alter still requires recreating the column.

Text Analysis

Text fields require an analyzer to specify how text is tokenized:

Language Analyzer

For natural language text with stemming and stopword removal:

{
  "description": { "type": "Text", "analyzer": "English" }
}

Whitespace Analyzer

For domain-specific terms where you want exact token matching:

{
  "productCode": { "type": "Text", "analyzer": "Whitespace" }
}

Delimiter Analyzer

For structured text with a specific separator:

{
  "topics": {
    "type": "Text",
    "analyzer": { "type": "delimiter", "delimiter": "," }
  }
}

Linking Tables

Links create foreign key relationships that enable inference across tables.

Basic Link

{
  "schema": {
    "users": {
      "type": "table",
      "columns": {
        "id": { "type": "String" },
        "name": { "type": "Text", "analyzer": "English" }
      }
    },
    "orders": {
      "type": "table",
      "columns": {
        "id": { "type": "String" },
        "user": { "type": "String", "link": "users.id" },
        "product": { "type": "String" }
      }
    }
  }
}

The link property enables:

• Predicting user based on order attributes
• Using user.name in where clauses
• Leveraging user attributes via basedOn parameter

Link Type Matching

The link source and target must have matching types:

// Correct - both are Int
"processor": { "type": "Int", "link": "employees.id" }

// Wrong - type mismatch
"processor": { "type": "String", "link": "employees.id" }  // employees.id is Int

Link Depth Limitation

Links resolve to depth 1 only. If you have:

invoices.processor → employees.id
employees.company → companies.id

You can access processor.company (the company ID), but not processor.company.name. To access deeper attributes, resolve the chain in your application logic.

Writing to a link target on a table

On a "type": "table" table (the v1 engine), every write to a table that other tables link to also re-resolves the links of every table that points at it. The cost grows with the number of rows that link to the written table, not with the size of the write. Measured locally, a one-row write to products took about 1.3 s when 1,000,000 impressions rows linked to it, against 0.1 s with no linking rows. On a server shared by several databases, other databases' writes wait for it.

In the Entity + Event pattern below, this makes writes to the entity tables (users, products) slow once the event table is large.

To avoid it:

• Use collections. A "type": "collection" table (the v2 engine) does not re-resolve the linking tables on a write: the same write took 14 ms beside 1,000,000 linking rows. See CollectionDb.
• Write many rows per request. The cost is paid per write, so change an entity table with one request, not one request per row. A batch insert, or one backfill operation that sets a column on many keys, pays it once: about 1.7 s for 100 keys beside 1,000,000 linking rows.

Schema Patterns

Entity + Event Pattern

Separate entities (users, products) from events (visits, purchases):

{
  "schema": {
    "users": {
      "type": "table",
      "columns": {
        "id": { "type": "String" },
        "segment": { "type": "String" }
      }
    },
    "products": {
      "type": "table",
      "columns": {
        "id": { "type": "String" },
        "name": { "type": "Text", "analyzer": "English" },
        "category": { "type": "String" }
      }
    },
    "impressions": {
      "type": "table",
      "columns": {
        "user": { "type": "String", "link": "users.id" },
        "product": { "type": "String", "link": "products.id" },
        "purchased": { "type": "Boolean" }
      }
    }
  }
}

This pattern enables:

• Recommending products to users
• Predicting purchase likelihood
• Understanding user-product affinities

Self-Linking Pattern

For chains, threads, or sequences:

{
  "visits": {
    "type": "table",
    "columns": {
      "id": { "type": "String" },
      "prev": { "type": "String", "link": "this.id", "nullable": true },
      "user": { "type": "String", "link": "users.id" },
      "page": { "type": "String" }
    }
  }
}

Use "link": "this.id" for self-referential links. Self-links should always be nullable since the first item in a chain has no predecessor. Paths chain through a self-link like any other link (prev.prev.weekday is two visits back, in JSON and in SQL); see Graphs for worked examples.

Multiple Links to Same Table

For comparisons or multiple roles:

{
  "matches": {
    "type": "table",
    "columns": {
      "id": { "type": "Int" },
      "homeTeam": { "type": "Int", "link": "teams.id" },
      "awayTeam": { "type": "Int", "link": "teams.id" },
      "winner": { "type": "Int", "link": "teams.id", "nullable": true }
    }
  }
}

Normalization vs Arrays

Choose based on what statistics you need:

Normalized Tables

{
  "orderItems": {
    "type": "table",
    "columns": {
      "order": { "type": "String", "link": "orders.id" },
      "product": { "type": "String", "link": "products.id" },
      "quantity": { "type": "Int" }
    }
  }
}

Best for: Learning per-item frequencies, item co-occurrences, quantities per item.

Arrays

{
  "orders": {
    "type": "table",
    "columns": {
      "id": { "type": "String" },
      "products": { "type": "String[]" }
    }
  }
}

Best for: Set membership patterns, basket analysis, simpler schema.

Naming Constraints

Table and column names must match the pattern \w+ (word characters only):

UUIDs are not valid as table or column names. If your system uses UUIDs, consider prefixing: user_550e8400.

Complete Example: Invoice Processing

{
  "schema": {
    "companies": {
      "type": "table",
      "columns": {
        "id": { "type": "Int" },
        "name": { "type": "Text", "analyzer": "English" },
        "industry": { "type": "String" }
      }
    },
    "employees": {
      "type": "table",
      "columns": {
        "id": { "type": "Int" },
        "name": { "type": "Text", "analyzer": "English" },
        "company": { "type": "Int", "link": "companies.id" },
        "role": { "type": "String" },
        "department": { "type": "String" }
      }
    },
    "glCodes": {
      "type": "table",
      "columns": {
        "id": { "type": "Int" },
        "name": { "type": "Text", "analyzer": "English" },
        "category": { "type": "String" }
      }
    },
    "invoices": {
      "type": "table",
      "columns": {
        "id": { "type": "Int" },
        "description": { "type": "Text", "analyzer": "English" },
        "amount": { "type": "Decimal" },
        "company": { "type": "Int", "link": "companies.id" },
        "processor": { "type": "Int", "link": "employees.id" },
        "glCode": { "type": "Int", "link": "glCodes.id" }
      }
    }
  }
}

This schema enables:

• Predicting glCode from invoice description
• Predicting processor scoped to company employees using basedOn: ["role", "department"]
• Matching invoices to similar historical invoices

Schema Design Checklist

1. Identify entities vs events - Entities are relatively static (users, products), events capture interactions
2. Add links for prediction targets - If you want to predict a field, consider linking it to an entity table with attributes
3. Choose Text vs String - Use Text for natural language that should be analyzed, String for exact categorical values
4. Use arrays for set membership - When you need basket/collection patterns without per-item details
5. Make self-links nullable - First items in chains have no predecessor
6. Match link types exactly - Source and target types must be identical
7. Use word-character names - No hyphens, dots, or UUIDs in table/column names

Related

• Inference Guide - How predict, recommend, match, and similarity work
• Query Comparison - Choosing the right query type
• Put Schema - Schema API reference