Dynamic Query

Erstellen von komplexen Datenbankabfragen via JSON: Selektion, Filterung, Joins, Aggregationen und Subqueries.

Übersicht

Der Endpunkt /api/v1/dynamic/query ermöglicht es, flexible und komplexe Datenbankabfragen direkt über ein JSON-Objekt zu definieren. Die Logik abstrahiert SQL-Befehle wie SELECT, WHERE, JOIN, GROUP BY und Subqueries, um Daten dynamisch, aber typsicher abzurufen.

Die Dynamic-Query-Engine arbeitet auf den SQLAlchemy-Modellen u. a.:

Tenant (tenants)
Scopes (scopes)
Agent, AgentTool, AgentPhoneNumber, WhatsAppTemplate
User, APIKey, Instruction, Chat, History, ToolLogs
File, FileStore
Calendar
FileAgent, CalendarAgent
AgentWhitelistEntry, AgentParentChild
AllowedExtensions

Die Modellnamen im JSON sind in der Regel die kleinen, singularen Varianten, z. B.:

tenant → Tenant / Tabelle tenants
user → User / Tabelle users
file → File / Tabelle files
fileagent → FileAgent / Tabelle file_agents
history → History / Tabelle history

Grundstruktur der Anfrage

Ein minimaler Request benötigt das Zielmodell (query_model) und die gewünschten Felder (query_attributes).

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 }
}'

Schema & Metadaten

Um valide Abfragen zu konstruieren, bietet die API Endpunkte zur Introspektion (Discovery) an. Hiermit können verfügbare Tabellen und deren Attribute ermittelt werden.

Verfügbare Tabellen auflisten

Gibt eine Liste aller für dynamische Abfragen freigegebenen Modelle / Tabellen zurück. Optional können direkt alle Attribute mitgeliefert werden.

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

Beispiel-Antwort:

{
  "tables": {
    "tenant": [
      "id",
      "name",
      "domain",
      "created_at",
      "..."
    ],
    "user": [
      "id",
      "tenant_id",
      "scope_id",
      "email",
      "phone_number",
      "permission_level",
      "created_at",
      "last_login",
      "..."
    ],
    "agent": [
      "id",
      "tenant_id",
      "scope_id",
      "name",
      "description",
      "llm_model_name",
      "..."
    ],
    "file": [
      "id",
      "scope_id",
      "file_name",
      "uri",
      "size",
      "created_at",
      "updated_at",
      "..."
    ],
    "fileagent": [
      "id",
      "agent_id",
      "file_id",
      "scope_id",
      "created_at",
      "..."
    ],
    "history": [
      "id",
      "chat_id",
      "agent_id",
      "scope_id",
      "model_name",
      "input_timestamp",
      "output_timestamp",
      "usage_output_tokens",
      "..."
    ]
  }
}

Attribute eines Modells abrufen

Ermittelt spezifische Attribute für ein einzelnes Modell, die in query_attributes oder query_filter verwendet werden können.

Beispiel für das Modell file:

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

Beispiel-Antwort:

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

SQL-Operationen im Detail

Die API unterstützt die wichtigsten SQL-Klauseln. Hier wird erklärt, wie diese im JSON-Format abgebildet werden.

Subqueries in Filtern

Die Query-Engine unterstützt Subqueries in WHERE-Bedingungen.

Subqueries können bei folgenden Operatoren verwendet werden:

__in
__not_in
__eq
__ne
__lt
__lte
__gt
__gte

Verhalten:

Für __in / __not_in wird die Subquery direkt als Menge verwendet
→ col IN (SELECT ...) bzw. col NOT IN (SELECT ...).
Für Vergleichsoperatoren (__eq, __ne, __lt, __lte, __gt, __gte) wird die Subquery intern auf eine Zeile begrenzt
→ es wird immer nur der erste Wert verwendet (SQL-Äquivalent: SELECT ... LIMIT 1 als Skalarwert).

Dadurch gilt:

Wenn ein Feld mit einer Subquery verglichen wird (z. B. __eq, __lt, …),
verwendet die Engine immer den ersten Datensatz der Subquery.

Beispiele:

id__in mit Subquery → id IN (SELECT file_id FROM file_agents WHERE ...)
created_at__gt mit Subquery → created_at > (SELECT created_at FROM ... ORDER BY created_at LIMIT 1)

1. SELECT (Attribute & Aggregationen)

Das Feld query_attributes bestimmt, welche Spalten zurückgegeben werden. Es entspricht dem SELECT-Teil einer SQL-Query.

Wähle Spalten des Hauptmodells oder verknüpfter Modelle (via Punkt-Notation).

Beispiel: einfache User-Liste inkl. Tenant-Domain.

{
  "query_model": "user",
  "joins": [
    {
      "target_model": "tenant",
      "on": "tenant_id=id"
    }
  ],
  "query_attributes": [
    "id",
    "email",
    "tenant.domain"  // Zugriff auf Tenant-Tabelle
  ]
}

Um Spalten umzubenennen (analog zu SELECT col AS alias), kann eine Liste aus Tupeln [Ausdruck, Alias] übergeben werden.

Beispiel: Agenten mit sprechenden Aliasen:

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

Die API akzeptiert eine gemischte Liste aus Strings und Tupeln. Tupel folgen dem Format ['Attribut', 'Alias']. Das Attribut kann dabei entweder ein direkter Spaltenname oder eine SQL-Aggregatfunktion sein.

Verfügbare Funktionen u. a.:
MAX, MIN, COUNT, SUM, AVG, ARRAY_AGG, JSON_AGG, JSONB_AGG, STRING_AGG, VAR_POP, VAR_SAMP, STDDEV_POP, STDDEV_SAMP, BOOL_AND, BOOL_OR.

Beispiel: Token-Nutzung pro Modell in history:

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

2. WHERE (Filterung)

Das query_filter-Objekt übersetzt Schlüssel-Wert-Paare in SQL-WHERE-Klauseln.

Format: Feld__Operator.

Feld-Auflösung

Filter können sich auf das Hauptmodell beziehen (email) oder auf gejointe Tabellen (tenant.domain, fileagent.agent_id, etc.).

Verfügbare Operatoren (ohne Subquery):

Suffix	SQL Äquivalent	Beispiel JSON
(kein Suffix) / `__eq`	`=`	`"status": "active"`
`__ne`	`!=`	`"status__ne": "deleted"`
`__lt`	`<`	`"usage_output_tokens__lt": 1000`
`__lte`	`<=`	`"size__lte": 1048576`
`__gt`	`>`	`"usage_output_tokens__gt": 0`
`__gte`	`>=`	`"created_at__gte": "2024-01-01"`
`__like`	`LIKE` (Case-sensitive)	`"file_name__like": "report_%"`
`__ilike`	`ILIKE` (Case-insensitive)	`"email__ilike": "%@gmail.com"`
`__in`	`IN (...)`	`"permission_level__in": ["ADMIN", "OWNER"]`
`__not_in`	`NOT IN (...)`	`"permission_level__not_in": ["READONLY"]`
`__isnull`	`IS NULL` / `IS NOT NULL`	`"deleted_at__isnull": true`

Beispiel (User mit bestimmter Rolle und Login-Bedingung):

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

Subqueries in WHERE-Bedingungen

Neben einfachen Werten (string, number, boolean, Arrays) kann ein Filterwert auch eine Subquery sein.

Subqueries werden als verschachteltes Objekt unter dem Schlüssel subquery übergeben und haben wieder die Felder:

query_model
query_attributes
optional query_filter, joins, group_by, order_by, page (wobei page in Subqueries meist keinen Sinn macht)

Beispiel 1: `IN (Subquery)` für Files eines bestimmten Agents

Szenario: Finde alle file-Einträge, die einem bestimmten Agenten zugeordnet sind.
Die Relation läuft über die N:M-Tabelle fileagent (FileAgent):

file.id ↔ fileagent.file_id
fileagent.agent_id ist der Agent

{
  "query_model": "file",
  "query_attributes": [
    "id",
    "updated_at",
    "file_name",
    "size",
    ["ARRAY_AGG(fileagent.agent_id)", "agent_ids"]
  ],
  "joins": [
    {
      "target_model": "fileagent",
      "isouter": true,
      "on": "id=file_id"
    }
  ],
  "query_filter": {
    "id__in": {
      "subquery": {
        "query_model": "fileagent",
        "query_attributes": ["file_id"],
        "query_filter": {
          "agent_id": [7387494553877897217]
        }
      }
    }
  },
  "group_by": ["id", "updated_at", "file_name", "size"],
  "order_by": ["-id"],
  "page": {
    "page": 1,
    "size": 50
  }
}

Logisch entspricht das:

WHERE file.id IN (SELECT file_id FROM file_agents WHERE agent_id = 7387494553877897217)

und zusätzlich wird über fileagent gejoint, um ARRAY_AGG(fileagent.agent_id) als agent_ids zu erhalten.

Beispiel 2: `NOT IN (Subquery)`

Alle Files, die keinem Agenten zugeordnet sind:

{
  "query_model": "file",
  "query_attributes": [
    "id",
    "file_name",
    "size"
  ],
  "query_filter": {
    "id__not_in": {
      "subquery": {
        "query_model": "fileagent",
        "query_attributes": ["file_id"]
      }
    }
  },
  "order_by": ["-created_at"]
}

Beispiel 3: Vergleich mit dem ersten Subquery-Ergebnis

Szenario: Zeige history-Einträge, deren output_timestamp nach dem frühesten output_timestamp im System liegt.

{
  "query_model": "history",
  "query_attributes": [
    "id",
    "model_name",
    "output_timestamp",
    "usage_output_tokens"
  ],
  "query_filter": {
    "output_timestamp__gt": {
      "subquery": {
        "query_model": "history",
        "query_attributes": ["output_timestamp"],
        "order_by": ["output_timestamp"]
      }
    }
  },
  "order_by": ["output_timestamp"]
}

Intern entsteht so etwas wie:

output_timestamp > (SELECT output_timestamp FROM history ORDER BY output_timestamp LIMIT 1)

Wichtig:

Wenn irgendetwas mit einer Subquery verglichen wird (__eq, __lt, etc.),
nimmt die Engine immer das erste Ergebnis der Subquery (LIMIT 1).

Unterstützte Operatoren mit Subqueries

__in → col IN (subquery)
__not_in → col NOT IN (subquery)
__eq → col = (SELECT … LIMIT 1)
__ne → col != (SELECT … LIMIT 1)
__lt → col < (SELECT … LIMIT 1)
__lte → col <= (SELECT … LIMIT 1)
__gt → col > (SELECT … LIMIT 1)
__gte → col >= (SELECT … LIMIT 1)

Erweiterte Filterlogik (AND / OR, verschachtelte Bedingungen)

Neben einfachen Key-Value-Filtern unterstützt die Query-Engine komplexe logische Ausdrücke.
Damit lassen sich Bedingungen beliebig tief verschachteln und kombinieren.

Es gibt zwei Grundformen:

Flache Filterobjekte (ohne and/or)
Explizite Logikblöcke mit and oder or

Beide Varianten können kombiniert werden.

Standardverhalten (implizites AND)

Wenn du nur einfache Filter ohne and oder or angibst, werden sie automatisch mit AND verknüpft.

Beispiel: Alle User mit ADMIN-Rechten, die ab 2024 erstellt wurden:

{
  "query_model": "user",
  "query_filter": {
    "permission_level": "ADMIN",
    "created_at__gte": "2024-01-01T00:00:00Z"
  }
}

Das entspricht logisch:

{
  "query_model": "user",
  "query_filter": {
    "and": [
      { "permission_level": "ADMIN" },
      { "created_at__gte": "2024-01-01T00:00:00Z" }
    ]
  }
}

Logische Blöcke (`and` / `or`)

Beispiel: User, die entweder eine bestimmte Domain haben oder bestimmten Tenant-Regeln unterliegen.

{
  "query_model": "user",
  "joins": [
    {
      "target_model": "tenant",
      "on": "tenant_id=id"
    }
  ],
  "query_filter": {
    "or": [
      { "email__ilike": "%@example.com" },
      { "tenant.domain__ilike": "%.acme.com" }
    ]
  }
}

Kombination: flache Filter + `and`/`or`

Flache Filter + Logikblock kombinieren sich zu:

(alle flachen Filter) AND (Logikblock)

Beispiel:

Tenant-Domain *.acme.com
UND User muss Admin/Owner sein
UND zusätzlich (nie eingeloggt ODER sehr alter Login)

{
  "query_model": "user",
  "joins": [
    { "target_model": "tenant", "on": "tenant_id=id" }
  ],
  "query_filter": {
    "tenant.domain__ilike": "%.acme.com",
    "and": [
      { "permission_level__in": ["ADMIN", "OWNER"] },
      {
        "or": [
          { "last_login__isnull": true },
          { "last_login__lt": "2024-01-01T00:00:00Z" }
        ]
      }
    ]
  }
}

Multi-Depth (Joins + verschachtelte Logik)

Im folgenden Beispiel:

Startmodell: agent
Join auf tenant (Mandant)
Join auf agent_phone_numbers (WhatsApp-Nummer)
Filter:
- Entweder Tenant-Domain + LLM-Modell
- oder Agent hat eine WhatsApp-Nummer mit bestimmtem Verified-Namen

{
  "query_model": "agent",
  "joins": [
    {
      "target_model": "tenant",
      "on": "tenant_id=id"
    },
    {
      "target_model": "agentphonenumber",
      "on": "agent_phone_number_id=id",
      "isouter": true
    }
  ],
  "query_attributes": [
    "id",
    "name",
    "tenant.domain",
    "llm_model_name",
    "agentphonenumber.business_phone_verified_name"
  ],
  "query_filter": {
    "or": [
      {
        "and": [
          { "tenant.domain__ilike": "%.acme.com" },
          { "llm_model_name": "gpt-5.1" }
        ]
      },
      {
        "agentphonenumber.business_phone_verified_name__ilike": "%Support%"
      }
    ]
  }
}

In solchen Strukturen können ebenfalls Subqueries als Werte in den inneren Objekten verwendet werden.

3. JOIN (Verknüpfungen)

Es findet keine implizite Auflösung von Relationen statt. Der Zugriff auf Felder anderer Tabellen (z. B. via tenant.domain oder fileagent.agent_id) erfordert, dass das Zielmodell explizit in joins registriert wurde.

Target Model

Das Zielmodell / Zieltabelle, das verknüpft werden soll (target_model), z. B. tenant, fileagent, calendaragent.

ON Clause (Optional)

Die ON-Bedingung legt fest, wie verknüpft wird (on).

Automatisch:
Wird on weggelassen, führt das System den Join automatisch anhand von Foreign Keys zwischen Parent-Modell und Zielmodell aus.
Explizit:
Format linkes_feld=rechtes_feld.
Ohne Tabellenpräfix bezieht sich die linke Seite auf das Parent-Modell.

Beispiel file → fileagent:

Parent: file
Join: "on": "id=file_id"
→ files.id = file_agents.file_id

Join Type

Mit "isouter": true wird ein LEFT OUTER JOIN erzeugt.
Standard (false oder weggelassen) ist INNER JOIN.

Beispiel: Files inkl. zugeordneter Agenten

{
  "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"]
}

4. ORDER BY (Sortierung)

Sortiert die Ergebnismenge. Die Reihenfolge im Array bestimmt die Priorität.

Feldname: Aufsteigend (ASC)
-Feldname: Absteigend (DESC)

Beispiel: User nach Rolle und letztem Login:

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

5. GROUP BY (Gruppierung)

Beispiel: Token-Nutzung pro Modell (siehe oben in Aggregationen) verwendet group_by auf model_name.

Weiteres Beispiel: Anzahl Chats pro User:

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

Vollständige Beispiele

Beispiel 1: Klassische Abfrage ohne Subquery

Szenario:
Zeige alle User (user), die seit 01.01.2024 existieren. Hole id, email, Tenant-Domain, sortiert nach Erstellungsdatum (neueste zuerst).

curl https://api.livoi.de/api/v1/dynamic/query \
  --request POST \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer YOUR_SECRET_TOKEN' \
  --data '{
  "query_model": "user",
  "query_attributes": [
    "id",
    "email",
    "tenant.domain"
  ],
  "joins": [
    {
      "target_model": "tenant",
      "on": "tenant_id=id",
      "isouter": true
    }
  ],
  "query_filter": {
    "created_at__gte": "2024-01-01"
  },
  "order_by": [
    "-created_at"
  ],
  "page": {
    "page": 1,
    "size": 25
  }
}'

Beispiel 2: File-Liste mit Subquery-Filter (Agent-Files)

Szenario:
Liste alle Files, die einem bestimmten Agenten zugeordnet sind.
Nutze eine Subquery auf fileagent, um die file.id-Menge zu bestimmen.

curl https://api.livoi.de/api/v1/dynamic/query \
  --request POST \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer YOUR_SECRET_TOKEN' \
  --data '{
  "query_model": "file",
  "query_attributes": [
    "id",
    "updated_at",
    "file_name",
    "size",
    ["ARRAY_AGG(fileagent.agent_id)", "agent_ids"]
  ],
  "joins": [
    {
      "target_model": "fileagent",
      "isouter": true,
      "on": "id=file_id"
    }
  ],
  "query_filter": {
    "id__in": {
      "subquery": {
        "query_model": "fileagent",
        "query_attributes": ["file_id"],
        "query_filter": {
          "agent_id": [7387494553877897217]
        }
      }
    }
  },
  "group_by": ["id", "updated_at", "file_name", "size"],
  "order_by": ["-id"],
  "page": {
    "page": 1,
    "size": 50
  }
}'

Hier greift genau die Subquery-Logik: id__in → files.id IN (SELECT file_id FROM file_agents WHERE agent_id = …)
Joins und Aggregationen darüber laufen wie gewohnt.

Fehlerbehandlung

Die API validiert Felder gegen die MODEL_REGISTRY sowie die Syntax der Anfrage. Fehler werden in einem standardisierten JSON-Format zurückgegeben.

Tritt auf, wenn dem Benutzer die notwendigen Berechtigungen für die angefragte Ressource oder Aktion fehlen.

{
  "status_code": "403",
  "status_message": "Forbidden",
  "errors": [
    {
      "info": "Access denied. You do not have permission to perform this action.",
      "type": "Exception",
      "cause": {
        "details": "Access denied. You do not have permission to perform this action."
      }
    }
  ]
}

Tritt auf, wenn das angefragte query_model, ein Join-Ziel oder eine spezifische Ressource nicht in der Registry gefunden wurde.

{
  "status_code": "404",
  "status_message": "Not Found",
  "errors": [
    {
      "info": "Requested resource could not be found.",
      "type": "Exception",
      "cause": {
        "details": "Requested resource could not be found."
      }
    }
  ]
}

Tritt bei Validierungsfehlern auf (z. B. ungültige Attribute, falsche Datentypen oder unauflösbare on-Klauseln).

{
  "status_code": "422",
  "status_message": "Unprocessable Content",
  "errors": [
    {
      "info": "Validation failed. One or more fields are invalid.",
      "type": "ValueError",
      "cause": {
        "details": "Validation failed. One or more fields are invalid."
      }
    }
  ]
}

Ein unerwarteter Fehler auf Serverseite.

{
  "status_code": "500",
  "status_message": "Internal Server Error",
  "errors": [
    {
      "info": "Unexpected internal server error occurred.",
      "type": "Exception",
      "cause": {
        "details": "Unexpected internal server error occurred."
      }
    }
  ]
}

Dynamic Query

Auf dieser Seite