Table of Contents

1. Overview

Cinchy queries are automatically available as REST APIs to allow for external application integration.

2. Example

The following example shows how to use API Saved Queries.

1. Create or navigate to your saved query.

2. Retrieve your REST API endpoint by selecting the green REST API button located in the top-right corner of the Execute Query screen (Image 1).

3. Copy and paste this endpoint into your browser URL to access your Saved Query.

You can also use the below API specification table to create your endpoint, inputting your values for the Web URL, Domain, Query, and additional parameters, such as an access token, as needed.

How to Use the Access Token to Call Cinchy APIs

GET https://<Cinchy Web URL>/API/MyDomain/MyQuery

To access any Cinchy Saved Query API, pass the access token received from the Bearer Token Request in the Authorization header, prefixed by "Bearer".

Query Parameters

Headers

< HTTP/2 200 
< cache-control: private, s-maxage=0
< content-type: application/json; charset=utf-8
< server: Microsoft-IIS/10.0
< x-aspnetmvc-version: 5.2
< access-control-allow-origin: *
< x-aspnet-version: 4.0.30319
< x-powered-by: ASP.NET
< date: Wed, 1 Aug 2020 17:40:13 GMT
< content-length: 2985

--given the following CQL used within a saved query:

IF (ISNULL(@Int,0) = 0)
BEGIN
  RAISERROR('Invalid parameter: @Int cannot be NULL or zero',18,0)
  RETURN
END
IF (ISNULL(@String,'') = '' OR ISNULL(@String,'') = 'X')
BEGIN
  RAISERROR('Invalid parameter: @String cannot be NULL or empty or X',18,0)
  RETURN
END

--note "X-Cinchy-Error" in the sample response:

< HTTP/1.1 400 Bad Request
< Cache-Control: private
< Content-Type: text/html; charset=utf-8
< Server: Microsoft-IIS/10.0
< X-AspNetMvc-Version: 5.2
< Access-Control-Allow-Origin: *
< X-Cinchy-Error: Invalid parameter: @Int cannot be NULL or zero
< X-AspNet-Version: 4.0.30319
< X-Powered-By: ASP.NET
< Date: Wed, 1 Aug 2020 17:43:04 GMT
< Content-Length: 4517

< HTTP/1.1 401 Unauthorized
< Cache-Control: private
< Content-Type: text/html; charset=utf-8
< Server: Microsoft-IIS/10.0
< X-AspNetMvc-Version: 5.2
< X-AspNet-Version: 4.0.30319

Endpoint Example

curl --request GET \
  --url https://sandbox.cinchy.net/product-aurora-1/API/Sandbox/Get%20Colours \
  --header 'Authorization: Bearer xxx’ \

Use Basic Authentication to Call Cinchy APIs

https://<Cinchy Web URL>/BasicAuthAPI/MyDomain/MyQuery

3. Anonymous Access

The following instructions detail how to allow anonymous access to your saved query API endpoint. You can use this in the case where you don't need any type of authentication to access the API response.

1. Navigate to the table your query will be referencing. In this example, it is the Accessibility Assessments table (Image 1).

2. Navigate to Data Controls > Entitlements.

3. On a new row, add in the Anonymous user and ensure that either "View All Comuns" (to expose all the data) or "View Selected Columns" (to select individual columns) is checked off (Image 3).

4. Design your query (Image 4). For more information on creating new saved queries, click here.

5. Once you have written your query, navigate to Design Query > Info, on the left navigation bar.

6. Change your API Result Format to JSON (Image 5).

7. Navigate to Design Controls from the left navigation bar.

8. To ensure that anonymous users have the correct permission needed to execute the query that generates the API response, add the "Anonymous" user to the users permission group uiunder "Who can execute this query?" (Image 6).

9. Navigate to "Execute Query" from the left navigation bar.

10. Copy your REST API endpoint URL (Image 7).

11. To confirm that anonymous access has been successfully set up, paste the URL into an incognito/private browser (Image 8).

3.1 Troubleshooting

• 401 errors likely mean you have not added the Anonymous user to the list of users (not "Groups") that can execute the query.

• 400 errors likely mean that you have not added the Anonymous user to the list of users that (not "Groups") that can view column data from the tables that your query uses.

Table of Contents

1. Introduction

Compatibility: Webhook ingestion was introduced in Cinchy platform v4.21. Note that previous Cinchy versions will not include a Webhooks system table, and will not support this feature.

Context: A webhook uses a trigger event to initiate a data transfer that can then be ingested by another application. Many applications support webhooks to provide data updates in real time with minimal configuration required. Cinchy users can subscribe to and ingest webhooks via configuring a unique API endpoint. When the external application addresses this endpoint, a pre-identified saved query can be run, under an authorized user account, to ingest the data and insert or update it into Cinchy.The following video walks you through a specific configuration, with a general step-by-step guide below:

2. Walkthrough Video

3. Step-by-Step Instructions for Configuring a Webhook

1. Identify the table in Cinchy that you want the information to be pushed to. (Image 1)

2. Create your query in Cinchy. This query should take data from the webhook event, and push it into the Cinchy table that you identified in Step 1. (Image 2)

• Your query will be running under a specific user account that you will assign in the next step. Ensure that whichever user you choose for this purpose has the correct permissions to execute the query, and to insert / update data in the target table.

3. Navigate to the webhooks table in Cinchy, and populate the following columns (Image 3):

1. Key: This can be any string (we recommend that you treat this like a password and make it random and unguessable).

2. Run As: Insert the user who will be running this query here. This should be the same user to whom you gave permissions in Step 2a.

3. Saved Query: Select the saved query that you created in Step 2.

4. Forward Payload as Parameter: This column will depend on the manner in which you would like to ingest the webhook payload.

   • If you are configuring individual parameters in the webhook payload (for example: @name, @url, etc.), you may leave this column blank.

   • If you are not configuring individual parameters, as an alternative you can ingest the entire payload under one parameter and specify it in this field. In the below image, we have defined it as “JSON”. This means that the full payload (which happens to be a JSON file in this case) will be parameterized as @JSON and then inserted into a table column named JSON.

4. In your source application, navigate to the webhook settings and configure the following:

• URL: This will be your Base URL + /API/callback?key= + the key value that you assigned in Step 3a.

• Example: {baseURL}/API/callback?key=rGh5tfgHK8989J5

1. Introduction

You can query or manipulate any data on the Cinchy Platform using the Cinchy Query Language (CQL). You can then save that as a Saved Query which allows it to be accessed via an API endpoint.

You will need to first a Cinchy bearer token (see Authentication), and then you can either access a pre-defined Saved Query via the Saved Query endpoints, or perform freeform querying via the ExecuteCQL endpoint.

2. Master List of Endpoints

The following is a list of common API endpoints. These follow the format of <baseurl>/endpoint

• /admin/index

• /apps/modelloader

• /healthcheck

• /metaforms/healthcheck

• /cache/clear

• /API/ExecuteCQL

• /API/MyDomain/MyQuery

• /identity/connect/token

• /api/getstsauthorityuri

• /api/jobs

2.1 /admin/index

2.2 /apps/modelloader

2.3 /healthcheck

2.4 /metaforms/healthcheck

2.5 /cache/clear

2.6 /API/ExecuteCQL

POST: https://<Cinchy Web URL>/API/ExecuteCQL

Query Parameters:

Header Parameters:

Responses:

• 200 (OK)

Parameters

To pass in parameters in your executeCQL, you will need to pass in sets of parameters in the following format. So if you have one parameter then you would pass in 3 query parameters beginning with Parameters[0]. , and if you have a second parameter you would include an additional 3 query parameters beginning with Parameters[1]. .

2.7 /API/MyDomain/MyQuery

GET: https://<Cinchy Web URL>/API/MyDomain/MyQuery

Query Parameters

Header Parameters

Responses

200: The request has successfully returned the record set.

< HTTP/2 200 
< cache-control: private, s-maxage=0
< content-type: application/json; charset=utf-8
< server: Microsoft-IIS/10.0
< x-aspnetmvc-version: 5.2
< access-control-allow-origin: *
< x-aspnet-version: 4.0.30319
< x-powered-by: ASP.NET
< date: Wed, 1 Aug 2020 17:40:13 GMT
< content-length: 2985

400: The request could not be understood, the client is sending a request with incomplete data, poorly constructed data or invalid data.

Optional Validation Logic: To validate query business / control conditional logic failure can be added at the beginning of the API which can intentionally generate a 400 error code (using RAISERROR) and stopping (using RETURN) the API. If there is no RETURN the errors will accumulate and will be provided at the end of running the API.

An attribute (X-Cinchy Error) will be returned in the HTTP header with the custom RAISERROR message indicated (see example)

Example:
--given the following CQL used within a saved query:

IF (ISNULL(@Int,0) = 0)
BEGIN
  RAISERROR('Invalid parameter: @Int cannot be NULL or zero',18,0)
  RETURN
END
IF (ISNULL(@String,'') = '' OR ISNULL(@String,'') = 'X')
BEGIN
  RAISERROR('Invalid parameter: @String cannot be NULL or empty or X',18,0)
  RETURN
END

--note "X-Cinchy-Error" in the sample response:

< HTTP/1.1 400 Bad Request
< Cache-Control: private
< Content-Type: text/html; charset=utf-8
< Server: Microsoft-IIS/10.0
< X-AspNetMvc-Version: 5.2
< Access-Control-Allow-Origin: *
< X-Cinchy-Error: Invalid parameter: @Int cannot be NULL or zero
< X-AspNet-Version: 4.0.30319
< X-Powered-By: ASP.NET
< Date: Wed, 1 Aug 2020 17:43:04 GMT
< Content-Length: 4517

401: The saved query API endpoint will return a 401 error code when you provide invalid credentials, this includes not providing credentials, expired credentials and incorrect credentials.

< HTTP/1.1 401 Unauthorized
< Cache-Control: private
< Content-Type: text/html; charset=utf-8
< Server: Microsoft-IIS/10.0
< X-AspNetMvc-Version: 5.2
< X-AspNet-Version: 4.0.30319

2.8 /identity/connect/token

POST: https://<Cinchy SSO URL>/identity/connect/token

Header Parameters

Body Parameters

Responses

200: The request is successful

// {
    "access_token": "eyUzI1NiIsImtpZCI6IkE4M0UwQTFEQTY1MzE0NkZENUQxOTFDMzRDNTQ0RDJDODYyMzMzMzkiLCJ0eXAiOiJKV1QiLCJ4NXQiOiJxRDRLSGFaVEZHX1YwWkhEVEZSTkxJWWpNemsifQ.eyJuYmYiOjE1NTQxMzE4MjAsImV4cCI6MTU1NDEzNTQyMCwiaXNzIjoiaHR0cDovL2xvY2FsaG9zdDo4MDgxIiwiYXVkIjpbImh0dHA6Ly9sb2NhbGhvc3Q6ODA4MS9yZXNvdXJjZXMiLCJqc19hcGkiXSwiY2xpZW50X2lkIjoiYWJjIiwic3ViIjoiMSIsImF1dGhfdGltZSI6MTU1NDEzMTgyMCwiaWRwIjoibG9jYWwiLCJwcm9maWxlIjoiQWRtaW5pc3RyYXRvciIsImVtYWlsIjoiYWRtaW5AY2luY2h5LmNvIiwicm9sZSI6IkNpbmNoeSBVc2VyIEFjY291bnQiLCJpZCI6ImFkbWluIiwic2NvcGUiOlsianNfYXBpIl0sImFtciI6WyJjdXN0b20iXX0.N7drAlvtFiQoN4njs1rd5ZnTvJ_x8ZEnUEi6G1GjR4FS5FyS4hC6xdsT-Zhn1yRJQMkI2HA7HMPWwjsfkZ0IlBwuC25ECkGhbjv7DlK6baHQIkqeB0aTB9aDZSxWfDhV66O0dhby6EIEa4YuGspyjQMsDpx_LimmE9alfsUU-608944ZZkS6lBJlJ9LFCC5hYKARQIMZavrftz0tFUBsDU0T2fHpLNGo5GGwG1f9jUZTWTu7s3C05EsgboW3scUfDzjS_Wf55ExwhopIg9SD6ktHYYNRaCPtfMhU-e43l6a2LH-XrmP7OfoxJP2bvTMcvQCQWUEizKHuxKLl-ehWBw",
    "expires_in": 3600,
    "token_type": "Bearer"
}

400: For invalid parameters, a 400 error will be returned with the following JSON response with a description of the error.

Example:
{
  "error": "invalid_grant",
  "error_description": "Invalid username or password"
}

2.9 /api/getstsauthorityuri

2.10 /api/jobs

POST: https://<Connections-URL>/api/jobs

Request Header Parameters

Body Parameters

Options - JSON Structure

 {
     "paramValues": {
       "<name of parameter in sync>": {
         "isFile": <set to true if this parameter corresponds to a file, otherwise set it to false>,
         "value": "<value of the parameter, if the parameter corresponds to a file, then it should match the file name that was uploaded>"
       }
     },
     "server": "<cinchy url without protocol>",
     "useHttps": <true or false, depending on the server's protocol>,
     "userId": "<username of the user you want to run the data sync as, leave as null if you want to run it as the user associated with the access token used in the Authorization header>",
     "password": "<password of the user you want to run the data sync as, leave as null if you want to run it as the user associated with the access token used in the Authorization header>",
     "model": "Cinchy",
     "feed": "<name of the data sync configuration>",
     "batchsize": <Set to 5000 for the default behaviour - the size of the batches when performing inserts/updates/deletes in the target, set to 5000 if you want the default behaviour>,
     "retrievalbatchsize": <Set to 5000 for the default behaviour -  this is the size of the batches when retrieving records from the source to process>,
     "writeToFile": <Set to true for the default behaviour - This will ensure it will write records to temp files on disk to save memory on larger syncs. If this is set to false, it'll store source records in memory, which will be faster for smaller data syncs>
   }

Endpoint Example

curl --request POST \
  --url <cinchy.com>/connections/api/jobs \
  --header 'Authorization: Bearer xxx’ \
  --header 'Content-Type: multipart/form-data' \
  --form files=@/Users/shawn/Worker.zip \
  --form 'options={ "paramValues": {  "file": {   "isFile": true,   "value": "FBL costing model with upload to SF.xlsx"  } }, "server": "sandbox.cinchy.net/technicalsales", "useHttps": true, "password": null, "userId": null, "model": "Cinchy", "feed": "Excel -> Cinchy", "batchsize": 5000, "retrievalbatchsize": 5000, "writeToFile": true}'

Responses

200: The request was successful.

{
   executionId = "<The Cinchy Id of the data sync execution, it will correspond go the Cinchy Id of the newly created record in the [Cinchy.[Execution Log] table>"
}

400: The request could not be completed.

{
   status = <Http status code>,
   message = "<Error message>"
}

You can execute CQL directly without creating a Saved Query using the below endpoint:

POST: https://<Cinchy Web URL>/API/ExecuteCQL

Query Parameters:

Header Parameters:

Responses:

• 200 (OK)

Parameters

To pass in parameters in your executeCQL, you will need to pass in sets of parameters in the following format. So if you have one parameter then you would pass in 3 query parameters beginning with Parameters[0]. , and if you have a second parameter you would include an additional 3 query parameters beginning with Parameters[1]. .

Table of Contents

1. Authentication Methods

The APIs in Cinchy use bearer token based authentication. This token is issued by the Cinchy SSO using the OAuth 2.0 Resource Owner Password Flow and can be retrieved for any Cinchy User Account or SSO Account. API calls made using a bearer token will run under the privileges of the authenticated user, and are driven by the configured data level access controls. You must include the token in each request in the Authorization header.

APIs that are dynamically generated through a Saved Query in Cinchy also allow for basic authentication. In this case, the url to the saved query is different, it will be:

https://<Cinchy Web URL>/BasicAuthAPI/MyDomain/MyQuery

The Resource Owner Password Flow uses a combination of a client id, client secret, username, and password to authenticate both the calling application as well as the user. To get started with, you must register a client in Cinchy. You should use a different client id for each calling application to distinguish activity from each source.

If you are on Cinchy v5.5+, you can also use Cinchy's Personal Access Token based authentication. These can be used in APIs the same way bearer tokens can be. Review the documentation here for information on generating PATs.

1.1 Registering a New Client

Clients are managed in the Integrated Clients table within the Cinchy domain. To register a client, create a new record in this table. In a fresh install, only members of the Cinchy Administrators group will have access to perform this function.

Below is a description of the value that should be used for each column in the Integrated Clients table.

1.2 API Method Table

POST: Bearer Token Request

https://<Cinchy SSO URL>/identity/connect/token

The Post Request will return an access token which can be used to access Cinchy APIs.

Header Parameters:

Body Parameters:

Responses:

• 200: The request is successful

// {
    "access_token": "eyUzI1NiIsImtpZCI6IkE4M0UwQTFEQTY1MzE0NkZENUQxOTFDMzRDNTQ0RDJDODYyMzMzMzkiLCJ0eXAiOiJKV1QiLCJ4NXQiOiJxRDRLSGFaVEZHX1YwWkhEVEZSTkxJWWpNemsifQ.eyJuYmYiOjE1NTQxMzE4MjAsImV4cCI6MTU1NDEzNTQyMCwiaXNzIjoiaHR0cDovL2xvY2FsaG9zdDo4MDgxIiwiYXVkIjpbImh0dHA6Ly9sb2NhbGhvc3Q6ODA4MS9yZXNvdXJjZXMiLCJqc19hcGkiXSwiY2xpZW50X2lkIjoiYWJjIiwic3ViIjoiMSIsImF1dGhfdGltZSI6MTU1NDEzMTgyMCwiaWRwIjoibG9jYWwiLCJwcm9maWxlIjoiQWRtaW5pc3RyYXRvciIsImVtYWlsIjoiYWRtaW5AY2luY2h5LmNvIiwicm9sZSI6IkNpbmNoeSBVc2VyIEFjY291bnQiLCJpZCI6ImFkbWluIiwic2NvcGUiOlsianNfYXBpIl0sImFtciI6WyJjdXN0b20iXX0.N7drAlvtFiQoN4njs1rd5ZnTvJ_x8ZEnUEi6G1GjR4FS5FyS4hC6xdsT-Zhn1yRJQMkI2HA7HMPWwjsfkZ0IlBwuC25ECkGhbjv7DlK6baHQIkqeB0aTB9aDZSxWfDhV66O0dhby6EIEa4YuGspyjQMsDpx_LimmE9alfsUU-608944ZZkS6lBJlJ9LFCC5hYKARQIMZavrftz0tFUBsDU0T2fHpLNGo5GGwG1f9jUZTWTu7s3C05EsgboW3scUfDzjS_Wf55ExwhopIg9SD6ktHYYNRaCPtfMhU-e43l6a2LH-XrmP7OfoxJP2bvTMcvQCQWUEizKHuxKLl-ehWBw",
    "expires_in": 3600,
    "token_type": "Bearer"
}

• 400: For invalid parameters, a 400 error will be returned with the following JSON response with a description of the error.

  • Example:

  Copy

  {
    "error": "invalid_grant",
    "error_description": "Invalid username or password"
  }