feat(cnpg): implement missing CNPG features for production parity

[birdmanmandbir]

Summary

Comparison with the production infra-prod CNPG cluster (flicknote-deploy Helm chart) revealed several missing features needed for production parity.

Missing Features

Critical (Blocking Production Use)

• Recovery (PITR) - Support point-in-time recovery from existing backups

  • spec.database.recovery.enabled
  • spec.database.recovery.source - externalClusters reference
  • spec.database.recovery.serverName - original cluster name in backup
  • spec.database.recovery.targetTime - optional PITR target (RFC 3339)
  • spec.database.recovery.objectStore - S3 credentials for backup source
  • When enabled, bootstrap uses recovery instead of initdb

• ScheduledBackup CRD - Currently only adding barman plugin to Cluster, not creating the backup schedule

  • Create ScheduledBackup resource with cron schedule
  • Reference the Cluster and use plugin method

• ObjectStore CRD - Backup destination configuration

  • Create barmancloud.cnpg.io/v1 ObjectStore resource
  • Configure S3 credentials, compression, retention policy

High Priority (Production Recommended)

• Pod Anti-Affinity - Spread replicas across nodes for HA

  • Automatically enabled when instances > 1
  • Uses soft anti-affinity so pods still schedule on single-node clusters

• Publication CRD - Won't fix: consumers can create their own CNPG Publication CRDs

• Additional Roles - Custom roles beyond Supabase defaults

  • spec.database.additionalRoles[] for roles like sequin_replication
  • Support: login, replication, bypassRLS, createDB, createRole, inherit, passwordSecretRef, inRoles

Medium Priority (Nice to Have)

• Database CRD - Won't fix: consumers can create their own CNPG Database CRDs for extensions

• auto_explain settings - Performance debugging

  • auto_explain.log_min_duration: "1s"
  • auto_explain.log_analyze/buffers/timing/nested_statements: "on"

• pgaudit settings - Audit logging

  • pgaudit.log: "function, ddl, role"
  • Full pgaudit configuration matching Supabase Cloud

Current Implementation

The operator currently creates:

• ✅ CNPG Cluster with managed roles + additional custom roles
• ✅ Init SQL ConfigMap (schemas, grants, PostgREST DDL watch)
• ✅ Barman plugin reference on Cluster (when backup.enabled)
• ✅ Pod anti-affinity when instances > 1
• ✅ pgaudit and auto_explain settings
• ❌ Missing: ObjectStore, ScheduledBackup CRDs

Reference

Production Helm chart: flicknote-deploy/charts/cnpg-cluster/

• templates/cluster.yaml - Cluster with recovery support
• templates/objectstore.yaml - ObjectStore CRD
• templates/scheduled-backup.yaml - ScheduledBackup CRD
• templates/publications.yaml - Publication CRD (consumers create their own)
• templates/database.yaml - Database CRD for extensions (consumers create their own)

Acceptance Criteria

1. Operator can restore from an existing CNPG backup (recovery mode)
2. Operator creates working scheduled backups to S3/R2
3. Pod anti-affinity works for HA deployments ✅

[birdmanmandbir]

Completed in PR #12. All critical features implemented:

• Recovery (PITR) - RecoverySpec with serverName, targetTime, S3 credentials
• ScheduledBackup CRD - BuildScheduledBackup() with cron schedule
• ObjectStore CRD - BuildObjectStore() and BuildRecoveryObjectStore() for backup/recovery

The operator now has full production parity for backup and recovery features.