Well, you can still have an app server if you want -- having app logic that runs local to the storage doesn't preclude you from also having an "app server" (that is, a place app logic runs that isn't local to the user and isn't local to the storage, but somewhere in between.)

Very typically, that's how it's done with traditional client/server databases.

There's no built-in "wire-protocol" for clients to connect, but there are reasonable options (it's a pretty common pattern, if fact, for systems to have a data service that provides an app-level HTTP interface to data -- so there you go, it's something you might have implemented anyway).

But I think this project would help in the creation of a full/rich application data service without a need for an intermediate app tier.

There are a few reasons people end up with an intermediate app-level data service, but it's starting to seem like a service based on sqlite (running local to the storage, of course) may be able to provide a decent alternative answer in many cases.

I'm imagining a service light-weight enough to run as a lambda or other serverless environment (including fast cold start) which then opens up some interesting things like one-db per user and maybe user-controled host, etc.

You could of course do that, but the main reason to use SQLite in a server application is for the performance characteristics of staying in-process for accessing the database. This makes it cheap to make queries in response to a previous query, which would need to somehow be batched for good latency with a client/server database. Re-adding the client server model defeats almost all of the PROs of SQLite, leaving you with only the CONs.