Diagnosing Ci And Merge Bottlenecks

---
name: diagnosing-ci-and-merge-bottlenecks
description: >
  Diagnoses CI and pull-request pipeline health for a GitHub repo using the engineering analytics MCP tools —
  pull-requests (PR list with CI status), workflow-health (per-workflow CI trends), and pr-lifecycle (a single PR's
  timeline). Use when asked whether CI is getting faster or slower, which GitHub Actions workflow is the slow or
  flaky long-pole, how long PRs take from open to merge, how an author's merge time compares to the cohort, which
  open PRs have failing or pending CI, or where a specific pull request is stuck. Triggers on "engineering
  analytics", "is CI getting slower", "slow workflow", "flaky CI", "time to merge", "cycle time", "PR throughput",
  "failing checks", "where is PR <n> stuck", "CI long pole", "what's holding up this PR". For a verdict on one
  specific CI failure (whose fault, which commit) use investigating-ci-failures; to save these numbers as insights
  use turning-engineering-analytics-into-insights.
---

# Diagnosing CI and merge bottlenecks

Engineering analytics treats a pull request like product analytics treats a user: a PR moves through a pipeline
(`opened → CI → review → merged → deployed`) and the job is to find where it slows down. The surface is **named
MCP tools** — you call them, you don't write SQL. Dogfooded on `PostHog/posthog`; the same tools serve
autonomous agents (e.g. PostHog Desktop) reasoning about their own PRs. Scope is aggregate pipeline health:
to take one failing test or red run to a verdict, switch to the `investigating-ci-failures` skill.

## The tools

- **`pull-requests`** — the PR workhorse. Open PRs plus anything merged or closed since `date_from` (default
  `-30d`), newest first. Each row carries `author` (nested object: `handle`, `display_name`, `is_bot`), `repo`
  (nested: `owner`, `name`), `state`, `is_draft`, `labels`, `open_to_merge_seconds`, `ready_to_merge_seconds`,
  and a `ci` rollup (`runs` / `passing` / `failing` / `pending`) from the head-SHA join. Answers most PR-level
  questions: which PRs have failing or pending CI, which are stuck open longest, per-author or per-repo triage, and
  time-to-merge stats (aggregate over the returned merged rows yourself, median and p95, never a mean; prefer
  `ready_to_merge_seconds` where non-null, it excludes draft time).
- **`workflow-health`** — per-workflow CI health over a window (`date_from` / `date_to`, default last 24 hours):
  `run_count`, `success_rate`, `p50_seconds`, `p95_seconds`, `last_failure_at`. Answers "is CI getting faster or
  slower" and "which workflow is the slow or flaky long pole". There is no built-in trend — call it over two
  adjacent windows and compare. `success_rate` covers runs that succeeded or ended in a decisive failure
  (`failure`, `timed_out`, `startup_failure`, or `stale`), excluding skipped, cancelled, neutral, and
  action-required runs. `p50_seconds` / `p95_seconds` cover successful runs only because
  cancelled and failed runs end early and would bias the duration trend. Each is `null`
  when a window has no qualifying runs — guard for null before comparing two windows (a workflow can have runs
  in one and none in the other). `run_scope=pull_request` scopes to PR-attributed runs, excluding master/main
  (same-repo PRs only — fork runs carry no PR attribution).
- **`pr-lifecycle`** — a single PR's timeline: a header plus ordered events — opened, ready-for-review and
  converted-to-draft transitions (when the issue-events table is synced), then a CI started/finished pair
  **per workflow run** (many on a multi-workflow repo, interleaved by time), then merged/closed. Answers
  "where is PR N stuck". `metric_quality` is `partial` (no review or comment events).
- **`engineering-analytics-flaky-tests`** — the active test-health queue from the per-test CI spans, over a
  window (`date_from` default `-7d`, max 30 days). Evidence is counted per CI run, never per span or run attempt.
  `classification` is `confirmed_flake` only where the evidence proves nondeterminism
  (`same_commit_recovery_run_count > 0`: one commit both failed and passed the test **in the same matrix
  job**, via a "Re-run failed jobs" attempt going green or an in-job retry; a pass in a different leg, such as FOSS
  against EE, is not recovery); `quarantined` means a tolerated failure was recorded while masked;
  `suspected_regression` means only failures were recorded, which is absence of proof, not proof of a real break.
  A test qualifies on any same-commit recovery, a quarantined failure, any master/main failure, or failures on ≥
  `min_failed_prs` distinct PRs (`failed_pr_count`). Answers "what is this failing test costing us" and picks
  quarantine candidates. **It does not answer "which tests are flaky"**: this queue only sees the main Backend pytest
  and Frontend Jest suites, and recovery proof only arrives when someone re-runs failed jobs (or a pytest test is hand-marked
  `@pytest.mark.flaky(reruns=N)`). Counts are absolute signal, never rates: passing runs are mostly not
  emitted, so there is no honest denominator.

- **`engineering-analytics-sources`**: the team's connected GitHub sources and repos. With more than one of
  either, call it first and pass the chosen entry's `source_id` **and** `repo` to `pull-requests`,
  `workflow-health`, and `pr-lifecycle`. Passing only `source_id` reads that source's default repo, not the one
  you picked. With a single source and repo the tools default to it.

There is no aggregate time-to-merge tool and no "counts" tool — derive those from `pull-requests` (the stuck/failing
counts, the merge-time percentiles).

## Caveats you must carry into every answer

These are structural limits of today's snapshot data — state them, don't paper over them.

- **`open_to_merge_seconds` is coarse.** It fuses _draft_ time and _ready-for-review_ time into one figure. Report
  it as "open to merge", never "cycle time" or "review time". Flag it when long-lived drafts inflate a number.
- **`ready_to_merge_seconds` is the precise companion**: merged_at minus the last observed ready-for-review
  transition (only the last draft/ready switch counts), or minus created_at for a merged PR verifiably never
  drafted. Null means "not observed" (the PR's life isn't fully inside the synced issue-event window, or the
  table isn't synced), never zero, so aggregate only over non-null rows and say how many were observable.
- **CI status can be stale.** The CI source syncs on a watermark and does not refresh a run that completes after
  newer runs land (until the `workflow_run` webhook ships). Treat a `pending` count as unsettled, not as a settled
  failure; lead with status, not a verdict.
- **CI for a PR is the head-SHA join, nothing else.** The `ci` rollup reflects only the latest commit's runs. There
  is no other link between a PR and its checks.
- **Review reads are deferred by choice.** The GitHub `reviews` endpoint syncs review submissions with their timestamps, but reads stay deferred until a wedge tool needs them. Don't infer review behaviour from their absence.
  `pr-lifecycle` is `partial` for the same reason.
- **Deploy metrics live on `engineering-analytics-dora`**, from the GitHub deployments tables when synced
  (`deploy_data_available`). Its change-failure and time-to-restore fields are deploy-status proxies (no incident
  link) — report them under their honest field names, never as the true DORA definitions.
- **Bots and drafts are present in `pull-requests` output, excluded by convention.** Filter out `author.is_bot`
  (nested under `author`, not a row-level field) and `is_draft` for throughput / merge-time questions; keep them in
  for bot-impact questions.
- **`pull-requests` returns a capped page.** 1000 rows, newest first: a fixed server-side cap, echoed in the
  response as `limit`, that no parameter raises. When `truncated` is `true`, any percentile or count you derive
  covers only that newest page, not the whole window. Say so, then narrow until the real set fits: `author`
  filters to one handle, `source_id` / `repo` to one repo, and `date_from` shortens the window.

## Choosing a tool

| The question                                           | Tool                                | How                                                                                                                                                                                                                                                                                                                                                          |
| ------------------------------------------------------ | ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Is CI getting slower? Which workflow is the long pole? | `workflow-health`                   | Call over two adjacent windows (e.g. `date_from=-14d`, then `date_from=-28d` `date_to=-14d`); compare `p50_seconds` and `p95_seconds` per workflow. Lead with the median but always check p95 separately — they move independently.                                                                                                                          |
| Which open PRs have failing or pending CI?             | `pull-requests`                     | Keep rows where `ci.failing > 0` or `ci.pending > 0`. `pending` means unsettled (or stale) — not a settled failure.                                                                                                                                                                                                                                          |
| Which PRs are stuck open longest?                      | `pull-requests`                     | Keep `state = open`, not `is_draft`, not `author.is_bot`; sort by `created_at` ascending (oldest first).                                                                                                                                                                                                                                                     |
| How long are PRs taking to merge? Per author?          | `pull-requests`                     | Over merged rows (`merged_at` set, not bot, not draft), aggregate `ready_to_merge_seconds` where non-null (fall back to `open_to_merge_seconds`, labeled as coarse) — median and p95. Group by `author.handle` for **cohort context, not a ranking** (per-developer surveillance is an explicit non-goal). Trend it by calling with two `date_from` windows. |
| Where is PR N stuck?                                   | `pr-lifecycle`                      | Walk the sorted events: `opened → ready_for_review` (draft time, when transition events are present), the CI span (first start → last finish; one pair per workflow), `last CI finished → merged`. The largest gap is the bottleneck. A long ready→merge with quick CI points at review/idle time the `partial` data can't itemize yet — say so.             |
| What is a failing test costing us? What to quarantine? | `engineering-analytics-flaky-tests` | Default window is `-7d`; rows are already ranked by blast radius (master failures, then distinct PRs hit). Report counts, never rates. For "is it flaky": only `confirmed_flake` rows are proven (one commit both failed and passed **in the same matrix job**: a re-run attempt went green, or an in-job retry recovered it).                               |

## The high-value chain

Mirror how a human investigates: aggregate signal → confirm → concrete PR.

```text
workflow-health  (find the slow/flaky long-pole workflow)
   → pull-requests  (confirm it's dragging merge time; list the affected PRs)
      → pr-lifecycle  (open a representative stuck PR and show the gap)
```

"CI median rose because `e2e-playwright` p95 doubled; that workflow is the long pole on PR #1234, which sat 47m in
CI before merging."

## Output expectations

- Lead with the verdict in one line, then the supporting numbers.
- Carry the coarse / partial / staleness caveat whenever the distinction matters.
- For multi-window or multi-workflow comparisons, a short table beats prose. Report median and p95 side by side —
  never collapse them into one "average".

## What NOT to do

- Don't call `open_to_merge_seconds` cycle time or review time — it's coarse open-to-merge;
  `ready_to_merge_seconds` is the cycle-time figure, and only where non-null.
- Don't report a CI count as a settled failure when `pending > 0` — it may be unsettled or stale.
- Don't infer reviews or approvals — review reads stay deferred until a wedge tool needs them. Don't infer per-check counts. Deploys come from
  `engineering-analytics-dora`, not from inference.
- Don't turn per-author buckets into a leaderboard — they're for finding stuck work, not ranking people.
- Don't reach for these tools to fetch raw PR contents or diffs — they surface pipeline signal, not the PR thread.

## Persisting an answer

These tools are ad-hoc reads; they cannot be saved as an insight or subscribed to. When the user wants the same
numbers as a saved insight, a dashboard tile, or a scheduled email/Slack delivery, switch to the
`turning-engineering-analytics-into-insights` skill: the underlying warehouse tables
(`<prefix>github_pull_requests` / `<prefix>github_workflow_runs`, prefix from `engineering-analytics-sources`)
are directly queryable with HogQL, and that skill carries the curated column semantics plus the
insight-create / subscriptions-create workflow.