I think the idea of using SQLite as an application file format is excellent, and it's great that it can serve as a general store of files. As a general purpose compressed archive format, however, I agree it leaves a lot to be desired.
That is often desired for file archives. For use cases like genomics, though, reads from random offsets are common, hence the bgzf format used by BAM files which is a sequence of gzip chunks and an index. Lower compression ratio, but faster seeking.
Another missing element is that SQLite's support for large blobs is via linked lists of pages. That's fine for sequential access, but unacceptable for random access.
For a database to be usable as a filesystem backend, it must at least be composable with itself without sacrificing performance. Lets say you built a kernel module or fuse module using sqlite as the backend. How well would sqlite itself perform on a file in this filesystem?
This surprised me and you were correct, there is no guarantee that related payload overflow pages are consecutively ordered. I thought extents can be implemented with those pages but it would be pretty bad for random access as is. In fact one of the reasons I thought SQLite can function as a file system was a built-in support of random-accessible BLOBs, and some more research revealed that the support was based on the fact that SQLite loads the entire BLOB into memory. Oops.
That's mentioned both in the article (3.2) and GP ("solid compression" is the term of art for a single compression stream for the entire archive).