I recently implemented SQLite for a metadata application at work. My constraints in design were pretty concise; the application will only ever need to scale vertically, my data persistence story is a matter of speed and accessibility over longevity, I have plenty of options for scaling disk IO, and my read performance is much more paramount than write performance.

The outcome is that my persistence later is not adding $200/m immediate service overhead cost and my deployment was easy to manage, which is a strong promise made to the rest of the team. I think there's a place for SQLite, but just like any tool you need to know that your design constraints match the constraints of the tool.