I think that is where sync engines come in that allow doing arbitrary hybrid queries (across local and remote data) and then keeping the results of those hybrid queries in sync on the client.

This is one of the ideas that appears to be central to the genesis of Zero [1]

ElectricSQL allows for a similar pattern and PowerSync is also working on this [2]

[1] https://www.youtube.com/watch?v=rqOUgqsWvbw

[2] https://www.powersync.com/blog/powersync-2025-roadmap-sqlite...