creating-an-endpoint

Files (2)

• SKILL.md
• materializing.md · references/

SKILL.md (first lines)

# Creating an endpoint

This skill walks through creating a new endpoint with the right configuration. Endpoints expose
saved HogQL or insight queries as callable HTTP routes — the configuration choices made at
creation time determine cost, latency, and how callers integrate.

The materialisation deep-dive lives at `references/materializing.md`. Pull it in when the
materialisation decision is non-obvious.

## When to use this skill

- "Create an endpoint for [query]"
- "Expose this insight as an API"
- "Help me turn this HogQL into a callable endpoint"
- A new caller (mobile app, customer-facing dashboard, downstream pipeline) needs PostHog data
  and the user is choosing how to deliver it

## Decisions to make in order

### 1. Should this even be 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.

### 2. Pick a name

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.

### 3. Pick the query kind

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.

### 4. Decide which inputs become variables

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.

### Creating from an existing insight

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.

### 5. Set `data_freshness_seconds`

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.

| `data_freshness_seconds` | When to pick it                                                      |
| ------------------------ | -------------------------------------------------------------------- |
| 900–1800                 | Freshest available — dashboards where staleness is visible           |

From PostHog/skills, MIT.