Feature request: expose API endpoint for online presence

[giovannibonetti-jota]

Hi there,
In my company, we're leveraging PowerSync to build a real-time chat app, and one challenge we're facing is figuring out when a given user (session) is online or not. We're evaluating some options for that, but it would be very handy if we could make an API call to the PowerSync service to ask if a user (session) is online. Better yet, we could pass an array of IDs, and it could tell us which of them are currently online.

This way, our backend could decide based on that info whether to store a row in a database and wait for PowerSync to send it, or send a push notification instead. This presence API doesn't have to be 100% accurate; a best-effort thing would be enough, where an ok means the user (session) was online recently, even though they might have closed the app a few seconds ago.

I imagine the PowerSync service already has that info stored somewhere, so why not expose it and avoid the need to add another dependency just for that, right?

[giovannibonetti-jota]

Hey, could you please comment in this thread that my comment above is not spam? After posting it, a Github bot has immediately flagged my user as spammy, and I am having trouble contacting a human to undo that. Apparently, having an issue closed immediately after it is created is a giant red flag for the Github spam service.

[simolus3]

This is not spam of course. I believe I might have contributed to this by turning the issue into a discussion, my apologies for that. We were also shocked about your account and both the discussion and the issue returning a 404 error yesterday, we have reached out to GH support but have not heard back from them yet.

[giovannibonetti-jota]

Thank you. I had opened an account reinstatement ticket yesterday, and a few years ago someone from GH removed the mark from my account, perhaps thanks to your message.

[raffidahmad]

not spam

[simolus3]

I like the idea from this proposal 👍 One thing that potentially stands out is that in PowerSync Cloud, we could have more than one instance serving oplog data to users due to autoscaling. We already have a layer that could forward this request to all instances and aggregate results, but if you call this endpoint for every new chat message I assume it could turn into a fair bit of overhead.

This way, our backend could decide based on that info whether to store a row in a database and wait for PowerSync to send it, or send a push notification instead

You would unconditionally store the row in the database, right? A potentially related approach might be to add some kind of webhook mechanism to PowerSync to trigger actions on sync events. Being able to sync apps in the background is not an uncommon request, and this could be used to "wake up" clients by letting your backend trigger a push notification. To me at least, that potentially feels more like a "sync first" approach instead of forcing you to proactively check if a user is online to deal with that.

There are definitely some efficiency concerns with this as well, each streaming connection to PowerSync is relatively expensive and this adds one per user even if offline. But perhaps there could be an endpoint to register webhooks that would take a user's JWT along with subscriptions. And then when a bucket covered by that request changes, the service would invoke the webhook to let your backend send a notification. We would have to look at ways to make that scale as well and that might impact how easy it would be to use that, but I wanted to share that idea in case it helps with this discussion.

[giovannibonetti-jota]

Thank you for the ideas!

You would unconditionally store the row in the database, right?

We can do that, but without the presence info we would have to blindly send push notifications for each message, or maybe wait a few seconds to see if the message arrived from PS, and if not send the push notification. We'll figure out how to deal with it over the next few weeks, probably some compromises will need to be done. We'll add another dependency for sending push notifications anyway, so we'll research how other companies deal with this problem. I bet it is much easier for those without a custom messaging solution built into the app, as they can plug an end-to-end solution, whereas in our case we have to connect all the pieces that were not built to work together.

A potentially related approach might be to add some kind of webhook mechanism to PowerSync to trigger actions on sync events. Being able to sync apps in the background is not an uncommon request, and this could be used to "wake up" clients by letting your backend trigger a push notification. To me at least, that potentially feels more like a "sync first" approach instead of forcing you to proactively check if a user is online to deal with that.

You know, as we're working on pagination right, we're trying to build this feedback loop directly in the application layer – meaning, having the client write to tables that are synced back to the server, and let the server control the data flow. The client is responsible for marking which messages have been read – either individually, or in pages (e.g. for each 20 messages). This is important when syncing conversations from different devices belonging to the same user.

At least for our use case, I have the feeling adding webhooks at the infrastructure layer (or however you would like to call it) might be more complex. Anyway, this is all very new to us and we're spending a lot of time at the whiteboard discussing the pros and cons of each approach.

Does that fit in the "sync first" approach you discussed, or did I go off on a tangent? I mean, we were discussing push notifications, but I think the pagination sync approach can be extended to it as well somehow.

[rkistner]

We are actually already working on APIs to expose some sync stats per user, but this functionality is limited to the enterprise version.

The current plan is to expose info like this:

Do note that the same user_id may be connecting from multiple clients/devices, in which case you'd get multiple different client_id values for the same user_id.

The disconnected_at value is also not a strong guarantee - there are cases where we could lose track of a connection, for example when the API process crashed. To cover for that, it helps to have short expiry times for the JWT, which then gives a stronger guarantee for disconnected_at (the JWT exp field effectively forces a disconnect at that time).