Auditing Endpoints

1---
2name: auditing-endpoints
3description: >
4  Audit every endpoint in a PostHog project for staleness, failed materialisations, and unused
5  materialised versions. Use when the user asks "what endpoints can I clean up?", "are any of my
6  endpoints broken?", "which materialised versions are still being called?", or wants a one-shot
7  cleanup pass over the Endpoints product. Produces a prioritised report grouped by issue type, with
8  recommended actions but does not modify anything without explicit confirmation.
9---
10 
11# Auditing endpoints
12 
13This skill produces a project-wide audit of the Endpoints product. Use it when the user wants to
14**find what to clean up** — unused endpoints, failing materialisations, materialised versions that
15nobody calls any more. It does not modify anything; it reports.
16 
17The deeper investigation per endpoint is `diagnosing-endpoint-performance`. The audit's job is to
18find candidates and hand off.
19 
20## When to use this skill
21 
22- "Audit my endpoints" / "What endpoints can I clean up?"
23- The user is taking over a project and wants to know what they've inherited
24- A periodic review (monthly / quarterly) of endpoint sprawl
25- The user is over a materialisation cost budget and wants to know what to disable
26 
27The dedicated tools give a fast endpoint-level view. For call frequency, recency, and cost over
28time, query the `query_log` table with `execute-sql` (endpoint-level). Per-version recency comes
29from `endpoint-versions` — each version carries its own `last_executed_at`.
30 
31## Available tools
32 
33| Tool                              | What it's for                                                                                                                                                                                                                          |
34| --------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
35| `execute-sql` (HogQL)             | **Primary read path.** Query `system.data_modeling_endpoints` for metadata (name, is_active, current_version, derived_from_insight, last_executed_at) and `query_log` for endpoint-level usage (call counts, recency, duration, bytes) |
36| `endpoint-materialization-status` | Per endpoint: is materialisation eligible, current status, last run, last error (not in the system tables — use this tool)                                                                                                             |
37| `endpoint-versions`               | All versions for one endpoint, latest first, with each version's query, materialisation state, and `last_executed_at`                                                                                                                  |
38| `endpoint-update`                 | Write path — disable (`is_active: false`) or unmaterialise (`is_materialized: false`) after the user confirms                                                                                                                          |
39| `agent-feedback`                  | Tell the PostHog team what's missing or confusing in this flow so the product and skill improve                                                                                                                                        |
40 
41Prefer reading from the system tables over the `endpoints-get-all` / `endpoint-get` tools — one
42SQL query returns the whole inventory and lets you join metadata to usage in `query_log`.
43 
44## What counts as an issue
45 
46| Category                        | Trigger                                                                                          | Typical action                                     |
47| ------------------------------- | ------------------------------------------------------------------------------------------------ | -------------------------------------------------- |
48| **Never called**                | No rows in `query_log` for the endpoint (personal-API-key calls only)                            | Confirm with the user, then disable                |
49| **Stale**                       | `query_log` shows the last call more than 30 days ago                                            | Confirm with the user; often safe to disable       |
50| **Inactive**                    | `is_active = 0` in `system.data_modeling_endpoints`                                              | Verify intent; if abandoned, delete                |
51| **Failing materialisation**     | `endpoint-materialization-status` returns `Failed` with an error                                 | Hand off to `diagnosing-endpoint-performance`      |
52| **Unused materialised version** | A materialised version whose `last_executed_at` (from `endpoint-versions`) is null or long stale | Unmaterialise that version, or roll to a newer one |
53| **Drifted versions**            | Many versions exist (query changed repeatedly)                                                   | History noise — not an issue, but worth noting     |
54 
55Usage counts only **personal-API-key calls** — an endpoint exercised solely from the Playground
56tab or the app will look unused. Per-version `last_executed_at` is recorded only for runs since
57that tracking was added, so a version can read null while still being used; always confirm before
58removing.
59 
60## Workflow
61 
62### 1. List endpoints and their metadata
63 
64One `execute-sql` query gets the whole inventory from `system.data_modeling_endpoints`:
65 
66```sql
67SELECT name, is_active, current_version, derived_from_insight, last_executed_at
68FROM system.data_modeling_endpoints
69ORDER BY name
70```
71 
72No rows → the project has no endpoints; say so and stop. Don't invent issues. (The
73`last_executed_at` column here is a convenience endpoint-level timestamp; for call frequency and
74cost, use `query_log` in the next step.)
75 
76### 2. Pull usage from `query_log`
77 
78`query_log` records every personal-API-key call, tagged with the endpoint name. One query gives
79recency and call counts across all endpoints:
80 
81```sql
82SELECT name, count() AS calls, max(query_start_time) AS last_called
83FROM query_log
84WHERE endpoint LIKE '%/endpoints/%' AND is_personal_api_key_request
85GROUP BY name
86ORDER BY name
87```
88 
89Cross-reference with step 1:
90 
91- **In metadata, absent from `query_log`** → never called via API key
92- **Last call more than 30 days ago** → stale
93 
94`query_log` also exposes `query_duration_ms`, `read_rows`, and `read_bytes` per call — useful to
95flag expensive endpoints in the same pass. This is endpoint-level; per-version recency comes from
96`endpoint-versions` (step 3).
97 
98### 3. Check materialisation health and unused versions
99 
100For each materialised endpoint, call `endpoint-materialization-status` (this isn't in the system
101tables). Surface any with `status: "Failed"` separately — these are active failures, not staleness.
102 
103Then call `endpoint-versions` and read each version's `last_executed_at`: a **materialised**
104version that's null or long stale is an unused-materialised-version candidate. Treat this as a
105lead, not proof — per-version recency only counts API-key runs since tracking was added, so confirm
106with the user before unmaterialising.
107 
108### 4. Present the audit
109 
110Render a prioritised report grouped by category. Don't dump raw JSON; use a readable table per
111section:
112 
113```text
114## Endpoints audit — 9 issues
115 
116### 🔴 Failing materialisations (1)
117- weekly_revenue (v3) — Failed 2h ago, "Column 'event_date' does not exist"
118  → hand off to diagnosing-endpoint-performance
119 
120### 🟠 Never called via API key (3)
121- internal_admin_query — created 5 months ago
122- legacy_signup_funnel — created 1 year ago, materialised
123- experiment_arm_lookup — created 9 months ago
124 
125### 🟠 Unused materialised versions (2)  [from endpoint-versions]
126- monthly_active_users — v3 materialised, last_executed_at null (currently on v4 — unmaterialise v3)
127- order_summary — v1 materialised, last_executed_at null
128 
129### 🟡 Stale (3)
130- holiday_promo_2024 — last called 4 months ago
131- ab_test_phase_1 — last called 2 months ago
132- daily_revenue_cohort — last called 45 days ago
133 
134Recommended order:
1351. Investigate the failing materialisation (blocks fresh data)
1362. Unmaterialise the unused versions (free storage + compute)
1373. Disable the never-called endpoints (if user confirms)
1384. Review stale endpoints with the user
139```
140 
141The exact format is less important than: prioritised, grouped, actionable, and hand-off
142clear.
143 
144### 5. Offer the next step
145 
146End with a clear question, not a decision:
147 
148- "Want me to unmaterialise the unused versions?" — needs `endpoint-update` with
149  `is_materialized: false` per version
150- "Want me to disable the never-called endpoints?" — needs `endpoint-update` with `is_active: false`
151- "Want me to dig into the failing materialisation?" — hands off to
152  `diagnosing-endpoint-performance`
153 
154**Never act from the audit alone.** Disabling or unmaterialising affects external API consumers;
155always confirm before modifying.
156 
157## Example interaction
158 
159```text
160User: "Audit my endpoints"
161 
162Agent steps:
163- execute-sql on system.data_modeling_endpoints → 12 endpoints (4 materialised)
164- execute-sql on query_log grouped by name → 8 endpoints have calls
165- endpoint-materialization-status on each materialised endpoint → 1 failing
166- endpoint-versions on the materialised ones → order_summary v2's last_executed_at is null
167 
168- Report:
169  "Found 6 things worth looking at across 12 endpoints:
170 
171   🔴 Failing materialisations (1)
172   - monthly_active_users (v2) — materialisation failed yesterday with
173     'memory limit exceeded'. The endpoint is still callable inline but
174     responses will be slower.
175 
176   🟠 Never called via API key (3)
177   - legacy_funnel_v1 — created 11 months ago
178   - test_export — created 3 months ago
179   - admin_dump — created 8 months ago, materialised (wasteful)
180 
181   🟠 Unused materialised versions (1)  [from endpoint-versions]
182   - order_summary v2 — materialised but its last_executed_at is null
183     while v3 is current. Likely safe to unmaterialise v2.
184 
185   🟡 Stale (1)
186   - holiday_2024 — last called 5 months ago
187 
188   Caveat: usage only counts personal-API-key calls; playground and
189   dashboard runs aren't counted. Per-version recency comes from
190   endpoint-versions and is sparse. Confirm before disabling.
191 
192   Want me to start with the failing materialisation, or clean up the
193   unused version first?"
194```
195 
196## Important notes
197 
198- **The audit is read-only.** Never call destructive tools from this flow. Hand off or confirm
199  before any modification.
200- **Empty = healthy.** Don't pad an empty report with theoretical issues. "Nothing to clean up"
201  is a good answer.
202- **Read with SQL, drill in with the version tool.** `system.data_modeling_endpoints` (metadata)
203  and `query_log` (endpoint-level call counts, recency, cost) via `execute-sql` answer most of the
204  audit. Per-version recency comes from `endpoint-versions` (each version's `last_executed_at`).
205- **API-key-only scope.** Usage only counts personal-API-key calls. An endpoint exercised only from
206  the Playground tab or the app will look unused. Always confirm before acting.
207- **Materialisation costs storage and compute.** When an endpoint no longer needs materialisation,
208  the cheapest fix is `endpoint-update` with `is_materialized: false` — not deleting the endpoint.
209- **Inactive ≠ stale.** An endpoint with `is_active: false` was deliberately turned off. Don't
210  recommend deletion unless the user confirms it's truly abandoned.
211