How do you workaround the fact that SQLite only supports a single writer? For example, your app could be blocked when you run a long operation like creating an index.
For moderate load: just catch the exception and try again. It will rarely happen, and when it does, you can easily recover from it since your site is not hammered.
For bigger load, have a worker that does the writes with a queue.
You create an index, and the index creation takes 30 seconds. Then instead of writing directly to SQLite (which won't work since the index creation blocks other writers) you suggest to store the write in a queue (for example another SQLite database for durability), and have a worker apply the write to the main database when the index creation is done?
For bigger load, have a worker that does the writes with a queue.