操作員記錄三成停機，機器捕捉七成

What you'll get out of this

In about 15 minutes, you'll walk away with:

• Why your downtime log and your machines disagree, and why both are right. Operators wrote down 3 in 10 stops (30.8%). The machines caught 7 in 10 (70.0%). Together they caught 97.5% of the stops longer than five minutes.
• The five gateway habits that quietly corrupt machine data, and the defense for each.
• How three generations of equipment talk, side by side: a laser on Sparkplug B, a press brake sending flat JSON, and legacy counters polled every 30 seconds.
• A serverless pattern for machine data on AWS: AWS IoT Core Basic Ingest, AWS IoT rules, Amazon Data Firehose, Amazon Simple Storage Service (Amazon S3), and Apache Iceberg tables built with Amazon Athena.
• What happened to AWS IoT Analytics and AWS IoT Events, and what this design uses instead.
• Measured cost per 1,000 tags: about $1,150 a month sending one message per change, and $2 to $4 a month batching once a second. Same data. One gateway setting.
• A repo you can deploy and break on purpose.

Plant leaders: read the opening, "What operators see, and what machines see," and the closing questions. Builders: jump to "Rebuilding from machine data."

Part 1: The problem

The query that wouldn't run

Storytime. I had a plan for this post. Turn on every machine mess at once, rerun post 1's margin query, and measure how far PB-02's number drifted.

It didn't drift. It didn't run.

One corrupted message from PB-01's gateway, and DuckDB guessed the wrong schema for the entire file. Not one bad row. A bad file.

Why would I bring this up? Great question. Because that was the good outcome. A query that fails is a query somebody fixes.

So I told DuckDB the columns instead of letting it guess. Then it ran, and it was quietly wrong:

• Brake placement fell from 933 of 935 operations on the right machine to 914.
• Every PB-01 setup read short: 0.78 to 0.91 times standard, when the real number was 1.06 to 1.07.
• PB-02's second-shift setups read 2.53 times standard instead of 2.84.
• PB-02's second-shift gap, the whole point of post 1, shrank from 5.7 points to 5.0.

No errors. Nothing failed. The answer got a little worse, in a way nobody would ever notice.

One mess did almost all of it: clock drift. PB-01's clock wandering a couple of minutes off true was enough to put setups in the wrong window. Duplicates, outages, and corrupted messages changed nothing once the reader survived them.

TL;DR

• Crestline's machines now behave like real gateways: drifting clocks, counters that reset at shift start, duplicates, outages, and the occasional corrupted message.
• Post 1's query on that data first failed outright, then ran and drifted. Clock drift did the damage.
• Rebuilt properly, machine data gives post 1's answer cell for cell, and cycle counts match truth exactly on all 320 machine-days.
• Operators logged 3 in 10 stops, machines caught 7 in 10, and together they caught 97.5% of stops over five minutes. 427 short stops never made it into the log.
• On AWS: 2.2 million messages through IoT Core, every one accounted for. 1,000 tags cost about $1,150 a month sent one change at a time, and $2 to $4 batched.
• Repo: https://github.com/trcollinson/crestline-supply (tag post-03-missing-historian)

The historian you don't have

Big process plants have a historian: one system that collects every tag from every machine, timestamps it, compresses it, and keeps it for years. Most mid-market discrete shops don't. They have whatever each machine shipped with, plus whatever a gateway could pull out of it.

Crestline is typical. Its shop floor speaks three languages, plus a fourth stream that isn't about parts at all:

The laser knows what it's cutting. PB-01 knows it's running, but not for which job. PB-02 knows a number went up. The powder line knows its ovens are hot and has no idea which parts are inside them.

PB-02 is old. PB-02 is grumpy. PB-02 still loses money on second shift, and we're going to prove it from its counter alone.

Five gateway habits

Every mess here is one switch in mess.yaml, and every one has a test that checks it really happens at the rate it claims.

• Clock drift. PB-01's device clock wanders about two minutes off true. The laser's drifts a few seconds.
• Counter resets. PB-02 and PEM-01 go back to zero when someone power cycles them, usually at the start of first shift. Thirteen times in 90 days.
• Duplicates. About one message in 200 arrives twice.
• Outages. A couple a week per gateway, 2 to 45 minutes each. The laser and the powder line replay what they buffered, oldest first. PB-01's gateway floods its buffer back newest first. PB-02's gateway loses everything it missed.
• Corrupted messages. About one in 1,000 from PB-01's gateway arrives cut short.

None of these are exotic. If you've connected a shop floor, you've met most of them. (If you've met all five on the same machine, my condolences.)

Part 2: The fix

Rebuilding from machine data

Here's what each habit does to a naive query, and the defense for each:

On the messy data, the rebuilt query gives post 1's answer table, cell for cell: 933 operations on the right brake, PB-02 second shift's gap at 5.7 points, setups at 2.84 times standard.

Cycle counts did better than the plan asked. The target was within 2% of truth per machine per day. The rebuild is exact on all 320 machine-days, with all 13 resets and both rollovers told apart. Post 1's simple counter arithmetic, on the same raw messages, scores 95.9%. Its 13 misses are the 13 power cycles, each one read as a 65,000-stroke day.

It isn't perfect underneath. 11 of 956 brake operations still differ from truth, and each traces to something the data can't undo: polls PB-02's gateway never sent, one corrupted message, and replayed PB-01 messages put back in time to within 46 seconds. That's the honest floor.

What operators see, and what machines see

This is the part that matters for your plant.

The MES has a downtime file. Operators fill it in when a machine stops: start, end, and a reason in their own words (jam, JAMMED, jam - see Mike, waiting on matl). I rebuilt downtime from the machines too, and scored both against the 749 stops that really happened:

The two sources fail in opposite ways.

Operators don't write down short stops. 427 stops under five minutes never made it into the file. Nobody fills out a form for a two-minute jam. The machines caught nearly all of them.

Machines don't know why they stopped. The laser reports a nozzle change as "setup" and maintenance as "idle." PB-02 can only show that its counter stopped climbing while someone was scanned onto a job, and a gap under three minutes looks like a slow cycle. The operator knows it was a die change. The machine never will.

So operators win on hours (they write down the long stops), and machines win on count (they see the short ones). Neither one is the truth. Together, they cover 95.5% of the hours and 97.5% of the stops over five minutes.

One more, because every plant has one of these. The MES showed 212 hours of downtime on PB-01. The truth was 30. Three stops were opened on a Friday night and closed Monday morning, so the weekends counted as downtime. In working time, the file was 99% right. Three entries made it look seven times worse.

And the control: with every mess switched off, the MES file finds every stop, and the machines still miss the same ones. What machines can't see isn't a data quality problem. It's what each generation reports.

On AWS: every message accounted for

Each gateway is an AWS IoT thing with its own certificate, allowed to publish only to its own Basic Ingest topics ($aws/rules/<rule>/...). Basic Ingest skips the message broker, so there's no messaging charge; you pay for the rule and its action. Each rule stamps the topic and IoT Core's receive time and hands the message to a Firehose stream, which lands it in S3 untouched. From there it's post 2's pattern: curated Iceberg tables built with Athena MERGE, checks before every merge, and an alarm. Machine data only ever arrives, so a machine table that shrinks fails the run.

A publisher replays the plant's 90 days through real MQTT connections, one certificate per gateway: 2,216,620 messages at 2,000 times real speed, in 97 minutes. Every one is accounted for: it's in the raw bucket, in the error prefix, or in IoT Core's parse-error count.

The powder line, the one machine that knows nothing about parts, sends 70% of the messages.

Then the real test. The same SQL runs on both engines: DuckDB on the laptop's files, Athena on data that went through IoT Core, Firehose, and the curated tables. Every result matches, cell for cell, on all 90 days. That includes post 1's margin table, rebuilt from machine data that went through AWS: PB-02 second shift at 20.5% margin in the ERP against 14.8% rebuilt, setups at 2.84 times standard.

Part 3: Lessons and choices

What building it on AWS taught us

Athena's plant days were UTC days. Remember the six-hour bug from my earlier post on Athena time zones? It came back, the other way around. To roll machine data up by plant day, the SQL converts a UTC time to plant time and casts it to a timestamp. DuckDB keeps the plant's wall clock. Athena quietly converts it back to UTC. Both engines held the same 277,637 brake readings and the same 33,275 strokes. They put them on different days. Nothing failed. What caught it was the new daily reconciliation check, which failed on 61 of 64 days. (That's what it's for.) The fix: take the date straight from the plant-time value, tested on both engines in summer and in winter. Last time the lesson was local time to UTC. This time it's UTC to a local date.

A corrupted message doesn't always vanish. Sometimes it lands empty. PB-01's gateway sent 29 truncated messages. The 21 cut mid-token were dropped by IoT Core before any rule ran. They never reached the rule's action or its error action, and the only trace is the rule's ParseError metric in Amazon CloudWatch. The other 8 were cut between tokens ({"ts":"2026-09-28T23:44:12.516Z"), which parses fine, so they landed as rows with none of the payload's fields. The message count balanced either way. A row count would never have shown those 8. The null-rate check did.

The bugs only real runs found. Three of them, and no unit test could have caught any:

• The data-shape checks failed every hour, all night, on one missing AWS Identity and Access Management (IAM) permission. The unit tests passed because they stub AWS. The alarm fired every hour, as designed.
• Three tests broke only at full scale. A two-day replay showed none of them. The full 90 days took 97 minutes. Replay all of it.
• Before tagging, I cloned the public repo into an empty account and reran everything. The setup command never built the machine views in Athena. My working copy's test suite rebuilt them every time, so nobody noticed. In your account, the margin query wouldn't have run. Every number in this post comes from that fresh run.

Reconcile by the day a scan opened, not the day it closed. Brake strokes against the pieces operators reported, day by day. By scan-off date, it missed by up to 21%, because forgotten scans close the next morning. By scan-on date, strokes equal pieces exactly on all 64 working days. Same data. Different day boundary. Opposite verdict.

Small ones: IoT Core allows 100 publishes a second per connection, so 1,000 tags at one message each needs 12 connections (one more reason to batch). MQTT delivers at least once, so a dropped connection mid-replay landed 4 messages twice, and the dedupe ate them. A payload Sparkplug's decode() can't parse is dropped as silently as bad JSON. IoT SQL's function is get_user_properties, plural. Iceberg tables in the AWS Glue Data Catalog want long, not bigint. And the AWS Cloud Development Kit's (AWS CDK) BucketDeployment won't empty a bucket its own stack owns, so a rollback got stuck.

What happened to the AWS IoT services

If you've read an AWS IoT architecture from a few years ago, it probably used AWS IoT Analytics for storage and processing and AWS IoT Events for detecting machine states. Both are gone. AWS ended support for IoT Analytics on December 15, 2025, and for IoT Events on May 20, 2026. Amazon Timestream for LiveAnalytics stopped taking new customers on June 20, 2025, and AWS points new customers to Amazon Timestream for InfluxDB.

This design uses none of them. IoT Core takes the messages, and everything after that is services that aren't going anywhere: Firehose, S3, Athena, and Iceberg tables any engine can read.

S3 and Athena, or something purpose-built?

I built the first option. Here's how it compares, because the right answer depends on your plant.

For a mid-market shop whose questions are about jobs, margins, and downtime by shift, S3 and Athena is the right default: it puts machine data right next to the ERP and MES data it has to be joined with. If you need a live screen on the floor that updates every second, that's a different tool.

What 1,000 tags cost

Crestline's whole platform, ERP polling and machine curation together, idles at about $0.06 a day, roughly $2 a month. But Crestline has five machines. So I ran a load test: one synthetic gateway with 1,000 tags changing once a second, for an hour, two ways.

• Mode A: one message per tag change. 3.6 million messages.
• Mode B: one batched message per second. 3,600 messages.

Same data, side by side, split in the bill by tag. Every count matched in both modes, end to end. Here's the bill (us-west-2, from Cost Explorer):

Two things drive it. IoT Core bills a rule triggered and an action executed for every message, at $0.15 per million each. (Basic Ingest did its job: the day's messaging charge was $0.00.) And Firehose's Direct PUT ingestion bills every record as at least 5 KB. Mode A's records averaged 78 bytes, so each one was billed as 5,120 bytes, about 66 times its size. The bill says 17.16 GB for the hour: 3.6 million records times 5 KB, to the hundredth.

(Mode B's range is honest uncertainty: the bill can't tell whether a 15 KB message triggers its rule once or three times, because the 90-day replay ran the same day.)

The lever is at the edge. Batch before you send. Most gateways can.

Readers made this better

Four readers of the first two posts sent ideas that are in the code now. Thank you.

• Muhammed Rasin O M (@rasinmuhammed) pointed out that switching a mess on and off proves the switch exists, not that the plant produces it. Every mess now has a test that its real rate matches its setting. He also asked for a fixture across the daylight saving change, which is how the hour that happens twice got a test instead of a surprise.
• Yuhe He (@yuhehe) asked for timestamp columns that say what they mean. Every timestamp now ends in _utc or _local, including the machine data's three clocks. Yuhe also asked for a time zone test that runs on Athena itself. It's required before every release tag now, and it guards the UTC-days fix above.
• Karan Pulyala (@karanpulyala) found the hole in post 2's delete rule: nothing proved a sweep was complete. Every full read now counts the ERP's rows at both ends, and every merge records exactly which rows it deleted (post-02.1). I promised him a measurement of the ten rows of slack on a busy ERP: the biggest gap was zero, because new jobs and cancelled duplicates both land ahead of the cursor. The slack is insurance.
• Alex Shev (@alexshev) asked for checks on the shape of the data, not only whether the job ran, and for proof the delete guard works. The fire drill refused a merge that would have deleted 65 of 565 jobs. His checks run after every merge now, and on their first full run they caught both Athena's UTC days and the 8 hollow rows.

Part 4: Your turn

Run it yourself

Everything runs on your laptop first, then deploys into your own AWS account.

You'll need: uv, Node.js with the AWS CDK CLI at version 2.1143 or later, the AWS Command Line Interface (AWS CLI), and an AWS profile with a region configured.

git clone --branch post-03-missing-historian https://github.com/trcollinson/crestline-supply.git
cd crestline-supply
uv sync

# On your laptop: simulate 90 days, rebuild the machine data, score it against the answer key
uv run crestline sim backfill --days 90 --end 2026-10-01 --seed 42
uv run crestline query downtime
uv run python eval/machines.py

# On AWS (set AWS_PROFILE first; cdk bootstrap is once per account and region)
(cd infra && cdk bootstrap && cdk deploy CrestlineFoundation)
uv run crestline sim backfill --days 90 --end 2026-10-01 --seed 42 --upload
uv run crestline erp-api secrets
(cd infra && cdk deploy CrestlineErpApi -c erpApiMess=../mess.yaml)
(cd infra && cdk deploy CrestlineCurated)
(cd infra && cdk deploy CrestlineErpIngest -c erpIngestAlarmEmail=you@example.com)
(cd infra && cdk deploy CrestlineMachineIngest -c machineIngestAlarmEmail=you@example.com)
uv run crestline machines setup     # a certificate per gateway, and the Sparkplug schema
uv run crestline athena setup

# Replay 90 days of machine messages through IoT Core (about an hour and a half), then count every one
uv run crestline machines publish --from 2026-07-03 --to 2026-09-30 --speed 2000
uv run crestline machines account

# After the curator's next hourly run: Athena against DuckDB, cell for cell
uv run crestline query margin-gap --engine athena
uv run pytest -m athena

The experiment that makes the point: set mess_level: 0 in mess.yaml, then rerun the backfill and eval/machines.py. With every mess off, the MES file finds every stop, and the machines still miss the same ones.

Cost: about $2 a month idle while the hourly jobs run. The 90-day replay is about $1, once.

Teardown: certificates first (cdk destroy can't remove a thing or policy a certificate is still attached to), then the two stacks with hourly schedules, then the rest:

uv run crestline machines setup --delete
cd infra
cdk destroy CrestlineErpIngest CrestlineMachineIngest
cdk destroy CrestlineErpApi CrestlineCurated CrestlineFoundation
cd ..
uv run crestline erp-api secrets --delete

As before, two log groups from the bucket-emptying functions stay behind (/aws/lambda/CrestlineFoundation-* and /aws/lambda/CrestlineCurated-*). Delete them with aws logs delete-log-group.

What this means for your plant

Your downtime log is the plant's memory of what went wrong. It's missing most of the stops, and the ones it has are the ones somebody had time to write down.

Three questions worth asking your team this week:

1. When did someone last compare the downtime log to what the machines say? Pick one machine and one week. Count the stops in each.
2. Whose clock is your oldest gateway using? Check it against your phone. If it's two minutes off, every report that matches machine data to jobs is off too.
3. What happens to your legacy counters at a power cycle? If the answer is "they go back to zero," find out whether any report has ever read that as a 65,000-piece day.

If you don't know the answers, you're in good company. Go find out. Do it this week.

The takeaway

If you remember one thing from this post: your operators and your machines see different halves of your downtime, and neither half is the truth.

• The downtime file caught 3 in 10 stops and most of the hours. The machines caught 7 in 10 stops and couldn’t name a single reason.
• Five gateway habits corrupt machine data quietly, and clock drift is the one that hurts. With a defense for each, the rebuild matches truth exactly.
• On AWS, the whole platform idles at about $2 a month. At scale, one gateway setting decides the bill: 1,000 tags cost about $1,150 a month one message at a time, and $2 to $4 batched.

Post 1 found a money-losing press brake. Post 2 made sure the ERP data was true. This one got the machines to tell the same story without anybody typing it in.

What's next

Crestline now has its ERP, MES, and machine data in one place you can query. The obvious next step is letting an AI agent answer questions about it. The obvious next mistake is letting that agent anywhere near a machine.

Next up: why your AI agent should never have write access to a PLC. A read-only agent on AWS, with policies enforced outside the model, and a prompt injection hidden in an operator's downtime note, to find out whether the policy layer catches it.

P.S. If your plant's downtime log has its own jam - see Mike, tell me in the comments. I want to know who Mike is.

Tim Collinson has spent nearly 30 years building software, much of it on AWS, across manufacturing, oil and gas, and enterprise IT. Reach him at trcollinson@gmail.com.