Backend auto-discovery: populate mysql_servers/pgsql_servers from backend operator CRs

[renecannao]

Problem

Backends must be listed manually in ProxySQLConfig. Watching backend database CRs and feeding mysql_servers/pgsql_servers automatically — including writer-failover updates — is the strongest differentiator for this operator.

Proposal (high level — needs its own design round before implementation)

• API shape candidate: a backendSource stanza on ProxySQLConfig (alternative: a small dedicated CR) selecting backend kind + name/labels and mapping roles (writer/reader) to hostgroups.
• Initial backends: CloudNativePG and MariaDB Operator (both already exercised in nightly CI). Percona PXC, Oracle InnoDBCluster, Crunchy PGO follow.
• Failover: a writer change in the backend CR status must converge the writer hostgroup promptly.

First step

Write the design spec (watch mechanics, RBAC for third-party CRs, behavior when the backend operator is absent, conflict with manually listed servers).

Acceptance (eventual)

• e2e against the nightly CNPG and MariaDB examples: kill the primary, assert the writer hostgroup converges.

See docs/superpowers/specs/2026-06-10-operator-roadmap-design.md §3.1.

[coderabbitai]

🔗 Related PRs

#11 - test(e2e): expand coverage into a multi-scenario suite (#4–#8) [closed]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[renecannao]

Design doc landed: docs/superpowers/specs/2026-06-10-backend-autodiscovery-design.md (a951bed).

TL;DR of the decisions:

• API: a backendSources LIST on ProxySQLConfig (no dedicated CR in v1) — each entry: kind (enum cnpg | mariadb initially), clusterName or labelSelector, hostgroups: {writer, reader}, endpointMode: service|pod, port override, optional serverDefaults (weight/maxConnections/useSSL). Config-adjacent, reuses the whole build→hash→write-to-all pipeline, no new user RBAC surface. A dedicated ProxySQLBackendSource CR stays an escape hatch via the M3.2 aggregator if multi-tenancy demands it.
• Prefer the stable role Services (-rw/-ro, -primary/-secondary): on failover the backend operator re-points the Service, so ProxySQL needs no update at all — zero hostgroup churn. pod mode (follows status.currentPrimary / instance lists) exists only for per-replica routing/weights, and there the operator pushes placement changes hash-driven.
• Mechanics: per-kind GVK registry, dynamic unstructured watches (the ServiceMonitor pattern, no backend Go API imports), RBAC gated by Helm values per kind; a missing third-party CRD degrades to a per-source CRDMissing condition without blocking the rest of the config.
• Merge/conflicts: discovered rows merge with static mysqlServers/pgsqlServers into one Desired (no second sync path). Static/discovered collision → Degraded (StaticOverlap, discovered wins); a replication-hostgroup pair overlapping discovered hostgroups → Degraded (TopologyAuthorityConflict) — CR status is the topology authority there, per the external-failover decision (Design: failover handling for external (non-K8s) backend databases #30); pair-aware drift (Drift detection fights monitor-driven writer/reader moves (re-pushes stale placement) #34) never applies to discovered hostgroups.
• Status: status.backendSources[] with discovered count, current writer, and per-source reason (Discovered/CRDMissing/BackendNotFound/StatusIncomplete/...).
• Testing: pure per-kind resolvers + envtest with fake CNPG/MariaDB CRDs; nightly e2e kill-the-primary assertions on the live cloudnativepg and mariadb-operator examples.
• Non-goals (v1): probing/seeded discovery (settled by Design: failover handling for external (non-K8s) backend databases #30), Percona/Crunchy/InnoDBCluster kinds, cross-namespace discovery, credential discovery.