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

# Filtering & sorting

> Every operator, the filter request model, sorting, and where the full per-record dimension catalog lives.

The records query endpoint (`POST /v1/records/query`) is the comprehensive filter + sort surface: it can filter any of the **501 filterable dimensions** across the **32 record roots** and sort on any sortable field. Resource list endpoints (`GET /v1/<resource>`) accept the same sort + cursor model with a curated subset of filters.

## The filter request model

A filter is a `FilterConfig`: a **field**, an **operator**, a **value**, and a **conjunction** that joins it to the next rule.

| Param         | Type                  | Meaning                                                                            |
| ------------- | --------------------- | ---------------------------------------------------------------------------------- |
| `field`       | string (dot path)     | The dimension to filter, e.g. `status`, `issuer.name`, `instructed_amount.amount`. |
| `operator`    | enum                  | One of the operators below (gated by the field's data type).                       |
| `value`       | scalar · array · none | The comparison value. Array for `in`; omitted for `is_null` / `is_not_null`.       |
| `conjunction` | `and` · `or`          | How this rule joins the next. Defaults to `and`.                                   |

Filters compile to a Hasura `whereClause`; you may also pass a raw `whereClause` for the full Hasura reach. Sorting is `orderBy` ( `field` + `direction` ). See `POST /v1/records/query`.

## Operators by data type

The operator set is **gated by the field's data type** — sending an out-of-type operator is rejected.

| Data type     | Allowed operators                                                             |
| ------------- | ----------------------------------------------------------------------------- |
| **text**      | `contains`, `eq`, `neq`, `starts_with`, `ends_with`, `is_null`, `is_not_null` |
| **number**    | `eq`, `neq`, `gt`, `gte`, `lt`, `lte`, `is_null`, `is_not_null`               |
| **date**      | `eq`, `lt`, `gt`, `is_null`, `is_not_null`                                    |
| **boolean**   | `eq`                                                                          |
| **enum**      | `eq`, `neq`, `in`, `is_null`, `is_not_null`                                   |
| **workspace** | `eq`                                                                          |

`is_null` / `is_not_null` take no value. `in` takes an array. `contains` / `starts_with` / `ends_with` map to Hasura `_ilike` (case-insensitive).

## Raw `whereClause` — full Hasura reach

The `filters` model above is the curated, type-gated layer. For anything it cannot express, `POST /v1/records/query` accepts a raw **`whereClause`** that is passed through to Hasura unchanged (`z.object({}).passthrough()`), giving you the **complete Hasura boolean-expression grammar** — workspace row-level security is still enforced, so passthrough never widens your scope.

### Boolean connectives

Combine conditions with `_and`, `_or`, and `_not` (arbitrarily nested):

```json theme={null}
"whereClause": {
  "_and": [
    { "status": { "_in": ["issued", "paid"] } },
    { "_or": [
      { "grand_total": { "amount": { "_gte": 1000 } } },
      { "issuer": { "name": { "_ilike": "%anthropic%" } } }
    ] },
    { "_not": { "deleted_at": { "_is_null": false } } }
  ]
}
```

### Comparison operators (full set)

A field condition is `{ "<field>": { "<op>": <value> } }`. The complete Hasura operator set is available through passthrough — a superset of the curated operators above:

| Category                   | Operators                                                       |
| -------------------------- | --------------------------------------------------------------- |
| Equality                   | `_eq`, `_neq`                                                   |
| Ordering                   | `_gt`, `_gte`, `_lt`, `_lte`                                    |
| Set membership             | `_in`, `_nin`                                                   |
| Null                       | `_is_null` (`true` / `false`)                                   |
| Pattern (case-sensitive)   | `_like`, `_nlike`, `_similar`, `_nsimilar`, `_regex`, `_nregex` |
| Pattern (case-insensitive) | `_ilike`, `_nilike`, `_iregex`, `_niregex`                      |

How the curated operators compile to Hasura: `eq → _eq`, `neq → _neq`, `gt/gte/lt/lte → _gt/_gte/_lt/_lte`, `in → _in`, `is_null → _is_null: true`, `is_not_null → _is_null: false`, `contains → _ilike "%v%"`, `starts_with → _ilike "v%"`, `ends_with → _ilike "%v"`.

### Filtering across relationships (related / included objects)

You can filter on the fields of a **related object** — the same relationships you pass to `include` and see under a response's `relationships`. **How you nest depends on the relationship cardinality**, which each resource's object-reference page lists as `to-one` or `to-many`:

**To-one relationships** (`issuer`, `receiver`, `document`, `workspace`, …) — nest the relationship name directly, then the field condition. The dot-path `field` form in the curated model does the same thing:

```json theme={null}
// raw whereClause — invoices whose issuer is US-registered
"whereClause": { "issuer": { "registry_country": { "_eq": "US" } } }

// curated filters — identical result, type-gated
{ "field": "issuer.name", "operator": "contains", "value": "Anthropic" }
```

**To-many relationships** (`invoice_items`, `invoice_transactions`, `payment_means`, …) — you **must** quantify with `_some`, `_every`, or `_none`; nesting a field directly on an array relation is invalid:

```json theme={null}
// invoices that have AT LEAST ONE line item over 100 units
"whereClause": { "invoice_items": { "_some": { "quantity": { "_gt": 100 } } } }

// invoices where EVERY line item is tax-exempt
"whereClause": { "invoice_items": { "_every": { "tax_rate": { "_eq": 0 } } } }

// invoices with NO matched bank transaction (unreconciled)
"whereClause": { "invoice_transactions": { "_none": {} } }
```

Relationships nest **arbitrarily deep** (`{ "issuer": { "workspace": { "name": { "_eq": "Acme" } } } }`), and each hop must be one of the resource's documented relationships. Workspace row-level security still applies at every hop, so a related-object filter never widens your tenant scope.

<Warning>The curated `filters` model is type-gated and safer for clients, and supports one-hop `to-one` dot-paths (`issuer.name`). Reach for raw `whereClause` when you need `_some`/`_every`/`_none` on a `to-many` relation, a deeper multi-hop path, or a boolean shape the curated model does not expose. Both compile to the same Hasura query under the same RLS.</Warning>

### Composite fields

Composite columns (`composite_*`, e.g. `composite_total_amount_currency`) are **virtual** — they are reconstructed from underlying source fields at response time and **cannot be filtered or sorted directly**. Filter or sort on the **source field** instead (each composite's object-reference entry lists its sources): to order by the invoice total, use `grand_total`, not `composite_total_amount_currency`.

## Filtering — example

```bash theme={null}
curl -X POST https://api.wellapp.ai/v1/records/query \
  -H "Authorization: Bearer $WELL_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "root": "invoices",
    "filters": [
      { "field": "status",            "operator": "in",  "value": ["issued","paid"], "conjunction": "and" },
      { "field": "issuer.name",       "operator": "contains", "value": "Anthropic" },
      { "field": "grand_total.amount","operator": "gte", "value": 1000 }
    ],
    "orderBy": { "field": "issued_at", "direction": "desc" },
    "limit": 50
  }'
```

The same rules expressed as a raw Hasura `whereClause` are also accepted (`_and`, `_or`, `_ilike`, `_gte`, …).

## Sorting

`orderBy` takes a `field` and a `direction` (`asc` / `desc`). Sortability is gated per field (JSONB and computed columns may be non-sortable); an unsortable field is rejected. A stable tie-break is applied automatically. List endpoints accept `sort` (comma-separated, `-` prefix for descending) for the same effect.

### Sorting across relationships (related / included objects)

**To-one relationships** — order by a related field with a **dot path**; it compiles to a nested Hasura `order_by`:

```json theme={null}
// invoices, newest issuer first
"orderBy": { "field": "issuer.name", "direction": "asc" }
// -> Hasura order_by: { issuer: { name: asc } }
```

**To-many relationships cannot be sorted directly.** Ordering invoices by `invoice_items.amount` is ambiguous (each invoice has many items), so a bare array-relation sort is **rejected** (the sort is dropped and the default order applies — no error). To order by a to-many relation, the field must expose an **aggregate sort proxy** (configured per field, e.g. `subtasks_aggregate.min.created_at`), which sorts by the aggregate (min/max/sum) over the related rows. Where a proxy exists it is applied automatically when you sort the corresponding composite; where one does not, that relation is simply not sortable.

<Warning>**Composite columns can't be sorted or filtered directly** — see [Composite fields](#composite-fields) above. Sort by the underlying source field (e.g. `grand_total`), not `composite_total_amount_currency`. Sorting by a related field also changes pagination: a related/aggregate sort falls back to offset-based cursors (capped at 10,000 rows) instead of keyset pagination, so very deep paging on a related sort is bounded.</Warning>

## The full per-record dimension catalog

The complete map of **which dimensions are filterable on each of the 32 roots, their data types, and the resulting operator set** is maintained as the Atlas matrix — see **`docs/wireframes/table-views/matrix.html`** (the filter / sort / view-management reference). Each root's filterable fields are also visible in its records data model. This page is the *contract* (operators + model); the matrix is the *exhaustive per-field index*.

<Note>Chat and MCP apply filters through this same model — the records query is the single filter engine behind every surface.</Note>