Observation idempotency is entirely the client's problem, and it costs a round-trip per write

[bharkema]

Companion to the entity-uniqueness thread. Same root cause, different altitude, and this one has a
throughput consequence we ran into during back-fill.

The situation. (Datastream, phenomenonTime) is a natural key in every deployment I have seen,
but nothing enforces it. Re-polling a vendor API, back-filling a gap, or retrying after a timeout
can all duplicate observations. So every producer that wants to be safe builds the same two things:
a persistent write-log, and a check before each write.

We have both: a Postgres write-log keyed on (datastream_id, phenomenon_time), and a
GET …/Observations?$filter=phenomenonTime eq … probe before each write. It works: a multi-day
back-fill after a restart produced a contiguous series with no duplicates.

The cost, which surprised us. We support two transports: HTTP POST and MQTT publish. On the MQTT
path the write is asynchronous and fast, but the safety probe in front of it is a synchronous HTTP
round-trip. So the fast transport is gated by the slow protocol. During back-fill over a
higher-latency path, the probe, not the publish, was the bottleneck, repeatedly hitting our client
timeout while publishes kept pace easily. Adding MQTT capacity does nothing, because the queue forms
in front of the probe. It also reads like a transport failure and is not one.

A second, smaller issue in the same area: MQTT publish is fire-and-forget. There is no ack and no
server response, so a producer cannot tell whether a publish persisted, and the only way to verify is
a REST read-back. That is defensible protocol design, but it means the MQTT write path cannot report
its own success and monitoring has to be built separately.

What would help:

• An optional but discoverable dedupe-on-natural-key for Observations, where the server ignores
  or updates a duplicate (Datastream, phenomenonTime) rather than creating a second row. That
  removes the probe entirely.
• Some form of acknowledgement on the MQTT path, so a publisher can distinguish delivered from
  dropped without a REST round-trip. like described by you @hylkevds in 2.0

To be clear this is not a criticism of either server we used, since both behave correctly. The
specification is not ambiguous here, it is silent, and the cost of that silence is a round-trip per
observation paid independently by every producer.

Curious whether others have solved this differently, or whether anyone is relying on the Data Array
extension for bulk insert and getting different performance characteristics. @justinschembri @mathisvfr

[mathisvfr]

Adding our numbers since we have been running continuously for three weeks now across three FROST targets.

We ended up with three safeguards rather than one:

Startup seeding: on each restart, query the latest observation timestamp per datastream from FROST, then only push readings newer than that. This eliminates the re-push-everything-on-restart problem entirely.
Optimistic 409: write without probing and treat HTTP 409 as "already delivered". This is fast, but it only works if the server actually enforces uniqueness and returns 409, which varies by implementation and is not documented anywhere.
Probe only on replay: the GET ...?$filter=phenomenonTime eq existence check is confined to the dead-letter queue replay path, where we are re-attempting a previously failed write and cannot trust the cursor.
The probe cost Bart described is real. During a backlog replay of ~4,500 observations we measured the per-observation probe at 150-300ms on a good connection, which made the probe the bottleneck by a wide margin. Moving to the dataArray batch extension helped enormously for the write side (500 observations per request instead of 4,500 individual POSTs), but the probe cannot be batched, so for the replay path it still dominates.

On dataArray performance across servers: it varies significantly. A 4,500-observation batch succeeds on one server but times out on another. We chunk at 500 observations per request with configurable timeouts, and we auto-detect per target whether dataArray is supported at all. Targets that reject it fall back to parallel individual POSTs (up to 10 concurrent). This detection is currently trial-and-error on first push, which is another argument for the discoverable capabilities Bart proposed.

One sharp finding on the 409 path: our CollaborAll PHP target returned 409 Conflict on every observation, but it was not a deduplication signal. The server could not auto-generate a FeatureOfInterest because Things were not linked to Locations (deep-insert was silently ignored). Our optimistic dedup logic treated every 409 as "already delivered", so we silently dropped every observation for weeks. The lesson: 409 is ambiguous. Without a standard error code that distinguishes "duplicate observation" from "missing prerequisite entity", the optimistic path is fragile.

A server-side dedupe-on-natural-key that a client can discover would remove all of this complexity. Failing that, even just a standardised error shape for "duplicate vs. other conflict" would save implementers a painful debugging cycle.

[znetsixe]

reading all of the posts, i feel like claude has been writing too many of these posts. haha.

You guys do know that, while claude is awsome at prototyping code and I use it all the time to write code, its still terrible at reasoning right? :-P