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