CollectionDb (v2, Beta)

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. v2 is in beta.

Collection vs table

collection (rep2)table (rep1)
EngineCollectionDb (new, columnar)TableDb (production)
Schemaflexible — infer from JSON, evolve incrementallyfixed at creation
Storagecompact columnar, memory-mapped readsrow-oriented
Use whenyou want the v2 engine, JSON ingest, schema evolutionyou need v1 parity today

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

EndpointWhat it does
POST /api/v2/data/{table}/importSchema-flexible import. POST a JSON array; Aito infers column types, creates the table if absent, and appends if present. ?type=table makes a rep1 table instead.
POST /api/v2/data/{table}Insert one document.
POST /api/v2/data/{table}/batchInsert an array of documents. Add ?on_conflict=update|ignore to upsert/skip against a declared primaryKey.
POST /api/v2/data/{table}/streamInsert NDJSON (one object per line).
POST /api/v2/schema/_inferInfer a schema from example rows without creating anything (a dry run).
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.

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:

?on_conflict=BehaviourSQL analogue
error (default)A colliding key fails loud — the batch is rejected.INSERT (unique-constraint violation)
updateUpsert — the incoming row replaces the existing one (the old row is tombstoned, the new one appended). A brand-new key is a plain insert.ON CONFLICT (pk) DO UPDATE
ignoreThe incoming row is dropped; the existing row is kept.ON CONFLICT DO NOTHING
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).

Known limitation. Automatic new-column inference currently fires on bulk /import only — not on incremental single/batch inserts, which validate against the existing schema. To add a field outside import, use _apply or PUT .../{column}.

Maintaining a collection

EndpointWhat it does
POST /api/v2/data/{table}/optimizeConsolidate segments into an optimized state (faster queries).
POST /api/v2/data/_modifyAtomic by-key ops: update / upsert / delete a document, plus maintenance (optimize, copy, warm the linkage cache).
POST /api/v2/data/_deleteDelete rows matching a where-clause.

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