Most data reaches a new database as a file: a CSV exported from the old system, a spreadsheet someone has kept for years, a JSON dump from another service. Moving the bytes is the easy part. The real work is deciding that a ZIP code column is text and not a number, that "Yes" means true, that a date is a date, and finding out which rows will not load before half of them have.
InventDB does this work in the engine's own API rather than in a separate import tool. This article covers the bulk insert endpoint, the file parser and its type inference, the preview that comes before a commit, how relationships between imported types are found and enforced, and the limits of each path.
Bulk insert: one request, checked before writing
POST /api/{namespace}/{type}/bulk takes a JSON array of records, or the same array wrapped as { "documents": [...] }. Any other shape is refused with a 400 that says what was expected, including a hint when the body used the wrong key, such as records. A body with the wrong key is never treated as an empty batch that quietly inserts nothing.
Before the first record is written, the engine runs every check that could refuse the batch:
- the caller's write permission on the type, refused with a 403;
- for every record, each foreign key on a relationship marked as enforced, refused with a 409 that names the record's position in the array;
- the instance's storage capacity for the whole batch, refused with a 507;
- the caller's row rules for every record, refused with a 403.
A refusal at any of these checks writes nothing. When the checks pass, each record without an _id gets one, and every record is stamped with _createdAt, _updatedAt and _createdBy. Each record is appended to the write-ahead log and indexed, and the log is flushed once for the whole batch rather than once per record. The response is a 201 with insertedCount and the ids in the same order as the request.
Because the batch goes through the write-ahead log like a single insert, every imported record produces its own entry in the change feed. Webhook subscribers see a row that arrived in a bulk load exactly as they see a row inserted on its own.
Indexing is not optional. The endpoint still accepts a disableIndexing parameter so that older clients keep working, but it has no effect: every write updates the indexes before the request returns, so a large load pays for its indexing as it goes, and reads are consistent the moment it finishes.
One parser for every file format
Files go through a single parser, shared by every import path. It picks the format from an explicit hint or from the file's extension:
| Format | How it is read |
|---|---|
| CSV, TSV | Comma or tab separated. A header row by default; without one, columns are named col_0, col_1 and so on. Fields are trimmed, rows may differ in length, and leading rows can be skipped. |
| TXT | The delimiter is detected from the first five non-empty lines: tab, comma, semicolon or pipe, whichever appears most often, with ties going to the one whose count varies least from line to line. |
| JSON | The root must be an array of objects. Columns are collected in the order they first appear across all rows; a row that is not an object is skipped with a warning. |
| JSONL, NDJSON | One object per line. A line that does not parse is reported as a warning with its line number, and the rest load. |
| XLSX, XLS, XLSB, ODS | The first sheet unless one is named. Whole-number cells become integers, date cells become ISO 8601 datetimes, and cells holding an error value become nulls with a warning. |
By default the parser treats an empty cell, NULL, null, NA, N/A and - as null, and the list can be replaced per import.
Type inference, column by column
For each column, the parser looks at every non-null value and gives the column the first type that all of them fit, in this order: integer, float, boolean, datetime, date, string.
| Type | Values that fit |
|---|---|
| Integer | An optional sign and digits, with no leading zero. |
| Float | Digits with a decimal point or an exponent; a column mixing 1 and 2.5 is float. |
| Boolean | true, false, yes and no, in lower case or with a capital first letter, and TRUE and FALSE. |
| Datetime | 2024-01-15T10:30:00, optionally with fractional seconds and Z or an offset such as +05:30. |
| Date | 2024-01-15. |
| String | Everything else. |
Some of the rules exist to protect data that only looks numeric. A column of values like 02139 stays a string and produces a warning, because leading zeros in ZIP codes, account numbers and phone numbers are part of the value. A column of 0 and 1 stays an integer rather than becoming a boolean. Dates written as 01/15/2024, and datetimes with a space instead of the T, stay strings rather than being guessed at. The parser also warns about any column that is more than 20% null.
Every guess can be overruled. Pass column_types, such as {"zip": "integer", "billable": "string"}, to pin a column, or switch inference off and keep values as the format delivered them. When the records are built, a row with a value that does not fit its column's type is rejected with its row number, the column, the value, the expected type and the reason. The other rows still load.
Preview, then commit
A file import is two steps. The first parses the file, infers the schema and stores a proposal without writing a record. It returns the inferred type of every column, the first five rows exactly as they would be stored, statistics for each column (null count; minimum, maximum and median for numbers; the five most common values for text), the warnings and the total row count. The proposal belongs to the person who made it and stays valid for five minutes.
The second step commits the proposal, optionally with corrections to column types or the null list. The file is parsed again with those corrections, and each row is inserted through the engine, under the caller's permissions and row rules. The response lists how many rows were inserted, every rejected row with its reason, the final schema and the time taken. Every inserted row is also written to the audit log with the proposal's id, so the rows an import created can be found and reviewed later.
The MCP server exposes this flow to AI agents. bulk_upload_url returns a link to a browser drop zone, valid for 15 minutes and for one upload, so a file goes from the person's browser to the instance without passing through the agent. The server previews the file as it arrives, and the agent then calls bulk_insert_commit with any corrections the person asked for. Inline inserts through bulk_insert are capped at 20 records per call, because larger batches are cheaper and safer through the drop zone.
Finding the relationships between imported types
After an import, the types usually refer to each other: leases point at properties, invoices point at tenants. InventDB stores these as edges in a namespace's relationship graph: a source type and property, a target type and property, a cardinality, and a flag that says whether the foreign key is enforced.
POST /api/relationships/{namespace}/detect suggests edges for an administrator to review. It works in two passes. The first reads names: a property ending in _id, id, _key or _ref whose prefix names another type, in singular or plural form, becomes an edge to that type's _id with a confidence of 0.7; a property named exactly like another type scores 0.5; and parent_id becomes a reference to the same type at 0.6. The second pass samples values, five by default and up to 20, and checks that each one exists in the target. If more than 80% are found, the confidence rises by 0.3, up to 1.0, and the edge is marked as verified. If more than half are found it rises by 0.1, and if fewer than 10% are found it falls by 0.3.
Sampling catches the most common wrong guess. Suppose leases.property_id holds business keys such as P-5009, while properties have generated ids. The name pass proposes properties._id at 0.7; the sample finds none of the values there, and the confidence drops to 0.4. The reviewer changes the target to properties.property_id and saves. Detection never saves anything itself, and a save replaces the namespace's whole edge set, so a client reads the graph first and sends back the edited list. In InventDB SOAR, detection can also ask the AI model to propose edges from the columns and a few sample rows, and the same graph tells the AI agent which columns to join on.
An enforced edge is checked on single inserts and updates, inside transactions, and by the bulk endpoint before it writes. A value must exist in the target, matched by _id or by the target property; a null or missing value is allowed, because the relationship may be optional. Deleting a row that an enforced edge still points at is refused with a 409, and a multi-row delete checks the whole set before removing any of it.
Limits
- Requests, including uploaded files and bulk JSON bodies, are limited by the instance's request size limit, 100 MB by default. The parser reads a whole file into memory before parsing it, so very large exports are better split.
- A file import reads one sheet per run. Nested objects and arrays in a JSON file are stored as JSON text; send them to the bulk endpoint as JSON if you want them kept as nested fields.
- Only ISO dates and datetimes are recognised.
YESandNOin capitals are not read as booleans, so a column holding them stays a string. - A committed file is inserted row by row, so a row refused late in the file does not undo the rows before it; the response lists exactly which rows were rejected. The bulk endpoint, by contrast, checks the whole batch before it writes.
- Relationship detection is a suggestion built from names and a small sample. It finds foreign keys whose names follow common conventions, and a person decides what is saved and what is enforced.
Using it
POST /api/pms/leases/bulk
Authorization: Bearer <token>
Content-Type: application/json
[
{ "lease_id": "L-1001", "property_id": "P-5009", "rent": 1850, "start": "2026-10-01" },
{ "lease_id": "L-1002", "property_id": "P-5011", "rent": 2100, "start": "2026-11-01" }
]For a large export, split the rows into batches and send each batch to this endpoint, or use the preview and commit flow and let the parser type the columns for you. In InventDB SOAR, the Store's import reads spreadsheets and JSON files in the browser, shows a column mapping and a preview, and sends the rows to the bulk endpoint 500 at a time, with a progress count and a way to stop. The bulk endpoint, the parser and relationship detection by name and sample work the same on InventDB Serverless and InventDB SOAR. The live reference at /api/docs on your instance lists every parameter.