Imagine being the owner of a makerspace, where it is up to you to organize and track what you have in inventory. Sometimes your members might take components and not return them, or they bring their own stuff and then combine it with the space’s. This issue is why I have created an inventory management system that uses NFC and storage bins to organize and track components with the power of IoT.
Demonstration Video
General Layout
Each bin gets assigned a name, description, general type, and an NFC UID that corresponds with its sticker. Parts go into bins, and each part has a name, description, quantity, type, and which bin ID it belongs to. All this data is hosted on a MySQL database. I used a NodeJS webserver to create a cloud API which could then do certain actions to the MySQL database, such as creating new parts or removing a bin. On the hardware side, I used a Particle Photon with a 128x128 OLED screen and NFC reader, along with an MPR121 for virtual buttons.
I have also created a webpage that allows for full access to the API, where it is possible to change almost anything about the bins or parts in an easy and clean way.
Webserver
I decided that using the Express library would be best for my use case, as it allows for the creation of an easy RESTful API, along with easy path creation. I began by using npm to install express, along with cors and mysql. Then I included them in the top with “require(‘package_name’)”. I used the createPool function in the MySQL library to connect to the MySQL database, as it allows for the creation and release of connections easily and safely. Each API path has its own function, where “app.post” is for POST requests, and “app.get” is for GET requests.
The string is the path to send the data, such as “/parts/get_all_bins”. The content of the functions varies, but in general, each one establishes a connection to the database, parses the request data, and executes a certain query. Adding a buffer between the database and device creates a layer of security and control.
MySQL Database
So now there is a way to access and change the data, but where or how is the data stored? When I installed the MySQL database, I also installed MySQL Workbench, which allows users to manage their database. I created a new schema called “components” and made two tables: parts and bins.
Each table needed different columns, since bins don’t have a quantity and parts don’t need an NFC UID.
I also created a new user with a password to use with the NodeJS server. Doing this instead of using the root password greatly increases security if someone gains access to the server’s code.
NFC RFID Tags
When I first started this project, I only had a few NFC cards, so I had to find a new way to store and scan data. So then I went onto AliExpress in search for a better medium. I came across NFC stickers, which house a very small antenna and chip, but are paper-thin and small. This allows for them to be stuck onto bins and read from with ease.
Hardware
So how exactly does the bin get scanned, and how are the quantities changed? For this issue, I used an RC522 RFID reader to read the NFC tags. To display the data and instructions, I used a 128x128 OLED screen that uses the SSD1351 controller. Since this project is meant for makerspaces, I wanted to avoid physical buttons, as they can wear out. I opted to use an MPR121 capacitive touch board due to its cheapness and I2C interface.
It can handle up to 12 different channels. In my design for the case, I added 3 places where 1/4-inch copper tape is attached. These pieces of copper act as buttons when touched.
Code for hardware
Since the system relies on a series of steps, I developed a system that uses a state machine to control which pages get displayed. It goes in the following order: search for card -> select bin -> display bin -> display part -> change quantity. In order to parse the responses from the webserver, I used the SparkJson library. At each stage, a request gets generated and sent to the webserver.
Webpage
This was the hardest aspect of the project to get correct. It has three components: HTML, JavaScript, and CSS.
Since I am still new to CSS, I used W3 School’s W3.CSS classes. In order to ensure a clean interface, I made a simple grid of buttons, and each button corresponds to a modal card that pops up. Each modal card also has a close button in the top-right. Buttons such as list bins and list items display a table that updates when clicked. Buttons such as add a new item and add bin contain a form.
The JavaScript code is too complex to explain in this writeup, but it basically contains various functions that get called whenever a form is submitted. Some functions also populate select elements and tables.
Usage
To use the system, someone will first populate the list of bins they have by entering in their names and other data. Then the parts get created in a similar manner. There is a button to create parts in bulk if necessary, such as having a bin full of various values of resistors. Once everything is created, bins can now be scanned with the photon.
Future Plans
In the future, I hope to expand the system to include a log-in and ownership aspect. Users could scan their makerspace RFID card and then have access to their own personal bins.
const mysql = require('mysql');
var express = require('express');
var parser = require('body-parser');
const cors = require('cors');
var app = express();
app.use(parser.json({extended: true}));
app.use(cors());
app.options('*',cors());
var con = mysql.createPool({
host: "127.0.0.1",
user: "username",
password: "password",
database: "components"
});
app.post("/parts/get_part_info", function(request, response){
console.log(request.body);
con.getConnection(function(err,connection){
if(err) throw err;
var query = `SELECT * FROM parts WHERE id = '${request.body.id}'`;
connection.query(query,function(err, result){
connection.release();
if (err) throw err;
console.log(result[0]);
response.send(result[0]);
});
});
});
app.post("/parts/get_parts_from_bin", function(request, response){
console.log(request.body);
con.getConnection(function(err,connection){
if(err) throw err;
var query = `SELECT id, name FROM parts WHERE belongs_to = '${request.body.bin_id}'`;
if(request.body.detailed !== undefined){
query = `SELECT * FROM parts WHERE belongs_to = '${request.body.bin_id}'`;
}
connection.query(query,function(err, result){
connection.release();
if (err) throw err;
res_json = {rows:result,entries:result.length};
console.log(res_json);
response.send(res_json);
});
});
});
app.post("/parts/get_bin_info", function(request, response){
console.log(request.body);
con.getConnection(function(err,connection){
if(err) throw err;
var query = `SELECT * FROM bins WHERE rfid = '${request.body.rfid}'`;
console.log(query);
connection.query(query,function(err, result){
connection.release();
if (err) throw err;
console.log(result[0]);
response.send(result[0]);
});
});
});
app.get("/parts/get_bins", function(request, response){
con.getConnection(function(err,connection){
if(err) throw err;
var query = "SELECT * FROM bins";
connection.query(query, function(err, result){
connection.release();
if(err) throw err;
//var response_json = {entries_amount:result.length,rows:result}
//console.log(result);
response.send(result);
});
});
});
app.get("/parts/get_parts", function(request, response){
con.getConnection(function(err,connection){
if(err) throw err;
var query = "SELECT * FROM parts";
connection.query(query, function(err, result){
connection.release();
if(err) throw err;
//var response_json = {entries_amount:result.length,rows:result}
//console.log(result);
response.send(result);
});
});
});
app.post("/parts/add_part", function(request, response){
con.getConnection(function(err,connection){
//console.log(request.body);
if(err) throw err;
var body = request.body;
var query = `INSERT INTO parts (name, value, quantity, description, package, \
belongs_to, is_SMD) VALUES ('${body.name}','${body.value}', \
'${body.quantity}','${body.description}','${body.package}','${body.belongs}', \
'${body.is_SMD}')`;
connection.query(query, function(err, result){
connection.release();
if(err) throw err;
});
//response.send_header('Access-Control-Allow-Origin', '*')
response.send("ok");
});
});
app.post("/parts/add_bin", function(request, response){
con.getConnection(function(err,connection){
//console.log(request.body);
if(err) throw err;
var body = request.body;
var query = `INSERT INTO bins (name, rfid, description, type, is_SMD, \
is_mixed) VALUES ('${body.name}','${body.rfid}', \
'${body.description}','${body.type}','${body.is_smd}','${body.is_mixed}')`;
connection.query(query, function(err, result){
connection.release();
if(err) throw err;
});
//response.send_header('Access-Control-Allow-Origin', '*')
response.send("ok");
});
});
app.post("/parts/remove_bin", function(request, response){
con.getConnection(function(err,connection){
//console.log(request.body);
if(err) throw err;
var body = request.body;
var query = `DELETE FROM bins WHERE (id = '${body.bin_id}')`;
connection.query(query, function(err, result){
//connection.release();
if(err) throw err;
});
var query = `DELETE FROM parts WHERE (belongs_to = '${body.bin_id}')`;
connection.query(query, function(err, result){
connection.release();
if(err) throw err;
});
//response.send_header('Access-Control-Allow-Origin', '*')
response.send("ok");
});
});
app.post("/parts/remove_item", function(request, response){
con.getConnection(function(err,connection){
if(err) throw err;
var body = request.body;
var query = `DELETE FROM parts WHERE (id = '${body.item_id}')`;
connection.query(query, function(err,result){
connection.release();
if(err) throw err;
});
response.send("ok");
});
});
app.post("/parts/change_amount", function(request,response){
console.log(request.body);
var amount = request.body.amount;
var inc_dec = request.body.inc_dec;
var item_id = request.body.id;
if(inc_dec === true){
var query = `UPDATE parts SET quantity = quantity + ${amount} WHERE id = ${item_id} and quantity >= 0`;
}
else if(inc_dec === false){
var query = `UPDATE parts SET quantity = quantity - ${amount} WHERE id = ${item_id} and quantity >= 0+${amount}`;
}
console.log(query);
con.getConnection(function(err,connection){
if(err) throw err;
connection.query(query,function(err, result){
connection.release();
if (err) throw err;
console.log(`Successfully changed ${result.changedRows} entries`);
response.send(`Successfully changed ${result.changedRows} entries`);
});
});
});
app.listen(3010);
Webpage HTML
Part Tracker
Part Tracker Main Page
View all parts
×
Name
ID
Value
Quantity
Description
Package
Bin ID
Is SMD?
View all bins
×
Name
ID
RFID UID
Type
Description
Is SMD?
Is mixed?
View parts from bin
×
Remove an item
×
Change the quantity of an item
×
Add a new bin
×
Remove a bin (Dangerous!)
×
Add new items in bulk
×
Add a new item
×
Webpage Javascript
const ip = "ip address here"; //Make sure to change this
function create_parts(bin_sel, value_str, pkg,is_smd,qty){
console.log(bin_sel);
var sel_elem = document.getElementById(bin_sel);
var bin_str = sel_elem.options[sel_elem.selectedIndex].value;
var bin_parsed = JSON.parse(bin_str);
var id = bin_parsed.id;
var type = bin_parsed.type;
var val_array = value_str.split(",");
console.log(val_array);
val_array.forEach(element => {
console.log(element);
var smd_modify = "";
if(is_smd==="1"){
smd_modify = "SMD"
}
var modifier = `${pkg} ${smd_modify}`;
var dataString = {
name: `${modifier} ${element} ${type}`,
value: `${ele
