How I’m Saving my College £10,000 a Year by Building a Web App During The COVID-19 Lockdown
Here’s a short story on how I was able to build and engineer a fully functioning web app to save hundreds of office hours. This was all done to cure my absolute boredom spawned by the COVID-19 Lockdown in the UK.
From the months of July to September the UK was plunged into lockdown in a desperate attempt to try and tame the global crisis. The micro result for me was having exams canceled, my educational institution closing, and suddenly becoming extremely bored.
A great friend of mine told me how he was planning an ‘’overhaul’ to the digital, visual display boards around the college and that it was his summer project because, like me, he was extremely bored too. The next phrase he said shaped my boredom cure for the next few months, although I can’t remember exactly what it was, it went a bit like this. “The Office Staff spend hours every Friday modifying and updating the Microsoft PowerPoint slides that are displayed around the college, I have been given the task of re-designing these displays over the lockdown”. I thought this was crazy that in twenty-twenty we’re burning hundreds of hours modifying a PowerPoint document that is meant to display simple things such as the college’s calendar events, notices, a weather feed, news, and other essentials such as the current and upcoming song playing on our college’s radio. I was then informed about how my friend was going to try and reduce the hours required. However, I, a 17-year-old entrepreneur, programmer, and complete geek knew there had to be something much simpler.
I found out that the plan was to use excel files to handle the data entry and then another piece of software, VMix, to read in these files and display them in a beautiful fashion across the college’s campus. I was to control the data entry and my friend was to sort the ‘beautiful fashion’ part. Let's call this, ‘Project Update’. My plan here was to build a secure, versatile, functional, and most importantly, a reliable flask-powered web app to handle all of this.
“Flask is a micro web framework written in Python. It is classified as a microframework because it does not require particular tools or libraries. It has no database abstraction layer, form validation, or any other components where pre-existing third-party libraries provide common functions. However, Flask supports extensions that can add application features as if they were implemented in Flask itself. Extensions exist for object-relational mappers, form validation, upload handling, various open authentication technologies, and several common framework related tools.
Applications that use the Flask framework include Pinterest and LinkedIn.”
I had only used Flask a handful of times before, however, paired with a few libraries like OpenPyxl for data manipulation, a bit of security using werkzeug security, and some web scraping using requests I knew I just had a powerful combo with a hint of a learning curve.
The web app was simple and its stack consisted of Bootstrap, HTML, CSS & JavaScript as the front end with Jinja2 as the templating engine and SQL (for the Database) & Python (Flask) for the backend.
The design was simple, it needed to have multi-user support with a tiered user system. Each user should be able to access different sections of the web app depending on their ‘role’. Furthermore, the app should also have thorough security with hashed password values and a secure database. Furthermore, it should have a failover to catch any errors to ensure as much reliability as possible.
I started prototyping lots of ideas and once I got the basic excel manipulation functioning I was able to replicate lots of code to save lots of time. The front-end acted as a ‘pretty’ interface to be able to modify and manipulate the excel sheets that were then read into the “vMix” software. This also allowed me to be able to write multiple modules of ‘Project Update’ that were able to control different functions. For example, the weather module is a separate file of Python code that executes simultaneously, connecting to the OpenWeather API to get real-time data. This file of code then organised the incoming data, processed it, then finally exported it into an excel file to be read in later on. This process happens every 5 minutes, gaining new information and updating the files. Although not perfect the ability to use excel files makes it very easy for anyone non-technical to diagnose issues or change data. This was all part of the ‘reliability’ plan.
What is vMix?
vMix is a software vision mixer available for the Windows operating system. The software is developed by StudioCoast PTY LTD. Like most vision mixing software, it allows users to switch inputs, mix audio, record outputs, and live stream cameras, videos files, audio, and more, in resolutions of up to 4K.
Within a couple of weeks, I had a fully working model up and running of ‘Project Update’. The user tiers depended on the role of the office staff, for example, ‘Regular’ office staff were assigned the database role ‘regular’, this means that these users are only able to update the notices and edit the calendar events and aren’t able to edit the ‘radio schedule’ and create new users for example. I explore this concept later on. The idea for the roles system came from using the popular Slack-like chat platform, Discord.
Discord is an American VoIP, instant messaging and digital distribution platform designed for creating communities. Users communicate with voice calls, video calls, text messaging, media and files in private chats or as part of communities called “servers.” Servers are a collection of persistent chat rooms and voice chat channels. Discord runs on Windows, macOS, Android, iOS, Linux, and in web browsers. As of July 21, 2019, there are over 250 million users of the software.
Having ‘roles’ allowed me to write simple code to ‘disallow’ access to certain parts of the web app based on the role each user had. It was pretty robust and worked well for ‘Project Update’ however it would have to be tweaked and investigated more if I were to use it in a larger scale web app.
Here's a tiny snippet of how I would initialise the routing for the web app. If you have used flask before this will be really familiar.
Note the methods used, POST & GET, this is due to the HTML page containing form data that needs to be sent to the server and processed. Secondly, note the @login_required. This ensures that an authenticated cookie is stored in the browser to make sure the user has some sort of authorisation. If no cookie is found then the user is directed to the login page. Please note that my institutions' logo is positioned above the “Dynamic Update” logo, but I have removed it for this visual example.
A quick look at the HTML will show how the ‘roles’ work in terms of Jinja2.
Note the “current_user.role” logic. This was used here to enable different users to see different parts of static content within the app. Here a look at how this works on the backend — it’s basically the same principle.
This shows the authentication route and specifically the path for generating new users. Note the current role check. If the currently authenticated user isn’t an “Administrator” they simply get kicked back to the login page. I am not saying this is the best and most secure way to handle this however in this case it works perfectly.
Lastly, I want to shed a bit of light on what the output looks like. All data that is to be displayed gets placed into an Excel file. Although not necessarily super-efficient or very pretty, it works and for this case, that's all that's needed. The example shown here is of the weather data. The first example is the map that was created of where the data had to be store. The second example is of actual live data.
Although this project is nowhere near complete, a start has definitely been made and many hours have been saved as a result. There are lots of other sections of the web app such as ‘password reset flows’ and an ‘admin dashboard’ that I created to check the status of all the modules, however, I will explore these in the future.
