Bringing data in
Routes, modes and columns
A connector has one or more routes. Each route picks records out of what arrived, maps fields to columns, and writes one table. Receive and fetch connectors use routes. You shape a route in the add flow, or in the Code tab when you set a connector up by hand.
Modes
When you shape a route, you choose what happens each time data arrives or is fetched. The form calls this When data arrives again (receive) or When it fetches again (fetch).
| Mode as shown | In the document | Table kind | What happens |
|---|---|---|---|
| Add and update | merge |
Keyed | Adds new rows and updates the ones it has. Nothing is marked gone. |
| Replace everything | replace |
Keyed | The payload is the whole table. Rows that aren’t in it are marked gone. A payload with no records takes nothing and marks nothing gone. |
| Append events | append |
Log | Adds each event as a new row. An event whose key is already stored is left alone. Needs a Time column. |
An uploaded file offers the first two, as Add and update and Replace everything.
A table is either keyed or a log. Every route that writes the same table must agree on that, on its key columns and on its time column. A later route can add columns to a table. It can’t change a column’s type, the key or the time column of a table that exists.
The key
The Key says which columns identify a row. Several key columns are joined, so a key of location and item_id makes one row for each item in each location.
- Tick Key on one or more columns.
- A route needs at least one key column.
- If records share a key value in one payload, they become one row and the last one wins. For a log, only the first is kept.
- A record with nothing in a key column is refused.
Columns
Each column has a name, a type (Text, Number, Money, True/false or Time), where its value comes from, and whether it is Required. See Tables for what each type accepts.
- A value comes from a field of the record, or is a constant value that fills every row. Use one, not both.
- A money column needs a currency: a three-letter upper-case code typed on the column, such as
AUD, or a column calledcurrencyor<column>_currencyin the same route. With a typed currency, Mestra adds a<column>_currencytext column to the table. - A value that doesn’t fit its column’s type refuses the whole record, required or not. A required column with nothing in it refuses the record too.
- Fields in a record that no column reads aren’t kept. Their names come back in the result as
dropped. - You can remove a column you don’t want, and Put back the ones you removed. Mestra lists fields it can’t make a column of under “Left out”.
While you shape a route, Mestra warns before you save: for example “3 of 200 values in “price” are not whole amounts in cents (2400 is $24.00). Those records will be refused.” or “The key “id” has repeated values.”
Where the records are
A route’s Records path says where the list of records is in the payload.
- A dot path. A name takes a field of an object. A number takes an item of a list.
data.ordersandshops.0.itemsboth work. - Leave it empty and the whole payload is the records.
- It can lead to a list of objects, or to one object, which is one record.
- If the path isn’t in the payload at all, the route finds no records and the result says
no_records. An empty list at the path is not that. - A path may have at most 32 parts.
- A record that isn’t an object, or a list holding anything but objects, is an error: the route fails with
routing_failed.
Only some records. A route can keep only records whose field equals some text. The Code tab calls this when. A number or true/false is compared as it is written.
CSV. A CSV payload is read as records keyed by its header row. A header with a dot in it, such as price.cents, is found as it is.
The connector document
The add flow builds a document. In the Code tab you can read it and edit it, and the form follows. On a connector’s page, Versions shows the document of each version. Mestra checks a document when it is saved and names every problem with its path, such as routes[1].columns[0].type. A field the document doesn’t have is a problem too, so a typo is never ignored.
input:
| Field | Meaning |
|---|---|
kind |
push (receive), http (fetch) or upload. |
format |
json (the default) or csv. xlsx is only for an upload. |
url |
Fetch only. An https:// address with no port and no user name. |
every_seconds |
Fetch only. At least 60. |
headers |
Fetch only. The names of headers to send. Their values are saved sealed, never in the document. |
Each item of routes:
| Field | Meaning |
|---|---|
name |
Letters, digits, hyphens and underscores. Unique in the document. Results are listed by it. |
records |
Dot path to the records. Optional. |
when |
Optional. {"field": "status", "equals": "paid"}. |
dataset |
The table’s key. Starts with a lower-case letter, then lower-case letters, digits and underscores, up to 63 characters. |
mode |
replace, merge or append. |
key |
The names of the key columns. At least one. Each must be a mapped column. |
time |
Append only. The column that holds each event’s time. It must be a Time column. |
columns |
The columns to fill. At least one. |
Each item of columns:
| Field | Meaning |
|---|---|
column |
The column’s name. Unique in the route. |
type |
text, number, money, bool or time. |
from |
A dot path into the record. |
value |
A constant. Exactly one of from and value. |
required |
true refuses any record that lacks it. |
currency |
Money only. Three upper-case letters. |
A full example
Orders arrive as a list at data.orders, and only paid ones are kept. Events arrive at data.events.
{ "input": {"kind": "push", "format": "json"}, "routes": [ { "name": "orders", "records": "data.orders", "when": {"field": "status", "equals": "paid"}, "dataset": "orders", "mode": "merge", "key": ["order_id"], "columns": [ {"column": "order_id", "from": "id", "type": "text", "required": true}, {"column": "total", "from": "total", "type": "money", "currency": "NZD", "required": true}, {"column": "placed_at", "from": "placed.at", "type": "time"}, {"column": "channel", "value": "web", "type": "text"} ] }, { "name": "events", "records": "data.events", "dataset": "order_events", "mode": "append", "key": ["event_id"], "time": "at", "columns": [ {"column": "event_id", "from": "id", "type": "text", "required": true}, {"column": "at", "from": "at", "type": "time", "required": true}, {"column": "kind", "from": "kind", "type": "text"} ] } ]}A payload for it:
{ "data": { "orders": [ {"id": "A-1", "status": "paid", "total": 2400, "placed": {"at": "2026-11-14T09:30:00+11:00"}}, {"id": "A-2", "status": "open", "total": 1800, "placed": {"at": "2026-11-14T09:31:00+11:00"}} ], "events": [ {"id": "E-1", "at": "2026-11-14T09:30:05+11:00", "kind": "paid"} ] }}Order A-2 is left out because its status isn’t paid. The orders table gets one row. The order_events table gets one event.
What a save can refuse
| Message | Fix |
|---|---|
| must be push, http or upload | Choose a valid kind. |
| must start with https:// | Fetch addresses are https only. |
| can’t name a port; an address uses the standard https port | Remove the port. |
| can’t carry a user name or password; send them as a header | Move them to a header. |
| must be at least 60 | Set every_seconds to 60 or more. |
| Mestra never sends this header | Remove that header. See Fetch data on a schedule. |
| a route needs a name, or another route has this name | Name each route, uniquely. |
| may use only letters, digits, hyphens and underscores | Fix the route’s name. |
| must start with a lower-case letter and use only lower-case letters, digits and underscores | Fix the table’s key. |
| at least one key column is needed | Tick Key on a column. |
| “x” is not a mapped column | A key or time name must be one of the route’s columns. |
| an append route needs a time column | Choose a Time column that holds each event’s time. |
| only an append route has a time column | Remove time, or change the mode to append. |
a money column needs a three-letter currency or a mapped <column>_currency column |
Enter a currency such as AUD. |
| needs one of from and value, or has both from and value; use one | Give each column one source. |
| a path has at most 32 parts | Shorten the path. |
| not a field of a connector document | A name in the document is misspelled. |
<table> is a keyed (or log) table in another route, and this mode does not fit it |
Make every route that writes a table agree on its kind, key and time column. |