I’m currently working on a little chat app project (using the Shiny for Python framework, if that matters) that involves two users joining a chatroom and having separate side-by-side conversations with an AI assistant. The two users’ conversations are to be visible to each other, but each user should only be able to participate in their own conversation. I use a PostgreSQL database to store all the data — users, conversations, messages, chatrooms, etc.

I’m having trouble with “detecting” if you have a partner who’s connected, and getting their info (their user ID, their conversation ID, etc.) It’s simple enough for the second user who connects — they can simply check if the chatroom is full (are there 2 people inside after you joined?), but I can’t figure out how to best do it for the first user who connects to a chatroom.

I do know I’d like to avoid polling the database regularly to see if anyone’s joined the chatroom — that just seems horribly inefficient to me. To that end, I figured I’d use server-sent events/notifications to inform the first user when anyone joins, but I’m not very familiar with any of the technologies for that.

I recently learned of the LISTEN/NOTIFY functionality in PostgreSQL, as well as the WebSocket protocol. That being said, WebSocket seems really complicated and somewhat daunting to use/learn, especially considering all of the security nuances and stuff. If it’s possible to get this done in my project using PostgreSQL, I’d like to. Please advise — thanks!