I have spent the last 18 months building a web app using SQLite, python (starlette) and htmx. It is a back office app that now runs daily operations for a 60 person tax firm. The load is low, and it runs on a very cheap ec2 instance with litestream backups to s3.

The low latency is wonderful because I can write serial queries to construct a complex response. All the queries are written by hand and I do not use an ORM.

I have had a couple of outages, and all but one were basic operator errors. The notable exception was when tax season started, load went up, and I found that I was leaking (or creating too many) db connections. This was scary and I never quite understood the low level failure mechanism. The solution I came up with is to issue warnings whenever a connection (subclass of std sqlite3 Connection) is destroyed (__delete__()) without having been explicitly closed. Then I found all of the usage sites and put them into `with` contexts. Explicitly managing the connection lifetimes took the pressure off and I haven’t had to think about it since. I’d still like to reproduce the problem better though.

I do wonder what I would do if we scaled up to a point where things started to fall apart again, but there’s a good chance it won’t happen. It would require a lot more traffic, which implies massive staff growth.

The other architectural decision worth noting is that I keep complete local copies of various saas api data. A lightweight CRM, customer support, billing, call center, etc. Each of these has a background api fetcher/poller, and writes to its own SQLite file. Then the web app attaches each file to the main db. This gives me schema.table namespacing in the sql, and allows for separate backup policies for the different files. The trade off is no atomicity across the files, but for my purposes it is not significant.