Every few weeks since The Public Radio went into production last summer, I've been struck with a pressing - and often urgent - need for some new and/or improved business or operations tool. One recent occurrence was particularly pressing, as we've had a few (ultimately manageable) inventory issues and are facing continually more complex logistics: We needed to know more about what parts and assemblies we had on hand at any given moment.

The Public Radio is assembled, programmed, and fulfilled from two locations: Worthington Assembly (our contract manufacturer and PCBA house in Massachusetts) and our corporate headquarters (a.k.a. my basement). The materials we use come from China (our speaker, potentiometer, and a few other electronic and mechanical components), Taiwan (our lid), a few stock component distributors in the US (our jar and a few SMT components), and a few custom US manufacturers (our box, and for radio station orders some laser marking on the lids). Some of these parts are used by Worthington to make our PCBAs; some are used to make our mechanical assembly; the rest are used to fulfill orders.

What we need to track is that conversion chain: Parts to PCB assembly, parts (and PCBA) to mechanical assembly, parts (and mechanical assembly) out the door.

For my current purposes, there are three events in this chain: Receive inventory, Create mechanical assembly, Fulfill order. (Note that I'm ignoring the components in the PCBA at this stage, and instead simply tracking the PCBA at the assembly level.) The first of these happens every few months and can be done manually, but the latter two will happen something like ten thousand times this year; it simply needs to be automated.

Luckily, our entire manufacturing process runs online. Our order database runs on Heroku, and each manufacturing cell needs internet connectivity in order to process orders. As a result, I had a perfect opportunity to build a quick and dirty cloud based inventory dashboard.

As these things go, I ended up only having a few days to build the system, and I was pretty sure that the early data would need a good amount of ad hoc massaging (i.e. I would need to use a database that could be manually edited whenever inventory needed to be reconciled). So, Google sheets it was :)

With manual edits being sufficient for receiving (and reconciling) inventory, what I still needed was a way to record inventory events and then send them to Google. The result was two scripts:

The first records inventory events in a CSV that's stored locally to the event (on the manufacturing cell's Raspberry Pi). Each record contains a timestamp, the event type, and some username (either a string that's passed in as an argument or the Pi's hostname, which is unique).
The second script parses the CSV line by line and updates our Google sheet for each inventory event.

Our Google spreadsheet contains many sheets: One for each inventory part/assembly, and a dashboard which sums up the current stock on hand for all parts/assemblies. As a result, our second script needs to make multiple updates for each line in the CSV. This took a bit of fiddling to get right (note: appending rows one by one is slow), but now the whole process only takes a few seconds, even with 1-200 events (which is about what one manufacturing cell can do in a day) to update.

The result is simple, but powerful: A real time (or nearly real time - the second script currently runs once a day on a cron task, as there's really no need for more granular data) dashboard showing on hand inventory of 8 different items:

Critical levels all over the place. But also, just in time!

This will eventually get a few updates. The most important one is to track inventory of the two manufacturing locations (Massachusetts and Brooklyn) independently - a relatively easy task. I'll probably also create an interface for reconciling inventory, which is currently done by manually adding reconcile rows (instead it could probably be done by removing all old inventory events and adding a single "starting inventory" row). Lastly, there's a high likelihood that the work being done in the inventory scripts ends up being integrated directly into Tulip, which is already handling the vast majority of our operations.

But even without those improvements, these two simple scripts form an incredible tool.