> Documentation index: [/llms.txt](/llms.txt). Every page as one file: [/llms-full.txt](/llms-full.txt).

# Configure the semantic layer

Configure the semantic layer [#configure-the-semantic-layer]

Generate a database connection's semantic layer, then describe its tables and add business definitions and KPIs.

Agents that use the connection get the layer in their prompt: business names, relationships, definitions and KPIs instead of raw names (**Orders** for `public.tbl_ord`). `/query` refuses tables with `is_included: false`.

Endpoints [#endpoints]

Base URL: `/api/v1/data-sources/connections/{connection_id}/semantic-layer`.

| Method             | Path                           | Body                                               | Does                                                                   | Auth          |
| ------------------ | ------------------------------ | -------------------------------------------------- | ---------------------------------------------------------------------- | ------------- |
| `GET`              | `/`                            |                                                    | Read the layer and its `status`                                        | Key or bearer |
| `POST`             | `/initialize`                  | none                                               | Regenerate the layer from the database; returns `status: "generating"` | Key or bearer |
| `PATCH`            | `/tables/{table_name}`         | `display_name`, `purpose`, `domain`, `is_included` | Describe or exclude one table                                          | Key or bearer |
| `GET` · `POST`     | `/definitions`                 | `name`, `description`, `domain`, `conditions`      | List · add business terms                                              | Key or bearer |
| `PATCH` · `DELETE` | `/definitions/{definition_id}` | `name`, `description`, `domain`, `conditions`      | Edit · delete one                                                      | Key or bearer |
| `GET` · `POST`     | `/kpis`                        | `name`, `description`                              | List · add KPIs                                                        | Key or bearer |
| `PATCH` · `DELETE` | `/kpis/{kpi_id}`               | `name`, `description`                              | Edit · delete one                                                      | Key or bearer |

* Reads need Read connections; writes need Edit connections (`connection:edit`).
* On an `oauth2` connection only its owner can write; others get `403 Only the connection owner can modify its semantic layer.`
* Use the paths exactly as listed; only `GET /` ends with a slash. An added or missing slash returns a `307` redirect.
* `DELETE` returns `204` even when the ID does not exist.

Layer fields [#layer-fields]

| Field                    | Notes                                                                                                                         |
| ------------------------ | ----------------------------------------------------------------------------------------------------------------------------- |
| `status`                 | `generating`, `ready` or `failed`. A `generating` older than 15 minutes reads as `failed`                                     |
| `config.tables[]`        | `name` (as `/metadata` names it, e.g. `public.tbl_ord`), `display_name`, `purpose`, `domain`, `is_included`, `column_samples` |
| `config.relationships[]` | `from_table`, `to_table`, `from_columns`, `to_columns`, `cardinality`, `join_type`, `confidence`, `when_to_use`               |
| `config.definitions[]`   | `id`, `name`, `description`, `domain`, `conditions[]`, `source`                                                               |
| `config.kpis[]`          | `id`, `name`, `description`, `formula`, `sql`, `tables`, `source`                                                             |

`source` is `ai-generated`, `manual` (the default for what you add) or a label you send.

Generate the layer [#generate-the-layer]

A new connection with `connector_type` `database` and category `data_source` generates its layer automatically.

1. To regenerate it, send `POST …/semantic-layer/initialize`. Optional `?sample_columns_per_table=` (0–200, default 20; 0 turns off value sampling):

   ```bash
   curl -X POST "$CENTRAL_HUB_URL/api/v1/data-sources/connections/123/semantic-layer/initialize" \
     -H "X-API-Key: $ALO_API_KEY" \
     -H "X-Space-Slug: $WORKSPACE_SLUG"
   ```

   The response returns at once with `"status": "generating"` and the current `config`.

2. Poll `GET …/semantic-layer/` until `status` is `ready` or `failed`:

   ```bash
   curl "$CENTRAL_HUB_URL/api/v1/data-sources/connections/123/semantic-layer/" \
     -H "X-API-Key: $ALO_API_KEY" \
     -H "X-Space-Slug: $WORKSPACE_SLUG"
   ```

   ```json
   {
     "connection_id": 123,
     "status": "ready",
     "config": {
       "tables": [
         {
           "name": "public.tbl_ord",
           "display_name": "Orders",
           "purpose": "Records order transactions, including the purchasing customer, transaction amount, and order creation date.",
           "domain": "Sales",
           "is_included": true,
           "column_samples": {"amount": ["80.00", "120.50", "42.00"]}
         }
       ],
       "relationships": [
         {"from_table": "public.customers", "to_table": "public.tbl_ord", "from_columns": ["id"], "to_columns": ["cust_id"], "cardinality": "1:N", "join_type": "LEFT", "confidence": "high", "when_to_use": "To join order transactions with customer profile and demographic information."}
       ],
       "definitions": [],
       "kpis": [],
       "status": "ready",
       "generated_at": "2026-10-13T19:38:46.075462+00:00"
     }
   }
   ```

Regenerating rebuilds `tables`: your `display_name`, `purpose`, `domain` and `is_included` edits are lost, so excluded tables are included again. Definitions, KPIs and relationships stay; new AI-generated ones are added when their name, or table pair, is not taken. If no table is readable, `status` becomes `failed`.

Describe or exclude a table [#describe-or-exclude-a-table]

1. Find the table's exact `name` in `config.tables[]`; for PostgreSQL it is schema-qualified.
2. Send only the fields you change. `is_included: false` hides the table from agents and from `/query`:

   ```bash
   curl -X PATCH "$CENTRAL_HUB_URL/api/v1/data-sources/connections/123/semantic-layer/tables/public.tbl_ord" \
     -H "X-API-Key: $ALO_API_KEY" \
     -H "X-Space-Slug: $WORKSPACE_SLUG" \
     -H "Content-Type: application/json" \
     -d '{"display_name": "Orders", "purpose": "Customer purchase transactions", "domain": "Sales", "is_included": true}'
   ```

   The response is the whole layer.

Add a business definition [#add-a-business-definition]

1. Send `name` and `description`, with optional `domain` and `conditions`. A condition's `operator` is `equals`, `not_equals`, `contains`, `greater_than`, `less_than`, `in` or `not_in`:

   ```bash
   curl -X POST "$CENTRAL_HUB_URL/api/v1/data-sources/connections/123/semantic-layer/definitions" \
     -H "X-API-Key: $ALO_API_KEY" \
     -H "X-Space-Slug: $WORKSPACE_SLUG" \
     -H "Content-Type: application/json" \
     -d '{
       "name": "Active Customer",
       "description": "A customer with an order in the last 90 days",
       "domain": "Sales",
       "conditions": [{"field": "tbl_ord.created_at", "operator": "greater_than", "value": "NOW() - INTERVAL '"'"'90 days'"'"'"}]
     }'
   ```

2. Keep the `id` from the `201` response to edit or delete it later:

   ```json
   {
     "id": "06a5349b-05bf-49c3-aaf4-ec8aad1aac03",
     "name": "Active Customer",
     "description": "A customer with an order in the last 90 days",
     "domain": "Sales",
     "conditions": [{"field": "tbl_ord.created_at", "operator": "greater_than", "value": "NOW() - INTERVAL '90 days'"}],
     "source": "manual"
   }
   ```

Add a KPI [#add-a-kpi]

1. Send `name` and `description`. The SQL is always AI-generated from them and the layer: any `formula`, `sql` or `tables` you send is replaced:

   ```bash
   curl -X POST "$CENTRAL_HUB_URL/api/v1/data-sources/connections/123/semantic-layer/kpis" \
     -H "X-API-Key: $ALO_API_KEY" \
     -H "X-Space-Slug: $WORKSPACE_SLUG" \
     -H "Content-Type: application/json" \
     -d '{"name": "Total Revenue", "description": "Sum of all order amounts"}'
   ```

2. Read `formula`, `sql` and `tables` from the `201` response. When generation fails they are `null`, `null` and `[]`:

   ```json
   {
     "id": "7e7e537c-6fc8-41f0-afcf-77c72c1c7d4b",
     "name": "Total Revenue",
     "description": "Sum of all order amounts",
     "formula": "SUM(tbl_ord.amount)",
     "sql": "SELECT SUM(amount) AS total_revenue FROM public.tbl_ord",
     "tables": ["public.tbl_ord"],
     "source": "manual"
   }
   ```

* `PATCH …/kpis/{kpi_id}` accepts only `name` and `description`; changing either generates the SQL again.
* KPI names are not unique: `POST …/kpis` adds a KPI even when one with that name exists. List them first with `GET …/kpis`.

Troubleshooting [#troubleshooting]

| Response                                                                                                  | Cause                                                | Fix                                                                       |
| --------------------------------------------------------------------------------------------------------- | ---------------------------------------------------- | ------------------------------------------------------------------------- |
| `404` `Table 'tbl_ord' not found in semantic layer`                                                       | `table_name` is not the exact `config.tables[].name` | Use the schema-qualified name, e.g. `public.tbl_ord`                      |
| `422` `Input should be 'equals', 'not_equals', 'contains', 'greater_than', 'less_than', 'in' or 'not_in'` | Unknown condition `operator`                         | Use one of those values                                                   |
| `403` `Only the connection owner can modify its semantic layer.`                                          | A write on another user's `oauth2` connection        | Ask the owner                                                             |
| `status` stays `failed`                                                                                   | No readable table, or generation stopped             | Check the credentials can read the tables, then `POST …/initialize` again |
| `404` `Definition '<id>' not found` · `KPI '<id>' not found`                                              | Wrong ID on `PATCH`                                  | List them with `GET …/definitions` or `GET …/kpis`                        |