Luftdaten decoder mis-routes SPS30_N1 into the NC10 sensor — greedy substring matching + non-deterministic sensor order

[MMBytes]

Summary

On the new backend, a device uploading Luftdaten-format SPS30 data can have its
SPS30_N1 (NC1.0) values silently delivered into the NC10 sensor, starving
the NC1.0 sensor entirely. Two independent defects compound to cause this:

1. findLuftdatenSensorId() uses greedy substring alias matching with
   first-match-wins over the device's sensor list — and "nc10".includes("nc1")
   is true, so a sensor titled NC10 can capture the n1 phenomenon.
2. The sensor list used for decoding is loaded without any ORDER BY, so
   Postgres returns heap order. Whether bug 1 bites therefore depends on
   physical row order, which silently changes when rows are rewritten
   (migration, autovacuum, any UPDATE).

Either fix alone resolves the symptom; both are worth fixing.

Environment

• Host: upload.staging.opensensemap.org (new backend, openSenseMap/frontend)
• Device: ID redacted, please PM me for details. SPS30 + BME280
  • DNMS + WiFi sensors, uploading Luftdaten JSON every 3 min via
    POST /boxes/:deviceId/data?luftdaten=1
• Payload includes (among others):
  {"value_type":"SPS30_N1","value":"12.01"} and
  {"value_type":"SPS30_N10","value":"12.10"}

Observed behaviour (field data)

• Until 2026-07-16 ~04:00 UTC all 14 sensors updated every cycle.
• From one cycle to the next — with no device-side or box-config change —
  the NC1.0 sensor stopped receiving values and has been stale since
  (22+ days). All other sensors continue to update normally.
• The NC10 sensor now receives two writes per upload: the mis-routed
  SPS30_N1 value followed by the correct SPS30_N10 value (body order is
  preserved by the transform), so its measurement history is polluted with
  interleaved NC1.0 values.
• The device's current sensor order, as returned by the API, is:
  NC0.5, NC4.0, rel. Luftfeuchte, PM10, NC10, NC1.0, Luftdruck, Temperatur, PM1 (P0), Schallpegel, NC2.5, Signal, PM2.5, PM4 — note NC10 precedes
  NC1.0. The clean break at a specific timestamp, with unchanged
  configuration, indicates the physical row order flipped server-side at that
  moment (deploy/migration/vacuum window).

Root cause 1 — greedy substring alias matching

app/services/decoding-service.server.ts, findLuftdatenSensorId():

const aliases = luftdatenMatchings[vt_phenomenon] // n1 → ['nc1.0','nc1','n1.0','n1']
const titleMatches =
 title === vt_phenomenon ||
 aliases.includes(title) ||
 aliases.some((alias) => title.includes(alias)) // ← "nc10".includes("nc1") === true
if (titleMatches) return sensor.id // ← first match wins

For value_type = "SPS30_N1" (phenomenon n1), a sensor titled NC10
matches via the substring rule before the correctly-titled NC1.0 sensor is
ever considered — whenever NC10 happens to come first in the list.

Note the collision is not renameable-around: the n10 aliases are
['nc10','n10'], and every string containing either necessarily contains an
n1-matching substring. Only ordering (or a matcher fix) disambiguates.

The classic API (sensebox/openSenseMap-API,
packages/models/src/measurement/decoding/luftdatenHandler.js) has the same
matcher logic, but is shielded in practice: sensors there are an embedded
MongoDB array whose order is structurally stable (creation order). The new
Postgres backend removed that accidental protection.

Root cause 2 — sensor relation loaded without ORDER BY

app/db/models/device.server.ts, getDeviceForMeasurementWrite():

with: {
 sensors: {
 columns: { id: true, title: true, sensorType: true },
 // no orderBy → Postgres heap order, not deterministic over time
 },
},

Without ORDER BY, Postgres returns rows in physical order; any tuple rewrite
(UPDATE, migration, VACUUM FULL, etc.) can permanently reorder them. Decode
results should not depend on this.

Suggested fixes

1. Matcher specificity (decoding-service.server.ts): resolve matches in
   priority tiers across all sensors before accepting a weaker tier —
   (a) title === phenomenon, then (b) aliases.includes(title) (exact alias
   equality), and only then (c) substring containment, preferring the longest
   matching alias. Under that rule NC1.0 (exact alias of n1) always beats
   NC10 (substring hit) regardless of order.
2. Deterministic order (device.server.ts): add
   orderBy: (sensor, { asc }) => asc(sensor.createdAt) (or asc(sensor.id))
   to the sensors relation in getDeviceForMeasurementWrite() — and any other
   sensor list consumed by decoders.

Minimal repro

1. Create a device with two phenomenon-colliding sensors so that NC10
   precedes NC1.0 in the relation result (on a fresh box: create NC10 first).
2. POST /boxes/:id/data?luftdaten=1 with body
   {"sensordatavalues":[{"value_type":"SPS30_N1","value":"1.0"},{"value_type":"SPS30_N10","value":"2.0"}]}
3. Observe: NC1.0 sensor receives nothing; NC10 receives both values.

[MMBytes]

Follow-up: storage-layer analysis — impact is silent data replacement, not just channel starvation

After reading the storage path, the impact is worse than the original report
states. The mis-routed value doesn't just starve the NC1.0 sensor — it
replaces the NC10 history, and the true NC10 values are silently discarded:

1. The luftdaten decoder stamps each decoded value with createdAt: new Date()
   per element inside a tight .map()
   (decoding-service.server.ts) — consecutive elements almost always get the
   identical millisecond. The mis-routed SPS30_N1 row and the correct
   SPS30_N10 row therefore share (sensorId, time).
2. The measurement table declares unique().on(t.sensorId, t.time)
   (app/db/schema/measurement.ts).
3. insertMeasurementsWithLocation() inserts with .onConflictDoNothing()
   (app/lib/measurement-server-helper.ts), and rows are inserted in body
   order — Luftdaten senders emit SPS30_N1 before SPS30_N10.

Net effect per upload: the NC1.0 value inserts first under the NC10 sensor ID,
and the true NC10 value is dropped as a same-millisecond conflict. On the
affected device, the NC10 measurement history (and the 10-min/1-hour
materialized views built from it) since 2026-07-16 is actually NC1.0 data.

Why it's easy to miss: updateLastMeasurements() builds its per-sensor map by
scanning the batch backwards with a strict < timestamp comparison, so the
true NC10 value always wins the "latest measurement" display — dashboards look
healthy while the stored history is wrong. And numerically NC1.0 ≈ NC10 for
ambient air (SPS30 number concentrations are cumulative, dominated by
sub-micron particles), so the corruption is statistically subtle.

Data recovery note

The mis-routed rows are not garbage — they are the NC1.0 sensor's missing
history stored under the wrong sensor ID, and the NC1.0 channel is empty in
exactly that window. After the matcher/ordering fix lands, affected devices
could be repaired with a targeted

UPDATE measurement
SET sensor_id = '<nc1.0-sensor-id>'
WHERE sensor_id = '<nc10-sensor-id>'
 AND time > '<corruption-start>';

(excluding the rare millisecond-straddling cycles where both rows survived —
identifiable as pairs ~1 ms apart). The true NC10 values in the window are
unrecoverable since they were never stored; NC10 would honestly show a gap.

Two additional hardening suggestions this analysis surfaces, independent of
the matcher fix:

• Stamp one shared timestamp per decoded batch (as the classic API's
  transformAndValidateMeasurements does with a single utcNow()), so
  same-batch duplicates become deterministic conflicts rather than
  millisecond-lottery ones.
• Consider logging/counting onConflictDoNothing skips — a nonzero conflict
  rate on inserts is exactly the signal that would have surfaced this bug
  months earlier.

[MMBytes]

Addendum: the fix for root cause 2 is already half-built — an order column exists, the UI persists it, decode ignores it

While evaluating workarounds we noticed the ordering fix is simpler than
proposed in the original report:

• The sensor schema already has an order column:
  order: integer('order').default(0) (app/db/schema/sensor.ts).
• The device sensors edit page already persists it: the action in
  app/routes/device.$deviceId.edit.sensors.tsx walks the submitted list and
  writes order = 0, 1, 2, … per sensor on save.
• But getDeviceForMeasurementWrite() (app/db/models/device.server.ts)
  loads the sensors relation with no orderBy, so the persisted order is
  never consulted by the luftdaten decoder — routing follows Postgres heap
  order instead.

So root cause 2 reduces to a one-liner:

with: {
 sensors: {
 columns: { id: true, title: true, sensorType: true },
 orderBy: (sensor, { asc }) => [asc(sensor.order), asc(sensor.createdAt)],
 },
},

(The createdAt tiebreaker covers legacy devices where every sensor still has
the default order = 0.)

This would also turn the UI drag-reorder into a deliberate, supported way
for users to resolve alias collisions while the matcher fix (root cause 1) is
pending — today it only "works" by the accident that saving a reorder rewrites
every sensor row and MVCC tuple placement tends to follow update order, which
is the same nondeterminism that triggered this bug in the first place.

Any other sensor-list consumer feeding a decoder should get the same
orderBy treatment.