Expensify is powered entirely using sqlite (inside bedrockdb.com) and it is freaking amazing.
Seriously, as impressive as SQLite is for what it is, people shouldn't be given the impression that it doesn't come with some massive and often surprising caveats when viewed as a general purpose database.
As for what most concerned me most when using SQLite was the ease of which it would happily allow me to make broken queries and not raise a fuss, e.g. referencing non-GROUP BYed terms in an aggregation.
Can you provide example(s) of the broken query with non-GROUP BY columns in an aggregation? (Or a reference link if this is well known - I've searched briefly but can't find anything.)
Select user.id, user.name, sum(tx.amount) from user inner join txn on (user.id = txn.uid) group by user.id
User.name is not in the group by list so it is picked arbitrarily from the rows in the group. It's harmless here but not in all cases.
But hell yes, tools should warn you about this loudly, as it's not generally correct.
Also, it has 64-bit integers - https://www.sqlite.org/datatype3.html