Operational Reliability Reports

Service Window Reliability

Gate Failed Checks

A reliability report can count failed checks and label each service against a selected failure budget.

Program

Play the script to choose the allowed failure count and inspect the service window status rows.

service_window_reliability_report.sql 
CREATE TABLE service_checks (service TEXT, check_name TEXT, failed INTEGER);
INSERT INTO service_checks VALUES ('api', 'smoke', 0), ('api', 'schema', 1), ('web', 'smoke', 0), ('web', 'cdn', 0), ('billing', 'ledger', 1), ('billing', 'queue', 1);
WITH params(allowed_failures) AS (VALUES ()), failures AS (SELECT service, SUM(failed) AS failed_checks FROM service_checks GROUP BY service) SELECT service, failed_checks, CASE WHEN failed_checks <= (SELECT allowed_failures FROM params) THEN 'ready' ELSE 'blocked' END AS window_status FROM failures ORDER BY service;

CREATE TABLE service_checks (service TEXT, check_name TEXT, failed INTEGER);
INSERT INTO service_checks VALUES ('api', 'smoke', 0), ('api', 'schema', 1), ('web', 'smoke', 0), ('web', 'cdn', 0), ('billing', 'ledger', 1), ('billing', 'queue', 1);
WITH params(allowed_failures) AS (VALUES ()), failures AS (SELECT service, SUM(failed) AS failed_checks FROM service_checks GROUP BY service) SELECT service, failed_checks, CASE WHEN failed_checks <= (SELECT allowed_failures FROM params) THEN 'ready' ELSE 'blocked' END AS window_status FROM failures ORDER BY service;

CREATE TABLE service_checks (service TEXT, check_name TEXT, failed INTEGER);
INSERT INTO service_checks VALUES ('api', 'smoke', 0), ('api', 'schema', 1), ('web', 'smoke', 0), ('web', 'cdn', 0), ('billing', 'ledger', 1), ('billing', 'queue', 1);
WITH params(allowed_failures) AS (VALUES ()), failures AS (SELECT service, SUM(failed) AS failed_checks FROM service_checks GROUP BY service) SELECT service, failed_checks, CASE WHEN failed_checks <= (SELECT allowed_failures FROM params) THEN 'ready' ELSE 'blocked' END AS window_status FROM failures ORDER BY service;
failure budget `allowed_failures` is the selected policy for the report.
service aggregate The grouped CTE counts failed checks for each service.
status label The CASE expression turns the count into a ready or blocked status.