: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
from typing import Self
from uuid import uuid4
from langchain_core.runnables import RunnableConfig
from pydantic import BaseModel, Field
from posthog.schema import (
ArtifactContentType,
ArtifactSource,
AssistantDataVisualizationChartSettings,
AssistantDataVisualizationDisplayType,
AssistantToolCallMessage,
ChartDisplayType,
ChartSettings,
DataVisualizationNode,
HogQLFilters,
VisualizationArtifactContent,
)
from posthog.models import Team, User
from ee.hogai.chat_agent.schema_generator.parsers import PydanticOutputParserException
from ee.hogai.chat_agent.sql.mixins import HogQLGeneratorMixin
from ee.hogai.chat_agent.sql.prompts import (
SQL_EXPRESSIONS_DOCS,
SQL_SUPPORTED_AGGREGATIONS_DOCS,
SQL_SUPPORTED_FUNCTIONS_DOCS,
)
from ee.hogai.context import AssistantContextManager
from ee.hogai.context.insight.context import InsightContext
from ee.hogai.context.insight.format.sql import SQLResultsFormatter
from ee.hogai.tool import MaxTool, ToolMessagesArtifact
from ee.hogai.tool_errors import MaxToolError, MaxToolRetryableError
from ee.hogai.utils.prompt import format_prompt_string
from ee.hogai.utils.types import AssistantState
from ee.hogai.utils.types.base import NodePath
from .prompts import (
EXECUTE_SQL_CONTEXT_PROMPT,
EXECUTE_SQL_RECOVERABLE_ERROR_PROMPT,
EXECUTE_SQL_SYSTEM_PROMPT,
EXECUTE_SQL_UNRECOVERABLE_ERROR_PROMPT,
)
class ExecuteSQLToolArgs(BaseModel):
query: str = Field(description="The final SQL query to be executed.")
filters: HogQLFilters | None = Field(
default=None,
description=(
"Optional filters applied through `{filters}` or column-bound `{filters(...)}` placeholders in the query. "
"Use this when editing a SQL editor query that already uses such a placeholder and the user asks to change "
"dateRange, property filters, or test-account filtering. Set this to an empty object to clear existing "
"SQL editor filters while preserving the placeholder."
),
)
viz_title: str = Field(
description="Short, concise name of the SQL query (2-5 words) that will be displayed as a header in the visualization."
)
viz_description: str = Field(
description="Short, concise summary of the SQL query (1 sentence) that will be displayed as a description in the visualization."
)
display: AssistantDataVisualizationDisplayType | None = Field(
default=None,
description="Optional visualization type for the SQL result, such as ActionsBar or ActionsLineGraph. Use this when the user asks for a chart.",
)
chart_settings: AssistantDataVisualizationChartSettings | None = Field(
default=None,
description="Optional chart settings for the SQL result, including X-axis label, left Y-axis label, right Y-axis label, and which Y series uses the right axis.",
)
class ExecuteSQLTool(HogQLGeneratorMixin, MaxTool):
name: str = "execute_sql"
args_schema: type[BaseModel] = ExecuteSQLToolArgs
context_prompt_template: str = EXECUTE_SQL_CONTEXT_PROMPT
@classmethod
async def create_tool_class(
cls,
*,
team: Team,
user: User,
node_path: tuple[NodePath, ...] | None = None,
state: AssistantState | None = None,
config: RunnableConfig | None = None,
context_manager: AssistantContextManager | None = None,
) -> Self:
prompt = format_prompt_string(
EXECUTE_SQL_SYSTEM_PROMPT,
sql_expressions_docs=SQL_EXPRESSIONS_DOCS,
sql_supported_functions_docs=SQL_SUPPORTED_FUNCTIONS_DOCS,
sql_supported_aggregations_docs=SQL_SUPPORTED_AGGREGATIONS_DOCS,
)
return cls(team=team, user=user, state=state, node_path=node_path, config=config, description=prompt)
async def _arun_impl(
self,
query: str,
viz_title: str,
viz_description: str,
filters: HogQLFilters | None = None,
display: AssistantDataVisualizationDisplayType | None = None,
chart_settings: AssistantDataVisualizationChartSettings | dict[str, object] | None = None,
) -> tuple[str, ToolMessagesArtifact | None]:
connection_id = self.context.get("connection_id") or None
parsed_query = self._parse_output({"query": query})
if connection_id:
# An external connection's tables aren't in the ClickHouse catalog, so local HogQL
# validation would reject them. Tag the query with the connection and defer validation
# to the runner, which resolves the connection's schema.
parsed_query.query.source = parsed_query.query.source.model_copy(update={"connectionId": connection_id})
else:
try:
await self._quality_check_output(
output=parsed_query,
)
except PydanticOutputParserException as e:
return format_prompt_string(EXECUTE_SQL_RECOVERABLE_ERROR_PROMPT, error=str(e)), None
source_query = (
parsed_query.query.source.model_copy(update={"filters": filters})
if filters is not None
else parsed_query.query.source
)
variables = await self._abuild_query_variables(source_query.query)
if variables:
source_query = source_query.model_copy(update={"variables": variables})
artifact_query = parsed_query.query.model_copy(update={"source": source_query})
if display or chart_settings:
if isinstance(chart_settings, AssistantDataVisualizationChartSettings):
chart_settings_data = chart_settings.model_dump(mode="json", exclude_none=True)
elif chart_settings:
chart_settings_data = AssistantDataVisualizationChartSettings.model_validate(chart_settings).model_dump(
mode="json", exclude_none=True
)
else:
chart_settings_data = None
artifact_query = DataVisualizationNode(
source=source_query,
display=ChartDisplayType(display) if display else None,
chartSettings=ChartSettings.model_validate(chart_settings_data) if chart_settings_data else None,
)
# Display an ephemeral visualization message to the user.
artifact = await self._context_manager.artifacts.acreate(
VisualizationArtifactContent(query=artifact_query, name=viz_title, description=viz_description),
"SQL Query",
)
artifact_message = self._context_manager.artifacts.create_message(
artifact_id=artifact.short_id,
source=ArtifactSource.ARTIFACT,
content_type=ArtifactContentType.VISUALIZATION,
)
insight_context = InsightContext(
max_sql_result_chars=SQLResultsFormatter.MAX_RESULT_CHARS,
team=self._team,
query=artifact_query,
name=viz_title,
description=viz_description,
insight_id=artifact_message.artifact_id,
user=self._user,
)
try:
result = await insight_context.execute_and_format()
except MaxToolRetryableError as e:
return format_prompt_string(EXECUTE_SQL_RECOVERABLE_ERROR_PROMPT, error=str(e)), None
except MaxToolError as e:
return f"{e.to_summary()}.{e.retry_hint}", None
except Exception:
return EXECUTE_SQL_UNRECOVERABLE_ERROR_PROMPT, None
tool_payload: str | dict[str, object]
if display or chart_settings:
# Full node so the SQL editor can adopt the visualization settings, not just the SQL
tool_payload = artifact_query.model_dump(mode="json", exclude_none=True)
elif filters is not None:
tool_payload = source_query.model_dump(mode="json", exclude_none=True)
else:
tool_payload = artifact_query.source.query
return "", ToolMessagesArtifact(
messages=[
artifact_message,
AssistantToolCallMessage(
content=result,
id=str(uuid4()),
tool_call_id=self.tool_call_id,
ui_payload={self.get_name(): tool_payload},
),
]
)