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

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

• 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

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

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):

   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:

   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"

   {
     "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

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:

   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

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:

   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:

   {
     "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

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:

   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 []:

   {
     "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