Summarise which data sources recorded for which sensors, instead of scanning timed_belief

[Flix6x]

Part of the index consolidation programme in #2377.

The problem

Two mirrored accessors ask which data sources have recorded for which sensors:

accessor	query
Sensor.data_sources (via search_data_sources() with no time filters)	SELECT DISTINCT source_id FROM timed_belief WHERE sensor_id = ?
DataSource.sensors	SELECT DISTINCT sensor_id FROM timed_belief WHERE source_id = ?

Both answer a question about a relation bounded by sensors × sources — a few thousand rows at most in a large deployment — by reading the largest table in the database. Their docstrings claim they "scale to very large timed_belief tables", which is true only in the sense that they avoid fetching every row; they still have to reach every candidate row.

#2378 makes the second one materially worse. After the primary key is reordered to lead with sensor_id, nothing leads with source_id, so DataSource.sensors degrades to a sequential scan. The obvious remedy — a dedicated index on source_id — would cost a large fraction of what #2378 reclaims, which is the wrong trade for a lookup this small.

The proposal

A summary table, sensor_data_source (sensor_id, source_id), primary key on the pair, foreign keys to both with ON DELETE CASCADE. Kilobytes rather than gigabytes, and it serves both accessors as a plain lookup — so it also removes the sensor-leading one's dependence on scanning beliefs.

Maintained on the write path: every belief save funnels through TimedBelief.add_to_session, which already knows the sensor and the sources in the batch, so one ON CONFLICT DO NOTHING upsert per batch (not per row) keeps it current at negligible cost.

The honest limitation

It is a superset. A pair is added when beliefs are saved and is not removed when those beliefs are deleted, because deciding whether a pair went stale needs exactly the scan the table exists to avoid.

So a row means "this source has recorded for this sensor at some point", not "this source has beliefs stored for this sensor right now". That is the right trade for the discovery and filtering these accessors feed, and Sensor.search_data_sources still consults timed_belief directly whenever time filters are given — so time-bounded questions stay exact.

If exactness without time filters is ever needed, the options are to reconcile in the delete paths (they are CLI commands that already know their sensor/source scope, so the check is bounded) or to reconcile periodically.

Alternatives considered

• An index on timed_belief.source_id — restores DataSource.sensors but costs a large permanent index to answer a question about a few thousand rows, and does nothing for the sensor-leading accessor.
• A materialized view over SELECT DISTINCT sensor_id, source_id — correct and self-maintaining in the sense that a refresh recomputes it, but each refresh is the full scan we are trying to eliminate.
• Deriving it from most_recent_beliefs_mview (if Speed up queries via materialized view #1671 lands) — cheaper than from timed_belief, but still proportional to the number of events rather than the number of pairs, and it would couple this to that PR.

Not yet measured

The real distinct-pair count on a production-scale database. The bound is sensors × sources, and observed cardinalities put that in the low tens of thousands at worst, but the actual figure would be worth confirming when a full dataset is next available.