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.

A field that some rows leave out, or set to null, makes its column nullable, and a missing value is a real NULL: {"where": {"flag": null}} finds the rows without one. That includes a Boolean, and a hit leaves the key out where a row has no value (it does not return "flag": null). The same holds when a batch insert (POST /api/v2/data/{table}/batch, or v1's) adds a new Boolean field to a collection without a declared schema. A column that already exists keeps its type.

An import stores each value as sent, or refuses it. A JSON array or object for a column that already holds plain values (a String, a number, a Boolean) answers 400 import.failed, naming the column, and imports nothing. So does a value the column's type cannot hold, such as 1.5 or "N/A" for an Int column.

An import appends to the table as it is when the import commits, so a row that another client inserted while the import ran is kept. Two imports that both create the same new table (the first chunks of one load, sent in parallel) both land: the later one is appended exactly as if it had been sent after the other. So parallel chunks must be importable one after the other, with the same columns and values that fit them. A chunk that is not answers the 400 it would get if sent second, for example when it leaves out a column every row of the other chunk has; which chunk gets it depends on which commits first. The chunk that becomes an append answers "status": "appended" with the table's schema, as an append does, instead of "status": "created" with inferred. If another write drops the table or gives it a primary key at that moment, the import answers 409 write.contention; retrying it then takes the path the table now calls for.

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.

Missing values. An empty cell is a missing value. SQL and ERP exports write one as NULL (MySQL as \N), so an unquoted NULL, null or \N is a missing value too, in a column whose other cells are numbers or booleans, or that has no other values. In a column of text it stays the text NULL, because there it can be a real value. When the CSV is appended to a table that exists, the table's column type decides instead of the file: NULL is a missing value in a number or Boolean column and text in a String or Text column, which also takes every other cell as text. The table's type decides only how NULL reads: any other cell must already fit the column, so N/A or 1.5 for an Int column answers 400 import.failed, naming the column. A quoted "NULL" is always text. Either way the response says so in warnings, with the column and the number of cells.

A column with no value in any row (all empty or all NULL, an unused export field) is not created when the table does not have it yet, because nothing says what type it is. The warning import.csv_column_omitted names it. The first import with a value for it adds it, typed by that value, and the earlier rows read it as null. Until then the column does not exist: a where or a prediction that names it answers as for any unknown field, and an export has no header for it. A load sent in chunks can therefore be refused part way: if the first chunk types a column Int (or creates it from its first value), a later chunk with 12.5 in it answers 400 import.failed. Declare the schema first (PUT /api/v2/schema/{table}) when the chunks may disagree. A new table needs at least one column, so a file in which no column has any value creates nothing: it answers 400 import.failed. So do JSON rows with no fields.

A CSV is read against the table's column types as they are when the import starts. Two CSV chunks that race to create the same table are each read as if the table did not exist, so the one that becomes an append is not read exactly as it would be if sent second: a NULL in a column the other chunk typed as text is a missing value instead of the text NULL, and a column the other chunk created can still be left out as having no value. Send a CSV load's first chunk on its own when that matters.

"warnings": [
  { "code": "import.csv_null_as_null", "column": "qty", "count": 2, "message": "…" },
  { "code": "import.csv_null_as_text", "column": "note", "count": 2, "message": "…" },
  { "code": "import.csv_column_omitted", "column": "discount", "count": 2, "message": "…" }
]

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

Values in a _delete where. A JSON null is "no value": {"f": null} and {"f": {"$eq": null}} delete the rows where f is NULL, and {"f": {"$ne": null}} the rows where it has a value. The text "null" is a value like any other. A value is compared only with a column of its own kind:

• numbers exactly;
• true / false;
• strings (for String and Text columns and link keys);
• dates and timestamps written as ISO-8601 text. A timestamp may carry Z or an offset, a datetime without one is UTC, and a bare date is midnight UTC.

Any other mix is a 400 naming the column and its type, and nothing is deleted: {"id": "123"} on an Int column, {"code": 5} on a String column, {"flag": "yes"} on a Boolean, or any value on a Json column. Send the value in the column's own type ({"id": 123}). An empty condition ({}) anywhere in a where is also a 400, because it would match every row.

$not over $and / $or through the v1 API. On a v1-engine table, the v1 endpoints /api/v1/_search and /api/v1/data/_delete select under $not { "$and": [...] } or $not { "$or": [...] } only the rows where every condition inside has a value. A row where one condition is false and another has no value (a NULL field, or a link that is NULL or names no row) is not selected, although SQL's three-valued NOT would select it. v1 _search and v1 _delete agree on this, so a v1 delete removes exactly the rows a v1 search with the same where returns, and never a row the condition does not exclude.

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.