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:

Operator	Description
`_eq`	Equal
`_neq`	Not equal
`_lt`	Less than
`_lte`	Less than or equal
`_gt`	Greater than
`_gte`	Greater than or equal
`_in`	In array
`_nin`	Not in array
`_null`	Is null
`_nnull`	Is not null
`_contains`	String contains
`_ncontains`	String does not contain
`_icontains`	Case-insensitive contains
`_starts_with`	String starts with
`_ends_with`	String ends with
`_between`	Between two values
`_nbetween`	Not between two values
`_empty`	Is empty string or null
`_nempty`	Is not empty

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

Dynamic variables in filter values:

Variable	Description
`$CURRENT_USER`	Current authenticated user UUID
`$CURRENT_ROLES`	Array of current user's role UUIDs
`$NOW`	Current 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).

search

Full-text search across configured searchable fields:

?search=fastify

Value	Description
`total_count`	Total items in the collection (ignores filter)
`filter_count`	Items matching the current filter

deep

Apply nested query options to relational fields:

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

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

ItemsService.readByQuery() calls the permissions layer before executing the query.
If accountability.admin is true, all permission checks are bypassed.
Row-level filters from matching permissions are injected as additional WHERE conditions.
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:8055/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:8055/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:8055/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:8055/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"]
  }'

Items CRUD & Query System ​

Overview ​

Endpoints ​

POST /items/:collection ​

GET /items/:collection ​

GET /items/:collection/:pk ​

PATCH /items/:collection/:pk ​

PATCH /items/:collection ​

DELETE /items/:collection/:pk ​

DELETE /items/:collection ​

POST /items/:collection/search ​

Query System ​

fields ​

filter ​

sort ​

limit / offset ​

search ​

meta ​

deep ​

aggregate ​

Business Logic ​

Permission Enforcement ​

Collection Validation ​

Singleton Collections ​

Examples ​

Items CRUD & Query System

Overview

Endpoints

POST /items/:collection

GET /items/:collection

GET /items/:collection/:pk

PATCH /items/:collection/:pk

PATCH /items/:collection

DELETE /items/:collection/:pk

DELETE /items/:collection

POST /items/:collection/search

Query System

fields

filter

sort

limit / offset

search

meta

deep

aggregate

Business Logic

Permission Enforcement

Collection Validation

Singleton Collections

Examples