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.

8.8 KB
Raw Blame
import asyncio
from typing import cast

from langchain_core.prompts import ChatPromptTemplate
from pydantic import BaseModel, Field

from posthog.schema import (
    AssistantHogQLQuery,
    ChartAxis,
    ChartDisplayType,
    ChartSettings,
    ChartSettingsFormatting,
    DataVisualizationNode,
    HogQLQuery,
    HogQLVariable,
    Settings,
    Style,
)

from posthog.hogql import ast
from posthog.hogql.context import HogQLContext
from posthog.hogql.database.database import Database
from posthog.hogql.errors import (
    ExposedHogQLError,
    NotImplementedError as HogQLNotImplementedError,
    QueryError,
    ResolutionError,
)
from posthog.hogql.filters import replace_filters
from posthog.hogql.parser import parse_select
from posthog.hogql.placeholders import find_placeholders
from posthog.hogql.printer import prepare_and_print_ast
from posthog.hogql.variables import replace_variables

from posthog.models import Team
from posthog.models.user import User
from posthog.sync import database_sync_to_async

from products.product_analytics.backend.facade.api import insight_variables_by_code_names

from ee.hogai.chat_agent.schema_generator.utils import SchemaGeneratorOutput
from ee.hogai.core.mixins import AssistantContextMixin
from ee.hogai.utils.warehouse import serialize_database_schema

from ..schema_generator.parsers import PydanticOutputParserException, parse_pydantic_structured_output
from .errors import hogql_validation_message
from .prompts import (
    HOGQL_GENERATOR_SYSTEM_PROMPT,
    SQL_EXPRESSIONS_DOCS,
    SQL_SUPPORTED_AGGREGATIONS_DOCS,
    SQL_SUPPORTED_FUNCTIONS_DOCS,
)


class RawSQLSchemaGeneratorOutput(BaseModel):
    query: str
    display: ChartDisplayType | None = None
    x_axis: str | None = None
    y_axis: list[str] = Field(default_factory=list)
    series_breakdown_column: str | None = None
    y_axis_format: Style | None = None
    y_axis_decimal_places: int | None = None
    y_axis_prefix: str | None = None
    y_axis_suffix: str | None = None
    show_legend: bool | None = None


SQLSchemaGeneratorOutput = SchemaGeneratorOutput[DataVisualizationNode]


class HogQLDatabaseMixin:
    _team: Team
    _user: User
    _database_instance: Database | None = None

    def _get_database(self):
        if self._database_instance:
            return self._database_instance
        self._database_instance = Database.create_for(team=self._team, user=self._user)
        return self._database_instance

    @database_sync_to_async
    def _aget_database(self):
        return self._get_database()

    def _get_default_hogql_context(self, database: Database):
        hogql_context = HogQLContext(team=self._team, user=self._user, database=database, enable_select_queries=True)
        return hogql_context

    async def _serialize_database_schema(self):
        database = await self._aget_database()
        return await serialize_database_schema(database, self._get_default_hogql_context(database))


class HogQLOutputParserMixin(HogQLDatabaseMixin):
    def _parse_output(self, output: dict) -> SQLSchemaGeneratorOutput:
        result = parse_pydantic_structured_output(RawSQLSchemaGeneratorOutput)(output)
        cleaned_query = result.query.rstrip(";").strip() if result.query else ""

        formatting = self._build_axis_formatting(result)
        y_axis = [
            ChartAxis(
                column=column,
                settings=Settings(formatting=formatting) if formatting else None,
            )
            for column in result.y_axis
        ]

        chart_settings = None
        if result.display not in (None, ChartDisplayType.ACTIONS_TABLE, ChartDisplayType.BOLD_NUMBER):
            chart_settings = ChartSettings(
                xAxis=ChartAxis(column=result.x_axis) if result.x_axis else None,
                yAxis=y_axis or None,
                seriesBreakdownColumn=result.series_breakdown_column,
                showLegend=result.show_legend,
            )

        return SQLSchemaGeneratorOutput(
            query=DataVisualizationNode(
                source=HogQLQuery(query=cleaned_query),
                display=result.display,
                chartSettings=chart_settings,
            ),
        )

    def _build_axis_formatting(self, result: RawSQLSchemaGeneratorOutput) -> ChartSettingsFormatting | None:
        has_formatting = (
            result.y_axis_format not in (None, Style.NONE)
            or result.y_axis_decimal_places is not None
            or bool(result.y_axis_prefix)
            or bool(result.y_axis_suffix)
        )
        if not has_formatting:
            return None
        return ChartSettingsFormatting(
            style=result.y_axis_format,
            decimalPlaces=result.y_axis_decimal_places,
            prefix=result.y_axis_prefix,
            suffix=result.y_axis_suffix,
        )

    def _get_insight_variables(self, placeholder_fields: list[list[str | int]]) -> list[HogQLVariable]:
        code_names = {str(chain[1]) for chain in placeholder_fields if len(chain) >= 2 and chain[0] == "variables"}
        if not code_names:
            return []
        insight_variables = insight_variables_by_code_names(self._team.pk, code_names)
        return [
            HogQLVariable(variableId=str(variable.id), code_name=variable.code_name)
            for variable in insight_variables
            if variable.code_name
        ]

    def _build_query_variables(self, query: str) -> dict[str, HogQLVariable]:
        parsed_query = parse_select(query, placeholders={})
        finder = find_placeholders(parsed_query)
        return {variable.variableId: variable for variable in self._get_insight_variables(finder.placeholder_fields)}

    @database_sync_to_async
    def _abuild_query_variables(self, query: str) -> dict[str, HogQLVariable]:
        return self._build_query_variables(query)

    def _validate_hogql_query_sync(self, query: str) -> AssistantHogQLQuery:
        """
        Validate a HogQL query string and return AssistantHogQLQuery.

        This is the core validation logic used by both internal and external tools.
        """
        cleaned_query = query.rstrip(";").strip() if query else ""
        if not cleaned_query:
            raise PydanticOutputParserException(llm_output="", validation_message="Query is empty")

        database = self._get_database()
        hogql_context = self._get_default_hogql_context(database)

        try:
            parsed_query = parse_select(cleaned_query, placeholders={})

            finder = find_placeholders(parsed_query)

            # Handle filter placeholders using the proper filter replacement system.
            # Passing None for filters resolves all recognized filter placeholders to True (no-op).
            if finder.has_filters:
                parsed_query = cast(ast.SelectQuery, replace_filters(parsed_query, None, self._team))

            if any(field and field[0] == "variables" for field in finder.placeholder_fields):
                variables = self._get_insight_variables(finder.placeholder_fields)
                parsed_query = cast(ast.SelectQuery, replace_variables(parsed_query, variables, self._team))

            prepare_and_print_ast(parsed_query, context=hogql_context, dialect="clickhouse")
        except (ExposedHogQLError, HogQLNotImplementedError, QueryError, ResolutionError) as err:
            raise PydanticOutputParserException(
                llm_output=cleaned_query, validation_message=hogql_validation_message(err, cleaned_query)
            ) from err

        return AssistantHogQLQuery(query=cleaned_query)

    @database_sync_to_async(thread_sensitive=False)
    def _validate_hogql_query(self, query: str) -> AssistantHogQLQuery:
        """Async wrapper for _validate_hogql_query_sync."""
        return self._validate_hogql_query_sync(query)

    @database_sync_to_async(thread_sensitive=False)
    def _quality_check_output(self, output: SQLSchemaGeneratorOutput):
        query = output.query.source.query if output.query else None
        if not query:
            raise PydanticOutputParserException(llm_output="", validation_message="Output is empty")
        self._validate_hogql_query_sync(query)


class HogQLGeneratorMixin(HogQLOutputParserMixin, AssistantContextMixin):
    async def _construct_system_prompt(self) -> ChatPromptTemplate:
        schema_description, core_memory = await asyncio.gather(
            self._serialize_database_schema(),
            self._aget_core_memory_text(),
        )

        prompt = ChatPromptTemplate.from_messages(
            [
                ("system", HOGQL_GENERATOR_SYSTEM_PROMPT),
            ],
            template_format="mustache",
        ).partial(
            sql_expressions_docs=SQL_EXPRESSIONS_DOCS,
            sql_supported_functions_docs=SQL_SUPPORTED_FUNCTIONS_DOCS,
            sql_supported_aggregations_docs=SQL_SUPPORTED_AGGREGATIONS_DOCS,
            schema_description=schema_description,
            core_memory=core_memory,
        )

        return prompt