A Release Command Center in Claude Dashboards: SLOs, Burn Rates and DORA

# 1. Log every request on the backend service (repeat per service)
gcloud compute backend-services update api \
  --enable-logging --logging-sample-rate=1.0 --global

# 2. Route load balancer logs to the ops dataset
gcloud logging sinks create lb-to-bq \
  bigquery.googleapis.com/projects/acme/datasets/ops \
  --log-filter='resource.type="http_load_balancer"' \
  --use-partitioned-tables

CREATE TABLE ops.deploys (
  service STRING,
  sha STRING,
  status STRING,          -- success | failure | cancelled
  committed_at TIMESTAMP,
  deployed_at TIMESTAMP
)
PARTITION BY DATE(deployed_at);

CREATE TABLE ops.incidents (
  service STRING,
  sha STRING,             -- the deploy that caused it
  fix_sha STRING,         -- the deploy that fixed it (hotfix or rollback)
  opened_at TIMESTAMP,
  resolved_at TIMESTAMP
)
PARTITION BY DATE(opened_at);

name: deploy
on:
  push:
    branches: [main]

permissions:
  contents: read
  id-token: write

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: google-github-actions/auth@v2
        with:
          workload_identity_provider: ${{ secrets.WIF_PROVIDER }}
          service_account: ${{ secrets.DEPLOY_SA }}
      - uses: google-github-actions/setup-gcloud@v2
      - run: ./deploy.sh
      - name: Record deploy
        if: always()
        run: |
          bq query --nouse_legacy_sql "INSERT ops.deploys
            (service, sha, status, committed_at, deployed_at)
            VALUES ('api', '${{ github.sha }}', '${{ job.status }}',
            TIMESTAMP('$(git log -1 --format=%cI)'), CURRENT_TIMESTAMP())"

Build a live release command center from the ops dataset in BigQuery for the
services api, web and worker.

1. SLO cards: SLO 99.9% for api and worker, 99.5% for web, over 30 days.
   Show error budget left, and the burn rate over the last 1 hour and 6 hours.
   A 5xx response counts as an error. Mark PAGE when the 1h burn rate is
   at least 14.4 or the 6h burn rate is at least 6.
2. A chart of the 5xx rate in 5-minute buckets over the last 6 hours, per
   service, with every deploy from ops.deploys as a vertical marker.
3. Suspect deploys: for every deploy in the last 6 hours, the 5xx rate in the
   15 minutes after it divided by the rate in the hour before, highest first.
4. A strip with the five DORA metrics over 90 days, as defined on dora.dev.

Aggregate in SQL so each dataset stays small, and show me the query behind
every number.

SELECT
  1 - SAFE_DIVIDE(COUNTIF(httpRequest.status >= 500), COUNT(*)) / (1 - 0.999)
    AS budget_left
FROM ops.requests
WHERE resource.labels.backend_service_name = 'api'
  AND timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

WITH m AS (
  SELECT
    httpRequest.status >= 500 AS err,
    timestamp > TIMESTAMP_SUB(
      CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AS last_1h
  FROM ops.requests
  WHERE resource.labels.backend_service_name = 'api'
    AND timestamp > TIMESTAMP_SUB(
      CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
)
SELECT
  COUNTIF(err AND last_1h) / COUNTIF(last_1h)
    / (1 - 0.999) AS burn_1h,
  COUNTIF(err) / COUNT(*) / (1 - 0.999) AS burn_6h
FROM m;

SELECT
  TIMESTAMP_SECONDS(DIV(UNIX_SECONDS(timestamp), 300) * 300) AS bucket,
  COUNTIF(httpRequest.status >= 500) / COUNT(*) AS error_rate
FROM ops.requests
WHERE resource.labels.backend_service_name = 'api'
  AND timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
GROUP BY bucket
ORDER BY bucket;

WITH d AS (
  SELECT service, sha, deployed_at
  FROM ops.deploys
  WHERE status = 'success'
    AND deployed_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
),
r AS (
  SELECT
    resource.labels.backend_service_name AS service,
    timestamp,
    httpRequest.status >= 500 AS err
  FROM ops.requests
  WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 HOUR)
),
w AS (
  SELECT d.service, d.sha, d.deployed_at,
    SAFE_DIVIDE(COUNTIF(r.err AND r.timestamp >= d.deployed_at),
                COUNTIF(r.timestamp >= d.deployed_at)) AS err_after,
    SAFE_DIVIDE(COUNTIF(r.err AND r.timestamp < d.deployed_at),
                COUNTIF(r.timestamp < d.deployed_at)) AS err_before
  FROM d
  JOIN r
    ON r.service = d.service
   AND r.timestamp BETWEEN TIMESTAMP_SUB(d.deployed_at, INTERVAL 60 MINUTE)
                       AND TIMESTAMP_ADD(d.deployed_at, INTERVAL 15 MINUTE)
  GROUP BY d.service, d.sha, d.deployed_at
)
SELECT *, SAFE_DIVIDE(err_after, err_before) AS ratio
FROM w
ORDER BY ratio DESC;

-- Deployment frequency per week
SELECT service, DATE_TRUNC(DATE(deployed_at), WEEK) AS week, COUNT(*) AS deploys
FROM ops.deploys
WHERE status = 'success'
  AND deployed_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY service, week;

-- Median change lead time (hours)
SELECT service,
  APPROX_QUANTILES(TIMESTAMP_DIFF(deployed_at, committed_at, MINUTE), 100)[OFFSET(50)] / 60
    AS median_lead_time_h
FROM ops.deploys
WHERE status = 'success'
  AND deployed_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY service;

-- Change fail rate
SELECT service, COUNTIF(i.sha IS NOT NULL) / COUNT(*) AS change_fail_rate
FROM ops.deploys AS d
LEFT JOIN (SELECT DISTINCT sha FROM ops.incidents) AS i USING (sha)
WHERE d.status = 'success'
  AND d.deployed_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY service;

-- Median failed deployment recovery time (minutes)
SELECT service,
  APPROX_QUANTILES(TIMESTAMP_DIFF(resolved_at, opened_at, MINUTE), 100)[OFFSET(50)]
    AS median_recovery_min
FROM ops.incidents
WHERE resolved_at IS NOT NULL
  AND opened_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY service;

-- Deployment rework rate
SELECT d.service, COUNTIF(f.fix_sha IS NOT NULL) / COUNT(*) AS rework_rate
FROM ops.deploys AS d
LEFT JOIN (SELECT DISTINCT fix_sha FROM ops.incidents) AS f ON d.sha = f.fix_sha
WHERE d.status = 'success'
  AND d.deployed_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY d.service;