pal-e-docs

Review: Critical: Migrate basketball-api Postgres to CNPG (v3)

review-417-2026-03-26-v3 Review

review ready
Project

Verdict: READY

Template Completeness

  • [x] Type -- Bug
  • [x] Lineage -- standalone, discovered during #184
  • [x] Repo -- pal-e-platform + pal-e-deployments + basketball-api (multi-repo correctly identified)
  • [x] What Broke -- thorough risk assessment of standalone postgres
  • [x] Repro Steps -- kubectl verification commands
  • [x] Expected Behavior -- CNPG parity with pal-e-docs
  • [x] Environment -- PG version gap documented (16 to 17), DB size noted (9MB)
  • [x] Acceptance Criteria -- updated in refinement v3 comment
  • [x] Related -- links to #184 trigger and project-westside-basketball
  • [x] Architecture diagram -- before/after included
  • [x] File Targets -- corrected in refinement v3 comment
  • [x] Migration Steps -- updated in refinement v2 comment with correct sequence
  • [x] Constraints -- PG major version, zero downtime, PVC retention

Traceability

  • [x] story:WS-S5 -- superadmin database backup coverage
  • [x] arch:postgres -- correct architecture component
  • [x] Forgejo issue -- forgejo_admin/pal-e-platform#187, open

File Targets (refinement v3 corrected list)

  • [x] terraform/network-policies.tf (lines 175-179) -- verified: postgres namespace netpol exists, currently allows pal-e-docs + cnpg-system + monitoring. basketball-api is correctly missing and needs adding.
  • [x] ~/pal-e-deployments/overlays/basketball-api/prod/deployment-patch.yaml -- verified: line 29-30 has BASKETBALL_DATABASE_URL with connection string postgresql://basketball:$(POSTGRES_PASSWORD)@postgres:5432/basketball. Needs host change to pal-e-postgres-rw.postgres.svc.cluster.local.
  • [x] ~/pal-e-deployments/overlays/basketball-api/prod/postgres.yaml -- verified: contains standalone postgres Deployment + PVC (postgres-data) + Service. Correct target for removal.
  • [x] ~/pal-e-deployments/overlays/basketball-api/prod/kustomization.yaml -- verified: line 6 lists postgres.yaml as resource. Must be removed when postgres.yaml is deleted.
  • [x] pvc.yaml -- verified: contains photo-uploads PVC (NOT postgres). Correctly marked DO NOT TOUCH in v3.
  • [x] ~/basketball-api/src/basketball_api/config.py -- verified: uses database_url setting with default connection string format. Compatible with CNPG host.

Repo Placement

Correctly identified as multi-repo: pal-e-platform (network policy), pal-e-deployments (kustomize overlay), basketball-api (verify config compatibility). CNPG cluster manifest is in pal-e-services (prereq #33 already resolved). Single Forgejo issue is appropriate since the primary change is in pal-e-platform (network policy) and pal-e-deployments (overlay update).

Dependencies

  • [x] pal-e-services#33 (Re-establish orphaned CNPG cluster manifest) -- DONE (board item #423 in done column). Prereq resolved.
  • No blocking items in in_progress column affect this ticket.
  • Board item #435 (tofu apply blocked by MinIO provider refresh) is in backlog -- could block the tofu apply for network policy if it's still active, but this is operational, not scope-related.

Acceptance Criteria

All criteria from refinement v2/v3 are verifiable by an agent:
  • SQL database creation -- verifiable via kubectl exec psql
  • Network policy -- verifiable via tofu plan/apply
  • Connection string update -- verifiable via kustomize build + ArgoCD sync
  • Data integrity -- verifiable via row count queries (pg_dump/restore)
  • Health check -- verifiable via curl
  • Backup -- verifiable by checking next daily Barman run
  • Cleanup -- verifiable via kustomize overlay diff

Blast Radius

Previously identified: mcd-tracker and pal-e-mail have identical standalone postgres vulnerability. Already tracked as discovered scope (#189, #190). No other downstream consumers affected -- basketball-api is the only consumer of its own database.

Recommendation

No action needed. All previous review findings (v1: 4 issues, v2: 1 blocker + 1 advisory) have been addressed in refinement v3. The corrected file targets, env var naming, pvc.yaml protection, and network policy scope are all verified against the codebase. Prereq (CNPG manifest restoration) is complete. Ticket is ready for execution.
Note: The agent executing this ticket should read refinement v3 comment (not just the issue body) for the corrected file targets and acceptance criteria.