sid.stg.net

ESP32 telemetry ingest, flexible query API, and OTA firmware service. Hosted by bserver at the sid.stg.net virtual host.

Layout

sid.stg.net/
  report.yaml         # POST endpoint — device telemetry ingest
  api.yaml            # GET endpoint — JSON query API
  firmware/
    index.php         # GET=download, POST=upload (raw body)
    <platform>/<version>.bin
  dump.yaml           # human-readable HTML dump of the database
  index.yaml          # landing page
  data/
    reports.db        # SQLite, WAL mode, append-only
    db.inc            # shared PHP bootstrap (not web-servable)
  _config.yaml        # allow-http: true (devices cannot do TLS)

Database

Single SQLite file at data/reports.db, created by the first POST to /report and thereafter owned by the nobody user (bserver's worker). WAL mode lets external tooling read concurrently with writes. The schema is append-only (WORM) — no culling yet. Full schema lives in data/db.inc.

Four tables:

Table	Purpose
`reports`	Every incoming device report. Full blob in `body` JSON column; hot-path columns (`mac`, `event`, `platform`, `version`) promoted and indexed.
`devices`	One row per MAC — upserted on every POST. Denormalized `last_seen`, `last_platform`, `last_version`, `report_count`, `crash_count`.
`firmware_versions`	Release catalog. `(platform, version) -> replaces, filename, sha256`. Unknown `(platform, version)` pairs seen in reports are auto-registered with `replaces = NULL` so a human can later wire the upgrade edge.
`firmware_downloads`	Audit log — one row per download attempt (success or 404).

Read-side tooling can open data/reports.db directly in read-only mode.

`POST /report` — Device telemetry ingest

The device submits a blob on boot, heartbeat, crash, or any other event. Schema is open-ended — any additional fields land verbatim in the reports.body JSON column and become queryable via the API without any code change.

Request

Method: POST
Content-Type: application/x-www-form-urlencoded or application/json
Body fields: any keys/values. The server recognises a few by name for indexing; the rest are preserved.

Field	Required	Stored as	Semantics
`mac`	yes	indexed column + body	Unique device identifier. Report is rejected with `400` if missing or empty.
`event`	no	indexed column + body	Event type. `crash` increments `devices.crash_count`. Other common values: `boot`, `heartbeat`.
`platform`	no	indexed column + body	Hardware platform (e.g. `m5dial`). Used for firmware lookup.
`version`	no	indexed column + body	Firmware version string — opaque to the server (git hash, githash-mod, or numeric all work).
`sdk`	no	body only	Typically an esp-idf version tag.
`uptime_ms`	no	body only	Milliseconds since boot on the device.
`reset_reason`	no	body only	Numeric reset-reason code from esp-idf.
`reset_reason_str`	no	body only	Symbolic reset reason (`POWERON`, `INT_WDT`, …).
`free_heap`	no	body only	Current free heap (bytes).
`min_free_heap`	no	body only	Lifetime minimum free heap (bytes).
anything else	no	body only	Queryable via `json_extract` on the `/api` side.

Server-added fields

received_at (unix epoch) is assigned by the server — the device's clock is not trusted. Stored on the reports row, not inside body.

Side effects

A row is inserted into reports.
The devices row for this MAC is upserted: first_seen set if new, last_seen bumped, report_count incremented, crash_count incremented when event='crash', and last_platform/last_version/ last_event refreshed (keeping previous values when the report omits the field).
If both platform and version are present and the pair is not already in firmware_versions, a placeholder row is inserted with replaces = NULL, filename = '', and a note marking it auto-registered. This lets humans later wire the upgrade edge without the device having to re-report.

Response

For now: a 200 OK with a 16-byte body of <!DOCTYPE html>\n (a quirk of bserver's yaml rendering — see "Known quirks" below). The device should treat any 2xx as success.

Errors:

Status	Meaning
`400 Bad Request`	`mac` missing or empty.
`405 Method Not Allowed`	Request used a method other than POST.
`500 Internal Server Error`	Database open or write failed (see server log).

Planned: emit a JSON body describing an available firmware update when firmware_versions.replaces = <device's current version> resolves to a newer row. Not wired up yet.

Example

curl -X POST http://sid.stg.net/report \
     --data-urlencode 'event=boot' \
     --data-urlencode 'mac=b0:81:84:96:90:68' \
     --data-urlencode 'platform=m5dial' \
     --data-urlencode 'version=9f3c223-mod' \
     --data-urlencode 'sdk=v5.5.2-729-g87912cd291' \
     --data-urlencode 'uptime_ms=4772' \
     --data-urlencode 'free_heap=51656' \
     --data-urlencode 'min_free_heap=46080'

JSON is also accepted:

curl -X POST http://sid.stg.net/report \
     -H 'Content-Type: application/json' \
     -d '{"event":"boot","mac":"b0:81:84:96:90:68","platform":"m5dial","version":"9f3c223-mod"}'

`GET /api` — Flexible JSON query

Dispatches on the ?what= parameter. All responses are application/json, but bserver prepends a <!DOCTYPE html>\n line before the JSON (yaml-rendering quirk). Consumers should strip the first line, e.g. tail -n +2 or sed 1d, before piping to jq.

All list endpoints accept ?limit=N (default 100, max 1000).

`?what=devices`

One row per MAC, ordered by last_seen descending.

curl 'http://sid.stg.net/api?what=devices' | sed 1d | jq

{
  "ok": true,
  "rows": [
    {
      "mac": "b0:81:84:96:90:68",
      "first_seen": 1776560545,
      "last_seen": 1776560545,
      "last_platform": "m5dial",
      "last_version": "9f3c223-mod",
      "last_event": "boot",
      "report_count": 1,
      "crash_count": 0
    }
  ]
}

`?what=device&mac=XX`

That device's row plus its recent reports (body decoded from JSON).

curl 'http://sid.stg.net/api?what=device&mac=b0:81:84:96:90:68&limit=20' | sed 1d | jq

Response shape:

{
  "ok": true,
  "device": { ... },
  "reports": [ { "id": 1, "received_at": 1776560545, "event": "boot",
                 "platform": "m5dial", "version": "9f3c223-mod",
                 "body": { ... full decoded blob ... } } ]
}

Returns {"ok":false,"error":"mac required"} with status 400 if mac is missing.

`?what=reports` — filtered history

The full append-only report stream, filtered by any combination of parameters. This is the core surface for crash-pattern investigations.

Filter semantics:

Known columns (mac, event, platform, version) — equality match against the indexed column (fast).
Time range — since=<unix_epoch> (inclusive) and/or until=<unix_epoch> (exclusive) against received_at.
Anything else — equality match against json_extract(reports.body, '$.<key>') = ?. Any field the device ever reported is searchable this way, even if it wasn't known at the time the server was deployed.

Reserved parameters (not treated as filters): what, limit, order, since, until.

Examples:

# All crashes, newest first
curl 'http://sid.stg.net/api?what=reports&event=crash' | sed 1d | jq

# Crashes on m5dial running version 9f3c223-mod
curl 'http://sid.stg.net/api?what=reports&event=crash&platform=m5dial&version=9f3c223-mod' | sed 1d | jq

# Find reports where the (arbitrary) field `wifi_rssi` equals -72
curl 'http://sid.stg.net/api?what=reports&wifi_rssi=-72' | sed 1d | jq

# Crashes in the last 24h
SINCE=$(($(date +%s) - 86400))
curl "http://sid.stg.net/api?what=reports&event=crash&since=$SINCE" | sed 1d | jq

Response:

{
  "ok": true,
  "rows": [
    { "id": 17, "received_at": 1776560545, "mac": "...", "event": "crash",
      "platform": "m5dial", "version": "9f3c223-mod",
      "body": { ... full decoded blob ... } },
    ...
  ]
}

`?what=firmware_versions`

The firmware release catalog, including auto-registered rows from devices reporting unknown (platform, version) pairs.

curl 'http://sid.stg.net/api?what=firmware_versions' | sed 1d | jq

`?what=firmware_downloads[&mac=XX]`

Audit trail of firmware download attempts. Without mac, returns the last limit rows across all devices; with mac, scopes to that device.

curl 'http://sid.stg.net/api?what=firmware_downloads&mac=b0:81:84:96:90:68' | sed 1d | jq

Error responses

{"ok": false, "error": "..."}

Delivered with status 400 for bad input, 500 for DB failures.

`GET /firmware` — Download a binary

Streams firmware/<platform>/<version>.bin, with defence-in-depth path validation (regex allow-list plus realpath containment check). Every attempt is logged to firmware_downloads.

Parameters

Param	Required	Pattern
`mac`	yes	`^[0-9A-Fa-f:]{11,17}$` — used only for the download log.
`platform`	yes	`^[A-Za-z0-9._-]+$`
`version`	yes	`^[A-Za-z0-9._-]+$`

Responses

Status	Meaning
`200 OK` + `application/octet-stream`	Binary streamed, `firmware_downloads.ok = 1`.
`400 Bad Request`	Missing or malformed parameter.
`404 Not Found`	File missing; a row with `ok = 0` is still logged.
`405 Method Not Allowed`	Non-GET method (other than POST — see below).

Example

curl -o firmware.bin \
  'http://sid.stg.net/firmware?mac=b0:81:84:96:90:68&platform=m5dial&version=9f3c224-mod'

`POST /firmware` — Upload a binary

Intended for the CI/build pipeline. Writes the raw POST body to firmware/<platform>/<version>.bin and upserts the firmware_versions row. No auth yet — runs on a trusted/internal network only; lock-down is a planned follow-up.

Parameters (query string)

Param	Required	Notes
`platform`	yes	Matches `^[A-Za-z0-9._-]+$`.
`version`	yes	Matches `^[A-Za-z0-9._-]+$`. Becomes the filename stem.
`replaces`	optional	Which prior version this supersedes. Leaving unset/null means "new release without an upgrade edge yet". When set, devices currently on `<replaces>` will be offered `<version>` once the report response includes update info.
`notes`	optional	Free text release notes (stored in `firmware_versions.notes`).
`sha256`	optional	Expected sha256 of the body. If supplied, the server verifies against the body it received and rejects with 400 on mismatch. If omitted, the server computes and stores the actual sha256.

Body

Raw firmware bytes. Content-Type doesn't matter but application/octet-stream is conventional.

Side effects

Body written atomically (tempfile + rename) to firmware/<platform>/<version>.bin. The platform subdirectory is created with mode 0755 if needed.
firmware_versions row upserted by (platform, version). If a placeholder row was auto-registered from a device report, filename, sha256, and released_at are updated; the original replaces and notes are preserved unless you pass new values.

Response

On success, status 201 Created:

{
  "ok": true,
  "platform": "m5dial",
  "version": "9f3c224-mod",
  "bytes": 1048576,
  "sha256": "...",
  "filename": "9f3c224-mod.bin",
  "replaces": "9f3c223-mod",
  "released_at": 1776560832
}

On error, status 400 or 500 with {"ok":false,"error":"..."}.

Example

curl -X POST --data-binary @build/m5dial/firmware.bin \
     -H 'Content-Type: application/octet-stream' \
     "http://sid.stg.net/firmware?platform=m5dial&version=9f3c224-mod&replaces=9f3c223-mod&notes=heap+leak+fix"

With integrity check:

SHA=$(sha256sum build/m5dial/firmware.bin | awk '{print $1}')
curl -X POST --data-binary @build/m5dial/firmware.bin \
     -H 'Content-Type: application/octet-stream' \
     "http://sid.stg.net/firmware?platform=m5dial&version=9f3c224-mod&replaces=9f3c223-mod&sha256=$SHA"

`GET /dump`

HTML dump of the entire database in Bootstrap tables — devices, firmware versions, firmware downloads, and the last 500 reports with expandable JSON bodies. Intended for humans doing quick eyeballing; use the API for anything programmatic.

Known quirks

Yaml endpoints emit a leading <!DOCTYPE html>\n. bserver's yaml renderer hard-codes the DOCTYPE prefix before anything PHP echoes (/root/bserver/render.go:147). This means /report responses have 16 bytes that the device's HTTP client ignores, and /api JSON is preceded by one doctype line that consumers must strip (sed 1d, tail -n +2). Switching these endpoints to .php files would eliminate the quirk if/when needed.
Status: from yaml-embedded PHP is not honoured. header('Status: 204') in report.yaml does not change the HTTP status code — it arrives as a response header instead. Endpoint currently returns 200 on success, 200 on client error, and bserver's own 500 path on database failure. Acceptable for the ESP32 side today.
No authentication. Any client on the network can POST reports or firmware uploads. Planned: API token / mTLS / source-IP allow-list for the upload endpoint specifically.

Reset / reinstall

Delete the database to start clean. Next POST recreates the schema.

rm -f data/reports.db data/reports.db-wal data/reports.db-shm
rm -rf firmware/*/   # drops uploaded binaries

sid.stg.net

Layout

Database

POST /report — Device telemetry ingest

Request

Server-added fields

Side effects

Response

Example

GET /api — Flexible JSON query

?what=devices

?what=device&mac=XX

?what=reports — filtered history

?what=firmware_versions

?what=firmware_downloads[&mac=XX]

Error responses

GET /firmware — Download a binary

Parameters

Responses

Example

POST /firmware — Upload a binary

Parameters (query string)

Body

Side effects

Response

Example

GET /dump

Known quirks

Reset / reinstall

`POST /report` — Device telemetry ingest

`GET /api` — Flexible JSON query

`?what=devices`

`?what=device&mac=XX`

`?what=reports` — filtered history

`?what=firmware_versions`

`?what=firmware_downloads[&mac=XX]`

`GET /firmware` — Download a binary

`POST /firmware` — Upload a binary

`GET /dump`