CollectionDb (v2)

CollectionDb is the v2 storage engine — the "type": "collection" table. It's a schema-flexible, columnar store: you can POST raw JSON and let Aito infer the schema, evolve that schema as your data grows, and edit it declaratively. This guide is the reference for what a collection exposes.

Collection vs table

table is still the default; create a collection with "type": "collection", or let /import create one for you.

The v2 engine is built for collections. A rep1 table can be reached through /api/v2 for basic filtering and predict, but its v2 support is deliberately minimal (v1 is being retired) — e.g. the text matchers $match / $search are collection-only. To use v2 fully on data in a v1 table, migrate it to a collection or branch it into an env to trial v2 — see Which Query Type? → v2 works on v2 collections.

Ingesting data

POST /api/v2/data/events/import
[
  { "user": "ann", "action": "view",  "ts": "2026-06-01" },
  { "user": "bob", "action": "buy",   "ts": "2026-06-02", "amount": 19.0 }
]

Type inference maps JSON scalars to Int/Long/Decimal/Boolean/String, dates to Date, and nested/irregular values to Json. When two rows disagree on a field's type, the column widens to Json (lossless) rather than failing. A string column that reads like free text — most values several words, over 15 characters on average, and many different values — is inferred as Text, so its words become evidence; short codes, labels and ids stay String. The response's inferred shows the type each column got.

Loading a CSV

Post the file as it is, with content-type: text/csv:

curl -X POST "$AITO_URL/api/v2/data/invoices/import" \
  -H "x-api-key: $AITO_KEY" -H 'content-type: text/csv' --data-binary @invoices.csv

The header row names the columns. A CSV cell is always text, so each column's type is read from all its cells: all integers → a number, all numbers → Decimal, all true/false → Boolean, otherwise a string — and a free-text column (an invoice description) → Text, as above. A column with a leading zero (00100) stays a string, so postal codes and ids keep their digits. A malformed CSV — a row with more or fewer cells than the header, an empty or duplicate header column — answers 400 with the code import.csv_invalid, naming the row or column.

Why Text matters. A String is one opaque value, so the individual words of a description never become evidence: a prediction that should lean on it comes back flat, with every candidate at about the same probability. On 200 invoices, predicting the category from the description scored 0.25 with the description as a String and 1.00 as inferred Text (5 categories).

If the table already exists with a free-text column declared String, /import keeps the declared type and adds an import.string_looks_like_text warning to the response naming the column — re-declare it as Text.

To choose the analyzer (e.g. english stemming), or to control every type, declare the schema first and insert with /batch:

import csv, os, requests

AITO_URL = 'http://127.0.0.1:9005'   # a hosted instance: https://your-instance.aito.ai/db/your-db
HEADERS = {'x-api-key': os.environ['AITO_KEY'], 'content-type': 'application/json'}

# 1. Declare the schema. The free-text column is Text, so its words become evidence.
schema = {
    'type': 'collection',
    'columns': {
        'vendor':      {'type': 'String'},
        'description': {'type': 'Text', 'analyzer': 'english'},
        'amount':      {'type': 'Decimal'},
        'gl_account':  {'type': 'String'},
    },
}
requests.put(f'{AITO_URL}/api/v2/schema/invoices', json=schema, headers=HEADERS).raise_for_status()

# 2. Read the CSV. Every CSV value is a string, so convert the numeric columns.
with open('invoices.csv', newline='') as f:
    rows = [dict(row, amount=float(row['amount'])) for row in csv.DictReader(f)]

# 3. Insert in batches.
for i in range(0, len(rows), 1000):
    requests.post(f'{AITO_URL}/api/v2/data/invoices/batch',
                  json=rows[i:i + 1000], headers=HEADERS).raise_for_status()

On eight coded invoices, the same prediction ("where": { "description": "annual software subscription" }) gave the software account 0.46 with description declared as Text, and a flat 0.25 across all four accounts when /import had inferred it as String. GET /api/v2/schema/{table} shows which type a column got; see Schema Design for the types.

De-duplicating a batch — ?on_conflict

By default a collection is an observation log: batch appends every row, so re-uploading a row you already sent stores a duplicate. When a table has a declared primaryKey, the ?on_conflict query parameter on POST /api/v2/data/{table}/batch chooses how an incoming row whose key already exists is handled:

POST /api/v2/data/customers/batch?on_conflict=update
[
  { "email": "ann@example.com", "name": "Ann (updated)" },
  { "email": "cid@example.com", "name": "Cid" }
]

Notes:

• update and ignore are meaningful only against a declared primaryKey. Sending either to a keyless collection is rejected (on_conflict=update/ignore requires a declared primaryKey) rather than silently appending — Aito never dedups on an ad-hoc column.
• A batch that carries the same key twice within one request always errors, in every mode — a single request naming a key twice is a client mistake, not a self-upsert.
• Statistics stay correct across an upsert: the replaced row is masked from reads immediately, so inference over live rows reflects the new value at once; optimize compacts the tombstones later.

Schema evolution

A collection's schema isn't frozen:

• New fields appear automatically on /import — importing rows with a field the table doesn't have extends the schema; existing rows treat it as null.
• Heterogeneous-schema merge — segments written with different schemas merge transparently (the read side takes the union), so cross-insert evolution just works.
• Declarative changes — describe the schema you want and apply the diff (see Schema Migration § plan & apply): POST .../{table}/_plan classifies each column (add / rebuild / drop / rejected / no-op); _apply converges in one commit (refuses lossy changes; data-loss drops need ?confirm=true; idempotent).
• Single-column edits — PUT /api/v2/schema/{table}/{column} adds a column (optional value backfill) or alters an existing one's type/analyzer in place (the engine re-indexes; durable across optimize; incompatible alters rejected).

Maintaining a collection

What _delete deletes. Exactly the rows a _query with the same where returns. The where is a field value (equality), a field condition with $eq, $ne, $lt, $lte, $gt, $gte, $in or $not, and $and / $or / $not around those:

{ "from": "qv", "where": { "generation": { "$not": "g1" } } }

deletes the rows whose generation is set to something other than g1 — like a query's $not (and SQL's NOT), a row with generation unset is not deleted. An unknown operator or column is a 400 naming it, never a silent {"total": 0}.

Writes land as segments and are queryable immediately; optimize merges them for steady-state query speed (it's the rep2 analogue of a compaction). See Schema Migration & Maintenance for worked examples of bulk backfill, plan/apply, and atomic _modify operations.

Nullable & sparse columns

Scalar nullable columns carry an honest defined-mask — a real null that does not collide with 0, "", or false — and round-trip through persistence and merge. Query them with $exists. (Non-scalar nullable inners — nullable arrays/Json — are still sentinel-based; that's a tracked follow-up.)

Related

• Schema Design (v2) — types, links, the quantity flag, nullable modelling.
• Schema Migration & Maintenance (v2) — evolve a schema in place: plan/apply, add/alter, backfill, optimize.
• Inference (v2) — predict / recommend / relate / $why.
• Query Operator Reference (v2).
• v2 vs v1 — by the numbers — memory, disk, speed, accuracy.