/auditing-endpoints

name: auditing-endpoints
description: >
  Audit every endpoint in a PostHog project for staleness, failed materialisations, and unused
  materialised versions. Use when the user asks "what endpoints can I clean up?", "are any of my
  endpoints broken?", "which materialised versions are still being called?", or wants a one-shot
  cleanup pass over the Endpoints product. Produces a prioritised report grouped by issue type, with
  recommended actions but does not modify anything without explicit confirmation.

Auditing endpoints

This skill produces a project-wide audit of the Endpoints product. Use it when the user wants to **find what to clean up** — unused endpoints, failing materialisations, materialised versions that nobody calls any more. It does not modify anything; it reports.

The deeper investigation per endpoint is `diagnosing-endpoint-performance`. The audit's job is to find candidates and hand off.

When to use this skill

• "Audit my endpoints" / "What endpoints can I clean up?"
• The user is taking over a project and wants to know what they've inherited
• A periodic review (monthly / quarterly) of endpoint sprawl
• The user is over a materialisation cost budget and wants to know what to disable

The dedicated tools give a fast endpoint-level view. For call frequency, recency, and cost over time, query the `query_log` table with `execute-sql` (endpoint-level). Per-version recency comes from `endpoint-versions` — each version carries its own `last_executed_at`.

Available tools

| Tool | What it's for | | --------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `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) | | `endpoint-materialization-status` | Per endpoint: is materialisation eligible, current status, last run, last error (not in the system tables — use this tool) | | `endpoint-versions` | All versions for one endpoint, latest first, with each version's query, materialisation state, and `last_executed_at` | | `endpoint-update` | Write path — disable (`is_active: false`) or unmaterialise (`is_materialized: false`) after the user confirms | | `agent-feedback` | Tell the PostHog team what's missing or confusing in this flow so the product and skill improve |

Prefer reading from the system tables over the `endpoints-get-all` / `endpoint-get` tools — one SQL query returns the whole inventory and lets you join metadata to usage in `query_log`.

What counts as an issue

| Category | Trigger | Typical action | | ------------------------------- | ------------------------------------------------------------------------------------------------ | -------------------------------------------------- | | **Never called** | No rows in `query_log` for the endpoint (personal-API-key calls only) | Confirm with the user, then disable | | **Stale** | `query_log` shows the last call more than 30 days ago | Confirm with the user; often safe to disable | | **Inactive** | `is_active = 0` in `system.data_modeling_endpoints` | Verify intent; if abandoned, delete | | **Failing materialisation** | `endpoint-materialization-status` returns `Failed` with an error | Hand off to `diagnosing-endpoint-performance` | | **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 | | **Drifted versions** | Many versions exist (query changed repeatedly) | History noise — not an issue, but worth noting |

Usage counts only **personal-API-key calls** — an endpoint exercised solely from the Playground tab or the app will look unused. Per-version `last_executed_at` is recorded only for runs since that tracking was added, so a version can read null while still being used; always confirm before removing.

Workflow

1. List endpoints and their metadata

One `execute-sql` query gets the whole inventory from `system.data_modeling_endpoints`:

SELECT name, is_active, current_version, derived_from_insight, last_executed_at
FROM system.data_modeling_endpoints
ORDER BY name

No rows → the project has no endpoints; say so and stop. Don't invent issues. (The `last_executed_at` column here is a convenience endpoint-level timestamp; for call frequency and cost, use `query_log` in the next step.)

2. Pull usage from `query_log`

`query_