Cold Chain Case Study: Truck Logger Records a 51-Minute Excursion With No Coverage

Medicines and fresh food travel in refrigerated trucks, and many loads must stay between 2 and 8 °C for the whole trip. When the cooling fails, somebody has to know how long the load was warm and how warm it got, even if the truck is nowhere near a mobile signal at the time. In the Alpha demo a logger inside the load keeps that record itself. It runs the AltSql engine on 512 KB of flash with 920 bytes of engine RAM.

The run covers a simulated trip of 48 hours, with a reading every 30 seconds. The truck has mobile coverage for half an hour at each depot: when it sets off, at hours 16 and 32, and on arrival. Two places receive data. The cloud gets 15-minute summaries and any excursion over the mobile network, and at the dock the whole log is read out over a local link.

The trip as the logger stored it: a reading every 30 seconds, the depot stops with their coverage and door openings, and the excursion the logger recorded on its own.

The Set-Up

The rule runs on the logger, against the logger’s own last 15 minutes of readings:

altsql_append(db, "raw", (int64_t)t, temp, door);
altsql_ts_window(db, "raw", "temp", t - 870, &w);        /* the last 15 minutes */
if (!open && w.count == 30 && w.min > 8.0) {
    start = t - 870;
    altsql_put(db, "status", msg, strlen(msg));           /* "excursion since 22h27" */
    open = 1;                                             /* decided on the device */
} else if (open && temp <= 8.0) {
    altsql_ts_window(db, "raw", "temp", start, &e);       /* the whole excursion */
    altsql_append(db, "excursion", (int64_t)start, (t - start) / 60, e.max);
    altsql_put(db, "status", "ok", 2);
    open = 0;
}

What Happened

Times are hours and minutes since the truck set off, as the demo’s clock shows them.

• 00h00. The truck leaves the first depot with coverage. At 00h10 the door opens for four minutes, and the load warms briefly to 9.0 °C.
• 16h00. Second depot. Coverage is back, and 2,142 bytes of summaries go to the cloud. The door opens again at 16h10.
• 22h00. Far from coverage, the compressor fails (a simulated fault) and the load starts to warm.
• 22h42. The load has now been above 8 °C for 15 minutes, since 22h27. With no coverage, the logger decided on its own: it opened an excursion, set its status and alerted the driver.
• 22h55. The compressor runs again. The load tops out at 12.4 °C and begins to cool.
• 23h18. Back to 8 °C. The logger closed the excursion and stored it as a record of 51 minutes with its peak.
• 32h00. Third depot. 2,233 bytes reach the cloud, the excursion record among them, almost nine hours after the logger wrote it.
• 47h30. Arrival, with coverage: 2,074 bytes more. The door opens at 47h40 to unload.
• 48h00. At the dock the full log is read out: 5,959 records and 179,610 bytes, byte for byte as the logger stored them.

What the Cloud and the Dock Can Answer

The cloud never received a single raw reading. From the summaries and the excursion record it can still answer the question a shipper asks first:

cloud> SELECT time / 3600 AS start_hour, minutes, peak FROM excursion
  start_hour  minutes  peak
  22          51       12.42

It can also count the quarter-hours in which the load went above 8 °C:

cloud> SELECT COUNT(*) AS quarters, MAX(max) AS warmest FROM quarter WHERE max > 8
  quarters  warmest
  13        12.42

Five of the 13 belong to the excursion. The other eight come from the four door openings, when the load stayed above 8 °C for three minutes at most. The rule asks for 15 minutes, so none of those short spells opened an excursion.

At the dock the whole log arrives in the format the logger wrote, so it can be queried the moment it lands, with no conversion step:

dock> SELECT COUNT(*) AS readings, SUM(door) AS door_open, MIN(temp) AS coldest, MAX(temp) AS warmest FROM raw
  readings  door_open  coldest  warmest
  5760      32         3.7      12.42

Thirty-two readings with the door open make 16 minutes: four stops of four minutes each.

These are the answers the demo gives after a run without power cuts. All three queries are among its ready-made questions.

The Numbers

The cloud received 199 records in all: 192 quarter-hour summaries, the excursion, and the status changes and series definitions that go with them.

The Whole Trip Stays on the Logger

A reading with its door state takes 30 bytes on flash. The trip wrote 191,392 bytes into 512 KB, so nothing rolled over, and the dock received every reading the logger took. With large 64 KB sectors the logger erased flash only twice in 48 hours. Once its log wraps, that works out at 0.18 erases per sector per day, and flash rated for 10,000 cycles would last about 150 years (computed from the bytes written in the demo).

This is what the record is for. The excursion and the readings around it are the evidence a shipper shows when a load is questioned, so they have to come through the trip whole, power cuts included. In the Alpha’s long test, 400,000 simulated power cuts lost nothing that had been saved. The Alpha page describes the test.

Next: On Real Hardware

In the Beta the logger becomes an ESP32-S3 board, with Xtensa cores, a temperature probe and a door switch, inside a cool box that stands in for the truck. A Wi-Fi access point switched on only in “depot” windows stands in for mobile coverage, and the full read-out happens at a “dock”. A cellular modem can replace the access point later without touching the engine. The board’s power gets cut while it writes, with a target of 10,000 real cuts and no acknowledged write lost. The Alpha could not measure Xtensa code at all, because its compiler has no Xtensa target, so this board is new ground.

Later: With the Learned Query Optimizer

Temperature logs and excursion records are evidence, so under the planned Learned Query Optimizer they would be marked exact: never sampled, predicted or dropped. The layer could still help around them. With the door open on a hot day and a half-empty load, a learned model could predict that the load will pass 8 °C in nine minutes and warn the driver before the 15-minute rule fires. On regular routes it could learn where coverage ends and upload before the truck loses signal. These are expectations, to be tested after the Beta.

Try It Yourself

Open the live demo and press Play. The whole trip takes under a minute, so press Pause when the logger’s clock nears 22h, then Resume and pull the plug on the logger while its temperature line is above 8 °C. It reboots, checks every record it had saved, reads its status back from its own database and carries on. The excursion still closes when the load cools.

• case studies
• cold chain
• alpha demo