Skip to content

: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.

13.1 KB
Raw Blame
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)