State Projection over an ADS-B Stream: One Pattern, Seven Names

There is one pattern under a dozen names. You have an append-only stream of events. You fold it into a materialized state view: one row per key, holding current state. The fold is cheap, the view is convenient, and the view forgets three things — the order the events arrived in, how many times each arrived, and where each came from.

Every community that has met this pattern gave it its own vocabulary. In Kafka it is an aggregate producing a KTable; in cloud messaging it is a consumer maintaining a table; in relational algebra it is a view over a base relation; in event sourcing it is a projector building a read model; in Kleppmann's derived data it is a materialized view; in event-driven microservices it is a stateful consumer materializing an entity; in order theory it is a monotone map from traces to down-sets. Same fold, seven words.

This report uses ADS-B as the worked instance, because ADS-B is a pattern you can point an antenna at. Two software-defined radios, a few miles apart, each hear the same aircraft. Merging their two streams into one track is a fold — and the merge makes the three forgotten things physical: two receivers with independent clocks, so order is a lie; the same aircraft heard twice, so multiplicity is real; and a track that cannot tell you which radio heard what, so provenance is gone.

The claim is not that ADS-B is special. The claim is the opposite: the importance is the language. Once you can name the merge defect in all seven vocabularies, you can talk to the streaming team, the database team, and the category theorist about the same bug without translating twice.

On 2026-09-07 a Lufthansa Airbus A380, registration D-AIMC, ICAO hex 3C65A3, flew LH424 Munich→Boston and landed at Logan at 17:30:45 EDT. It was heard on final descent by two of our RTL-SDR receivers in East Boston (42.3693, -71.0412): pi and hydra2, both feeding dump1090.

The three artifacts are exactly the three artifacts of the general pattern:

Here is the real head of both streams, and the two merged by receive time. Note that pi is already at 9,275 ft and settled before hydra2 hears a single squitter, and that once both are hearing it the messages interleave at sub-second spacing:

--- pi ---                         --- hydra2 ---
21:17:08.006  pi     MSG4          21:17:15.230  hydra2 MSG8
21:17:08.169  pi     MSG3 9275     21:17:17.720  hydra2 MSG4
21:17:08.169  pi     MSG7 9275     21:17:18.704  hydra2 MSG5 9150
21:17:08.659  pi     MSG7 9275     21:17:18.966  hydra2 MSG3 9150
21:17:13.747  pi     MSG7 9225     21:17:19.818  hydra2 MSG8
...                                ...

--- merged by receive time (which receiver, interleaved) ---
21:17:15.161  pi     MSG3 9200
21:17:15.230  hydra2 MSG8            <- 69 ms after a pi frame, different radio
21:17:17.720  hydra2 MSG4
21:17:18.966  hydra2 MSG3 9150

Capture totals: pi heard 3C65A3 across 222 distinct seconds (1,608 messages, acquired at 9 nm / 9,275 ft, held to 25 ft at touchdown); hydra2 across 32 seconds (86 messages, 8 nm / 8,925 ft, lost at 7,250 ft). The two overlap on 29 seconds. Everything below is about what the projection can and cannot say about those 29 seconds.

This is the center of the report. The same three artifacts, the same defects, and the same invariants have a name in every vocabulary. The value is not any one column; it is the row. When a merge produces an out-of-order track, the streaming engineer, the DBA, and the theorist are all looking at the same fact — but they will only discover that if someone has the table.

The projection — the aircraft's current lat/lon/alt/callsign — is a COUNT(*) > 0 over the merged stream, keyed by hex. It answers "where is 3C65A3 now" perfectly and answers nothing about the 29-second overlap:

• Order is gone. The row does not know pi heard the aircraft seven seconds before hydra2. Only the log, ordered by receive time per receiver, knows that.
• Multiplicity is gone. In the overlap the same position was heard twice; the projection keeps one value per field. This is compaction: one value per key, multiplicity discarded.
• Provenance is gone. The row cannot say pi contributed 1,608 messages and hydra2 86, or that hydra2 dropped the aircraft at 7,250 ft. The track is receiver-agnostic by construction; that is a feature for display and a defect for diagnosis.

This is the general theorem in physical form: a projection is a function of the stream that forgets everything the key does not carry. If you need order, multiplicity, or provenance, you need the log — the raw per-receiver squitters on /mnt/usb/adsb/raw/{pi,hydra2}-latest.csv. The track is where you look to fly; the log is where you look to explain.

The message distribution on pi (n = 1,608) is itself an argument for keying on identity, not on the field you happen to want:

The callsign (DLH8P inbound — note it is not the flight number LH424) rides only the 1 %-frequency identity squitter, and it arrives late. So the tracker keys on the ICAO hex 3C65A3, present on every frame, exactly as event sourcing keys the projector on an aggregate id and relational algebra keys the view on a primary key. Keying on the callsign would be keying on the rarest, latest field — a design error with a name in every column.

The classic projection defects, and where each lives in ADS-B:

Two of these are worth dwelling on because ADS-B states them more sharply than the abstract pattern does.

Duplicate is not an error here; it is the mechanism. The whole reason to run two receivers is that both hear the aircraft, so the merged stream is supposed to contain the same position twice. Deduplication — by hex plus frame content, ignoring receiver and receive-time — is the fold's job, and it is idempotent by construction: replay the log, get the same track. Every vocabulary calls the fix idempotence (ES: replay; DD: reprocessing; MQ: redrive with dedup; RA: the view is a function of T; OT: P is a function).

The CRC-decoded-to-a-valid-wrong-value case is the one the domain check cannot catch. A frame that fails CRC but decodes to an in-range altitude is ITM-05 generalized: a value inside the domain that is still wrong. No schema-domain constraint sees it; only cross-frame agreement (two receivers, or an even/odd position pair that fails to close) exposes it. This is the Baudot shift-state failure — one bit of state, lost, and every subsequent symbol decodes to a valid but wrong value.

Everything above is a claim about what a projection forgets. Here it is, counted — over months, then one hour — from our two receivers (pi and hydra2, KBOS arrivals). (Charts rendered from the raw logs in Clojure with cljplot; the loaders and specs are in the flight-tracking repo.)

The tracker is a fold and nothing more. It keys on the hex, merges the two receivers, and — crucially — answers order questions only from the log, never from the projection.

import csv, glob, gzip
from collections import OrderedDict

# SBS columns (dump1090): recv_ts, receiver, MSG, type, ..., hex(6), ..., alt(13), ..., lat(16), lon(17)
def stream(receiver):
    for f in sorted(glob.glob(f"/mnt/usb/adsb/raw/{receiver}-latest.csv/2026/09/07/*.csv.gz")):
        with gzip.open(f, "rt", errors="ignore") as fh:
            for ln in fh:
                c = ln.rstrip("\n").split(",")
                if len(c) > 17 and c[2] == "MSG" and c[6] == "3C65A3":
                    yield {"ts": c[0], "rx": receiver, "type": c[3],
                           "alt": c[13], "lat": c[16], "lon": c[17]}

def fold(*receivers):
    """projection: one row per hex, last-write-wins per field. Merges receivers,
    dedups by (field-value), keeps NO order or provenance -- by design."""
    rows = sorted((r for rx in receivers for r in stream(rx)), key=lambda r: r["ts"])
    state = OrderedDict()
    prov  = {}                       # provenance kept OUTSIDE the projection
    for r in rows:
        s = state.setdefault("3C65A3", {})
        if r["alt"]: s["alt"] = r["alt"]
        if r["lat"] and r["lon"]: s["lat"], s["lon"] = r["lat"], r["lon"]
        prov.setdefault("3C65A3", {}).setdefault(r["rx"], 0)
        prov["3C65A3"][r["rx"]] += 1
    return state, prov, rows

def check_order(rows):
    """I6 -- only expressible on the LOG. Out-of-order after merge => >1 clock."""
    out, last = [], {}
    for r in rows:
        # per-receiver the stream is monotone; across receivers the merge is not
        if r["rx"] in last and r["ts"] < last[r["rx"]]:
            out.append((r["ts"], r["rx"], "non-monotone within receiver (impossible)"))
        last[r["rx"]] = r["ts"]
    # cross-receiver interleave is expected; report the tightest gap as evidence
    return out

if __name__ == "__main__":
    state, prov, rows = fold("pi", "hydra2")
    print("projection :", state["3C65A3"])          # what you fly on
    print("provenance :", prov["3C65A3"])            # pi=1608 hydra2=86 -- NOT in the projection
    print("merged msgs:", len(rows))

The relational reading makes the schema gate literal: constraints on the base relation are checked before any view is evaluated, and the order invariant is a self-join that the view cannot express.

CREATE TABLE squitter(              -- the log: one row per received frame
  hex   TEXT NOT NULL,
  rx    TEXT NOT NULL,              -- provenance lives here, not in the view
  ts    TEXT NOT NULL,
  type  TEXT NOT NULL,
  alt   INT, lat REAL, lon REAL,
  PRIMARY KEY (hex, rx, ts, type)   -- same frame, two receivers => two rows, kept
);

-- the projection: one row per aircraft, provenance and order aggregated away
CREATE VIEW track AS
SELECT hex,
       MAX(alt) FILTER (WHERE type='3') AS alt,
       COUNT(*)                          AS n_frames   -- multiplicity collapsed to a count
FROM squitter GROUP BY hex;

-- I6: expressible ONLY on the log, never on the view
CREATE VIEW v_order AS
SELECT a.hex, a.rx, b.rx, 'I6 out-of-order' AS inv
FROM squitter a JOIN squitter b USING(hex)
WHERE a.rx <> b.rx AND a.ts > b.ts AND a.type = b.type;  -- two clocks disagree

Both rebuilds agree on the contract: the projection is a function of the log; provenance and order are not recoverable from the projection and must be asked of the log; and replaying the log yields the same track (idempotence).

Six of the seven vocabularies describe a projection that sits beside a log. The seventh — Datomic — is the one whose data model already is the ADS-B model: the log is the truth, indexes are derived, and every database is a value you can query. Because our capture stack is Clojure (babashka), this is not a metaphor — the squitter log is an append-only set of datoms, the track is a query against a db value, and the two things ADS-B loses (order and provenance) are exactly the two Datomic keeps in history and :db/txInstant.

Three ADS-B facts that the other readings only gesture at fall out directly.

1. Bitemporality is the multi-receiver clock skew. A squitter has two times: when the aircraft transmitted it (its valid time, which each receiver stamps on receipt) and when it was ingested (its transaction time). One receiver and the two move together; two receivers and the valid times disagree — pi stamped the 9,200 ft position at 21:17:15.161, hydra2 its copy at 21:17:15.230, 69 ms later. That disagreement is the skew, and Datomic is the only vocabulary here that carries both times first-class, so "which receiver's clock, and how far off" is a query, not a forensic reconstruction.

2. as-of is track replay; history is the order the track dropped. The current track keeps one position per aircraft. (d/as-of db t) gives the track as it stood at ingest time t; (d/history db) keeps every receiver's report, including the ones the track superseded — which is where the I6 out-of-order lives.

3. Retraction, not edit, is the fix. A CRC-passed-but-wrong altitude is corrected by retracting the bad datom and asserting a good one in a new transaction; as-of before the fix still shows the error. Never edit history.

{:deps {org.clojure/clojure {:mvn/version "1.12.0"}
        com.datomic/local   {:mvn/version "1.0.285"}}}
;; datascript {:mvn/version "1.7.4"} is the in-memory alternative — same
;; log-as-truth model, no Datomic dependency. Runs under clj on the JVM
;; (verified, FreeBSD/OpenJDK 21); it does NOT load in babashka out of the box.
;; Both this and Datomic Local are JVM libraries: available anywhere a JVM is,
;; FreeBSD included — not a native package.

(ns adsb.track
  (:require [datomic.client.api :as d]))

;; A squitter is a datom-set. :squitter/recv-ts is VALID time (the receiver's
;; clock); :db/txInstant (free, on every datom) is TRANSACTION time (ingest).
;; The unique tuple dedups a re-logged frame from ONE receiver (I5); the same
;; transmission heard by the OTHER receiver differs in :squitter/rx and is
;; kept — that is provenance, and it is the point of running two antennas.
(def schema
  [{:db/ident :rx/pi} {:db/ident :rx/hydra2}
   {:db/ident :squitter/hex     :db/valueType :db.type/string  :db/cardinality :db.cardinality/one}
   {:db/ident :squitter/rx      :db/valueType :db.type/ref     :db/cardinality :db.cardinality/one}
   {:db/ident :squitter/recv-ts :db/valueType :db.type/instant :db/cardinality :db.cardinality/one}
   {:db/ident :squitter/type    :db/valueType :db.type/long    :db/cardinality :db.cardinality/one}
   {:db/ident :squitter/alt     :db/valueType :db.type/long    :db/cardinality :db.cardinality/one}
   {:db/ident :squitter/uniq    :db/valueType :db.type/tuple
    :db/tupleAttrs [:squitter/hex :squitter/rx :squitter/recv-ts :squitter/type]
    :db/cardinality :db.cardinality/one :db/unique :db.unique/identity}])

;; real head of the D-AIMC (3C65A3) capture — one transaction per receiver
;; batch, so :db/txInstant records the ingest order (pi first, hydra2 later).
(def pi-batch
  [{:squitter/hex "3C65A3" :squitter/rx :rx/pi     :squitter/type 3
    :squitter/recv-ts #inst "2026-09-07T21:17:15.161Z" :squitter/alt 9200}])
(def hydra2-batch
  [{:squitter/hex "3C65A3" :squitter/rx :rx/hydra2 :squitter/type 3
    :squitter/recv-ts #inst "2026-09-07T21:17:18.966Z" :squitter/alt 9150}])

(defn track
  "the projection: current altitude for a hex, latest valid time across
   receivers. Dedups the two receivers at read; keeps neither which one nor
   the order they arrived."
  [db hex]
  (->> (d/q '[:find ?ts ?alt ?rx
              :in $ ?hex
              :where [?s :squitter/hex ?hex]
                     [?s :squitter/recv-ts ?ts] [?s :squitter/alt ?alt]
                     [?s :squitter/rx ?e] [?e :db/ident ?rx]]
            db hex)
       (sort-by first) last))

(defn skew
  "I6 / provenance: same aircraft, two receivers, valid-time gap. Expressible
   only over the log — the track has thrown :squitter/rx away."
  [db hex]
  (d/q '[:find ?rxa ?ta ?rxb ?tb
         :in $ ?hex
         :where [?a :squitter/hex ?hex] [?a :squitter/rx ?ea] [?ea :db/ident ?rxa] [?a :squitter/recv-ts ?ta]
                [?b :squitter/hex ?hex] [?b :squitter/rx ?eb] [?eb :db/ident ?rxb] [?b :squitter/recv-ts ?tb]
                [(not= ?rxa ?rxb)] [(< ?ta ?tb)]]
       db hex))

(comment
  ;; (def conn ...) ; Datomic Local, transact schema, then:
  ;; (d/transact conn {:tx-data pi-batch})       ; ingest pi
  ;; (d/transact conn {:tx-data hydra2-batch})   ; ingest hydra2 (later txInstant)

  (track (d/db conn) "3C65A3")
  ;; => [#inst "...18.966Z" 9150 :rx/hydra2]  latest valid time wins; pi's 9200 is
  ;;    gone from the track but NOT from the log.

  (skew (d/db conn) "3C65A3")
  ;; => #{[:rx/pi #inst"...15.161Z" :rx/hydra2 #inst"...18.966Z"]}  two clocks,
  ;;    ordered by valid time — the multi-receiver skew as a query.

  ;; as-of the txInstant between the two ingests: track shows pi's 9200. The
  ;; second receiver had not been ingested; the db value at that t proves it.

  ;; history keeps BOTH 9200 and 9150 with their :squitter/rx — the provenance
  ;; the current track dropped. (d/history (d/db conn)) is where I6 is legible.

  ;; fix a CRC-wrong altitude: (d/transact conn {:tx-data [[:db/retract eid :squitter/alt 40000]]})
  ;; then assert the corrected datom. (d/as-of db before-fix) still shows 40000.
  )

The skew query is the payoff. In SQL it was a self-join over T; in order theory it was "P is not faithful"; here it is one datalog clause over the log, and its existence — a non-empty result — is the proof that more than one receiver contributed and their clocks disagree. Everything the report has been saying about projection forgetting order and provenance, Datomic lets you not forget by keeping the log queryable as a value.

State it as a theorem so it has an edge to attack.

Claim. The wide current-state projection is sound (its valid rows are exactly the down-sets of the state order) iff the transition graph is a DAG, and complete (a valid row determines current state) iff the graph is a chain.

For ADS-B the relevant break is re-entry. An aircraft can leave and re-enter a state — descend, level off, descend again; or a CPR position can wrap. When a state is re-enterable:

ES

a second position event overwrites the first in the read model; the model no longer reflects history.

DD

the wide form is a compacted view; compaction keeps one value per key and discards multiplicity, and re-entry is multiplicity.

MQ

the hex still orders delivery within one receiver, but the consumer's table has one slot per field and loses the earlier value.

RA

the functional dependency (hex, field) → value fails; the correct key becomes (hex, field, ts) and the projection must aggregate (MIN, MAX, COUNT), each a different projection. The flag/current-state form is COUNT > 0, the one aggregate that forgets multiplicity.

OT

a cycle makes the state order a preorder, not a poset; antisymmetry fails and the down-set characterisation has no content.

The moral is the same in every column: the current-state projection is the right tool for "where is it now" and the wrong tool for anything that happened more than once. Our D-AIMC descent is monotone enough that the track is faithful; a holding pattern would not be, and only the log would show it.

None of the seven vocabularies invented this. The fold-into-a-projection is as old as writing things down as they happen and then summarising them. ADS-B is the modern terminus of a long line; four of its ancestors say something the software vocabularies do not.

Four ancestors sharpen four of this report's points:

• Railway interlocking is I1 (CPR pairing) as a physical lock: a signal cannot clear unless the lever frame proves the block empty. The invariant is in the mechanism, not checked afterward — the relational reading (constraints before views) with a lever you can touch. A signalman who altered the indicator without a bell code was dismissed: the anti-pattern of editing the projection instead of appending to the log, with a 170-year precedent.
• Baudot shift state is why a projection can be silently wrong: one bit of state carried by the fold and present in no single symbol. Lose it and every later symbol decodes to a valid-but-wrong value — the CRC-passes-wrong-value case, which no domain check catches.
• Dead reckoning vs. fix is integrity vs. timeliness, older by centuries: the log is integrity, the plot is a derivation, and the fix is a later event with better authority that corrects the projection without editing the log. That is how you fix a bad track: post a correcting observation, as a bookkeeper posts a reversing entry — never edit history.
• ADS-B multi-receiver merge is the whole report in one line: out-of-order in a merged feed proves more than one receiver and disagreeing clocks. We measured it: pi and hydra2, 69 ms apart on the same aircraft.

The engineering content of this report fits in one sentence: a projection is a fold of a log that forgets order, multiplicity, and provenance, so keep the log. Everyone who has built one of these knows that sentence. What they do not share is the words — and a bug report written in Kafka reaches the database team as noise, and a category-theory framing reaches the on-call engineer as nothing at all.

The two-receiver ADS-B capture is worth keeping precisely because it is concrete enough to anchor every vocabulary at once. "The merged track is out-of-order" is, simultaneously and provably: a processing-time-vs-event-time problem (Kafka), a cross-group ordering gap (SQS), a ts self-join hit (SQL), a late-arriving event (event sourcing), a timeliness rather than integrity failure (DDIA), an unfaithful functor (order theory), and two radios with skewed clocks (ADS-B). Being able to say all eight of those about one 69 ms interleave is the skill. The antenna is just what makes it real.

• Sun, The 1090 MHz Riddle — ADS-B message types, DF codes, CPR odd/even position decoding.
• Kleppmann & Riccomini, Designing Data-Intensive Applications 2e — derived data, integrity vs. timeliness, event time vs. processing time, log compaction, idempotence.
• Hoffman, Real-World Event Sourcing — projector, projection, read model, aggregate vs. projection, "all data on events".
• Bellemare, Building Event-Driven Microservices — event-carried state, single writer, entity vs. event streams, schema registry.
• Kafka and Kafka Streams documentation — topic, partition, offset, consumer group, compaction, KTable, exactly-once semantics.
• AWS SQS/SNS documentation — FIFO, MessageGroupId, deduplication, visibility timeout, DLQ.
• Codd, the relational model — domain, key, functional and inclusion dependency, view.
• Davey & Priestley, Introduction to Lattices and Order — down-sets, order ideals, monotone maps.