[ENGNR]

I reckon you want some kind of publish-subscribe websocket setup, with event streams. Something like Apache Pekko, or Elixir, or maybe Redis, so the web browser subscribes to a topic they're authorised to receive updates on. And then separately, subscribe listeners on the backend to the tables, and fan out updates to the various topics. They'd get moved out to the frontend. "reactive" would be the name of the game, so event streams, managing backpressure, etc, etc.

Something like the "stale while revalidate" strategy (eg as used by react-query) is wayyyy simpler however, and gets you most of the way there. You can set a timeout for when to invalidate the frontend cache to something short if you wanted (eg 3 seconds), and if the content is still being viewed by a component after that time it just fetches itself again while keeping the stale data in place. And for the few pieces that truly are realtime, like a stock ticker or something, just use a direct websocket.

[bruchim]

Expiration-based cache (e.g., 3 seconds) is not acceptable in this case, and more than that will reduce the load on the server + db dramatically.

Imaging subscribing to SQL query not just for a table; isn't it very complicated?