Investigating CI Failures

PostHogOfficialPublished by PostHog

Skill
Research and analysis
Data and AI
MIT
No issues found

From the author

Investigates a specific CI failure to a verdict: whose fault, which commit, who wrote it, and whether it's fixed. Use for "who broke master", "why did this test fail in CI", "is this failure my PR's fault or everyone's", "is this test flaky or actually broken", "when did this failure start". Works from the engineering_analytics warehouse views (engineering_analytics_ci_failures, engineering_analytics_ci_job_history) plus the CI failure logs. Not for aggregate CI health, cost, or merge bottlenecks (use diagnosing-ci-and-merge-bottlenecks) and not for building saved insights (use turning-engineering-analytics-into-insights).

— the description by PostHog

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

This skill provides a systematic approach to diagnosing CI failures by leveraging engineering analytics warehouse views. It enables developers to move from a failing test or red CI run to a definitive verdict—whether the failure is caused by a specific commit, is a flaky test, or is a trunk-borne issue. By analyzing failure fingerprints and job history, the workflow allows for rapid identification of the culprit SHA, the author, and the associated PR without needing to re-run CI jobs.

What it does

Fingerprints CI failures to group occurrences and identify patterns.
Determines if a failure is PR-specific, a trunk break, or a flaky test.
Identifies the culprit commit and the fix commit for trunk-borne failures.
Provides SQL queries to analyze CI job history and failure logs.
Distinguishes between deterministic failures and flaky tests using sibling attempt analysis.
Offers guidance on handling infrastructure, setup, and cache-related CI issues.

When to use it

When you need to determine who broke the master branch.
When investigating whether a CI failure is caused by your PR or an external factor.
When you need to verify if a test is flaky or actually broken.
When you need to find the specific commit that introduced a regression.
When you need to confirm if a fix for a CI failure has already landed.

Advantages

Eliminates the need to re-run CI jobs to diagnose failures.
Provides clear, evidence-based verdicts for developers.
Reduces time spent debugging by pinpointing the exact culprit commit.
Helps distinguish between genuine code regressions and infrastructure noise.
Standardizes the investigation process across the engineering team.

Usage examples

“Who broke master with the recent test failures in the data-warehouse job?”: The agent uses the boundary query to identify the first red run, naming the culprit commit, author, and PR, and confirms if a fix has already been merged.
“Is this CI failure my PR's fault or is it a known issue?”: The agent fingerprints the failure and checks its shape; if it appears on many branches, it identifies it as a trunk break rather than your PR's fault.
“Is this test flaky or is it failing consistently?”: The agent analyzes sibling attempts and failure history to determine if the test exhibits sporadic behavior or consistent failure.

Install

Download the skill as a ZIP:
https://autogineer.com/agent-skills/posthog/investigating-ci-failures/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip investigating-ci-failures.zip -d .agents/skills

Documentation by PostHog

Investigating CI failures

The job: take one failing test or one red run and get to a verdict a developer can act on — yours / trunk-borne / flaky, and when trunk-borne: the culprit SHA, its author, the PR, and whether a fix already landed. Everything below is derivation over data that already exists; you never need to re-run CI to answer.

Two warehouse views are the substrate (both non-materialized — always current, query them freely):

engineering_analytics_ci_failures — one row per pytest FAILED <nodeid> line from CI logs, pre-fingerprinted (fingerprint = test id + digit/hex-normalized error). Group by fingerprint to get first/last seen, occurrence count, and branch spread.
engineering_analytics_ci_job_history — one row per job attempt with conclusion AND commit attribution: head_sha, commit_author_name, commit_message, commit_pr_number (the merged PR that produced the commit, the only PR attribution a master push run has). This is where greens live; the logs are failure-only, so every "when did it turn red / green again" question must come from here, never from the logs.

Copy-ready SQL for every step is in references/investigation-queries.md.

Start wide: what's broken right now

For "what CI failures should I care about right now" (before you have a specific test in hand), the engineering-analytics-broken-tests MCP tool does the shape classification below across all live failures at once: it groups the last 2 days of failures by fingerprint and labels each breaking_master / blocking_merge_queue / novel_burst / potentially_resolved / flaky / pr_only, most urgent first, plus breaking_master_jobs (default-branch jobs whose latest run is red). Use it as the triage entry point, then drop into the per-failure workflow below to reach a culprit. It is the automated counterpart to fingerprinting by hand; the manual queries stay the way to pin a specific failure to a boundary and author.

blocking_merge_queue is the one shape the manual table below does not cover, because it looks like a single-branch failure and is not. The merge queue runs the full suite on a gate branch (trunk-merge/pr-<n>/…) carrying master, the PR, and every PR queued ahead of it with overlapping impacted targets (in practice most of the queue), so a failure there is on a commit that already passed the PR's own CI: a conflict with what landed or queued in between, or a queue-mate's own break, not that PR's bug by default. Read it as "this stopped a merge", list what the gate branch ran (query 8), and diff it against trunk rather than reading the PR alone.

The four failure shapes

Fingerprint the failure first (query 1 in the references), then read its shape — the classification falls out of three columns:

Shape	Reading	Next step
1 branch, any window	That PR's own problem	Read its failure lines; done
1 `trunk-merge/pr-<n>/…` gate branch	Queue-mate, or landed since	Query 8, then diff the gate branch against trunk
Many branches, dense burst, hits master	Trunk break (master is/was red)	Boundary query → culprit (below)
Many branches, sporadic over days/weeks	Flaky	Corroborate with `engineering-analytics-flaky-tests`

Why cross-branch means trunk: PR CI runs the PR merged with master, so one bad master commit fails every concurrently-running PR. A failure appearing on many unrelated branches in a tight window is the signature of a master-merge break, not of those PRs' code. Tell the asker explicitly when their PR is not at fault — that is usually the single most valuable sentence in the answer.

Trunk break → culprit

Run the boundary query (query 2): master-only job history for the failing job, ordered by created_at. The pattern reads directly:

... success success | failure failure ... failure | success ...
                    ^ first red = the culprit row  ^ first green = the fix row

The culprit row carries everything: head_sha, commit_author_name, commit_message (which names what changed), commit_pr_number. The first-green row identifies the fix the same way. Confidence check before naming anyone: does the culprit commit plausibly touch the failing area (its message / PR diff vs the failing test's module)? A boundary landing on an unrelated commit means sharding or timing noise — widen the window and check the adjacent commit before asserting.

Then verify the failure window in ci_failures matches (first_seen just after the culprit merged, last_seen shortly after the fix as the PR queue drained). Mismatch = you're looking at two different problems sharing a test.

Flaky → corroborate, don't guess

Read the sibling attempts before you say "flaky". One run cannot separate a flake from a deterministic failure, and ci_job_history already carries every attempt's conclusion for the branch. If every run on a branch is red, there is no flake to find, and offering a re-run wastes a run: a retry changes none of the inputs.

Sporadic shape alone is suggestive, not proof. The engineering-analytics-flaky-tests MCP tool reads per-test CI spans (rerun-pass signal — a test that failed then passed on retry in the same job) and is the stronger signal where it has coverage. Counts only, never rates: passing runs below the emitter's duration threshold aren't recorded, so there is no honest denominator.

Caveats you must carry into every answer

The logs are failure-only. No green baseline exists in ci_failures; absence of a fingerprint is weak evidence (the job may simply not have run). Greens come from ci_job_history only.
Fingerprints are pytest-only (v1). Jest / playwright / cargo failures appear in the raw failure logs but are not in ci_failures. For those, fall back to the raw failure logs via the engineering-analytics-ci-failure-logs (PR-scoped) / engineering-analytics-run-failure-logs (run-scoped) MCP tools. Carry ci_engine with run_id, or latest_ci_engine with latest_run_id, when fetching a run. A null engine means unknown historical provenance; do not join it to a known engine by integer ID.
A job that failed before its tests ran is invisible to every test-level surface. No FAILED line means no fingerprint, no span, nothing for the flaky-tests tool to read — the failure is real but the test-level answer is silence, not "fine". Setup, docker, and runner failures are only visible as job conclusions (query 7), which is also the one surface here that yields an honest rate, since it records greens.
Check runs-on before blaming a cache. Jobs on depot-* runners resolve actions/cache against Depot Cache, which the GitHub Actions cache API cannot see, so an empty result there is not evidence of eviction. Read the job's own Cache not found for input keys: line for the keys actually requested. Both steps stay green either way: actions/cache/restore succeeds on a miss, and a failed actions/cache/save is only a warning, so a green producer job may have stored nothing.
Freshness differs per source. Logs stream in near-real-time; the warehouse jobs/runs tables arrive via webhook sync and can lag. During a live incident, start from ci_failures and check the warehouse's max(created_at) before trusting a boundary (query 5). A boundary computed against a stale warehouse names the wrong commit.
A run's conclusion can be stale until the workflow_run webhook settles it (SPEC §7) — treat a very recent "failure-free" tail with suspicion.
Retries: run_attempt > 1 rows are the same job re-run. A failure that clears on attempt 2 is flake signal; one that fails through attempt 5+ is deterministic.
Reverts: a revert shows up as a new first-green (or first-red) commit whose commit_pr_number is the reverting PR — attribution follows the revert, not the original.
Time-bound every logs query. The failure-log stream is large; unbounded scans hit the read cap. 14 days covers almost every investigation.
Pair the warehouse twin too. A ci_job_history query windowed on created_at alone forces a full jobs scan — the parsed timestamp is a computed column the parquet scan can't prune on. Add a coarse created_at_raw >= '<YYYY-MM-DD>' string floor (a day below the window) alongside the precise created_at bound so the scan skips; created_at stays the exact filter.

Choosing a surface

Question	Use
"What's broken across CI right now?"	`engineering-analytics-broken-tests` MCP tool (triaged, classified)
Same, but from a terminal	`hogli ci:insights` — these endpoints, scoped to the checkout's repo
"Why did MY PR's CI fail?"	`engineering-analytics-ci-failure-logs` MCP tool (PR-scoped, grouped)
"Who broke master / when did X start?"	The two views, workflow above
"Is X flaky?"	Shape from `ci_failures` + the flaky-tests tool
"Does test X fail on other branches?"	`test` (+ `branch` / `pr_number`) on the broken-tests or flaky-tests tool
"Is this setup/infra failure common?"	Job conclusions, query 7 (no test rows exist for it)
"What's failing on master right now?"	`breaking_master` rows + `breaking_master_jobs` from the broken-tests tool
"Is CI slow / expensive / PRs stuck?"	The `diagnosing-ci-and-merge-bottlenecks` skill
"Save this as a dashboard/insight"	The `turning-engineering-analytics-into-insights` skill

Output expectations

Lead with the verdict and the exoneration/blame in plain words ("not your PR — master was broken between 08:01 and 09:58 UTC by #68727; fixed by #68855"), then the evidence: the boundary rows, the fingerprint window, occurrence/branch counts. Name the author factually (they authored the culprit commit), never accusatorially — the commit message and PR link let the reader judge the change, and half the time the "culprit" was a reasonable change with an unmocked test dependency.

Files

ATTRIBUTION.mdAdded by AutoGineer869 B
LICENSELicence file added by AutoGineer1.0 KiB
SKILL.md11.3 KiB
references/investigation-queries.md9.0 KiB

references/investigation-queries.md

Investigation queries

Copy-ready HogQL for each step of the investigation workflow. All of these run via execute-sql (or posthog:execute-sql through MCP). The two views are non-materialized — results are always current. Adjust windows to the question; 14 days covers almost every investigation.

1. Fingerprint a failure (the index query)

Start here whenever the input is a failing test name or an error string. Loose ILIKE beats an exact match — test ids get truncated and re-sharded in reports.

SELECT
    fingerprint,
    min(timestamp) AS first_seen,
    max(timestamp) AS last_seen,
    count() AS occurrences,
    uniqExact(branch) AS branches,
    countIf(branch = 'master') AS master_hits
FROM engineering_analytics_ci_failures
WHERE timestamp >= now() - INTERVAL 14 DAY
  AND test_id ILIKE '%<part of the test name>%'   -- or: error_signature ILIKE '%<error text>%'
GROUP BY fingerprint
ORDER BY last_seen DESC

Shape reading: branches = 1 → that branch's own problem. branches high + first_seen/last_seen tight + master_hits > 0 → trunk break. branches high + window spanning days/weeks with gaps → flaky.

2. Boundary query (trunk break → culprit + fix)

Master-only history for the failing job, around the failure window from query 1. The first failure row after a success run is the culprit; the first success after the red streak is the fix.

SELECT
    substring(head_sha, 1, 11) AS sha,
    conclusion,
    created_at,
    run_attempt,
    commit_author_name,
    commit_pr_number,
    substring(commit_message, 1, 100) AS message
FROM engineering_analytics_ci_job_history
WHERE head_branch = 'master'
  AND job_name = '<failing job name>'          -- e.g. 'Product tests (data-warehouse (1/2))'
  AND created_at >= <first_seen - 2h> AND created_at < <last_seen + 2h>
  AND created_at_raw >= '<window start date minus 1 day, YYYY-MM-DD>'
ORDER BY created_at ASC

The created_at_raw floor lets the warehouse scan prune — the parsed created_at filter alone hits a computed column and forces a full jobs scan. It's coarse (a whole-day, string floor a day below the window), so keep the precise created_at bounds too; the raw floor only shrinks what the scan reads.

Shard suffixes matter: job_name includes the (1/2) shard. If the test moved shards, run once per shard or match with job_name LIKE 'Product tests (data-warehouse%'.

3. Failure detail for one fingerprint

The actual error lines behind a fingerprint, newest first — for reading the traceback context and confirming two occurrences really are the same failure.

SELECT timestamp, branch, substring(head_sha, 1, 11) AS sha, ci_engine, run_id, job_name, error_signature
FROM engineering_analytics_ci_failures
WHERE timestamp >= now() - INTERVAL 14 DAY
  AND fingerprint = '<fingerprint from query 1>'
ORDER BY timestamp DESC
LIMIT 50

Join runs on (ci_engine, run_id). Only github_actions IDs link to https://github.com/<owner>/<repo>/actions/runs/<run_id>. Historical logs with a null engine have unknown provenance; do not infer their engine or use them to prove retry recovery.

4. What's new on master (novelty scan)

Fingerprints first seen recently that have hit master — the "did anything just break" sweep.

SELECT fingerprint, min(timestamp) AS first_seen, count() AS occurrences, uniqExact(branch) AS branches
FROM engineering_analytics_ci_failures
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY fingerprint
HAVING min(timestamp) >= now() - INTERVAL 1 DAY AND countIf(branch = 'master') > 0
ORDER BY occurrences DESC

5. Warehouse freshness check

Run before trusting a boundary during a live incident — a stale warehouse names the wrong commit. Logs stream near-real-time; the jobs table arrives via webhook sync.

SELECT max(created_at) AS newest_job_row, now() - max(created_at) AS lag
FROM engineering_analytics_ci_job_history

If lag exceeds ~15 minutes, do the classification from ci_failures now and defer the culprit-naming boundary query until the warehouse catches up (or corroborate the boundary commit against git log before naming it).

6. Deterministic or retry-passing?

Track the investigated fingerprint itself across retry attempts, not just the job's conclusion — a different failure can keep the job red after this fingerprint cleared. Only runs where the fingerprint ever appeared are considered.

SELECT
    h.ci_engine,
    h.run_id,
    h.run_attempt,
    h.conclusion AS job_conclusion,
    f.run_id != 0 AS fingerprint_present
FROM engineering_analytics_ci_job_history AS h
LEFT JOIN (
    SELECT DISTINCT ci_engine, run_id, run_attempt
    FROM engineering_analytics_ci_failures
    WHERE timestamp >= now() - INTERVAL 7 DAY AND fingerprint = '<fingerprint from query 1>'
) AS f ON h.ci_engine = f.ci_engine AND h.run_id = f.run_id AND h.run_attempt = f.run_attempt
WHERE h.job_name = '<failing job name>'
  AND h.created_at >= now() - INTERVAL 7 DAY
  AND h.created_at_raw >= '<8 days ago, YYYY-MM-DD>'
  AND (h.ci_engine, h.run_id) IN (
    SELECT DISTINCT ci_engine, run_id FROM engineering_analytics_ci_failures
    WHERE timestamp >= now() - INTERVAL 7 DAY AND fingerprint = '<fingerprint from query 1>'
  )
ORDER BY h.ci_engine, h.run_id, h.run_attempt

Reading: fingerprint_present dropping on a later attempt while the job goes green = retry-passed (flake signal). Fingerprint present through the last attempt = deterministic. Fingerprint absent but the job still red = a different failure holds the job red — don't attribute it to this one.

The created_at_raw floor lets the warehouse scan prune — the parsed created_at filter alone hits a computed column and forces a full jobs scan. It's coarse (a whole-day, string floor a day below the 7-day window), so keep the precise created_at bound too.

7. Job failure rate (for failures with no test rows)

A job that failed before its tests ran (docker setup, a runner port collision, a dependency install) writes no FAILED line, so ci_failures and the span-derived tools are blind to it. Job conclusions are the only place it shows up, and they record greens too, so this is the one rate here with an honest denominator.

SELECT
    countIf(conclusion = 'success') AS ok,
    countIf(conclusion IN ('failure', 'timed_out', 'startup_failure', 'stale')) AS fail,
    round(100.0 * fail / nullIf(ok + fail, 0), 2) AS fail_pct
FROM engineering_analytics_ci_job_history
WHERE repo_name = '<repo>'
  AND workflow_name = '<failing workflow name>'
  AND job_name = '<failing job name>'
  AND created_at >= now() - INTERVAL 7 DAY
  AND created_at_raw >= '<8 days ago, YYYY-MM-DD>'

Scope by repository and workflow as well as job: the view unions every connected repository, and job names repeat across workflows (Desktop Tests Pass fails under two), so a job-only filter pools unrelated attempts into the denominator.

failure, timed_out, startup_failure, and stale are decisive failures, the same set every other workflow-run rate in this product uses. cancelled, skipped, neutral, and action_required are absent from ok + fail on purpose: none reached a pass-or-fail verdict, and they are common enough here (superseded pushes, path filters) to halve the rate if counted.

A percentage alone can't tell an old flake from a live outage. Break the same window down by hour:

SELECT
    toStartOfHour(created_at) AS hour,
    countIf(conclusion = 'success') AS ok,
    countIf(conclusion IN ('failure', 'timed_out', 'startup_failure', 'stale')) AS fail
FROM engineering_analytics_ci_job_history
WHERE repo_name = '<repo>'
  AND workflow_name = '<failing workflow name>'
  AND job_name = '<failing job name>'
  AND created_at >= now() - INTERVAL 12 HOUR
  AND created_at_raw >= '<yesterday, YYYY-MM-DD>'
GROUP BY hour
ORDER BY hour DESC

Reading: a low percentage with recent hours mostly green = transient, so recommend a retry rather than a code change. Recent hours entirely red = an outage; stop advising retries. Steady across days = a standing defect that looks like noise one run at a time.

Both queries keep the created_at_raw string floor for the same pruning reason as query 6.

8. What the merge queue ran for a PR

The trunk-merge/pr-<n>/<uuid> branch is ephemeral, but the jobs it ran stay in ci_job_history under that head_branch. One row per job attempt, failures first:

SELECT head_branch, ci_engine, run_id, workflow_name, job_name, conclusion, created_at
FROM engineering_analytics_ci_job_history
WHERE startsWith(head_branch, 'trunk-merge/pr-<n>/')
  AND created_at >= now() - INTERVAL 7 DAY
  AND created_at_raw >= '<8 days ago, YYYY-MM-DD>'
ORDER BY conclusion IN ('failure', 'timed_out', 'startup_failure', 'stale') DESC, created_at DESC

Each <uuid> is one queue attempt; a PR kicked twice has two. The failing job_name feeds query 7. The PRs the branch carried are readable off its head: gh api repos/<owner>/<repo>/compare/master...<head_sha> lists merge commits titled Merging <sha> into trunk-temp/pr-<m>/…, one <m> per queue-mate.

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Published by

PostHog

Publisher

PostHog (Official)

Source

PostHog/skills · skills/omnibus/investigating-ci-failures · commit a5d30e7

Licence

MIT License (redistribution allowed)

LICENSE (added by AutoGineer from LICENSE)
ATTRIBUTION.md (written by AutoGineer)

Synced

2026-11-09

AutoGineer is not affiliated with or endorsed by PostHog or PostHog.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download investigating-ci-failures.zip