The “rebuttal” to “Rigid type enforcement helps prevent application bugs” seems pretty much devoid of any argument. The distinction it makes (between systems where the top type can be expressed and ones where it can’t) seems almost meaningless to me. Also no reasoning is given for anything, it’s just “this is my opinion and since I’ve had this opinion for a long time it must be objective fact”.
I think it’s telling that none of the examples given on this page do anything relational. If you move the definition of the schema inside the application (which you effectively are if you use one big KV table) you obviously don’t need your database to help you with it.
I was at a meet up the other day and this topic came up; it’s interesting how unexpected it is. What I find even more fun about SQLites implementation is how they keep memory usage low even though it’s dynamically typed (thus each item contains its type as well). I’d have to find the article on the wiki, but IIRC they use variable length encoding for the type, to keep memory overhead as low as possible.
Flexible types strike me as a weird choice, but really fits the vibe of SQLite. A database designed to be run anywhere, and run in any way.