Pipeline Analytics

Real SQL against the pipeline_runs table: joins, GROUP BY, and window functions, run live rather than looped over in the ORM. It needs a Postgres connection, since DATE_TRUNC and the window frames below are Postgres dialect. The queries live in app/services/analytics.py.

Success Rate Over Time

day	pass_rate	total_runs
2026-08-23	0.9333333333333333	45
2026-08-27	1.0	7
2026-08-29	1.0	7
2026-09-05	1.0	7

View SQL

SELECT
        DATE_TRUNC('day', started_at)::date AS day,
        COUNT(*) FILTER (WHERE status = 'pass')::float / COUNT(*) AS pass_rate,
        COUNT(*) AS total_runs
    FROM pipeline_runs
    GROUP BY 1
    ORDER BY 1

Mean Time Between Failures

mean_seconds_between_failures	failure_count
18952.873960500000	2

View SQL

WITH failures AS (
        SELECT
            started_at,
            started_at - LAG(started_at) OVER (ORDER BY started_at) AS gap
        FROM pipeline_runs
        WHERE status = 'fail'
    )
    SELECT
        AVG(EXTRACT(EPOCH FROM gap)) AS mean_seconds_between_failures,
        COUNT(*) AS failure_count
    FROM failures
    WHERE gap IS NOT NULL

Slowest Stage

stage	avg_duration_seconds	run_count
test_profanity	5.3198507000000000	10
sanitize	4.9280533636363636	11
test_uniqueness	4.7445764000000000	10
security_scan	4.1457449090909091	11
deploy	3.9251925000000000	8
build	3.7147818750000000	8
verify	3.5214366250000000	8

View SQL

SELECT
        stage,
        AVG(EXTRACT(EPOCH FROM (ended_at - started_at))) AS avg_duration_seconds,
        COUNT(*) AS run_count
    FROM pipeline_runs
    WHERE ended_at IS NOT NULL
    GROUP BY stage
    ORDER BY avg_duration_seconds DESC

Rolling 7-Day Pass Rate

day	pass_rate	rolling_7day_pass_rate
2026-08-23	0.9333333333333333	0.9333333333333333
2026-08-27	1.0	0.9666666666666667
2026-08-29	1.0	0.9777777777777779
2026-09-05	1.0	0.9833333333333334

View SQL

WITH daily AS (
        SELECT
            DATE_TRUNC('day', started_at)::date AS day,
            COUNT(*) FILTER (WHERE status = 'pass')::float / COUNT(*) AS pass_rate
        FROM pipeline_runs
        GROUP BY 1
    )
    SELECT
        day,
        pass_rate,
        AVG(pass_rate) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS rolling_7day_pass_rate
    FROM daily
    ORDER BY day

Appearance Duplication Counts

appearance_id	character_count
emerald	3
amber	3
crimson	2
teal	2
indigo	1

View SQL

SELECT
        appearance_id,
        COUNT(*) AS character_count
    FROM characters
    GROUP BY appearance_id
    ORDER BY character_count DESC