​It’s been a while since I posted anything here, so I’ve decided to find a topic to motivate me.

This post is the first in a series about my personal finance / budgeting system. I’m creating this for several reasons, one of course is to track my budget, but that’s not the main reason.

I’m building the application with an interesting architecture. I don’t think it’s novel, but it is new to me. This architecture is based off observations of other personal finance applications. The main thing I noticed was that they all seemed to use a large multi-tenanted database, with thousands of customers sharing one store. This required sharding the data in to many databases to scale and required careful design of the scheme to optimise storage.

I wondered if instead of few large stores, many smaller stores would work. This isn’t new, and the arguments against this are well known. Most database servers require maintenance, they connect to networks so need security patches and updates, backups need to be managed and restores tested, and migrations need to be tested and deployed in sync with application code. Additionally database creation and access control are traditionally infrequent tasks performed by DBA vetted scripts.

These are all good arguments, but on modern hardware we do have a fast and safe alternative to full database servers - SQLite.

SQLite is an embedded SQL engine that offers broad sql language coverage, ACID compliance and even multi-user read access (though I won’t use this feature). More importantly it stores data in a single file (or a few files if you use a write-ahead log) making it easy to move and backup. Additionally this would make a phased roll-out possible, with each store being updated on demand.

The best way to see how crazy an idea is to build it, so that’s what I’m doing!

Broadly it will look like this:

WebUI - Broker - API/DB

The broker takes authentication information from the web interface and forwards it to an api server for that user. The broker is also responsible for starting api servers when there isn’t one for a given user. Api servers will shut down after a period of inactivity.

I’m also taking this as a chance to use ASP.NET MVC 6, .NET Core and React, hopefully with server side rendering, and also to write more blog posts. Hopefully linking the two will motivate me to do both more.