Files
ZachandClaude Opus 5.5 900bffb782 refactor(schema): give observers an explicit idx primary key (#2407)
## Summary

`observations.observer_idx` points at the **implicit rowid** of
`observers`, whose declared key is `id TEXT`. Implicit rowids are not
stable:

- a dump-and-reload (`sqlite3 .dump`, `iterdump`) renumbers them, which
silently re-links observations to the wrong observer;
- PostgreSQL has no rowid at all.

This change makes the key explicit. Split out of #2396 at review's
request, so SQLite installs get it independently of the Postgres work.

## What changes

- **One-time rebuild, in the ingestor.** On first start, `observers` is
rebuilt as `idx INTEGER PRIMARY KEY AUTOINCREMENT`, copying every rowid
into `idx` in one transaction. It fails closed on unknown columns or
NULL ids.
- **Rollback-safe.** In SQLite an `INTEGER PRIMARY KEY` *is* the rowid,
so `rowid == idx` forever. Older binaries that still join on
`observers.rowid` keep working, so both upgrade and rollback are safe.
- **No inherited orphans.** `AUTOINCREMENT` is seeded past
`MAX(observations.observer_idx)`, so a new observer can never inherit a
deleted observer's leftover rows.
- Fresh installs create the new shape directly, and `AssertReady`
requires `observers.idx` as the primary key (a plain column named `idx`
does not pass).
- **Observer writes update first.** With `AUTOINCREMENT`, SQLite
advances `sqlite_sequence` for every INSERT it attempts, even one `ON
CONFLICT` turns into an update. The per-packet observer upsert and the
clock-skew upsert now run an `UPDATE` and fall back to the unchanged
`INSERT ... ON CONFLICT` only for a new observer. So the sequence moves
only when an observer is added, and the next new observer gets `max(idx)
+ 1`.
- `packets_v` now has a single definition
(`dbschema.EnsurePacketsView`).
- Every server and ingestor query, the fixture capture script and the
test schemas use `idx`. Guard tests fail if `observers.rowid` comes
back.

## Operator impact

Every SQLite install gets this on its next ingestor start, with no
config change. The rebuild is one transaction over `observers`, which
holds one row per observer (typically hundreds), so it's quick.

**When the rebuild refuses.** It refuses rather than lose data in two
cases, and then the ingestor stops on every start until the cause is
fixed:

- `observers` has a column CoreScope doesn't know (one added locally).
The rebuild would drop it. Save its data, then drop or rename it: `ALTER
TABLE observers DROP COLUMN <name>`.
- A row has a NULL `id` (the legacy `TEXT PRIMARY KEY` allows one). Find
it with `SELECT rowid, * FROM observers WHERE id IS NULL`, then delete
or fix it.

The error message names the column or the row count and these steps.
Starting the previous version also works: nothing was changed, and
`packets_v` stays readable for external SQLite readers in the meantime.

**A refusal stops the server too, not only ingest.** The server checks
the schema at startup (`AssertReady`) and exits when `observers.idx` is
not the primary key, so the whole site is down until the cause above is
fixed or the previous version is started again. Check for a locally
added column or a NULL `id` before upgrading if you are unsure.

**Ingest cost.** Measured with `BenchmarkUpsertObserverAtExisting`
(`cmd/ingestor`): 20000 `UpsertObserverAt` calls over 500 existing
observers, on a store opened by `OpenStore` with its own pragmas (WAL,
`synchronous=FULL`), Ryzen 3700X in Docker. A plain upsert on the new
table writes 5.23 WAL frames per call; update-first writes 4.23, one
frame less. The pre-PR table was not measured. Time per call is 676 to
726 µs before and 665 to 668 µs after; it is bound by fsync, so the
difference is within noise.

## Testing

- `internal/dbschema/observers_idx_test.go`: the rebuild keeps every
observation linked to the same observer, refuses an unknown column or a
NULL id, never reuses an `idx` (including one only orphaned observations
still reference), is idempotent, and handles the full legacy table.
- `TestObserversIdxPreservesLinksOnE2EFixture`: on a copy of
`test-fixtures/e2e-fixture.db`, 499/499 observations keep the same
observer.
- Refusals and rollback: a refused rebuild leaves the table, its rows,
`foreign_keys` and a readable `packets_v` as they were; a failure after
the old table is dropped rolls all of it back, leaves no
`observers_new`, and the next start completes the rebuild
(`TestObserversIdxRollsBackMidRebuild`,
`TestPacketsViewFollowsObserversShape`).
- Guards: `TestNoObserversRowidInSource` (ingestor) and
`TestNoObserversRowidInServerSources` fail if a query joins on
`observers.rowid` again. `TestFreshObserversMatchesRebuiltShape` pins
that a fresh install and a rebuilt one end up identical.
- `TestUpsertObserverAtLeavesSequenceAlone` and
`TestRecordNaiveSkewLeavesSequenceAlone`: 1000 calls on an existing
observer leave the sequence alone (on the previous head both moved it 1
-> 1001), and a new observer then gets `max(idx) + 1`.
`TestUpdateObserverMatchesUpsert` and `TestUpdateNaiveSkewMatchesUpsert`
check that the update path writes the same row as the upsert (COALESCE
of empty fields, `last_seen` never going back, the `can_relay`
tri-state, the 24 h skew counter).
- `TestAssertReadyRequiresObserversIdxPrimaryKey`: the rebuilt shape
passes; a plain `idx` column or none fails.
`TestOpenStoreRefusesUnconvertibleObservers`: through `OpenStore`, an
unknown column or a NULL id fails the open and leaves the table, its
rows and `packets_v` as they were.
- `go vet` and `go test` pass on all modules (`scripts/allmod.sh`), with
gofmt clean.

🤖 Generated with [Claude Code](https://claude.com/claude-code)



---

## Merge order

These four PRs were split out of #2396 and are **independent — they can
merge in any order.** Verified: every pair is conflict-free, and all
four merged together pass `go vet`, `go test ./...` (all 16 modules) and
`sh test-all.sh`.

Suggested order, none of it urgent:

1. **#2408** — never lose rows during startup / a failed poll.
2. **#2407** — explicit `observers.idx` primary key.
3. **#2409** — content-hash migration moved into the ingestor.
4. **#2410** — pin musl thread stacks at 8 MiB. Released images are not
affected (zig already links them with 8 MiB thread stacks); it fixes
native Alpine builds.

After these land, #2396 (Postgres) will be rebased onto master.

---------

Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-21 22:27:09 +01:00
..

MeshCore MQTT Ingestor (Go)

Standalone MQTT ingestion service for CoreScope. Connects to MQTT brokers, decodes raw MeshCore packets, and writes to the same SQLite database used by the Node.js web server.

This is the first step of a larger Go rewrite — separating MQTT ingestion from the web server.

Architecture

MQTT Broker(s)  →  Go Ingestor  →  SQLite DB  ←  Node.js Web Server
                    (this binary)     (shared)
  • Single static binary — no runtime dependencies, no CGO
  • SQLite via github.com/mattn/go-sqlite3 (cgo; cross-compiled with zig cc, see the root Makefile)
  • MQTT via github.com/eclipse/paho.mqtt.golang
  • Runs alongside the Node.js server — they share the DB file
  • Does NOT serve HTTP/WebSocket — that stays in Node.js

Build

Requires Go 1.22+.

cd cmd/ingestor
go build -o corescope-ingestor .

Cross-compile for Linux (e.g., for the production VM):

GOOS=linux GOARCH=amd64 go build -o corescope-ingestor .

Run

./corescope-ingestor -config /path/to/config.json

The config file uses the same format as the Node.js config.json. The ingestor reads the mqttSources array (or legacy mqtt object) and dbPath fields.

Environment Variables

Variable Description Default
DB_PATH SQLite database path data/meshcore.db
MQTT_BROKER Single MQTT broker URL (overrides config) —
MQTT_TOPIC MQTT topic (used with MQTT_BROKER) meshcore/#
CORESCOPE_INGESTOR_STATS Path to the per-second stats JSON file consumed by the server's /api/perf/io and /api/perf/write-sources endpoints (#1120) /tmp/corescope-ingestor-stats.json

Stats file (CORESCOPE_INGESTOR_STATS)

Every second the ingestor publishes a JSON snapshot of its counters (tx_inserted, obs_inserted, walCommits, backfillUpdates.*, etc.) plus a procIO block sampled from /proc/self/io (read/write/cancelled bytes per second + syscall counts). The server reads this file and surfaces the data on the Perf page so operators can self-diagnose write-volume anomalies.

The writer uses O_NOFOLLOW | O_CREAT | O_TRUNC mode 0o600, so a pre-planted symlink at the path cannot be used to clobber an arbitrary file.

Security note: the default lives in /tmp, which is world-writable on most hosts (sticky bit only protects deletion, not creation). On shared/multi-tenant hosts, override CORESCOPE_INGESTOR_STATS to point at a private directory (e.g. /var/lib/corescope/ingestor-stats.json) that only the corescope user can write to.

Minimal Config

{
  "dbPath": "data/meshcore.db",
  "mqttSources": [
    {
      "name": "local",
      "broker": "mqtt://localhost:1883",
      "topics": ["meshcore/#"]
    }
  ]
}

Full Config (same as Node.js)

The ingestor reads these fields from the existing config.json:

  • mqttSources[] — array of MQTT broker connections
    • name — display name for logging
    • broker — MQTT URL (mqtt://, mqtts://)
    • username / password — auth credentials
    • topics — array of topic patterns to subscribe
    • iataFilter — optional regional filter
    • clientId: optional MQTT ClientID. Default corescope-<name>-<random>, new suffix per ingestor start. Two running ingestors must not share a value on one broker
  • mqtt — legacy single-broker config (auto-converted to mqttSources)
  • dbPath — SQLite DB path (default: data/meshcore.db)

Test

cd cmd/ingestor
go test -v ./...

What It Does

  1. Connects to configured MQTT brokers with auto-reconnect
  2. Subscribes to mesh packet topics (e.g., meshcore/+/+/packets)
  3. Receives raw hex packets via JSON messages ({ "raw": "...", "SNR": ..., "RSSI": ... })
  4. Decodes MeshCore packet headers, paths, and payloads (ported from decoder.js)
  5. Computes content hashes (path-independent, SHA-256-based)
  6. Writes to SQLite: transmissions + observations tables
  7. Upserts nodes from decoded ADVERT packets (with validation)
  8. Upserts observers from MQTT topic metadata

Schema Compatibility

The Go ingestor creates the same v3 schema as the Node.js server:

  • transmissions — deduplicated by content hash
  • observations — per-observer sightings with observer_idx (references observers.idx)
  • nodes — mesh nodes discovered from adverts
  • observers — MQTT feed sources

Both processes can write to the same DB concurrently (SQLite WAL mode).

What's Not Ported (Yet)

  • Companion bridge format (Format 2 — meshcore/advertisement, channel messages, etc.)
  • Channel key decryption (GRP_TXT encrypted payload decryption)
  • WebSocket broadcast to browsers
  • In-memory packet store
  • Cache invalidation

These stay in the Node.js server for now.

Files

cmd/ingestor/
  main.go          — entry point, MQTT connect, message handler
  decoder.go       — MeshCore packet decoder (ported from decoder.js)
  decoder_test.go  — decoder tests (25 tests, golden fixtures)
  db.go            — SQLite writer (schema-compatible with db.js)
  db_test.go       — DB tests (schema validation, insert/upsert, E2E)
  config.go        — config struct + loader
  util.go          — shared utilities
  go.mod / go.sum  — Go module definition