For book keeping of my freelance activities I've been using a home made web app since many years. I could have kept using it as it's working fine, but as my activities evolved slightly January was the occasion to take a few days to rewrite a new one reflecting these changes and start afresh a new year. In this article I'll talk about how I've implemented it, and of course share the code for anyone willing to use it.
My previous app was using my old WebBuilder framework and stored data in a MySQL database. Nowadays I'm more and more prone to use SQLite for its simplicity of use, and because it's available in C, PHP and many other languages which cover all my needs. This also allows me to share data easily between my projects. Then I decided to move to that database engine for my new book keeping app.
SQLite databases are single files, conveniently created automatically by the SQLite engine. It means there is nothing to configure or setup, everything can be automated programmatically. Pushing in this direction, I wished to try to make the app as one single file, without dependencies, whose installation procedure reduced to copying that single file on a PHP server. My previous apps ReflectanceEditor or ColorBlind are also built that way. However the book keeping web app is more complex and I needed to pack all the PHP, HTML, CSS, Javascript into one file, which was a bit of a concern but I gave it a try anyway. Of course it also forbids external assets like images but I'm fine with a simplistic UI.
Compare to years when I was working as a photographer in addition to software developer, with stocks to manage, several providers and customers, my current activities focus only on programming and have become incredibly simple from the accounting point of view. Taking advantage of it, plus limiting functionalities to the strict minimum, I could code it in around 700 lines (without comments), which seems to me reasonable. I'm not particularly demanding regarding to the user experience, and the app as it is now would certainly not fit an intensive business. Nevertheless, as a test I could smoothly and swiftly reproduce all my accounting for the past year and prepare this year tax declaration. Thus it's definitely good enough for some real use cases at least, and there is still room left for improvements before that single file becomes an unreadable monster.
I consider the "just copy one file" goal as a success despite two issues. First, as I said the database is automatically created by the PHP script. This requires the folder in which the app is installed to be writable by the account running the app. On a public web server that should be the case and you should have nothing to do here. However the server configuration may also be set up differently, in which case you'll have to edit permissions manually. On my rental web server and the LAMP stack of my Manjaro machine I had nothing to do. On the LAMP stack of my Fedora machine I had to fight with the permissions.
Second, the app is protected by a password, its API by a token system and the database filename is obfuscated. Contrary to the first two points which do not require any manual setup, I can't think of a way to automate the obfuscation. If you have an idea, let me know! Instead I've choosen to require the manual setting of a variable in the PHP script before uploading it to the server. So, not 100% "just copy one file", but near enough!
The file has four sections: the PHP script, the CSS section, the HTML section, and the Javascript script. The PHP script implements the API, the interaction with the database, and controls the creation of the DOM. The CSS defines the style of all the DOM elements. The HTML section contains the static DOM. Finally, the Javascript script responds to the user interaction with the app, processes it via HTTP requests to the API, and updates the DOM as necessary.
The PHP script contains one class which is instanciated globally at the end of the PHP section.
The class instanciation consists of initialising some properties, checking that the obfuscation parameter has been set, and opening the connection to the database. That connection is closed upon destruction of the instance. Properties are: login and token, obfuscated path to the database, operating mode (login or app), and the description of the database model.
Right after the instanciation, the script checks if it responds to an API request, indicated by the presence of a POST variable. If that's the case, it processes the API request and stops here to avoid the following sections to pollute the API response. Else, it executes the main function to run the app.
The main function ensures the database is ready to use, then check if the user is logged in to set the operating mode that will control the construction of the static DOM in the HTML section.
On the first connection an empty database will have been automatically created during class instanciation. Checking the database simply consists of looping through the database definition and creating missing tables. This becomes useless from then on, but doesn't concern me much: the model is super small and the page load, and so the useless database checks, only occurs when the user accesses the app and login.
The CheckLogin method has three roles. It saves the login information if it's the first time the app is used. It checks if the login information matches the info in the database if it's not the first time the app is used. And it prepares the token and expiration date for secure communication with the API. In the code below the expiration date is set to one hour after the login. The shorter the more secure, but the less convenient (the user has login again).
Here I use a single user model, as it simplifies greatly the app. For multi-users, first an additional user creation feature is necessary and it must ensure that the user creation is legit, and data in the database must be managed per user instead of globally. In my implementation, the user is supposed to login soon after the app is installed. In the improbable event of a malicious user login in before the legit user, the database would be necessarily empty and the legit user would notice he/she can't login (except if the malicious user has initialised the login info with those the legit user intended to use, but ...).
The API method checks the token to validate the request, then route the request toward the appropriate method.
The token verification consists of comparing the token sent with the request and the token currently recorded in the database, and checking that the current date is before the expiration date.
The other methods used to process the API requests are just interfaces to the respective SELECT, INSERT, DELETE and UPDATE SQL commands, and return in JSON format success/failure and necessary information for the Javascript to update the DOM. For tables 'Category' and 'Source', which are identicals in structure, I commonalised the code, while the commands for the 'Transact' table are implemented in their own methods. I only show the add/remove method for Category/Source below, check the whole code at the end for the other methods.
Before removing a category or source I manually check that it isn't used by some transactions. It could be simplified by using foreign key constraints in the database model definition. However the support for foreign key isn't guaranteed (it's available only from version 3.6.19 and can be disable). So I leave it like that. See this link for more information.
The CSS section has nothing noticeable. It's my trademark 'grey, yellow, blue' usual theme. The HTML section has three parts: a common one containing a simple title and footer, and two others containing the static DOM for the login page and the app main, and single, page. Which of the last two is used is controlled by the PHP script via conditional HTML.
The login page looks like this:
and the app page like this:
The tile on the left allows to add new record, add/delete categories and sources, filter the displayed transactions, and show the total debit/credit/balance for currently displayed transactions. The tile on the right displays the transactions and allows to edit them. There is no explicit logout, the session is active as long as the user uses the app, or until the token expires.
Finally, the Javascript script contains one class instanciated globally on the window.onload event.
That event also takes care of setting the handlers and initialising displayed values in the DOM in case the user has logged in.
The Javascript class has only two properties: the login and token to execute API requests. These properties are set by the PHP script.
Its role is to send HTTP requests to the API according to the user actions and update the DOM according to the API response in JSON format. Beside the main method managing the request, it has some helper methods: to add elements to the DOM, convert data sent to the API from dictionary to form element, and display the 3 most recent messages from the API.
The method sending the HTTP requests appends the token information to the request data, sends and waits for the response, and takes care of failures or executes the handler to process successful request.
Other methods then simply call PostRequest with appropriate data from the DOM and appropriate handler for the response. For example, the method for the deletion of a transaction is as follow:
In conclusion, this little app rewriting was a useful occasion to refresh my memories about PHP/HTML/SQL/CSS/Javascript, which I don't touch that often nowadays. It took me less than 20 hours, the majority spent searching in the docs about forgotten syntax. No doubt that a sharper mind could have done it in less than a day. I consider the 'monolithic' challenge a success, and I'm definitely willing to reuse this app as a template for some others. The app's code is available here.
Edit on 2023/09/22: Add a link to download the database.