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.

Query types

The SQL Passthrough has two types of queries: asynchronous and immediate.

Query type	Description
Immediate queries	Immediate queries are designed for small, time-sensitive queries that can be executed on the server in seconds. The result is returned to the integrator immediately in the POST response.
Async queries	Async queries require integrators POST the query then later GET the query result as a separate API call. Designed to support even the largest queries, async query results are streamed to blob storage and cached for 24-48 hours, in order to give integrators plenty of time to retrieve the results.

Immediate queries

Immediate queries only have one POST endpoint which creates the query and returns the result or error.

See the API reference entry for Creating an immediate query for more information.

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

Limitations

Maximum query execution time: ≤60 seconds.
Maximum result size: ≤1MB.

Async queries

Async queries involve a couple of endpoints:

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. 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.

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

Limitations

Limitations are based on the practice server's resources.

Pagination

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

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:

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.
Immediate queries are interleaved between async queries and result uploads, to ensure their results are returned as soon as possible.

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:

Send us a SQL query which returns multiple pieces of data, receive that data back, then process it on your server.
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: Getting started