writing-streamlit-apps

Writing Streamlit apps for the PostHog sandbox

The source you write becomes app.py at the root of a sandboxed Streamlit 1.31 runtime. Deployment mechanics (create/start/share) are the managing-streamlit-apps skill; this one is about the code.

Reading PostHog data: posthog_apps.query()

The one and only data door is the in-sandbox bridge:

import posthog_apps

df = posthog_apps.query("SELECT event, count() FROM events GROUP BY event LIMIT 10")

• Takes a HogQL string, returns a pandas DataFrame.
• Raises RuntimeError on failure. The message is deliberately generic ("Query execution failed") — the bridge does not return query internals to the sandbox, so you cannot diagnose a bad query from inside the app. Catch it and render with st.error(str(e)) so viewers get a message instead of a stack trace, and test queries in the SQL editor where real errors are visible.
• import posthog does NOT exist in the sandbox — the module is posthog_apps, deliberately distinct from the posthog-python SDK's name.
• The bridge is pre-authenticated to the app's project; user code never sees a token, and there is nothing to configure.
• Queries run with server-side caps (30 s execution, 256 MB memory) that don't scale with sandbox sizing — so bound time ranges, LIMIT results, and aggregate in HogQL rather than pulling raw events into pandas.

Design for Streamlit's rerun model

Streamlit reruns the whole script top to bottom on every widget interaction. Two consequences:

1. Cache every bridge call — uncached, one slider drag re-fires every query:

   @st.cache_data(ttl=300, show_spinner="Running query...")
   def run_query(hogql: str) -> pd.DataFrame:
       return posthog_apps.query(hogql)

1. Use `st.session_state` for anything that must survive reruns — accumulated selections, pagination cursors, "last refreshed" stamps. Module-level variables reset on every interaction.

Widgets drive parameters naturally, but never interpolate a free-text widget value into HogQL. The bridge runs your query with the version author's data access, and anyone who can view the app drives those widgets — a raw st.text_input spliced into a query hands viewers the author's access to write their own. Constrain the input instead: pick from a fixed list you control (st.selectbox over known values), or coerce to a type that can't carry SQL (int(days), a date from st.date_input), and validate before it reaches the query.

Layout and charts

• st.set_page_config(page_title=..., layout="wide") first — the default narrow layout wastes most of the screen for data apps.
• Structure with st.columns for side-by-side metrics, st.tabs for alternate views, st.expander for detail sections; st.metric for headline numbers.
• Charts: st.plotly_chart(fig, use_container_width=True) with plotly express is the reliable default; st.dataframe(df, use_container_width=True) for tables. matplotlib/seaborn also work via st.pyplot.

What's installed

The image ships Python 3.11 with: streamlit 1.31, pandas, numpy, polars, plotly, matplotlib, seaborn, scipy, scikit-learn, pyarrow, duckdb, requests, beautifulsoup4, lxml, sqlalchemy, aiohttp. There is no way to add dependencies: the sandbox never runs pip (a deliberate security posture — no arbitrary package code at boot), and a requirements.txt in an uploaded zip is tolerated but dropped. Only import what's listed above.

Structure and runtime constraints

• One file. Via the MCP set-source flow your source IS app.py; there are no other modules, so keep everything in it.
• The sandbox is ephemeral: anything written to disk disappears on stop/restart. Don't build state on files; recompute from queries (with caching) or hold it in st.session_state.
• Your code runs as an unprivileged user; there's no posthog SDK, no way to pass your own environment variables or secrets to the app, and no expectation of general network egress. Don't read from os.environ — anything there belongs to the sandbox runtime, not your app. Design around posthog_apps.query() as the data source.

A minimal well-shaped app

import pandas as pd
import plotly.express as px
import posthog_apps
import streamlit as st

st.set_page_config(page_title="Events overview", layout="wide")
st.title("Events overview")


@st.cache_data(ttl=300, show_spinner="Running query...")
def run_query(hogql: str) -> pd.DataFrame:
    return posthog_apps.query(hogql)


# A slider is bounded and coerced to int, so it is safe to interpolate.
days = int(st.slider("Days to show", 1, 30, 7))
try:
    daily = run_query(
        f"""
        SELECT toDate(timestamp) AS day, count() AS events
        FROM events
        WHERE timestamp >= now() - INTERVAL {days} DAY
        GROUP BY day ORDER BY day
        """
    )
    st.plotly_chart(px.bar(daily, x="day", y="events"), use_container_width=True)
except RuntimeError as e:
    st.error(str(e))