Skip to content

SQL Passthrough API overview

The SQL Passthrough API enables PMS integrators to switch existing products to Halo Connect with minimal changes and to reduce development costs for new products. It accepts any standard SQL query -- if you could successfully run a query on the PMS database directly, then you can run it via the SQL Passthrough API.

This page assumes you have already read the Halo Cloud overview.

Supported PMS

The SQL Passthrough API currently supports:

Query types

The SQL Passthrough has three types of queries:

Query type Description
Immediate queries Immediate queries are small, time-sensitive queries that can be executed on the server in seconds or less. The result is returned to the integrator immediately in the POST response.
Async queries Async queries are queued and executed when bandwidth is available, and the query results are made available via an API endpoint for approximately 24 hours. There is no limit imposed on query size, though queries may time out if execution takes too long.
Registered queries Registered queries allow integrators to register a query with a practice for up to 24 hours, to be queued at a specified frequency. Results can be fetched periodically in the same way async query results are.

Immediate queries

Immediate queries are the simplest and fastest queries available in the SQL Passthrough API. Designed for real-time applications, immediate queries are given priority in the queue and are given limited size and execution time to ensure an immediate query does not hold up the execution of others.

See the API reference for more information about Sending an immediate query.

Restrictions

  • Maximum query execution time: 60 seconds.
  • Maximum result size: 8MB.
Immediately query workflow 
    sequenceDiagram
      participant I as Integrator
      participant HC as Halo Cloud
      participant HL as Halo Link
      participant PMS as PMS database

      I->>+HC: POST query
      HC->>+HL: Routes query to practice
      HL->>+PMS: Executes query
      PMS-->>-HL: Returns query result
      HL-->>-HC: Returns query result
      HC-->>-I: Returns query result

Asynchronous queries

Async queries are designed for anything that isn't time sensitive, but particularly for large data extractions. Async queries are queued when they are received by Link, but their execution and result upload can be interrupted by immediate queries, to ensure they do not interrupt real time applications. The query result is also not returned directly, but is made available via an endpoint. The three API endpoints relevant to async queries are:

Method Endpoint name Description API Reference
POST Create an async query Accepts a query, routes it to the appropriate Halo Link instance for queueing, and returns a query ID for use with the other async query endpoints. Link
GET Check query status Allows integrators to check the status of a query using its query ID. Result pages become available when status is successful. Link
GET Retrieve query result page For retrieving query result pages once the query is complete. Pagination information can be found in the query status data. Link

When implementing an async query in your integration, please consider how frequently you need to check the status and results. It may be possible to optimise the frequency based on statistics such as the average of previous query result times. Checking less often will also reduce your costs, as Halo Connect charges on a per query and per usage basis.

Restrictions

  • Maximum query execution time: 5 minutes.
  • This does not include time taken for queuing or uploading results.

Pagination

Async query results are paged based on data size. Each page is maximum 1MB. See below for more information about handling paged query results .

Async query workflow 
  sequenceDiagram
    participant I as Integrator
    participant HC as Halo Cloud
    participant HL as Halo Link
    participant DB as PMS database

    I->>+HC: POST async query
    HC->>+HL: Route query to practice
    HL-->>-HC: Return query ID
    HC-->>-I: Return query ID

    HL->>+DB: Executes query
    DB-->>-HL: Return result
    HL->>HC: Uploads result to cache

    loop Check query status
      I->>+HC: GET query status
      HC-->>-I: Return status
    end
    loop Get result pages
      I->>+HC: GET query result page
      HC-->>-I: Return query result page
    end

Registered queries

To reduce the overhead of polling practice databases, queries can be registered at a practice for up to 24 hours, to be run at a specified frequency. Results can be fetched via an endpoint in a similar way to async query results.

  • To register a query, specify:
    • the query to be run,
    • a name to reference it by,
    • the frequency to run it at, and
    • the duration to run it for.
  • The API endpoint for registering queries will return a query ID which can be used to fetch the results.
  • When the query is run, if the result has changed since the last time (or it is being run for the first time), the result will be uploaded to cloud storage. It can then be fetched using the query ID via the Get registered query result endpoint.
  • The result of each run overwrites any previous results. Halo Connect will not maintain a history of query results. We will only store the result from the most recent execution that produced a different result.
  • Registered queries can be cancelled using the query ID.
  • Endpoints are also available to list out active registered queries for a practice, or to get the details of a particular registered query. The results of these endpoints include the query name, to make it easier to know which query is which.
  • Registered queries are queued at the specified frequency. This may result in a slight delay before the query is executed, if there are many other immediate or registered queries already queued.

See the API reference for more information.

Restrictions

  • Maxiumum duration: 86400 seconds (24 hours).
  • Minimum frequency: 60 seconds.
  • Maximum query execution time: 60 seconds.
  • Maximum result size: 8MB.
Registered query workflow 
  sequenceDiagram
  participant I as Integrator
  participant HC as Halo Cloud
  participant HL as Halo Link
  participant PMS as PMS database

  I->>+HC: POST query, {frequency}, {duration}
  HC->>HL: Registers query with Link
  loop Every {frequency} for {duration}
    HL->>+PMS: Executes query
    PMS-->>-HL: Returns query result
    HL->>HC: Uploads result
  end
  loop
    I->>+HC: Polls for latest result
    HC->>-I: Returns result
  end

Query priority in queue

One of Halo Connect's key goals is to manage and reduce load on the practice server. To do this, Halo Link prioritises processing queries according to their type:

  • Immediate queries have highest priority, and will be queued ahead of async and registered queries (but not other immediate queries).
  • Async queries are queued and executed in the order they are received by Halo Link.
  • Async query results are chunked for upload, and each chunk is queued individually.
  • Registered queries are queued at the specified frequency.

Query lifetime

All queries have a maxTimeInQueue configuration value that dictates the length of time a query will remain queued. After this time has elapsed, the query is marked as expired. While this occurrence is rare, it can occur during periods of high demand and an integrator must be able to handle the re-queuing of expired queries.

Query results and pagination

The query results are delivered embedded in a JSON response. They are currently base64 encoded.

When you receive the query response, you can base64 decode the data then save it to your selected form of storage.

If the query result is paged, you'll need to stitch the rows together by merging the JSON arrays.

Example: Encoded and decoded query results

The following JSON:

{
  "data": "WwogIHsKICAgICJQYXRpZW50IjogIkphc29uIEFoZXJuIiwKICAgICJJbnRlcm5hbElEIjogMzYsCiAgICAiQXBwb2ludG1lbnREYXRlIjogIjIwMDYtMDItMjNUMDA6MDA6MDAiLAogICAgIkFwcG9pbnRtZW50VGltZSI6IDQ1MDAwLAogICAgIkFwcG9pbnRtZW50TGVuZ3RoIjogOTAwLAogICAgIlByb3ZpZGVyIjogIkRyIEZyZWRlcmljayBGaW5kYWN1cmUiLAogICAgIlVyZ2VudCI6ICJObyIsCiAgICAiQXBwb2ludG1lbnRUeXBlIjogIlN0YW5kYXJkIGFwcHQuIiwKICAgICJTdGF0dXMiOiAiQm9va2VkIiwKICAgICJBcnJpdmFsVGltZSI6IDAsCiAgICAiQ29uc3VsdGF0aW9uVGltZSI6IDAsCiAgICAiQm9va2VkQnkiOiAiRnJlZGVyaWNrIEZpbmRhY3VyZSIKICB9LAogIHsKICAgICJQYXRpZW50IjogIkphc29uIEFoZXJuIiwKICAgICJJbnRlcm5hbElEIjogMzYsCiAgICAiQXBwb2ludG1lbnREYXRlIjogIjIwMDYtMDItMjRUMDA6MDA6MDAiLAogICAgIkFwcG9pbnRtZW50VGltZSI6IDM0MjAwLAogICAgIkFwcG9pbnRtZW50TGVuZ3RoIjogOTAwLAogICAgIlByb3ZpZGVyIjogIkRyIEZyZWRlcmljayBGaW5kYWN1cmUiLAogICAgIlVyZ2VudCI6ICJObyIsCiAgICAiQXBwb2ludG1lbnRUeXBlIjogIlN0YW5kYXJkIGFwcHQuIiwKICAgICJTdGF0dXMiOiAiQm9va2VkIiwKICAgICJBcnJpdmFsVGltZSI6IDAsCiAgICAiQ29uc3VsdGF0aW9uVGltZSI6IDAsCiAgICAiQm9va2VkQnkiOiAiRnJlZGVyaWNrIEZpbmRhY3VyZSIKICB9Cl0K="
}

Will decode into:

[
  {
    "Patient": "Jason Ahern",
    "InternalID": 36,
    "AppointmentDate": "2006-02-23T00:00:00",
    "AppointmentTime": 45000,
    "AppointmentLength": 900,
    "Provider": "Dr Frederick Findacure",
    "Urgent": "No",
    "AppointmentType": "Standard appt.",
    "Status": "Booked",
    "ArrivalTime": 0,
    "ConsultationTime": 0,
    "BookedBy": "Frederick Findacure"
  },
  {
    "Patient": "Jason Ahern",
    "InternalID": 36,
    "AppointmentDate": "2006-02-24T00:00:00",
    "AppointmentTime": 34200,
    "AppointmentLength": 900,
    "Provider": "Dr Frederick Findacure",
    "Urgent": "No",
    "AppointmentType": "Standard appt.",
    "Status": "Booked",
    "ArrivalTime": 0,
    "ConsultationTime": 0,
    "BookedBy": "Frederick Findacure"
  }
]

Recommendations

Handling personally identifiable information (PII)

Where possible, Halo Connect recommends pre-processing data on the server to reduce the amount of PII transmitted over the internet.

Example: Pre-processing PII

When checking if a patient qualifies for something by collating several pieces of data, you could:

  1. Send us a SQL query which returns multiple pieces of data, receive that data back, then process it on your server.
  2. Send us a SQL query which retrieves multiple pieces of data and processes it on the server, such that only the required data is returned to you via our API.

The first option requires less complicated SQL, but increases the amount of PII transmitted over the internet for this query. The second option reduces PII and reduces the query result size which, due to Halo Connect charging based on usage, could also reduce your cost per query.

Use async queries where possible

Many integrators we talk to have time-sensitive applications which require results within seconds. Integrators should use immediate queries for such use cases. However, integrators should also be aware that because immediate queries have a short timeout (only 60 seconds), if a practice server is receiving too many immediate queries too quickly, the queue can fill and immediate queries may start to timeout due to there being too many other immediate queries ahead of them.

Halo Connect does actively monitor query behaviour in order to identify such issues, and will keep improving our query handling and queueing in order to minimise this risk. However, we do also appreciate integrators assisting us in optimising query frequency and size, in order to improve the experience for everyone.

For that reason, Halo Connect recommends integrators use async queries where possible.

Using async queries allows Halo Link to better control query queueing and resource usage on the practice server. It allows time-sensitive immediate queries to be prioritised appropriately, while reducing the risk of any query timing out.

Optimise usage by requesting batches of data

To reduce your usage and to lighten the load on the practice server, we recommend sending one query to retrieve a batch of data instead of sending multiple small queries at once, and processing the batched data on your server.

For example, if you wish to poll multiple database tables for changes every minute, we recommend sending one query which returns the data for all tables over sending one query per table. You can then post-process the data to split it up. This will save you money on usage, and allow Halo Link to better handle queries from multiple integrations. It will also ensure you receive all the results at once. If you send multiple queries, there may be a slight delay between receiving each result, due to the way Halo Link queues queries and may interleave queries from multiple integrators.

Prev: Halo Cloud overview Next: SQL Passthrough query examples

Version: 24.1011.3695