T
TanStack•2w ago
foreign-sapphire

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 
  const filter = `user_id = '${user.id}'`
  originUrl.searchParams.set("where", filter)
  const filter = `user_id = '${user.id}'`
  originUrl.searchParams.set("where", filter)
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!
2 Replies
harsh-harlequin
harsh-harlequin•2w ago
it seems like this is what you want? https://github.com/electric-sql/electric/discussions/2931 It's experimental atm but we're close to rolling it out more widely
GitHub
RFC: Cross-Table Shape Filtering with Subquery Support · ele...
Summary Electric currently limits shapes to single-table filtering, preventing developers from expressing common authorization patterns that require cross-table relationships. This RFC proposes ext...
foreign-sapphire
foreign-sapphireOP•2w ago
very nice, yeah I think that would get the job done!

Did you find this page helpful?