Food Truck Inventory Management System

Inspiration

This project was done for my database class to demonstrate our knowledge of SQL. My team and I decided to make a inventory management system (IMS) for a fleet of food trucks. I came up with the idea from reading about the difficulty in running a food truck. One of the challenges was keeping track of inventory, because unlike a typical brick-and-mortar restaurant, food trucks usually have inventory on the truck and at a warehouse. I wanted to make an IMS that would easily let fleet owners see the inventory on each truck across their entire fleet.

The IMS was made as a web application, using Python with Flask on the backend and HTML/CSS and some JavaScript on the frontend.

My Contributions

My main contributions were leading the design of the database schema (a blueprint of our database) and building the backend of the application.

Schema Design

Above is the ERD diagram made for the database. I lead the design of this and touched on nearly all of it, though most of my contributions were to the entities on the left. These collectively represent a Meal. At the bottom we have a Ingredient entity which has a many:many relationship with the Meal entity that is facilitated by the MealIngredient entity. This means several meals can have the same ingredient. The entity MealType is a domain table used to specify whether a meal is vegan, kosher, halal, etc.

On the right hand side we have several entities collectively representing a Truck. The address is represented by the Address entity and the phone number is represented by the PhoneInfo entity.

In the middle, we have the Inventory entity facilitating a many:many relationship between the Truck and Meal entities. This allows many trucks to carry the same meal.

Building the Backend

The backend was written in Python, using the Flask web framework with the socketio extension for Websocket support. I chose this framework mostly because I have previous experience with it, but also because it's simple, lightweight, and powerful as it comes with a lot of features.

The way the application mainly works is via HTTP requests. For example, the user may click a button (check the image below on the left/top) to look at the info of a truck (location, name, etc.). The button would have a URL that's dynamically generated, capturing the information needed by the backend. For example, if clicked the "Truck1" button, the URL would be FoodTruckIMS/Truck1. The backend of the application has an event handler for URL's in that form that extracts the name of the truck and queries the database for the information. It then inserts this information into a HTML template, which is then sent back to the user to be displayed in their browser (the image below on the right/bottom).

A WebSocket was used on the meal page, which displays the ingredients of a meal and its current inventory. You can update the inventory by entering a new value, or using the buttons on the sides. This utilized the Websocket to update the inventory in real-time, which can be seen if you had another client looking at the same meal.

Challenges

One challenge was preventing SQL injection attacks. While I understood what they were, how they are executed and how to prevent them, it was hard to actually get the countermeasures working. The database I used, PosgreSQL, let you to prepare statements and would supposedly sanitize your input when you executed them, but it never worked for me. I tried to prepare statements using the Python API I was using for interacting with the database, psycopg2, but this didn't work either.

I'm not sure if this was an error on my part or a broken API, so what I eventually did was write my own code to sanitize inputs. It would search the string for anything malicious and return a boolean value indicating whether the input was safe, which worked well.

Another challenge was that one of my team members was a business student and had little experience with coding. Sometimes they would suggest a feature that with their limited experience seemed relatively feasible, when it actually wasn't. Being able to explain why it wasn't rather than just saying so was difficult at times, either due to their lack of technical knowledge or my struggle to explain clearly. However, this person also brought up ideas that I with my background would have never considered, some of which ended up in the final product.

What I Learned

This project solidified my understanding of SQL, from crafting queries to designing my own database. I learned of some of the security issues when building an application, such as injection attacks, and how they can be prevented. I also learned some more about frontend and how you could use a templating engine like Jinja to create dynamic HTML pages. Overall, this project was a lot more interesting than I expected and re-ignited an interest in backend development.

See the Code

Here is the source code.