[design-spec] k8s-postgrescluster-pgbouncer-spec

[rw-codebundle-agent]

CodeBundle Design Spec — Parent: intake Issue #64 (PgBouncer monitoring)

codebundle_name: "k8s-postgrescluster-pgbouncer-spec"
target_collection: "rw-cli-codecollection"
display_name: "Kubernetes PostgresCluster PgBouncer Spec Audit"
author: "rw-codebundle-agent"

purpose: |
Validates Crunchy Postgres Operator (PGO) PostgresCluster custom resource
specifications for the PgBouncer proxy block: pool_mode, default_pool_size,
max_client_conn, max_db_connections, and replica expectations. Provides a
Kubernetes-native complement to Prometheus runtime health checks.

tasks:

• name: "Fetch PostgresCluster PgBouncer Configuration"
  description: "Reads PostgresCluster CR (apiVersion postgres-operator.crunchydata.com/v1beta1) and extracts spec.proxy.pgBouncer (or current PGO field names) for the target cluster."
  script_name: "fetch-postgrescluster-pgbouncer.sh"
  expected_issue_severity: [1, 2]
  access_level: "read-only"
  data_type: "config"

• name: "Validate Pool Mode Matches Expected"
  description: "Compares configured poolMode to EXPECTED_POOL_MODE (transaction, session, statement) for ORM-appropriate settings."
  script_name: "validate-pool-mode.sh"
  expected_issue_severity: [2, 3]
  access_level: "read-only"
  data_type: "config"

• name: "Validate Connection Limit Consistency"
  description: "Checks logical ordering: max_client_conn and max_db_connections vs default_pool_size; flags impossible or risky combinations (e.g., max_db_connections below pool demand)."
  script_name: "validate-connection-limits.sh"
  expected_issue_severity: [2, 3]
  access_level: "read-only"
  data_type: "config"

• name: "Check PgBouncer Replica Count vs Service Expectations"
  description: "Compares PgBouncer Deployment/StatefulSet replica count (from CR status or workload) to minimum availability policy; flags single-replica production when policy requires HA."
  script_name: "validate-pgbouncer-replicas.sh"
  expected_issue_severity: [1, 2]
  access_level: "read-only"
  data_type: "config"

• name: "Optional Cross-Check CRD Limits with Live Prometheus Samples"
  description: "When PROMETHEUS_URL and selectors are provided, compares CRD max_client_conn to recent pgbouncer_config_max_client_connections; flags drift from GitOps source of truth."
  script_name: "cross-check-crd-vs-metrics.sh"
  expected_issue_severity: [2, 3]
  access_level: "read-only"
  data_type: "metrics"

scope:
level: "Resource"
qualifiers:
- CLUSTER_CONTEXT
- NAMESPACE
- POSTGRESCLUSTER_NAME
iteration_pattern: |
One run targets a named PostgresCluster in a namespace. Optional discovery:
list PostgresCluster CRs in namespace when POSTGRESCLUSTER_NAME=All.

resource_types:

• "postgrescluster"

generation_strategy: |
One SLX per PostgresCluster CR discovered in workspace scope, qualifiers:
cluster, namespace, postgrescluster name. Resource match: custom resource
postgres-operator.crunchydata.com/v1beta1 PostgresCluster.

env_vars:

• name: CONTEXT
  description: "Kubernetes context name"
  required: true

• name: NAMESPACE
  description: "Namespace containing the PostgresCluster"
  required: true

• name: POSTGRESCLUSTER_NAME
  description: "Name of the PostgresCluster CR or All for discovery"
  required: true

• name: EXPECTED_POOL_MODE
  description: "Expected pool mode string (transaction, session, statement)"
  required: true

• name: MIN_PGBOUNCER_REPLICAS
  description: "Minimum acceptable PgBouncer replicas for policy (optional)"
  required: false
  default: "1"

• name: PROMETHEUS_URL
  description: "Optional Prometheus URL for cross-check task"
  required: false

secrets:

• name: kubeconfig
  description: "Kubernetes credentials with get/list on PostgresCluster and workloads"
  format: "kubeconfig YAML"

platform:
name: "kubernetes"
cli_tools:
- "kubectl"
- "jq"
auth_methods:
- "kubeconfig"
api_docs: "https://access.crunchydata.com/documentation/postgres-operator/latest/"

related_bundles:

• name: "k8s-pgbouncer-prometheus-health"
  relationship: "complements"
  notes: "Runtime metrics and saturation detection; this bundle validates declared CRD configuration."

test_scenarios:

• name: "aligned_spec"
  description: "CRD pool_mode matches expected; limits are internally consistent; replicas meet minimum"
  expected_issues: 0

• name: "wrong_pool_mode"
  description: "pool_mode is session when transaction expected for async SQLAlchemy"
  expected_issues: 1
  expected_severities: [3]

notes: |
Target implementation repo: runwhen-contrib/rw-cli-codecollection.
PGO field names vary by version; implementer must confirm exact JSONPath for
pgbouncer settings against installed operator CRD (kubectl explain postgrescluster).
Optional Prometheus cross-check is best-effort when metrics RBAC differs from CR read access.

[rw-codebundle-agent]

🔧 Creator agent starting (sre mode). Target: runwhen-contrib/rw-cli-codecollection

[rw-codebundle-agent]

⚠️ Agent completed but no CodeBundle changes detected.

[rw-codebundle-agent]

🔧 Creator agent starting (sre mode). Target: runwhen-contrib/rw-cli-codecollection

[rw-codebundle-agent]

⚠️ Agent completed but no CodeBundle changes detected.

[rw-codebundle-agent]

🔧 Creator agent starting (sre mode). Target: runwhen-contrib/rw-cli-codecollection

[rw-codebundle-agent]

⚠️ Agent completed but no CodeBundle changes detected.