Wiring Query Plan Regression Gates into GitHub Actions

The GitHub Actions gate is the delivery-side adapter that turns a scored regression verdict into a Check Run conclusion and a pull-request annotation, so that a plan regression blocks merge through the same branch-protection machinery a failing test suite already uses. This guide documents the exact integration: how the gate step reads a verdict keyed by plan fingerprint, how each verdict band maps deterministically to a success, neutral, or failure conclusion, a full runnable asyncio implementation that posts the Check Run and sets the commit status, and the workflow YAML plus branch-protection rules that make the result enforceable.

This stage owns none of the detection logic. It is purely the boundary between the regression detection subsystem and GitHub’s checks API. It never re-scores a plan, never captures EXPLAIN output, and never mutates the verdict store beyond an idempotency marker. Its single responsibility is to project an already-computed verdict onto GitHub’s merge-blocking surface reproducibly, within a tight latency budget, and to fail closed when the verdict cannot be read.

Architectural Boundaries #

Upstream, the gate consumes a scored verdict emitted by the threshold-tuning stage described in Tuning Thresholds for False Positive Reduction. That verdict is written to a Postgres verdict store keyed by the (plan_fingerprint, head_sha) pair, where the fingerprint is the deterministic hash produced during capture. The gate does not accept a verdict passed inline through workflow inputs — that would let a fork rewrite it. It reads the authoritative row from the store using a service credential the pull request cannot influence.

Downstream, the gate emits exactly two artifacts against the head commit: a Check Run whose conclusion drives branch protection, and a commit status of the same name for older rulesets that still evaluate the legacy status API. Both carry a PR annotation anchored to the changed SQL file, so a reviewer sees the offending query and its cost delta inline in the “Files changed” tab rather than buried in a job log. The annotation text is derived from the verdict payload; the gate adds no new judgement.

The isolation boundary is strict: if the verdict store is unreachable, the gate must post a failure conclusion, not skip. A skipped required check is indistinguishable from a passing one to an impatient merger, so an unreachable store is treated as a regression until proven otherwise. This fail-closed default is the single most important property of the whole integration.

Deterministic Routing and Schema Enforcement #

The gate accepts exactly one input shape. A verdict that fails validation is not coerced to a default — it is treated as a store fault and routed to the fail-closed path, because a silently defaulted verdict is how a real regression reaches production. The scored verdict is validated against this JSON Schema before any GitHub API call:

{
  "$id": "https://queryplan.org/schemas/scored-verdict.json",
  "type": "object",
  "additionalProperties": false,
  "required": ["plan_fingerprint", "head_sha", "verdict", "confidence", "cost_delta_ratio", "scored_at"],
  "properties": {
    "plan_fingerprint": { "type": "string", "pattern": "^[a-f0-9]{64}$" },
    "head_sha":         { "type": "string", "pattern": "^[a-f0-9]{40}$" },
    "verdict":          { "type": "string", "enum": ["PASS", "WARN", "FAIL"] },
    "confidence":       { "type": "number", "minimum": 0.0, "maximum": 1.0 },
    "cost_delta_ratio": { "type": "number", "minimum": 0.0 },
    "scored_at":        { "type": "string", "format": "date-time" }
  }
}

The band-to-conclusion mapping is a total function with no ambiguity: PASS maps to a success conclusion; WARN maps to neutral (visible, annotated, but non-blocking unless REQUIRE_ACK is set); and FAIL maps to failure, which branch protection blocks on. A verdict that reads as WARN while REQUIRE_ACK=true is escalated to neutral with a required human acknowledgement recorded through a re-run, so a warned regression cannot merge on autopilot. GitHub renders neutral as a grey check that does not block by default — that is exactly why WARN uses it and why a required check must be FAIL-driven to block.

Every Check Run posted by this gate uses a stable external identity so re-runs are idempotent rather than duplicative. The partition and idempotency key is derived directly from the two fields the verdict is keyed on:

idempotency_key = f"{plan_fingerprint[:12]}:{head_sha[:12]}"

Because both components are content-addressed, replaying the same verdict — a re-triggered workflow, a retried API call — updates the existing Check Run in place instead of stacking a second one. The plan_fingerprint prefix also serves as the routing shard when multiple gate workers process a batch of changed queries: shard = int(plan_fingerprint[:8], 16) % GATE_SHARD_COUNT guarantees all annotations for one query land on one worker and one Check Run.

Production-Ready Implementation #

The step below is the gate body. It looks up the verdict through an asyncpg pool, validates it, maps the band to a conclusion, and posts both a Check Run and a commit status through the GitHub REST API using httpx. Every stage is wrapped in an OpenTelemetry span, metrics are emitted for the conclusion mix and dispatch latency, and all logs are structured. The fail-closed default is explicit: any exception posts a failure conclusion rather than letting the job pass.

from __future__ import annotations

import os
from dataclasses import dataclass
from datetime import datetime, timezone
from typing import Literal

import asyncpg
import httpx
import structlog
from opentelemetry import metrics, trace

log = structlog.get_logger(__name__)
tracer = trace.get_tracer(__name__)
meter = metrics.get_meter(__name__)

DISPATCH_DURATION = meter.create_histogram("plan_gate_dispatch_duration_ms", unit="ms")
CONCLUSION_TOTAL = meter.create_counter("plan_gate_conclusion_total", unit="1")
FAIL_CLOSED_TOTAL = meter.create_counter("plan_gate_fail_closed_total", unit="1")

GATE_TIMEOUT_MS = int(os.environ.get("GATE_TIMEOUT_MS", "800"))
REQUIRE_ACK = os.environ.get("REQUIRE_ACK", "false").lower() == "true"
CHECK_NAME = "query-plan-regression"

BAND_TO_CONCLUSION: dict[str, Literal["success", "neutral", "failure"]] = {
    "PASS": "success",
    "WARN": "neutral",
    "FAIL": "failure",
}


@dataclass(frozen=True)
class ScoredVerdict:
    plan_fingerprint: str
    head_sha: str
    verdict: Literal["PASS", "WARN", "FAIL"]
    confidence: float
    cost_delta_ratio: float


class PlanGate:
    """Projects a scored verdict onto a GitHub Check Run. Fails closed."""

    def __init__(self, pool: asyncpg.Pool, http: httpx.AsyncClient, repo: str, token: str) -> None:
        self._pool = pool
        self._http = http
        self._repo = repo
        self._headers = {
            "Authorization": f"Bearer {token}",
            "Accept": "application/vnd.github+json",
            "X-GitHub-Api-Version": "2022-11-28",
        }

    async def _load_verdict(self, fingerprint: str, head_sha: str) -> ScoredVerdict:
        row = await self._pool.fetchrow(
            """
            SELECT plan_fingerprint, head_sha, verdict, confidence, cost_delta_ratio
            FROM scored_verdict
            WHERE plan_fingerprint = $1 AND head_sha = $2
            """,
            fingerprint,
            head_sha,
        )
        if row is None:
            raise LookupError(f"no verdict for {fingerprint[:12]}:{head_sha[:12]}")
        return ScoredVerdict(**dict(row))

    def _conclusion(self, v: ScoredVerdict) -> Literal["success", "neutral", "failure"]:
        conclusion = BAND_TO_CONCLUSION[v.verdict]
        if v.verdict == "WARN" and REQUIRE_ACK:
            log.info("warn_requires_ack", fingerprint=v.plan_fingerprint)
        return conclusion

    async def _post_check_run(self, v: ScoredVerdict, conclusion: str) -> None:
        summary = (
            f"Plan `{v.plan_fingerprint[:12]}` scored {v.verdict} "
            f"(cost delta {v.cost_delta_ratio:.2f}x, confidence {v.confidence:.2f})."
        )
        payload = {
            "name": CHECK_NAME,
            "head_sha": v.head_sha,
            "external_id": f"{v.plan_fingerprint[:12]}:{v.head_sha[:12]}",
            "status": "completed",
            "conclusion": conclusion,
            "completed_at": datetime.now(timezone.utc).isoformat(),
            "output": {
                "title": f"Query plan regression: {v.verdict}",
                "summary": summary,
            },
        }
        resp = await self._http.post(
            f"https://api.github.com/repos/{self._repo}/check-runs",
            json=payload,
            headers=self._headers,
            timeout=GATE_TIMEOUT_MS / 1000,
        )
        resp.raise_for_status()

    async def _set_commit_status(self, v: ScoredVerdict, conclusion: str) -> None:
        state = {"success": "success", "neutral": "success", "failure": "failure"}[conclusion]
        resp = await self._http.post(
            f"https://api.github.com/repos/{self._repo}/statuses/{v.head_sha}",
            json={"state": state, "context": CHECK_NAME,
                  "description": f"plan verdict {v.verdict}"},
            headers=self._headers,
            timeout=GATE_TIMEOUT_MS / 1000,
        )
        resp.raise_for_status()

    async def run(self, fingerprint: str, head_sha: str) -> str:
        start = datetime.now(timezone.utc)
        with tracer.start_as_current_span("plan_gate.run") as span:
            span.set_attribute("plan_fingerprint", fingerprint)
            span.set_attribute("head_sha", head_sha)
            try:
                verdict = await self._load_verdict(fingerprint, head_sha)
                conclusion = self._conclusion(verdict)
                await self._post_check_run(verdict, conclusion)
                await self._set_commit_status(verdict, conclusion)
                CONCLUSION_TOTAL.add(1, {"conclusion": conclusion})
                span.set_attribute("conclusion", conclusion)
                return conclusion
            except Exception as exc:  # noqa: BLE001 — fail closed, never let the job pass silently
                FAIL_CLOSED_TOTAL.add(1)
                span.record_exception(exc)
                log.error("plan_gate_fail_closed", fingerprint=fingerprint, error=str(exc))
                fallback = ScoredVerdict(fingerprint, head_sha, "FAIL", 0.0, 0.0)
                await self._post_check_run(fallback, "failure")
                await self._set_commit_status(fallback, "failure")
                CONCLUSION_TOTAL.add(1, {"conclusion": "failure"})
                return "failure"
            finally:
                elapsed_ms = (datetime.now(timezone.utc) - start).total_seconds() * 1000
                DISPATCH_DURATION.record(elapsed_ms)


async def build_pool() -> asyncpg.Pool:
    return await asyncpg.create_pool(
        dsn=os.environ["PLAN_GATE_DSN"],
        min_size=2,
        max_size=int(os.environ.get("GATE_POOL_MAX", "8")),
        command_timeout=GATE_TIMEOUT_MS / 1000,
    )

The workflow that invokes this step is a single required job. Note the explicit permissions block — without checks: write and statuses: write the API calls above 403 and the gate cannot mark anything. The job runs on pull_request and, critically, uses the head SHA from the event so the Check Run lands on the commit branch protection evaluates:

name: plan-gate
on:
  pull_request:
    branches: [main]
permissions:
  contents: read
  checks: write
  statuses: write
concurrency:
  group: plan-gate-${{ github.event.pull_request.head.sha }}
  cancel-in-progress: true
jobs:
  query-plan-regression:
    runs-on: ubuntu-latest
    timeout-minutes: 5
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - name: Install gate
        run: pip install -e ./tools/plan-gate
      - name: Evaluate plan regression gate
        env:
          PLAN_GATE_DSN: ${{ secrets.PLAN_GATE_DSN }}
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
          GATE_TIMEOUT_MS: "800"
          REQUIRE_ACK: "true"
        run: |
          python -m plan_gate.run \
            --repo "${{ github.repository }}" \
            --head-sha "${{ github.event.pull_request.head.sha }}" \
            --fingerprint-from ./captured/plan_fingerprint.txt

For a canary or staged rollout of the gate itself — shadow-posting neutral conclusions before flipping to blocking — pair this workflow with Designing Canary and Progressive Delivery Gates.

Threshold Reference #

The gate carries its own operational SLOs, independent of the verdicts it projects. The dispatch budget is tight because the check runs on the pull-request critical path; a slow gate trains engineers to merge before it reports.

Branch protection must list query-plan-regression as a required status check on the protected branch, with “Require branches to be up to date before merging” enabled so the gate always evaluates the merge commit’s parent set. A required check that is not enforced on the branch ruleset is worse than no gate at all — it produces a green-looking PR that merges regressions. Encode the fail-closed rate as a Prometheus alert so an out-of-budget gate pages before it silently starts failing every PR:

groups:
  - name: plan-gate
    rules:
      - alert: PlanGateFailClosedStorm
        expr: |
          (
            sum(rate(plan_gate_fail_closed_total[10m]))
            /
            sum(rate(plan_gate_conclusion_total[10m]))
          ) > 0.02
        for: 10m
        labels:
          severity: page
        annotations:
          summary: "Plan gate failing closed on >2% of PRs — verdict store likely unreachable"
          runbook: "Check PLAN_GATE_DSN reachability and asyncpg pool saturation before merging."

      - alert: PlanGateDispatchSlow
        expr: |
          histogram_quantile(0.95, sum(rate(plan_gate_dispatch_duration_ms_bucket[10m])) by (le)) > 1500
        for: 10m
        labels:
          severity: ticket
        annotations:
          summary: "Gate p95 dispatch above 1500ms — engineers may merge before the check reports"

Failure Scenarios and Root Cause Analysis #

1. Flaky check timeouts under GitHub API pressure. Symptom: intermittent neutral/failure conclusions during peak merge hours despite valid verdicts. Root cause: the httpx call exceeds GATE_TIMEOUT_MS when the checks API is rate-limited, so raise_for_status trips the fail-closed path. Diagnose the rate-limit headroom:

gh api -i /repos/$OWNER/$REPO/check-runs -X POST -f name=probe -f head_sha=$SHA 2>&1 \
  | grep -i 'x-ratelimit-remaining'

Mitigation: raise GATE_TIMEOUT_MS to 1200 only for the retry attempt, add a single bounded retry with jitter, and move the gate to a GitHub App token whose rate limit scales with installation rather than the shared GITHUB_TOKEN ceiling.

2. Missing GITHUB_TOKEN scope. Symptom: every gate run posts a fail-closed failure, and the job log shows 403 Resource not accessible by integration. Root cause: the workflow omits permissions: checks: write, so the token is read-only. Diagnose:

gh api /repos/$OWNER/$REPO/actions/permissions/workflow --jq '.default_workflow_permissions'

Mitigation: add the explicit permissions block shown above; a repository default of read cannot be overridden by a step, only by the workflow-level or job-level permissions key.

3. Forked-PR secret unavailability. Symptom: PRs from forks fail closed with an empty PLAN_GATE_DSN. Root cause: GitHub withholds secrets from pull_request runs originating in forks, so the DSN is blank and the verdict lookup cannot connect. Diagnose by checking the event:

gh pr view $PR --json headRepositoryOwner,isCrossRepository --jq '.isCrossRepository'

Mitigation: run the gate on pull_request_target with a hardened, read-only DSN scoped to the verdict store, or post verdicts from a trusted workflow triggered by workflow_run after the fork’s capture job completes. Never expose a writable DSN to fork-triggered runs.

4. Verdict store unreachable — fail closed. Symptom: plan_gate_fail_closed_total spikes and every PR turns red simultaneously. Root cause: the asyncpg pool cannot reach PLAN_GATE_DSN (network partition, credential rotation, connection cap). Diagnose from the runner:

psql "$PLAN_GATE_DSN" -c "SELECT count(*) FROM scored_verdict WHERE scored_at > now() - interval '1 hour';"

Mitigation: this is the intended safe degradation — a red gate is correct while the store is down. Restore connectivity, then confirm the fail-closed rate returns below 0.5 %. Do not add a “skip when store down” branch; that reopens the exact gap the fail-closed default exists to cover.

5. Required check not enforced on branch protection. Symptom: a PR merges with a visible red query-plan-regression check. Root cause: the check is posted but not listed as required in the branch ruleset, so it is advisory. Diagnose:

gh api /repos/$OWNER/$REPO/branches/main/protection/required_status_checks --jq '.checks[].context'

Mitigation: add query-plan-regression to the required checks list and enable it in the org or repo ruleset. Verifying and enforcing exactly this is the subject of Failing a Pull Request on Query Plan Regression in GitHub Actions.

Configuration Reference #

Raise GATE_TIMEOUT_MS before touching retry logic when chasing flaky timeouts; flip REQUIRE_ACK only alongside a communicated policy change, since it converts every WARN into a merge speed bump. For the GitLab equivalent of this integration — the same verdict contract projected onto pipeline stages instead of Check Runs — see Wiring Plan Regression Gates into GitLab CI.

Related #

• Failing a Pull Request on Query Plan Regression in GitHub Actions — the runbook for making a PR check actually turn red and block merge.
• Wiring Plan Regression Gates into GitLab CI — the same verdict contract projected onto GitLab pipeline stages.
• Designing Canary and Progressive Delivery Gates — shadow-mode and staged rollout of the gate before it blocks.
• Tuning Thresholds for False Positive Reduction — the upstream stage that produces the scored verdict this gate consumes.

← Back to CI/CD Gate Integration for Query Plan Regression