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

  • Original records receive updates and deletes
  • Relatively easy to implement
  • New records that would have fit the original query’s parameters don’t appear

Option 2: web server remembers the client’s query parameters

  • All updates, creates, deletes will appear for client
  • With Flask-Admin the query parameters are a known set of filters and sorts
  • Harder to implement

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 realtime block contains the JavaScript that connects to the Flask-SocketIO server. When the connection is established the script sends the table name and a list of record IDs that were rendered. This data is generated server-side by a function, accessible in the template thanks to Flask’s context processor feature.

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:

  1. 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.

  2. 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.

  3. Wrap the notification triggers with SQLAlchemy so that they can be created for any given model with just a decorator.

  4. 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.