Better polling and custom notifications using Registered Queries

We’ve heard time and again that integrators want an easy way to tell when the PMS database changes. But we’ve also heard that something like “anytime the Appointments table changes” is too broad and too noisy - integrators want granularity and control. We could set up more specific listeners, but every integrator wants something different, so we would end up with a long list of similar-but-different events.

Which made us ask: Why don’t we just let the integrators define what they listen to?

This was what drove us to develop Registered Queries and Webhooks, the combination of which allows exactly this. Registered Queries are also great for polling use cases in general, reducing costs and overhead, and improving performance.

It’s also database agnostic which means Registered Queries work for all Halo Cloud supported PMS, despite their different database technologies and change notification systems.

So how do they work?

What are Registered Queries?

The short answer is that Registered Queries are a repeated SQL query, run at a specified frequency for a specified duration, whose latest results (or errors) can be fetched from Halo Cloud the same way you would fetch async results.

The basic use case for Registered Queries is to replace polling, where they reduce overhead and may improve performance. This is because the query is only sent from Halo Cloud to Halo Link once, reducing the number of calls over the wire. Results are also only uploaded to Halo Cloud when they change, reducing the amount of data uploaded unnecessarily.

For these reasons, Registered Queries are also cheaper for integrators, compared to sending multiple immediate or async queries. 

Beyond that use case though, the real power comes from combining Registered Queries and Webhooks, enabling integrators to receive webhook notifications when data changes in the PMS database according to a SQL query they specified. This gives integrators full control over what they are notified about and how often, while also reducing noise and redundancy.

If you would like more information, our docs have a whole section on Registered Queries. But to give a quick introduction...

Using Registered Queries

Creating a Registered Query

Integrators “register” a Registered Query by specifying:

• The SQL they want run each time
• The frequency at which to run the query (minimum 60 seconds currently)
• The duration to run the query for (maximum 24 hours)
• A name for the Registered Query (to help differentiate if an integrator is running multiple Registered Queries at the same practice)

More details can be found in the API reference for Create registered query.

For example:

{
  "name": "RegisteredQueryTest",
  "registeredQueryType": "query",
  "frequency": 60,
  "duration": 1200,
  "registerCommand": {
    "command": {
      "text": "select 1",
      "executionMode": "reader",
      "type": "text",
      "parameters": []
    }
  }
}

If registering the query is successful, this will return a query ID, such as:

{
  "id": "12345678-4560-4f58-a142-5375c0592a6d"
}

Fetching results

Link will run this query every minute for the next 20 minutes (1200 seconds), and push the result up to Halo Cloud where it can be fetched using the Get registered query result endpoint, which will return something like:

{
  "data": "e2lk...", // snipped for readability
  "parameters": null, // may be null or an array
  "rows": 1,
  "size": 8
}

Other endpoints

There are also endpoints for:

• Cancelling a Registered Query
• Listing your active Registered Queries at a practice
• Retrieving metadata about a Registered Query

And to combine with Webhooks…

Webhooks is a feature we enable for integrators upon request. Check out our blog post about Webhooks for more information, or reach out to Support if you have any questions or want to get set up.

Once enabled for your subscription, combining Webhooks and Registered Queries means receiving a webhook notification each time a Registered Query result is uploaded. This means no webhooks if the data hasn’t changed, but does mean a webhook if the query has errored for any reason.

Webhooks also do not contain query results in the payload for security reasons - the result data would still need to be fetched from Halo Cloud.

The recommended workflow for using these two features in combination, once you’re set up with Webhooks, is:

1. Create a Registered Query at a practice
2. Listen on your webhook URL for notifications regarding that query
3. When you receive a webhook, fetch the Registered Query result
4. Process as usual

How do I get started?

Any integrator can use Registered Queries, but Webhooks must be configured for your subscription. Please reach out to Halo Connect support to get set up, or if you have any questions.