Integrating PostgreSQL Compliance Checks into Your CI/CD Pipeline

Why Shift Compliance Left

Most compliance issues are discovered during audits — months after they were introduced. By then, fixing them requires emergency patches, data backfills, and awkward conversations with auditors.

CI/CD integration catches issues at the source: the developer who adds a phone_number column sees immediately that it needs to be registered as PII.

GitHub Actions Example

name: Compliance Check
on:
  push:
    branches: [main]
    paths: ['migrations/**', 'sql/**']

jobs:
  compliance:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_PASSWORD: test
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports: ['5432:5432']

    steps:
      - uses: actions/checkout@v4

      - name: Apply migrations
        run: psql -h localhost -U postgres -f migrations/*.sql
        env:
          PGPASSWORD: test

      - name: Install pgcomply
        run: psql -h localhost -U postgres -f pgcomply.sql
        env:
          PGPASSWORD: test

      - name: Check PII drift
        run: |
          DRIFT=$(psql -h localhost -U postgres -t -c \
            "SELECT count(*) FROM pgcomply.schema_drift() WHERE confidence = 'high'")
          if [ "$DRIFT" -gt 0 ]; then
            echo "::error::Unregistered PII detected!"
            psql -h localhost -U postgres -c "SELECT * FROM pgcomply.schema_drift()"
            exit 1
          fi
          echo "✓ No PII drift detected"
        env:
          PGPASSWORD: test

      - name: Health check
        run: |
          FAILS=$(psql -h localhost -U postgres -t -c \
            "SELECT count(*) FROM pgcomply.health_check() WHERE status = 'FAIL' AND severity = 'critical'")
          if [ "$FAILS" -gt 0 ]; then
            echo "::warning::Critical health check failures detected"
            psql -h localhost -U postgres -c "SELECT * FROM pgcomply.health_check() WHERE status = 'FAIL'"
          fi
          echo "✓ Health check complete"
        env:
          PGPASSWORD: test

      - name: Save compliance report
        if: always()
        run: |
          psql -h localhost -U postgres -c \
            "SELECT * FROM pgcomply.health_check()" > compliance-report.txt
          psql -h localhost -U postgres -c \
            "SELECT * FROM pgcomply.schema_drift()" >> compliance-report.txt
        env:
          PGPASSWORD: test

      - uses: actions/upload-artifact@v4
        if: always()
        with:
          name: compliance-report
          path: compliance-report.txt

What to Check When

| Pipeline Stage | Check | Fail On | |---------------|-------|---------| | After migrations | schema_drift() | Any high-confidence PII drift | | Before staging deploy | health_check() | Critical failures | | Before production deploy | classification_map() | Unclassified PII tables | | Post-deploy verification | Full health_check() | Any regression |

Developer Experience

When a build fails due to PII drift, the developer sees:

::error::Unregistered PII detected!

 drift_type | table_name | column_name  | suspected_pii | confidence
------------+------------+--------------+---------------+-----------
 new_column | contacts   | phone_number | phone         | high

Fix: SELECT pgcomply.register_pii('contacts', 'phone_number', 'phone', 'contact_id');

Clear, actionable, immediate.

Handling CI Failures

When compliance checks fail in CI, the pipeline should:

1. Block the merge — the PR cannot be merged until compliance passes
2. Show actionable output — developers need to know what to fix
3. Provide a fix path — link to documentation or auto-suggest fixes

Example GitHub Actions output on failure:

❌ Compliance check FAILED

  DRIFT DETECTED:
    - new_column: orders.customer_phone (suspected PII: phone)
    - new_column: users.backup_email (suspected PII: email)

  ACTION REQUIRED:
    Register new PII columns before merging:
      SELECT pgcomply.register_pii('orders', 'customer_phone', 'phone', 'user_id');
      SELECT pgcomply.register_pii('users', 'backup_email', 'email', 'user_id');

    Then re-run: SELECT pgcomply.schema_drift();

Multi-Environment Strategy

Different environments need different compliance strictness:

# .github/workflows/compliance.yml
jobs:
  staging:
    # Warn but don't fail
    env:
      COMPLIANCE_MODE: warn
    steps:
      - run: |
          result=$(psql "$STAGING_URL" -t -c "SELECT count(*) FROM pgcomply.schema_drift();")
          if [ "$result" -gt 0 ]; then
            echo "::warning::Schema drift detected in staging"
          fi

  production:
    # Fail on any finding
    env:
      COMPLIANCE_MODE: strict
    steps:
      - run: |
          drift=$(psql "$PROD_URL" -t -c "SELECT count(*) FROM pgcomply.schema_drift();")
          fails=$(psql "$PROD_URL" -t -c "SELECT count(*) FROM pgcomply.health_check() WHERE status = 'FAIL';")
          if [ "$drift" -gt 0 ] || [ "$fails" -gt 0 ]; then
            echo "::error::Compliance check failed"
            exit 1
          fi

Pre-Commit Hooks

Catch issues before they reach CI:

# .git/hooks/pre-push
#!/bin/bash
echo "Running compliance pre-check..."
drift=$(psql "$DEV_DATABASE_URL" -t -c "SELECT count(*) FROM pgcomply.schema_drift();" 2>/dev/null)
if [ "$drift" -gt 0 ]; then
  echo "⚠️  Schema drift detected. Run: SELECT * FROM pgcomply.schema_drift();"
  echo "Register new PII columns before pushing."
  exit 1
fi

Summary

CI/CD compliance integration catches issues before they reach production. pgcomply's lightweight metadata checks add under 2 seconds to your pipeline while preventing compliance gaps that cost hours to fix later. Treat compliance failures like test failures — block the deploy until they are resolved.