AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

investigating-ci-failures

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).

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read posthog--investigating-ci-failures at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 2

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.