Skip to content

Items CRUD & Query System ​

Overview ​

The Items API provides generic CRUD operations for any user-defined collection. All collection data flows through ItemsService (src/services/items.ts), which applies permission checks, field restrictions, and query filtering.

Endpoints ​

All endpoints are prefixed with /items/:collection. The :collection segment is the collection name (e.g., articles, products).

POST /items/:collection ​

Create one or many items.

Auth required: Yes (create permission on collection)

Single item:

json
{
  "title": "My Article",
  "status": "draft",
  "author_id": "user-uuid"
}

Response 200

json
{ "data": "new-item-uuid" }

Multiple items (batch create):

json
[
  { "title": "Article 1" },
  { "title": "Article 2" }
]

Response 200

json
{ "data": ["uuid-1", "uuid-2"] }

GET /items/:collection ​

Read items with optional filtering, sorting, pagination, and field selection.

Auth required: Yes (read permission)

Response 200

json
{
  "data": [
    {
      "id": "uuid",
      "title": "My Article",
      "status": "published"
    }
  ],
  "meta": {
    "total_count": 100,
    "filter_count": 25
  }
}

meta is only included when ?meta=total_count or ?meta=filter_count is specified.


GET /items/:collection/:pk ​

Read a single item by primary key.

Auth required: Yes (read permission)

Response 200

json
{
  "data": {
    "id": "uuid",
    "title": "My Article",
    "author_id": {
      "id": "user-uuid",
      "first_name": "John"
    }
  }
}

PATCH /items/:collection/:pk ​

Update a single item.

Auth required: Yes (update permission)

Request Body — Partial update (only included fields are modified):

json
{
  "status": "published",
  "published_at": "2026-03-26T10:00:00.000Z"
}

Response 200

json
{ "data": "item-uuid" }

PATCH /items/:collection ​

Batch update multiple items.

Auth required: Yes (update permission)

By explicit keys:

json
{
  "keys": ["uuid-1", "uuid-2", "uuid-3"],
  "data": { "status": "published" }
}

By query:

json
{
  "query": {
    "filter": { "status": { "_eq": "draft" } }
  },
  "data": { "status": "published" }
}

Response 200

json
{ "data": ["uuid-1", "uuid-2", "uuid-3"] }

DELETE /items/:collection/:pk ​

Delete a single item.

Auth required: Yes (delete permission)

Response 204


DELETE /items/:collection ​

Batch delete items.

Auth required: Yes (delete permission)

By keys:

json
{ "keys": ["uuid-1", "uuid-2"] }

By query:

json
{
  "query": {
    "filter": { "status": { "_eq": "archived" } }
  }
}

Response 204


POST /items/:collection/search ​

Alternative to GET with query params — send the full query as a JSON body. Useful when filter objects are complex or too long for a URL.

Request Body — Full query object (same format as query parameters):

json
{
  "filter": {
    "_and": [
      { "status": { "_eq": "published" } },
      { "publish_date": { "_lt": "$NOW" } }
    ]
  },
  "fields": ["id", "title", "author_id.first_name"],
  "sort": ["-publish_date"],
  "limit": 10
}

Response 200 — Same as GET /items/:collection.


Query System ​

The query system is applied uniformly across all list endpoints (items, users, roles, etc.).

fields ​

Select specific fields to return. Supports dot notation for relations.

?fields=id,title,author_id.first_name,author_id.email

Use * for all direct fields:

?fields=*,author_id.*

filter ​

Filter items. Supports complex nested conditions.

Operator syntax: { field: { _operator: value } }

Comparison operators:

OperatorDescription
_eqEqual
_neqNot equal
_ltLess than
_lteLess than or equal
_gtGreater than
_gteGreater than or equal
_inIn array. A string is split on commas (filter[id][_in]=1,2)
_ninNot in array. A string is split on commas
_nullIs null (true) or is not null (false)
_nnullIs not null (true) or is null (false)
_containsString contains
_ncontainsString does not contain
_icontainsCase-insensitive contains
_starts_withString starts with
_ends_withString ends with
_betweenBetween two values (array or "min,max")
_nbetweenNot between two values
_emptyIs empty string or null (true), or has a value (false)
_nemptyIs not empty (true), or is empty (false)

Logical operators:

json
{
  "filter": {
    "_and": [
      { "status": { "_eq": "published" } },
      {
        "_or": [
          { "featured": { "_eq": true } },
          { "views": { "_gte": 1000 } }
        ]
      }
    ]
  }
}

Query string:

?filter[status][_eq]=published&filter[views][_gte]=100

The boolean operators (_null, _nnull, _empty, _nempty) accept true / false and the strings "true" / "false" / "1" / "0". Any other operand returns 400 instead of being ignored.

An unknown operator returns 400 instead of being ignored.

Logical groups under a relation: _and / _or directly under a relational field group conditions on the related item. They are ANDed with any field filter next to them:

json
{ "author": { "_or": [{ "name": { "_eq": "Alice" } }, { "name": { "_eq": "Bob" } }] } }

Dynamic variables in filter values:

VariableDescription
$CURRENT_USERCurrent authenticated user UUID
$CURRENT_ROLESArray of current user's role UUIDs
$NOWCurrent ISO timestamp

sort ​

Sort results. Prefix with - for descending.

?sort=-created_at,title

limit / offset ​

Pagination:

?limit=25&offset=50

Use limit=-1 to fetch all records (use with caution on large collections).

Full-text search across configured searchable fields:

?search=fastify

meta ​

Request metadata alongside results:

?meta=total_count,filter_count
ValueDescription
total_countTotal items in the collection (ignores filter)
filter_countItems matching the current filter

deep ​

Apply nested query options to relational fields. Deep params use the _ prefix convention to distinguish query parameters from nested relation field names.

Supported deep query params: _fields, _filter, _sort, _limit, _offset, _page, _search.

?deep[author_id][_limit]=1&deep[author_id][_fields]=id,first_name

JSON body:

json
{
  "deep": {
    "comments": {
      "_filter": { "status": { "_eq": "approved" } },
      "_sort": ["-date_created"],
      "_limit": 5
    }
  }
}

Nested deep (multi-level relations):

json
{
  "deep": {
    "comments": {
      "_limit": 10,
      "author_id": {
        "_fields": ["id", "first_name", "avatar"]
      }
    }
  }
}

TIP

Keys with _ prefix (_filter, _sort, etc.) are query parameters applied to the relation. Keys without _ prefix (author_id, comments) are nested relation names for deeper traversal.

Relational Filtering ​

Filters can traverse relations using dot notation. The system automatically detects the relation type and generates the appropriate subquery.

M2O (Many-to-One):

?filter[author_id][first_name][_eq]=John

O2M (One-to-Many):

?filter[comments][status][_eq]=approved

A2O (Any-to-One / Polymorphic):

Filter across polymorphic relations. The system generates per-collection subqueries combined with OR logic, each gated by the collection field check:

?filter[item][title][_contains]=draft

Known Limitation

M2M relational filtering from the parent side is not yet supported. ODP stores a single relation record per M2M (from junction → related), whereas parent-side M2M detection requires a second record (from junction → parent). Filtering on M2M fields from the junction side works correctly. This limitation will be addressed in a future relation model update.

aggregate ​

Aggregate functions (used in analytics):

?aggregate[count]=*&aggregate[avg]=views&aggregate[sum]=views
?groupBy[]=status

Supported functions: count, sum, avg, min, max, countDistinct.


Business Logic ​

Permission Enforcement ​

  1. ItemsService.readByQuery() calls the permissions layer before executing the query.
  2. If accountability.admin is true, all permission checks are bypassed.
  3. Row-level filters from matching permissions are injected as additional WHERE conditions.
  4. Field-level restrictions strip disallowed columns from the result before returning.

Collection Validation ​

The collectionExists middleware validates that :collection is a known collection in the current schema. Returns 404 if not found. Also prevents access to system collections (odp_*) unless the user is an admin.

Singleton Collections ​

Collections marked as singletons work the same way but always return a single item (or create one if none exists).


Examples ​

Get published articles sorted by date with author:

bash
curl "http://localhost:6688/items/articles?filter[status][_eq]=published&sort=-published_at&fields=id,title,published_at,author_id.first_name&limit=10" \
  -H "Authorization: Bearer $TOKEN"

Create an article:

bash
curl -X POST http://localhost:6688/items/articles \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"title":"Hello World","status":"draft","author_id":"user-uuid"}'

Batch publish articles:

bash
curl -X PATCH http://localhost:6688/items/articles \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": { "filter": { "status": { "_eq": "draft" } } },
    "data": { "status": "published", "published_at": "2026-03-26T00:00:00Z" }
  }'

Complex search via POST:

bash
curl -X POST http://localhost:6688/items/articles/search \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "filter": {
      "_and": [
        { "status": { "_eq": "published" } },
        { "category": { "_in": ["tech", "science"] } }
      ]
    },
    "fields": ["id", "title", "category", "author_id.email"],
    "sort": ["-published_at"],
    "limit": 20,
    "meta": ["filter_count"]
  }'

ODP Internal API Documentation