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.

See it in action: Explore the demo database schema and demo workbook to see how schema design enables powerful queries.

Data Types

Aito supports the following data types:

TypeDescriptionUse Case
StringFixed categorical textIDs, categories, tags
BooleanTrue/false valuesFlags, binary outcomes
Int32-bit integersCounts, IDs, years
Long64-bit integersLarge IDs, timestamps
DecimalFloating point numbersPrices, scores, measurements
TextAnalyzed text (requires analyzer)Descriptions, names, content
JsonFlexible JSON structureDynamic/nested data

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.

{
  "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

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

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.

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.

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):

ValidInvalid
usersuser-data
order_itemsorder.items
productV2product-v2
user_123550e8400-e29b-41d4

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