Dynamic Query

Overview

The /api/v1/dynamic/query namespace provides a set of endpoints for building flexible SQL-like queries from JSON. The engine maps request payloads to SQLAlchemy queries and supports, among other things:

• SELECT
• WHERE
• JOIN
• GROUP BY
• ORDER BY
• subqueries in filters

Available endpoints:

• GET /api/v1/dynamic/query/tables
• GET /api/v1/dynamic/query/attributes
• POST /api/v1/dynamic/query
• POST /api/v1/dynamic/query/bulk
• POST /api/v1/dynamic/query/default-configuration

---

Model naming and registry keys

For query_model and target_model, the engine registers both:

• the lowercased class name
• the lowercased table name

This means both forms are often valid.

Examples:

• tenant or tenants
• user or users
• file or files
• fileagent or file_agents
• allowedextensions or allowed_extensions
• agentphonenumber or agent_phone_numbers

Use /api/v1/dynamic/query/tables as the source of truth for the exact keys available in your environment.

Use consistent keys

If you reference joined fields such as tenant.domain or fileagent.agent_id, use the same model key style consistently throughout the request.

---

Basic request structure

A dynamic query request typically contains:

• query_model
• query_attributes
• optional query_filter
• optional joins
• optional group_by
• optional order_by
• optional page

curl https://api.livoi.de/api/v1/dynamic/query \
  --request POST \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer YOUR_TOKEN' \
  --data '{
  "query_model": "user",
  "query_attributes": ["id", "email"],
  "page": { "page": 1, "size": 10 }
}'

About query_attributes

If query_attributes is empty, the service selects all columns from the base model and from joined models.

---

Schema & metadata

List available queryable tables

curl 'https://api.livoi.de/api/v1/dynamic/query/tables?include_attributes=true' \
  --header 'Authorization: Bearer YOUR_SECRET_TOKEN'

Example response:

{
  "tables": {
    "user": ["id","email","created_at"],
    "users": ["id","email","created_at"],
    "fileagent": ["id","file_id","agent_id"],
    "file_agents": ["id","file_id","agent_id"]
  }
}

Duplicate entries are normal

Because both class names and table names are registered, entries such as user and users may return identical attribute lists.

---

Get attributes for a specific model

curl 'https://api.livoi.de/api/v1/dynamic/query/attributes?query_model=file' \
  --header 'Authorization: Bearer YOUR_SECRET_TOKEN'

Example response:

{
  "attributes": [
    "content_type",
    "created_at",
    "file_name",
    "id",
    "size",
    "tags",
    "title",
    "updated_at",
    "uri"
  ]
}

Returned attributes

Hybrid properties and mapped properties may also appear.
scope_id is intentionally removed.

---

SQL operations

SELECT

Simple columns

{
  "query_model": "user",
  "joins": [
    { "target_model": "tenant", "on": "tenant_id=id" }
  ],
  "query_attributes": [
    "id",
    "email",
    "tenant.domain"
  ]
}

Projections

{
  "query_model": "agent",
  "query_attributes": [
    ["name","agent_name"],
    ["tenant.domain","tenant_domain"]
  ],
  "joins": [
    { "target_model": "tenant", "on": "tenant_id=id" }
  ]
}

Aggregations

{
  "query_model": "history",
  "query_attributes": [
    "model_name",
    ["COUNT(id)","message_count"],
    ["SUM(usage_output_tokens)","total_tokens"]
  ],
  "group_by": ["model_name"]
}

---

WHERE filtering

Format:

field__operator

Operators are mandatory

Plain fields without operator are invalid.

Use:

permission_level__eq

instead of:

permission_level

Supported operators:

Suffix	SQL
__eq	=
__ne	!=
__lt	<
__lte	<=
__gt	>
__gte	>=
__like	LIKE
__ilike	ILIKE
__in	IN
__not_in	NOT IN
__isnull	IS NULL

Example:

{
  "query_model": "user",
  "query_filter": {
    "permission_level__in": ["ADMIN","OWNER"],
    "last_login__gte": "2024-01-01T00:00:00Z"
  }
}

---

Subqueries

Example:

{
  "id__in": {
    "subquery": {
      "query_model": "fileagent",
      "query_attributes": ["file_id"]
    }
  }
}

Subquery behavior

The implementation uses only query_attributes[0].

Inside subqueries:

• query_filter works
• group_by works
• joins, order_by, and page are not currently applied

---

JOIN

1. target_model

   Example:

   tenant
   fileagent
   calendaragent

2. ON clause

   Format:

   left_field=right_field

   Example:

   id=file_id

3. Join type

   "isouter": true

   produces a LEFT OUTER JOIN.

Example:

{
  "query_model": "file",
  "joins": [
    {
      "target_model": "fileagent",
      "on": "id=file_id",
      "isouter": true
    }
  ],
  "query_attributes": [
    "id",
    "file_name",
    ["ARRAY_AGG(fileagent.agent_id)","agent_ids"]
  ],
  "group_by": ["id","file_name"]
}

---

ORDER BY

{
  "query_model": "user",
  "order_by": [
    "permission_level",
    "-last_login"
  ]
}

• field → ascending
• -field → descending

---

GROUP BY

{
  "query_model": "chat",
  "query_attributes": [
    "user_id",
    ["COUNT(id)","chat_count"]
  ],
  "group_by": ["user_id"]
}

---

Bulk queries

curl https://api.livoi.de/api/v1/dynamic/query/bulk \
  --request POST \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer YOUR_SECRET_TOKEN'

Example body:

{
  "queries": [
    {
      "query_model": "user",
      "query_attributes": ["id","email"]
    },
    {
      "query_model": "history",
      "query_attributes": [
        "model_name",
        ["COUNT(id)","message_count"]
      ],
      "group_by": ["model_name"]
    }
  ]
}

---

Default configuration

POST /api/v1/dynamic/query/default-configuration

Filters:

• reference
• function

---

Error handling

403

{
  "status_code": "403",
  "status_message": "Forbidden"
}

404

{
  "status_code": "404",
  "status_message": "Not Found"
}

422

{
  "status_code": "422",
  "status_message": "Unprocessable Content"
}

500

{
  "status_code": "500",
  "status_message": "Internal Server Error"
}