> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kaireonai.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Schemas

> Define entity schemas that create real PostgreSQL tables. Manage fields with DDL side effects (ALTER TABLE) for customer, account, and custom entity types.

## GET /api/v1/schemas

List all schemas with their fields. Supports cursor-based pagination.

### Query Parameters

| Parameter | Type    | Default | Description                            |
| --------- | ------- | ------- | -------------------------------------- |
| `limit`   | integer | `50`    | Max results per page (capped at `100`) |
| `cursor`  | string  | —       | Cursor for pagination                  |

### Response

```json theme={null}
{
  "data": [
    {
      "id": "schema_001",
      "tenantId": "tenant_001",
      "name": "customers",
      "displayName": "Customers",
      "description": "Core customer entity",
      "tableName": "ds_customers",
      "entityType": "customer",
      "status": "active",
      "fields": [
        {
          "id": "field_001",
          "schemaId": "schema_001",
          "name": "email",
          "displayName": "Email",
          "dataType": "varchar",
          "length": 255,
          "precision": null,
          "scale": null,
          "isNullable": false,
          "isPrimaryKey": false,
          "isUnique": true,
          "isPredictor": false,
          "defaultValue": null,
          "description": "",
          "ordinal": 1
        }
      ],
      "createdAt": "2026-01-10T08:00:00.000Z",
      "updatedAt": "2026-01-10T08:00:00.000Z"
    }
  ],
  "pagination": {
    "total": 5,
    "hasMore": false,
    "limit": 50,
    "cursor": null
  }
}
```

***

## POST /api/v1/schemas

Create a new schema. This creates both a metadata record and a real PostgreSQL table via DDL.

<Warning>Creating a schema executes `CREATE TABLE` against the database. If the DDL fails, the metadata record is automatically rolled back.</Warning>

### Request Body

| Field              | Type   | Required | Description                                                                                                                                                                                                                                                                                                                        |
| ------------------ | ------ | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `name`             | string | Yes      | Schema name (auto-sanitized to lowercase alphanumeric + underscores)                                                                                                                                                                                                                                                               |
| `displayName`      | string | Yes      | Human-readable name                                                                                                                                                                                                                                                                                                                |
| `description`      | string | No       | Description                                                                                                                                                                                                                                                                                                                        |
| `entityType`       | string | No       | What this schema is: `"customer"`, `"account"`, or `"custom"`. Default: `"custom"`. The `entityType: "customer"` schema is loaded as the base `customer.*` namespace in every decision.                                                                                                                                            |
| `customPrimaryKey` | object | No       | Sugar for declaring a natural primary key column. Shape: `{ "name": "customer_id", "dataType": "varchar" }`. When set, the auto `id BIGSERIAL` column is skipped and the named column is promoted to a `fields[0]` entry with `isPrimaryKey: true`. Equivalent to passing it explicitly in `fields[]` — pick whichever is cleaner. |
| `fields`           | array  | No       | Initial field definitions (see field object below)                                                                                                                                                                                                                                                                                 |

<Note>To bring a **related** entity (accounts, orders, addresses) into decisions, don't flag it on the schema — create a [Schema Join](/api-reference/schema-joins) (Data → Schema Joins) with the customer schema as primary, the related schema as foreign, and `aggregations` (sum/count/avg/min/max/any/all/first) to roll up one-to-many rows.</Note>

### Field Object

| Field          | Type    | Default     | Description                    |
| -------------- | ------- | ----------- | ------------------------------ |
| `name`         | string  | —           | Column name                    |
| `displayName`  | string  | `name`      | Display label                  |
| `dataType`     | string  | `"varchar"` | PostgreSQL data type           |
| `length`       | integer | `null`      | Length for varchar/char types  |
| `precision`    | integer | `null`      | Precision for decimal types    |
| `scale`        | integer | `null`      | Scale for decimal types        |
| `isNullable`   | boolean | `true`      | Whether the column allows NULL |
| `isPrimaryKey` | boolean | `false`     | Primary key constraint         |
| `isUnique`     | boolean | `false`     | Unique constraint              |
| `defaultValue` | string  | `null`      | Default value expression       |
| `description`  | string  | `""`        | Field description              |

### Example

```bash theme={null}
curl -X POST https://playground.kaireonai.com/api/v1/schemas \
  -H "Content-Type: application/json" \
  -H "X-Tenant-Id: my-tenant" \
  -d '{
    "name": "customers",
    "displayName": "Customers",
    "entityType": "customer",
    "fields": [
      { "name": "email", "dataType": "varchar", "length": 255, "isUnique": true },
      { "name": "loan_amount", "dataType": "decimal", "precision": 12, "scale": 2 },
      { "name": "tenure_months", "dataType": "integer" }
    ]
  }'
```

#### Example with `customPrimaryKey` sugar

```bash theme={null}
curl -X POST https://playground.kaireonai.com/api/v1/schemas \
  -H "Content-Type: application/json" \
  -H "X-Tenant-Id: my-tenant" \
  -d '{
    "name": "accounts",
    "displayName": "Accounts",
    "entityType": "account",
    "customPrimaryKey": { "name": "account_id", "dataType": "varchar" }
  }'
```

This is equivalent to passing `fields: [{ "name": "account_id", "dataType": "varchar", "isPrimaryKey": true, "isNullable": false, "isUnique": true }]` directly — pick whichever is more ergonomic for your use case. When set, the table is **not** given the default `id BIGSERIAL` auto-key.

**Response:** `201 Created`

### Error Codes

| Code  | Reason                                                                                                                                                                                                                                                                      |
| ----- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `400` | Invalid body (missing `name`, invalid field definitions, etc.)                                                                                                                                                                                                              |
| `409` | A schema with the same `name` already exists in this tenant. The duplicate check runs **before** the playground entity-count limit, so a duplicate name returns `409 Conflict` even when the tenant is at its entity cap (rather than being masked by a `400` limit error). |

***

## PUT /api/v1/schemas

Update an existing schema's metadata. Only provided fields are changed. This does **not** modify the underlying PostgreSQL table structure -- use the field endpoints for DDL changes.

### Request Body

| Field         | Type   | Required | Description                                                    |
| ------------- | ------ | -------- | -------------------------------------------------------------- |
| `id`          | string | Yes      | Schema ID to update                                            |
| `displayName` | string | No       | Updated display name                                           |
| `description` | string | No       | Updated description                                            |
| `entityType`  | string | No       | Updated entity type (`"customer"`, `"account"`, or `"custom"`) |

**Response:** `200 OK` with the updated schema object including fields.

### Error Codes

| Code  | Reason                       |
| ----- | ---------------------------- |
| `400` | Missing `id` or invalid body |
| `404` | Schema not found             |

***

## DELETE /api/v1/schemas

Delete a schema and drop its backing PostgreSQL table.

### Query Parameters

| Parameter | Type   | Required | Description         |
| --------- | ------ | -------- | ------------------- |
| `id`      | string | Yes      | Schema ID to delete |

<Warning>This drops the underlying database table. All data in the table is permanently lost.</Warning>

**Response:** `204 No Content`

***

## GET /api/v1/schemas/fields

List the fields (columns) for a single schema. Used by the Schema Joins editor
and any other UI surface that needs a column list without pulling the full schema
object.

### Query Parameters

| Parameter  | Type   | Required | Description                     |
| ---------- | ------ | -------- | ------------------------------- |
| `schemaId` | string | Yes      | Schema ID whose fields you want |

**Response:** `200 OK` with an array of field objects, sorted by `ordinal`.

```json theme={null}
[
  {
    "id": "...",
    "schemaId": "...",
    "name": "customer_id",
    "displayName": "customer_id",
    "dataType": "varchar",
    "length": 64,
    "isNullable": false,
    "isPrimaryKey": true,
    "ordinal": 1
  }
]
```

**Errors:**

* `400 Bad Request` — `schemaId` query parameter missing
* `404 Not Found` — schema does not exist in caller's tenant

***

## POST /api/v1/schemas/fields

Add a column to an existing schema. Executes `ALTER TABLE ADD COLUMN`.

### Request Body

| Field          | Type    | Required | Description                                                                                                                                                                                 |
| -------------- | ------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `schemaId`     | string  | Yes      | Parent schema ID                                                                                                                                                                            |
| `name`         | string  | Yes      | Column name                                                                                                                                                                                 |
| `dataType`     | string  | Yes      | PostgreSQL data type                                                                                                                                                                        |
| `displayName`  | string  | No       | Display label                                                                                                                                                                               |
| `length`       | integer | No       | Length for varchar/char                                                                                                                                                                     |
| `precision`    | integer | No       | Precision for decimal                                                                                                                                                                       |
| `scale`        | integer | No       | Scale for decimal                                                                                                                                                                           |
| `isNullable`   | boolean | No       | Default: `true`                                                                                                                                                                             |
| `isPrimaryKey` | boolean | No       | Default: `false`                                                                                                                                                                            |
| `isUnique`     | boolean | No       | Default: `false`                                                                                                                                                                            |
| `isPredictor`  | boolean | No       | Mark column as ML predictor. Default: `false`                                                                                                                                               |
| `defaultValue` | string  | No       | Default value expression                                                                                                                                                                    |
| `description`  | string  | No       | Field description                                                                                                                                                                           |
| `upsert`       | boolean | No       | When `true`, allows overwriting metadata on an existing field with the same name. Used by the CSV re-upload + predictor-toggle codepaths. Default: `false` (returns 409 on duplicate name). |

**Response:** `201 Created` with the field object on success, `200 OK` when `upsert: true` matched an existing row.

### Duplicate-name handling

Without `upsert: true`, the endpoint returns `409 Conflict` if a field with the
same `name` already exists on this schema:

```json theme={null}
{
  "title": "Conflict",
  "detail": "A field named \"first_name\" already exists on this schema. Use PUT to alter it, DELETE to remove it, or pass upsert=true to overwrite metadata."
}
```

This protects the metadata-vs-PG invariant — re-POSTing a field name without
opting in won't silently overwrite length/nullable/predictor flags while the
real PG column stays unchanged.

***

## PUT /api/v1/schemas/fields

Alter an existing column. Executes `ALTER TABLE RENAME COLUMN` and/or
`ALTER COLUMN TYPE` / `SET NOT NULL` / `DROP NOT NULL` against the live table.

### Request Body

| Field         | Type    | Required | Description                                                                                      |
| ------------- | ------- | -------- | ------------------------------------------------------------------------------------------------ |
| `fieldId`     | string  | Yes      | The `SchemaField.id` to alter                                                                    |
| `newName`     | string  | No       | New column name (snake\_case). Triggers `ALTER TABLE RENAME COLUMN` when different from current. |
| `dataType`    | string  | No       | New PostgreSQL data type                                                                         |
| `length`      | integer | No       | New length                                                                                       |
| `precision`   | integer | No       | New precision                                                                                    |
| `scale`       | integer | No       | New scale                                                                                        |
| `isNullable`  | boolean | No       | When provided, toggles `SET NULL` / `DROP NULL`                                                  |
| `displayName` | string  | No       | Display label                                                                                    |
| `description` | string  | No       | Field description                                                                                |

**Response:** `200 OK` with the updated field object.

### Error Codes

| Status | Meaning                                                                                                                                                                       |
| ------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 400    | Invalid `newName` format, missing `fieldId`, or unsupported `dataType`                                                                                                        |
| 404    | Field not found in this tenant                                                                                                                                                |
| 409    | `newName` collides with another field on the same schema                                                                                                                      |
| 422    | Primary-key columns can't be altered here (use the PrimaryKey panel), or the requested type cast would fail on existing data (e.g. `varchar → integer` with non-numeric rows) |

<Warning>Type changes run a `USING` cast on existing data. Narrowing casts that lose precision return 422 — the operation is refused, not silently truncated.</Warning>

***

## DELETE /api/v1/schemas/fields

Remove a column from a schema. Executes `ALTER TABLE DROP COLUMN`.

### Query Parameters

| Parameter | Type   | Required | Description        |
| --------- | ------ | -------- | ------------------ |
| `fieldId` | string | Yes      | Field ID to delete |

<Warning>This drops the column from the underlying table. Data in this column is permanently lost.</Warning>

**Response:** `204 No Content`

***

## POST /api/v1/schemas/{id}/primary-key

Replace the live PostgreSQL `PRIMARY KEY` constraint on a schema's `ds_*` table and
sync each field's `isPrimaryKey` flag to match. Supports composite keys (multiple
columns). PK columns are forced `NOT NULL` (PostgreSQL requires it).

### Path Parameters

| Parameter | Type   | Description |
| --------- | ------ | ----------- |
| `id`      | string | Schema ID   |

### Request Body

| Field    | Type      | Required | Description                                                                                                                                    |
| -------- | --------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------- |
| `fields` | string\[] | No       | Column names in PK order. An **empty array** (or omitted) drops the current PK and restores the surrogate `id` column as the sole primary key. |

### Response — `200 OK`

```json theme={null}
{
  "tableName": "ds_customers_abc123",
  "primaryKey": ["customer_id"],
  "isComposite": false
}
```

`primaryKey` echoes the columns that now form the key (`["id"]` when reset), and
`isComposite` is `true` when more than one column was supplied.

### Error Codes

| Code  | Reason                                             |
| ----- | -------------------------------------------------- |
| `400` | A supplied field name does not exist on the schema |
| `404` | Schema not found                                   |

### Roles

admin, editor

***

## POST /api/v1/schemas/{id}/sync-table

Reconcile the live `ds_*` table with the schema's field metadata. Use it to recover
from drift — e.g. the table was dropped, a `prisma db push` ran, or a backup predates
the schema's fields. **Never drops columns or tables**: columns present in PostgreSQL
but absent from metadata are reported in `extraColumnsInPg`, not removed.

### Path Parameters

| Parameter | Type   | Description |
| --------- | ------ | ----------- |
| `id`      | string | Schema ID   |

No request body.

### Response

Returns `201 Created` when the table was missing and had to be created, otherwise
`200 OK`. The `status` field distinguishes the three outcomes: `"created"`,
`"patched"`, or `"already-in-sync"`.

```json theme={null}
{
  "status": "patched",
  "tableName": "ds_customers_abc123",
  "createdColumns": [],
  "addedColumns": ["loan_amount"],
  "extraColumnsInPg": []
}
```

### Error Codes

| Code  | Reason           |
| ----- | ---------------- |
| `404` | Schema not found |

### Roles

admin, editor

***

## GET /api/v1/schemas/{id}/incoming-fks

List tables whose foreign-key constraints **reference** this schema's `ds_*` table.
Used by the `blue_green` target form to warn that a swap-table rebuild would fail
while inbound FKs still point at the old table.

### Path Parameters

| Parameter | Type   | Description |
| --------- | ------ | ----------- |
| `id`      | string | Schema ID   |

### Response — `200 OK`

```json theme={null}
{
  "schemaId": "schema_001",
  "tableName": "ds_customers_abc123",
  "incomingFks": [
    {
      "constraintName": "ds_accounts_customer_id_fkey",
      "referencingSchema": "public",
      "referencingTable": "ds_accounts_def456",
      "referencingColumn": "customer_id",
      "referencedColumn": "customer_id"
    }
  ],
  "count": 1
}
```

### Error Codes

| Code  | Reason           |
| ----- | ---------------- |
| `404` | Schema not found |

### Roles

admin, editor, viewer

***

## Roles

| Endpoint                         | Allowed Roles         |
| -------------------------------- | --------------------- |
| `GET /schemas`                   | admin, editor, viewer |
| `POST /schemas`                  | admin, editor         |
| `PUT /schemas`                   | admin, editor         |
| `DELETE /schemas`                | admin, editor         |
| `GET /schemas/fields`            | admin, editor, viewer |
| `POST /schemas/fields`           | admin, editor         |
| `PUT /schemas/fields`            | admin, editor         |
| `DELETE /schemas/fields`         | admin, editor         |
| `POST /schemas/{id}/primary-key` | admin, editor         |
| `POST /schemas/{id}/sync-table`  | admin, editor         |
| `GET /schemas/{id}/incoming-fks` | admin, editor, viewer |

See also: [Data Platform](/data/overview) | [Computed Values](/tutorials/computed-values)