CI/CD Gate Integration for Query Plan Regression: Blocking Bad Plans Before They Ship

CI/CD gate integration is the synchronous enforcement point where an automated query-plan regression verdict becomes a deploy decision — the small, strictly-ordered service that reads a scored PASS, WARN, or BLOCK payload and returns an exit code that either admits a release or halts it in the pipeline. This guide is the reference for Database SREs and Python platform teams who need to wire plan-regression verdicts into GitHub Actions, GitLab CI, Argo CD, and progressive-delivery controllers without adding unbounded latency, and without ever letting an unverified plan reach production traffic.

The gate does not compute anything expensive. Scoring, diffing, and threshold evaluation already happened upstream in the Regression Detection & Rule Engines stage, which emits a compact verdict artifact. The gate’s entire job is to ingest that verdict, map it onto deployment policy, report an idempotent commit status back to the source-control system, and write an audit record — all inside a bounded latency budget, and all fail-closed. If the gate cannot confirm a plan is safe, it treats the plan as unsafe. That single design rule — admit only on positive evidence — is what separates a real regression gate from a decorative check that quietly passes during an outage.

Pipeline Overview: Capture → Regression → CI Gate → Index Sync → Debugging #

The wider automation is five strictly isolated stages joined by typed, versioned contracts. Capture normalizes and fingerprints plans; Regression scores each fingerprint against its baseline and emits a verdict; the CI Gate turns that verdict into an admit-or-halt decision; Index Sync propagates only baseline-approved structural changes; and Debugging replays every block on isolated compute. This guide owns the third stage — the synchronous CI Gate — and treats the other four as fixed interfaces it consumes from or hands off to.

Isolation is the load-bearing property of the whole pipeline, and it constrains the gate more tightly than any other stage because the gate is the only one that runs in the deploy request path. Capture and Regression are asynchronous consumers behind a broker; they can retry, backfill, and scale replicas freely. The gate cannot. It runs once per merge or per deploy, it blocks a human or a rollout controller while it runs, and it must return a decision within a hard budget or fail closed. Everything about the gate’s architecture follows from that: it reads an already-scored payload rather than recomputing, it routes lookups to read replicas, it caches nothing it cannot prove is fresh, and it wraps every external call in a timeout and a circuit breaker.

The contract the gate consumes from Regression is a scored verdict envelope: {canonical_hash, baseline_version, result, violated_rules[], deltas, scored_at, verdict_ttl}. The contract it emits downstream is a deploy decision: an exit code, an annotated status posted to the source-control platform, and an immutable audit event. On a PASS the change proceeds to Index Sync for structural reconciliation; on a BLOCK the merge or deploy is halted and the violated rules are surfaced on the pull request. The gate itself never issues DDL, never mutates a baseline, and never writes to the verdict store — it is a read-and-decide component by construction.

Because the gate is downstream of scoring, the quality of every decision it makes is bounded by the quality of the verdict it receives. A gate cannot recover signal that the regression engine discarded, and it must not second-guess a verdict it does not have the context to re-derive. That division of labor is deliberate: verdict quality is owned upstream in Tuning Thresholds for False Positive Reduction, while the gate owns only the mapping from verdict to deploy action and the operational guarantees around that mapping.

Stage-by-Stage Architecture #

The CI Gate is itself a short, strictly-ordered internal pipeline. Each step has a single responsibility and a failure-isolation boundary, and the order is fixed so that a decision is never emitted before its audit record is durable.

Verdict ingestion (upstream contract) #

The gate is triggered by a webhook from the source-control platform (a check_run or pipeline event) carrying the commit SHA and the changed query fingerprints. It does not accept a verdict from the webhook payload itself — that payload is attacker-influenceable and can be replayed. Instead the gate takes the commit SHA as a key and performs an authenticated lookup against the verdict store, fetching {result, violated_rules[], scored_at, verdict_ttl, baseline_version} for every fingerprint in the change. Ingestion validates the envelope with a pydantic model and rejects any verdict whose scored_at is older than its verdict_ttl allows, treating stale rows as absent. Failure isolation: a malformed or missing verdict never resolves to PASS; it resolves to a fail-closed BLOCK with reason no_verdict, so a plan that was never scored cannot slip through on an empty lookup.

Policy mapping (verdict to action) #

Policy mapping is a pure function from the highest-severity verdict across the change to a deploy action. PASS admits with exit code 0. WARN admits only with a recorded acknowledgement — an approving reviewer or a signed override annotation — and otherwise holds the deploy; the acknowledgement identity and reason are captured in the audit record. BLOCK fails the check with a non-zero exit code and posts the violated_rules[] list onto the merge request as an annotated report. The mapping is deterministic and versioned; the same verdict must always produce the same action for a given policy version, which is what makes gate behavior reproducible across reruns. Threshold derivation for the underlying verdicts lives in Defining Regression Thresholds for Query Plans; the gate consumes those decisions rather than reproducing them. Failure isolation: the mapping has no I/O and cannot fail on external state — if it receives an unrecognized result value it treats it as BLOCK.

Synchronous decision and latency budget #

The gate runs in the deploy request path, so its total wall-clock is a hard SLO, not a best effort. The end-to-end budget is $p95 \le 800 ms$ and $p99 \le 2 s$, decomposed into a verdict-lookup sub-budget ($p95 \le 300 ms$ against a read replica) and a fixed policy-and-report cost. Every external call is wrapped in asyncio.wait_for; the aggregate deadline is a single gate_timeout of 2 s. When the deadline fires, the gate does not retry inline and does not degrade to a permissive answer — it emits BLOCK with reason gate_timeout and lets the pipeline surface a retryable failure. Failure isolation: the timeout is enforced at the top of the decision coroutine, so a slow verdict store or a saturated replica manifests as a bounded, fail-closed halt rather than a hung pipeline job.

Idempotent status reporting to the SCM #

After a decision is made the gate reports a commit status back to the source-control platform. Reporting is idempotent: the status is keyed by (commit_sha, policy_version, gate_run_id), so a retried webhook or a re-invoked Action posts the same terminal status instead of duplicating checks or flipping a green build red. The report carries the decision, the violated rules, a link to the audit event, and the verdict age used, so a reviewer can see exactly which scored payload drove the outcome. This is the step that makes the gate safe to rerun — a property wiring plan-regression gates into GitHub Actions and wiring plan-regression gates into GitLab CI both depend on, since CI systems retry jobs freely. Failure isolation: if status reporting fails after the decision is durable, the exit code still governs the job outcome; the missing status is reconciled on the next poll rather than inverting the decision.

Audit trail (downstream contract) #

Every decision writes one immutable audit event before the exit code is returned: the commit SHA, the fingerprints and their verdicts, the policy version, the acknowledgement identity for any WARN override, the verdict age, and the final action. The audit store is append-only and is the system of record for “why did this deploy ship” and “who acknowledged this warning.” For Argo CD and progressive-delivery flows the same audit event drives the canary hold decision, wiring the gate into enforcing plan baselines with Argo CD and designing canary and progressive delivery gates. Failure isolation: the audit write precedes the decision return; if the audit store is unreachable the gate fails closed on BLOCK with reason audit_unavailable, because an unaudited admit is not an admit the system is willing to make.

Threshold Matrix #

The gate’s thresholds are operational — they govern latency, verdict freshness, and the mapping from verdict to action — rather than the plan-quality thresholds that the regression engine owns. The bands below are the reference default for a Tier-1 (release-blocking) pipeline; lower-criticality pipelines widen the latency bands and may downgrade a missing baseline from BLOCK to WARN.

Evaluation order inside the gate is fixed and short-circuits: ingestion validity is checked first, then verdict freshness, then the policy map. A stale or missing verdict is resolved before the result field is ever consulted, so a plan can never be admitted on the strength of a PASS that has since expired. The verdict_ttl default is 300 s for interactive merges and 120 s for automated progressive-delivery gates, where a fresher signal matters more because the rollout is already in motion.

Production Readiness Requirements #

• Dedicated governance connection pool. The gate reads verdicts through a pool that is separate from every application pool and from the regression evaluators — for example an asyncpg pool of min_size=2, max_size=10 pointed at the governance database, with statement_timeout set to 500 ms so a slow lookup fails inside the latency budget instead of consuming it. Sizing is small by design: the gate is low-QPS and strictly ordered, and an oversized pool only masks replica pressure.
• Read-replica routing. All verdict and audit-read lookups route to a read replica or a read-only endpoint; the gate never touches the primary and never issues a write beyond the append-only audit event, which goes to its own audit sink. Routing reads off the primary keeps a deploy storm from contending with production write traffic, and it makes the gate safe to scale to one replica per environment.
• Circuit breaker. The verdict-store client sits behind a circuit breaker that trips after 5 consecutive failures, holds open for 30 s, then transitions to half-open and admits exactly 1 probe request before closing or re-tripping. While the breaker is open the gate does not wait on the store at all — it fails closed immediately with store_open, converting a slow dependency into a fast, deterministic BLOCK and protecting the latency budget from a cascading stall.
• Least-privilege gate identity. The gate runs as an identity that can read verdicts and append audit events and nothing else: no DDL, no writes to baselines or the verdict store, no access to production application schemas. The credential is scoped to the governance read replica and the audit sink. This mirrors the split enforced across the pipeline — only Index Sync holds DDL rights — and it guarantees that the component making the ship-or-halt decision cannot itself alter the evidence it decides on. Upstream capture of that evidence is detailed in Automated EXPLAIN Capture & Storage Workflows.

Observability Hooks #

The gate emits OpenTelemetry spans and Prometheus-style metrics with stable names and types, because a gate that cannot be observed cannot be trusted to fail closed correctly. Every decision is one span with attributes for the commit SHA, the policy version, the winning verdict, and the verdict age, so a single trace ties a deploy outcome back to the exact scored payload.

The two metrics to alert on first are ci_gate_open_seconds and ci_gate_fail_closed_total. A rising p99 on the former means the gate is approaching its budget and will soon start timing out; a spike in the latter — especially with reason store_open — means the verdict store or its replica is unhealthy and every deploy is being held. Both are leading indicators of a stuck pipeline, and both should page before developers start filing tickets about red builds.

Python Orchestration Patterns #

The gate is not a DAG. It is a single small synchronous service invoked once per deploy — a CI Action step, a GitLab job, or an Argo CD resource hook — that must run to a decision in strict order and then exit. There is no worker pool to size and no partition count to match; there is exactly one replica per environment because the gate must be strictly ordered and idempotent, and horizontal fan-out would only create the possibility of two conflicting statuses on the same commit. The service reads the scored verdict, applies the policy map under a hard deadline, writes the audit event, and returns an exit code that the surrounding CI system interprets as admit or halt.

The reference implementation is an asyncio entrypoint instrumented with structlog and OpenTelemetry, reading verdicts through the dedicated governance pool and returning a POSIX exit code:

import asyncio
import sys
from dataclasses import dataclass, field

import asyncpg
import structlog
from opentelemetry import metrics, trace
from pydantic import BaseModel, Field

log = structlog.get_logger()
tracer = trace.get_tracer("ci_gate.decision")
meter = metrics.get_meter("ci_gate.decision")

decisions = meter.create_counter("ci_gate_decisions_total")
fail_closed = meter.create_counter("ci_gate_fail_closed_total")
open_seconds = meter.create_histogram("ci_gate_open_seconds")

EXIT_ADMIT = 0        # PASS, or WARN with a recorded acknowledgement
EXIT_HALT = 1         # BLOCK, or WARN awaiting ack — halt the deploy
GATE_TIMEOUT_S = 2.0  # aggregate fail-closed deadline
VERDICT_TTL_S = 300.0 # reject verdicts older than this


class Verdict(BaseModel):
    canonical_hash: str
    result: str  # "PASS" | "WARN" | "BLOCK"
    violated_rules: list[str] = Field(default_factory=list)
    scored_at_age_s: float
    baseline_version: str | None = None


@dataclass(frozen=True)
class Decision:
    exit_code: int
    action: str          # "admit" | "hold" | "block"
    reason: str
    rules: list[str] = field(default_factory=list)


def map_policy(v: Verdict, ack_present: bool) -> Decision:
    # Pure function: verdict + acknowledgement -> deploy action.
    if v.result == "PASS":
        return Decision(EXIT_ADMIT, "admit", "pass")
    if v.result == "WARN":
        if ack_present:
            return Decision(EXIT_ADMIT, "admit", "warn_acked", v.violated_rules)
        return Decision(EXIT_HALT, "hold", "warn_needs_ack", v.violated_rules)
    # BLOCK or any unrecognized result fails closed.
    return Decision(EXIT_HALT, "block", "block", v.violated_rules)


async def fetch_verdict(pool: asyncpg.Pool, commit_sha: str) -> Verdict | None:
    async with pool.acquire() as conn:  # governance read replica, statement_timeout=500ms
        row = await conn.fetchrow(
            "SELECT canonical_hash, result, violated_rules, baseline_version, "
            "EXTRACT(EPOCH FROM (now() - scored_at)) AS age "
            "FROM plan_verdicts WHERE commit_sha = $1 "
            "ORDER BY severity_rank DESC LIMIT 1",
            commit_sha,
        )
    if row is None:
        return None
    return Verdict(
        canonical_hash=row["canonical_hash"],
        result=row["result"],
        violated_rules=list(row["violated_rules"] or []),
        scored_at_age_s=float(row["age"]),
        baseline_version=row["baseline_version"],
    )


async def decide(pool: asyncpg.Pool, commit_sha: str, ack_present: bool) -> Decision:
    with tracer.start_as_current_span("ci_gate.decide") as span:
        span.set_attribute("vcs.commit_sha", commit_sha)
        loop = asyncio.get_running_loop()
        started = loop.time()
        try:
            verdict = await asyncio.wait_for(
                fetch_verdict(pool, commit_sha), timeout=GATE_TIMEOUT_S
            )
        except asyncio.TimeoutError:
            fail_closed.add(1, {"reason": "gate_timeout"})
            return Decision(EXIT_HALT, "block", "gate_timeout")
        except (asyncpg.PostgresError, OSError):
            log.exception("verdict_store_unreachable", commit=commit_sha)
            fail_closed.add(1, {"reason": "store_open"})
            return Decision(EXIT_HALT, "block", "store_open")
        finally:
            open_seconds.record(loop.time() - started)

        if verdict is None:
            fail_closed.add(1, {"reason": "no_verdict"})
            return Decision(EXIT_HALT, "block", "no_verdict")
        if verdict.scored_at_age_s > VERDICT_TTL_S:  # stale == absent
            fail_closed.add(1, {"reason": "stale_verdict"})
            return Decision(EXIT_HALT, "block", "stale_verdict")

        decision = map_policy(verdict, ack_present)
        span.set_attribute("ci_gate.action", decision.action)
        decisions.add(1, {"decision": decision.action})
        log.info("gate_decision", commit=commit_sha, action=decision.action,
                 reason=decision.reason, rules=decision.rules,
                 verdict_age_s=round(verdict.scored_at_age_s, 1))
        return decision


async def main(commit_sha: str, ack_present: bool) -> int:
    pool = await asyncpg.create_pool(min_size=2, max_size=10, command_timeout=1.0)
    try:
        decision = await decide(pool, commit_sha, ack_present)
        await write_audit_event(pool, commit_sha, decision)  # durable before exit
    finally:
        await pool.close()
    return decision.exit_code


if __name__ == "__main__":
    sys.exit(asyncio.run(main(sys.argv[1], ack_present="--ack" in sys.argv)))

The audit write is intentionally the last awaited call before the process exits, and it runs inside the same try that owns the pool, so the exit code is never returned until the decision is durable. The circuit breaker and the SCM status post live in the wrappers around this entrypoint — the breaker in the asyncpg client factory, the status post keyed by (commit_sha, policy_version, gate_run_id) in the CI adapter — keeping the decision coroutine itself small, pure where it can be, and fast to reason about. That thin, ordered shape is what lets the same gate drop into GitHub Actions, GitLab CI, or an Argo resource hook with only the adapter changing.

Common Failure Modes and Mitigations #

• Verdict store unreachable — deploys hang or slip through. Symptom: the gate stalls, or worse, an empty lookup is read as clean. Cause: replica outage, network partition, or an oversubscribed governance pool. Mitigation: the aggregate gate_timeout of 2 s plus the circuit breaker convert an unreachable store into a fast BLOCK with reason store_open; alert on ci_gate_fail_closed_total{reason="store_open"} and ci_gate_circuit_state, and never treat a null verdict as PASS.
• Gate flakiness and timeout — intermittent red builds. Symptom: the same commit passes on rerun after failing on gate_timeout. Cause: replica lag spikes pushing verdict-lookup p95 past its 300 ms sub-budget. Mitigation: track ci_gate_verdict_lookup_seconds, route to a dedicated read replica, and keep the governance pool small so queuing is visible rather than hidden; a persistent flake is a replica-sizing problem, not a reason to widen the deadline.
• Ack-bypass abuse — WARN overrides used to ship regressions. Symptom: repeated WARN-with-ack admits on the same fingerprint, each individually justified. Cause: the acknowledgement path is treated as a rubber stamp. Mitigation: record every ack identity and reason in the audit trail, alert on ci_gate_required_ack_total rising for a single fingerprint, and cap consecutive acked overrides before the policy escalates the verdict to a hard BLOCK pending a re-score.
• Stale cached verdict — an expired PASS admits a changed plan. Symptom: a plan that has since regressed is admitted on an old green verdict. Cause: caching or a long verdict_ttl outliving the plan it scored. Mitigation: reject any verdict older than verdict_ttl as no_verdict, count it on ci_gate_stale_verdict_total, and shorten the TTL to 120 s for progressive-delivery gates where the rollout is already moving. Verdict quality and freshness upstream are governed in Tuning Thresholds for False Positive Reduction.

Related #

• Wiring Plan Regression Gates into GitHub Actions — the gate as a check_run step with idempotent commit statuses.
• Wiring Plan Regression Gates into GitLab CI — running the gate as a required job with merge-request annotations.
• Enforcing Plan Baselines with Argo CD — wiring verdicts into resource hooks and sync policy.
• Designing Canary and Progressive Delivery Gates — verdict-driven canary hold windows and rollback triggers.
• Regression Detection & Rule Engines — the upstream stage that scores plans and produces the verdicts this gate enforces.

← Back to QueryPlan.org