Schema Joins - KaireonAI

Schemas list view in the Data module where joins are configured — The Schemas page where joins are configured.

/api/v1/schema-joins is the CRUD surface for join definitions — declarative links between two registered schemas on a primary-key/foreign-key pair. The API accepts the same join spec the visual schema-joins builder produces in the UI. Joins drive customer-360 enrichment, segment overlap calculations, and reach-estimate queries that span more than one schema.

What it does

Each join definition records the primary and foreign schema, the key field on each side, the join type, the auto-enrich flag, an active/inactive status, and an optional list of aggregations — one-to-many rollup rules that collapse multi-row joins into scalar decision-context values. Before persisting a join, the platform validates that both schemas exist for the tenant, that the named key fields exist on each schema, and that every aggregated field exists on the foreign schema. Invalid combinations are rejected with a 400 and a precise error message rather than allowed to fail at query time. The route handles four operations:

List — return every join for the tenant, each enriched with the display names of the two schemas it links.
Create — accept a new join after schema and field validation.
Update — change fields on an existing join, re-validating any key combinations that changed.
Delete — hard-delete a join.

Quick start

Create a join from customers to accounts so customer reads automatically pull account fields:

curl -X POST https://playground.kaireonai.com/api/v1/schema-joins \
  -H "Content-Type: application/json" \
  -H "X-API-Key: krn_your_api_key" \
  -H "X-Tenant-Id: 5a9904b9-..." \
  -d '{
    "name": "Customer accounts",
    "primarySchemaId": "schema_customers",
    "primaryKey": "customer_id",
    "foreignSchemaId": "schema_accounts",
    "foreignKey": "customer_id",
    "joinType": "left",
    "autoEnrich": true,
    "aggregations": [
      { "field": "balance", "aggregator": "sum", "alias": "total_balance" },
      { "field": "*", "aggregator": "count" }
    ]
  }'

When a customer has three accounts, this join contributes accounts.total_balance (the summed balances) and accounts.count (3) to the assembled decision context instead of silently picking one row.

How it works

Authentication and roles

Listing joins requires the viewer, editor, or admin role. Create, update, and delete each require editor or admin. Every handler binds the request to a tenant and scopes all database operations by tenant id — no cross-tenant joins are possible.

Field validation

On create, the platform validates each side once: it confirms the schema exists for the tenant, then confirms the named field exists on that schema. The error messages are precise:

Schema {id} not found — when the schema id does not exist for the tenant.
Field "{name}" not found in schema "{schemaName}" — when the schema exists but the named key column is missing.

On update, validation only runs for sides whose schema id or key field actually changed.

One-to-many rollups (`aggregations`)

A join from customers to accounts typically matches multiple foreign rows per customer. The optional aggregations array configures how those rows collapse into scalar values in the assembled decision context (the <entity>.<key> namespace read by decisioning gates, formulas, and enrichment):

"aggregations": [
  { "field": "balance", "aggregator": "sum", "alias": "total_balance" },
  { "field": "*", "aggregator": "count" }
]

Each entry has:

field (required) — a column on the foreign schema. count also accepts "*" (or an empty string) for a plain row count; every other aggregator requires a real column.
aggregator (required) — one of sum, count, avg, min, max, any, all, first.
alias (optional) — the output key under the entity namespace. Defaults to <field>_<aggregator> (e.g. balance_sum), or count for a count on "*".

Semantics, as implemented by the decision-context assembler:

Aggregator	Result	Empty row set
`sum`	Sum of numeric values	`0`
`count`	Row count for `"*"`; non-null count for a named field	`0`
`avg`	Mean of numeric values	`null`
`min` / `max`	Numeric minimum / maximum	`null`
`any`	`true` if any row’s value is truthy	`false`
`all`	`true` if every row’s value is truthy (and at least one row)	`false`
`first`	The first row’s value	`null`

Validation on create and update rejects, with a 400 and a precise message:

an aggregated field that is not a registered column on the foreign schema — Aggregation field "{name}" not found in schema "{schemaName}";
"*" (or an empty field) with any aggregator other than count — Aggregation field is required for aggregator "{name}" (only "count" accepts "*");
an unknown aggregator value (schema validation).

Updates that change foreignSchemaId without resending aggregations re-validate the stored aggregations against the new schema, so a join can never end up with rollups pointing at columns that don’t exist. Default rollup. When a join has no aggregations (the default, []), a one-to-many match falls back to <entity>.count plus the first row’s scalar fields passed through as <entity>.<field> — and the assembler records a warning about the implicit first-row reduction. Configure aggregators to make the rollup explicit. The same rollups are configurable visually in the Aggregations section of the join form at Data → Schema Joins: pick a foreign-schema column, an aggregator, and an optional alias per row. The join list shows a compact summary of each join’s rollups (e.g. sum(balance)→total_balance, count), or “Default rollup” when none are configured.

List enrichment

The list response attaches primarySchemaName and foreignSchemaName to each row, using the schema’s display name first, falling back to its internal name, then to "Unknown". This avoids a second round trip per row from the UI.

Reference

`GET /api/v1/schema-joins`

List all schema joins for the tenant, ordered by createdAt descending.

Response

[
  {
    "id": "join_001",
    "tenantId": "5a9904b9-...",
    "name": "Customer accounts",
    "primarySchemaId": "schema_customers",
    "primaryKey": "customer_id",
    "foreignSchemaId": "schema_accounts",
    "foreignKey": "customer_id",
    "joinType": "left",
    "autoEnrich": true,
    "status": "active",
    "aggregations": [
      { "field": "balance", "aggregator": "sum", "alias": "total_balance" },
      { "field": "*", "aggregator": "count" }
    ],
    "createdAt": "2026-04-30T10:00:00.000Z",
    "updatedAt": "2026-04-30T10:00:00.000Z",
    "primarySchemaName": "Customers",
    "foreignSchemaName": "Accounts"
  }
]

Status codes

Code	When
200	Returns the array (possibly empty)
401 / 403	Caller fails authentication or role check
500	Unexpected DB error

`POST /api/v1/schema-joins`

Create a new join after schema/field validation.

Request body

The platform validates the body and rejects invalid join specs with 400.

string

required

Display name. Must be at least 1 character.

string

required

Id of the primary schema. Must belong to the tenant.

string

required

Field name on the primary schema. Must exist as a registered field on the primary schema.

string

required

Id of the foreign schema. Must belong to the tenant.

string

required

Field name on the foreign schema. Must exist as a registered field on the foreign schema.

string

default:"left"

"left" (default) or "inner".

boolean

default:"true"

When true, customer-360 reads and enrichment stages auto-pull this join. When false, the join exists in the catalog but is opt-in per query.

array

Optional one-to-many rollup rules: [{ field, aggregator, alias? }]. field must exist on the foreign schema ("*" allowed only with count); aggregator is one of sum, count, avg, min, max, any, all, first. Defaults to [] (default rollup). See One-to-many rollups.

Response

201 Created with the created join row (including the stored aggregations).

Status codes

Code	When
201	Created
400	Invalid JSON body
400	Schema/field validation failure (with precise message)
401 / 403	Caller fails authentication or role check
500	Unexpected DB error

`PUT /api/v1/schema-joins`

Update fields on an existing join. Re-runs validation for any side whose schema or key changed.

Request body

The platform validates the body and rejects invalid updates with 400.

string

required

Id of the join to update.

string

New display name.

string

New primary schema id. When set, key validation re-runs against primaryKey.

string

New primary key field name.

string

New foreign schema id. When set, key validation re-runs against foreignKey.

string

New foreign key field name.

string

"left" or "inner".

boolean

Toggle the auto-enrichment flag.

string

"active" or "inactive". inactive joins stay in the catalog but are skipped by enrichment paths.

array

Replace the join’s one-to-many rollup rules. The full array is replaced (not merged); send [] to clear all aggregations and fall back to the default rollup. Validated against the effective foreign schema. See One-to-many rollups.

Response

200 OK with the updated join row.

Status codes

Code	When
200	Updated
400	Invalid JSON body or validation failure
401 / 403	Caller fails authentication or role check
404	Join not found for tenant
500	Unexpected DB error

`DELETE /api/v1/schema-joins?id={joinId}`

Hard-delete a join. The id is read from the query string, not the path.

Query parameters

string

required

Id of the join to delete.

Response

{ "deleted": true }

Status codes

Code	When
200	Deleted
400	Missing `id` query parameter
401 / 403	Caller fails authentication or role check
404	Join not found for tenant
500	Unexpected DB error

Required headers

Header	Required	Purpose
`Content-Type`	POST / PUT only	`application/json`
`X-API-Key`	Yes (one of the two)	API key (`krn_…`)
`X-Tenant-Id`	Yes (one of the two)	Direct tenant id

Honest limits

The route does not enforce a uniqueness constraint on { primarySchemaId, primaryKey, foreignSchemaId, foreignKey }. Operators can register multiple joins with the same key pair (e.g., one active and one inactive) — the consumer-side enrichment code picks the first active row it finds.
joinType is restricted to "left" and "inner". Right and outer joins are not supported in V1 — enforce them by inverting the primary/foreign relationship instead.
Deletes are unconditional — no foreign-key guard prevents removing a join that an active enrichment stage relies on. Callers should set status: "inactive" for a phased deprecation rather than deleting in production.
The delete endpoint reads id from the query string, not a path param, to allow batch tools to pass the id outside the URL path.

Schemas — register the schemas referenced by primarySchemaId and foreignSchemaId.
Customers — Unified Profile — consumes auto-enrich joins for customer-360 reads.
Segment Overlap — reads cross-schema joins to compute audience intersections.
Reach Estimate — uses joins for cross-schema rule reach calculations.

​What it does

​Quick start

​How it works

​Authentication and roles

​Field validation

​One-to-many rollups (aggregations)

​List enrichment

​Reference

​GET /api/v1/schema-joins

​Response

​Status codes

​POST /api/v1/schema-joins

​Request body

​Response

​Status codes

​PUT /api/v1/schema-joins

​Request body

​Response

​Status codes

​DELETE /api/v1/schema-joins?id={joinId}

​Query parameters

​Response

​Status codes

​Required headers

​Honest limits

​Related

What it does

Quick start

How it works

Authentication and roles

Field validation

One-to-many rollups (`aggregations`)

List enrichment

Reference

`GET /api/v1/schema-joins`

Response

Status codes

`POST /api/v1/schema-joins`

Request body

Response

Status codes

`PUT /api/v1/schema-joins`

Request body

Response

Status codes

`DELETE /api/v1/schema-joins?id={joinId}`

Query parameters

Response

Status codes

Required headers

Honest limits

Related