Queries

SELECT table_name, column_name, data_type
FROM system.information_schema.columns
WHERE table_name IN ('system.tasks', 'system.task_runs')
ORDER BY table_name, column_name

SELECT
    t.origin_product                                             AS origin,
    count()                                                      AS runs,
    uniq(r.task_id)                                              AS tasks,
    uniq(t.created_by_id)                                        AS creators,
    uniq(t.repository)                                           AS repos,
    countIf(r.status = 'failed')                                 AS failed,
    countIf(isNotNull(r.error_message))                          AS with_error_msg,
    countIf(isNotNull(r.branch))                                 AS with_branch
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE r.created_at > now() - interval 14 day
  AND t.origin_product NOT IN ('signals_scout', 'scout_suggestions')
GROUP BY origin
ORDER BY runs DESC

SELECT
    t.origin_product                                             AS origin,
    count()                                                      AS runs,
    countIf(r.status = 'failed')                                 AS failed,
    round(100.0 * countIf(r.status = 'failed') / count(), 1)     AS fail_pct,
    round(100.0 * countIf(r.status = 'cancelled') / count(), 1)  AS cancel_pct,
    countIf(r.status IN ('not_started', 'queued', 'in_progress')) AS in_flight,
    uniq(t.created_by_id)                                        AS users
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE r.created_at > now() - interval 14 day
  AND t.origin_product NOT IN ('signals_scout', 'scout_suggestions')
GROUP BY origin
ORDER BY runs DESC

SELECT
    cityHash64(t.repository)                                     AS repo_fingerprint,
    t.repository                                                 AS repo,
    count()                                                      AS runs,
    uniq(r.task_id)                                              AS tasks,
    countIf(r.status = 'failed')                                 AS failed,
    uniqIf(r.task_id, r.status = 'failed')                       AS failed_tasks,
    round(countIf(r.status = 'failed')
          / nullIf(uniqIf(r.task_id, r.status = 'failed'), 0), 1) AS failed_runs_per_task,
    round(100.0 * countIf(r.status = 'failed') / count(), 1)     AS fail_pct,
    -- Reach must be the creators who were *affected*. A plain uniq() mixes in everyone whose
    -- runs succeeded, dressing a one-person failure up as broad reach.
    uniqIf(t.created_by_id, r.status = 'failed')                 AS affected_users
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE r.created_at > now() - interval 14 day
  AND t.origin_product NOT IN ('signals_scout', 'scout_suggestions')
  -- Repo-less tasks are unrelated work that would group into one synthetic "repository";
  -- that cluster can trip the total-failure exception with no shared repo behind it.
  -- Query 3 owns cross-task failure classes that have no repository.
  AND isNotNull(t.repository) AND t.repository != ''
GROUP BY repo_fingerprint, repo
-- The floor applies to *partial* failure rates. A repo where every run fails is a
-- readiness break the body says to file at any volume, so it must survive the floor —
-- but only with independent spread. `failed_tasks >= 2` is what stops one person
-- retrying a single task against a fresh repo from minting a team-visible report.
HAVING runs > 20 OR (failed = runs AND runs >= 3 AND failed_tasks >= 2)
-- Order by the discriminator, not raw count. `HAVING` is the volume guard, so everything
-- here already clears the floor; ranking by count would let a high-traffic repo with a
-- healthy 1% rate push a small repo at 100% past the LIMIT — the exact inversion the body
-- calls noise. Rate first, count only as tie-breaker.
ORDER BY fail_pct DESC, failed DESC
LIMIT 25

SELECT
    cityHash64(substring(r.error_message, 1, 60))                AS err_fingerprint,
    substring(r.error_message, 1, 60)                            AS err_prefix,
    count()                                                      AS runs,
    uniq(r.task_id)                                              AS tasks,
    round(count() / uniq(r.task_id), 1)                          AS failed_runs_per_task,
    uniq(t.repository)                                           AS repos,
    uniq(t.created_by_id)                                        AS users
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE r.created_at > now() - interval 14 day
  AND t.origin_product NOT IN ('signals_scout', 'scout_suggestions')
  AND r.status = 'failed'
  AND isNotNull(r.error_message)
  -- Project-wide by default. When query 2 named a candidate repository, re-run this
  -- scoped to it — a repo's own worst class is often outside the global top 20, and
  -- without this the query 2 -> query 3 -> query 4 chain stalls with nothing to localize:
  -- AND cityHash64(t.repository) = 0000000000000000000  -- repo_fingerprint from query 2
GROUP BY err_fingerprint, err_prefix
-- Rank by how many distinct tasks a class touches, not raw runs: one task retried 200 times
-- would otherwise fill the page and push out a class that hit 30 tasks once each — the
-- systemic shape this lens exists to find.
ORDER BY tasks DESC, runs DESC
LIMIT 20

SELECT
    t.repository                                                 AS repo,
    substring(r.error_message, 1, 60)                            AS err_prefix,
    count()                                                      AS runs,
    uniq(r.task_id)                                              AS tasks
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE r.created_at > now() - interval 14 day
  AND t.origin_product NOT IN ('signals_scout', 'scout_suggestions')
  AND r.status = 'failed'
  AND isNotNull(r.error_message)
  -- Substitute the integer from query 3's err_fingerprint column (digits only):
  AND cityHash64(substring(r.error_message, 1, 60)) = 0000000000000000000
GROUP BY repo, err_prefix
HAVING runs > 0
ORDER BY runs DESC
LIMIT 30

SELECT
    t.repository                                                 AS repo,
    count()                                                      AS runs,
    countIf(r.status = 'cancelled')                              AS cancelled,
    round(100.0 * countIf(r.status = 'cancelled') / count(), 1)  AS cancel_pct,
    countIf(r.status IN ('not_started', 'queued', 'in_progress')) AS in_flight
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE r.created_at > now() - interval 14 day
  AND t.origin_product NOT IN ('signals_scout', 'scout_suggestions')
GROUP BY repo
HAVING runs > 20
-- Rate first: the guard above already handles volume, and a busy healthy repo would
-- otherwise displace the low-volume repo whose cancellation rate actually spiked.
ORDER BY cancel_pct DESC, cancelled DESC
LIMIT 20

SELECT
    t.repository                                                 AS repo,
    r.status                                                     AS status,
    count()                                                      AS stuck_runs,
    uniq(r.task_id)                                              AS stuck_tasks,
    round(count() / nullIf(uniq(r.task_id), 0), 1)               AS runs_per_stuck_task,
    min(r.created_at)                                            AS oldest,
    max(r.created_at)                                            AS newest
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE r.status IN ('not_started', 'queued', 'in_progress')
  AND r.created_at < now() - interval 1 day
  AND t.origin_product NOT IN ('signals_scout', 'scout_suggestions')
  -- `Task.soft_delete()` does not transition its runs, so without this a deleted task's
  -- stuck run stays "backlog" forever. This scan is unbounded, so that false finding never ages out.
  AND t.deleted = 0
  -- A local (Desktop) run can sit `queued` by design while the local agent drives it, so the
  -- cloud staleness rule doesn't apply to it. Restrict to cloud rather than reporting normal
  -- long-lived local sessions as silent non-completion.
  AND r.environment = 'cloud'
GROUP BY repo, status
-- Oldest first: the point of an unbounded scan is the run stuck for months, which ranking by
-- count would bury under several repos holding a few two-day-old runs.
ORDER BY oldest ASC, stuck_runs DESC
LIMIT 20

SELECT
    origin_product                                               AS origin,
    repository                                                   AS repo,
    count()                                                      AS tasks,
    uniq(created_by_id)                                          AS requesters,
    round(avg(length(description)))                              AS avg_desc_len
FROM system.tasks
WHERE created_at > now() - interval 30 day
  AND deleted = 0
  AND origin_product IN ('user_created', 'slack', 'posthog_ai', 'hogdesk')
GROUP BY origin, repo
-- Requester spread first: the demand lens requires repetition across people, so one person's
-- high-volume queue must not displace groups where several people asked for the same thing.
ORDER BY requesters DESC, tasks DESC
LIMIT 30

SELECT
    t.id                                                         AS task_id,
    t.repository                                                 AS repo,
    t.created_by_id                                              AS creator,
    substring(r.error_message, 1, 60)                            AS err_prefix,
    count()                                                      AS failed_runs
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE r.created_at > now() - interval 30 day
  AND r.status = 'failed'
  -- Lens B anchors on task creation, so bound the task too. Without this an old or
  -- soft-deleted request retried inside the window reads as current demand.
  AND t.created_at > now() - interval 30 day
  AND t.deleted = 0
  AND t.origin_product IN ('user_created', 'slack', 'posthog_ai', 'hogdesk')
GROUP BY task_id, repo, creator, err_prefix
ORDER BY failed_runs DESC
LIMIT 30

SELECT
    r.id                                                         AS run_id,
    r.task_id                                                    AS task_id,
    t.repository                                                 AS repo,
    r.status                                                     AS status,
    substring(r.error_message, 1, 120)                           AS error_message,
    r.created_at                                                 AS run_created_at
FROM system.task_runs AS r
JOIN system.tasks AS t ON r.task_id = t.id
WHERE t.origin_product NOT IN ('signals_scout', 'scout_suggestions')
  AND t.deleted = 0
  -- Failure clusters: keep both lines as written.
  -- Backlog clusters (query 5b): swap the status list for
  -- ('not_started', 'queued', 'in_progress') AND replace the recent-run bound with
  -- `r.created_at < now() - interval 1 day`, then flip the ORDER BY to `r.created_at ASC`,
  -- and add `AND r.environment = 'cloud'` to match 5b's scope.
  -- Dropping the age predicate entirely would return the *newest* active runs, and dropping
  -- the environment one would surface a local run 5b never counted — either lets the report
  -- cite a run that was never part of the backlog it claims to evidence.
  AND r.status = 'failed'
  AND r.created_at > now() - interval 14 day
  -- Narrow to the cluster you are filing, e.g.:
  -- AND cityHash64(t.repository) = 0000000000000000000  -- repo_fingerprint from query 2
  -- AND cityHash64(substring(r.error_message, 1, 60)) = 0000000000000000000  -- err_fingerprint from query 3
ORDER BY r.created_at DESC
LIMIT 5