Creating An Endpoint

1---
2name: creating-an-endpoint
3description: >
4  Create a PostHog endpoint with the right shape on the first try — covers query kind choice, name
5  conventions, what to expose as variables (HogQL code_name vs insight breakdown),
6  data_freshness_seconds, and whether to materialise on day one. Use when the user says "create an endpoint", "expose this
7  query as an API", "turn this insight into an endpoint", or asks for help structuring a new
8  endpoint. Steers away from common mistakes: materialising a query with cohort breakdowns or
9  compare mode, inline-only variables on a materialised endpoint, unbounded date ranges, ambiguous
10  names.
11---
12 
13# Creating an endpoint
14 
15This skill walks through creating a new endpoint with the right configuration. Endpoints expose
16saved HogQL or insight queries as callable HTTP routes — the configuration choices made at
17creation time determine cost, latency, and how callers integrate.
18 
19The materialisation deep-dive lives at `references/materializing.md`. Pull it in when the
20materialisation decision is non-obvious.
21 
22## When to use this skill
23 
24- "Create an endpoint for [query]"
25- "Expose this insight as an API"
26- "Help me turn this HogQL into a callable endpoint"
27- A new caller (mobile app, customer-facing dashboard, downstream pipeline) needs PostHog data
28  and the user is choosing how to deliver it
29 
30## Decisions to make in order
31 
32### 1. Should this even be an endpoint?
33 
34Endpoints are right when:
35 
36- An **external system** (someone else's code) needs to call PostHog for data
37- The query is **stable** — not exploratory analysis
38- The shape is **reusable** — same query with different parameters
39 
40Endpoints are wrong when:
41 
42- An internal PostHog dashboard or insight needs the data — use the insight directly; an endpoint
43  only adds an external API surface you don't need internally
44- One-off, exploratory analysis — use the `execute-sql` tool (or the SQL editor) directly
45 
46Heavy aggregation is **not** a reason to avoid an endpoint. Endpoints are themselves saved
47queries, and a heavy, frequently-called aggregation is often the _best_ case for an endpoint with
48materialisation turned on.
49 
50If the user is unsure, ask what's calling the endpoint and what shape they expect.
51 
52### 2. Pick a name
53 
54Names are URL-safe (letters, numbers, hyphens, underscores), start with a letter, max 128 chars,
55must be unique within the project. Lean toward:
56 
57- **Descriptive over generic** — `weekly_active_users_by_org` over `metrics`
58- **Snake_case** — matches how the name appears in code paths and URLs
59- **No version in the name** — versions are managed by the endpoint itself
60- **No "endpoint" in the name** — redundant
61 
62The name appears in the URL: `/api/projects/{team_id}/endpoints/{name}/run`. It's not
63trivially renameable later (callers depend on the path) — get it right at creation.
64 
65### 3. Pick the query kind
66 
67Two options exist:
68 
69- **HogQL** (`HogQLQuery`) — raw SQL written by the user. Variables defined via `{variables.x}`
70  syntax, matched on `code_name`. Recommended for new endpoints when the caller cares about
71  the exact column shape of the response.
72- **Insight** — wraps an existing insight definition. Best supported for `TrendsQuery`,
73  `LifecycleQuery`, and `RetentionQuery`: these can be materialised, and the breakdown can act as
74  a variable (Trends and Retention only; Lifecycle has no breakdown). Other insight kinds such as
75  `FunnelsQuery` can run inline but **cannot be materialised and don't expose breakdown
76  variables** — rewrite those as HogQL if you need either.
77 
78HogQL is the more flexible choice. Pick insight only when the user is genuinely re-publishing an
79existing insight (see "Creating from an existing insight" below) rather than building a new query.
80 
81### 4. Decide which inputs become variables
82 
83Anything that should change per-caller goes in variables; the rest is hard-coded in the query.
84 
85**For HogQL endpoints**, variables are declared in the query payload with `code_name`, `type`,
86and `default`. Each execution call passes `{ "variables": { "<code_name>": value } }`.
87 
88Common patterns:
89 
90- Time windows: `date_from`, `date_to`, or a single `lookback_days` integer
91- Identity filters: `user_id`, `account_id`, `team_id`
92- Pagination control beyond `limit` / `offset` (these are first-class on the run endpoint already)
93 
94**For insight endpoints**, the breakdown property acts as the variable (Trends and Retention
95only — Lifecycle has no breakdown). Pass the breakdown property name as the key. `date_from` /
96`date_to` are accepted as variables **only on non-materialised** insight endpoints — a materialised
97endpoint bakes its date range into the view, so callers can't shift the window.
98 
99Avoid:
100 
101- **Variables that change the shape of the result** — keep the columns stable. If callers need
102  fundamentally different result shapes, ship separate endpoints.
103- **Variables that bypass safety** — don't expose a `where_clause` variable that lets callers
104  inject arbitrary SQL.
105 
106### Creating from an existing insight
107 
108There's no server-side "make an endpoint from insight N" operation. To do it: read the insight's
109query (via the insight tools), pass that query to `endpoint-create`, and set `derived_from_insight`
110to the insight's short id so the origin is recorded. The endpoint then owns its own **copy** of
111the query — later edits to the insight don't propagate. Starting from scratch instead? Build the
112query first with the insight / `sql-variables` tools, then create the endpoint from it.
113 
114### 5. Set `data_freshness_seconds`
115 
116This one field does **two** jobs, so set it deliberately:
117 
1181. **Cache TTL** — results are served from cache until they're this many seconds old.
1192. **Materialisation refresh frequency** — on a materialised endpoint, this is also how often the
120   warehouse recomputes the materialised view.
121 
122So a lower value means fresher data _and_ more frequent recompute/refresh cost; a higher value is
123cheaper on both counts but staler.
124 
125The value must be one of a fixed set: `900` (15 min), `1800` (30 min), `3600` (1 h), `21600`
126(6 h), `43200` (12 h), `86400` (24 h, default), `604800` (7 d). There is no sub-15-minute
127option — `900` is the floor.
128 
129| `data_freshness_seconds` | When to pick it                                                      |
130| ------------------------ | -------------------------------------------------------------------- |
131| 900–1800                 | Freshest available — dashboards where staleness is visible           |
132| 3600–43200               | Most cases — fresh enough for product usage, cheap to recompute      |
133| 86400–604800             | Reports, weekly/daily metrics, anything aggregated over long periods |
134 
135Bias toward higher values unless the user explicitly needs fresher data. On a materialised
136endpoint, remember this also sets the refresh cadence.
137 
138### 6. Decide on day-one materialisation
139 
140See `references/materializing.md` for the full decision tree. Short version:
141 
142- **Recommend materialisation** when the endpoint will be called frequently, latency matters,
143  and the user can tolerate staleness equal to the refresh interval (typically 5-15 minutes for
144  scheduled materialisation, or hourly).
145- **Skip materialisation** for low-traffic endpoints, exploratory new endpoints (you don't
146  know yet if it'll get called), and queries where freshness is critical.
147 
148If unsure, create unmaterialised and add `is_materialized: true` later once usage stabilises.
149That avoids paying for materialisation on a query nobody ends up calling.
150 
151## Workflow
152 
1531. Confirm the use case (step 1 above). If it's not actually a fit for an endpoint, recommend
154   the alternative.
1552. Agree a name with the user.
1563. Walk through the query — confirm it's HogQL or insight, and that the columns/shape make sense.
1574. Identify what should be a variable. Show the user the variable declaration syntax.
1585. Pick `data_freshness_seconds` based on the user's freshness requirement (ask if not clear) —
159   remembering it also sets the materialisation refresh cadence.
1606. Make the materialisation call. If on the fence, ship without and revisit later.
1617. Call `endpoint-create` with the agreed config.
1628. Confirm by calling `endpoint-run` with a sample payload to verify the response shape.
1639. Hand off to `consuming-endpoints-from-client-code` if the user is about to wire it up.
164 
165## Example interaction
166 
167```text
168User: "I want to expose our monthly active users count as an API
169       for our analytics partner"
170 
171Agent:
172- "Quick check: is the partner going to call this on demand, or
173   should we be pushing data to them? Endpoints are pull-only."
174- User: "On demand"
175- "Got it. A few choices:
176   - Name: how about monthly_active_users? Lowercase, descriptive,
177     no version baggage.
178   - The query — is it HogQL or are you exposing an existing insight?
179     HogQL gives you exact column control; insight reuses what you've
180     already built.
181   - What should the partner be able to filter by? Org, time window,
182     both?
183   - How fresh? If MAU drifts within a day is fine, we can cache
184     results for 1-24 hours and serve almost everything from cache.
185   - Materialisation: if the partner is going to hit this many times
186     per day, materialising it would make responses near-instant.
187     The trade-off is ~10-15 minute staleness.
188 
189   What's your starting point on these?"
190 
191[user picks: HogQL, lookback_days + org_id variables, 3600s TTL,
192 not materialised yet]
193 
194- endpoint-create monthly_active_users {query, variables, ...}
195- endpoint-run with sample payload {org_id: "test", lookback_days: 30}
196- "Created and tested. Want help wiring up the client code?
197   That's consuming-endpoints-from-client-code."
198```
199 
200## Important notes
201 
202- **The name lives in the URL.** Changing it later requires migrating callers. Pick well.
203- **HogQL endpoints are more flexible than insight endpoints.** Default to HogQL unless the
204  user has a specific reason to wrap an existing insight.
205- **Variables with no default fail at call time.** Always set defaults during creation so the
206  endpoint is testable from the playground without specifying every variable.
207- **Materialised endpoints require all variables to be passed.** Calls without them are
208  rejected — this is intentional (security: prevents returning unfiltered data). Pair the
209  materialisation recommendation with a note to the user about which variables become required.
210  (Optional/partial variables on materialised endpoints are a known limitation the PostHog team
211  plans to lift — if it's blocking the user, nudge them via the `agent-feedback` tool.)
212- **Don't enable materialisation on a query that isn't eligible.** Use
213  `endpoints-materialization-preview` first to confirm eligibility and see the rejection reason
214  if any.
215- **Endpoints are not stable forever.** When the user changes the query, a new version is created
216  automatically (the old version stays accessible via `?version=N`). `data_freshness_seconds` and
217  materialisation are per-version. Adjust as the endpoint evolves.
218- **Recommend callers pin to a version.** Tell the user to call with `?version=N` rather than
219  relying on "latest" — that way a future query edit (which cuts a new version) can't silently
220  change their results. They bump the pinned version deliberately once they've validated the new
221  one.
222- **Share friction via `agent-feedback`.** If a limitation gets in the way (eligibility rules,
223  required variables, the TTL enum), send the PostHog team a note — it's how the product and these
224  tools improve.
225