Data Ingestion (file → table sync)

The UI has an Import Data page (Schema Builder → Import Data) for no-code uploads — pick a file, choose or name a table, set the options, and see a live insert/update/delete summary. For automation and large files, use the API: load a CSV / Parquet / XML dataset into a table and keep it in sync via POST /api/ddl/ingest (multipart upload) or …/ingest/from-content (inline JSON).

The endpoint creates the table if it’s missing — inferring column types from the data, with your keyColumns as the primary key — then upserts matching on the table’s primary key: new rows are inserted, existing rows are updated only when a value actually changed, and unchanged rows are left alone. Set mirror=true for a full-set sync that also removes rows absent from the file — soft (dl_deleted=1, default) or hard (deleteMode=hard → DELETE). For files over 100 MB, stage a signed upload (POST …/ingest/upload → PUT the bytes → ingest with uploadId+uploadToken).

mirror doesn’t apply to the very first load. A mirror removes rows the file no longer carries — but on a first load this run creates the table, so there is nothing to remove, and a brand-new table has no dl_deleted column for a soft delete. A first load asked to mirror is therefore run as a plain (non-mirror) load and says so in the run notes; mirroring takes effect from the next load onward. Nothing is lost either way: the two are identical on a table that starts empty.

Everything runs in the active schema in one transaction with RLS enforced; it’s audited as DATA_INGEST. Gated by the data.ingest permission (granted to the Admin role) and refused for scope-restricted API keys (an arbitrary file load can’t be entity/field-scoped). Over MCP this is the ingest_table tool (file contents passed inline as content or contentBase64).

Ingest enforces size caps — on row count, column count, header length, individual cell size, and (for XML/Parquet) raw byte size — so a malformed or runaway file fails fast with a clear message naming the limit it hit instead of stalling the load. When a file introduces new columns, the schema evolution is transactional: the table’s shape and the data land together, or neither does.

Ingest needs a natural-key primary key. Because it upserts row by row on the primary key, ingest refuses a table whose PK is an IDENTITY column — “Row-by-row upsert needs a key whose values come from the file”. Give the table a natural key instead (a SKU, code, or external id the file carries), or keep the IDENTITY PK and seed/maintain those rows through the Data API (create_record over MCP, POST over REST) rather than by file.