postgres-integration.yml

name: PostgreSQL integration

on:
  workflow_dispatch:
  pull_request:
    paths:
      - "backend/**"
      - "deployment/**"
      - "scripts/**"
      - "sql/**"
      - "compose.yaml"
      - ".github/workflows/postgres-integration.yml"
  push:
    branches: [main]
    paths:
      - "backend/**"
      - "deployment/**"
      - "scripts/**"
      - "sql/**"
      - "compose.yaml"
      - ".github/workflows/postgres-integration.yml"
  schedule:
    - cron: "17 3 * * 1"

permissions:
  contents: read

concurrency:
  group: postgres-integration-${{ github.ref }}
  cancel-in-progress: false

jobs:
  fresh-stack:
    name: Fresh PostgreSQL stack (amd64)
    runs-on: ubuntu-latest
    timeout-minutes: 45
    env:
      COMPOSE_PROJECT_NAME: advisor-ci-integration
      POSTGRES_ADMIN_PASSWORD: integration-admin-strong-value
      POWA_COLLECTOR_PASSWORD: integration-collector-strong-value
      ADVISOR_API_PASSWORD: integration-api-strong-value
      ADVISOR_EVALUATOR_PASSWORD: integration-evaluator-strong-value
      ADVISOR_JOIN_SOURCE_PASSWORD: integration-join-source-strong-value
      ADVISOR_JOIN_REPOSITORY_PASSWORD: integration-join-repository-strong-value
      WORKLOAD_DB_PASSWORD: integration-workload-strong-value
    steps:
      - name: Check out repository
        uses: actions/checkout@v4

      - name: Generate disposable runtime-validation credentials
        run: |
          python3 - <<'PY'
          import base64
          import hashlib
          import json
          import os
          import secrets

          def random_value() -> str:
              return base64.urlsafe_b64encode(secrets.token_bytes(32)).decode().rstrip("=")

          api_token = "adv_pat_v1_" + random_value()
          generated = {
              "ADVISOR_API_TOKEN": api_token,
              "ADVISOR_AUTH_PRINCIPALS": json.dumps(
                  [
                      {
                          "credential_id": "ci-runtime-validation",
                          "subject": "ci:postgres-integration",
                          "token_sha256": hashlib.sha256(
                              api_token.encode("utf-8")
                          ).hexdigest(),
                          "roles": ["admin"],
                      }
                  ],
                  separators=(",", ":"),
              ),
              "CLONE_ADMIN_PASSWORD": random_value(),
              "CLONE_RUNNER_PASSWORD": random_value(),
              "CLONE_EVALUATOR_TOKEN": random_value(),
          }
          for name, value in generated.items():
              if name != "ADVISOR_AUTH_PRINCIPALS":
                  print(f"::add-mask::{value}")
          with open(os.environ["GITHUB_ENV"], "a", encoding="utf-8") as handle:
              for name, value in generated.items():
                  handle.write(f"{name}={value}\n")
          PY

      - name: Build the complete core and real-validation stack
        run: docker compose --profile real-validation build

      - name: Boot fresh named volumes
        run: docker compose up -d --wait --wait-timeout 240

      - name: Verify health and migration idempotency
        run: |
          curl --fail --silent --show-error \
            http://127.0.0.1:8000/api/v1/health \
            | python -c '
          import json, sys
          health = json.load(sys.stdin)
          assert health["status"] == "healthy", health
          assert health["repository"] == "healthy", health
          assert health["collector"] == "healthy", health
          '
          docker compose run --rm repository-migrate
          bash scripts/verify-temporal-reliability.sh
          docker compose exec -T repository-db \
            psql -X --set=ON_ERROR_STOP=1 --username postgres \
              --port 5433 --dbname powa_repository --file=- \
            < sql/tests/join_duplicate_aggregation_integration.sql
          docker compose exec -T repository-db \
            psql -X --set=ON_ERROR_STOP=1 --username postgres \
              --port 5433 --dbname powa_repository --file=- \
            < sql/tests/product_scope_optimize_release_integration.sql
          docker compose exec -T source-db \
            psql -X --set=ON_ERROR_STOP=1 --username postgres --dbname powa --file=- \
            < sql/tests/join_outbox_guardrail_integration.sql

      - name: Verify a persisted 0013 database upgrades to 0014
        shell: bash
        run: |
          set -Eeuo pipefail
          upgrade_database="advisor_upgrade_0013"
          old_manifest="${RUNNER_TEMP}/repository-migrations-0013.manifest"
          cleanup() {
            docker compose exec -T repository-db \
              psql -X --set=ON_ERROR_STOP=1 --username postgres --port 5433 \
                --dbname postgres \
                --command="DROP DATABASE IF EXISTS ${upgrade_database} WITH (FORCE)" \
              >/dev/null 2>&1 || true
          }
          trap cleanup EXIT

          docker compose exec -T repository-db \
            psql -X --set=ON_ERROR_STOP=1 --username postgres --port 5433 \
              --dbname postgres \
              --command="CREATE DATABASE ${upgrade_database} TEMPLATE template0"
          docker compose exec -T repository-db \
            psql -X --set=ON_ERROR_STOP=1 --username postgres --port 5433 \
              --dbname "${upgrade_database}" <<'SQL'
          CREATE EXTENSION pg_stat_statements;
          CREATE EXTENSION btree_gist;
          CREATE SCHEMA "PoWA";
          CREATE EXTENSION powa WITH SCHEMA "PoWA";
          SELECT "PoWA".setup_powa_roles(true);
          SQL

          head -n 14 sql/repository-migrations.manifest >"${old_manifest}"
          docker compose run --rm \
            --env PGDATABASE="${upgrade_database}" \
            --env MIGRATIONS_MANIFEST=/tmp/repository-migrations-0013.manifest \
            --volume "${old_manifest}:/tmp/repository-migrations-0013.manifest:ro" \
            repository-migrate
          version_before="$(docker compose exec -T repository-db \
            psql -X --tuples-only --no-align --username postgres --port 5433 \
              --dbname "${upgrade_database}" \
              --command="SELECT max(version) || ':' || count(*) FROM advisor_migrations.schema_migrations")"
          test "${version_before}" = "0013:13"

          docker compose run --rm \
            --env PGDATABASE="${upgrade_database}" \
            repository-migrate
          docker compose exec -T repository-db \
            psql -X --set=ON_ERROR_STOP=1 --username postgres --port 5433 \
              --dbname "${upgrade_database}" <<'SQL'
          DO $upgrade_test$
          DECLARE
              release record;
          BEGIN
              SELECT * INTO STRICT release FROM advisor.release_info();
              IF release.current_migration <> '0014' OR release.applied_count <> 14 THEN
                  RAISE EXCEPTION 'unexpected upgraded release state: %', row_to_json(release);
              END IF;
              IF NOT has_function_privilege(
                  'advisor_api',
                  'advisor.query_trend(timestamp with time zone,interval,integer,oid)',
                  'EXECUTE'
              ) THEN
                  RAISE EXCEPTION 'scoped query_trend grant missing after upgrade';
              END IF;
          END
          $upgrade_test$;
          SQL

      - name: Verify real role drift reconciliation
        run: |
          docker compose exec -T source-db \
            psql -X -U postgres -d appdb --set=ON_ERROR_STOP=1 \
              --command="ALTER ROLE advisor_evaluator SUPERUSER CONNECTION LIMIT 17 VALID UNTIL '2020-01-01'"
          for attempt in $(seq 1 20); do
            role_state="$(
              docker compose exec -T source-db \
                psql -X -U postgres -d appdb --tuples-only --no-align \
                  --command="SELECT rolsuper::text || '|' || rolconnlimit::text || '|' || rolvaliduntil::text FROM pg_authid WHERE rolname='advisor_evaluator'"
            )"
            if [[ "$role_state" == "false|2|infinity" ]]; then
              exit 0
            fi
            printf 'drift check attempt=%s state=%s\n' "$attempt" "$role_state"
            sleep 1
          done
          echo "role drift was not reconciled: $role_state" >&2
          exit 1

      - name: Prepare the deterministic runtime candidate
        run: bash scripts/verify.sh

      - name: Boot disposable clone services
        run: |
          docker compose --profile real-validation up -d --wait \
            --wait-timeout 240 clone-db clone-evaluator

      - name: Verify disposable-clone runtime and runner policy
        run: bash scripts/verify-real-validation.sh

      - name: Show service logs on failure
        if: failure()
        run: docker compose --profile real-validation logs --no-color --tail=300

      - name: Remove disposable CI volumes
        if: always()
        run: docker compose --profile real-validation down --volumes --remove-orphans