Recommended patterns for syncing rows which can belong to multiple users
I have an AI chat feature which I am expanding to allow multi-user chat, with the option to tag the AI instead of just sending the message to all human users.
Before this feature, I added a check like
to my Electric API proxy.
I now need to allow users to see other user's messages and AI responses, provided they belong to the same chat. So I could query for the user's chats and use that in my filter, ie all messages that belong to this set of chats. The problem though is, AFAICT, that that query will be evaluated once, so if a user gets added to a chat and they already have the site open it won't update the query until they refresh the page?
I have toyed with the idea of adding a participants field (list of user ids) to every message and filtering on that, but it seems a bit wasteful. The third option would be to rely on pg's RLS, which I already have policies for but feel a bit uncomfortable relying exclusively on (probably irrationally).
I think these are just the set of options until joins arrive in the where clause (if I understood correctly that that's in the pipeline). Please correct me if I'm wrong though!
Before this feature, I added a check like
to my Electric API proxy.
I now need to allow users to see other user's messages and AI responses, provided they belong to the same chat. So I could query for the user's chats and use that in my filter, ie all messages that belong to this set of chats. The problem though is, AFAICT, that that query will be evaluated once, so if a user gets added to a chat and they already have the site open it won't update the query until they refresh the page?
I have toyed with the idea of adding a participants field (list of user ids) to every message and filtering on that, but it seems a bit wasteful. The third option would be to rely on pg's RLS, which I already have policies for but feel a bit uncomfortable relying exclusively on (probably irrationally).
I think these are just the set of options until joins arrive in the where clause (if I understood correctly that that's in the pipeline). Please correct me if I'm wrong though!
