creating-an-endpoint

Endpoints are right when:

• An external system (someone else's code) needs to call PostHog for data
• The query is stable — not exploratory analysis
• The shape is reusable — same query with different parameters

Endpoints are wrong when:

• An internal PostHog dashboard or insight needs the data — use the insight directly; an endpoint only adds an external API surface you don't need internally
• One-off, exploratory analysis — use the execute-sql tool (or the SQL editor) directly

Heavy aggregation is not a reason to avoid an endpoint. Endpoints are themselves saved queries, and a heavy, frequently-called aggregation is often the best case for an endpoint with materialisation turned on.

If the user is unsure, ask what's calling the endpoint and what shape they expect.

Names are URL-safe (letters, numbers, hyphens, underscores), start with a letter, max 128 chars, must be unique within the project. Lean toward:

• Descriptive over generic — weekly_active_users_by_org over metrics
• Snake_case — matches how the name appears in code paths and URLs
• No version in the name — versions are managed by the endpoint itself
• No "endpoint" in the name — redundant

The name appears in the URL: /api/projects/{team_id}/endpoints/{name}/run. It's not trivially renameable later (callers depend on the path) — get it right at creation.

Two options exist:

• HogQL (HogQLQuery) — raw SQL written by the user. Variables defined via {variables.x} syntax, matched on code_name. Recommended for new endpoints when the caller cares about the exact column shape of the response.
• Insight — wraps an existing insight definition. Best supported for TrendsQuery, LifecycleQuery, and RetentionQuery: these can be materialised, and the breakdown can act as a variable (Trends and Retention only; Lifecycle has no breakdown). Other insight kinds such as FunnelsQuery can run inline but cannot be materialised and don't expose breakdown variables — rewrite those as HogQL if you need either.

HogQL is the more flexible choice. Pick insight only when the user is genuinely re-publishing an existing insight (see "Creating from an existing insight" below) rather than building a new query.

Anything that should change per-caller goes in variables; the rest is hard-coded in the query.

For HogQL endpoints, variables are declared in the query payload with code_name, type, and default. Each execution call passes { "variables": { "<code_name>": value } }.

Common patterns:

• Time windows: date_from, date_to, or a single lookback_days integer
• Identity filters: user_id, account_id, team_id
• Pagination control beyond limit / offset (these are first-class on the run endpoint already)

For insight endpoints, the breakdown property acts as the variable (Trends and Retention only — Lifecycle has no breakdown). Pass the breakdown property name as the key. date_from / date_to are accepted as variables only on non-materialised insight endpoints — a materialised endpoint bakes its date range into the view, so callers can't shift the window.

Avoid:

• Variables that change the shape of the result — keep the columns stable. If callers need fundamentally different result shapes, ship separate endpoints.
• Variables that bypass safety — don't expose a where_clause variable that lets callers inject arbitrary SQL.

There's no server-side "make an endpoint from insight N" operation. To do it: read the insight's query (via the insight tools), pass that query to endpoint-create, and set derived_from_insight to the insight's short id so the origin is recorded. The endpoint then owns its own copy of the query — later edits to the insight don't propagate. Starting from scratch instead? Build the query first with the insight / sql-variables tools, then create the endpoint from it.

This one field does two jobs, so set it deliberately:

1. Cache TTL — results are served from cache until they're this many seconds old.
2. Materialisation refresh frequency — on a materialised endpoint, this is also how often the warehouse recomputes the materialised view.

So a lower value means fresher data and more frequent recompute/refresh cost; a higher value is cheaper on both counts but staler.

The value must be one of a fixed set: 900 (15 min), 1800 (30 min), 3600 (1 h), 21600 (6 h), 43200 (12 h), 86400 (24 h, default), 604800 (7 d). There is no sub-15-minute option — 900 is the floor.

Bias toward higher values unless the user explicitly needs fresher data. On a materialised endpoint, remember this also sets the refresh cadence.

See references/materializing.md for the full decision tree. Short version:

• Recommend materialisation when the endpoint will be called frequently, latency matters, and the user can tolerate staleness equal to the refresh interval (typically 5-15 minutes for scheduled materialisation, or hourly).
• Skip materialisation for low-traffic endpoints, exploratory new endpoints (you don't know yet if it'll get called), and queries where freshness is critical.

If unsure, create unmaterialised and add is_materialized: true later once usage stabilises. That avoids paying for materialisation on a query nobody ends up calling.