Contrail

▶️ Try the live app

Geospatial air-traffic chat agent: you ask in natural language, and the answer is an interactive visual — a live radar, an animated route network, a replay of a real day — not a wall of text.

The OpenSky API is a snapshot with no memory. Contrail is the queryable memory: a ClickHouse-backed record of what flew, where, and when — including the most dramatic 30 months in aviation history, the COVID-19 collapse.

Built for the ClickHouse × Trigger.dev hackathon 2026 ("Beyond the Wall of Text").

How it works

you ──chat──▶ apps/web (Next.js)
                │ Trigger.dev React transport
                ▼
              contrail-agent (chat.agent, packages/jobs)
                │  system prompt + component spec   packages/agent
                │  tools → @repo/db → ClickHouse    (aggregates only)
                │  lifecycle hooks → @repo/chat-store → Postgres (transcripts)
                ▼
              answer streams as prose + OpenUI Lang
                │
                ▼
              apps/web renders Components (@repo/ui)
                │ each Component fetches its own rows
                ▼
              apps/web/app/api/* → @repo/db → ClickHouse

The rule that governs everything: parameters, not datasets. The model emits the parameters of a query (a bounding box, a date, a limit) — never rows. A Component resolves those parameters against the read API client-side. A 60,000 flight replay costs the model about thirty tokens, and the model can never hallucinate a coordinate because data never travels through it.

Every number the model states comes from a summary tool (summarizeAirspace, summarizeRoutes, summarizeCovidImpact, summarizeAirlines) — small aggregates, never rows. The stock OpenUI prompt rule that tells models to "generate realistic/plausible data" is stripped at build time, and the build fails if that stripping ever stops matching (packages/agent/src/prompt.ts).

The data

Live — State Vectors over the continental US

A Trigger.dev task polls the OpenSky REST API every 60 s and ingests every aircraft aloft over CONUS into ClickHouse: states (append-only history, ordered for trajectory reads), states_latest (materialized latest position per aircraft) and traffic_density (materialized geohash-5 × minute counts, read by the TrafficDensity heatmap).

Historical — 66M flights worldwide, 2019-01 → 2021-06

The historical record is the OpenSky flightlist, seeded 100% server-side: ClickHouse Cloud downloads 30 monthly CSVs straight from Zenodo with the url() table function into a staging table, verifies the expected 66,010,819 rows, and publishes atomically with EXCHANGE TABLES — readers never see a half-loaded dataset.

Xavier Olive, Martin Strohmeier, Jannis Lübbe. Crowdsourced air traffic data from The OpenSky Network 2019–2021. doi:10.5281/zenodo.5092942

Each flight carries callsign, aircraft (icao24, typecode, registration), origin/destination ICAO codes, first/last-seen timestamps and takeoff/landing coordinates. No commercial data exists in it: no schedules, delays, cancellations or passenger counts — and the agent says so rather than guess.

The window is the point: it brackets COVID-19. An intact 2019 network, the March-2020 collapse (April 2020: −64.5% flights year-over-year; quietest day 2020-04-12 with 19,731 flights vs a 2019 peak of 103,782), and the partial recovery to −4.5% by June 2021.

Derived tables — the COVID layer

flights is static after the seed and 66M rows deep; aggregating it at request time costs seconds. Every read path therefore goes through a derived table, rebuilt from flights after each seed via the same staging + EXCHANGE TABLES publish (never observable half-built):

Table	Rows	Feeds
route_stats	~1.6M	All-time Routemap, top-routes
route_stats_monthly	~5.5M	Routemap timeline/month modes, dead-routes, COVID tools
flight_counts_daily	~900	TrendChart, summarizeCovidImpact
airline_stats_monthly	~80K	AirlineTrends, summarizeAirlines
flight_tracks	~64M	FlightSwarm (day-ordered replay reads)

flight_tracks is the slim replay copy: reading one day of flights went from a ~7 s full scan to a ~0.1–1.2 s contiguous read by ordering on day and keeping only the six numbers a moving dot needs.

Rebuild standalone with bun run build:route-stats, build:monthly-stats, build:flight-tracks in packages/db (the seed runs all of them itself).

The agent's vocabulary

The component library is closed — these thirteen are everything the agent can draw (packages/ui/src/openui/library.ts):

Component	What it shows
Stack / TextContent	Layout and in-layout prose
StatStrip	Pre-formatted headline KPIs
ScanBadge	Provenance line for a ranked answer: rows scanned, query time, engine — copied verbatim from the tool result
Table	Rows from a named source (top-routes, dead-routes, flights-timeline, …)
Livemap	Live radar: current State Vectors, click a plane to ask about it
Routemap	The historical route network as volume-weighted arcs; month snapshots one month, timeline animates all 30
Trajectory	One aircraft's flown path with an altitude profile, detected events (orbits, climbs/descents, sharp turns) marked on both
TrendChart	Flight volume over time — the collapse curve, with dated event markers
AirlineTrends	Monthly volume per airline; indexed normalises each to its 2019 level
FlightSwarm	One or two real days replayed as moving dots on a shared clock
AnomalyCards	The ranked anomalies from findAnomalies as clickable cards; clicking one asks for that aircraft's Trajectory
TrafficDensity	Where live traffic concentrates, as a heatmap over the traffic_density geohash-5 × minute counts

How the Routemap timeline works

One payload carries the busiest routes with a dense 30-month flight-count array each (/api/route-months, zero-filled server-side). Scrubbing or autoplaying recomputes the arc styling client-side — no refetch per month. The width scale is fixed to the busiest route-month of the whole range, so the collapse doesn't quietly rescale itself away; routes at zero remain as faint ghosts, because the scar tissue is part of the picture.

How the FlightSwarm animation works

The EUROCONTROL-style replay. The dataset has no en-route positions, so each flight is interpolated origin→destination across its firstseen→lastseen window — the caption says so, and the prompt forbids presenting the dots as radar tracks. What is real: which flights flew, when, between which airports, and how many are airborne at any instant.

• Payload: one day from flight_tracks as compact tuples [t0, t1, o_lon, o_lat, d_lon, d_lat], times in seconds relative to the day's 00:00 UTC. t0 is often negative — flightlist's day is the arrival date, so red-eyes are already airborne when the clock starts, as in reality. Days above the limit (60K) are down-sampled by deterministic hash, not truncated.
• Rendering: a plain 2D canvas over a Mapbox map, typed arrays, one requestAnimationFrame loop driving both panels. React never re-renders during playback (clock, counters and scrubber update through refs). Two paths:
  • Fast path (flat map, camera idle): endpoints are projected to pixels once; each frame is a lerp and a fillRect. ~100K flights at 60 fps.
  • Direct path (3D globe, or while panning/zooming): only the flights airborne at sim-time (~10% of a day) are projected through the live camera each frame; on the globe, the far hemisphere is culled by angular distance so back-side dots don't fold into the visible disc.
• The look: additive blending makes overlapping dots burn brighter — density reads as heat — and a destination-out fade leaves comet trails (screen-space, so they reset while the camera moves). Two days side by side share one clock: April 2019's thousands and April 2020's hundreds drain in lockstep.
• Interactive after the answer: the agent only sets the opening state. The reader can click a panel's date to pick another day, toggle one-day vs comparison, switch region presets (World / Europe / CONUS), flip flat ↔ globe, change the day length, scrub the clock, and pan/zoom the maps. A new agent emission resets the interaction.

Audition it without the agent at /playground/swarm (day pairs, region presets, flat/globe, day length).

Conversations — chats that survive

Every chat is a Conversation: it has a URL (/chat/[id]), appears in the sidebar, and survives anything — a mid-stream refresh included.

Two stores split the work (ADR-0008): ClickHouse keeps the flight data (OLAP); Postgres — ClickHouse Cloud's managed Postgres, behind Drizzle in packages/chat-store — keeps the chat state (OLTP): the UIMessage[] transcript, the title, the owning visitor, and the live session state the transport needs after a reload (a session-scoped access token plus the SSE resume cursor).

Identity is one anonymous cookie. apps/web/proxy.ts mints a contrail_visitor UUID (httpOnly, 1 year) on the first request; every sidebar query and token mint is scoped to it server-side. No accounts.

Writes ride the agent's lifecycle hooks (packages/jobs/src/trigger/contrail-agent.ts): onChatStart creates the Conversation row; onTurnStart persists the transcript before streaming begins — awaited, so the user message is durable even if the tab dies mid-turn; onTurnComplete writes the final transcript plus the resume cursor in one transaction, so a refresh can never replay chunks on top of a persisted reply. After the first turn a deferred LLM call (chat.defer) refines the truncated seed title into a real one.

Recovery is three layers, one per failure mode:

What happened	What recovers it
Refresh mid-stream	useChat's resume reconnects to the Session's output stream; the persisted lastEventId skips chunks already applied — the answer keeps streaming where it was
Continue yesterday's chat	The Trigger.dev Session outlives its runs: a new message boots a continuation run that rebuilds the model's context from Trigger's internal snapshot — Postgres only feeds the UI, never the model
Same chat in two tabs	multiTab coordination: the second tab goes read-only with live message broadcast

Deleting a Conversation hard-deletes its rows and closes the Trigger Session (best-effort), stopping any in-flight generation first so no run keeps streaming to nobody.

Honesty notes & limitations

• Interpolated positions. FlightSwarm dots travel straight lines between airports on the schedule of the real flight. They are not radar tracks and are labelled as such.
• day is the arrival date in the flightlist, so a day's replay includes flights that took off the previous evening (negative t0).
• Source data gap: 2019-09-22 has only 3,299 flights — an ingestion gap in the upstream dataset, not a real quiet day. Tools scope "quietest day" to 2020 so it can never be quoted as one.
• Airline = callsign prefix. Three-letter ICAO prefixes (SWA, DAL, RYR…) from callsigns shaped like airline flight numbers — 73% of flights. General aviation (tail-number callsigns) is excluded from airline comparisons.
• Dead route means ≥100 flights in 2019 and zero in 2021-H1. The threshold is shared between the table and the tool so both describe the same population. (The headline result is real: every route into Berlin Tegel shows last_flown = 2020-11 — the airport closed for good that month.)
• Live coverage is CONUS only; the historical record is worldwide.
• No commercial information in either dataset.

Running it

Requirements: bun, a ClickHouse instance (Cloud or local), a Postgres database for chat state (ours is ClickHouse Cloud's managed Postgres), a Mapbox token, a Trigger.dev project.

bun install

# 1. Flight data (packages/db — CLICKHOUSE_URL in .env.local, see .env.example)
bun run migrate            # numbered SQL migrations, tracked in _migrations
bun run seed:flights       # server-side Zenodo seed + all derived rebuilds

# 2. Chat state (packages/chat-store — DATABASE_URL in .env)
bun run push               # drizzle-kit push: conversations + chat_sessions

# 3. Web (apps/web — CLICKHOUSE_URL, DATABASE_URL, NEXT_PUBLIC_MAPBOX_TOKEN)
bun dev                    # chat at /, concepts at /playground

# 4. Agent + ingestion (packages/jobs — Trigger.dev project; DATABASE_URL must
#    also exist in the Trigger environment — the persistence hooks run there)
bunx trigger.dev@latest dev

Model access goes through the Vercel AI Gateway (AI_GATEWAY_API_KEY in the Trigger.dev dashboard); the system prompt and model are versioned with prompts.define, overridable from the dashboard without a redeploy.

More documentation

• CONTEXT.md — the domain language (use these exact terms).
• docs/adr/ — architecture decision records, including why there's no ORM on the ClickHouse side (0003), how live ingestion is sourced (0006), the derived-table + interpolated-replay design behind the COVID analytics (0007), and why chat state lives in Postgres behind Drizzle (0008).
• packages/agent/README.md — the agent's tools, prompt safety and invariant.