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.0 KB
Raw Blame
import type { z } from 'zod'

import { wrapError } from '@/lib/errors'
import { withUiApp } from '@/resources/ui-apps'
import { ExplainChartInputSchema } from '@/schema/tool-inputs'
import { GENERATED_TOOLS } from '@/tools/generated/query-wrappers'
import { applyProjectTestAccountDefault } from '@/tools/query-wrapper-factory'
import { type Context, POSTHOG_FORMATTED_RESULTS_OVERRIDE_KEY, type ToolBase } from '@/tools/types'

import type {
    ExplainChartData,
    StoryChapter,
    StoryEvidenceChartData,
    StorySeriesResult,
    StoryTrendsQuery,
} from 'products/product_analytics/mcp/apps/explainChart/explainChartTypes'
import { sqlEvidenceChart } from 'products/product_analytics/mcp/apps/explainChart/sqlEvidenceChart'
import { insightPageShowsStory } from 'products/product_analytics/mcp/apps/explainChart/storyDisplays'
import { storyEvidenceNode } from 'products/product_analytics/mcp/apps/explainChart/storyEvidenceNode'

import { analyzeQuery } from '../shared'
import { findStoryRange, lastBucketInProgress } from './storyRanges'

const schema = ExplainChartInputSchema

export type Params = z.infer<typeof schema>

type Result = ExplainChartData & { [POSTHOG_FORMATTED_RESULTS_OVERRIDE_KEY]: string }

const RENDERED_NOTE =
    'The person sees the chart explanation rendered inline: the chart, every chapter, each cause and each piece of evidence. Do not write any of that again in any form, not as a recap, a bullet list or a summary of the moments. Keep your reply short: which chapter to start with, only what the chart explanation cannot hold (a caveat about the data, a linked fix, a decision that is still open), and one next step you offer. If the person says the explanation did not load, give the findings in text.'

// The markers sit on one value over time. These displays draw that as they are.
const TIME_SERIES_DISPLAYS = new Set([
    'ActionsLineGraph',
    'ActionsLineGraphCumulative',
    'ActionsBar',
    'ActionsUnstackedBar',
    'ActionsAreaGraph',
    'ActionsStackedBar',
])

// These show a total over the range, so the explanation draws the line behind it. Any other display is refused.
const TOTAL_DISPLAYS = new Set([
    'Auto',
    'BoldNumber',
    'Metric',
    'ActionsPie',
    'ActionsDonut',
    'ActionsProportionBar',
    'ActionsBarValue',
    'ActionsTable',
    'WorldMap',
    'CalendarHeatmap',
    'SlopeGraph',
])

const NOT_TRENDS = 'This tool only explains trends charts. For other kinds, write the explanation in the chat instead.'
const NOT_OVER_TIME =
    'This tool explains a trends chart that shows one value over time, and this chart does not. Write the explanation in the chat instead.'

// The explanation redraws insight evidence through the query tool for its kind. A tool's schema is narrower than the
// query's, so evidence the tool would refuse keeps its link and no chart.
const REDRAW_TOOLS = new Map(
    (
        [
            ['TrendsQuery', 'query-trends'],
            ['FunnelsQuery', 'query-funnel'],
            ['RetentionQuery', 'query-retention'],
            ['StickinessQuery', 'query-stickiness'],
            ['PathsQuery', 'query-paths'],
            ['LifecycleQuery', 'query-lifecycle'],
        ] as const
    ).map(([kind, tool]) => [kind as string, { tool, schema: GENERATED_TOOLS[tool]!().schema }])
)

function redrawToolFor(query: Record<string, unknown> | undefined): string | null {
    // A trends query may leave out `kind`, which the trends tool fills in.
    const kind = query?.kind ?? 'TrendsQuery'
    const redraw = query && typeof kind === 'string' ? REDRAW_TOOLS.get(kind) : undefined
    return redraw && redraw.schema.safeParse(query).success ? redraw.tool : null
}

type SqlEvidence = { chart: StoryEvidenceChartData | null } | { error: string }

const SQL_EVIDENCE_TIME_LIMIT_MS = 60_000

function isSqlQuery(query: Record<string, unknown> | undefined): query is Record<string, unknown> {
    return query?.kind === 'HogQLQuery' && typeof query.query === 'string'
}

async function runSqlEvidence(
    context: Context,
    projectId: string,
    chapters: Params['chapters']
): Promise<Map<string, SqlEvidence>> {
    const queries = new Map<string, Record<string, unknown>>()
    for (const item of chapters.flatMap((chapter) => chapter.evidence ?? [])) {
        if (isSqlQuery(item.query)) {
            queries.set(JSON.stringify(item.query), item.query)
        }
    }
    // One at a time, so the evidence does not use up the team's concurrent query limit, within one shared time limit.
    const signal = AbortSignal.timeout(SQL_EVIDENCE_TIME_LIMIT_MS)
    const results = new Map<string, SqlEvidence>()
    for (const [key, query] of queries) {
        if (signal.aborted) {
            results.set(key, { error: 'it did not finish in time' })
            continue
        }
        try {
            const response = await context.api.query({ projectId }).runQuery({ query, signal })
            const columns = (response.columns ?? []).map(String)
            const rows = Array.isArray(response.results) ? (response.results as unknown[][]) : []
            results.set(key, { chart: sqlEvidenceChart(columns, rows) })
        } catch (error) {
            results.set(key, { error: error instanceof Error ? error.message : String(error) })
        }
    }
    return results
}

function sqlProblems(chapters: Params['chapters'], sqlEvidence: Map<string, SqlEvidence>): string[] {
    return chapters.flatMap((chapter) =>
        (chapter.evidence ?? []).flatMap((item) => {
            const result = isSqlQuery(item.query) ? sqlEvidence.get(JSON.stringify(item.query)) : undefined
            if (!result) {
                return []
            }
            if ('error' in result) {
                return [`The SQL behind "${item.text}" failed, so it shows as a link only: ${result.error}`]
            }
            return result.chart
                ? []
                : [
                      `The SQL behind "${item.text}" returned nothing to draw, so it shows as a link only. Select the x-axis column first, then either the numbers to draw, or one text column to break down by followed by one number.`,
                  ]
        })
    )
}

export interface StoryChart {
    query: StoryTrendsQuery
    linkableInsightId: number | null
    insightName: string | null
    path: string
}

function asTrendsQuery(query: unknown): StoryTrendsQuery {
    const inner = analyzeQuery(query).innerQuery
    if (inner?.kind !== 'TrendsQuery') {
        throw new Error(NOT_TRENDS)
    }
    const trends = { ...inner } as StoryTrendsQuery
    const display = trends.trendsFilter?.display
    if (typeof display !== 'string' || TIME_SERIES_DISPLAYS.has(display)) {
        return trends
    }
    if (!TOTAL_DISPLAYS.has(display)) {
        throw new Error(NOT_OVER_TIME)
    }
    trends.trendsFilter = { ...trends.trendsFilter, display: 'ActionsLineGraph' }
    return trends
}

function newInsightPath(query: Record<string, unknown>): string {
    return `/insights/new#q=${encodeURIComponent(JSON.stringify(storyEvidenceNode(query)))}`
}

async function loadChart(context: Context, projectId: string, params: Params): Promise<StoryChart> {
    if (params.insightId === undefined) {
        const query = asTrendsQuery(params.query)
        await applyProjectTestAccountDefault(context, query)
        return { query, linkableInsightId: null, insightName: null, path: newInsightPath(query) }
    }
    const insight = await context.api.insights({ projectId }).get({ insightId: params.insightId })
    if (!insight.success) {
        throw wrapError(`Failed to get insight: ${insight.error.message}`, insight.error)
    }
    return {
        query: asTrendsQuery(insight.data.query),
        linkableInsightId: insightPageShowsStory(
            (analyzeQuery(insight.data.query).innerQuery as StoryTrendsQuery | undefined)?.trendsFilter?.display
        )
            ? insight.data.id
            : null,
        insightName: insight.data.name || insight.data.derived_name || null,
        path: `/insights/${insight.data.short_id}`,
    }
}

function warningMessages(warnings: unknown[]): string[] {
    return warnings.map((warning) =>
        typeof warning === 'string' ? warning : ((warning as { message?: string }).message ?? JSON.stringify(warning))
    )
}

function describeResult(built: BuiltExplanation, chapterCount: number): string {
    const { chart, series, missed, warnings, evidenceProblems } = built
    const name = chart.insightName ?? series.label ?? 'the chart'
    const lines = [`The explanation of "${name}" has ${chapterCount} chapters.`]
    if (chart.linkableInsightId !== null) {
        lines.push(
            'Where the person cannot see it rendered inline, call explain-chart-save with the same arguments for a link that opens it beside the chart in PostHog.'
        )
    }
    if (warnings.length) {
        lines.push(`The query came with warnings the person should hear about: ${warningMessages(warnings).join(' ')}`)
    }
    if (missed.length) {
        lines.push(
            `No point on the chart matches the dates of ${missed.map((title) => `"${title}"`).join(', ')}, so ${
                missed.length === 1 ? 'that chapter shows' : 'those chapters show'
            } without a highlight. Use dates from the chart's \`days\` and call this tool again.`
        )
    }
    lines.push(...evidenceProblems)
    return lines.join(' ')
}

export interface BuiltExplanation {
    projectId: string
    baseUrl: string
    chart: StoryChart
    series: StorySeriesResult
    data: ExplainChartData
    missed: string[]
    warnings: unknown[]
    evidenceProblems: string[]
}

export async function buildExplanation(context: Context, params: Params): Promise<BuiltExplanation> {
    const projectId = await context.stateManager.getProjectId()
    const chart = await loadChart(context, projectId, params)

    const response = await context.api.query({ projectId }).runQuery({ query: chart.query })
    const results = (Array.isArray(response.results) ? response.results : []) as StorySeriesResult[]
    const series = results[params.series]
    if (!series?.data?.length) {
        throw new Error(
            results.length
                ? `The chart has ${results.length} series, so \`series\` must be below ${results.length}.`
                : 'The chart has no data to explain.'
        )
    }

    const sqlEvidence = await runSqlEvidence(context, projectId, params.chapters)
    const baseUrl = context.api.getProjectBaseUrl(projectId)
    const interval = chart.query.interval ?? 'day'
    const { timezone, warnings = [] } = response as { timezone?: string; warnings?: unknown[] | null }
    const missed: string[] = []
    const chapters: StoryChapter[] = params.chapters.map((chapter) => {
        const range = findStoryRange(series.days ?? [], interval, chapter.start, chapter.end)
        if (!range) {
            missed.push(chapter.title)
        }
        return {
            title: chapter.title,
            range,
            summary: chapter.summary ?? null,
            cause: chapter.cause ?? null,
            confidence: chapter.confidence ?? null,
            confidenceReason: chapter.confidence_reason ?? null,
            evidence: (chapter.evidence ?? []).map((item) => {
                const queryTool = redrawToolFor(item.query)
                const sql = isSqlQuery(item.query) ? sqlEvidence.get(JSON.stringify(item.query)) : undefined
                return {
                    text: item.text,
                    url: item.query
                        ? `${baseUrl}${newInsightPath(item.query)}`
                        : item.insightShortId
                          ? `${baseUrl}/insights/${item.insightShortId}`
                          : (item.url ?? null),
                    query: item.query ?? null,
                    insightShortId: item.insightShortId ?? null,
                    queryTool,
                    chart: sql && 'chart' in sql ? sql.chart : null,
                }
            }),
        }
    })

    const data: ExplainChartData = {
        shape: 'time_series',
        query: chart.query,
        results,
        seriesIndex: params.series,
        insightName: chart.insightName,
        lastPointInProgress: lastBucketInProgress(series.days ?? [], interval, timezone),
        ...(warnings?.length ? { warnings } : {}),
        headline: params.headline,
        chapters,
    }
    return {
        projectId,
        baseUrl,
        chart,
        series,
        data,
        missed,
        warnings: warnings ?? [],
        evidenceProblems: sqlProblems(params.chapters, sqlEvidence),
    }
}

export const explainChartHandler: ToolBase<typeof schema, Result>['handler'] = async (
    context: Context,
    params: Params
) => {
    const built = await buildExplanation(context, params)
    return {
        ...built.data,
        _posthogUrl: `${built.baseUrl}${built.chart.path}`,
        [POSTHOG_FORMATTED_RESULTS_OVERRIDE_KEY]: describeResult(built, params.chapters.length),
    }
}

export default (): ToolBase<typeof schema, Result> =>
    withUiApp('explain-chart', {
        name: 'explain-chart',
        schema,
        renderedNote: RENDERED_NOTE,
        handler: explainChartHandler,
    })