This may be the underlying problem:
SQLite Atomic Commit Documentation
For this reason, SQLite does a “flush” or “fsync” operation at key points. SQLite assumes that the flush or fsync will not return until all pending write operations for the file that is being flushed have completed. We are told that the flush and fsync primitives are broken on some versions of Windows and Linux. This is unfortunate. It opens SQLite up to the possibility of database corruption following a power loss in the middle of a commit. However, there is nothing that SQLite can do to test for or remedy the situation. SQLite assumes that the operating system that it is running on works as advertised. If that is not quite the case, well then hopefully you will not lose power too often.
A solution may be to re-write the SNO software to be database agnostic and make default suggestion that the node operator utilize a more robust database such as postgresql.
I made this suggestion a few weeks ago and have been looking at the problem myself on and off since then. There are various libraries that could be used to connect and utilize postgresql databases rather than the current hardwired sqlite.
It was brought up that use of postgresql would require a network connection. However, this is not the case on GNU/Linux hosts. A connection can be accomplished using Unix Domain sockets. Here’s a sample implementation in Go. Unix Domain sockets do not utilize networking. The connection is made through the filesystem and so would not have any networking overhead. This is the default connection method for postgresql local users on GNU/Linux systems and it is very fast.
I will continue to look at how to put it all together for a more robust DB connection… but, I’m just another SNO and have other responsibilities in the real world.