## 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>
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 withzig cc, see the rootMakefile) - 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 connectionsname— display name for loggingbroker— MQTT URL (mqtt://,mqtts://)username/password— auth credentialstopics— array of topic patterns to subscribeiataFilter— optional regional filterclientId: optional MQTT ClientID. Defaultcorescope-<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 tomqttSources)dbPath— SQLite DB path (default:data/meshcore.db)
Test
cd cmd/ingestor
go test -v ./...
What It Does
- Connects to configured MQTT brokers with auto-reconnect
- Subscribes to mesh packet topics (e.g.,
meshcore/+/+/packets) - Receives raw hex packets via JSON messages (
{ "raw": "...", "SNR": ..., "RSSI": ... }) - Decodes MeshCore packet headers, paths, and payloads (ported from
decoder.js) - Computes content hashes (path-independent, SHA-256-based)
- Writes to SQLite:
transmissions+observationstables - Upserts
nodesfrom decoded ADVERT packets (with validation) - Upserts
observersfrom MQTT topic metadata
Schema Compatibility
The Go ingestor creates the same v3 schema as the Node.js server:
transmissions— deduplicated by content hashobservations— per-observer sightings withobserver_idx(referencesobservers.idx)nodes— mesh nodes discovered from advertsobservers— 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