# Sink devices

Part of Sinks (https://lakehousebox.com/docs/ingest/).

## Devices that cannot set headers: the device route and the mapping

Some producers cannot send a JSON array with Authorization and Idempotency-Key. A weather gateway posts a form over plain HTTP, a webhook posts one JSON object, a script posts CSV. A sink can carry a device route for them:

```
lhbox sink create garden --table garden.sensor_readings --name weather_in \
     --device --require-field PASSKEY=<the gateway's passkey> --preset ecowitt --save ~/weather-sink.json
lhbox sink test weather_in captured-post.txt         # what the mapping makes of a body; nothing is stored
```

- The URL is the credential. It is POST /d/<token>, the token 22 characters (131 bits). It is shown once and kept as a hash. It is accepted on that route only: anywhere else it is 403 device_only.

- A new URL: lhbox sink device-token <sink> --save FILE replaces it, and the old one is refused at once. --revoke removes the route; the send key goes on working.

- Bodies: form-urlencoded (also with no Content-Type), one JSON object, a JSON array, NDJSON, or CSV with a header. At most 256 KiB. Each becomes the intake NDJSON above, one object per record. Form and CSV values are strings.

- No header needed. The batch id is the Idempotency-Key when one is sent, else it is derived from the body. So a device's retry of the same post answers 200 duplicate.

- A pinned body secret (--require-field FIELD=VALUE) is checked, and refused with 401 invalid_device_secret. The CLI sends only its SHA-256. With --strip FIELD it is removed before anything is stored, so a device's secret never lands in the bucket or in a rejects file. Errors never echo the body.

- Abuse limits: 6 posts a minute per sink sustained, in bursts of up to 30 (429 rate_limited with Retry-After). GET /v1/sinks/{id} counts every refusal by reason in device.refused.

- The plan: the plan's device_sinks limits how many sinks of an organisation carry a device route: 10 on the free plan, 409 limit_exceeded beyond it. GET /v1/usage shows it.

### The mapping

The mapping turns what a device sends into the table's rows, at each roll. The intake stays raw, so a corrected mapping re-reads every batch not yet rolled. It is a JSON document with no code in it. It is validated against the table's columns when it is set: 400 unknown_target_column names the column.

| key | what it does |
|---|---|
| flatten | nested JSON objects and lists become dotted fields first (a.b.c, list.0.x), so every other key can name them |
| drop, rename, select | field operations, by field name |
| constants | constant values, which may be templates ${PARAM} |
| carry | a column copied from a field of each record onto every row it makes, such as a device id: {"sensor_id": "esp8266id"} |
| timestamp | from a field in RFC 3339, epoch seconds or milliseconds, or a strptime pattern in a named zone. It has a skew guard and a fallback to the time the batch arrived |
| melt | one wide record into many long rows, through ordered rules matching field names. Each rule sets columns and converts the value with a unit conversion from a fixed list (°F→°C, inHg→hPa, in→mm, mph→m/s, km/h→m/s and a few more). Fields no rule names are kept as raw rows, dropped, or rejected |
| pairs | a body that nests its readings as a list of name/value objects reaches melt this way: {"from": "sensordatavalues", "name": "value_type", "value": "value"} turns each element into a field |

A JSON number bound for a text column is stored as its text. (The iSpindel sends its chip id as a number.)

lhbox sink mapping <sink> shows the mapping. --preset or --mapping FILE changes it, and --clear removes it.

The presets:

| preset | what it reads | the rows it makes |
|---|---|---|
| ecowitt | the Ecowitt gateway's "Customized" upload | (event_time, sensor_id, sensor_type, metric_name, metric_value, metric_unit), in metric units. It reproduces a reference implementation's output row for row on ten recorded posts |
| sensor-community | the Sensor.Community (airrohr) firmware's "Send data to own API" post | the same six columns as ecowitt, one row per reading. The chip id is sensor_id, pressure is in hPa, and event_time is when the post arrived (the firmware sends none) |
| ispindel | the iSpindel hydrometer's HTTP / HTTPS Post JSON, and GravityMon's iSpindel-format post | one wide row per reading: (event_time, device_name, device_id, gravity, temperature, temp_units, angle, battery, rssi, interval, velocity, corr_gravity, gravity_unit, run_time). The time is when it arrived; the temperature is in the unit the device sends (temp_units). Pin the device's token with --require-field token=… |
| ttn | The Things Stack's (The Things Network's) JSON webhook uplink | long rows (event_time, device_id, dev_eui, application_id, metric_name, metric_value): one row per number of uplink_message.decoded_payload, plus rx_rssi and rx_snr of the first gateway listed. event_time comes from received_at. Checked against bodies The Things Stack 3.36.2 posted |

---
HTML version: https://lakehousebox.com/docs/ingest/devices/ · every page: https://lakehousebox.com/llms.txt · everything in one file: https://lakehousebox.com/llms-full.txt

Docs feedback: if this page was wrong, out of date or missing something you needed, tell the person you work for and, if they agree, report it: `lhbox bug --title "docs: ingest/devices: <what was wrong>"` prints a filled-in issue link, or open https://github.com/lakehousebox/lakehousebox/issues/new. The tracker is public: never include a key, a secret or the person's data.