test: fix env isolation and comment accuracy in channel key tests

Agent-Logs-Url: https://github.com/Kpa-clawbot/meshcore-analyzer/sessions/38b3e96f-861b-4929-8134-b1b9de39a7fc

Co-authored-by: KpaBap <746025+KpaBap@users.noreply.github.com>

cmd/ingestor/config.go

@@ -26,13 +26,14 @@ type MQTTLegacy struct {
 
 // Config holds the ingestor configuration, compatible with the Node.js config.json format.
 type Config struct {
-	DBPath         string            `json:"dbPath"`
-	MQTT           *MQTTLegacy       `json:"mqtt,omitempty"`
-	MQTTSources    []MQTTSource      `json:"mqttSources,omitempty"`
-	LogLevel       string            `json:"logLevel,omitempty"`
-	ChannelKeysPath string           `json:"channelKeysPath,omitempty"`
-	ChannelKeys    map[string]string `json:"channelKeys,omitempty"`
-	Retention      *RetentionConfig  `json:"retention,omitempty"`
+	DBPath          string            `json:"dbPath"`
+	MQTT            *MQTTLegacy       `json:"mqtt,omitempty"`
+	MQTTSources     []MQTTSource      `json:"mqttSources,omitempty"`
+	LogLevel        string            `json:"logLevel,omitempty"`
+	ChannelKeysPath string            `json:"channelKeysPath,omitempty"`
+	ChannelKeys     map[string]string `json:"channelKeys,omitempty"`
+	HashChannels    []string          `json:"hashChannels,omitempty"`
+	Retention       *RetentionConfig  `json:"retention,omitempty"`
 }
 
 // RetentionConfig controls how long stale nodes are kept before being moved to inactive_nodes.

cmd/ingestor/main.go

@@ -512,34 +512,64 @@ func firstNonEmpty(vals ...string) string {
 	return ""
 }
 
+// deriveHashtagChannelKey derives an AES-128 key from a channel name.
+// Same algorithm as Node.js: SHA-256(channelName) → first 32 hex chars (16 bytes).
+func deriveHashtagChannelKey(channelName string) string {
+	h := sha256.Sum256([]byte(channelName))
+	return hex.EncodeToString(h[:16])
+}
+
 // loadChannelKeys loads channel decryption keys from config and/or a JSON file.
-// Priority: CHANNEL_KEYS_PATH env var > cfg.ChannelKeysPath > channel-rainbow.json next to config.
+// Merge priority: rainbow (lowest) → derived from hashChannels → explicit config (highest).
 func loadChannelKeys(cfg *Config, configPath string) map[string]string {
 	keys := make(map[string]string)
 
-	// Determine file path for rainbow keys
+	// 1. Rainbow table keys (lowest priority)
 	keysPath := os.Getenv("CHANNEL_KEYS_PATH")
 	if keysPath == "" {
 		keysPath = cfg.ChannelKeysPath
 	}
 	if keysPath == "" {
-		// Default: look for channel-rainbow.json next to config file
 		keysPath = filepath.Join(filepath.Dir(configPath), "channel-rainbow.json")
 	}
 
+	rainbowCount := 0
 	if data, err := os.ReadFile(keysPath); err == nil {
 		var fileKeys map[string]string
 		if err := json.Unmarshal(data, &fileKeys); err == nil {
 			for k, v := range fileKeys {
 				keys[k] = v
 			}
-			log.Printf("Loaded %d channel keys from %s", len(fileKeys), keysPath)
+			rainbowCount = len(fileKeys)
+			log.Printf("Loaded %d channel keys from %s", rainbowCount, keysPath)
 		} else {
 			log.Printf("Warning: failed to parse channel keys file %s: %v", keysPath, err)
 		}
 	}
 
-	// Merge inline config keys (override file keys)
+	// 2. Derived keys from hashChannels (middle priority)
+	derivedCount := 0
+	for _, raw := range cfg.HashChannels {
+		trimmed := strings.TrimSpace(raw)
+		if trimmed == "" {
+			continue
+		}
+		channelName := trimmed
+		if !strings.HasPrefix(channelName, "#") {
+			channelName = "#" + channelName
+		}
+		// Skip if explicit config already has this key
+		if _, exists := cfg.ChannelKeys[channelName]; exists {
+			continue
+		}
+		keys[channelName] = deriveHashtagChannelKey(channelName)
+		derivedCount++
+	}
+	if derivedCount > 0 {
+		log.Printf("[channels] %d derived from hashChannels", derivedCount)
+	}
+
+	// 3. Explicit config keys (highest priority — overrides rainbow + derived)
 	for k, v := range cfg.ChannelKeys {
 		keys[k] = v
 	}

cmd/ingestor/main_test.go

@@ -3,6 +3,8 @@ package main
 import (
 	"encoding/json"
 	"math"
+	"os"
+	"path/filepath"
 	"testing"
 	"time"
 )
@@ -492,3 +494,132 @@ func TestAdvertRole(t *testing.T) {
 		})
 	}
 }
+
+func TestDeriveHashtagChannelKey(t *testing.T) {
+	// Test vectors validated against Node.js server-helpers.js
+	tests := []struct {
+		name string
+		want string
+	}{
+		{"#General", "649af2cab73ed5a890890a5485a0c004"},
+		{"#test", "9cd8fcf22a47333b591d96a2b848b73f"},
+		{"#MeshCore", "dcf73f393fa217f6b28fcec6ffc411ad"},
+	}
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			got := deriveHashtagChannelKey(tt.name)
+			if got != tt.want {
+				t.Errorf("deriveHashtagChannelKey(%q) = %q, want %q", tt.name, got, tt.want)
+			}
+		})
+	}
+
+	// Deterministic
+	k1 := deriveHashtagChannelKey("#foo")
+	k2 := deriveHashtagChannelKey("#foo")
+	if k1 != k2 {
+		t.Error("deriveHashtagChannelKey should be deterministic")
+	}
+
+	// Returns 32-char hex string (16 bytes)
+	if len(k1) != 32 {
+		t.Errorf("key length = %d, want 32", len(k1))
+	}
+
+	// Different inputs → different keys
+	k3 := deriveHashtagChannelKey("#bar")
+	if k1 == k3 {
+		t.Error("different inputs should produce different keys")
+	}
+}
+
+func TestLoadChannelKeysMergePriority(t *testing.T) {
+	dir := t.TempDir()
+	cfgPath := filepath.Join(dir, "config.json")
+
+	// Create a rainbow file with two keys: #rainbow (unique) and #override (to be overridden)
+	rainbowPath := filepath.Join(dir, "channel-rainbow.json")
+	t.Setenv("CHANNEL_KEYS_PATH", rainbowPath)
+	rainbow := map[string]string{
+		"#rainbow":  "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
+		"#override": "rainbow_value_should_be_overridden",
+	}
+	rainbowJSON, err := json.Marshal(rainbow)
+	if err != nil {
+		t.Fatal(err)
+	}
+	if err := os.WriteFile(rainbowPath, rainbowJSON, 0o644); err != nil {
+		t.Fatal(err)
+	}
+
+	cfg := &Config{
+		HashChannels: []string{"General", "#override"},
+		ChannelKeys:  map[string]string{"#override": "explicit_wins"},
+	}
+
+	keys := loadChannelKeys(cfg, cfgPath)
+
+	// Rainbow key loaded
+	if keys["#rainbow"] != "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" {
+		t.Errorf("rainbow key missing or wrong: %q", keys["#rainbow"])
+	}
+
+	// HashChannels derived #General
+	expected := deriveHashtagChannelKey("#General")
+	if keys["#General"] != expected {
+		t.Errorf("#General = %q, want %q (derived)", keys["#General"], expected)
+	}
+
+	// Explicit config wins over both rainbow and derived
+	if keys["#override"] != "explicit_wins" {
+		t.Errorf("#override = %q, want explicit_wins", keys["#override"])
+	}
+}
+
+func TestLoadChannelKeysHashChannelsNormalization(t *testing.T) {
+	t.Setenv("CHANNEL_KEYS_PATH", "")
+	dir := t.TempDir()
+	cfgPath := filepath.Join(dir, "config.json")
+
+	cfg := &Config{
+		HashChannels: []string{
+			"NoPound",       // should become #NoPound
+			"#HasPound",     // stays #HasPound
+			"  Spaced  ",   // trimmed → #Spaced
+			"",              // skipped
+		},
+	}
+
+	keys := loadChannelKeys(cfg, cfgPath)
+
+	if _, ok := keys["#NoPound"]; !ok {
+		t.Error("should derive key for #NoPound (auto-prefixed)")
+	}
+	if _, ok := keys["#HasPound"]; !ok {
+		t.Error("should derive key for #HasPound")
+	}
+	if _, ok := keys["#Spaced"]; !ok {
+		t.Error("should derive key for #Spaced (trimmed)")
+	}
+	if len(keys) != 3 {
+		t.Errorf("expected 3 keys, got %d", len(keys))
+	}
+}
+
+func TestLoadChannelKeysSkipExplicit(t *testing.T) {
+	t.Setenv("CHANNEL_KEYS_PATH", "")
+	dir := t.TempDir()
+	cfgPath := filepath.Join(dir, "config.json")
+
+	cfg := &Config{
+		HashChannels: []string{"General"},
+		ChannelKeys:  map[string]string{"#General": "my_explicit_key"},
+	}
+
+	keys := loadChannelKeys(cfg, cfgPath)
+
+	// Explicit key should win — hashChannels derivation should be skipped
+	if keys["#General"] != "my_explicit_key" {
+		t.Errorf("#General = %q, want my_explicit_key", keys["#General"])
+	}
+}

cmd/server/coverage_test.go

@@ -46,14 +46,6 @@ func setupTestDBv2(t *testing.T) *DB {
 			observer_id TEXT, observer_name TEXT, direction TEXT,
 			snr REAL, rssi REAL, score INTEGER, path_json TEXT, timestamp INTEGER NOT NULL
 		);
-		CREATE VIEW packets_v AS
-			SELECT o.id, t.raw_hex,
-				strftime('%Y-%m-%dT%H:%M:%fZ', o.timestamp, 'unixepoch') AS timestamp,
-				o.observer_id, o.observer_name,
-				o.direction, o.snr, o.rssi, o.score, t.hash, t.route_type,
-				t.payload_type, t.payload_version, o.path_json, t.decoded_json, t.created_at
-			FROM observations o
-			JOIN transmissions t ON t.id = o.transmission_id;
 	`
 	if _, err := conn.Exec(schema); err != nil {
 		t.Fatal(err)
@@ -551,8 +543,8 @@ func TestHandlePacketDetailNoStore(t *testing.T) {
 		req := httptest.NewRequest("GET", "/api/packets/abc123def4567890", nil)
 		w := httptest.NewRecorder()
 		router.ServeHTTP(w, req)
-		if w.Code != 200 {
-			t.Fatalf("expected 200, got %d: %s", w.Code, w.Body.String())
+		if w.Code != 404 {
+			t.Fatalf("expected 404 (no store), got %d: %s", w.Code, w.Body.String())
 		}
 	})
 
@@ -560,8 +552,8 @@ func TestHandlePacketDetailNoStore(t *testing.T) {
 		req := httptest.NewRequest("GET", "/api/packets/1", nil)
 		w := httptest.NewRecorder()
 		router.ServeHTTP(w, req)
-		if w.Code != 200 {
-			t.Fatalf("expected 200, got %d: %s", w.Code, w.Body.String())
+		if w.Code != 404 {
+			t.Fatalf("expected 404 (no store), got %d: %s", w.Code, w.Body.String())
 		}
 	})
 
@@ -1475,8 +1467,8 @@ func TestHandleObserverAnalyticsNoStore(t *testing.T) {
 	req := httptest.NewRequest("GET", "/api/observers/obs1/analytics", nil)
 	w := httptest.NewRecorder()
 	router.ServeHTTP(w, req)
-	if w.Code != 200 {
-		t.Fatalf("expected 200, got %d: %s", w.Code, w.Body.String())
+	if w.Code != 503 {
+		t.Fatalf("expected 503, got %d: %s", w.Code, w.Body.String())
 	}
 }
 
@@ -3272,20 +3264,6 @@ func TestHandlePacketDetailWithStoreAllPaths(t *testing.T) {
 
 // --- Additional DB function coverage ---
 
-func TestDBGetTimestamps(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	ts, err := db.GetTimestamps("2000-01-01")
-	if err != nil {
-		t.Fatal(err)
-	}
-	if len(ts) < 1 {
-		t.Error("expected >=1 timestamps")
-	}
-}
-
 func TestDBGetNewTransmissionsSince(t *testing.T) {
 	db := setupTestDB(t)
 	defer db.Close()

cmd/server/db.go

@@ -120,14 +120,14 @@ func (db *DB) scanTransmissionRow(rows *sql.Rows) map[string]interface{} {
 
 // Node represents a row from the nodes table.
 type Node struct {
-	PublicKey   string   `json:"public_key"`
-	Name       *string  `json:"name"`
-	Role       *string  `json:"role"`
-	Lat        *float64 `json:"lat"`
-	Lon        *float64 `json:"lon"`
-	LastSeen   *string  `json:"last_seen"`
-	FirstSeen  *string  `json:"first_seen"`
-	AdvertCount int     `json:"advert_count"`
+	PublicKey     string   `json:"public_key"`
+	Name         *string  `json:"name"`
+	Role         *string  `json:"role"`
+	Lat          *float64 `json:"lat"`
+	Lon          *float64 `json:"lon"`
+	LastSeen     *string  `json:"last_seen"`
+	FirstSeen    *string  `json:"first_seen"`
+	AdvertCount  int      `json:"advert_count"`
 	BatteryMv    *int     `json:"battery_mv"`
 	TemperatureC *float64 `json:"temperature_c"`
 }
@@ -162,7 +162,7 @@ type Transmission struct {
 	CreatedAt      *string `json:"created_at"`
 }
 
-// Observation (from packets_v view).
+// Observation (observation-level data).
 type Observation struct {
 	ID           int      `json:"id"`
 	RawHex       *string  `json:"raw_hex"`
@@ -435,7 +435,7 @@ func (db *DB) QueryGroupedPackets(q PacketQuery) (*PacketResult, error) {
 		w = "WHERE " + strings.Join(where, " AND ")
 	}
 
-	// Count total transmissions (fast — queries transmissions directly, not packets_v)
+	// Count total transmissions (fast — queries transmissions directly, not a VIEW)
 	var total int
 	if len(where) == 0 {
 		db.conn.QueryRow("SELECT COUNT(*) FROM transmissions").Scan(&total)
@@ -628,18 +628,6 @@ func (db *DB) resolveNodePubkey(nodeIDOrName string) string {
 	return pk
 }
 
-// GetPacketByID fetches a single packet/observation.
-func (db *DB) GetPacketByID(id int) (map[string]interface{}, error) {
-	rows, err := db.conn.Query("SELECT id, raw_hex, timestamp, observer_id, observer_name, direction, snr, rssi, score, hash, route_type, payload_type, payload_version, path_json, decoded_json, created_at FROM packets_v WHERE id = ?", id)
-	if err != nil {
-		return nil, err
-	}
-	defer rows.Close()
-	if rows.Next() {
-		return scanPacketRow(rows), nil
-	}
-	return nil, nil
-}
 
 // GetTransmissionByID fetches from transmissions table with observer data.
 func (db *DB) GetTransmissionByID(id int) (map[string]interface{}, error) {
@@ -673,24 +661,6 @@ func (db *DB) GetPacketByHash(hash string) (map[string]interface{}, error) {
 	return nil, nil
 }
 
-// GetObservationsForHash returns all observations for a given hash.
-func (db *DB) GetObservationsForHash(hash string) ([]map[string]interface{}, error) {
-	rows, err := db.conn.Query(`SELECT id, raw_hex, timestamp, observer_id, observer_name, direction, snr, rssi, score, hash, route_type, payload_type, payload_version, path_json, decoded_json, created_at
-		FROM packets_v WHERE hash = ? ORDER BY timestamp DESC`, strings.ToLower(hash))
-	if err != nil {
-		return nil, err
-	}
-	defer rows.Close()
-
-	result := make([]map[string]interface{}, 0)
-	for rows.Next() {
-		p := scanPacketRow(rows)
-		if p != nil {
-			result = append(result, p)
-		}
-	}
-	return result, nil
-}
 
 // GetNodes returns filtered, paginated node list.
 func (db *DB) GetNodes(limit, offset int, role, search, before, lastHeard, sortBy, region string) ([]map[string]interface{}, int, map[string]int, error) {
@@ -798,30 +768,6 @@ func (db *DB) GetNodeByPubkey(pubkey string) (map[string]interface{}, error) {
 	return nil, nil
 }
 
-// GetRecentPacketsForNode returns recent packets referencing a node.
-func (db *DB) GetRecentPacketsForNode(pubkey string, name string, limit int) ([]map[string]interface{}, error) {
-	if limit <= 0 {
-		limit = 20
-	}
-	pk := "%" + pubkey + "%"
-	np := "%" + name + "%"
-	rows, err := db.conn.Query(`SELECT id, raw_hex, timestamp, observer_id, observer_name, direction, snr, rssi, score, hash, route_type, payload_type, payload_version, path_json, decoded_json, created_at
-		FROM packets_v WHERE decoded_json LIKE ? OR decoded_json LIKE ?
-		ORDER BY timestamp DESC LIMIT ?`, pk, np, limit)
-	if err != nil {
-		return nil, err
-	}
-	defer rows.Close()
-
-	packets := make([]map[string]interface{}, 0)
-	for rows.Next() {
-		p := scanPacketRow(rows)
-		if p != nil {
-			packets = append(packets, p)
-		}
-	}
-	return packets, nil
-}
 
 // GetRecentTransmissionsForNode returns recent transmissions referencing a node (Node.js-compatible shape).
 func (db *DB) GetRecentTransmissionsForNode(pubkey string, name string, limit int) ([]map[string]interface{}, error) {
@@ -1045,103 +991,6 @@ func (db *DB) GetDistinctIATAs() ([]string, error) {
 	return codes, nil
 }
 
-// GetNodeHealth returns health info for a node (observers, stats, recent packets).
-func (db *DB) GetNodeHealth(pubkey string) (map[string]interface{}, error) {
-	node, err := db.GetNodeByPubkey(pubkey)
-	if err != nil || node == nil {
-		return nil, err
-	}
-
-	name := ""
-	if n, ok := node["name"]; ok && n != nil {
-		name = fmt.Sprintf("%v", n)
-	}
-
-	pk := "%" + pubkey + "%"
-	np := "%" + name + "%"
-	whereClause := "decoded_json LIKE ? OR decoded_json LIKE ?"
-	if name == "" {
-		whereClause = "decoded_json LIKE ?"
-		np = pk
-	}
-
-	todayStart := time.Now().UTC().Truncate(24 * time.Hour).Format(time.RFC3339)
-
-	// Observers
-	observerSQL := fmt.Sprintf(`SELECT observer_id, observer_name, AVG(snr) as avgSnr, AVG(rssi) as avgRssi, COUNT(*) as packetCount
-		FROM packets_v WHERE (%s) AND observer_id IS NOT NULL GROUP BY observer_id ORDER BY packetCount DESC`, whereClause)
-	oRows, err := db.conn.Query(observerSQL, pk, np)
-	if err != nil {
-		return nil, err
-	}
-	defer oRows.Close()
-
-	observers := make([]map[string]interface{}, 0)
-	for oRows.Next() {
-		var obsID, obsName sql.NullString
-		var avgSnr, avgRssi sql.NullFloat64
-		var pktCount int
-		oRows.Scan(&obsID, &obsName, &avgSnr, &avgRssi, &pktCount)
-		observers = append(observers, map[string]interface{}{
-			"observer_id":   nullStr(obsID),
-			"observer_name": nullStr(obsName),
-			"avgSnr":        nullFloat(avgSnr),
-			"avgRssi":       nullFloat(avgRssi),
-			"packetCount":   pktCount,
-		})
-	}
-
-	// Stats
-	var packetsToday, totalPackets int
-	db.conn.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM packets_v WHERE (%s) AND timestamp > ?", whereClause), pk, np, todayStart).Scan(&packetsToday)
-	db.conn.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM packets_v WHERE (%s)", whereClause), pk, np).Scan(&totalPackets)
-
-	var avgSnr sql.NullFloat64
-	db.conn.QueryRow(fmt.Sprintf("SELECT AVG(snr) FROM packets_v WHERE (%s)", whereClause), pk, np).Scan(&avgSnr)
-
-	var lastHeard sql.NullString
-	db.conn.QueryRow(fmt.Sprintf("SELECT MAX(timestamp) FROM packets_v WHERE (%s)", whereClause), pk, np).Scan(&lastHeard)
-
-	// Avg hops
-	hRows, _ := db.conn.Query(fmt.Sprintf("SELECT path_json FROM packets_v WHERE (%s) AND path_json IS NOT NULL", whereClause), pk, np)
-	totalHops, hopCount := 0, 0
-	if hRows != nil {
-		defer hRows.Close()
-		for hRows.Next() {
-			var pj sql.NullString
-			hRows.Scan(&pj)
-			if pj.Valid {
-				var hops []interface{}
-				if json.Unmarshal([]byte(pj.String), &hops) == nil {
-					totalHops += len(hops)
-					hopCount++
-				}
-			}
-		}
-	}
-	avgHops := 0
-	if hopCount > 0 {
-		avgHops = int(math.Round(float64(totalHops) / float64(hopCount)))
-	}
-
-	// Recent packets
-	recentPackets, _ := db.GetRecentTransmissionsForNode(pubkey, name, 20)
-
-	return map[string]interface{}{
-		"node":      node,
-		"observers": observers,
-		"stats": map[string]interface{}{
-			"totalTransmissions": totalPackets,
-			"totalObservations":  totalPackets,
-			"totalPackets":       totalPackets,
-			"packetsToday":       packetsToday,
-			"avgSnr":             nullFloat(avgSnr),
-			"avgHops":            avgHops,
-			"lastHeard":          nullStr(lastHeard),
-		},
-		"recentPackets": recentPackets,
-	}, nil
-}
 
 // GetNetworkStatus returns overall network health status.
 func (db *DB) GetNetworkStatus(healthThresholds HealthThresholds) (map[string]interface{}, error) {
@@ -1190,10 +1039,28 @@ func (db *DB) GetNetworkStatus(healthThresholds HealthThresholds) (map[string]in
 	}, nil
 }
 
-// GetTraces returns observations for a hash.
+// GetTraces returns observations for a hash using direct table queries.
 func (db *DB) GetTraces(hash string) ([]map[string]interface{}, error) {
-	rows, err := db.conn.Query(`SELECT observer_id, observer_name, timestamp, snr, rssi, path_json
-		FROM packets_v WHERE hash = ? ORDER BY timestamp ASC`, strings.ToLower(hash))
+	var querySQL string
+	if db.isV3 {
+		querySQL = `SELECT obs.id AS observer_id, obs.name AS observer_name,
+			strftime('%Y-%m-%dT%H:%M:%fZ', o.timestamp, 'unixepoch') AS timestamp,
+			o.snr, o.rssi, o.path_json
+			FROM observations o
+			JOIN transmissions t ON t.id = o.transmission_id
+			LEFT JOIN observers obs ON obs.rowid = o.observer_idx
+			WHERE t.hash = ?
+			ORDER BY o.timestamp ASC`
+	} else {
+		querySQL = `SELECT o.observer_id, o.observer_name,
+			strftime('%Y-%m-%dT%H:%M:%fZ', o.timestamp, 'unixepoch') AS timestamp,
+			o.snr, o.rssi, o.path_json
+			FROM observations o
+			JOIN transmissions t ON t.id = o.transmission_id
+			WHERE t.hash = ?
+			ORDER BY o.timestamp ASC`
+	}
+	rows, err := db.conn.Query(querySQL, strings.ToLower(hash))
 	if err != nil {
 		return nil, err
 	}
@@ -1219,7 +1086,7 @@ func (db *DB) GetTraces(hash string) ([]map[string]interface{}, error) {
 }
 
 // GetChannels returns channel list from GRP_TXT packets.
-// Queries transmissions directly (not packets_v) to avoid observation-level
+// Queries transmissions directly (not a VIEW) to avoid observation-level
 // duplicates that could cause stale lastMessage when an older message has
 // a later re-observation timestamp.
 func (db *DB) GetChannels() ([]map[string]interface{}, error) {
@@ -1435,31 +1302,7 @@ func (db *DB) GetChannelMessages(channelHash string, limit, offset int) ([]map[s
 	return messages, total, nil
 }
 
-// GetTimestamps returns packet timestamps since a given time.
-func (db *DB) GetTimestamps(since string) ([]string, error) {
-	rows, err := db.conn.Query("SELECT timestamp FROM packets_v WHERE timestamp > ? ORDER BY timestamp ASC", since)
-	if err != nil {
-		return nil, err
-	}
-	defer rows.Close()
-	var timestamps []string
-	for rows.Next() {
-		var ts string
-		rows.Scan(&ts)
-		timestamps = append(timestamps, ts)
-	}
-	if timestamps == nil {
-		timestamps = []string{}
-	}
-	return timestamps, nil
-}
 
-// GetNodeCountsForPacket returns observation count for a hash.
-func (db *DB) GetObservationCount(hash string) int {
-	var count int
-	db.conn.QueryRow("SELECT COUNT(*) FROM packets_v WHERE hash = ?", strings.ToLower(hash)).Scan(&count)
-	return count
-}
 
 // GetNewTransmissionsSince returns new transmissions after a given ID for WebSocket polling.
 func (db *DB) GetNewTransmissionsSince(lastID int, limit int) ([]map[string]interface{}, error) {

cmd/server/db_test.go

@@ -73,16 +73,6 @@ func setupTestDB(t *testing.T) *DB {
 			timestamp INTEGER NOT NULL
 		);
 
-		CREATE VIEW packets_v AS
-			SELECT o.id, t.raw_hex,
-				strftime('%Y-%m-%dT%H:%M:%fZ', o.timestamp, 'unixepoch') AS timestamp,
-				obs.id AS observer_id, obs.name AS observer_name,
-				o.direction, o.snr, o.rssi, o.score, t.hash, t.route_type,
-				t.payload_type, t.payload_version, o.path_json, t.decoded_json,
-				t.created_at
-			FROM observations o
-			JOIN transmissions t ON t.id = o.transmission_id
-			LEFT JOIN observers obs ON obs.rowid = o.observer_idx;
 	`
 	if _, err := conn.Exec(schema); err != nil {
 		t.Fatal(err)
@@ -569,51 +559,6 @@ func TestGetNewTransmissionsSince(t *testing.T) {
 	}
 }
 
-func TestGetObservationsForHash(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	obs, err := db.GetObservationsForHash("abc123def4567890")
-	if err != nil {
-		t.Fatal(err)
-	}
-	if len(obs) != 2 {
-		t.Errorf("expected 2 observations, got %d", len(obs))
-	}
-}
-
-func TestGetPacketByIDFound(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	pkt, err := db.GetPacketByID(1)
-	if err != nil {
-		t.Fatal(err)
-	}
-	if pkt == nil {
-		t.Fatal("expected packet, got nil")
-	}
-	if pkt["hash"] != "abc123def4567890" {
-		t.Errorf("expected hash abc123def4567890, got %v", pkt["hash"])
-	}
-}
-
-func TestGetPacketByIDNotFound(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	pkt, err := db.GetPacketByID(9999)
-	if err != nil {
-		t.Fatal(err)
-	}
-	if pkt != nil {
-		t.Error("expected nil for nonexistent packet ID")
-	}
-}
-
 func TestGetTransmissionByIDFound(t *testing.T) {
 	db := setupTestDB(t)
 	defer db.Close()
@@ -656,34 +601,6 @@ func TestGetPacketByHashNotFound(t *testing.T) {
 	}
 }
 
-func TestGetRecentPacketsForNode(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	packets, err := db.GetRecentPacketsForNode("aabbccdd11223344", "TestRepeater", 20)
-	if err != nil {
-		t.Fatal(err)
-	}
-	if len(packets) == 0 {
-		t.Error("expected packets for TestRepeater")
-	}
-}
-
-func TestGetRecentPacketsForNodeDefaultLimit(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	packets, err := db.GetRecentPacketsForNode("aabbccdd11223344", "TestRepeater", 0)
-	if err != nil {
-		t.Fatal(err)
-	}
-	if packets == nil {
-		t.Error("expected non-nil result")
-	}
-}
-
 func TestGetObserverIdsForRegion(t *testing.T) {
 	db := setupTestDB(t)
 	defer db.Close()
@@ -733,46 +650,6 @@ func TestGetObserverIdsForRegion(t *testing.T) {
 	})
 }
 
-func TestGetNodeHealth(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	t.Run("found", func(t *testing.T) {
-		result, err := db.GetNodeHealth("aabbccdd11223344")
-		if err != nil {
-			t.Fatal(err)
-		}
-		if result == nil {
-			t.Fatal("expected result, got nil")
-		}
-		node, ok := result["node"].(map[string]interface{})
-		if !ok {
-			t.Fatal("expected node object")
-		}
-		if node["name"] != "TestRepeater" {
-			t.Errorf("expected TestRepeater, got %v", node["name"])
-		}
-		stats, ok := result["stats"].(map[string]interface{})
-		if !ok {
-			t.Fatal("expected stats object")
-		}
-		if stats["totalPackets"] == nil {
-			t.Error("expected totalPackets in stats")
-		}
-	})
-
-	t.Run("not found", func(t *testing.T) {
-		result, err := db.GetNodeHealth("nonexistent")
-		if err != nil {
-			t.Fatal(err)
-		}
-		if result != nil {
-			t.Error("expected nil for nonexistent node")
-		}
-	})
-}
-
 func TestGetChannelMessages(t *testing.T) {
 	db := setupTestDB(t)
 	defer db.Close()
@@ -815,48 +692,6 @@ func TestGetChannelMessages(t *testing.T) {
 	})
 }
 
-func TestGetTimestamps(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	t.Run("with results", func(t *testing.T) {
-		ts, err := db.GetTimestamps("2020-01-01")
-		if err != nil {
-			t.Fatal(err)
-		}
-		if len(ts) == 0 {
-			t.Error("expected timestamps")
-		}
-	})
-
-	t.Run("no results", func(t *testing.T) {
-		ts, err := db.GetTimestamps("2099-01-01")
-		if err != nil {
-			t.Fatal(err)
-		}
-		if len(ts) != 0 {
-			t.Errorf("expected 0 timestamps, got %d", len(ts))
-		}
-	})
-}
-
-func TestGetObservationCount(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	count := db.GetObservationCount("abc123def4567890")
-	if count != 2 {
-		t.Errorf("expected 2, got %d", count)
-	}
-
-	count = db.GetObservationCount("nonexistent")
-	if count != 0 {
-		t.Errorf("expected 0 for nonexistent, got %d", count)
-	}
-}
-
 func TestBuildPacketWhereFilters(t *testing.T) {
 	db := setupTestDB(t)
 	defer db.Close()
@@ -1280,29 +1115,6 @@ func TestOpenDBInvalidPath(t *testing.T) {
 	}
 }
 
-func TestGetNodeHealthNoName(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-
-	// Insert a node without a name
-	db.conn.Exec(`INSERT INTO observers (id, name, iata) VALUES ('obs1', 'Observer One', 'SJC')`)
-	db.conn.Exec(`INSERT INTO nodes (public_key, role, last_seen, first_seen, advert_count)
-		VALUES ('deadbeef12345678', 'repeater', '2026-01-15T10:00:00Z', '2026-01-01T00:00:00Z', 5)`)
-	db.conn.Exec(`INSERT INTO transmissions (raw_hex, hash, first_seen, route_type, payload_type, decoded_json)
-		VALUES ('DDEE', 'deadbeefhash1234', '2026-01-15T10:05:00Z', 1, 4,
-		'{"pubKey":"deadbeef12345678","type":"ADVERT"}')`)
-	db.conn.Exec(`INSERT INTO observations (transmission_id, observer_idx, snr, rssi, path_json, timestamp)
-		VALUES (1, 1, 11.0, -91, '["dd"]', 1736935500)`)
-
-	result, err := db.GetNodeHealth("deadbeef12345678")
-	if err != nil {
-		t.Fatal(err)
-	}
-	if result == nil {
-		t.Fatal("expected result, got nil")
-	}
-}
-
 func TestGetChannelMessagesObserverFallback(t *testing.T) {
 	db := setupTestDB(t)
 	defer db.Close()
@@ -1383,20 +1195,6 @@ func TestQueryGroupedPacketsWithFilters(t *testing.T) {
 	}
 }
 
-func TestGetTracesEmpty(t *testing.T) {
-	db := setupTestDB(t)
-	defer db.Close()
-	seedTestData(t, db)
-
-	traces, err := db.GetTraces("nonexistenthash1")
-	if err != nil {
-		t.Fatal(err)
-	}
-	if len(traces) != 0 {
-		t.Errorf("expected 0 traces, got %d", len(traces))
-	}
-}
-
 func TestNullHelpers(t *testing.T) {
 	// nullStr
 	if nullStr(sql.NullString{Valid: false}) != nil {
@@ -1474,9 +1272,11 @@ func TestNodeTelemetryFields(t *testing.T) {
 	db := setupTestDB(t)
 	defer db.Close()
 
+	// Insert node with telemetry data
 	db.conn.Exec(`INSERT INTO nodes (public_key, name, role, lat, lon, last_seen, first_seen, advert_count, battery_mv, temperature_c)
 		VALUES ('pk_telem1', 'SensorNode', 'sensor', 37.0, -122.0, '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z', 5, 3700, 28.5)`)
 
+	// Test via GetNodeByPubkey
 	node, err := db.GetNodeByPubkey("pk_telem1")
 	if err != nil {
 		t.Fatal(err)
@@ -1491,6 +1291,7 @@ func TestNodeTelemetryFields(t *testing.T) {
 		t.Errorf("temperature_c=%v, want 28.5", node["temperature_c"])
 	}
 
+	// Test via GetNodes
 	nodes, _, _, err := db.GetNodes(50, 0, "sensor", "", "", "", "", "")
 	if err != nil {
 		t.Fatal(err)
@@ -1502,6 +1303,7 @@ func TestNodeTelemetryFields(t *testing.T) {
 		t.Errorf("GetNodes battery_mv=%v, want 3700", nodes[0]["battery_mv"])
 	}
 
+	// Test node without telemetry — fields should be nil
 	db.conn.Exec(`INSERT INTO nodes (public_key, name, role, last_seen, first_seen, advert_count)
 		VALUES ('pk_notelem', 'PlainNode', 'repeater', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z', 3)`)
 	node2, _ := db.GetNodeByPubkey("pk_notelem")

cmd/server/routes_test.go

@@ -18,7 +18,9 @@ func setupTestServer(t *testing.T) (*Server, *mux.Router) {
 	hub := NewHub()
 	srv := NewServer(db, cfg, hub)
 	store := NewPacketStore(db)
-	store.Load()
+	if err := store.Load(); err != nil {
+		t.Fatalf("store.Load failed: %v", err)
+	}
 	srv.store = store
 	router := mux.NewRouter()
 	srv.RegisterRoutes(router)
@@ -722,6 +724,9 @@ func TestNodePathsFound(t *testing.T) {
 	if body["paths"] == nil {
 		t.Error("expected paths in response")
 	}
+	if got, ok := body["totalTransmissions"].(float64); !ok || got < 1 {
+		t.Errorf("expected totalTransmissions >= 1, got %v", body["totalTransmissions"])
+	}
 }
 
 func TestNodePathsNotFound(t *testing.T) {
@@ -832,6 +837,9 @@ func TestObserverAnalytics(t *testing.T) {
 		if body["recentPackets"] == nil {
 			t.Error("expected recentPackets")
 		}
+		if recent, ok := body["recentPackets"].([]interface{}); !ok || len(recent) == 0 {
+			t.Errorf("expected non-empty recentPackets, got %v", body["recentPackets"])
+		}
 	})
 
 	t.Run("custom days", func(t *testing.T) {
@@ -1251,6 +1259,11 @@ func TestNodeAnalyticsNoNameNode(t *testing.T) {
 	cfg := &Config{Port: 3000}
 	hub := NewHub()
 	srv := NewServer(db, cfg, hub)
+	store := NewPacketStore(db)
+	if err := store.Load(); err != nil {
+		t.Fatalf("store.Load failed: %v", err)
+	}
+	srv.store = store
 	router := mux.NewRouter()
 	srv.RegisterRoutes(router)
 
@@ -1282,6 +1295,11 @@ func TestNodeHealthForNoNameNode(t *testing.T) {
 	cfg := &Config{Port: 3000}
 	hub := NewHub()
 	srv := NewServer(db, cfg, hub)
+	store := NewPacketStore(db)
+	if err := store.Load(); err != nil {
+		t.Fatalf("store.Load failed: %v", err)
+	}
+	srv.store = store
 	router := mux.NewRouter()
 	srv.RegisterRoutes(router)
 
@@ -1521,8 +1539,6 @@ func TestHandlerErrorPaths(t *testing.T) {
 	router := mux.NewRouter()
 	srv.RegisterRoutes(router)
 
-	// Drop the view to force query errors
-	db.conn.Exec("DROP VIEW IF EXISTS packets_v")
 
 	t.Run("stats error", func(t *testing.T) {
 		db.conn.Exec("DROP TABLE IF EXISTS transmissions")
@@ -1563,7 +1579,7 @@ func TestHandlerErrorTraces(t *testing.T) {
 	router := mux.NewRouter()
 	srv.RegisterRoutes(router)
 
-	db.conn.Exec("DROP VIEW IF EXISTS packets_v")
+	db.conn.Exec("DROP TABLE IF EXISTS observations")
 
 	req := httptest.NewRequest("GET", "/api/traces/abc123def4567890", nil)
 	w := httptest.NewRecorder()
@@ -1697,13 +1713,12 @@ func TestHandlerErrorTimestamps(t *testing.T) {
 	router := mux.NewRouter()
 	srv.RegisterRoutes(router)
 
-	db.conn.Exec("DROP VIEW IF EXISTS packets_v")
-
+	// Without a store, timestamps returns empty 200
 	req := httptest.NewRequest("GET", "/api/packets/timestamps?since=2020-01-01", nil)
 	w := httptest.NewRecorder()
 	router.ServeHTTP(w, req)
-	if w.Code != 500 {
-		t.Errorf("expected 500 for timestamps error, got %d", w.Code)
+	if w.Code != 200 {
+		t.Errorf("expected 200 for timestamps without store, got %d", w.Code)
 	}
 }
 
@@ -1740,8 +1755,8 @@ func TestHandlerErrorBulkHealth(t *testing.T) {
 	req := httptest.NewRequest("GET", "/api/nodes/bulk-health", nil)
 	w := httptest.NewRecorder()
 	router.ServeHTTP(w, req)
-	if w.Code != 500 {
-		t.Errorf("expected 500, got %d", w.Code)
+	if w.Code != 200 {
+		t.Errorf("expected 200, got %d", w.Code)
 	}
 }
 
@@ -1876,7 +1891,9 @@ func TestGetNodeHashSizeInfoFlipFlop(t *testing.T) {
 db := setupTestDB(t)
 seedTestData(t, db)
 store := NewPacketStore(db)
-store.Load()
+if err := store.Load(); err != nil {
+	t.Fatalf("store.Load failed: %v", err)
+}
 
 pk := "abcdef1234567890abcdef1234567890abcdef1234567890abcdef1234567890"
 db.conn.Exec("INSERT OR IGNORE INTO nodes (public_key, name, role) VALUES (?, 'TestNode', 'repeater')", pk)
@@ -1934,7 +1951,17 @@ for _, field := range arrayFields {
 if body[field] == nil {
 t.Errorf("field %q is null, expected []", field)
 }
+	}
 }
+func TestObserverAnalyticsNoStore(t *testing.T) {
+	_, router := setupNoStoreServer(t)
+	req := httptest.NewRequest("GET", "/api/observers/obs1/analytics", nil)
+	w := httptest.NewRecorder()
+	router.ServeHTTP(w, req)
+
+	if w.Code != 503 {
+		t.Fatalf("expected 503, got %d", w.Code)
+	}
 }
 func min(a, b int) int {
 	if a < b {