I can tell you one of the easiest ways to corrupt an SQLite db file is to not let a library specifically designed for file operations, handle file operations. And of course, not using provided libraries for db access (E.G. PDO if you're using PHP).
I ran a forum on SQLite a while back, and things worked extremely well as long as I didn't pretend I knew better than the library.
There's no good excuse to not use PDO and there hasn't been one for years now. Before that, I've implemented a write queue, which seems redundant in retrospect, in case the file lock issue came about, but it never did even though I did hit the write queue a few times.
As for crashes, periodic snapshots of the db and journals (that's very important) is usually the best way to avoid recovery issues.
I ran a forum on SQLite a while back, and things worked extremely well as long as I didn't pretend I knew better than the library.
There's no good excuse to not use PDO and there hasn't been one for years now. Before that, I've implemented a write queue, which seems redundant in retrospect, in case the file lock issue came about, but it never did even though I did hit the write queue a few times.
As for crashes, periodic snapshots of the db and journals (that's very important) is usually the best way to avoid recovery issues.