fix(packets): preserve area filtering in SQL history fallback #569

Problem

The GPS area filter is applied by the in-memory packet query path but silently ignored by the SQL packet query path. When an ordinary or grouped packet request falls back to SQL to cover history older than the loaded cache, it can return packets outside the selected area and an incorrect total.

This is an existing correctness bug in our fork, not a dependency on PostgreSQL.

Verified fork evidence

Read-only source review of master 157e7831980deb094db36e906c0fe7bff2f55efb on 24 October 2026, independently cross-checked. No live staging/production reproduction or new runtime test is claimed.

• PacketQuery and query implementations: PacketQuery.Area represents the transmitting node's GPS area. Both DB query variants use buildTransmissionWhere for their count and row selection.
• SQL predicate builder: handles Region, but never Area.
• Ordinary packet fallback and grouped fallback: a nonempty since or until earlier than the nonempty oldestLoaded selects the DB path, which returns directly without area filtering.
• In-memory area predicate: excludes packets whose decoded originator public key is not in the configured area.
• HTTP handler: forwards area into the query without compensating for the SQL omission. A direct DB query when no store is available has the same gap.

Our fork does not have the separate startupMemoryLimited && q.Since == "" fallback introduced by the comparison PR. Do not describe that new condition as present here.

area (originator GPS membership) and region (observer IATA membership) are different, cumulative filters. SQL currently preserves region; it is area that is missing.

Suggested deterministic reproduction

1. Configure a nonempty GPS area and create nodes inside and outside it.
2. Add an ADVERT from each node to the test database and the in-memory store. Use otherwise matching packet fields so that area membership is the distinguishing filter.
3. Verify the in-memory control returns only the inside packet.
4. Set a realistic loaded-history boundary, then request a since value older than that boundary but earlier than both test packets. This forces the SQL fallback without changing which fixture rows should match.
5. Repeat with groupByHash=true and with an older until query using appropriate fixture timestamps.
6. Assert packet identity, total, limit and offset. Current SQL does not exclude the outside packet.

Expected change

Apply the same configured-area membership contract before SQL counting and pagination for both packet query variants. Do not merely post-filter a returned page: that would leave totals wrong and pages incomplete.

Preserve current semantics for decoded ADVERT originator keys, packets without an attributable originator, unknown/empty configured areas, combined area/region filters, and all existing type/channel/observer/node/time filters. Resolve these contracts from our fork rather than assuming an upstream column is an equivalent replacement.

Keep telemetry access read-only. No schema change, additional frontend request, per-packet SQL lookup or unrelated filtering redesign should be bundled into this fix.

Acceptance and regression coverage

• Demonstrate the regression red on unchanged fork code and green with the fix.
• Cover ordinary and grouped store-to-SQL fallbacks and direct DB querying, including both since and until boundaries.
• Compare area-only and combined area/region results with equivalent in-memory fixtures.
• Verify totals and pagination before and after filtering, including empty results and multiple pages.
• Pin the fork's existing unknown/empty-area and missing-originator semantics; unfiltered requests must remain unchanged.
• Cover polygon and bounding-box area definitions as applicable, without duplicating the geometry implementation.
• Preserve existing query filters, visibility rules and the read-only DB invariant. Bound SQL parameters/work and document the performance characteristics.
• Validate the real Packets UI with a local fixture and a historical area-filtered request. No staging/production access is required.

Comparison source

Literal provenance only; this issue concerns our fork's existing historical fallback, not a request to port the PostgreSQL change.

Source repository: Kpa-clawbot/CoreScope
Pull request number: 2145
Reviewed head: f0637c40bd21369e21800c8260616008c3a64a7a
Related review topic: GPS area predicate missing from SQL packet queries