LLM-Driven Extraction of Unstructured Data — Built for API Deployments & ETL Pipeline Workflows
Python ai-agentsdata-engineeringdocument-aigenerative-aiidpjson-extractionllmmcp-serverocrpdf-extractionprompt-engineeringstructured-output
"""Services for querying metrics from existing tables.
This module provides the MetricsQueryService class which queries metrics
directly from source tables (usage, page_usage, workflow_execution, etc.)
and aggregates them into event_metrics_hourly/daily/monthly tables.
Note: Uses _base_manager for models with DefaultOrganizationManagerMixin
to bypass the UserContext filter when running from Celery tasks.
"""
from datetime import datetime
from typing import Any
from account_usage.models import PageUsage
from account_v2.models import Organization
from api_v2.models import APIDeployment
from django.db.models import CharField, Count, OuterRef, Q, Subquery, Sum
from django.db.models.functions import Cast, Coalesce, TruncDay, TruncHour, TruncWeek
from pipeline_v2.models import Pipeline
from usage_v2.models import Usage
from workflow_manager.execution.enum import ExecutionEntity
from workflow_manager.file_execution.models import WorkflowFileExecution
from workflow_manager.workflow_v2.models.execution import WorkflowExecution
from workflow_manager.workflow_v2.models.workflow import Workflow
from dashboard_metrics.models import ActivitySurface, Granularity
from unstract.core.data_models import ExecutionStatus
def _get_hitl_queue_model():
"""Get HITLQueue model if available (cloud-only).
Returns None on OSS where manual_review_v2 is not installed.
"""
try:
from pluggable_apps.manual_review_v2.models import HITLQueue
return HITLQueue
except ImportError:
return None
def _get_usage_queryset():
"""Get Usage queryset bypassing the organization context filter.
Usage model uses DefaultOrganizationManagerMixin which filters by
UserContext.get_organization(). This returns None in Celery tasks,
causing queries to return empty results. Use _base_manager instead.
"""
return Usage._base_manager.all()
def _activity_source_ids(organization_id: str) -> dict[str, set]:
"""Pipeline and API deployment ids by the surface they belong to."""
# Use _base_manager to bypass DefaultOrganizationManagerMixin
# (UserContext is None in Celery tasks)
pipelines = Pipeline._base_manager.filter(organization_id=organization_id)
return {
ActivitySurface.ETL: set(
pipelines.filter(pipeline_type=Pipeline.PipelineType.ETL).values_list(
"id", flat=True
)
),
ActivitySurface.TASK: set(
pipelines.filter(pipeline_type=Pipeline.PipelineType.TASK).values_list(
"id", flat=True
)
),
ActivitySurface.API: set(
APIDeployment._base_manager.filter(
organization_id=organization_id,
).values_list("id", flat=True)
),
}
def _org_file_executions(organization_id: str):
return WorkflowFileExecution.objects.filter(
workflow_execution__workflow__organization_id=organization_id,
).select_related(
"workflow_execution",
"workflow_execution__workflow",
)
def _surface_filter(organization_id: str, surfaces: list[str]) -> Q:
"""Rows whose run came from any of ``surfaces``.
``workflow`` is every run that is not from an ETL or Task pipeline or an
API deployment: manual runs, and default and app pipelines.
"""
pipelines = Pipeline._base_manager.filter(organization_id=organization_id)
subqueries = {
ActivitySurface.ETL: pipelines.filter(
pipeline_type=Pipeline.PipelineType.ETL
).values("id"),
ActivitySurface.TASK: pipelines.filter(
pipeline_type=Pipeline.PipelineType.TASK
).values("id"),
ActivitySurface.API: APIDeployment._base_manager.filter(
organization_id=organization_id
).values("id"),
}
pipeline_id = "workflow_execution__pipeline_id"
q = Q()
for surface in surfaces:
if surface == ActivitySurface.WORKFLOW:
from_known_source = Q()
for subquery in subqueries.values():
from_known_source |= Q(**{f"{pipeline_id}__in": subquery})
# Django's negation keeps rows whose pipeline_id is NULL.
q |= ~from_known_source
else:
q |= Q(**{f"{pipeline_id}__in": subqueries[surface]})
return q
def _activity_rows(
executions, source_ids: dict[str, set], with_surface: bool
) -> list[dict[str, Any]]:
"""Serialize file executions as activity rows, with tokens and cost.
``with_surface`` adds the DG-03 ``surface`` field. The default response
leaves it out, so it stays byte-identical for existing callers.
"""
results = []
for execution in executions:
pipeline_id = execution.workflow_execution.pipeline_id
# Determine execution type based on pipeline_id
if pipeline_id and pipeline_id in source_ids[ActivitySurface.ETL]:
exec_type = "etl"
elif pipeline_id and pipeline_id in source_ids[ActivitySurface.API]:
exec_type = "api"
else:
exec_type = "workflow"
row = {
"id": str(execution.id),
"execution_id": str(execution.workflow_execution.id),
"type": exec_type,
"file_name": execution.file_name,
"status": execution.status,
"workflow_name": execution.workflow_execution.workflow.workflow_name,
"created_at": execution.created_at.isoformat(),
"execution_time": execution.execution_time,
}
if with_surface:
row["surface"] = (
ActivitySurface.TASK.value
if pipeline_id and pipeline_id in source_ids[ActivitySurface.TASK]
else exec_type
)
results.append(row)
# Batch query: get aggregated token/cost per file execution
file_exec_ids = [r["id"] for r in results]
usage_agg: dict[str, dict[str, Any]] = {}
if file_exec_ids:
agg_qs = (
_get_usage_queryset()
.filter(run_id__in=file_exec_ids)
.values("run_id")
.annotate(
total_tokens=Sum("total_tokens"),
cost=Sum("cost_in_dollars"),
)
)
usage_agg = {
str(row["run_id"]): {
"total_tokens": row["total_tokens"] or 0,
"cost": round(row["cost"] or 0, 4),
}
for row in agg_qs
}
# Enrich results with LLM usage data
for r in results:
agg = usage_agg.get(r["id"], {})
r["total_tokens"] = agg.get("total_tokens", 0)
r["cost"] = agg.get("cost", 0)
return results
class MetricsQueryService:
"""Service for querying metrics from source tables.
This service provides methods to query each metric type from its
respective source table, with support for date filtering and
time-based aggregation (hourly, daily, weekly).
Metrics available:
- documents_processed: from workflow_file_execution
- pages_processed: from page_usage
- llm_calls: from usage (type=llm)
- challenges: from usage (reason=challenge)
- summarization_calls: from usage (reason=summarize)
- deployed_api_requests: from workflow_execution + api_deployment
- etl_pipeline_executions: from workflow_execution + pipeline
- llm_usage: from usage (cost_in_dollars)
- prompt_executions: from workflow_execution
"""
@staticmethod
def _get_trunc_func(granularity: str):
"""Get the appropriate truncation function for the granularity."""
trunc_map = {
Granularity.HOUR: TruncHour,
Granularity.DAY: TruncDay,
Granularity.WEEK: TruncWeek,
}
return trunc_map.get(granularity, TruncDay)
@staticmethod
def get_documents_processed(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> list[dict[str, Any]]:
"""Query documents processed from workflow_file_execution.
Counts completed file executions grouped by time period.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
Returns:
List of dicts with 'period' and 'value' keys
"""
trunc_func = MetricsQueryService._get_trunc_func(granularity)
return list(
WorkflowFileExecution.objects.filter(
workflow_execution__workflow__organization_id=organization_id,
status=ExecutionStatus.COMPLETED,
created_at__gte=start_date,
created_at__lte=end_date,
)
.annotate(period=trunc_func("created_at"))
.values("period")
.annotate(value=Count("id"))
.order_by("period")
)
@staticmethod
def _resolve_org_identifier(
organization_id: str, org_identifier: str | None = None
) -> str | None:
"""Resolve PageUsage's string org identifier from UUID PK.
PageUsage.organization_id stores Organization.organization_id (a string
like "org_abc123"), not the UUID PK used everywhere else. This helper
resolves the string identifier, accepting a pre-resolved value to avoid
redundant DB lookups when called in a loop.
Args:
organization_id: Organization UUID string (PK)
org_identifier: Pre-resolved string identifier (skips DB lookup)
Returns:
Organization string identifier, or None if org not found
"""
if org_identifier:
return org_identifier
try:
org = Organization.objects.get(id=organization_id)
return org.organization_id
except Organization.DoesNotExist:
return None
@staticmethod
def get_pages_processed(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
org_identifier: str | None = None,
) -> list[dict[str, Any]]:
"""Query pages processed from page_usage.
Sums pages_processed field grouped by time period.
Note: PageUsage.organization_id stores the Organization's string
identifier (organization.organization_id), NOT the UUID PK.
Pass org_identifier to avoid a DB lookup per call.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
org_identifier: Pre-resolved Organization.organization_id string.
If None, will be looked up from organization_id.
Returns:
List of dicts with 'period' and 'value' keys
"""
page_usage_org_id = MetricsQueryService._resolve_org_identifier(
organization_id, org_identifier
)
if not page_usage_org_id:
return []
trunc_func = MetricsQueryService._get_trunc_func(granularity)
return list(
PageUsage.objects.filter(
organization_id=page_usage_org_id,
created_at__gte=start_date,
created_at__lte=end_date,
)
.annotate(period=trunc_func("created_at"))
.values("period")
.annotate(value=Sum("pages_processed"))
.order_by("period")
)
@staticmethod
def get_llm_metrics_combined(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> list[dict[str, Any]]:
"""Query all LLM metrics from usage table in a single query.
Uses conditional aggregation to compute llm_calls, challenges,
summarization_calls, and llm_usage (cost) in one DB round-trip
instead of four separate queries.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
Returns:
List of dicts with 'period', 'llm_calls', 'challenges',
'summarization_calls', and 'llm_usage' keys
"""
trunc_func = MetricsQueryService._get_trunc_func(granularity)
return list(
_get_usage_queryset()
.filter(
organization_id=organization_id,
usage_type="llm",
created_at__gte=start_date,
created_at__lte=end_date,
)
.annotate(period=trunc_func("created_at"))
.values("period")
.annotate(
llm_calls=Count("id"),
challenges=Count("id", filter=Q(llm_usage_reason="challenge")),
summarization_calls=Count("id", filter=Q(llm_usage_reason="summarize")),
llm_usage=Sum("cost_in_dollars"),
)
.order_by("period")
)
# Mapping from metric name to the key in get_llm_metrics_combined() results.
LLM_METRIC_KEYS: dict[str, str] = {
"llm_calls": "llm_calls",
"challenges": "challenges",
"summarization_calls": "summarization_calls",
"llm_usage": "llm_usage",
}
@classmethod
def get_llm_metrics_split(
cls,
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> dict[str, list[dict[str, Any]]]:
"""Fetch combined LLM metrics once and split into per-metric series.
Returns:
Dict mapping metric name to list of {period, value} dicts.
"""
combined = cls.get_llm_metrics_combined(
organization_id, start_date, end_date, granularity
)
return {
metric_name: [
{"period": r["period"], "value": r[combined_key]} for r in combined
]
for metric_name, combined_key in cls.LLM_METRIC_KEYS.items()
}
@staticmethod
def get_deployed_api_requests(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> list[dict[str, Any]]:
"""Query API deployment requests from workflow_execution.
Counts workflow executions where pipeline_id matches an API deployment
for the organization, grouped by time period.
Uses subquery to avoid N+1 query pattern.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
Returns:
List of dicts with 'period' and 'value' keys
"""
trunc_func = MetricsQueryService._get_trunc_func(granularity)
# Use _base_manager to bypass DefaultOrganizationManagerMixin
# (UserContext is None in Celery tasks)
api_subquery = APIDeployment._base_manager.filter(
organization_id=organization_id
).values("id")
return list(
WorkflowExecution.objects.filter(
pipeline_id__in=Subquery(api_subquery),
created_at__gte=start_date,
created_at__lte=end_date,
)
.annotate(period=trunc_func("created_at"))
.values("period")
.annotate(value=Count("id"))
.order_by("period")
)
@staticmethod
def get_etl_pipeline_executions(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> list[dict[str, Any]]:
"""Query ETL pipeline executions from workflow_execution.
Counts workflow executions where pipeline_id matches an ETL pipeline
for the organization, grouped by time period.
Uses subquery to avoid N+1 query pattern.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
Returns:
List of dicts with 'period' and 'value' keys
"""
trunc_func = MetricsQueryService._get_trunc_func(granularity)
# Use _base_manager to bypass DefaultOrganizationManagerMixin
# (UserContext is None in Celery tasks)
pipeline_subquery = Pipeline._base_manager.filter(
organization_id=organization_id,
pipeline_type="ETL",
).values("id")
return list(
WorkflowExecution.objects.filter(
pipeline_id__in=Subquery(pipeline_subquery),
created_at__gte=start_date,
created_at__lte=end_date,
)
.annotate(period=trunc_func("created_at"))
.values("period")
.annotate(value=Count("id"))
.order_by("period")
)
@staticmethod
def get_prompt_executions(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> list[dict[str, Any]]:
"""Query prompt executions from workflow_execution.
Counts all workflow executions for the organization grouped by time period.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
Returns:
List of dicts with 'period' and 'value' keys
"""
trunc_func = MetricsQueryService._get_trunc_func(granularity)
return list(
WorkflowExecution.objects.filter(
workflow__organization_id=organization_id,
created_at__gte=start_date,
created_at__lte=end_date,
)
.annotate(period=trunc_func("created_at"))
.values("period")
.annotate(value=Count("id"))
.order_by("period")
)
@staticmethod
def get_failed_pages(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> list[dict[str, Any]]:
"""Query failed pages from workflow_file_execution + page_usage.
Sums pages_processed for file executions with status='ERROR',
grouped by time period based on when the failure occurred.
Note: Unlike get_pages_processed, this does NOT need org_identifier
because it joins through WorkflowFileExecution -> WorkflowExecution ->
Workflow -> Organization (UUID FK), not through PageUsage.organization_id.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
Returns:
List of dicts with 'period' and 'value' keys
"""
trunc_func = MetricsQueryService._get_trunc_func(granularity)
# Main query: failed executions with their pages, grouped by period
return list(
MetricsQueryService.failed_file_executions_with_pages(
organization_id, start_date, end_date
)
.annotate(period=trunc_func("created_at"))
.values("period")
.annotate(value=Sum("pages"))
.order_by("period")
)
@staticmethod
def failed_file_executions_with_pages(
organization_id: str,
start_date: datetime,
end_date: datetime,
):
"""Failed file executions in the range, each annotated with ``pages``.
The one definition of a failed page: ``get_failed_pages`` and the
dashboard's failure attribution both group this queryset, so their
totals agree by construction.
"""
# Subquery: get pages for each file execution from page_usage
# Join on: page_usage.run_id = workflow_file_execution.id::text
pages_subquery = (
PageUsage.objects.filter(run_id=Cast(OuterRef("id"), CharField()))
.values("run_id")
.annotate(total=Sum("pages_processed"))
.values("total")[:1]
)
return WorkflowFileExecution.objects.filter(
workflow_execution__workflow__organization_id=organization_id,
status=ExecutionStatus.ERROR,
created_at__gte=start_date,
created_at__lte=end_date,
).annotate(pages=Coalesce(Subquery(pages_subquery), 0))
@staticmethod
def get_hitl_reviews(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> list[dict[str, Any]]:
"""Query HITL review counts from manual_review_v2.
Counts all HITLQueue records created in the date range,
regardless of their current state.
Returns empty list on OSS where manual_review_v2 is not installed.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
Returns:
List of dicts with 'period' and 'value' keys
"""
HITLQueue = _get_hitl_queue_model()
if HITLQueue is None:
return []
trunc_func = MetricsQueryService._get_trunc_func(granularity)
return list(
HITLQueue._base_manager.filter(
organization_id=organization_id,
created_at__gte=start_date,
created_at__lte=end_date,
)
.annotate(period=trunc_func("created_at"))
.values("period")
.annotate(value=Count("id"))
.order_by("period")
)
@staticmethod
def get_hitl_completions(
organization_id: str,
start_date: datetime,
end_date: datetime,
granularity: str = Granularity.DAY,
) -> list[dict[str, Any]]:
"""Query completed HITL reviews from manual_review_v2.
Counts HITLQueue records with state='approved' that were approved
within the date range (using approved_at timestamp).
Returns empty list on OSS where manual_review_v2 is not installed.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
granularity: Time granularity (hour, day, week)
Returns:
List of dicts with 'period' and 'value' keys
"""
HITLQueue = _get_hitl_queue_model()
if HITLQueue is None:
return []
trunc_func = MetricsQueryService._get_trunc_func(granularity)
return list(
HITLQueue._base_manager.filter(
organization_id=organization_id,
state=HITLQueue.State.APPROVED,
approved_at__gte=start_date,
approved_at__lte=end_date,
)
.annotate(period=trunc_func("approved_at"))
.values("period")
.annotate(value=Count("id"))
.order_by("period")
)
@classmethod
def get_all_metrics_summary(
cls,
organization_id: str,
start_date: datetime,
end_date: datetime,
) -> dict[str, float]:
"""Get summary totals for all metrics.
Aggregates all metric types for the given date range.
Args:
organization_id: Organization UUID string
start_date: Start of date range
end_date: End of date range
Returns:
Dict mapping metric name to total value
"""
# Resolve org identifier once for PageUsage queries
org_identifier = cls._resolve_org_identifier(organization_id)
# Combined LLM metrics (1 query instead of 4)
llm_combined = cls.get_llm_metrics_combined(organization_id, start_date, end_date)
llm_calls_total = sum(r["llm_calls"] for r in llm_combined)
challenges_total = sum(r["challenges"] for r in llm_combined)
summarization_total = sum(r["summarization_calls"] for r in llm_combined)
llm_usage_total = sum(r["llm_usage"] or 0 for r in llm_combined)
return {
"documents_processed": sum(
r["value"]
for r in cls.get_documents_processed(
organization_id, start_date, end_date
)
),
"pages_processed": sum(
r["value"] or 0
for r in cls.get_pages_processed(
organization_id,
start_date,
end_date,
org_identifier=org_identifier,
)
),
"llm_calls": llm_calls_total,
"challenges": challenges_total,
"summarization_calls": summarization_total,
"deployed_api_requests": sum(
r["value"]
for r in cls.get_deployed_api_requests(
organization_id, start_date, end_date
)
),
"etl_pipeline_executions": sum(
r["value"]
for r in cls.get_etl_pipeline_executions(
organization_id, start_date, end_date
)
),
"llm_usage": llm_usage_total,
"prompt_executions": sum(
r["value"]
for r in cls.get_prompt_executions(organization_id, start_date, end_date)
),
"failed_pages": sum(
r["value"] or 0
for r in cls.get_failed_pages(organization_id, start_date, end_date)
),
"hitl_reviews": sum(
r["value"]
for r in cls.get_hitl_reviews(organization_id, start_date, end_date)
),
"hitl_completions": sum(
r["value"]
for r in cls.get_hitl_completions(organization_id, start_date, end_date)
),
}
@staticmethod
def get_recent_activity(
organization_id: str,
limit: int = 10,
) -> list[dict[str, Any]]:
"""Get recent processing activity across all execution types.
Returns recent file executions with type differentiation:
- etl: pipeline_id matches ETL Pipeline
- api: pipeline_id matches APIDeployment
- workflow: pipeline_id is null (manual workflow/prompt studio)
Args:
organization_id: Organization UUID string
limit: Maximum number of records to return
Returns:
List of dicts with execution details including type
"""
source_ids = _activity_source_ids(organization_id)
recent = _org_file_executions(organization_id).order_by("-created_at")[:limit]
return _activity_rows(recent, source_ids, with_surface=False)
@staticmethod
def get_activity_page(
organization_id: str,
start_date: datetime,
end_date: datetime,
statuses: list[str] | None,
surfaces: list[str] | None,
page: int,
page_size: int,
) -> dict[str, Any]:
"""Get one page of activity in a date range, with counts (DG-02).
``status_counts`` covers the range and surfaces but ignores
``statuses``, so every status filter chip can show its count;
``count`` is the number of rows matching every filter.
Args:
organization_id: Organization UUID string
start_date: Start of range, inclusive
end_date: End of range, inclusive
statuses: File execution statuses to keep, or None for all
surfaces: ActivitySurface values to keep, or None for all
page: 1-based page number
page_size: Rows per page
Returns:
Dict with ``activity`` (rows with ``surface``), ``count`` and
``status_counts``
"""
source_ids = _activity_source_ids(organization_id)
# Accepted for now: no index leads on created_at, so a wide range scans
# the org's rows in it. Responses are cached.
in_range = _org_file_executions(organization_id).filter(
created_at__gte=start_date,
created_at__lte=end_date,
)
if surfaces:
in_range = in_range.filter(_surface_filter(organization_id, surfaces))
by_status = {
row["status"]: row["n"]
for row in in_range.order_by().values("status").annotate(n=Count("id"))
}
status_counts = {s.value: by_status.pop(s.value, 0) for s in ExecutionStatus}
# Keep any status the enum does not know, so the counts still add up.
status_counts.update(by_status)
rows = in_range
if statuses:
rows = rows.filter(status__in=statuses)
count = sum(status_counts.get(s, 0) for s in statuses)
else:
count = sum(status_counts.values())
offset = (page - 1) * page_size
page_rows = rows.order_by("-created_at", "-id")[offset : offset + page_size]
return {
"activity": _activity_rows(page_rows, source_ids, with_surface=True),
"count": count,
"status_counts": status_counts,
}
@staticmethod
def _get_deployment_names(
deployment_type: str,
) -> tuple[dict[str, str], dict[str, Any]]:
"""Get deployment ID->name mapping and execution filter for a type.
Uses the default model manager which auto-filters by the current
user's organization via DefaultOrganizationManagerMixin.
Returns:
(dep_names, exec_filter) or ({}, {}) if type is invalid.
"""
if deployment_type == ExecutionEntity.API.value:
dep_list = list(APIDeployment.objects.all().values_list("id", "display_name"))
exec_filter = {"pipeline_id__in": [d[0] for d in dep_list]}
elif deployment_type in (
ExecutionEntity.ETL.value,
ExecutionEntity.TASK.value,
):
dep_list = list(
Pipeline.objects.filter(
pipeline_type=deployment_type,
).values_list("id", "pipeline_name")
)
exec_filter = {"pipeline_id__in": [d[0] for d in dep_list]}
elif deployment_type == ExecutionEntity.WORKFLOW.value:
dep_list = list(Workflow.objects.all().values_list("id", "workflow_name"))
exec_filter = {
"pipeline_id__isnull": True,
"workflow_id__in": [d[0] for d in dep_list],
}
else:
return {}, {}
dep_names = {str(dep_id): name for dep_id, name in dep_list}
return dep_names, exec_filter
@staticmethod
def _build_exec_stats(
exec_rows: list[dict], dep_field: str
) -> tuple[dict[str, str], dict[str, dict[str, Any]]]:
"""Build exec->deployment mapping and per-deployment stats."""
exec_to_dep: dict[str, str] = {}
dep_exec_stats: dict[str, dict[str, Any]] = {}
status_counters = {
ExecutionStatus.COMPLETED.value: "completed",
ExecutionStatus.ERROR.value: "failed",
}
def _default_stats() -> dict[str, Any]:
return {
"total": 0,
"completed": 0,
"failed": 0,
"first_execution_at": None,
"last_execution_at": None,
}
for e in exec_rows:
exec_id = str(e["id"])
dep_id = str(e[dep_field])
exec_to_dep[exec_id] = dep_id
stats = dep_exec_stats.setdefault(dep_id, _default_stats())
stats["total"] += 1
counter_key = status_counters.get(str(e["status"] or ""))
if counter_key:
stats[counter_key] += 1
ts = e["created_at"]
if not ts:
continue
first = stats["first_execution_at"]
last = stats["last_execution_at"]
stats["first_execution_at"] = ts if first is None else min(first, ts)
stats["last_execution_at"] = ts if last is None else max(last, ts)
return exec_to_dep, dep_exec_stats
@staticmethod
def _get_pages_by_deployment(
exec_rows: list[dict], exec_to_dep: dict[str, str]
) -> dict[str, int]:
"""Query pages processed, aggregated by deployment.
Two queries:
1. WorkflowFileExecution → map file_exec_id to workflow_execution_id
2. PageUsage → aggregate pages by file_exec_id, then map to deployment
"""
exec_ids = [e["id"] for e in exec_rows]
if not exec_ids:
return {}
# Map file_execution_id → workflow_execution_id
file_exec_map: dict[str, str] = {}
for fid, weid in WorkflowFileExecution.objects.filter(
workflow_execution_id__in=exec_ids,
).values_list("id", "workflow_execution_id"):
file_exec_map[str(fid)] = str(weid)
if not file_exec_map:
return {}
page_rows = (
PageUsage.objects.filter(
run_id__in=list(file_exec_map.keys()),
)
.values("run_id")
.annotate(pages=Sum("pages_processed"))
)
dep_pages: dict[str, int] = {}
for row in page_rows:
we_id = file_exec_map.get(row["run_id"])
dep_id = exec_to_dep.get(we_id) if we_id else None
if dep_id:
dep_pages[dep_id] = dep_pages.get(dep_id, 0) + (row["pages"] or 0)
return dep_pages
@staticmethod
def _aggregate_usage_by_deployment(
usage_rows: list[dict], exec_to_dep: dict[str, str]
) -> dict[str, dict[str, Any]]:
"""Aggregate LLM token usage by deployment."""
dep_agg: dict[str, dict[str, Any]] = {}
for row in usage_rows:
dep_id = exec_to_dep.get(row["execution_id"])
if not dep_id:
continue
if dep_id not in dep_agg:
dep_agg[dep_id] = {
"total_tokens": 0,
"total_cost": 0,
"call_count": 0,
}
agg = dep_agg[dep_id]
agg["total_tokens"] += row["total_tokens"] or 0
agg["total_cost"] += row["total_cost"] or 0
agg["call_count"] += row["call_count"] or 0
return dep_agg
@staticmethod
def get_deployment_usage(
deployment_type: str,
start_date: datetime,
end_date: datetime,
) -> list[dict[str, Any]]:
"""Get LLM usage grouped by deployment.
Joins workflow_execution -> deployment table -> usage to aggregate
token usage per deployment. Supports 4 deployment types:
- API: workflow_execution.pipeline_id -> api_deployment
- ETL: workflow_execution.pipeline_id -> pipeline (type=ETL)
- TASK: workflow_execution.pipeline_id -> pipeline (type=TASK)
- WF: workflow_execution with no pipeline (direct workflow runs)
Also includes execution status counts, pages processed (via
page_usage), and execution date range per deployment.
Args:
deployment_type: One of API, ETL, TASK, WF
start_date: Start of date range
end_date: End of date range
Returns:
List of dicts ordered by total_tokens descending.
"""
# Step 1: Get deployment names and execution filter
dep_names, exec_filter = MetricsQueryService._get_deployment_names(
deployment_type
)
if not dep_names:
return []
# Step 2: Get executions with status and timestamp
dep_field = (
"workflow_id"
if deployment_type == ExecutionEntity.WORKFLOW.value
else "pipeline_id"
)
# exec_filter already scopes to org-owned deployment IDs
exec_rows = list(
WorkflowExecution.objects.filter(
created_at__gte=start_date,
created_at__lte=end_date,
**exec_filter,
).values("id", dep_field, "status", "created_at")
)
if not exec_rows:
return []
# Step 3: Build exec->deployment mapping and stats
exec_to_dep, dep_exec_stats = MetricsQueryService._build_exec_stats(
exec_rows, dep_field
)
# Step 4: Query LLM usage
# Use _get_usage_queryset() to bypass DefaultOrganizationManagerMixin
# which auto-filters by UserContext and can return empty results.
usage_rows = list(
_get_usage_queryset()
.filter(
execution_id__in=list(exec_to_dep.keys()),
usage_type="llm",
created_at__gte=start_date,
created_at__lte=end_date,
)
.values("execution_id")
.annotate(
total_tokens=Sum("total_tokens"),
total_cost=Sum("cost_in_dollars"),
call_count=Count("id"),
)
)
# Step 5: Query pages and aggregate usage
dep_pages = MetricsQueryService._get_pages_by_deployment(exec_rows, exec_to_dep)
dep_agg = MetricsQueryService._aggregate_usage_by_deployment(
usage_rows, exec_to_dep
)
# Ensure all deployments with executions appear in results
empty_agg = {"total_tokens": 0, "total_cost": 0, "call_count": 0}
for dep_id in dep_exec_stats:
dep_agg.setdefault(dep_id, dict(empty_agg))
# Step 6: Build results sorted by total_tokens descending
results = []
for dep_id, agg in sorted(
dep_agg.items(), key=lambda x: x[1]["total_tokens"], reverse=True
):
stats = dep_exec_stats.get(dep_id, {})
results.append(
{
"deployment_id": dep_id,
"deployment_name": dep_names.get(dep_id, "Unknown"),
"total_tokens": agg["total_tokens"],
"total_cost": round(agg["total_cost"], 4),
"call_count": agg["call_count"],
"execution_count": stats.get("total", 0),
"completed_executions": stats.get("completed", 0),
"failed_executions": stats.get("failed", 0),
"total_pages_processed": dep_pages.get(dep_id, 0),
"first_execution_at": (
stats["first_execution_at"].isoformat()
if stats.get("first_execution_at")
else None
),
"last_execution_at": (
stats["last_execution_at"].isoformat()
if stats.get("last_execution_at")
else None
),
}
)
return results