Dynamic Query

Overview

The /api/v1/dynamic/query namespace provides endpoints for defining flexible SQL-like queries in JSON. The engine translates request payloads into SQLAlchemy queries and supports:

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

Available endpoints:

Method	Path
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

All Dynamic Query endpoints require authentication. An effective tenant context is required by default. Tables and attributes are filtered by READ permissions.

---

Model names and registry keys

query_model and target_model must be keys returned by GET /api/v1/dynamic/query/tables. The current registry uses table names from __tablename__; do not assume that singular, plural, or class-name variants also work.

Examples of table-based keys:

• users
• agents
• agent_tools

Use consistent keys

When referencing joined fields, use the same model key consistently throughout the query.

---

Request structure

A Dynamic Query request uses this schema:

Field	Type	Description
query_model	String	Required. A key from /tables.
query_attributes	List	Optional. List of strings or [expression, alias] pairs.
query_filter	Object	Optional. Filter tree with operators.
order_by	List of strings	Optional. -field sorts in descending order.
group_by	List of strings	Optional. Grouping fields.
joins	List	Optional. Join definitions.
page	Object	Required for POST /dynamic/query and for each entry in POST /dynamic/query/bulk.

Join definitions use this format:

{
  "target_model": "agent_tools",
  "on": "id=agent_id",
  "isouter": false
}

on can be a string or null. isouter is a boolean.

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

query_attributes

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

---

Schema and metadata

List available tables

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

Example response:

{
  "tables": {
    "users": ["id", "email", "created_at"],
    "agents": ["id", "name", "tenant_id"],
    "agent_tools": ["id", "agent_id", "tool_id"]
  }
}

Get a model’s attributes

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

Example response:

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

Returned attributes

The metadata logic returns mapped columns whose names do not start with _, plus presigned URL fields. Redacted columns are returned as "<redacted>". Presigned URL fields may be materialized or refreshed during the query.

---

SELECT

Simple columns

{
  "query_model": "users",
  "joins": [
    { "target_model": "agents", "on": "id=user_id", "isouter": true }
  ],
  "query_attributes": [
    "id",
    "email",
    "agents.name"
  ],
  "page": { "number": 1, "size": 10 }
}

Projections

{
  "query_model": "agents",
  "query_attributes": [
    ["name", "agent_name"],
    ["tenant_id", "tenant"]
  ],
  "page": { "number": 1, "size": 10 }
}

Aggregations

{
  "query_model": "agents",
  "query_attributes": [
    "tenant_id",
    ["COUNT(id)", "agent_count"]
  ],
  "group_by": ["tenant_id"],
  "page": { "number": 1, "size": 10 }
}

---

WHERE filters

Filter fields use this format:

field__operator

Fields without an operator are invalid. For example, use permission_level__eq instead of permission_level.

Supported operators:

Suffix	SQL	Value
__eq	=	Single value
__ne	!=	Single value
__lt	<	Single value
__lte	<=	Single value
__gt	>	Single value
__gte	>=	Single value
__like	LIKE	String
__ilike	ILIKE	String
__in	IN	List
__not_in	NOT IN	List
__isnull	IS NULL / IS NOT NULL	Boolean

in and not_in require lists. isnull expects a boolean. Filters can be nested using and and or.

{
  "query_model": "users",
  "query_filter": {
    "and": [
      { "permission_level__in": ["ADMIN", "OWNER"] },
      { "last_login__gte": "2024-01-01T00:00:00Z" },
      { "deleted_at__isnull": true }
    ]
  },
  "page": { "number": 1, "size": 10 }
}

---

Subqueries

{
  "id__in": {
    "subquery": {
      "query_model": "agent_tools",
      "query_attributes": ["agent_id"],
      "query_filter": {
        "tool_id__eq": "tool_123"
      }
    }
  }
}

Subquery behavior

A subquery must contain query_model and at least one query_attributes entry. The current implementation uses only query_attributes[0]. query_filter is supported. joins and group_by are not supported; order_by and page are not applied to subqueries.

For ordinary operators, subquery comparisons are scalar and use limit(1). For in and not_in, the subquery is used as a set comparison.

---

JOIN

1. target_model

   The target key must come from /api/v1/dynamic/query/tables .

2. ON clause

   The format is left_field=right_field, for example id=agent_id.

3. Join type

   ”isouter”: true produces a LEFT OUTER JOIN.

{
  "query_model": "agents",
  "joins": [
    {
      "target_model": "agent_tools",
      "on": "id=agent_id",
      "isouter": true
    }
  ],
  "query_attributes": [
    "id",
    "name",
    ["ARRAY_AGG(agent_tools.tool_id)", "tool_ids"]
  ],
  "group_by": ["id", "name"],
  "page": { "number": 1, "size": 10 }
}

---

ORDER BY

{
  "query_model": "users",
  "order_by": [
    "permission_level",
    "-last_login"
  ],
  "page": { "number": 1, "size": 10 }
}

• field: ascending
• -field: descending

---

GROUP BY

{
  "query_model": "agents",
  "query_attributes": [
    "tenant_id",
    ["COUNT(id)", "agent_count"]
  ],
  "group_by": ["tenant_id"],
  "page": { "number": 1, "size": 10 }
}

---

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": "users",
      "query_attributes": ["id", "email"],
      "page": { "number": 1, "size": 10 }
    },
    {
      "query_model": "agents",
      "query_attributes": [
        "tenant_id",
        ["COUNT(id)", "agent_count"]
      ],
      "group_by": ["tenant_id"],
      "page": { "number": 1, "size": 10 }
    }
  ]
}

---

Default configuration

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

Filters:

• reference
• function

---

Limits

Area	Limit
query_attributes	500
Bulk queries	100
Filter branches	250
Filter depth	25
Filter nodes	100
Filter values for in / not_in	100
group_by	100
joins	50
order_by	100
page.size	1000

---

Error handling

403

{
  "status_code": "403",
  "status_message": "Forbidden",
  "errors": [
    {
      "info": "Missing permission for agents.read."
    }
  ]
}

404

{
  "status_code": "404",
  "status_message": "Not Found",
  "errors": [
    {
      "info": "Unknown query_model: users_archive."
    }
  ]
}

422

{
  "status_code": "422",
  "status_message": "Unprocessable Entity",
  "errors": [
    {
      "info": "Validation failed.",
      "type": "ValidationError",
      "cause": [
        {
          "details": "Subquery group_by is not supported",
          "location": ["body", "query_filter"],
          "code": "value_error"
        }
      ]
    }
  ]
}

500

{
  "status_code": "500",
  "status_message": "Internal Server Error",
  "errors": [
    {
      "info": "An unexpected error occurred."
    }
  ]
}