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.9655172413793104	29

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
None	0

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_uniqueness	8.1375382500000000	4
security_scan	6.5410740000000000	4
sanitize	5.8503440000000000	4
deploy	5.5830425000000000	4
verify	5.5021347500000000	4
test_profanity	5.2193100000000000	5
build	3.3966315000000000	4

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.9655172413793104	0.9655172413793104

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
teal	1
emerald	1
amber	1
crimson	1

View SQL

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