A challenge for building realtime web clients is that the server must know which records the client is currently rendering.
Option 1: web server remembers which unique IDs it rendered for client
Option 2: web server remembers the client's query parameters
We're going to start with Option 1.
First, we override the default list template to add unique IDs to each cell in the HTML table. This will make life easier when we receive an update and need to modify that specific cell's text. I also added a realtime block at the top.
The table name and list of IDs is received by the Flask-SocketIO server and inserted into a SQLite table.
When an update is made on the todo_items table the PostgreSQL database emits a notification. This notification is consumed by pgpubsub on an eventlet greenthread. The subscriptions SQLite table is queried to see if the updated record's ID is currently rendered in a client. If it is, a socket.io message is emitted to that specific client.
The client receives the update and modifies the relevant "cells" in the HTML table.
There are a few improvements that I'd like to make to this experiment:
Replace SQLite with redis. SQLite is certainly simpler but it is problematic if we're going to put this app on gunicorn with several workers.
Factor out the pgpubsub client. Ideally it would be a modern asyncio script that takes Postgres notifications, looks up the socket.io client in redis, and emits the notification to the client.
Wrap the notification triggers with SQLAlchemy so that they can be created for any given model with just a decorator.
Further customize the template so that the columns do not resize (too often).
Longer term, Option 2 of full query subscriptions is still worth exploring as new records can be important to the user.