The why:

Applications are usually read heavy. It is a sad state of affairs that, for these kinds of apps, we don't put more work on the write path to allow reads to benefit.

Would the whole No-SQL movement ever even have been a thing if relational databases had great support for materialized views that updated incrementally? I'd like to think not.

And more context:

I'm working to push the state of "functional relational programming" [1], [2] further forward. Materialized views with incremental updates are key to this. Bringing them to SQLite so they can be leveraged one the frontend would solve this whole quagmire of "state management libraries." I've been solving the data-sync problem in SQLite (https://vlcn.io/) and this piece is one of the next logical steps.

If nobody knows of an existing solution, would love to collaborate with someone on creating it.

[1] - https://github.com/papers-we-love/papers-we-love/blob/main/design/out-of-the-tar-pit.pdf [2] - http://lambda-the-ultimate.org/node/1446