# =============================================================================
# AI Software Factory — PostgreSQL 16 (StatefulSet, single replica) + PITR
#
# NOTE FOR PRODUCTION:
#   A self-hosted StatefulSet is the simple path. For production you are
#   strongly advised to use a MANAGED database instead (AWS RDS / Aurora,
#   GCP Cloud SQL, Azure Database for PostgreSQL) — you get automated
#   backups, patching, failover and monitoring for free. If you do that:
#   1. delete this file from the kustomization resources list, and
#   2. point the DATABASE_URL at the managed endpoint (the apps reference
#      the `factory-db` service name — create a Service with that name in
#      front of the endpoint, or change the env var).
#
# PITR (TASK-0135 backup/DR — RPO ≤ 5 min, RTO ≤ 1 h), wal-g v3 (pinned
#   image `chekkan/wal-g:v3.0.7`):
#
#   WAL archiving — Postgres-native, the pattern wal-g's own PostgreSQL.md
#     prescribes: postgresql.conf `archive_command = '/opt/walg/
#     wal-push-wrapper.sh %p'` (the wrapper maps the spec env names onto
#     wal-g v3's and runs `wal-g wal-push %p` — see `postgres/configmap.yaml`).
#     Each completed WAL segment (~16 MB, seconds of writes under load) is
#     pushed to S3 the moment it completes. `archive_timeout = 60`
#     force-flushes a partial segment every 60 s so the RPO bound also holds
#     when the cluster is idle. The wal-g BINARY is staged into the shared
#     emptyDir `walg-tools` (mount `/opt/walg`) by the `walg-stager`
#     initContainer from the pinned image, so `archive_command` has no PATH
#     dependence.
#
#   wal-g v3 env (VERIFIED against the pinned image, 2026-10):
#     - wal-g v3 REMOVED `WALG_S3_BUCKET` / `WALG_S3_ENDPOINT`. The storage
#       root is ONE variable: `WALG_S3_PREFIX=s3://<bucket>/<prefix>` (the
#       spec's "WAL_S3_BUCKET" requirement maps to the bucket component of
#       this prefix). wal-g organizes `basebackups/` and `wal_005/` under
#       that root itself — do NOT split base vs WAL into different roots
#       (PITR restore requires one storage per database).
#     - endpoint/region/creds come from the AWS_* names: AWS_ENDPOINT,
#       AWS_REGION, AWS_S3_FORCE_PATH_STYLE, AWS_ACCESS_KEY_ID,
#       AWS_SECRET_ACCESS_KEY (source: pkg/storages/s3/configure.go, v3.0.7).
#     - wal-g v3 has NO `wal-archivestart` and NO `backup-start` — archiving
#       is the archive_command above, and the daily base backup is a single
#       `wal-g backup-push` (online streaming mode, see cronjob-backup.yaml).
#
#   Backup-health gate (stage 9, `scripts/backup-health.ts`, runbook §8.7)
#     reads the REAL ARTIFACTS — no component writes a synthetic marker
#     object (a marker without its artifact would be a false signal, and an
#     artifact without a marker a false alarm):
#       • "last base backup < 26 h" = newest object LastModified under
#         `pg/basebackups_005/` (each base ends with its
#         `*_backup_stop_sentinel.json`),
#       • "last WAL ship < 10 min" = newest object LastModified under
#         `pg/wal_005/` (the segments this archive_command ships),
#       • "last OS snapshot < 26 h" = newest SUCCESS snapshot start_time
#         from `GET /_snapshot/factory-s3/_all` (the snapshot CronJob puts
#         it in S3 under opensearch/snapshots/; the gate reads it via the
#         OS API, not a marker file).
#     Verdicts: reachable-but-stale → always FAIL; unreachable → loud
#     report-only SKIP in dev, hard FAIL in staging (BACKUP_HEALTH_STRICT /
#     DEPLOY_STAGING).
#
#   S3 bucket: `WAL_S3_BUCKET` (factory-backups) must be created with
#     VERSIONING ON — S3 versioning is a bucket-level setting (runbook
#     §8.7), so a corrupt or bad backup can be rolled back to the previous
#     object version. A least-priv IAM policy for the backup identity is in
#     `postgres/walg-iam-policy.yaml` (put/get on the `pg/` prefix only).
# =============================================================================
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  labels:
    app.kubernetes.io/name: postgres
    app.kubernetes.io/component: database
spec:
  serviceName: postgres
  replicas: 1
  selector:
    matchLabels:
      app.kubernetes.io/name: postgres
  template:
    metadata:
      labels:
        app.kubernetes.io/name: postgres
        app.kubernetes.io/component: database
    spec:
      securityContext:
        runAsUser: 999 # postgres user in the official image
        runAsGroup: 999
      initContainers:
        # Stage the pinned wal-g binary into the shared emptyDir `walg-tools`
        # (mounted at /opt/walg in the postgres container — that is where
        # archive_command finds it). Verified against the image (2026-10):
        # Ubuntu 24.04 base, wal-g v3.0.7 PostgreSQL build, binary at
        # /usr/local/bin/wal-g. The image does NOT ship pg tools — which is
        # fine here: archiving only needs the wal-g binary (postgres itself
        # calls it per segment), and the daily base backup uses wal-g's
        # ONLINE streaming mode (no local pg_basebackup needed, see
        # cronjob-backup.yaml).
        - name: walg-stager
          image: chekkan/wal-g:v3.0.7
          command:
            - sh
            - -c
            - |
              set -e
              mkdir -p /opt/walg
              cp /usr/local/bin/wal-g /opt/walg/wal-g
              chmod 0755 /opt/walg/wal-g
              # Copy the archive_command wrapper too (it maps the spec env
              # names onto wal-g v3's, then runs `wal-g wal-push`; no marker
              # writes — the gate reads the artifacts, see header).
              cp /etc/pitr/wal-push-wrapper.sh /opt/walg/wal-push-wrapper.sh
              chmod 0755 /opt/walg/wal-push-wrapper.sh
              /opt/walg/wal-g --version
          volumeMounts:
            - name: walg-tools
              mountPath: /opt/walg
            - name: pitr-config
              mountPath: /etc/pitr
              readOnly: true
        # Make the PITR config authoritative in $PGDATA (idempotent, safe on
        # restarts of an already-initialized volume):
        #   - stage conf.d/pitr.conf (archive_command + archive_timeout +
        #     wal sizing) ONLY if not present — so a later manual edit of
        #     that file is never clobbered by a restart;
        #   - ensure `include_dir 'conf.d'` is in postgresql.conf (fresh
        #     volumes: postgresql.conf doesn't exist yet — initdb hasn't run —
        #     so only the conf.d staging happens now; the
        #     /docker-entrypoint-initdb.d/zz-first-boot-pitr.sh hook appends
        #     the include_dir line right after initdb).
        - name: pitr-stager
          image: busybox:1.36
          command:
            - sh
            - -c
            - |
              set -e
              PGD="${PGDATA:-/var/lib/postgresql/data/pgdata}"
              mkdir -p "$PGD/conf.d"
              if [ ! -f "$PGD/conf.d/pitr.conf" ]; then
                cp /etc/pitr/pitr.conf "$PGD/conf.d/pitr.conf"
                echo "[pitr-stager] staged $PGD/conf.d/pitr.conf"
              fi
              CONF="$PGD/postgresql.conf"
              if [ -f "$CONF" ] && ! grep -q "include_dir 'conf.d'" "$CONF"; then
                echo "include_dir 'conf.d'" >> "$CONF"
                echo "[pitr-stager] appended include_dir to $CONF"
              fi
          env:
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
          volumeMounts:
            - name: pgdata
              mountPath: /var/lib/postgresql/data
            - name: pitr-config
              mountPath: /etc/pitr
              readOnly: true
      containers:
        - name: postgres
          image: postgres:16-alpine
          ports:
            - containerPort: 5432
              name: postgres
          envFrom:
            # Secret `factory-db` (create per secret.example.yaml):
            #   POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB
            - secretRef:
                name: factory-db
          env:
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
            # ---- wal-g S3 target (the spec's WAL_S3_BUCKET) ---------------
            # ONE storage root for base backups AND WAL (wal-g v3 layout:
            # <prefix>/basebackups/ + <prefix>/wal_005/ — do not split them).
            # The wrapper the archive_command invokes exports this to the
            # wal-g v3 name (WALG_S3_PREFIX) — see configmap.yaml, where the
            # env → wal-g mapping is recorded and unit-tested by the
            # runbook's staging checklist.
            - name: WAL_S3_BUCKET
              value: "factory-backups"
            - name: WAL_S3_PREFIX
              value: "pg"
            - name: S3_ENDPOINT
              valueFrom:
                configMapKeyRef:
                  name: factory-config
                  key: S3_ENDPOINT
            - name: S3_REGION
              valueFrom:
                configMapKeyRef:
                  name: factory-config
                  key: S3_REGION
            # wal-g v3 credential names, mapped from the app's single set of
            # storage credentials (see secret.example.yaml S3_ACCESS_KEY /
            # S3_SECRET_KEY).
            - name: AWS_ACCESS_KEY_ID
              valueFrom:
                secretKeyRef:
                  name: factory-secrets
                  key: S3_ACCESS_KEY
            - name: AWS_SECRET_ACCESS_KEY
              valueFrom:
                secretKeyRef:
                  name: factory-secrets
                  key: S3_SECRET_KEY
          resources:
            requests:
              cpu: 500m
              memory: 1Gi
            limits:
              cpu: "2"
              memory: 4Gi
          volumeMounts:
            - name: pgdata
              mountPath: /var/lib/postgresql/data
            - name: walg-tools
              mountPath: /opt/walg
            # First-boot hook: runs as postgres AFTER initdb, fresh volume
            # only; appends `include_dir 'conf.d'` to the generated
            # postgresql.conf.
            - name: pitr-initdb-hook
              mountPath: /docker-entrypoint-initdb.d/zz-first-boot-pitr.sh
              subPath: zz-first-boot-pitr.sh
              readOnly: true
          startupProbe:
            # Cold storage can be slow; ~2 min budget.
            exec:
              command:
                - sh
                - -c
                - "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"
            periodSeconds: 5
            timeoutSeconds: 5
            failureThreshold: 24
          readinessProbe:
            exec:
              command:
                - sh
                - -c
                - "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"
            periodSeconds: 10
            timeoutSeconds: 5
      volumes:
        - name: walg-tools
          emptyDir: {}
        - name: pitr-config
          configMap:
            name: factory-pg-pitr-config
        - name: pitr-initdb-hook
          configMap:
            name: factory-pg-pitr-config
            items:
              - key: first-boot-pitr.sh
                path: zz-first-boot-pitr.sh
            defaultMode: 0755
      # No PgBouncer / read replica / multi-replica HA in this file: the
      # pooling + replica manifests are the Phase-15 "Pooling + replica"
      # deliverable (k8s: pgbouncer deployment + DATABASE_REPLICA_URL); the
      # PITR setup here is the backup/DR foundation they build on.
  volumeClaimTemplates:
    - metadata:
        name: pgdata
      spec:
        accessModes: ["ReadWriteOnce"]
        resources:
          requests:
            storage: 20Gi
        # In production, set storageClassName here (e.g. gp3-encrypted on AWS).
---
apiVersion: v1
kind: Service
metadata:
  # Named `factory-db` so application manifests can use a stable DNS name:
  #   postgresql://$(POSTGRES_USER):$(POSTGRES_PASSWORD)@factory-db:5432/$(POSTGRES_DB)
  name: factory-db
  labels:
    app.kubernetes.io/name: postgres
spec:
  type: ClusterIP
  selector:
    app.kubernetes.io/name: postgres
  ports:
    - name: postgres
      port: 5432
      targetPort: 5432