# Diagnosing CI And Merge Bottlenecks > 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 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. - Skill: `posthog/diagnosing-ci-and-merge-bottlenecks` (Agent Skill) - Install (CLI): `npx skillmds add posthog/diagnosing-ci-and-merge-bottlenecks` - Raw SKILL.md: https://api.skillmd.com/api/skills/posthog/diagnosing-ci-and-merge-bottlenecks/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: DevOps & Infra - Author: posthog (https://skillmd.com/u/posthog) - Updated: 2026-09-09 - Page: https://skillmd.com/skills/posthog/diagnosing-ci-and-merge-bottlenecks --- # 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 (`github_pull_requests` / `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.