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.

18.1 KB
Raw Blame
"""Cell-level state for markdown notebooks: the dependency graph an agent (or the UI)
needs to drive cells — which cells exist, what each depends on, and which are stale.

Staleness is derived, not stored: a cell is stale when re-running it now would execute
different code than its last completed run. For a SQL cell that last ran on ClickHouse the
comparison uses the variable-bound, CTE-resolved code (notebook variables and upstream
definitions inline into the stored run code, so a changed value or an upstream edit changes
the resolution). A SQL cell that last ran on the sandbox's DuckDB, and a Python cell, fall
back to code drift plus upstream run recency: both materialize inputs by run, and neither
run row records which input runs or variable values it used.
"""

import re
import json
from dataclasses import dataclass, field
from typing import Any

from posthog.hogql import ast
from posthog.hogql.parser import parse_select

from posthog.dataclasses import frozen

from products.notebooks.backend.facade.contracts import NotebookCellLimitExceeded
from products.notebooks.backend.models import NotebookNodeRun
from products.notebooks.backend.python_analysis import analyze_python_globals
from products.notebooks.backend.sql_v2_references import _TableReferenceCollector, resolve_sql_v2_references
from products.notebooks.backend.sql_v2_variables import (
    NotebookVariable,
    build_notebook_variables,
    substitute_duckdb_variables,
    substitute_hogql_variables,
)
from products.notebooks.backend.util import (
    _get_markdown_notebook_markdown,
    _iter_markdown_component_blocks,
    _parse_markdown_component_props,
    iter_markdown_blocks,
)

_CELL_TAGS = {"SQLV2": "sql", "PythonV2": "python", "Query": "saved_insight", "Insight": "saved_insight"}

# Prose sits outside the runnable-cell ceiling below, so this is the only bound on how many
# cells one save can turn into. The reader is an agent that pays context for every block it is
# handed, on top of the `markdown` this response already carries, so the budget is a response an
# agent can still afford rather than the largest one a worker survives. Past the cap a notebook
# stays readable through `markdown`; only its trailing prose loses an id to edit by.
MAX_ADDRESSABLE_PROSE_BLOCKS = 500

# The most cells one notebook may hold. A cell is the expensive unit: a SQL or Python cell is a
# ClickHouse query or a sandbox execution, and the MCP tools let an agent add one and run it in
# the same call, in a loop, with nothing between it and storage. Counted the way `extract_cells`
# counts, so this ceiling and what the API reports as a cell are the same number.
MAX_NOTEBOOK_CELLS = 50

__all__ = ["NotebookCellLimitExceeded"]
_DATAFRAME_NAME = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")
_CODE_PREVIEW_CHARS = 8_000


@frozen
class CellVisualization:
    display: str
    x_axis: str | None
    y_axis: tuple[str, ...] | None
    series_breakdown: str | None


# Built in stages: `build_dependency_edges` fills the graph edges, `annotate_run_state` the run
# status, and `_merge_prose_cells` the document span.
@dataclass(frozen=False)
class NotebookCellState:
    node_id: str
    cell_type: str
    dataframe_name: str = ""
    code: str = ""
    # The data source a SQL cell targets, and whether its code reaches that engine verbatim.
    # Both are cell attributes rather than run inputs, so anything that runs a cell on the
    # cell's own terms — not just the editor, which passes them per request — needs them.
    connection_id: str | None = None
    send_raw_query: bool = False
    status: str = "never_run"
    depends_on: list[str] = field(default_factory=list)
    dependents: list[str] = field(default_factory=list)
    last_run: dict[str, Any] | None = None
    start: int = 0
    end: int = 0
    visualization: CellVisualization | None = None


def _code_from_query_prop(value: Any) -> str:
    """The SQL a SQL cell carries in `query` instead of `code`, or "" when it carries none.

    Mirrors the editor's `getSqlV2PropsFromQueryProp`. A cell written before `code` existed,
    or converted from a v1 node, holds its SQL three ways: raw in the string prop, as a bare
    HogQLQuery, or wrapped in a data-table or visualization node. The editor renders all
    three, so a whole-notebook run has to plan all three rather than silently skip them.
    """
    if isinstance(value, str) and not value.strip().startswith("{"):
        # Anything else a string prop can hold is the SQL itself.
        return value.strip()
    query = _query_prop_object(value)
    if query is None:
        return ""
    source = query if query.get("kind") == "HogQLQuery" else query.get("source")
    if isinstance(source, dict) and source.get("kind") == "HogQLQuery" and isinstance(source.get("query"), str):
        return source["query"]
    return ""


def _query_prop_object(value: Any) -> dict[str, Any] | None:
    if isinstance(value, str):
        try:
            value = json.loads(value)
        except ValueError:
            return None
    return value if isinstance(value, dict) else None


# Mirrors the SQLV2 node, which opens on the chart only when `outputTab` says so and draws a line
# chart when `vizQuery` names no display.
_DEFAULT_CHART_DISPLAY = "ActionsLineGraph"


def _axis_column(axis: Any) -> str | None:
    column = axis.get("column") if isinstance(axis, dict) else None
    return column if isinstance(column, str) and column else None


def _visualization_from_props(props: dict[str, Any]) -> CellVisualization | None:
    output_tab, viz_query = props.get("outputTab"), props.get("vizQuery")
    code = props.get("code")
    if not (isinstance(code, str) and code.strip()):
        # Mirrors the editor's `getSqlV2PropsFromQueryProp`: a cell without `code` whose `query`
        # is a visualization node opens on that chart, whatever `outputTab` and `vizQuery` hold.
        query = _query_prop_object(props.get("query"))
        if query and query.get("kind") == "DataVisualizationNode" and _code_from_query_prop(query).strip():
            output_tab, viz_query = "visualization", query
    if output_tab != "visualization":
        return None
    viz_query = viz_query if isinstance(viz_query, dict) else {}
    chart_settings = viz_query.get("chartSettings")
    chart_settings = chart_settings if isinstance(chart_settings, dict) else {}
    display = viz_query.get("display")
    y_axis = chart_settings.get("yAxis")
    breakdown = chart_settings.get("seriesBreakdownColumn")
    return CellVisualization(
        display=display if isinstance(display, str) and display else _DEFAULT_CHART_DISPLAY,
        x_axis=_axis_column(chart_settings.get("xAxis")),
        # The chart picks the Y series only when `yAxis` is absent. An empty list records that the
        # author removed every series, so the chart plots nothing, and the two must not merge.
        y_axis=tuple(column for column in map(_axis_column, y_axis) if column) if isinstance(y_axis, list) else None,
        series_breakdown=breakdown if isinstance(breakdown, str) and breakdown else None,
    )


def extract_cells(content: Any) -> list[NotebookCellState]:
    markdown = _get_markdown_notebook_markdown(content)
    if markdown is None:
        return []
    cells: list[NotebookCellState] = []
    for tag_name, raw, _next_line_index in _iter_markdown_component_blocks(markdown):
        cell_type = _CELL_TAGS.get(tag_name)
        if cell_type is None:
            continue
        props = _parse_markdown_component_props(raw)
        node_id = props.get("nodeId")
        if not isinstance(node_id, str) or not node_id:
            continue
        code = props.get("dataframeQuery") if cell_type == "saved_insight" else props.get("code")
        if cell_type == "sql" and not (isinstance(code, str) and code.strip()):
            code = _code_from_query_prop(props.get("query"))
        dataframe_name = props.get("returnVariable")
        if not isinstance(dataframe_name, str):
            dataframe_name = {"sql": "sql_df", "python": "df", "saved_insight": "insight_df"}[cell_type]
        if cell_type == "saved_insight" and not (isinstance(code, str) and code.strip()):
            dataframe_name = ""
        connection_id = props.get("connectionId")
        cells.append(
            NotebookCellState(
                node_id=node_id,
                cell_type=cell_type,
                dataframe_name=dataframe_name.strip() if isinstance(dataframe_name, str) else "",
                code=code if isinstance(code, str) else "",
                connection_id=connection_id if isinstance(connection_id, str) and connection_id else None,
                send_raw_query=props.get("sendRawQuery") is True,
                visualization=_visualization_from_props(props) if tag_name == "SQLV2" else None,
            )
        )
    used_names: set[str] = set()
    for cell_type in ("sql", "saved_insight"):
        for cell in cells:
            if cell.cell_type != cell_type or not _DATAFRAME_NAME.fullmatch(cell.dataframe_name):
                continue
            base_name = cell.dataframe_name
            suffix = 2
            while cell.dataframe_name.lower() in used_names:
                cell.dataframe_name = f"{base_name}_{suffix}"
                suffix += 1
            used_names.add(cell.dataframe_name.lower())
    return cells


def validate_cell_count(previous_content: Any, next_content: Any) -> None:
    """Refuse a save that grows a notebook past the cell ceiling.

    Only growth is refused. A notebook already over the ceiling — written before this existed,
    or by a path that predates it — stays editable, so its owner can delete cells down instead
    of finding every save rejected. A save that leaves the count unchanged always passes.
    """
    next_count = sum(
        cell.cell_type != "saved_insight" or bool(cell.code.strip()) for cell in extract_cells(next_content)
    )
    previous_count = sum(
        cell.cell_type != "saved_insight" or bool(cell.code.strip()) for cell in extract_cells(previous_content)
    )
    if next_count <= MAX_NOTEBOOK_CELLS or next_count <= previous_count:
        return
    raise NotebookCellLimitExceeded(
        f"This notebook is at its limit of {MAX_NOTEBOOK_CELLS} cells. "
        "Delete a cell before adding another, or start a new notebook for the next step."
    )


def _referenced_names(cell: NotebookCellState, candidates: set[str]) -> set[str]:
    if not candidates or not cell.code.strip():
        return set()
    if cell.cell_type == "python":
        return set(analyze_python_globals(cell.code).used) & candidates
    if cell.cell_type == "sql":
        try:
            query = parse_select(cell.code)
            collector = _TableReferenceCollector(candidates)
            collector.visit(query)
            own_ctes = set(query.ctes.keys()) if isinstance(query, ast.SelectQuery) and query.ctes else set()
            return collector.found - own_ctes
        except Exception:
            # Unparseable code (mid-edit, DuckDB syntax): a word-boundary scan keeps the
            # graph usable instead of dropping every edge.
            return {name for name in candidates if re.search(rf"\b{re.escape(name)}\b", cell.code)}
    return set()


def get_dataframe_owners(cells: list[NotebookCellState]) -> dict[str, str]:
    eligible_cells = [cell for cell in cells if _DATAFRAME_NAME.fullmatch(cell.dataframe_name)]
    preferred_owners: dict[str, str] = {}
    for cell_type in ("sql", "saved_insight", "python"):
        for cell in eligible_cells:
            if cell.cell_type == cell_type:
                preferred_owners.setdefault(cell.dataframe_name.lower(), cell.node_id)
    owners: dict[str, str] = {}
    for cell in eligible_cells:
        if preferred_owners.get(cell.dataframe_name.lower()) == cell.node_id:
            owners.setdefault(cell.dataframe_name, cell.node_id)
    return owners


def build_dependency_edges(cells: list[NotebookCellState]) -> None:
    """Populate depends_on/dependents in place. Names follow the run-ref convention:
    SQL cells win dataframe-name collisions, unnamed cells export nothing."""
    owner_by_name = get_dataframe_owners(cells)

    by_node: dict[str, NotebookCellState] = {cell.node_id: cell for cell in cells}
    for cell in cells:
        candidates = {name for name, owner in owner_by_name.items() if owner != cell.node_id}
        for name in sorted(_referenced_names(cell, candidates)):
            upstream_id = owner_by_name[name]
            cell.depends_on.append(upstream_id)
            by_node[upstream_id].dependents.append(cell.node_id)


def _is_stale(
    cell: NotebookCellState,
    latest_run: NotebookNodeRun,
    cells_by_node: dict[str, NotebookCellState],
    latest_done_by_node: dict[str, NotebookNodeRun],
    variables: list[NotebookVariable],
) -> bool:
    if cell.cell_type == "sql" and latest_run.node_type != NotebookNodeRun.NodeType.DUCKDB:
        refs: dict[str, str | None] = {}
        for upstream_id in cell.depends_on:
            upstream = cells_by_node[upstream_id]
            upstream_run = latest_done_by_node.get(upstream_id)
            refs[upstream.dataframe_name] = (
                upstream_run.code
                if upstream_run is not None and upstream.cell_type in ("sql", "saved_insight")
                else None
            )
        try:
            # Same order as dispatch (resolve_sql_node_run): variables bind before the CTE
            # merge, so a changed variable value changes the resolution and marks the cell
            # stale, while an unchanged one compares equal to the stored run code.
            return resolve_sql_v2_references(substitute_hogql_variables(cell.code, variables), refs) != latest_run.code
        except Exception:
            # No resolvable definition for a referenced upstream (never ran, renamed,
            # deleted) or a variable the notebook no longer declares: whatever produced
            # the last result no longer reflects the document, which is exactly what
            # stale means.
            return True
    if cell.cell_type == "sql":
        # A DuckDB run stores the code with `{name}` rewritten to `$name` parameters and the
        # local frames left as table names, so that rewrite is the comparable form.
        try:
            code = substitute_duckdb_variables(cell.code, variables)[0]
        except Exception:
            return True
    else:
        code = cell.code
    if code.strip() != latest_run.code.strip():
        return True
    for upstream_id in cell.depends_on:
        upstream_run = latest_done_by_node.get(upstream_id)
        if upstream_run is not None and upstream_run.created_at > latest_run.created_at:
            return True
    return False


def annotate_run_state(cells: list[NotebookCellState], team_id: int, notebook: Any) -> None:
    runnable_ids = [cell.node_id for cell in cells if cell.cell_type in ("sql", "python", "saved_insight")]
    if not runnable_ids:
        return
    runs = (
        NotebookNodeRun.objects.for_team(team_id)
        .filter(notebook=notebook, node_id__in=runnable_ids)
        .order_by("node_id", "-created_at")
    )
    latest_by_node = {run.node_id: run for run in runs.distinct("node_id")}
    latest_done_by_node = {
        run.node_id: run for run in runs.filter(status=NotebookNodeRun.Status.DONE).distinct("node_id")
    }

    cells_by_node = {cell.node_id: cell for cell in cells}
    variables = build_notebook_variables(notebook.variables or [])
    for cell in cells:
        if cell.cell_type not in ("sql", "python", "saved_insight"):
            continue
        latest = latest_by_node.get(cell.node_id)
        if latest is None:
            cell.status = "never_run"
            continue
        envelope = latest.envelope if isinstance(latest.envelope, dict) else {}
        cell.last_run = {
            "run_id": str(latest.id),
            "status": latest.status,
            "finished_at": latest.updated_at,
            "row_count": envelope.get("row_count"),
            "columns": envelope.get("columns"),
            "error": latest.error or envelope.get("error") or None,
        }
        if latest.status == NotebookNodeRun.Status.RUNNING:
            cell.status = "running"
        elif latest.status == NotebookNodeRun.Status.DONE and _is_stale(
            cell, latest, cells_by_node, latest_done_by_node, variables
        ):
            cell.status = "stale"
        else:
            cell.status = latest.status


def build_notebook_cell_state(team_id: int, notebook: Any) -> list[NotebookCellState]:
    cells = extract_cells(notebook.content)
    build_dependency_edges(cells)
    annotate_run_state(cells, team_id, notebook)
    for cell in cells:
        if len(cell.code) > _CODE_PREVIEW_CHARS:
            cell.code = cell.code[:_CODE_PREVIEW_CHARS] + "\n… [truncated]"
    # Prose is added after the truncation above, and must stay that way. An editor that edits a
    # block by id compares the source it read against the document to find the block again when
    # the offsets no longer fit. A truncated source never matches, so the edit fails.
    return _merge_prose_cells(cells, notebook.content)


def _merge_prose_cells(cells: list[NotebookCellState], content: Any) -> list[NotebookCellState]:
    """Return every cell in document order, with the notebook's prose blocks added.

    Prose carries no run state and no dataframe, so it takes no part in the dependency graph.
    It is listed so a caller can address a paragraph, a heading, or a fenced block by id.
    """
    markdown = _get_markdown_notebook_markdown(content)
    if markdown is None:
        return cells

    by_node_id = {cell.node_id: cell for cell in cells}
    ordered: list[NotebookCellState] = []
    for block in iter_markdown_blocks(
        markdown, max_prose_blocks=MAX_ADDRESSABLE_PROSE_BLOCKS, group_anchored_prose=True
    ):
        if block.kind == "prose":
            ordered.append(
                NotebookCellState(
                    node_id=block.node_id,
                    cell_type="markdown",
                    code=block.source,
                    start=block.start,
                    end=block.end,
                )
            )
            continue
        cell = by_node_id.get(block.node_id)
        if cell is None:
            continue
        cell.start = block.start
        cell.end = block.end
        ordered.append(cell)
    return ordered