:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
import re
import json
import time
import asyncio
from dataclasses import field
from datetime import UTC, datetime
from typing import TYPE_CHECKING, Any, Optional
from django.conf import settings
from django.core.serializers.json import DjangoJSONEncoder
from django.utils import timezone
import structlog
from asgiref.sync import async_to_sync
from posthoganalytics import capture_exception
from pydantic import BaseModel
from rest_framework.exceptions import APIException, PermissionDenied
from posthog.schema import (
AssistantFunnelsQuery,
AssistantHogQLQuery,
AssistantLifecycleQuery,
AssistantPathsQuery,
AssistantRetentionQuery,
AssistantStickinessQuery,
AssistantTrendsQuery,
ChartDisplayType,
DataVisualizationNode,
FunnelsQuery,
FunnelVizType,
HogQLQuery,
LifecycleQuery,
PathsQuery,
RetentionQuery,
StickinessQuery,
TrendsQuery,
)
from posthog.hogql.constants import LimitContext
from posthog.hogql.errors import (
BaseHogQLError,
ExposedHogQLError,
NotImplementedError as HogQLNotImplementedError,
QueryError,
ResolutionError,
SyntaxError as HogQLSyntaxError,
TableAccessDeniedError,
)
from posthog.hogql.transforms.trino.errors import TrinoLoweringError
from posthog.api.services.query import process_query_dict
from posthog.clickhouse.client.execute_async import get_query_status
from posthog.clickhouse.client.limit import ConcurrencyLimitExceeded
from posthog.clickhouse.query_tagging import Feature, Product, get_query_tags, tag_queries, tags_context
from posthog.dataclasses import frozen
from posthog.errors import (
CH_TRANSIENT_ERRORS,
ExposedCHQueryError,
InternalCHQueryError,
QueryErrorCategory,
classify_query_error,
error_code_from_type,
internal_ch_error_user_message,
unwrap_exposed_hogql_error,
)
from posthog.event_usage import EventSource
from posthog.exceptions import (
TRANSIENT_QUERY_ERROR_CODES,
ClickHouseQueryMemoryLimitExceeded,
ClickHouseQueryTimeOut,
QueryRanConcurrently,
)
from posthog.hogql_queries.query_runner import BLOCKING_EXECUTION_MODES, ExecutionMode
from posthog.hogql_queries.utils.result_series import get_action_names
from posthog.models import Team
from posthog.sync import database_sync_to_async
from products.access_control.backend.facade.user_access_control import UserAccessControlError
from ee.hogai.context.insight.clickhouse_rejections import describe_clickhouse_rejection
from ee.hogai.context.insight.format import (
NULL_MARKER,
TRUNCATED_MARKER,
BoxPlotResultsFormatter,
FunnelResultsFormatter,
LifecycleResultsFormatter,
PathsResultsFormatter,
RetentionResultsFormatter,
SQLResultsFormatter,
StickinessResultsFormatter,
TrendsResultsFormatter,
format_access_control_warnings,
format_warehouse_sync_warnings,
get_boxplot_results,
is_boxplot_query,
)
from ee.hogai.tool_errors import (
MaxToolAccessDeniedError,
MaxToolError,
MaxToolErrorType,
MaxToolFatalError,
MaxToolRetryableError,
MaxToolTransientError,
)
from ee.hogai.utils.prompt import format_prompt_string
from ee.hogai.utils.query import validate_assistant_query
from ee.hogai.utils.types.base import AnyAssistantGeneratedQuery, AnyPydanticModelQuery
if TYPE_CHECKING:
from posthog.models import User
from .prompts import (
BOX_PLOT_EXAMPLE_PROMPT,
FALLBACK_EXAMPLE_PROMPT,
FUNNEL_STEPS_EXAMPLE_PROMPT,
FUNNEL_TIME_TO_CONVERT_EXAMPLE_PROMPT,
FUNNEL_TRENDS_EXAMPLE_PROMPT,
LIFECYCLE_EXAMPLE_PROMPT,
PATHS_EXAMPLE_PROMPT,
QUERY_RESULTS_PROMPT,
RETENTION_EXAMPLE_PROMPT,
SQL_EXAMPLE_PROMPT,
STICKINESS_EXAMPLE_PROMPT,
TRENDS_EXAMPLE_PROMPT,
)
logger = structlog.get_logger(__name__)
TIMING_LOG_PREFIX = "[QUERY_EXECUTOR]"
# Splits the most common HogQL query errors, which share one `code_name`, so analytics can count each
# without recording the message. The message can quote the caller's query.
HOGQL_ERROR_CODES_BY_MESSAGE_PREFIX = (
("Unable to resolve field", "hogql_unresolved_field"),
("Unknown table `", "hogql_unknown_table"),
("Unsupported function call", "hogql_unsupported_function"),
)
RECORDABLE_STATUS_ERROR_CODE = re.compile(r"[a-z][a-z0-9_]{0,99}")
QUERY_RESULT_ERROR_CODE = "query_result_error"
HOGQL_INPUT_ERROR_CODES = frozenset({QueryError.code_name, HogQLSyntaxError.code_name, TrinoLoweringError.code_name})
def get_clickhouse_error_code(error: BaseException | None) -> str | None:
if not isinstance(error, InternalCHQueryError):
return None
code = error_code_from_type(error)
return code if internal_ch_error_user_message(code) else None
def _hogql_code_from_message(message: str) -> str | None:
for prefix, code in HOGQL_ERROR_CODES_BY_MESSAGE_PREFIX:
if message.startswith(prefix):
return code
return None
def hogql_error_code(error: BaseException | None) -> str | None:
"""Return a code for a HogQL error that holds no caller input, or None for any other error."""
if not isinstance(error, BaseHogQLError):
return None
if code := _hogql_code_from_message(str(error)):
return code
if isinstance(error, ExposedHogQLError):
return error.code_name
if isinstance(error, HogQLNotImplementedError):
return "hogql_not_implemented"
if isinstance(error, ResolutionError):
return "hogql_resolution_error"
return "hogql_internal_error"
def internal_error_code(error: BaseException) -> str:
"""Return a code for an internal failure that holds no caller input."""
return hogql_error_code(error) or error_code_from_type(error)
def _hogql_tool_error(error: ExposedHogQLError) -> MaxToolError:
cause = unwrap_exposed_hogql_error(error)
if isinstance(cause, TableAccessDeniedError):
return MaxToolFatalError(str(error), error_type="permission")
if isinstance(cause, (QueryError, HogQLSyntaxError)):
return MaxToolRetryableError(str(error), error_type="validation", error_code=hogql_error_code(cause))
# User-safe errors can also describe outages; only known input errors should skip exception capture.
return MaxToolRetryableError(str(error), error_type="internal", error_code=internal_error_code(cause))
def _query_status_tool_error(query_status: dict[str, Any]) -> MaxToolError:
error_code: str | None = query_status.get("error_code")
error_message: str | None = query_status.get("error_message")
if error_code in TRANSIENT_QUERY_ERROR_CODES:
return MaxToolTransientError(
error_message or "The query service was too busy to run this query.",
error_type="api_5xx" if error_code == QueryRanConcurrently.default_code else "rate_limited",
)
error_code = error_code.lower() if error_code and error_code != APIException.default_code else None
if error_code and not RECORDABLE_STATUS_ERROR_CODE.fullmatch(error_code):
error_code = None
if error_code == TableAccessDeniedError.code_name:
return MaxToolFatalError(error_message or "Table access denied.", error_type="permission")
if error_message and error_code in HOGQL_INPUT_ERROR_CODES:
return MaxToolRetryableError(
error_message, error_type="validation", error_code=_hogql_code_from_message(error_message) or error_code
)
if rejection := describe_clickhouse_rejection(error_code, error_message):
return MaxToolRetryableError(rejection, error_type="validation", error_code=error_code)
# Async status loses the exception type, so keep retry advice without guessing its category.
error_type: MaxToolErrorType = (
"memory_limit" if error_code == ClickHouseQueryMemoryLimitExceeded.default_code else "internal"
)
message = (
error_message or internal_ch_error_user_message(error_code) or "The query failed because of an internal error"
)
return MaxToolRetryableError(message, error_type=error_type, error_code=error_code)
@frozen
class FormattedQueryResult:
formatted: str
fallback_used: bool
response: dict = field(repr=False)
def is_supported_query(query: AnyPydanticModelQuery | AnyAssistantGeneratedQuery) -> bool:
return isinstance(
query,
AssistantTrendsQuery
| TrendsQuery
| AssistantFunnelsQuery
| FunnelsQuery
| AssistantLifecycleQuery
| LifecycleQuery
| AssistantPathsQuery
| PathsQuery
| AssistantStickinessQuery
| StickinessQuery
| LifecycleQuery
| AssistantRetentionQuery
| RetentionQuery
| AssistantHogQLQuery
| HogQLQuery
| DataVisualizationNode,
)
class AssistantQueryExecutor:
"""
Reusable class for executing queries and formatting results for the AI assistant.
This class handles the complete query lifecycle:
- Executes queries using the appropriate execution mode
- Handles async query polling when needed
- Formats results using query-specific formatters
- Provides fallback error handling and result compression
Can be used by QueryExecutorNode and other components that need to run and format queries
for AI assistant responses.
Attributes:
_team: The PostHog team context for query execution
_utc_now_datetime: Current UTC datetime for time-based calculations
"""
WAIT_TIME_S = 0.5
def __init__(
self,
team: Team,
utc_now_datetime: datetime,
user: "User",
event_source: EventSource = EventSource.POSTHOG_AI,
max_sql_result_chars: int | None = None,
) -> None:
self._team = team
self._utc_now_datetime = utc_now_datetime
self._user = user
self._event_source = event_source
if max_sql_result_chars is not None and max_sql_result_chars < SQLResultsFormatter.MIN_RESULT_CHARS:
raise ValueError("The SQL preview budget must be at least 512 characters")
self._max_sql_result_chars = max_sql_result_chars
async def arun_format_and_capture(
self,
query: AnyPydanticModelQuery | AnyAssistantGeneratedQuery,
execution_mode: Optional[ExecutionMode] = None,
insight_id=None,
debug_timing=False,
truncate_results: bool = True,
) -> FormattedQueryResult:
"""
Run a query and format the results with detailed fallback information.
Args:
query: The query object (AssistantTrendsQuery, AssistantFunnelsQuery, etc.)
execution_mode: Optional execution mode override. If None, defaults to:
- RECENT_CACHE_CALCULATE_ASYNC_IF_STALE in production
- CALCULATE_BLOCKING_ALWAYS in tests
Returns:
A FormattedQueryResult carrying the formatted text, whether the JSON fallback was
used, and the raw response for callers that need the columns and rows themselves.
Raises:
Exception: If query execution fails with descriptive error messages
"""
start_time = time.time()
query_type = type(query).__name__
if debug_timing:
logger.warning(f"{TIMING_LOG_PREFIX} Starting arun_format_and_capture for {query_type}")
try:
active_tags = get_query_tags()
with tags_context(
product=Product.MAX_AI,
feature=active_tags.feature or Feature.POSTHOG_AI,
team_id=self._team.pk,
org_id=self._team.organization_id,
):
if insight_id:
# Including insight ID for insight search
tag_queries(insight_id=insight_id)
execute_start = time.time()
response_dict = await self.aexecute_query(query, execution_mode, debug_timing=debug_timing)
execute_elapsed = time.time() - execute_start
if debug_timing:
logger.warning(f"{TIMING_LOG_PREFIX} aexecute_query completed in {execute_elapsed:.3f}s")
try:
# Attempt to format results using query-specific formatters
format_start = time.time()
formatted_results = await self._compress_results(
query, response_dict, debug_timing=debug_timing, truncate_results=truncate_results
)
format_elapsed = time.time() - format_start
total_elapsed = time.time() - start_time
if debug_timing:
logger.warning(
f"{TIMING_LOG_PREFIX} _compress_results completed in {format_elapsed:.3f}s, "
f"total arun_format_and_capture: {total_elapsed:.3f}s"
)
return FormattedQueryResult(formatted=formatted_results, fallback_used=False, response=response_dict)
except Exception as err:
if not isinstance(err, NotImplementedError):
capture_exception(err, properties={"tag": "max_ai"})
# Fallback to raw JSON if formatting fails - ensures robustness
fallback_start = time.time()
fallback_results = json.dumps(response_dict["results"], cls=DjangoJSONEncoder, separators=(",", ":"))
if isinstance(query, AssistantHogQLQuery | HogQLQuery | DataVisualizationNode):
fallback_results = SQLResultsFormatter.bound_fallback(fallback_results, self._max_sql_result_chars)
fallback_elapsed = time.time() - fallback_start
total_elapsed = time.time() - start_time
if debug_timing:
logger.warning(
f"{TIMING_LOG_PREFIX} Fallback JSON formatting completed in {fallback_elapsed:.3f}s, "
f"total with fallback: {total_elapsed:.3f}s"
)
return FormattedQueryResult(formatted=fallback_results, fallback_used=True, response=response_dict)
except Exception:
elapsed = time.time() - start_time
if debug_timing:
logger.exception(f"{TIMING_LOG_PREFIX} arun_format_and_capture failed after {elapsed:.3f}s")
raise
async def arun_and_format_query(
self,
query: AnyPydanticModelQuery | AnyAssistantGeneratedQuery,
execution_mode: Optional[ExecutionMode] = None,
insight_id=None,
debug_timing=False,
truncate_results: bool = True,
) -> tuple[str, bool]:
result = await self.arun_format_and_capture(
query,
execution_mode,
insight_id=insight_id,
debug_timing=debug_timing,
truncate_results=truncate_results,
)
return result.formatted, result.fallback_used
@async_to_sync
async def run_and_format_query(
self,
query: AnyPydanticModelQuery | AnyAssistantGeneratedQuery,
execution_mode: Optional[ExecutionMode] = None,
debug_timing=False,
) -> tuple[str, bool]:
"""
Run a query and format the results with detailed fallback information.
Args:
query: The query object (AssistantTrendsQuery, AssistantFunnelsQuery, etc.)
execution_mode: Optional execution mode override. If None, defaults to:
- RECENT_CACHE_CALCULATE_ASYNC_IF_STALE in production
- CALCULATE_BLOCKING_ALWAYS in tests
Returns:
Tuple of (formatted results as string, whether fallback was used)
- formatted results: Query results formatted for AI consumption
- fallback used: True if JSON fallback was used due to formatting errors
Raises:
Exception: If query execution fails with descriptive error messages
"""
start_time = time.time()
query_type = type(query).__name__
if debug_timing:
logger.warning(f"{TIMING_LOG_PREFIX} [SYNC->ASYNC] Starting arun_and_format_query for {query_type}")
try:
sync_start = time.time()
result = await self.arun_and_format_query(query, execution_mode, debug_timing=debug_timing)
sync_elapsed = time.time() - sync_start
total_elapsed = time.time() - start_time
if debug_timing:
logger.warning(
f"{TIMING_LOG_PREFIX} [SYNC->ASYNC] Sync execution took {sync_elapsed:.3f}s, "
f"async wrapper overhead: {(total_elapsed - sync_elapsed) * 1000:.1f}ms, "
f"total: {total_elapsed:.3f}s"
)
return result
except Exception:
elapsed = time.time() - start_time
if debug_timing:
logger.exception(f"{TIMING_LOG_PREFIX} [SYNC->ASYNC] arun_and_format_query failed after {elapsed:.3f}s")
raise
async def aexecute_query(
self,
query: AnyPydanticModelQuery | AnyAssistantGeneratedQuery,
execution_mode: Optional[ExecutionMode] = None,
debug_timing=False,
) -> dict:
"""
Execute a query and return the response dict.
Args:
query: The query object
execution_mode: Optional execution mode override
Returns:
Response dict with query results
Raises:
Exception: If query execution fails
"""
start_time = time.time()
query_type = type(query).__name__
# Set appropriate execution mode based on environment
if execution_mode is None:
execution_mode = (
ExecutionMode.RECENT_CACHE_CALCULATE_ASYNC_IF_STALE
if not settings.TEST
else ExecutionMode.CALCULATE_BLOCKING_ALWAYS
)
if debug_timing:
logger.warning(
f"{TIMING_LOG_PREFIX} Starting aexecute_query for {query_type} with mode {execution_mode.value}"
)
try:
# Execute the query using PostHog's query processing system
process_start = time.time()
if debug_timing:
logger.warning(f"{TIMING_LOG_PREFIX} Calling process_query_dict")
# Snapshot the caller's query tags from the async context so we can replay them inside
# the threaded sync function — `database_sync_to_async` crosses a thread boundary and
# downstream code (e.g. `enqueue_process_query_task`) reads `get_query_tags()` from the
# executing thread to forward to Celery, where contextvars do not propagate.
parent_tag_kwargs = get_query_tags().model_dump(exclude_none=True)
team = self._team
user = self._user
event_source = self._event_source
query_dict = query.model_dump(mode="json")
def process_query_dict_with_tags() -> dict | BaseModel:
with tags_context(**parent_tag_kwargs):
return process_query_dict(
team,
query_dict,
execution_mode=execution_mode,
limit_context=LimitContext.POSTHOG_AI,
user=user,
analytics_props={"source": event_source},
)
# If the query has a blocking execution, execute on a separate thread. Otherwise, use the main thread
# as it only does lightweight ORM retrievals and Redis calls. If we run in tests, do not spawn another thread.
results_response = await database_sync_to_async(
process_query_dict_with_tags, thread_sensitive=execution_mode not in BLOCKING_EXECUTION_MODES
)()
process_elapsed = time.time() - process_start
if debug_timing:
logger.warning(f"{TIMING_LOG_PREFIX} process_query_dict completed in {process_elapsed:.3f}s")
# Normalize response to dict format for consistent handling
if isinstance(results_response, dict):
response_dict = results_response
else:
response_dict = results_response.model_dump(mode="json")
# Handle async queries that may need polling
if query_status := response_dict.get("query_status"):
if not query_status["complete"]:
polling_start = time.time()
poll_count = 0
total_wait_s = 0.0
if debug_timing:
logger.warning(
f"{TIMING_LOG_PREFIX} Query returned incomplete, starting async polling (query_id={query_status['id']})"
)
# Poll async query until completion
# Total wait time: 5 minutes with linear increments
while total_wait_s <= 60 * 5:
poll_count += 1
total_wait_s += self.WAIT_TIME_S
if poll_count % 10 == 0 and debug_timing: # Log every 10 polls
logger.warning(
f"{TIMING_LOG_PREFIX} Polling attempt {poll_count}, total wait: {total_wait_s:.1f}s"
)
await asyncio.sleep(self.WAIT_TIME_S) # wait in seconds
status_check_start = time.time()
# Fast operation–Redis access
query_status_res = await database_sync_to_async(get_query_status, thread_sensitive=True)(
team_id=self._team.pk, query_id=query_status["id"]
)
status_check_elapsed = time.time() - status_check_start
total_wait_s += status_check_elapsed
query_status = query_status_res.model_dump(mode="json")
if status_check_elapsed > 0.5 and debug_timing: # Log slow status checks
logger.warning(f"{TIMING_LOG_PREFIX} Slow status check: {status_check_elapsed:.3f}s")
if query_status["complete"]:
polling_elapsed = time.time() - polling_start
if debug_timing:
logger.warning(
f"{TIMING_LOG_PREFIX} Async query completed after {poll_count} polls, "
f"total polling time: {polling_elapsed:.3f}s"
)
break
else:
# Query timed out after maximum wait time
polling_elapsed = time.time() - polling_start
if debug_timing:
logger.error(
f"{TIMING_LOG_PREFIX} Query timeout after {poll_count} polls, {polling_elapsed:.3f}s"
)
raise ClickHouseQueryTimeOut(
"Query hasn't completed in time. It's worth trying again, maybe with a shorter time range."
)
# Check for query execution errors before using results
if query_status.get("error"):
raise _query_status_tool_error(query_status)
# Use the completed query results
response_dict = query_status["results"]
except MaxToolError:
raise
except UserAccessControlError as err:
raise MaxToolAccessDeniedError(err.resource, err.required_level) from err
except (PermissionDenied, TableAccessDeniedError) as err:
raise MaxToolFatalError(str(err), error_type="permission") from err
except (*CH_TRANSIENT_ERRORS, ConcurrencyLimitExceeded, QueryRanConcurrently) as err:
error_type: MaxToolErrorType = (
"rate_limited" if classify_query_error(err) == QueryErrorCategory.RATE_LIMITED else "api_5xx"
)
raise MaxToolTransientError(
str(err), error_type=error_type, error_code=get_clickhouse_error_code(err)
) from err
except ExposedHogQLError as err:
raise _hogql_tool_error(err) from err
except (
APIException,
HogQLNotImplementedError,
ExposedCHQueryError,
) as err:
elapsed = time.time() - start_time
# Handle known query execution errors with user-friendly messages
err_message = str(err)
if isinstance(err, APIException):
if isinstance(err.detail, dict):
err_message = ", ".join(f"{key}: {value}" for key, value in err.detail.items())
elif isinstance(err.detail, list):
err_message = ", ".join(map(str, err.detail))
if debug_timing:
logger.exception(f"{TIMING_LOG_PREFIX} Query execution failed after {elapsed:.3f}s: {err_message}")
error_type = (
"validation"
if isinstance(err, APIException) or classify_query_error(err) == QueryErrorCategory.USER_ERROR
else "internal"
)
if isinstance(err, ClickHouseQueryTimeOut):
error_type = "timeout"
elif isinstance(err, ClickHouseQueryMemoryLimitExceeded):
error_type = "memory_limit"
elif isinstance(err, APIException) and err.status_code >= 500:
if classify_query_error(err) != QueryErrorCategory.QUERY_PERFORMANCE_ERROR:
raise MaxToolFatalError(err_message, error_type="api_5xx") from err
elif isinstance(err, APIException) and err.status_code == 429:
raise MaxToolTransientError(err_message, error_type="rate_limited") from err
error_code = internal_error_code(err) if error_type == "internal" else get_clickhouse_error_code(err)
raise MaxToolRetryableError(err_message, error_type=error_type, error_code=error_code) from err
except Exception as err:
if isinstance(err, InternalCHQueryError):
error_code = error_code_from_type(err)
if rejection := describe_clickhouse_rejection(error_code):
raise MaxToolRetryableError(rejection, error_type="validation", error_code=error_code) from err
elapsed = time.time() - start_time
# Catch-all for unexpected errors during query execution. Surface the underlying error
# text (truncated) so callers can diagnose the failure instead of an opaque message —
# e.g. an invalid-UTF-8 encoding error points straight at substringUTF8().
if debug_timing:
logger.exception(f"{TIMING_LOG_PREFIX} Unknown error during query execution after {elapsed:.3f}s")
err_message = str(err).strip() or repr(err)
max_len = 500
if len(err_message) > max_len:
err_message = err_message[:max_len] + "… (truncated)"
raise Exception(f"There was an unknown error running this query: {err_message}") from err
# A failed query can come back as a structurally-valid response that carries an `error`
# field and empty `results` instead of raising — e.g. a direct-SQL adapter statement
# timeout (`_execute_direct_sql_query` stores `result.error`), or a ClickHouse error
# captured in debug mode. Without this guard that response is formatted as a header-only
# table, indistinguishable from "zero rows matched". Surface it as an error, mirroring the
# `query_status.error` check the async-polling branch above already does.
if isinstance(response_dict, dict) and (error := response_dict.get("error")):
raise MaxToolRetryableError(str(error), error_type="internal", error_code=QUERY_RESULT_ERROR_CODE)
total_elapsed = time.time() - start_time
if debug_timing:
logger.warning(f"{TIMING_LOG_PREFIX} aexecute_query completed successfully in {total_elapsed:.3f}s")
return response_dict
async def _get_action_names(self, query: BaseModel) -> dict[int, str | None]:
# The formatters label an action series by the action's current name, which only the database holds.
return await database_sync_to_async(get_action_names, thread_sensitive=False)(
self._team, query.model_dump(mode="json")
)
async def _compress_results(
self,
query: AnyPydanticModelQuery | AnyAssistantGeneratedQuery,
response: dict,
debug_timing=False,
truncate_results: bool = True,
) -> str:
"""
Format query results using appropriate formatter based on query type.
Args:
query: The query object to determine formatting approach
response: Raw query response dict containing results and metadata
Returns:
Formatted results as a string optimized for AI assistant consumption
Raises:
NotImplementedError: If the query type is not supported
"""
start_time = time.time()
query_type = type(query).__name__
formatter_name = None
if not is_supported_query(query):
raise NotImplementedError(f"Unsupported query type: {query_type}")
warning_prefix = format_warehouse_sync_warnings(response) + format_access_control_warnings(response)
result_budget = self._max_sql_result_chars
if isinstance(query, AssistantHogQLQuery | HogQLQuery | DataVisualizationNode) and result_budget is not None:
result_budget -= len(warning_prefix)
if result_budget < SQLResultsFormatter.MIN_RESULT_CHARS:
return SQLResultsFormatter.bound_fallback(
"[SQL result preview omitted because query warnings exceed the preview budget.]\n" + warning_prefix,
self._max_sql_result_chars,
)
try:
# Handle assistant-specific query types with direct formatting
if isinstance(query, AssistantTrendsQuery | TrendsQuery):
if is_boxplot_query(query):
formatter_name = "BoxPlotResultsFormatter"
result = BoxPlotResultsFormatter(get_boxplot_results(response)).format()
else:
formatter_name = "TrendsResultsFormatter"
result = TrendsResultsFormatter(
query,
response["results"],
self._team,
self._utc_now_datetime,
resolved_date_range=response.get("resolved_date_range"),
resolved_compare_date_range=response.get("resolved_compare_date_range"),
action_names=await self._get_action_names(query),
).format()
elif isinstance(query, AssistantFunnelsQuery | FunnelsQuery):
formatter_name = "FunnelResultsFormatter"
formatter = FunnelResultsFormatter(query, response["results"], self._team, self._utc_now_datetime)
# Contains a nested ClickHouse query in the date ranges
result = await database_sync_to_async(formatter.format, thread_sensitive=False)()
elif isinstance(query, AssistantLifecycleQuery | LifecycleQuery):
formatter_name = "LifecycleResultsFormatter"
result = LifecycleResultsFormatter(
query, response["results"], action_names=await self._get_action_names(query)
).format()
elif isinstance(query, AssistantPathsQuery | PathsQuery):
formatter_name = "PathsResultsFormatter"
result = PathsResultsFormatter(response["results"]).format()
elif isinstance(query, AssistantStickinessQuery | StickinessQuery):
formatter_name = "StickinessResultsFormatter"
result = StickinessResultsFormatter(
query, response["results"], action_names=await self._get_action_names(query)
).format()
elif isinstance(query, AssistantRetentionQuery | RetentionQuery):
formatter_name = "RetentionResultsFormatter"
result = RetentionResultsFormatter(query, response["results"]).format()
elif isinstance(query, DataVisualizationNode):
formatter_name = "SQLResultsFormatter"
max_cell_length = SQLResultsFormatter.MAX_CELL_LENGTH if truncate_results else None
result = SQLResultsFormatter(
query.source,
response["results"],
response["columns"],
max_cell_length=max_cell_length,
max_result_chars=result_budget,
).format()
elif isinstance(query, AssistantHogQLQuery | HogQLQuery):
formatter_name = "SQLResultsFormatter"
max_cell_length = SQLResultsFormatter.MAX_CELL_LENGTH if truncate_results else None
result = SQLResultsFormatter(
query,
response["results"],
response["columns"],
max_cell_length=max_cell_length,
max_result_chars=result_budget,
).format()
else:
raise NotImplementedError(f"Unsupported query type: {query_type}")
elapsed = time.time() - start_time
if debug_timing:
logger.warning(
f"{TIMING_LOG_PREFIX} {formatter_name}.format() completed in {elapsed:.3f}s for {query_type}"
)
if warning_prefix:
result = warning_prefix + result
if isinstance(query, AssistantHogQLQuery | HogQLQuery | DataVisualizationNode):
result = SQLResultsFormatter.bound_fallback(result, self._max_sql_result_chars)
return result
except Exception:
elapsed = time.time() - start_time
if debug_timing:
logger.exception(f"{TIMING_LOG_PREFIX} _compress_results failed after {elapsed:.3f}s for {query_type}")
raise
def _is_boxplot_query(query: AnyPydanticModelQuery | AnyAssistantGeneratedQuery) -> bool:
trends_filter = getattr(query, "trendsFilter", None)
if trends_filter:
display = getattr(trends_filter, "display", None)
if display == ChartDisplayType.BOX_PLOT:
return True
return False
def get_example_prompt(query: AnyPydanticModelQuery | AnyAssistantGeneratedQuery) -> str:
if isinstance(query, AssistantTrendsQuery | TrendsQuery):
if _is_boxplot_query(query):
return BOX_PLOT_EXAMPLE_PROMPT
return TRENDS_EXAMPLE_PROMPT
if isinstance(query, AssistantFunnelsQuery | FunnelsQuery):
if (
not query.funnelsFilter
or not query.funnelsFilter.funnelVizType
or query.funnelsFilter.funnelVizType == FunnelVizType.STEPS
):
return FUNNEL_STEPS_EXAMPLE_PROMPT
if query.funnelsFilter.funnelVizType == FunnelVizType.TIME_TO_CONVERT:
return FUNNEL_TIME_TO_CONVERT_EXAMPLE_PROMPT
return FUNNEL_TRENDS_EXAMPLE_PROMPT
if isinstance(query, AssistantLifecycleQuery | LifecycleQuery):
return LIFECYCLE_EXAMPLE_PROMPT
if isinstance(query, AssistantPathsQuery | PathsQuery):
return PATHS_EXAMPLE_PROMPT
if isinstance(query, AssistantStickinessQuery | StickinessQuery):
return STICKINESS_EXAMPLE_PROMPT
if isinstance(query, AssistantRetentionQuery | RetentionQuery):
return RETENTION_EXAMPLE_PROMPT
if isinstance(query, AssistantHogQLQuery | HogQLQuery | DataVisualizationNode):
return SQL_EXAMPLE_PROMPT
raise NotImplementedError(f"Unsupported query type: {type(query)}")
async def execute_and_format_query(
team: Team,
query_model: AnyPydanticModelQuery | AnyAssistantGeneratedQuery,
*,
user: "User",
execution_mode: Optional[ExecutionMode] = None,
insight_id: Optional[int] = None,
truncate_results: bool = True,
include_prompt_framing: bool = True,
event_source: EventSource = EventSource.POSTHOG_AI,
max_sql_result_chars: int | None = None,
) -> str:
"""
Executes a supported query and formats the results for the AI assistant:
```
Format description...
Query results...
```
Args:
team: The team to execute the query on.
query: The query to execute.
execution_mode: The execution mode to use.
insight_id: The insight ID to use.
include_prompt_framing: When False, return only the formatted results table without the
example-format description and the surrounding `QUERY_RESULTS_PROMPT` system reminder.
Used by the MCP `execute_sql` tool, which returns data straight to an external agent.
Returns:
The formatted query results.
"""
query = validate_assistant_query(query_model.model_dump(mode="json"))
utc_now_datetime = timezone.now().astimezone(UTC)
query_runner = AssistantQueryExecutor(
team, utc_now_datetime, user=user, event_source=event_source, max_sql_result_chars=max_sql_result_chars
)
results, used_fallback = await query_runner.arun_and_format_query(
query, execution_mode, insight_id, truncate_results=truncate_results
)
if not include_prompt_framing:
return results
example_prompt = FALLBACK_EXAMPLE_PROMPT if used_fallback else get_example_prompt(query)
insight_schema = ""
if not isinstance(query, AssistantHogQLQuery | HogQLQuery):
insight_schema = query.model_dump_json(exclude_none=True)
# Check if SQL results contain truncated values
has_truncated_values = isinstance(query, AssistantHogQLQuery | HogQLQuery | DataVisualizationNode) and (
TRUNCATED_MARKER in results and not used_fallback
)
# Check if SQL results contain null values
has_null_values = isinstance(query, AssistantHogQLQuery | HogQLQuery | DataVisualizationNode) and (
NULL_MARKER in results and not used_fallback
)
query_result = format_prompt_string(
QUERY_RESULTS_PROMPT,
query_kind=query.kind,
results=results,
insight_schema=insight_schema,
utc_datetime_display=utc_now_datetime.strftime("%Y-%m-%d %H:%M:%S"),
project_datetime_display=utc_now_datetime.astimezone(team.timezone_info).strftime("%Y-%m-%d %H:%M:%S"),
project_timezone=team.timezone_info.tzname(utc_now_datetime),
has_truncated_values=has_truncated_values,
has_null_values=has_null_values,
sql_query=True if isinstance(query, AssistantHogQLQuery | HogQLQuery | DataVisualizationNode) else None,
)
return f"{example_prompt}\n\n{query_result}"