Schema Joins

Define cross-schema joins for customer enrichment, segment overlap, and customer-360 reads with primary-key/foreign-key validation

/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, and an active/inactive status. Before persisting a join, the platform validates that both schemas exist for the tenant and that the named key fields exist on each 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
  }'

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.

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",
    "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.

name · string · required

Display name. Must be at least 1 character.

primarySchemaId · string · required

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

primaryKey · string · required

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

foreignSchemaId · string · required

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

foreignKey · string · required

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

joinType · string · default: "left"

"left" (default) or "inner".

autoEnrich · 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.

Response

201 Created with the created join row.

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.

id · string · required

Id of the join to update.

name · string

New display name.

primarySchemaId · string

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

primaryKey · string

New primary key field name.

foreignSchemaId · string

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

foreignKey · string

New foreign key field name.

joinType · string

"left" or "inner".

autoEnrich · boolean

Toggle the auto-enrichment flag.

status · string

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

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

id · 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.

Related

• 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.