sync-postgres.yml

name: "Sync: PostgreSQL"
run-name: "Sync plots to DB: ${{ github.ref_name }}"

# Syncs plots from main branch to PostgreSQL.
# This ensures the database only contains data for code that is actually in main.
#
# New structure: plots/{spec_id}/
# - spec.md: Spec description, data requirements, use cases
# - metadata.yaml: Tags, implementation metadata, generation history
# - implementations/{library}.py: Library-specific implementation code

on:
  push:
    branches:
      - main
    paths:
      - 'plots/**'
      - 'alembic/versions/**'  # Ensure migration-only PRs trigger a sync
  workflow_dispatch:  # Allow manual trigger

concurrency:
  group: sync-postgres
  cancel-in-progress: false  # Queue instead of cancel

jobs:
  sync:
    name: Sync to PostgreSQL
    runs-on: ubuntu-latest
    permissions:
      contents: read
      id-token: write  # Required for Workload Identity Federation

    steps:
    - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1  # v7.0.1

    - name: Set up Python
      uses: actions/setup-python@5fda3b95a4ea91299a34e894583c3862153e4b97  # v7.0.0
      with:
        python-version: '3.13'

    - name: Install uv
      uses: astral-sh/setup-uv@20cfd1bf945f4377ade1205e4dbc17946fc9a30d  # v10.0.1

    - name: Install dependencies
      run: |
        uv sync

    - name: Authenticate to GCP
      uses: google-github-actions/auth@7c6bc770dae815cd3e89ee6cdf493a5fab2cc093  # v3
      with:
        project_id: anyplot
        workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}

    - name: Run database migrations
      run: |
        uv run alembic upgrade head
      env:
        INSTANCE_CONNECTION_NAME: ${{ secrets.INSTANCE_CONNECTION_NAME }}
        DB_USER: ${{ secrets.DB_USER }}
        DB_PASS: ${{ secrets.DB_PASS }}
        DB_NAME: ${{ secrets.DB_NAME }}

    - name: Sync plots to database
      run: |
        uv run python automation/scripts/sync_to_postgres.py
      env:
        INSTANCE_CONNECTION_NAME: ${{ secrets.INSTANCE_CONNECTION_NAME }}
        DB_USER: ${{ secrets.DB_USER }}
        DB_PASS: ${{ secrets.DB_PASS }}
        DB_NAME: ${{ secrets.DB_NAME }}
        GCS_BUCKET: ${{ vars.GCS_BUCKET || 'anyplot-images' }}
        ENVIRONMENT: production

    - name: Invalidate API cache
      env:
        # Hit the Cloud Run service directly — Cloudflare's bot challenge blocks
        # unauthenticated curl posts against api.anyplot.ai with a 403 HTML page,
        # so the CF-fronted hostname is not usable here. The direct *.run.app URL
        # is unprotected by CF and accepts the bearer token directly.
        API_URL: ${{ vars.API_DIRECT_URL || 'https://anyplot-api-r3tvmejsmq-ez.a.run.app' }}
        CACHE_INVALIDATE_TOKEN: ${{ secrets.CACHE_INVALIDATE_TOKEN }}
        # The header the Cloudflare Transform Rule stamps on everything it
        # proxies for api.anyplot.ai. This call skips the edge on purpose (see
        # above), so it has to stamp its own — which is what let
        # api/origin_gate.py drop the /debug/cache/invalidate exemption. An
        # exempt path is one anybody may POST to from anywhere; a caller that
        # carries the header needs no hole in the gate. The value is never
        # echoed: it reaches curl through the environment, and the step runs
        # without `set -x`.
        ORIGIN_SECRET: ${{ secrets.ORIGIN_SECRET }}
      run: |
        if [ -z "${CACHE_INVALIDATE_TOKEN}" ]; then
          echo "CACHE_INVALIDATE_TOKEN not set — skipping cache invalidation (cache will fall back to TTL expiry)"
          exit 0
        fi
        # Absent secret => no header => the gate answers 403, which the branch
        # below names. Sending an empty header instead would look like a
        # mismatch and read as a rotation problem rather than a missing secret.
        HDR=()
        if [ -n "${ORIGIN_SECRET}" ]; then HDR=(-H "X-Origin-Secret: ${ORIGIN_SECRET}"); fi
        status=$(curl -sS -o /tmp/invalidate.json -w '%{http_code}' \
          -X POST "${API_URL}/debug/cache/invalidate" \
          "${HDR[@]}" \
          -H "X-Cache-Token: ${CACHE_INVALIDATE_TOKEN}")
        echo "HTTP ${status}"
        cat /tmp/invalidate.json || true
        if [ "${status}" = "403" ]; then
          echo "::error::Cache invalidation was refused by the origin gate (HTTP 403). Set the ORIGIN_SECRET repository secret to the same value as the ORIGIN_SECRET on the anyplot-api Cloud Run service."
          exit 1
        fi
        if [ "${status}" = "503" ] || [ "${status}" = "401" ]; then
          echo "::error::Cache invalidation returned HTTP ${status} — token misconfigured server-side (503) or mismatched (401)"
          exit 1
        fi
        if [ "${status}" != "200" ]; then
          echo "::warning::Cache invalidation returned HTTP ${status} (sync succeeded; cache will fall back to TTL expiry)"
        fi

    - name: Summary
      run: |
        echo "### Sync Complete" >> $GITHUB_STEP_SUMMARY
        echo "" >> $GITHUB_STEP_SUMMARY
        echo "Plots have been synced to PostgreSQL." >> $GITHUB_STEP_SUMMARY
        echo "" >> $GITHUB_STEP_SUMMARY
        echo "**Structure synced:**" >> $GITHUB_STEP_SUMMARY
        echo "- plots/{spec-id}/specification.md (descriptions, applications, data, notes)" >> $GITHUB_STEP_SUMMARY
        echo "- plots/{spec-id}/specification.yaml (tags, created, issue, suggested, history)" >> $GITHUB_STEP_SUMMARY
        echo "- plots/{spec-id}/metadata/{language}/{library}.yaml (per-library metadata)" >> $GITHUB_STEP_SUMMARY
        echo "- plots/{spec-id}/implementations/{language}/{library}.{ext} (code)" >> $GITHUB_STEP_SUMMARY
        echo "" >> $GITHUB_STEP_SUMMARY
        echo "**Trigger:** \`${{ github.event_name }}\`" >> $GITHUB_STEP_SUMMARY
        echo "**Commit:** \`${{ github.sha }}\`" >> $GITHUB_STEP_SUMMARY