365 Days of Code - Day 004

I installed a new MariaDB (opens in a new tab) container in my local server, to support local databases. Primarily, I wanted to install Monica (opens in a new tab) in a container, and required a database to set up Monica. I ran into some headaches when I could not log in as the root user into my new database container. Turns out, I had previously installed MariaDB, and had a persistent store with data already in it. I had to delete the persistent store and start fresh. Once that was done, the issue was easily resolved and I spun up the new Monica instance.

With Monica installed locally, I could evaluate their 4.x version, as well as their 5.x version that is hosted, code name Chandler. As an aside, I like their creativity in naming their app with characters from the show Friends. The primary issue I have with 4.x, is that everything requires manual entry. There isn’t a lot of features. It is basically just a contact database with some extra fields for logging calls and notes. Their 5.x version is more robust, and all the fields are editable. I appreciate their underlying design choices, but the UI is terrible. Technically, either version can work for my basic usage. Keeping information on my personal contacts. However, in addition to my problems with the apps themselves, they haven’t been getting updated lately. 4.x hasn’t had updates in a couple years, and 5.x hasn’t been updated in over six months. There are many issues and pull requests have not been attended to. I understand it is just two developers side project, but they have paying customers on their hosted application. I think the PRM space is ripe for a competing application. They have millions of downloads, and tens of thousands of users per their own statistics. There is a market for this type of application.

A PRM is inherently a very personal and private software. Security should be at the forefront, as any data leakage could not only leak a user’s data, but all the data that user has kept on people close to them. A database dump could be catastrophic not only for the app developer, but for the users and the user’s family and friends. The security implications of the application are my biggest mental hurdle to overcome when trying to develop this application. Therefore, my idea is to create an alpha build version, that is self-hostable only. Let’s see if I can make something that is interesting and gains a small following of users that like the application before I try to implement the necessary security measures for taking it public.

In coming up with the initial design for the alpha build, I came across discussions on managing the primary keys for the database. There are several approaches such as auto-incrementing integers, which is the standard solution, or choosing one of the variants of UUIDs (opens in a new tab). There is no perfect solution, as every one of these options carries several caveats. Choosing a database primary key used to be a simple default to auto-incrementing Integers, but in a modern, distributed world, that simplicity causes massive headaches for offline synchronization and privacy. While UUIDv4 solved the collision issue for mobile apps, its complete randomness fragments database indexes, killing performance at scale. This has led to the rise of UUIDv7 and ULID as the new gold standards. Both provide the global uniqueness of a UUID but include a timestamp component that makes them lexicographically sortable. This hybrid approach gives you the best of all worlds: the speed of an integer, the portability of a UUID, and a database that stays performant as it grows. However, there are privacy concerns with both UUIDv7 and ULID, since a timestamp is encoded in string. This is probably not an issue for the PRM. However, using integers only is not a solution either, as I intend to make the PRM data available through mobile devices, which require randomness in the records.

The time spent researching the database primary key value is a common problem when it comes to developing applications, at least in my little world. I get caught up in details. Call it what you want, either analysis paralysis or premature optimization, it is so easy to derail a project due to this type of problem. I’m evaluating a database performance problem (slow indexing) for a user base that has millions of people, and I haven’t even written a single line of code yet. Code is soft. It is “software”. It is meant to be changed, updated, optimized. When it is actually time to do so. Let’s change that mentality. Move fast, break stuff. I should either choose the easy way, integers, or UUIDv4, and just stick with that choice until it becomes necessary to change it.