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 Code) reasoning about their own PRs.

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, 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 open_to_merge_seconds over the returned merged rows yourself — median and p95, never a mean).
• 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 completed runs; p50_seconds / p95_seconds cover successful runs only (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, 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.
• engineering-analytics-flaky-tests — per-test flakiness leaderboard from the per-test CI spans, over a window (date_from default -7d, max 30 days). A test qualifies by passing on an automatic retry (rerun_passed_count, the strongest signal — only rerun-enabled CI lanes emit it) or failing on ≥ min_failed_prs distinct PRs (failed_pr_count, the signal for no-rerun lanes). Answers "which tests are flaky right now" and picks quarantine candidates; xfailed_count > 0 means already quarantined but still failing. Counts are absolute signal, never rates — passing runs are mostly not emitted, so there is no honest denominator.

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.
• 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.
• No reviews, approvals, per-check/job, or deploys yet. Don't infer review behaviour or DORA metrics from their absence; that data hasn't landed. pr-lifecycle is partial for the same reason.
• 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. At most limit rows (newest first); truncated is true when more match, and there is no repo or limit filter to narrow the call. When truncated is true, any percentile or count you derive covers only the newest page — not the whole window — so say so and shrink date_from until the real set fits under the cap.

Choosing a tool

The high-value chain

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

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.
• Don't report a CI count as a settled failure when pending > 0 — it may be unsettled or stale.
• Don't infer reviews, approvals, per-check counts, or deploys — that data isn't ingested yet.
• 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.