> For the complete documentation index, see [llms.txt](https://docs.pyck.cloud/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pyck.cloud/graphql-api/advanced/data-indices.md).

# Data Indices

Query a key inside an entity's data payload through a real database index, by declaring it in the datatype's x-indices block

Audience: Programmer

A data index lets you look up a key inside an entity's `data` payload through a real database index instead of a sequential scan. You declare the key in the datatype's `x-indices` block, and query it with the `dataIndex` filter.

Data indices are currently available on picking orders only.

## Why JSON path filters are not enough

The JSON filters (`Data`, `DataIn`, `DataContains`, see [JSON Field Queries](https://docs.pyck.cloud/graphql-api/advanced/json-field-queries.md)) extract a path out of `data` in every row they compare. A predicate that extracts a path cannot use an index over the `data` column, so each such filter scans every row of the tenant. On a large table that is slow, and a lookup that names many values at once, such as "which of these thousand serial numbers does another order already hold?", can time out.

A data index copies the value at one path into a typed column that has its own index, and the query filters on that column.

## The slot pool

pyck pre-creates a fixed pool of indexed columns, called slots, on each entity that supports data indices. A datatype claims a slot for one of its keys; no column is created per client.

| Entity (`entity` of the datatype) | Text slots                        | Numeric slots                           | Bool slots                        | List slots                        |
| --------------------------------- | --------------------------------- | --------------------------------------- | --------------------------------- | --------------------------------- |
| `picking_order`                   | `data_ix_text1` – `data_ix_text4` | `data_ix_numeric1` – `data_ix_numeric2` | `data_ix_bool1` – `data_ix_bool2` | `data_ix_list1` – `data_ix_list2` |

* A slot's kind is in its name. Text, numeric and bool slots hold one value and have a btree index. List slots hold a list of strings and have a GIN index.
* Slots belong to the platform. They are not fields of the GraphQL type and cannot be set in a mutation: pyck writes them from `data` in the same transaction as the order, so a slot never drifts from the payload.
* Every datatype of the entity uses the same physical columns. Two datatypes can each bind `data_ix_list1` to a different key; the query keeps them apart (see [Why `dataType` is required](#why-datatype-is-required)).

## Declaring indices: the `x-indices` block

`x-indices` is a top-level key of the datatype's `jsonSchema`. Each entry maps an index name to a source path and a slot:

```json
"x-indices": {
  "<index name>": { "source": "<JSON pointer into data>", "slot": "<slot column>" }
}
```

| Key        | Meaning                                                                                                                                |
| ---------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| index name | The name you query with (`field` in the `dataIndex` filter). It is the stable API handle: queries never name the slot.                 |
| `source`   | A JSON pointer ([RFC 6901](https://www.rfc-editor.org/rfc/rfc6901)) to the value in `data`, for example `/Serials` or `/Customer/Ref`. |
| `slot`     | The slot column that holds the value, from the entity's pool.                                                                          |

Rules for `source`:

* It starts with `/` and walks object keys only. A numeric segment such as `/Lines/0` is refused, so a value inside an array element cannot be indexed.
* No empty segment (`/a//b`, `/a/`), no whitespace or control characters, and none of `" ' { } , \`. Write `~1` for a `/` and `~0` for a `~` inside a key.

### Complete datatype example

This datatype for picking orders indexes two serial-number lists, the order number, a nested customer reference and an express flag:

```json
{
  "type": "object",
  "properties": {
    "OrderNumber": { "type": "string" },
    "Express": { "type": "boolean" },
    "Customer": { "type": "object", "properties": { "Ref": { "type": "string" } } },
    "MainItemSerialNumbers": { "type": "array", "items": { "type": "string" } },
    "SideComponentSerialNumbers": { "type": "array", "items": { "type": "string" } }
  },
  "x-indices": {
    "serialNumbers":     { "source": "/MainItemSerialNumbers",      "slot": "data_ix_list1" },
    "sideSerialNumbers": { "source": "/SideComponentSerialNumbers", "slot": "data_ix_list2" },
    "orderNumber":       { "source": "/OrderNumber",                "slot": "data_ix_text1" },
    "customerRef":       { "source": "/Customer/Ref",               "slot": "data_ix_text2" },
    "express":           { "source": "/Express",                    "slot": "data_ix_bool1" }
  }
}
```

`jsonSchema` is a string, so the mutation sends that document serialized:

```graphql
mutation CreateSerialOrderDataType($input: CreateDataTypeInput!) {
  createDataType(input: $input) {
    id
    slug
    version
  }
}
```

```json
{
  "input": {
    "name": "Serial picking order",
    "slug": "serial-picking-order",
    "entity": "picking_order",
    "jsonSchema": "{\"type\":\"object\",\"properties\":{\"OrderNumber\":{\"type\":\"string\"},\"Express\":{\"type\":\"boolean\"},\"Customer\":{\"type\":\"object\",\"properties\":{\"Ref\":{\"type\":\"string\"}}},\"MainItemSerialNumbers\":{\"type\":\"array\",\"items\":{\"type\":\"string\"}},\"SideComponentSerialNumbers\":{\"type\":\"array\",\"items\":{\"type\":\"string\"}}},\"x-indices\":{\"serialNumbers\":{\"source\":\"/MainItemSerialNumbers\",\"slot\":\"data_ix_list1\"},\"sideSerialNumbers\":{\"source\":\"/SideComponentSerialNumbers\",\"slot\":\"data_ix_list2\"},\"orderNumber\":{\"source\":\"/OrderNumber\",\"slot\":\"data_ix_text1\"},\"customerRef\":{\"source\":\"/Customer/Ref\",\"slot\":\"data_ix_text2\"},\"express\":{\"source\":\"/Express\",\"slot\":\"data_ix_bool1\"}}}"
  }
}
```

A datatype's schema cannot be edited in place: to add `x-indices` to an existing datatype, call `createDataType` with the same `slug` and the full current schema plus the block. That creates a new version of the datatype.

## What gets indexed

Every write of a picking order projects its `data` into the slots its datatype binds:

| Slot kind | Value at `source`                          | Stored as                          |
| --------- | ------------------------------------------ | ---------------------------------- |
| Text      | JSON string                                | The string                         |
| Numeric   | JSON number                                | The number                         |
| Bool      | JSON boolean                               | The boolean                        |
| List      | JSON array of strings, numbers or booleans | One text element per array element |

* A write whose value has a different JSON type than the slot kind is refused, for example a number at a text slot's source.
* A missing key, or `null`, clears the slot (a list slot becomes empty), so the order stops matching that index.
* In a list, `null` and empty strings are dropped. An element that is an object or an array is refused.

### List values are text

A list slot stores every element as text: a string as it is, a boolean as `true` or `false`, and a number in its plain decimal form, never with an exponent (`1234567`, `1.5`). Query a numeric element as a string: an order whose `data` holds `"Serials": [1234567]` matches `overlaps: ["1234567"]`.

{% hint style="warning" %}
**Known limitation: numbers are refused on write.** A number sent through the GraphQL API arrives in a form the projection does not accept yet, so creating or updating an order fails with `source type not assignable to slot` when a numeric slot's source, or an element of a list slot's source, holds a number. Rows indexed by the backfill (see [Rolling out](#rolling-out-x-indices-to-an-environment)) are not affected. Until this is fixed, bind numeric data only where the values are strings, or leave numeric slots unbound.
{% endhint %}

## Querying: the `dataIndex` filter

`PickingOrderWhereInput` has a `dataIndex` field of type `DataIndexWhereInput`:

| Field        | Type      | Meaning                                      |
| ------------ | --------- | -------------------------------------------- |
| `dataType`   | `String!` | Slug of the datatype that declares the index |
| `field`      | `String!` | Index name from its `x-indices` block        |
| one operator | see below | The comparison                               |

| Operator                               | Operand     | Applies to          | Matches when the slot value                                    |
| -------------------------------------- | ----------- | ------------------- | -------------------------------------------------------------- |
| `eq` / `neq`                           | `String`    | text, numeric, bool | equals / does not equal the operand                            |
| `in` / `notIn`                         | `[String!]` | text, numeric, bool | is / is not one of the operands                                |
| `gt` / `gte` / `lt` / `lte`            | `String`    | text, numeric, bool | compares greater / less (text compares as text)                |
| `contains` / `hasPrefix` / `hasSuffix` | `String`    | text                | contains / starts with / ends with the operand, case-sensitive |
| `overlaps`                             | `[String!]` | list                | holds at least one of the operands                             |

Operands are always strings. For a numeric slot they are parsed as numbers (`"12.5"`), for a bool slot as booleans (`"true"`, `"false"`); an operand that does not parse is refused.

Constraints:

* Exactly one operator per `dataIndex`. Combine several with `and` or `or`.
* The request must target exactly one tenant (the `X-Pyck-Tenant-Id` header), because the index name is resolved against that tenant's datatype.
* `dataIndex` cannot appear inside `not`. Negate the operand instead, with `neq` or `notIn`.
* `overlaps` has no practical limit on the number of values: the list travels as one parameter and is resolved in a single index search.

### Why `dataType` is required

A slot is one column shared by every datatype of the entity, and each datatype can bind it to a different key. The filter therefore matches the slot and the datatype's slug together. Without the slug, a lookup for a serial number in `data_ix_list1` would also match orders of another datatype that uses `data_ix_list1` for, say, pallet IDs.

So two clients, or two datatypes of one client, can share a slot without seeing each other's rows. The scope is by slug rather than by datatype ID, so it covers every version of the datatype: orders created before a new version was published are still found.

### Examples

Find the orders that already hold any of several serial numbers, in one index search:

```graphql
query SerialsInUse {
  pickingOrders(
    first: 50
    where: {
      dataIndex: {
        dataType: "serial-picking-order"
        field: "serialNumbers"
        overlaps: ["SN-0001", "SN-0005", "SN-9999"]
      }
    }
  ) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

Find an order by its order number:

```graphql
query OrderByNumber {
  pickingOrders(
    where: {
      dataIndex: { dataType: "serial-picking-order", field: "orderNumber", eq: "PO-1001" }
    }
  ) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

Find express orders of one customer, by a nested key:

```graphql
query ExpressOrdersOfCustomer {
  pickingOrders(
    where: {
      and: [
        { dataIndex: { dataType: "serial-picking-order", field: "customerRef", eq: "CUST-42" } }
        { dataIndex: { dataType: "serial-picking-order", field: "express", eq: "true" } }
      ]
    }
  ) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

Find orders whose order number starts with `PO-` and that hold none of the given numbers:

```graphql
query OrdersByPrefix {
  pickingOrders(
    where: {
      and: [
        { dataIndex: { dataType: "serial-picking-order", field: "orderNumber", hasPrefix: "PO-" } }
        { dataIndex: { dataType: "serial-picking-order", field: "orderNumber", notIn: ["PO-1000"] } }
      ]
    }
  ) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

### Errors

| Error                                                                            | Cause                                                             |
| -------------------------------------------------------------------------------- | ----------------------------------------------------------------- |
| `index "<field>": no index with that name`                                       | The datatype's current version declares no index named `field`    |
| `operator does not apply to this index: overlaps needs a list index, ...`        | `overlaps` on a text, numeric or bool index                       |
| `operator does not apply to this index: "<field>" is a list index, use overlaps` | Any other operator on a list index                                |
| `operator does not apply to this index: contains needs a text index, ...`        | `contains`, `hasPrefix` or `hasSuffix` on a numeric or bool index |
| `operand is not valid for this index: "<value>" is not a number`                 | An operand that does not parse for a numeric or bool slot         |
| `dataIndex needs exactly one operator`                                           | No operator, or more than one                                     |
| `dataIndex needs exactly one tenant; it resolves the datatype per tenant`        | The request targets several tenants or `all`                      |
| `dataIndex cannot be used inside not; negate the operand instead (neq, notIn)`   | `dataIndex` under `not`                                           |

## Rules enforced when a datatype is saved

Every `createDataType` that carries `x-indices` is checked against every version the slug has ever had, including deleted ones:

| Rule                                                                                                                                        | Error                                                                    |
| ------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------ |
| The slot exists in the entity's pool                                                                                                        | `index "<name>": unknown slot "<slot>"`                                  |
| The entity has a slot pool at all                                                                                                           | `unknown slot: entity "<entity>" has no indexed slots`                   |
| A slot is bound by one index only                                                                                                           | `slot bound more than once: slot "<slot>" bound by both "<a>" and "<b>"` |
| The property's declared type fits the slot kind: `string` for text, `number` or `integer` for numeric, `boolean` for bool, `array` for list | `index "<name>": source type not assignable to slot (...)`               |
| `source` is a usable path                                                                                                                   | `index "<name>": source pointer is not a usable path: ...`               |
| An existing binding keeps its `source` and `slot`, and is not removed                                                                       | `index "<name>": index binding is frozen (...)`                          |

The type check covers top-level properties only. A binding into a nested path, such as `/Customer/Ref`, is not type-checked when you save; a value of the wrong type is refused when an order is written.

Nullable types are allowed. A property may declare its `type` as a list, such as `["string", "null"]`. For the type check, `null` is ignored and the one remaining type counts: `["array", "null"]` can back a list slot, but `["string", "null"]` cannot. If a property declares several non-null types, such as `["string", "number"]`, it is not type-checked when you save, the same as a nested path. Properties that no index uses as a `source` are never type-checked, whatever type they declare.

```json
{
  "properties": {
    "Serials": { "type": ["array", "null"] },
    "Labels":  { "type": ["string", "null"] }
  },
  "x-indices": {
    "serials": { "source": "/Serials", "slot": "data_ix_list1" }
  }
}
```

{% hint style="danger" %}
**Bindings are frozen: you can add an index, but never repoint or delete one.** Once an index is declared, its name, `source` and `slot` stay fixed for the life of the slug, across all versions and even after the datatype is deleted and its slug reused.

The reason: the rows already indexed under a slot live in the picking service's database, and the datatype's service cannot check them. If a binding could move, a slot would hold values of the old key for old rows and of the new key for new rows, and a query could no longer trust what it matches. Choose the index name, `source` and slot carefully before the first save; a wrong binding can only be left in place and a new index added next to it on another slot.
{% endhint %}

## Rolling out `x-indices` to an environment

The slot columns exist in every environment, but the `x-indices` block is data in a datatype, per tenant. Nothing copies it between environments, so apply it in each environment (development, staging, production) yourself, before any client depends on `dataIndex` there.

1. Read the datatype's current `jsonSchema` (`dataTypes(where: {slug: "..."})`).
2. Add the `x-indices` block, keeping the rest of the schema unchanged, and save it with `createDataType` under the same slug. This creates a new version.
3. Verify: query, through `dataIndex`, an order that was created **before** step 2. Finding it proves that existing orders were indexed.
4. Only then roll out the client code that relies on `dataIndex`.

New and changed orders are indexed as they are written. Orders that existed before the binding are filled in by a backfill in the picking service, which runs:

* when the picking service receives the datatype change, normally within seconds of step 2; and
* every time the picking service starts.

The backfill is idempotent: it only fills empty slots, so repeating it does no harm.

{% hint style="warning" %}
**Until the backfill has run, existing orders are silently missing from `dataIndex` results; the query returns no error.** This happens when a client starts relying on `dataIndex` right after the binding is saved, or when the picking service missed the datatype change. Step 3 catches both. If the old order is not found after a minute, restart the picking service: the backfill on start indexes it.

Orders without a datatype slug are never indexed, by either path.
{% endhint %}

Before the binding exists, a `dataIndex` query fails with `no index with that name` rather than returning empty results, so a client deployed too early fails loudly.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.pyck.cloud/graphql-api/advanced/data-indices.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
