# APIs and SQL access

> Programmatic access to a Konstrukt tenant — the REST API and its OAuth client-credentials authentication, the Kex query endpoint, and the external SQL views with point-in-time queries.

A Konstrukt tenant can be read and written by other systems through three machine interfaces: the **REST API**, the **Kex endpoint** of that API (run a Kex script, get JSON back), and **external SQL views** for BI and data-warehouse tools. AI agents use a fourth interface, the MCP server, described in [AI agents and MCP](ai-agents.html). Inbound file imports are covered in [Data integration](integration.html).

| Interface | Direction | Protocol | Typical consumer |
|---|---|---|---|
| REST API | Read and write | HTTPS, JSON, OAuth 2.0 | Integration services, scripts |
| Kex endpoints (`/api/v1/kex…`) | Validate, read and write Kex scripts (results as JSON) | HTTPS, OAuth 2.0 | Applications that need model-aware queries |
| Import endpoint (`/api/v1/import/{key}`) | Write | HTTPS, OAuth 2.0 | See [Data integration](integration.html#rest) |
| External SQL views | Read | T-SQL over TCP 1433 | BI tools, data warehouses |
| MCP server (`/api/mcp`) | Read and write | Model Context Protocol | AI agents |

## REST API

Each tenant serves its API on its own host: `https://{tenant}.konstrukt.app/api/...`.

The endpoint reference is generated from the running service and is always current. Every tenant serves it at `/api/documentation/index.html`, for example [https://acme.konstrukt.app/api/documentation/index.html](https://acme.konstrukt.app/api/documentation/index.html). This page does not duplicate it.

| Resource | Path |
|---|---|
| Interactive reference (Swagger UI) | `/api/documentation/index.html` |
| OpenAPI 3.0 specification | `/api/v1/openapi.json` (no sign-in required) |
| OAuth 2.0 server metadata | `/.well-known/oauth-authorization-server` |

All operations are under `/api/v1`. There is currently one API version.

### Authentication

The API uses **OAuth 2.0**. The token endpoint supports two grant types:

| Grant | Used by |
|---|---|
| `client_credentials` | Integrations and scripts, with an API key |
| `authorization_code` (with PKCE) | Interactive clients that sign in through the browser, such as MCP clients |

For integrations:

1. **Create an API key** in the tenant under Integration → API keys (*API-nycklar*). Only administrators can create keys. The key is created for a chosen user and carries that user's permissions. The **Key ID** and **API key** are shown **once**, when the key is created. Store them then, because an existing key cannot be displayed again. Keys are valid for one year by default.
2. **Request an access token.**

| Property | Value |
|---|---|
| Endpoint | `POST https://{tenant}.konstrukt.app/api/v1/token` |
| Content type | `application/x-www-form-urlencoded` |
| `grant_type` | `client_credentials` |
| `client_id` | Key ID |
| `client_secret` | API key |

```bash
curl -X POST https://{tenant}.konstrukt.app/api/v1/token \
     -d "grant_type=client_credentials" \
     -d "client_id={key-id}" \
     -d "client_secret={api-key}"
```

3. **Call the API** with the returned `access_token` as a bearer token:

```
Authorization: Bearer {access_token}
```

A minimal Python client:

```python
import requests

BASE_URL = "https://{tenant}.konstrukt.app"

token = requests.post(f"{BASE_URL}/api/v1/token", data={
    "grant_type": "client_credentials",
    "client_id": "{key-id}",
    "client_secret": "{api-key}",
}).json()["access_token"]

headers = {"Authorization": f"Bearer {token}", "Content-Type": "application/json"}
# Call any endpoint listed in /api/documentation/index.html with these headers.
```

An access token from `client_credentials` is valid for about **six hours**. The specification defines no refresh grant, so when a token expires, request a new one with the same credentials. Interactive clients that sign in with `authorization_code`, such as MCP clients, can choose a longer token lifetime, up to 30 days.

The API has no rate limits, and no rate-limit headers are returned. Batch work rather than sending many small calls, since every write triggers dependent calculations.

### Kex endpoints

The Kex endpoints run a [Kex](kex.html) script against the tenant and return the result as JSON. They give external systems the same query semantics as calculations and apps: implicit joins over shared dimensions, plan and reference selection (`from plan`, `from reference`), and point-in-time reads (`from history`).

| Method and path | Body | Returns |
|---|---|---|
| `POST /api/v1/kex/script/validate` | `{ "Script": "…" }` | Validation result |
| `POST /api/v1/kex/script/read` | `{ "Script": "…" }` | `{ "resultSets": [ { "name", "rows" } ] }` |
| `POST /api/v1/kex/script/write` | `{ "Script": "…" }` | Result sets, including insert, update and delete counts for each write |
| `GET /api/v1/kex?script=…` | — | A flat array of rows |

```json
POST /api/v1/kex/script/read
{ "Script": "Total = sum(Amount), Department where Month >= \"202401\" from plan 3" }
```

A script can return several result sets, one per `evaluate` or bare expression.

!!! warning "Writes go to the default plan"
    The Kex endpoints take no plan parameter. A script runs in the tenant's [default plan](concepts.html#plan): `from plan` changes where data is read from, not where a `write` goes. To work in another plan, start the script with [`use plan`](kex.html#use-plan). Test write scripts with `/validate` first.

### Import endpoint

`/api/v1/import/{key}` imports a file using an import configuration. It is one of three transports for the same configuration; see [Data integration](integration.html).

## External SQL views

For BI tools and data warehouses, Konstrukt exposes read-only **external views** in the `externalViews` schema of the tenant's SQL database.

### Connecting

Create an SQL integration account (*SQL-integration*) under Integration → Accounts (*Konton*):

| Setting | Value |
|---|---|
| Host | `sql.konstrukt.app` |
| Port | `1433` |
| User | Account-specific user ID |
| Password | Generated when the account is created |
| Network | Connections only from IP addresses on the account's allow-list |

The views are queried with T-SQL.

### Enable export {#active-export}

A dimension or value type only gets a view when **Enable export** (*Aktivera export*) is ticked for it. The switch is in the object's Integration side panel (*Integration* → *Exportera*), together with a ready-made statement with a copy button and a link to the SQL integration settings (*Gå till SQL-integration*):

```sql
select * from externalViews.d_Department where PlanId = 3
```

### View catalogue

| View | Content |
|---|---|
| `externalViews.Plan` | All plans: `PlanId`, `Name`, `IsActive`, `IsDefault` |
| `externalViews.Reference` | All references usable for value type data: `ReferencedId`, `PlanId`, `Name`, `Description` |
| `externalViews.vt_{ValueType}` | Plan data of one value type |
| `externalViews.vtr_{ValueType}` | Reference data of one value type (adds `ReferenceId`) |
| `externalViews.d_{Dimension}` | Dimension values of one dimension with their properties |
| `externalViews.Metadata` | Lists available views — for troubleshooting only; content may change |

By default the value type and dimension views contain data from **all active plans**, with a union of the dimensions (or properties) used across those plans. Konstrukt can configure the views per plan — for example only the default plan, a range of plans, or including archived plans.

### Value type views

`externalViews.vt_{ValueType}` has one column per dimension plus:

| Column | Meaning |
|---|---|
| `PlanId` | Plan of the row |
| `ValueTypeId` | Value type ID |
| `DecimalValue` | Value for numeric value types |
| `StringValue` | Value for text value types (instead of `DecimalValue`) |
| `DateValue` | Value for date/time value types, in UTC |
| `CalculationId` | ID of the calculation that wrote the row; empty for non-calculated rows |
| `EditTime` | Last time the value changed |
| `EditState` | Type of the last change (below) |

| `EditState` | Meaning |
|---|---|
| `0` | Suggested — created in the admin interface or imported through an integration |
| `10` | Set by a user in an app |
| `30` | Set by a calculation |

```
PlanId  ValueTypeId  Year  Month  DecimalValue
3       Amount       2024  10     99500.00
3       Amount       2024  11     99500.00
4       Amount       2025  1      100250.00
```

### Dimension views

`externalViews.d_{Dimension}` has `PlanId`, `DimensionId`, `Code`, `Name`, `EditTime`, and two columns per property: `{Property}_Code` and `{Property}_Name`.

```
PlanId  DimensionId  Code  Name       Region_Code  Region_Name
3       Department   100   Sales      N            North
3       Department   200   Marketing  S            South
```

### Point-in-time queries

The views are temporal: the state of the data at any earlier moment can be queried with `for system_time as of` (UTC).

```sql
-- Amount as it stood on 1 June 2025, 00:00 UTC
select * from externalViews.vt_Amount for system_time as of '2025-06-01 00:00:00'
```

Point-in-time references do not appear in the `vtr_` views — they store a moment, not data. Query the `vt_` view with `for system_time as of` at the reference's timestamp instead. See [References and history](references-history.html).

### Compatibility and other views

- Older databases expose an earlier version of the views in the `dbo` schema with the prefix `vw_external_`. Differences: `PlanLineId` is removed from value type views, `DimValueId`/`Id` from dimension views, and property names are always included.
- Additional views (for example apps, app statuses or access groups) can be created for specific needs. They are not guaranteed to be as stable as the views listed above.

!!! warning "Sensitive value types are visible in SQL views"
    The SQL integration account is one account per tenant and sees all data. Values of sensitive value types (`isSensitive`) are **not** hidden in the `vt_` views. See [Security](security.html#sensitive-value-types).

## Choosing an interface

| Need | Use |
|---|---|
| Nightly load of actuals from an ERP | Import configuration + storage, SFTP or KDI ([Data integration](integration.html)) |
| Immediate feedback on each import | `/api/v1/import/{key}` |
| BI dashboards on plan data, including history | External SQL views |
| An application that needs aggregated, model-aware answers | Kex endpoint |
| Building or changing the model itself | MCP server ([AI agents and MCP](ai-agents.html)) or the admin GUI |

!!! agent "For agents"
    MCP clients do not need the REST API: `execute_read_script` runs the same Kex as `/api/v1/kex`, and the import tools cover the import endpoint. Use the REST API only when generating code for a customer's own integration. Always point to `https://{tenant}.konstrukt.app/api/documentation/index.html` for endpoint details instead of guessing request shapes.

## Related

- [Data integration](integration.html)
- [Kex](kex.html)
- [References and history](references-history.html)
- [Security and access](security.html)
- [AI agents and MCP](ai-agents.html)
