Transactions, locks, queues, etc. No different than multiple app instances changing the same row in other databases.
Any state mutation is ultimately ordered in time and how that that ordering is accomplished depends on the abstractions you're using: in your app, network layer, database, etc.
Why would you use SQLlite once you start dealing with network, just use MySQl or PG.
It's just re-inventing the wheel badly, I need to read the details but basically you're using a tool SQLLite that was not designed to be used outside of a single app use case.
For Cloudflare, they're offering it because it's simple and lightweight, and they already have their Durable Objects product which serves as the transaction ordering mechanism and takes care of writes.
If you're doing it yourself then sure it's probably not the best fit but that's up to you to decide.
SQLLite was not meant to be used by multiple process so you have to build the missing parts yourself, 100% those have more limitations and issues than regular RDBMS that were built for it.
Is think one way to think about this is to have one database being tied to one replica (replicas could handle more than one database). Where (importantly) the idea would be one database for each user. You horizontally scale for the number of users, but each user is only using one end node.
It’s interesting because you have to consider how to scale your database as well as your application. The fact that you don’t have one central database opens up more possibilities. But it doesn’t work for all instances (such as a shared read-write data source for all users). For example, this approach wouldn’t work for something like Twitter (at least the original architecture).
The fact that it's just a file on disk limits the usage.