My working demo: https://github.com/PierreRochard/realtime-flask-experiment
While doing research I came across a project that did what I was looking for: send realtime updates from Flask to the browser. Check out Luke Yeager’s demo here. The only wrinkle was that he is sending the update to a message queue at the application level while I’m looking to do it at the database level.
There are trade-offs between these two approaches. For performance or scalability I would have to run empirical tests. Architecturally, I think it shouldn’t matter what client modifies the record: a notification should always be emitted. To make that happen without repeating notification code in every client app we have to use PostgreSQL’s LISTEN/NOTIFY feature. Having said that, it may be the case that those notifications should go to an intermediate message queue like Redis or ZeroMQ. TBD.
I took Luke’s demo and extended it to use Brent Tubbs pgpubsub module instead of Redis or ZeroMQ. The relevant code is here. Pgpubsub is a light but incredibly useful wrapper around raw SQL queries, select, and psycopg2.