AWS Builder Center
From SQLite to the Cloud: Architecting a Migration Path for a Manufacturing Traceability Dashboard

From SQLite to the Cloud: Architecting a Migration Path for a Manufacturing Traceability Dashboard

How a single-station SQLite-based traceability dashboard hits its limits at scale, and the AWS-native services I'd use to redesign it — RDS, S3, Lambda, and a path toward tying in edge computer vision via IoT Greengrass.

From SQLite to the Cloud: Architecting a Migration Path for a Manufacturing Traceability Dashboard

The problem: traceability, one station at a time

At Station 7 (Horn Tuning & Testing) on a manufacturing line I've been working with, quality data used to live in disconnected spreadsheets and paper logs. Defects were caught, but rarely tracked in a way that let anyone spot a pattern — a bad batch, a drifting yield rate, a recurring failure mode — until it had already cost real production time.
I built a full-stack traceability dashboard to fix that: a Node.js/Express backend, a React front end with Recharts for visualization, and SQLite as the data store. It gives the floor a live view of KPI cards and yield trends, and generates QR codes for every defect so it can be traced back to a specific unit, station, and timestamp.
For a single-station pilot, this stack was the right call. SQLite meant zero infrastructure overhead, the whole thing could run on a single machine near the line, and iteration was fast — which mattered, because the requirements were still being discovered in real time by talking to the people actually running the station.
That's usually where these write-ups stop: "and it worked!" But the more interesting question is what happens next — because a single-station pilot and a multi-station, multi-line traceability system are not the same problem, and the architecture that got me to v1 is not the one that scales past it.

Where the current design breaks

A few specific pressure points show up as soon as you try to grow this beyond one station:
Concurrent writes. SQLite handles a single writer well. The moment you have multiple stations logging defects and KPI events at the same time, you're fighting file-level locking instead of writing application logic.
Defect image storage. Right now, QR codes point to structured defect records, but the natural next step is attaching photos of the defect itself for review. That's a binary-blob problem, and it doesn't belong in a relational database file sitting on a single machine's disk.
Durability and backup. A local SQLite file is a single point of failure. If that machine's disk fails, the traceability history for the line goes with it. There's no replication story here.
Real-time visibility across stations. KPI cards are only "live" for whoever's looking at that one machine's screen. Getting the same live view to a supervisor's laptop, or a dashboard on the factory floor, means the data needs to live somewhere network-accessible and push-capable — not read off a local file.
Alerting. Right now, a yield drop is something a person has to notice by looking at a chart. At scale, that should be a system catching it and notifying someone automatically.
None of these are flaws in the original design — they're just the natural ceiling of a single-machine, single-writer architecture. The interesting part is mapping each pressure point to a cloud-native replacement.

The AWS-native redesign, service by service

Here's how I'd rebuild this if I were taking it from a single-station pilot to a production system across multiple stations and lines.
Amazon RDS (or Aurora) replacing SQLite. A managed relational database solves the concurrent-write problem directly — multiple stations can write defect and KPI events simultaneously without file-locking contention. Aurora specifically would make sense if the line count grows enough that read replicas become useful for dashboard queries without competing with write traffic from the stations.
Amazon S3 for defect images. Every QR-linked defect record gets an associated S3 object instead of trying to force image blobs into the relational store. This also opens the door to lifecycle policies — moving older defect images to cheaper storage tiers once they're no longer needed for active review.
AWS Lambda for event-driven alerting. Instead of a human watching a yield chart, a Lambda function can watch for yield dropping below a threshold (triggered off new rows in the database, or off a queue — see below) and push a notification via SNS to whoever's on shift. This turns "someone noticed" into "the system noticed."
Amazon SQS or EventBridge between stations and the backend. If write volume grows — more stations, higher inspection frequency — putting a queue between the station data source and the database write path smooths out bursts and decouples the stations from backend availability. A station shouldn't lose a defect record because the backend was mid-deploy.
Amazon CloudWatch for monitoring the dashboard itself. Not just monitoring the manufacturing line — monitoring the system that monitors the line. API latency, error rates on defect submission, database connection health. If the traceability system goes down during a shift, that needs to be caught immediately, not discovered the next morning.
Amazon QuickSight as an option alongside (or instead of) Recharts. Recharts works well for a purpose-built, custom KPI view. QuickSight becomes worth considering once non-engineering stakeholders — plant managers, quality leads — want to build their own views into the same underlying data without filing a ticket for a new chart.

Where this connects to the computer vision side

There's a natural extension here I've also scoped but not yet built: a computer vision quality-inspection system for contact points, using YOLOv8 running on a Jetson Nano at the edge, tied to a pneumatic diverter gate that physically sorts defective units.
The interesting architectural question is how that edge inference connects back to this same traceability dashboard. AWS IoT Greengrass would let the Jetson Nano run inference locally (important — you don't want inspection decisions waiting on a round-trip to the cloud) while still syncing results back through AWS IoT Core into the same event pipeline feeding the dashboard. A defect caught by the vision system and a defect logged manually at Station 7 would end up in the same database, on the same dashboard, traceable through the same QR-code scheme — just with a different origin.

Open questions I'm still weighing

This is the part I'd actually like feedback on:
  • RDS vs. DynamoDB for the event log. The KPI/defect event stream is fairly append-heavy and query patterns are mostly "give me everything for this station in this time range." That's arguably a better fit for DynamoDB with a good partition key than a relational schema — but relational makes ad-hoc reporting joins (defects by operator, by shift, by material batch) much easier. I haven't fully settled this.
  • How much should live at the edge vs. the cloud once the Jetson Nano CV pipeline is in the picture. Full edge autonomy is more resilient to network issues on a factory floor, but makes the "single pane of glass" dashboard harder to keep consistent.
  • Multi-tenancy if this needs to eventually support multiple plants, not just multiple stations on one line — that changes the database design more than anything else on this list.
If anyone's built something similar — especially the RDS-vs-DynamoDB call for shop-floor event data — I'd genuinely like to hear how you landed on it.
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article