# Squeeze API - specification of new endpoints for master data tables
## Background and motivation
Squeeze is used via the Business Central (BC) integration to maintain master data per company in Squeeze tables. A single Squeeze system can serve several hundred BC companies, where each company has its own record in a master data table – typically identified by a column such as `companyName` or `companyId`.
### Current problem
The existing batch endpoints under `/masterData/tables/{tableId}/rows/batch` do not sufficiently cover the use case:
| Endpoint | Behavior | Problem |
|---|
| `PUT /rows/batch` | Deletes **all** existing rows and replaces them completely | Not usable, because the data of all companies would be deleted. To update only one company, the data of all other companies would have to be sent as well. |
| `POST /rows/batch` | Adds new rows but does **not** update existing ones | Creates duplicates on repeated calls instead of updating existing records. |
| `PUT /rows/{rowId}` | Updates a single row | For thousands of records per company this is far too slow and generates thousands of individual API requests. |
| `DELETE /rows/{rowId}` | Deletes a single row | Also one request per row – unsuitable for bulk operations. |
### Required solution
Three new batch endpoints are needed:
1. **Batch upsert** (`PATCH /masterData/tables/{tableId}/rows/batch`) – insert or update records without deleting rows that are not affected.
2. **Batch delete by ID** (`POST /masterData/tables/{tableId}/rows/batch/delete`) – delete multiple rows by an ID list in a single request.
3. **Batch delete by filter** (`POST /masterData/tables/{tableId}/rows/batch/delete/filter`) – delete multiple rows by column filters in a single request.
---
## Endpoint 1: Batch upsert
### Overview
```
PATCH /masterData/tables/{tableId}/rows/batch
```
**Goal:** Insert or update multiple rows in a table in a single request (upsert), without affecting other rows.
**Semantics:** For each submitted row the following applies:
- If a row with the given identifier already exists → **update** the existing row.
- If no row with the given identifier exists yet → **insert** as a new row.
- All other rows in the table that are not included in the request remain **unchanged**.
---
### HTTP method
`PATCH` – because the request represents a partial change to the table content (not a complete replacement as with `PUT`).
---
### URL parameters
| Parameter | In | Type | Required | Description |
|---|
| `tableId` | path | integer | yes | ID of the master data table |
### Query parameters
| Parameter | Type | Required | Default | Description |
|---|
| `useExternalId` | boolean | no | `false` | Analogous to the existing single-row endpoints. If `true`, the `externalId` of each row is used as the key for the upsert match. If `false` (default), the internal row ID (auto-increment) is used as the key. |
| `skipErrors` | boolean | no | `false` | If `true`, faulty rows are skipped and the remaining rows are processed. If `false`, the entire batch is rolled back transactionally on an error (see the "Behavior on errors" section). |
**Note on the key modes:**
- **`useExternalId=false` (default):** Each row must contain the internal `id` (integer, auto-increment). The API checks by this ID whether the row exists. This mode is suitable when the internal Squeeze IDs are known.
- **`useExternalId=true`:** Each row must contain the `externalId` field (string). The API checks by this value whether a row exists. In the context of Business Central, the `externalId` is typically composed of a combination of **SystemId**, **table name**, and **company name**, making it globally unique across all companies.
If the respective key field (`id` or `externalId`) is missing in a row, that row is treated as an error.
---
### Request body
`Content-Type: application/json`
A JSON array of objects. Each object represents a row as a key-value map (column name → value). Depending on the `useExternalId` parameter, each row must contain either the `id` field (internal) or `externalId`.
**Example with `useExternalId=true` (BC use case):**
```
[
{
"externalId": "CRONUS|Item|27f3a1c2-...",
"companyCode": "CRONUS",
"articleNo": "1000",
"description": "Bicycle",
"price": "599.00"
},
{
"externalId": "CRONUS|Item|91b8d4f7-...",
"companyCode": "CRONUS",
"articleNo": "1001",
"description": "Helmet",
"price": "49.90"
}
]
```
**Example with `useExternalId=false` (default, internal ID):**
```
[
{
"id": 101,
"articleNo": "1000",
"description": "Bicycle",
"price": "599.00"
},
{
"articleNo": "9999",
"description": "New article",
"price": "12.50"
}
]
```
Rows without an `id` field are inserted as new records in the default mode (`useExternalId=false`). Rows with a known `id` are updated.
---
### Response
#### HTTP 200 – Success
Response format identical to `MasterDataUploadResponse` (already used for `PUT /rows/batch` and `POST /rows/batch`):
```
{
"error": false,
"totalLines": 2,
"errorLineAmount": 0,
"skipErrors": false,
"dataType": "json",
"errorLines": []
}
```
| Field | Type | Description |
|---|
| `error` | boolean | `true` if at least one error occurred |
| `totalLines` | integer | Total number of processed rows |
| `errorLineAmount` | integer | Number of faulty rows |
| `skipErrors` | boolean | Indicates whether errors were skipped |
| `dataType` | string | Data format of the input (`"json"`) |
| `errorLines` | string\[\] | List of faulty rows (as a string representation) |
Recommendation: It would be helpful if the response additionally distinguished between `insertedLines` and `updatedLines`, so that the caller receives feedback about the upsert effect:
```
{
"error": false,
"totalLines": 50,
"insertedLines": 5,
"updatedLines": 45,
"errorLineAmount": 0,
"skipErrors": false,
"dataType": "json",
"errorLines": []
}
```
#### HTTP 400 – Bad request
When a row is missing the required key field (with `useExternalId=false` the `id`, with `useExternalId=true` the `externalId`):
```
{
"message": "Row at index 2 is missing the required key field 'externalId'.",
"statusCode": 400
}
```
#### HTTP 404 – Table not found
```
{
"message": "Master data table with id 42 does not exist.",
"statusCode": 404
}
```
---
### Behavior on errors
- If `skipErrors=false` (default): The entire batch is processed **transactionally**. If an error occurs on a row, **all changes already made in the batch are rolled back**. The response contains a detailed error message with the index of the faulty row and the reason for the error:
```
{
"error": true,
"message": "Batch aborted: row at index 3 failed – column 'price' contains invalid value 'abc'. All changes have been rolled back.",
"statusCode": 422
}
```
- If `skipErrors=true`: Faulty rows are skipped and documented in `errorLines`. Correct rows are committed. No rollback takes place.
---
### Example call (BC use case with externalId)
```
PATCH /api/v2/masterData/tables/42/rows/batch?useExternalId=true&skipErrors=false
Content-Type: application/json
Authorization: Bearer
[
{ "externalId": "CRONUS|Item|27f3a1c2-...", "companyCode": "CRONUS", "articleNo": "1000", "description": "Bicycle", "price": "599.00" },
{ "externalId": "CRONUS|Item|91b8d4f7-...", "companyCode": "CRONUS", "articleNo": "9999", "description": "New article", "price": "12.50" }
]
```
**Expected result:**
- The row with `externalId = "CRONUS|Item|27f3a1c2-..."` already exists → `description` and `price` are updated.
- The row with `externalId = "CRONUS|Item|91b8d4f7-..."` does not exist yet → a new row is inserted.
- All other rows in the table remain unchanged.
---
---
## Endpoint 2: Batch delete by ID list
### Overview
```
POST /masterData/tables/{tableId}/rows/batch/delete
```
**Goal:** Delete multiple rows of a table by a list of internal IDs or external IDs in a single request.
---
### URL parameters
| Parameter | In | Type | Required | Description |
|---|
| `tableId` | path | integer | yes | ID of the master data table |
### Query parameters
| Parameter | Type | Required | Default | Description |
|---|
| `useExternalId` | boolean | no | `false` | If `true`, the IDs given in the body are interpreted as external IDs (analogous to the existing single-row endpoints). |
---
### Request body
`Content-Type: application/json`
```
{
"rowIds": ["abc-123", "def-456", "ghi-789"]
}
```
| Field | Type | Required | Description |
| `rowIds` | string\[\] | yes | List of the row IDs to delete. With `useExternalId=false`, internal IDs (integer as string); with `useExternalId=true`, external IDs (for example `"CRONUS|Item|27f3a1c2-..."`). |
---
### Response
#### HTTP 200 – Success
```
{
"deletedRows": 3
}
```
| Field | Type | Description |
|---|
| `deletedRows` | integer | Number of rows actually deleted |
#### HTTP 400 – Bad request
When `rowIds` is missing or is an empty array:
```
{
"message": "Request body must contain a non-empty 'rowIds' array.",
"statusCode": 400
}
```
#### HTTP 404 – Table not found
```
{
"message": "Master data table with id 42 does not exist.",
"statusCode": 404
}
```
---
### Example call: delete by internal ID
```
POST /api/v2/masterData/tables/42/rows/batch/delete
Content-Type: application/json
Authorization: Bearer
{
"rowIds": ["101", "102", "103"]
}
```
### Example call: delete by externalId (BC use case)
```
POST /api/v2/masterData/tables/42/rows/batch/delete?useExternalId=true
Content-Type: application/json
Authorization: Bearer
{
"rowIds": ["CRONUS|Item|27f3a1c2-...", "CRONUS|Item|91b8d4f7-..."]
}
```
**Expected result:** The specified rows are deleted. All other rows remain untouched.
---
---
## Endpoint 3: Batch delete by filter
### Overview
```
POST /masterData/tables/{tableId}/rows/batch/delete/filter
```
**Goal:** Delete all rows of a table that match one or more column filters – in a single request.
---
### URL parameters
| Parameter | In | Type | Required | Description |
|---|
| `tableId` | path | integer | yes | ID of the master data table |
---
### Request body
`Content-Type: application/json`
Filter structure analogous to the existing endpoint `POST /masterData/tables/{tableId}/rows/search`:
```
{
"filters": [
{
"columnName": "companyCode",
"operand": "=",
"searchValue": "CRONUS"
}
],
"isAndConnectedQuery": true
}
```
| Field | Type | Required | Description |
|---|
| `filters` | MasterDataColumnFilter\[\] | yes | List of column filters. Must contain at least one filter. |
| `isAndConnectedQuery` | boolean | no | `false` = filters are connected with OR, `true` = filters are connected with AND. Default: `false`. |
**MasterDataColumnFilter schema** (already present in the API):
```
{
"columnId": 5,
"columnName": "companyCode",
"operand": "=",
"searchValue": "CRONUS",
"lookupFieldFilter": false
}
```
| Field | Type | Description |
|---|
| `operand` | string | Comparison operator (for example `=`, `!=`, `LIKE`, `>`, `<`) |
| `columnId` | integer | ID of the column (alternative to `columnName`) |
| `columnName` | string | Name of the column (alternative to `columnId`) |
| `searchValue` | string | Search value |
| `lookupFieldFilter` | boolean | Controls the SQL join logic (see existing documentation) |
---
### Response
#### HTTP 200 – Success
```
{
"deletedRows": 127
}
```
| Field | Type | Description |
|---|
| `deletedRows` | integer | Number of rows actually deleted |
#### HTTP 400 – Bad request
When `filters` is missing, is an empty array, or the body is empty:
```
{
"message": "Request body must contain a non-empty 'filters' array.",
"statusCode": 400
}
```
**Security note:** An empty `filters` array `[]` is rejected with HTTP 400. An empty filter must under no circumstances lead to all rows of the table being deleted.
#### HTTP 404 – Table not found
```
{
"message": "Master data table with id 42 does not exist.",
"statusCode": 404
}
```
---
### Example call
```
POST /api/v2/masterData/tables/42/rows/batch/delete/filter
Content-Type: application/json
Authorization: Bearer
{
"filters": [
{
"columnName": "companyCode",
"operand": "=",
"searchValue": "CRONUS"
}
],
"isAndConnectedQuery": true
}
```
**Expected result:** All rows of table 42 where `companyCode = "CRONUS"` are deleted. Rows of other companies remain untouched.
---
---
## Summary of the new endpoints
| Endpoint | Method | Function |
|---|
| `/masterData/tables/{tableId}/rows/batch` | `PATCH` | Upsert: insert or update rows without deleting other rows |
| `/masterData/tables/{tableId}/rows/batch/delete` | `POST` | Batch deletion by a list of internal IDs or external IDs |
| `/masterData/tables/{tableId}/rows/batch/delete/filter` | `POST` | Batch deletion by column filters |
---
## Data types used (reference to existing schemas)
| Schema | Already present | Usage |
|---|
| `MasterDataUploadResponse` | yes | Response for PATCH upsert |
| `MasterDataColumnFilter` | yes | Filter in the request body of endpoint 3 |
| `KeyValueCollection` | yes | Single row as key-value pairs (internal) |