There's gonna be a lot of judgement calls here, but I think a local Postgres install is the happy medium. A basic local install is super quick and easy to do, and it also has a lot of good scalability options. SQLite is okay as long as you stay small, but it doesn't scale up very well.
If I suddenly get big and my database is Postgres, then I can spin up my own dedicated server, or switch over to RDS after all, optimize concurrent queries, hire a DBA with scaling expertise, etc. Most of that isn't an option with SQLite. I'd have to switch to a completely different database engine. Despite the promises from ORM writers, I've never seen this go smoothly, and it would have to be done at the worst possible time for it.
If I suddenly get big and my database is Postgres, then I can spin up my own dedicated server, or switch over to RDS after all, optimize concurrent queries, hire a DBA with scaling expertise, etc. Most of that isn't an option with SQLite. I'd have to switch to a completely different database engine. Despite the promises from ORM writers, I've never seen this go smoothly, and it would have to be done at the worst possible time for it.