# Routes, modes and columns

How a connector picks records out of what arrives and writes them into a table. Route modes, keys, columns, the connector document and a full example.

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](https://withmestra.com/docs/topos/tables/#column-types) 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 called `currency` or `<column>_currency` in the same route. With a typed currency, Mestra adds a `<column>_currency` text 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.orders` and `shops.0.items` both 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`.

```json
{
  "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:

```json
{
  "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](https://withmestra.com/docs/topos/fetch-data/#headers-and-secrets). |
| 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.                                           |
