Files
meshcore-analyzer/cmd/server/packets_exclude_tx_test.go
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

152 lines
5.7 KiB
Go

package main
import (
"encoding/json"
"net/http"
"net/http/httptest"
"net/url"
"reflect"
"sort"
"testing"
"time"
)
// #667: reception comparisons must match observer and direction on the same
// observation, not on the transmission's globally selected display observation.
func TestPacketsExcludeTx(t *testing.T) {
for _, mode := range []string{"memory", "sql-v2", "sql-v3", "fallback"} {
t.Run(mode, func(t *testing.T) {
db := setupTestDB(t)
if mode == "sql-v2" {
db.Close()
db = setupTestDBV2(t)
}
t.Cleanup(func() { db.Close() })
now := time.Now().UTC().Truncate(time.Second)
seen := now.Add(-time.Hour)
const node = "aabbccdd11223344"
mustExec := func(query string, args ...any) {
t.Helper()
if _, err := db.conn.Exec(query, args...); err != nil {
t.Fatal(err)
}
}
mustExec("INSERT INTO nodes (public_key, name, role, last_seen) VALUES (?, 'Fixture node', 'repeater', ?)", node, seen.Format(time.RFC3339))
mustExec("INSERT INTO observers (id, name, iata) VALUES ('a', 'Observer A', 'AAA'), ('b', 'Observer B', 'BBB')")
for _, hash := range []string{"a-tx-b-rx", "a-rx-b-tx", "a-tx-and-rx", "legacy-empty", "legacy-null", "tx-only"} {
mustExec(`INSERT INTO transmissions (hash, raw_hex, first_seen, route_type, payload_type, decoded_json, from_pubkey)
VALUES (?, '1200', ?, 1, 4, '{"pubKey":"aabbccdd11223344","type":"ADVERT"}', ?)`, hash, seen.Format(time.RFC3339), node)
}
addObs := func(tx int, observer string, direction any, path string) {
t.Helper()
if db.isV3 {
mustExec(`INSERT INTO observations (transmission_id, observer_idx, direction, path_json, timestamp)
VALUES (?, (SELECT idx FROM observers WHERE id = ?), ?, ?, ?)`, tx, observer, direction, path, seen.Unix())
} else {
mustExec(`INSERT INTO observations (transmission_id, observer_id, direction, path_json, timestamp)
VALUES (?, ?, ?, ?, ?)`, tx, observer, direction, path, seen.Unix())
}
}
addObs(1, "a", "tx", `[]`)
addObs(1, "b", "rx", `["11","22"]`) // representative RX must not count as A's reception
addObs(2, "a", "rx", `[]`)
addObs(2, "b", "tx", `["11","22"]`) // representative TX must not hide A's reception
addObs(3, "a", "tx", `["11"]`)
addObs(3, "a", "rx", `[]`)
addObs(4, "a", "", `[]`)
addObs(5, "a", nil, `[]`)
addObs(6, "a", "tx", `[]`)
srv := NewServer(db, &Config{}, NewHub())
suffix := ""
if mode == "memory" || mode == "fallback" {
store := NewPacketStore(db, nil)
if err := store.Load(); err != nil {
t.Fatal(err)
}
if !store.WaitIndexesReady(5 * time.Second) {
t.Fatal("packet indexes did not become ready")
}
store.oldestLoaded = ""
if mode == "fallback" {
store.oldestLoaded = seen.Format(time.RFC3339)
suffix = "&since=" + url.QueryEscape(now.Add(-2*time.Hour).Format(time.RFC3339))
}
srv.store = store
}
all := []string{"a-tx-b-rx", "a-rx-b-tx", "a-tx-and-rx", "legacy-empty", "legacy-null", "tx-only"}
rxA := []string{"a-rx-b-tx", "a-tx-and-rx", "legacy-empty", "legacy-null"}
for _, tc := range []struct {
name, query string
want []string
total int
}{
{"default preserves TX", "observer=a", all, 6},
{"false preserves TX", "observer=a&excludeTx=false", all, 6},
{"observer reception", "observer=a&excludeTx=true", rxA, 4},
{"other observer", "observer=b&excludeTx=true", []string{"a-tx-b-rx"}, 1},
{"multiple observers", "observer=a,b&excludeTx=true", all[:5], 5},
{"unscoped", "excludeTx=true", all[:5], 5},
{"hash shortcut", "hash=tx-only&excludeTx=true", []string{}, 0},
{"node shortcut", "node=" + node + "&excludeTx=true", all[:5], 5},
{"pagination after exclusion", "observer=a&excludeTx=true&limit=2&offset=1", rxA[1:3], 4},
{"grouped default warms cache", "observer=a&groupByHash=true", all, 6},
{"grouped reception isolates cache", "observer=a&groupByHash=true&excludeTx=true", rxA, 4},
{"grouped default unchanged", "observer=a&groupByHash=true", all, 6},
} {
t.Run(tc.name, func(t *testing.T) {
w := httptest.NewRecorder()
srv.handlePackets(w, httptest.NewRequest(http.MethodGet, "/api/packets?order=asc&"+tc.query+suffix, nil))
if w.Code != http.StatusOK {
t.Fatalf("status %d: %s", w.Code, w.Body.String())
}
var got struct {
Packets []struct{ Hash string }
Total int
}
if err := json.Unmarshal(w.Body.Bytes(), &got); err != nil {
t.Fatal(err)
}
hashes := make([]string, 0, len(got.Packets))
for _, p := range got.Packets {
hashes = append(hashes, p.Hash)
}
want := append([]string{}, tc.want...)
sort.Strings(hashes)
sort.Strings(want)
if !reflect.DeepEqual(hashes, want) || got.Total != tc.total {
t.Errorf("packets = %v (total %d), want %v (total %d)", hashes, got.Total, tc.want, tc.total)
}
})
}
})
}
}
func TestPacketsExcludeTxGroupedCacheCannotAliasRadio(t *testing.T) {
srv, router := setupTestServer(t)
t.Cleanup(func() { srv.db.Close() })
for _, tc := range []struct {
query string
total int
}{
{"", 3},
{"&radio=%7CexcludeTx", 0},
{"&excludeTx=true", 3},
} {
w := httptest.NewRecorder()
router.ServeHTTP(w, httptest.NewRequest(http.MethodGet, "/api/packets?groupByHash=true&observer=obs1"+tc.query, nil))
if w.Code != http.StatusOK {
t.Fatalf("status %d: %s", w.Code, w.Body.String())
}
var got PacketResult
if err := json.Unmarshal(w.Body.Bytes(), &got); err != nil {
t.Fatal(err)
}
if got.Total != tc.total || len(got.Packets) != tc.total {
t.Errorf("query %q: got %d packets (total %d), want %d; filters must have separate cache entries", tc.query, len(got.Packets), got.Total, tc.total)
}
}
}