While researching a curious caching bug, I got inspired to take another look at how Actual stores data locally on the web. There's some history I need to explain. Years ago, Actual was only going to be a desktop app. That means all of your data is stored locally. No server.

Then I realized how important mobile is, and that most people don't want to worry about losing their data if they drop their computer in the ocean. A syncing engine was born, and desktop and mobile apps have happily synced their data ever since. A copy is kept on a server so users can login and easily view their data, and if they worry about privacy they can enable end-to-end encryption.

In the last year I grew jealous of web apps. Look at how easily they can deploy… how quickly they can drop users right into the app. No install required. Here I am asking users to download an 80MB file just to run the app. That download absolutely kills conversion rates, and makes the login flow, support, a/b testing and everything much harder.

I love desktop apps because you have access to much better tech (like native sqlite3), the app is super fast (no network calls), and the user owns their data. However, I can't ignore that the benefits of the web dwarfs these advantages.

I started thinking about a web version of Actual. After some hacking, I got it working without changing the architecture. That means all your data is still stored locally in the browser, and there are no network calls. It's a completely 100% "local" app in the browser [0].

I haven't marketed the web version much because it hasn't been tested enough, and it needs improvements like lazy loading code to make it load faster. The biggest thing I'm worried about is the data storage layer. Since all your data is local, if something goes wrong there you could potentially lose data. And we're really stressing the browser's persistant db by storing everything in it.

To be clear: we are not deprecating the desktop version. However in the future the web version will be the primary platform, with the choice to download the desktop version if desired.

The way it works a bit unusual. Here's a high-level overview:

  • Actual uses sqlite3. This is a hard requirement. The app runs tons of complex SQL queries to aggregate financial data and it's so good at doing it. Queries are easy to express and run super fast.
  • On desktop and mobile, native sqlite3 is used. The web does not support sqlite3, however. To get around this, Actual uses a wasm version of sqlite3 and creates an in-memory db.
  • The obvious problem is persistence. When you make changes, we need to persist them somewhere so when the user reloads they don't lose their data. Luckily we are using state-based CRDT's and all updates come through as a list of "messages". If you are online, these messages get synced to our server so when you reload, all your data should sync up.
  • It's not ideal to require a big sync every time you open the app though. Also, if you are offline there shouldn't be any chance of losing data. To solve this, Actual persists each message into IndexedDB. When the app opens, it applies all messages from the local IndexedDB to get up-to-date.
  • It's still not ideal to require applying any messages on load. It won't scale - if you use the app for months you'll accumulate tens of thousands of messages. IndexedDB would grow indefinitely and loading the app would get slower. To solve this, when the stored messages crosses a threshold it flushes the entire sqlite3 db to IndexedDB and clears all the messages.
  • That means both a binary representation of the sqlite3 db and a list of messages is persisted in IndexedDB. On load, the app creates the in-memory sqlite3 db from the snapshot and applies any remaining messages from IDB.

Turns out this is similar to how a write-ahead log works.

I was worried about the reliability of IndexedDB. Reading the docs it seems like browsers might delete databases as needed, but in practice this doesn't seem to happen [1]. It's probably a much bigger problem on mobile where memory is scarce, but I don't mess with the mobile web (use a native app instead). I was also worried about hitting the limit of IDB storage, but as explained next that hasn't been a problem.

This technique started as an experiment, but it has worked surprisingly well. I have 5 years worth of data in Actual, and the size of the sqlite3 db is 9.7MB. The threshold of the messages table is around 50KB, so in total I'm storing ~10MB in IndexedDB for a user who's been around for 5 years. It's not even close to hitting the IndexedDB max storage limit, which these days is at least 500MB.

While it has worked so far, I want to be 100% confident in this approach. I've been digging deep into how each browser stores IndexedDB data on disk and discovered a couple improvements I can make. I was going to write about them in this post, but I ended up writing more about the overall approach. In the next post I'll dig deep into how IndexedDB works across browsers.

[0] While I didn't talk about it in this post, this also means that the entire app runs in the browser. The "backend" runs in a background worker thread and everything happens locally.

[1] If the local data does somehow get corrupted or deleted, it's not a huge deal. All changes are still sent off and stored on a server (which is how all other devices get synced). If something goes wrong, the app can re-download your data from the server. The only case where you'd lose data is if you were offline and lost your local data, which is to be expected.