> For the complete documentation index, see [llms.txt](https://docs.systemsx.co.uk/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.systemsx.co.uk/sense/api-query-builder.md).

# API - Query Builder

<https://systemsx.co.uk/products/sense>

Production API URL:

The API is focused towards POST requests however these can be GET requests also.

Ensure you are using `x-www-form-urlencoded` or `json` when sending requests.

## Query a table

<mark style="color:green;">`POST`</mark> `https://api.sense-ai.co.uk/v4/{tableName?}`

Leave table empty to view all available tables and columns for selection.

**Body**

| Name         | Type              | Description                             |
| ------------ | ----------------- | --------------------------------------- |
| `api_key`    | string (required) | API Key for account                     |
| `per_page`   | number            | Default: 20, Max: 100                   |
| `page`       | number            | Default: 1                              |
| `type`       | string            | `first` (One) or `get` (Many - Default) |
| `select`     | array (required)  | Array of columns to select              |
| `conditions` | array             | Array of conditions (see below)         |

## Conditions

Conditions are how a query is built, for example a list of Calls made, but only ones that have a duration above 60 seconds would be;

```
['type' => 'where', 'column' => 'duration', 'operator' => '>', 'value' => 60]
```

Conditions are provided as an array allowing you to apply multiple conditions to a query.

Operators Supported: (=, !=, >, >=, <, <=)

<pre><code>// Basic conditions
['type' => 'where', 'column' => 'status', 'operator' => '=', 'value' => 'active'],
<strong>
</strong><strong>// IN conditions
</strong>['type' => 'where_in', 'column' => 'category_id', 'value' => [1, 2, 3, 4]],
['type' => 'where_not_in', 'column' => 'status', 'value' => ['deleted', 'banned']],

// BETWEEN conditions
['type' => 'where_between', 'column' => 'age', 'value' => [18, 65]],
['type' => 'where_not_between', 'column' => 'score', 'value' => [0, 10]],

// NULL conditions
['type' => 'where_not_null', 'column' => 'email_verified_at'],
['type' => 'where_null', 'column' => 'deleted_at'],

// LIKE conditions (Contact Support for access)
['type' => 'where_like', 'column' => 'name', 'value' => '%john%'],
['type' => 'where_not_like', 'column' => 'email', 'value' => '%temp%'],

// Date conditions
['type' => 'where_date', 'column' => 'created_at', 'operator' => '>=', 'value' => '2024-01-01'],
['type' => 'where_month', 'column' => 'created_at', 'value' => 12],
['type' => 'where_year', 'column' => 'created_at', 'value' => 2024],

// JSON conditions (if using JSON columns)
['type' => 'where_json_contains', 'column' => 'meta->tags', 'value' => 'booking'],
['type' => 'where_json_length', 'column' => 'meta->tags', 'operator' => '>', 'value' => 2],
</code></pre>

## Example Request

Within this request are are selecting the uuid, agent\_id and duration of calls within call\_history and returning only calls where the duration is ≥ (greater or equal to) 60 seconds

Example GET request:

```url
/v4/call_history
?api_key=APIKEY
&select[]=uuid
&select[]=agent_id
&select[]=duration
&conditions[0][type]=where
&conditions[0][column]=duration
&conditions[0][operator]=%3E=
&conditions[0][value]=60
```

Example POST request:

```powershell
curl -X POST \
  -H "Content-Type: application/json" \
  -d '{
    "api_key": "APIKEY",
    "select": ["uuid", "agent_id", "duration"],
    "conditions": [
      {
        "type": "where",
        "column": "duration",
        "operator": ">",
        "value": 60
      }
    ]
  }' \
  "/v4/call_history"
```

## Data Understanding

| Table          | Column           | Details                                 |
| -------------- | ---------------- | --------------------------------------- |
| `call_history` | `has_transcript` | <p>2 = Completed<br>30 = No Metrics</p> |

## Data Recommendations

| Type  | Description                                                                                                                   |
| ----- | ----------------------------------------------------------------------------------------------------------------------------- |
| Calls | We always recommend using `call_history` for polling calls and using `uuid` to pair with the `uuid` within `call_transcripts` |