How I went a little crazy and coded a fully functional Accounting Software without prior coding experience

Prototype Accounting Software named after ‘Autocount’

Live site (Username: protocount, Password: password)

In this post, I’ll share how I created a prototype fully functional web-based Accounting Software by myself. A brief introduction before I start, I’m a 37 year old trained auditor/accountant and have not done any form of coding before. Given the rapid digitalization taking place in the workplace (and every other aspects of our life for that matter), I’ve become concerned that I know next to nothing about tech other than using Microsoft Excel. Hence I’ve taken a leap of faith and started self learning on my way to (hopefully) become a Software Developer. Most of my learning are done using the learning route map provided by freeCodeCamp and various other free sources online.

This is also my first attempt at writing a blog and what better time to do it than after having spent the better part of the past 3 months painstakingly building a full stack app on a whim. I thought I’ll give it a go after reading many blogs about other people’s personal journey in their quest to becoming software developers. Now that I have decided to write it, i felt that its also a good learning process as I go through again and document why I did what I did.

Table of contents

• Motivation
• Challenges
  - Overall Approach and Design of App
  - Technologies/Framework
• Other Challenges
  - Cookies and User Verification
• To Sum Up
• Wishlist & FINALLY

MOTIVATION

To do it or not to do it?!

Building a full stack accounting software wasn’t part of a 20 point plan that I had — not that I had a grand 20 point plan to begin with — in my self learn journey to become a software developer. I just thought hey, after going through the painful process of learning some NodeJS, ExpressJs, reading a whole actual textbook on SQL (DATABASE SYSTEMS Carlos Coronel | Steven Morris Design, Implementation, and Management 12th edition) AND doing the 300 exercises in the textbook, why not go NUTS and build something cool? I had some experience using a off the shelf accounting program in my previous job as an accountant, why not build one?

I could clone an e-commerce website which I think would be easier and probably more suited for my level (correct me if I’m wrong) using the MERN (MongoDB/ExpressJS/ReactJS/NodeJS) stack. However since I have already gone through a 1000 page textbook on relational database management system design which is perfect for an accounting system, why not strive for something higher?

Uh oh

Looking back now, on many occasions during the development, I honestly thought that I was way out of my depth as I have only started to self learn over a year and half ago. I underestimated the amount of time and effort it would take to build even a bare-bone skeletal enterprise level app even with a readily available off the shelf accounting program as a reference.

However I gained a ton of experience and in the process learnt many new skills/frameworks and tools like:
1) React Router JS framework for client side routing
2) Relational Database modelling and design
3) cookies for user verification(cookie-parser, cookie-signature, keygrip modules from npm)
4) Heroku deployment and hosting platform
5) Git (including branching) and GitHub * checkout my repositories
6) Build tools like Create React App (including basic understanding of how Webpack works which hopefully i don't have to ever fully learn and configure in the future!)
7) nodejs-mysql module which is the driver to connect MySQL database to nodejs (also tried to deep dive into the source code how it attempts to do so using TCP server and difference to HTTP Server in Nodejs).
8) SQL injections and SQL prepared statements

In addition to the above more importantly I reinforced with mindless repetition and practice on things like git commands, React states, composition and hooks, SQL queries (data definition statements, data admin like backups, prepared statements, DB data-flow statements IF-ELSE,REPEAT), small things like various Array methods to manipulate data structures, tables, forms, HTML, HTTP and HTTPS and many more. I guess it’s the closest experience to what's it like working in an actual software company (There’s a high chance that I’m wrong on this!) although without the deadline pressures and stress.

I also think my user interface (UI) and user experience (UX) skills got some much needed workout using React and Bootstrap and most of I have learnt from https://www.freecodecamp.org/ for the past year was fruitfully put into practice. True to the old cliche saying, the only way to learn is by DOING!

CHALLENGES

Overall Approach and Design of App

The whole thing is a very iterative process. I had an off the shelf desktop based accounting program that ran on Microsoft SQL express database which I could refer to and it saved me a lot of time for app functionality research. I just needed to somehow ‘clone’ it and demonstrate that I could create something like that using Web technologies instead.

I did some research initially on each major area of the App and how the tools and technologies would fit and whether it WOULD actually work. ie user verification using cookies, testing out nodeJS-mysql driver on whether it would work for the latest version of MySQL. My tech stack consist of:

Front-End

• React, React-Router (both using Create-React-App development tool)
• Bootstrap (framework for CSS)

Back-End & Database

• NodeJS (JS runtime web server)
• ExpressJS (lightweight framework for NodeJS server)
• NodeJS-MySQL driver (connector for database)
• MySQL (community version database)

Off the shelf Accounting Software which I based my app on

My initial plan was to create a Minimum Viable Product (MVP)-like version of Autocount (a commercial off the shelf Accounting Software system) with the core functionalities and aimed to complete it in a couple of weeks time. Maybe a front page with 6 to 8 Bootstrap Card containers with navigational links to some basic accounting function input forms like Debtor, Sales Invoice & Debit Note (and equivalent Creditor items) populated with some real life company data for users to tinker with.

Bootstrap 4 cards

I thought its similar to building a website with forms with some database activity attached at the end, easy! Then I quickly realized that once you have Sales Invoice, you would definitely expect to be able to record some form of receipt from customers (bank or cash). You would then want to know how much each Sales Invoice has already been paid by which Customer using which Customer Receipt on which day and what’s the easiest way to view it UI wise. Then you want to know how much each customer owes you at a certain point in time and the aging brackets. Then what would really be helpful is to have a Stock System alongside those features above. The list could go on and on! Few weeks became 3 months.

Holy cow, what have I gotten myself into!

I wouldn’t dare call it an Accounting App without all these core features. Every additional feature have an impact on other areas of the app hence the layer of complexity increases exponentially on both the backend and frontend as more features are added. I had to make a conscious effort to decide which bundle of features to add with the limited time I have or risk not finishing the App at all. My ultimate objective is to demonstrate my understanding of software development concepts and doing too much or too little will defeat that purpose. During the initial stages, some features forced me to scrap the entire app’s Front End and redo it from scratch!

And those are the problems on top of HOW TO technically translate it all into a user friendly App and particularly how to design the database model to reflect the app features. The database design is central to the entire App and if it doesn’t work none of my eventual front end and back end server code matters. How and what the user sees the data in the app is completely different to how the data is structured within the database.

Full MySQL Schema!

Dilemmas & dilemmas

For example cash and bank payment, do i create a different table for the 2 similar yet distinct functionality? Putting them in same table would create redundant data however creating different tables seems like more work for little benefit. I eventually went for different tables which proved to be a right decision. In order for the Stock System to work, do I create a Stock Qty column in the Stock Item table which is foreign key linked to another table (i.e. Delivery Order table) and is updated every time a transaction happens or do I create a M:M (Many to Many) relationship table between Stock Item table and Delivery Order table that records stock qty on each transaction?

List of Stored Procedures in MySQL

For a transactional database design to work, do I create all my SQL queries in stored procedures within the DBMS and if that’s the case, how do I code my stored procedures to include error signaling if one of the transactions failed?
The answer to all these are not apparent until much later in the development of my App where I iteratively learn and refine my coding techniques on what works and what doesn’t.

Technologies/Framework

ReactJS

ReactJS — very popular front-end library

Naturally I chose React for Front End not because I thought it was better (not that I would know!) than other frameworks but mainly because everyone’s talking about it and with the added benefit that I’ve already learnt it from freeCodeCamp. I’ve only read about why its so popular but never truly understood what it all meant until when I started using it extensively for this Web App. I think React’s declarative and reusable component feature is very powerful and especially important for a large program. It enables(and somewhat forces) you to structure different parts of your program to be modular, maintainable and extensible.

For example, by thinking along reusable component lines, I split my Accounting App functions into 3 main segments:

1) Process Component

2) Item Component

3) Report component

1)Process components (find,filter,select,delete function for each Item components),
2) Item components (ie Sales Invoice item, Stock item, Bank Receipt Item)
3) Reports components (ie Sales Analysis Report, Stock Card Report)

Each component represents a different functionality of the Accounting App by itself. All these components share significant common components within their segment and any additional features unique to that function/component are added on its own component (i.e. for Sales Invoice form, the input states and basic Create, Read, Update, Delete CRUD fetch request are shared with other forms using a common “Item” component). As a result it’s really easy for me to update and add feature to my app.

React forced me to refactored my components many times to make it even more modular and reusable as I have discovered that changing a simple code/feature across 10 components with similar functionalities is a recipe for disaster due to errors and bugs(which I can spend hours to fix)

React Router

React Router

For React-Router, I stumbled across it by chance as I was first figuring out how to route my app functionalities through ExpressJS routes. The horror of it when I thought that I have to create a different bundle of HTML, CSS and JS file which will be served by the ExpressJS server for every different URL route. My first thoughts were:

1. How am I going to do that in development environment with Create-React-App? Have a different development instance set up for each route? Have a different index.js entry file for each bundle (with different folders) and npm run build every time? Can I even share common components? How is it going to work for maintainability? This is going to significantly increase my development time and I’m not even sure it works this way.
2. Also even IF I manage to resolve the above, how can I configure my routes to serve tailored pages based on user information sent in GET and POST? (ie serve a pre-populated Sales Invoice form based on document ID selected from another part of the App). This doesn’t seem possible without using some form of server-rendered App template like ejs from ExpressJS. Is there a React based server rendered template? I don't know of any except for Next.js but that is a different beast altogether. I definitely wouldn’t want 2 different types of frontend development framework for my app. Also this is not good for user experience either as pages have to be reloaded every time information is sent.
3. Not to mention the cumbersome and slow process (in this day and age) of performing a HTTP request every time when navigating to a different section of App. The microsecond delay is still substantial and noticeable during the page loading process especially so for a Web App that needs constant navigation between pages to function.

*CAVEAT- I didn't look into resolving the problem (especially 1) above as i have found the solution that worked for me as explained below. Also surprisingly there’s little resources on the web about these issues. Hence I might be wrong on how traditional routing works on other web apps and how they overcome the problems (1 and 2) described above. I still think its mind boggling to have a different set of files for each URL route and the effort it takes to develop and maintain them. Is this how it was done for PHP Apache servers? If anyone can kindly correct me that I’m completely wrong about how webpage development works in the traditional sense please let me know:)

Long story short on how client-side routing (specifically React Router) works, it attaches event listeners to your browsers URL and every time your browser URL changes (the URL address is a state itself) from a link <anchor> click to navigate to different section of App, instead of making HTTP request to server, it performs ‘routing’ internally by rendering different React components ON THE CLIENT SIDE from your JS files and hence it ‘looks’ like a different page was served but actually the page did not reload and the components just re-rendered in the background (anyone familiar with React would know the re-rendering process of React components once state changes-in this case the URL). Hence in order for this to work, the ENTIRE Accounting App along with its requisite file index.html, CSS and JS files needs to be served together to the user when the initial request to the App’s homepage URL is made.

Essentially this solved problem (1) for me since React Router is just React components and I can then develop the whole App together within one instance of Create-React-App with its many components/’internal’ routes. Also it solves problem (3) since very little to no page reloading takes place due to client-side routing, the App is blazing fast and excellent for UI purpose. It also frees up server resources significantly to respond to the request/response and the intensive nature of database querying of the Accounting App. To remedy item (2), I decided that all HTTP requests within the App are made using AJAX (Fetch API) and page is tailored/re-rendered by React when information is fetched hence negating the need to perform any processing on server side.

All-in-all it seems to me that using client-side routing together with AJAX really is a major paradigm shift and I can’t imagine any App not using it in the past. (*but my caveat applies as I did not look into how to overcome the issues as explained above and I might be wrong on many times). Also this method embodies the definition of Single Page Application (SPA) which I think is really an improvement in terms of App speed and ease of development over traditional web pages using server rendered application.

Bootstrap

Trusty old bootstrap

Trusty reliable bootstrap. I cant remember a time where I did not use Bootstrap. Like what most people say, its especially useful when you want to create a quick prototype and using Bootstrap lets you skip the boilerplate css codes which every website needs(layout, tables). I think the only major difference between version 3 and 4 is that version 4 is rewritten with flexbox. However that’s mostly under the hood and shouldn’t be too much difference in usage and appearance.

Only thing I would like to mention with Bootstrap is that I was able to use the bootstrap CSS style sheets and Javascript (for use in Bootstrap features like Collapse) directly with React without using a React rebuilt version like React-Bootstrap module. Its workable and I only have to make sure that they both don’t clash trying to control the DOM at the same time.

OTHER CHALLENGES

Cookies and User Verification

I felt that this was an important area and if I could grasp the underlying concepts it will be invaluable in any future App development. I sort of understood that user verification is separate from cookies although they are mostly used together in most Websites with login authentication.

User authentication is pretty straightforward where I created a table in MySQL storing all username and passwords. Once the App is served during initial HTTP request, the Login Page component is always rendered first (unless they have valid cookies — see below). Login credentials inputted by user are then sent back to server using fetch API (using POST request) and then checked against the table using MySQL queries. If correct, a JSON object is sent back to with property ‘auth:true’ and the App’s top level parent component would change its ‘Auth’ state to true and App would re-render giving user access to all of the App’s functionality.

As I understood it, the cookie’s main function is to provide enhance UX to user. Once user verification takes place, a cookie is sent by server to user’s browser with necessary information which allows user to continuously access the App until the cookie is revoked or the session ends (browser closes). This negates the need for user authentication to take place again every time a secure interaction takes place between browser and server. The way HTTP request and browsers work is that they don’t retain any information or state about the website that it visited (in this case, whether user authentication has already taken place) and hence cookies are invaluable to enhance user experience.

If a malicious user can somehow get a copy of the true user’s cookie after the true user has performed the necessary verification, he or she can use it for various purpose like breaking into the App and accessing database data hence I felt that the issue of cookie security is important.

I tested cookie behaviors across Chrome and Firefox and implemented the following (using cookies module):

1. set cookie headers with HttpOnly.
2. I placed a cookie verification middleware in every ExpressJS route that responds to an AJAX request verifying whether cookie exist and is valid. Otherwise user is redirected to Login page again.
3. Cookies are signed(encrypted) with rotating keygrip module to avoid tampering between client(user) and server communication.
4. Cookies are set to session (once browser closes cookies are gone).

I think the most important feature is (1) where if cookie header is set with ‘HttpOnly’, only the server can send the cookie and the client’s browser won’t allow any form of cookie access and manipulation on clients side (ie document.cookie). I don’t think it’s possible to access the cookie files for copy and paste too. Probably the only way to check it is through Chrome’s developer’s tool (and I’m pretty sure you can’t Ctrl+C from there too). To get around it, Somebody would have to manually copy (handwritten maybe?) the cookie information word by word sitting right next to the user. However they still won’t be able to access my app since there’s no way to manually alter or create cookies from the client’s side using javascript and send it to the server. So I think it’s secure enough (but again I might be wrong as this is my first experience with browser security). Also for additional security, ‘Secure’ usually is used alongside ‘HttpOnly’ and this means if https is not used, cookie is not sent and hence in this case all App features can’t be accessed. However i didn’t use it because my Heroku App doesn’t have https.

For no (2), a malicious user might be able to access the AJAX route directly using a HTML form template he created himself with POST and if he knows the right parameters he can access the database in my app. Hence i think no (2) is imperative to prevent this method of unauthorised access.

For more security, i could even put a persistent cookie with time limit (cookie will expire within a certain time limit) but i think session cookies are sufficient for my app purpose.

These are my first attempt and very rudimentary understanding of how a user authentication system works, anyone that can point out gaps please let me know thanks:)

TO SUM UP

Overall I’ll have to say that I’m satisfied with the App in the sense that I managed to finished what I set out to do and I got some feel on what it’s like to build and push to production something from the absolute zero. I think that even though the app is feature light, it has all the core elements of an Accounting Software especially on the database design and the App could be easily extended without much impact on the existing features and code. I tried to keep to best practices by writing clean, modular, scalable and maintainable codes but I feel that there is still much to learn and improve on.

I could easily spend more time adding more features like more sophisticated reports or even use D3.js for charts and graphs for better UX however I felt that it wouldnt be beneficial for my overall development as there’s plenty more stuff I have yet to learn and my time is better spent on them. My objective of demonstrating my current level of skills and knowledge is met I think.

WISHLIST

Next on my wish list of things to learn is definitely unit testing/automatic testing which I didn’t manage to learn for the purpose of developing this App. I’m not exactly sure what it is but towards the end I felt that I was spending a lot of time manually testing each functionality as a user EVERYTIME I made some code changes in the fervent hope that bugs didn’t appear in other parts of the App. Hence I hope that there’s a better way to address this problem and speed up development. Also Redux (state management) seems like something I can look through again although I didn’t feel that my states became unmanageable throughout the process.

FINALLY

If you have read this far, hopefully it means that my post either wasn’t a whole load of gibberish or a total bore. I appreciate the time you have taken to read my post and if you haven’t yet please visit protocount.herokuapp.com and tinker around with the app :). Also please visit my portfolio age at https://mseong.my. Feel free to comment and provide feedbacks!