Sink devices

Part of Sinks.

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