p24-infra Docs

infra-operations-audit-operations

6 min read

Infra Operations Audit Log — Operations Reference

Table: public.infra_operations
Purpose: Central audit log for all p24-infra operational events — credential rotations, deployments, restarts, config changes, migrations, archival runs.
Retention: 8 weeks rolling (older rows archived to Wasabi infra-ops/YYYY-MM.jsonl by infra_ops_archive audit action).
Grafana dashboard: infra-ops-audit-v1 → monitoring/grafana/provisioning/dashboards/infra-operations-audit.json
Implements: issue #1553

Schema

ColumnTypeRequiredDescription
idUUIDautoPrimary key — gen_random_uuid()
tsTIMESTAMPTZautoTimestamp (UTC) — defaults to NOW()
actorTEXTyesWho performed the operation: claude, radieu, ci, n8n, etc.
op_typeTEXTyesType of operation (see enum below)
resourceTEXTyesAffected resource name, e.g. GRAFANA_ADMIN_PASSWORD, monitoring-stack, bms-4
resultTEXTyessuccess, failed, or skipped
detailTEXTnoFree-text description — max ~1000 chars. MUST NOT contain secret values.
envTEXTnoTarget environment: vps-i1, bms-4, vps-h1, bms-1…bms-3, ci, local
gh_issueINTnoRelated GitHub issue number
gh_run_urlTEXTnoGitHub Actions run URL

op_type enum (non-exhaustive)

ValueWhen to use
credential_rotationRotating any secret, API key, or password
deployDeploying a service, pushing new secrets to a server
restartRestarting a Docker container or service
config_changeModifying config files, docker-compose.yml, Caddyfile, etc.
sshSignificant SSH session (e.g. server hardening, key installation)
migrationRunning a database migration
archivalMoving old rows to long-term storage
otherAnything else worth auditing

Access Control

  • service_role — full INSERT/SELECT/UPDATE/DELETE (used by scripts, CI, audit-engine)
  • grafana_readonly — SELECT only (Grafana dashboards)
  • anon / authenticated roles — DENIED (no policy)

How to Write a Log Entry

Python (scripts or audit-engine)

# Import from the project lib
import sys
sys.path.insert(0, '/opt/p24-infra')
from scripts.lib.log_op import log_op
 
log_op(
    actor="claude",
    op_type="credential_rotation",
    resource="GRAFANA_ADMIN_PASSWORD",
    result="success",
    detail="Rotated via credential-rotation.yml; bcrypt updated in Caddyfile",
    env="vps-i1",
    gh_issue=1553,
    gh_run_url="https://github.com/radieu/p24-infra/actions/runs/12345",
)

Bash (shell scripts on Linux)

source /opt/p24-infra/scripts/lib/log_op.sh
 
log_op "claude" "restart" "monitoring-caddy-1" "success" \
  "Restarted Caddy after Caddyfile update" "vps-i1"

GitHub Actions (inline Python step)

- name: Log operation
  continue-on-error: true  # MANDATORY — never block deploy on logging failure
  env:
    SUPABASE_URL: ${{ secrets.SUPABASE_URL }}
    SUPABASE_SERVICE_KEY: ${{ secrets.SUPABASE_SERVICE_KEY }}
  run: |
    python3 - <<PYEOF
    import os, json, urllib.request, ssl
    url = os.environ["SUPABASE_URL"].rstrip("/")
    key = os.environ["SUPABASE_SERVICE_KEY"]
    payload = json.dumps({
        "actor": "ci",
        "op_type": "deploy",
        "resource": "monitoring-stack",
        "result": "success",
        "detail": "secrets-sync.yml: deployed monitoring secrets to vps-i1",
        "env": "vps-i1",
        "gh_run_url": "https://github.com/${{ github.repository }}/actions/runs/${{ github.run_id }}",
    }).encode()
    req = urllib.request.Request(f"{url}/rest/v1/infra_operations", data=payload, method="POST",
        headers={"Content-Type": "application/json", "apikey": key, "Authorization": f"Bearer {key}", "Prefer": "return=minimal"})
    try:
        urllib.request.urlopen(req, timeout=3, context=ssl.create_default_context())
    except Exception as e:
        print(f"log_op: {e}")
    PYEOF

Required Environment Variables

Both wrappers (log_op.py and log_op.sh) read from environment:

VariableWhere to get it
SUPABASE_URLsecrets/monitoring.env.sops → SUPABASE_URL
SUPABASE_SERVICE_ROLE_KEYsecrets/monitoring.env.sops → SUPABASE_SERVICE_ROLE_KEY — preferred, checked first
SUPABASE_SERVICE_KEYsecrets/monitoring.env.sops → SUPABASE_SERVICE_KEY — legacy fallback, only used if SUPABASE_SERVICE_ROLE_KEY is unset. Both wrappers currently resolve SUPABASE_SERVICE_ROLE_KEY first, then fall back to SUPABASE_SERVICE_KEY — same underlying credential either way (verified identical value in secrets/monitoring.env.sops, 2026-08-02).

On Linux VPSes, these are available in /opt/p24-infra/bms-4/.env (bms-4) and /opt/p24-infra/monitoring/.env (vps-i1). As of 2026-08-02, /opt/p24-infra/bms-4/.env carries SUPABASE_SERVICE_ROLE_KEY only (no SUPABASE_SERVICE_KEY entry) — this is fine given the fallback order above, but do not assume both names are always present on every host.

Troubleshooting a 401 from PostgREST when calling log_op: see docs/playbooks/supabase-postgrest-jwt-auth-fix.md — specifically the 2026-08-02 correction. The sb_secret_* key format is rejected by Supabase’s gateway when the calling client’s User-Agent looks like a browser (e.g. PowerShell’s Invoke-WebRequest/Invoke-RestMethod default UA contains Mozilla). log_op.py (urllib.request) and log_op.sh (curl, no -A override) both send non-browser User-Agents and are verified working against the current key (live-tested via log_op.sh on bms-4, 2026-08-02) — do not diagnose this class of 401 using Invoke-WebRequest/Invoke-RestMethod without an explicit non-browser -UserAgent, or you will get a false “key is broken” reading.

Retention and Archival

  • Active table: rows older than 8 weeks are archived by the infra_ops_archive audit-engine action.
  • Archive destination: Wasabi p24-infra bucket, path infra-ops/YYYY-MM.jsonl (JSON Lines format, one row per line).
  • Archive schedule: weekly, Sunday 02:00 UTC (defined in audit.actions).
  • After archival: rows are DELETEd from Supabase to keep the table small and queries fast.

Secret Safety Rules

The detail field MUST NOT contain secret values. Both wrappers validate the field against common secret patterns before sending:

  • Long hex strings (32+ chars)
  • Long base64 strings (40+ chars)
  • Patterns like PASSWORD=<value>, TOKEN=<value>
  • Anthropic API key prefixes (sk-ant-)
  • Supabase new-format keys (sb_secret_, sb_publishable_)

If the pattern is matched, the detail is replaced with [REDACTED] and a warning is printed to stderr.

Indexes

IndexPurpose
idx_infra_operations_tsFast ORDER BY ts DESC queries (dashboard table panel)
idx_infra_operations_actor_tsFilter by actor over time
idx_infra_operations_op_type_tsFilter by op_type over time (bar chart)

Migration

Applied by: monitoring/supabase/migrations/20260627_1553_infra_operations.sql

See Also

  • scripts/lib/log_op.py — Python wrapper
  • scripts/lib/log_op.sh — Bash wrapper
  • audit-engine/actions/infra_ops_archive.py — archival action
  • monitoring/grafana/provisioning/dashboards/infra-operations-audit.json — Grafana dashboard
  • scripts/backfill-infra-ops-from-rotation-log.py — one-time backfill from rotation log

Mandatory Coverage

All playbooks that perform infra operations (SSH, Docker, config changes, credential rotations, deployments, restarts, migrations) MUST include a log_op() call. This section lists the mandatory playbooks and their required op_type.

Updated by issue #2730 (2026-07-05); extended by #3339 / Batch L (2026-07-08) to cover sops-edit-operations.md, credential-rotation-policy.md, and the server-operation.md task playbook. Each playbook listed below has an “Audit Log — Log to infra_operations” section (or, for policy/task playbooks, a mandatory-audit clause) with copy-paste-ready code.

Note on gh_issue: The value gh_issue=2730 in the snippets below is the issue that established this coverage requirement. When executing an operation, replace gh_issue with the current working issue number if one exists (e.g. the rotation issue, incident issue, or deploy issue). If there is no related issue, omit the parameter entirely.

Note on result: result should be "success" on completion, "failed" if the operation errored out before finishing, or "skipped" if it was intentionally bypassed (e.g. rotation deferred, no-op detected).

Credential Rotations — op_type: credential_rotation

PlaybookResource
docs/playbooks/anthropic-api-key-rotation.mdANTHROPIC_API_KEY
docs/playbooks/brand-credential-rotation.mdbrand-credentials
docs/playbooks/clickup-api-key-rotation.mdCLICKUP_API_KEY
docs/playbooks/cloudflare-credential-rotation.mdCF_GLOBAL_API_KEY / CLOUDFLARE_TOKEN_ZINTEGROWANA
docs/playbooks/github-pat-rotation.mdGITHUB_PAT_ALL_WRITES
docs/playbooks/gitlab-token-rotation.mdGITLAB_TOKEN
docs/playbooks/heygen-api-key-rotation.mdHEYGEN_API_KEY
docs/playbooks/hstgr-n8n-mcp-token-rotation.mdHSTGR_N8N_MCP_TOKEN
docs/playbooks/ionos-api-token-rotation.mdIONOS_API_TOKEN
docs/playbooks/mailgun-api-key-rotation.mdMAILGUN_API_KEY
docs/playbooks/mezmo-key-rotation.mdMEZMO_INGESTION_KEY
docs/playbooks/mongodb-admin-password-recovery.mdMONGODB_RS0_ADMIN_PASSWORD
docs/playbooks/mongodb-credential-rotation.mdMONGODB_RS0_ADMIN_PASSWORD
docs/playbooks/mysql-root-password-reset.mdMYSQL_ROOT_PASSWORD
docs/playbooks/n8n/n8n-bms4-api-key-rotation.mdBMS4_N8N_API_KEY
docs/playbooks/n8n/n8n-cloud-api-key-rotation.mdN8N_CLOUD_API_KEY
docs/playbooks/n8n/n8n-db-password-rotation.mdN8N_DB_PASSWORD
docs/playbooks/n8n/n8n-supabase-credential-rotation.mdN8N_SUPABASE_CREDENTIAL
docs/playbooks/n8n/n8n-waha-monitor-401-credential-drift.mdWAHA_CONTROL_TOKEN
docs/playbooks/nexcon-api-key-rotation.mdNEXCON_API_KEY
docs/playbooks/openai-key-management.mdOPENAI_API_KEY
docs/playbooks/resend-api-key-rotation.mdRESEND_API_KEY
docs/playbooks/rotate-discord-bot-token.mdDISCORD_BOT_TOKEN
docs/playbooks/sentry-token-rotation.mdSENTRY_TOKEN
docs/playbooks/ssh-key-rotation.mdSSH_KEY
docs/playbooks/static-api-key-incident-rotation.mdEXPOSED_KEY
docs/playbooks/supabase-access-token-rotation.mdSUPABASE_ACCESS_TOKEN
docs/playbooks/supabase-service-key-rotation.mdSUPABASE_SERVICE_KEY
docs/playbooks/telegram-bot-token-rotation.mdTELEGRAM_BOT_TOKEN
docs/playbooks/traccar-admin-key-rotation.mdTRACCAR_ADMIN_KEY
docs/playbooks/v42-prod-credential-rotation.mdV42_PROD_CREDENTIALS
docs/playbooks/vercel-token-rotation.mdVERCEL_TOKEN
docs/playbooks/w3-mongodb-credential-rotation.mdW3_APP_MONGODB_PASSWORD
docs/playbooks/w4-mongodb-credential-rotation.mdW4_APP_MONGODB_PASSWORD
docs/playbooks/waha-healthcheck-401-gh-secret-drift.mdWAHA_CONTROL_TOKEN
docs/playbooks/wasabi-iam-rotator.mdWASABI_IAM_KEY
docs/playbooks/wasabi-key-rotation.mdWASABI_ACCESS_KEY
docs/playbooks/secret-manager.md(per operation)
docs/playbooks/credential-rotation-policy.md(per operation — §5a)
docs/playbooks/sops-edit-operations.md(per SOPS file / key edited)

Server Restarts / Deployments

PlaybookResourceop_type
docs/playbooks/adding-new-worker.mdclaude-workerdeploy
docs/playbooks/dispatcher-keyerror-stall.mdmeta-dispatcherrestart
docs/playbooks/dispatcher-metadata-json-crash.mdmeta-dispatcherrestart
docs/playbooks/dispatcher-oom-loop.mdmeta-dispatcherrestart
docs/playbooks/gh-runner-restart-offline.mdgh-runner-ionosrestart
docs/playbooks/gitlab-runner-bms1-reregister.mdgitlab-runner-bms1deploy
docs/playbooks/hourly-triage-outage.mdhourly-triagerestart
docs/playbooks/hourly-triage-watchdog.mdhourly-triage-watchdogrestart
docs/playbooks/monitoring-stack-operations.mdmonitoring-stackrestart
docs/playbooks/monitoring-standby-vps-h1.mdmonitoring-standby-vps-h1deploy
docs/playbooks/n8n/n8n-bms4-worker-crash-storm.mdn8n-bms4-workerrestart
docs/playbooks/nc-alert-dispatch-outage.mdnc-alert-dispatchrestart
docs/playbooks/oom-auto-remediation.mdoom-targetrestart
docs/playbooks/pinbox24-bms1-manual-deploy.mdpinbox24-bms1deploy
docs/playbooks/pinbox24-container-runtime-hotfix.mdpinbox24-bms1deploy
docs/playbooks/provision-bms-3-dispatch-node.mdbms-3-dispatch-nodedeploy
docs/playbooks/socat-supabase-watchdog.mdsocat-supabaserestart
docs/playbooks/socat-supabase-zombie-crash-loop.mdsocat-supabaserestart
docs/playbooks/v42-prod-memory-leak.mdv42-prodrestart
docs/playbooks/vps-h1-stale-docker-iptables.mdvps-h1-dockerconfig_change
docs/playbooks/vps-h1-traefik-iptables-stale-rules.mdtraefik-vps-h1config_change
docs/playbooks/vps-i1-crash-loop-recovery.mdvps-i1restart
docs/playbooks/vps-i1-oom-outage.mdvps-i1restart
docs/playbooks/vps-i1-outage.mdvps-i1restart
docs/playbooks/waha-ingestion-failure.mdwaha-ingestionrestart
docs/playbooks/waha-session-stopped-after-reboot.mdwaha-sessionrestart
docs/playbooks/waha-traefik-down-ports-80-443.mdtraefik-waharestart

Config Changes / Database Operations

PlaybookResourceop_type
docs/playbooks/bms-server-root-ssh-lockout-recovery.mdbms-root-sshconfig_change
docs/playbooks/cross-server-ssh-key-standard.mdssh-authorized-keysconfig_change
.claude/task-playbooks/server-operation.md(per server op)restart / deploy / config_change / ssh
docs/playbooks/incident-management.md(per incident)other
docs/playbooks/mongodb-exporter-uri-special-chars.mdmongodb-exporterconfig_change
docs/playbooks/mongodb-rs-heartbeat-block.mdmongodb-rs0config_change
docs/playbooks/mongodb-rs0-full-restore.mdmongodb-rs0other
docs/playbooks/mongodb-rs0-heartbeat-drop-block.mdmongodb-rs0config_change
docs/playbooks/mongodb-slow-queries-index.mdmongodb-rs0config_change
docs/playbooks/gh-secret-sops-drift.mdgh-secret-sopsconfig_change
docs/playbooks/n8n/n8n-bms4-exporter-missing-key.mdn8n-bms4-exporterconfig_change
docs/playbooks/n8n/n8n-bms4-stuck-execution-cleanup.mdn8n-bms4-executionsother
docs/playbooks/n8n/n8n-crashed-executions-db-connection-drop.mdn8n-bms4restart
docs/playbooks/n8n/n8n-workflow-db-query-hang.mdn8n-bms4-dbrestart
docs/playbooks/pinbox24-no-logs-bms1.mdpinbox24-bms1config_change
docs/playbooks/pinbox24-s3-wasabi-bms1.mdpinbox24-s3-wasabiconfig_change
docs/playbooks/supabase-backup-stale.mdsupabase-backupother
docs/playbooks/supabase-migrations.mdsupabase-schemamigration
docs/playbooks/supabase-migrations-ci.mdsupabase-schemamigration
docs/playbooks/vps-h1-cpu-throttle.mdvps-h1config_change
docs/playbooks/wasabi-bucket-growth-spike.mdwasabi-bucketother

When in the Playbook Flow

  • Credential rotations: log AFTER the new credential is verified working and all consumers updated, BEFORE closing the session.
  • Restarts / deploys: log AFTER the service is confirmed healthy (uptime check passed).
  • Config changes: log AFTER the change is applied and verified.
  • Incident response: log at the END of the incident after the root cause is resolved.
  • Migrations: log AFTER the migration succeeds and is confirmed on the live DB.

Rules for the detail Field

  1. NEVER include secret values — the wrapper will redact them to [REDACTED] but do not rely on this.
  2. Include the trigger reason: “Scheduled 180d rotation”, “Incident response nnnn”, “Drift detected”, etc.
  3. Max ~200 chars for readability in the Grafana table panel.
  4. Reference the GitHub issue number in gh_issue — not in detail.

Graph View