: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 posthog.test.base import ClickhouseTestMixin, NonAtomicBaseTest, _create_event, flush_persons_and_events
from unittest.mock import AsyncMock, MagicMock, Mock, patch
from django.test import SimpleTestCase
from asgiref.sync import sync_to_async
from langchain_core.runnables import RunnableConfig
from parameterized import parameterized
from rest_framework.exceptions import PermissionDenied
from posthog.schema import (
ArtifactContentType,
ArtifactSource,
AssistantToolCallMessage,
ChartDisplayType,
DataVisualizationNode,
DateRange,
HogQLFilters,
VisualizationArtifactContent,
)
from posthog.exceptions import ClickHouseAtCapacity
from posthog.models import Organization, Team, User
from products.posthog_ai.backend.models.assistant import AgentArtifact, Conversation
from products.product_analytics.backend.facade.models import Insight
from ee.hogai.context.context import AssistantContextManager
from ee.hogai.context.insight.format.sql import SQLResultsFormatter
from ee.hogai.tools.execute_sql.tool import ExecuteSQLTool, ExecuteSQLToolArgs
from ee.hogai.utils.types import AssistantState
from ee.hogai.utils.types.base import ArtifactRefMessage, NodePath
class TestExecuteSQLPreview(SimpleTestCase):
async def test_native_tool_bounds_results_and_preserves_artifact_query(self) -> None:
context = MagicMock(spec=AssistantContextManager)
context.get_contextual_tools.return_value = {"execute_sql": {"connection_id": "example-connection"}}
context.artifacts.acreate = AsyncMock(return_value=AgentArtifact(short_id="preview1"))
context.artifacts.create_message.return_value = ArtifactRefMessage(
id="artifact",
artifact_id="preview1",
content_type=ArtifactContentType.VISUALIZATION,
source=ArtifactSource.ARTIFACT,
)
tool = ExecuteSQLTool(
team=Team(id=1, organization=Organization()),
user=User(),
context_manager=context,
description="Execute an example SQL query",
node_path=(NodePath(name="test", tool_call_id="sql"),),
)
query = "SELECT text FROM example_table"
response = {"columns": ["text"], "results": [["example text " * 200] for _ in range(300)]}
with patch("ee.hogai.context.insight.query_executor.process_query_dict", return_value=response):
_, artifact = await tool._arun_impl(query, "Example query", "Example description")
assert artifact is not None
message = artifact.messages[-1]
assert isinstance(message, AssistantToolCallMessage)
self.assertLess(len(message.content), SQLResultsFormatter.MAX_RESULT_CHARS + 2000)
self.assertIn("SQL result preview", message.content)
self.assertIn("Some cells were shortened", message.content)
self.assertEqual(message.ui_payload, {"execute_sql": query})
self.assertEqual(context.artifacts.acreate.call_args.args[0].query.source.query, query)
artifact_ref = artifact.messages[0]
assert isinstance(artifact_ref, ArtifactRefMessage)
self.assertEqual(artifact_ref.artifact_id, "preview1")
class TestExecuteSQLTool(ClickhouseTestMixin, NonAtomicBaseTest):
CLASS_DATA_LEVEL_SETUP = False
def setUp(self):
super().setUp()
self.tool_call_id = "test_tool_call_id"
self.conversation = Conversation.objects.create(user=self.user, team=self.team)
async def _create_tool(self, state: AssistantState | None = None):
if state is None:
state = AssistantState(messages=[])
config: RunnableConfig = RunnableConfig(configurable={"thread_id": str(self.conversation.id)})
context_manager = AssistantContextManager(team=self.team, user=self.user, config=config)
with patch.object(context_manager, "get_group_names", AsyncMock(return_value=["organization", "project"])):
tool = await ExecuteSQLTool.create_tool_class(
team=self.team,
user=self.user,
state=state,
config=config,
context_manager=context_manager,
node_path=(NodePath(name="test_node", tool_call_id=self.tool_call_id, message_id="test"),),
)
return tool
async def test_successful_sql_execution_returns_messages(self):
_create_event(team=self.team, distinct_id="user1", event="test_event")
_create_event(team=self.team, distinct_id="user2", event="test_event")
_create_event(team=self.team, distinct_id="user3", event="another_event")
await sync_to_async(flush_persons_and_events)()
tool = await self._create_tool()
result_text, artifact_messages = await tool._arun_impl(
"SELECT event, count() as count FROM events GROUP BY event ORDER BY count DESC",
"Event counts",
"Count events by type",
)
self.assertEqual(result_text, "")
self.assertIsNotNone(artifact_messages)
self.assertEqual(len(artifact_messages.messages), 2)
self.assertEqual(artifact_messages.messages[0].content_type, ArtifactContentType.VISUALIZATION)
self.assertIsInstance(artifact_messages.messages[1], AssistantToolCallMessage)
self.assertIn("test_event", artifact_messages.messages[1].content)
self.assertIn("another_event", artifact_messages.messages[1].content)
@parameterized.expand(
[
(
ClickHouseAtCapacity("Query service is busy"),
"MaxToolTransientError: Query service is busy. You may retry this operation once without changes.",
),
(PermissionDenied("Query access denied"), "MaxToolFatalError: Query access denied."),
]
)
@patch("ee.hogai.context.insight.query_executor.process_query_dict")
async def test_query_failures_preserve_recovery_advice(
self, error: Exception, expected: str, mock_query: Mock
) -> None:
mock_query.side_effect = error
tool = await self._create_tool()
self.assertEqual(await tool._arun_impl("SELECT 1", "Example query", "Example description"), (expected, None))
async def test_successful_sql_execution_can_set_chart_axis_labels(self):
_create_event(team=self.team, distinct_id="user1", event="test_event")
_create_event(team=self.team, distinct_id="user2", event="test_event")
_create_event(team=self.team, distinct_id="user3", event="another_event")
await sync_to_async(flush_persons_and_events)()
tool = await self._create_tool()
tool_args = ExecuteSQLToolArgs.model_validate(
{
"query": "SELECT event, count() AS events, uniq(distinct_id) AS people FROM events GROUP BY event",
"viz_title": "Event counts",
"viz_description": "Count events and people by event type",
"display": "ActionsBar",
"chart_settings": {
"xAxis": {"column": "event"},
"yAxis": [
{"column": "events"},
{"column": "people", "settings": {"display": {"yAxisPosition": "right"}}},
],
"xAxisLabel": "Event name",
"leftYAxisSettings": {"label": "Events"},
"rightYAxisSettings": {"label": "People", "showTicks": False},
},
}
)
result_text, artifact_messages = await tool._arun_impl(**tool_args.model_dump())
self.assertEqual(result_text, "")
self.assertIsNotNone(artifact_messages)
artifact = await AgentArtifact.objects.aget(short_id=artifact_messages.messages[0].artifact_id)
content = VisualizationArtifactContent.model_validate(artifact.data)
query = content.query
assert isinstance(query, DataVisualizationNode)
chart_settings = query.chartSettings
assert chart_settings is not None
left_y_axis_settings = chart_settings.leftYAxisSettings
assert left_y_axis_settings is not None
right_y_axis_settings = chart_settings.rightYAxisSettings
assert right_y_axis_settings is not None
self.assertEqual(query.display, ChartDisplayType.ACTIONS_BAR)
self.assertEqual(chart_settings.xAxisLabel, "Event name")
self.assertEqual(left_y_axis_settings.label, "Events")
self.assertEqual(right_y_axis_settings.label, "People")
self.assertEqual(right_y_axis_settings.showTicks, False)
tool_call_message = artifact_messages.messages[1]
assert isinstance(tool_call_message, AssistantToolCallMessage)
ui_payload = tool_call_message.ui_payload
assert ui_payload is not None
payload = ui_payload["execute_sql"]
self.assertEqual(payload["kind"], "DataVisualizationNode")
self.assertEqual(payload["display"], "ActionsBar")
self.assertEqual(payload["chartSettings"]["xAxisLabel"], "Event name")
self.assertEqual(
payload["source"]["query"],
"SELECT event, count() AS events, uniq(distinct_id) AS people FROM events GROUP BY event",
)
async def test_artifact_id_in_output(self):
_create_event(team=self.team, distinct_id="user1", event="test_event")
tool = await self._create_tool()
result_text, artifact_messages = await tool._arun_impl(
"SELECT event, count() as count FROM events GROUP BY event",
"Test query",
"Test description",
)
self.assertEqual(result_text, "")
self.assertIsNotNone(artifact_messages)
self.assertEqual(len(artifact_messages.messages), 2)
# Get artifact_id from the first message
artifact_id = artifact_messages.messages[0].artifact_id
self.assertIsNotNone(artifact_id)
# Verify artifact_id is included in the second message content
tool_call_content = artifact_messages.messages[1].content
self.assertIn(artifact_id, tool_call_content)
async def test_sql_execution_preserves_filters_in_hogql_query(self) -> None:
_create_event(team=self.team, distinct_id="user1", event="test_event")
tool = await self._create_tool()
filters = HogQLFilters(dateRange=DateRange(date_from="-90d"))
result_text, artifact_messages = await tool._arun_impl(
"SELECT count() FROM events WHERE {filters}",
"Recent events",
"Events matching the editor filters",
filters=filters,
)
self.assertEqual(result_text, "")
self.assertIsNotNone(artifact_messages)
artifact_id = artifact_messages.messages[0].artifact_id
self.assertIsNotNone(artifact_id)
artifact = await AgentArtifact.objects.aget(short_id=artifact_id, team=self.team)
content = VisualizationArtifactContent.model_validate(artifact.data)
self.assertIsInstance(content.query, DataVisualizationNode)
assert isinstance(content.query, DataVisualizationNode)
self.assertEqual(content.query.source.filters, filters)
tool_call_message = artifact_messages.messages[1]
self.assertIsInstance(tool_call_message, AssistantToolCallMessage)
assert isinstance(tool_call_message, AssistantToolCallMessage)
ui_payload = tool_call_message.ui_payload
assert ui_payload is not None
payload = ui_payload["execute_sql"]
self.assertEqual(payload["query"], "SELECT count() FROM events WHERE {filters}")
self.assertEqual(payload["filters"]["dateRange"]["date_from"], "-90d")
async def test_sql_execution_returns_empty_filters_payload(self) -> None:
_create_event(team=self.team, distinct_id="user1", event="test_event")
tool = await self._create_tool()
result_text, artifact_messages = await tool._arun_impl(
"SELECT count() FROM events WHERE {filters}",
"Recent events",
"Events matching the editor filters",
filters=HogQLFilters(),
)
self.assertEqual(result_text, "")
self.assertIsNotNone(artifact_messages)
tool_call_message = artifact_messages.messages[1]
self.assertIsInstance(tool_call_message, AssistantToolCallMessage)
assert isinstance(tool_call_message, AssistantToolCallMessage)
ui_payload = tool_call_message.ui_payload
assert ui_payload is not None
payload = ui_payload["execute_sql"]
self.assertEqual(payload["query"], "SELECT count() FROM events WHERE {filters}")
self.assertEqual(payload["filters"], {})
async def test_select_from_system_insights(self):
await sync_to_async(Insight.objects.create)(
team=self.team,
name="Revenue Trends",
query={"kind": "TrendsQuery", "series": [{"event": "$pageview", "kind": "EventsNode"}]},
)
tool = await self._create_tool()
result_text, artifact_messages = await tool._arun_impl(
"SELECT id, name FROM system.insights",
"System insights",
"List all insights",
)
self.assertEqual(result_text, "")
self.assertIsNotNone(artifact_messages)
self.assertIn("Revenue Trends", artifact_messages.messages[1].content)