bug(packets): page shows old/empty because first_seen=rxTime sorts buffered uploads to top — should order by ingest-time

[Kpa-clawbot]

Summary

Packets page shows old/empty even though MQTT ingest is active. Root cause: #1233 set transmission first_seen to the observer's receive time (rxTime), not the ingest time. When an observer buffers offline and uploads hours later, all those packets get a first_seen value from hours ago — they sort to the BOTTOM of the packets-page list (which orders by first_seen DESC), invisible behind newer-rxTime packets.

Symptom on staging:

• Ingestor [stats] shows tx_inserted=9 tx_dupes=760 per 5min (active ingest)
• DB MAX(first_seen) = 2026-05-24T04:00:01Z (recent)
• DB ORDER BY id DESC LIMIT 10 returns packets with first_seen ~7h ago (buffered observer uploads)
• Packets page renders only the freshly-stamped ones, looks empty during the buffered-upload tail

Per #1233's intent, packet time = receive time is the CORRECT semantic for analytics. But the UX of "show me the latest activity" is broken because latest-INGESTED ≠ latest-FIRST-SEEN.

Fix options

1. Order by id DESC instead of first_seen DESC on packets page. Restores "latest activity" semantic. Loses ability to chronologically navigate by receive time at this view (use packet-detail timestamps for that).

2. Add a "Sort by: ingest-time | receive-time" toggle. More work, more UX.

3. Expose last_observed_at (max observation timestamp) per tx. Order by that instead. Most "correct" for "latest network activity" but requires JOIN at query time or denormalized column.

4. Make /api/packets default to since=24h filter so buffered uploads with first_seen >24h ago are excluded from default view. Operator can opt to see them.

Recommend (1) + add a small "Sort by" toggle later. (1) is one-line SQL change.

Acceptance

• Packets page default view shows packets ordered by ingest-time (id DESC)
• Test on staging: after a buffered observer uploads N old packets, packets-page top still shows real-time inflow
• Mutation: revert the ORDER → buffered uploads dominate again

Out of scope

• Re-thinking fix: ingestor uses ingest-time now() instead of observer receive time #1233's first_seen semantics
• Changing observations table

References

• fix: ingestor uses ingest-time now() instead of observer receive time #1233 marcelverdult — ingestor uses ingest-time-aware rxTime (correct semantic)
• cmd/server/routes.go /api/packets handler
• public/packets.js rendering