Playbook: Secret-Manager Rotation Log

Table: dev_r_rotation_log (Supabase mwkqmgadqnkkihjdeqsi) Write path: scripts/rotation-log-entry.py (CLI) / scripts/lib/rotation_log.py (Python) Offline fallback: docs/rotation-log-fallback.md

The unified rotation log

Supabase dev_r_rotation_log is the single, authoritative rotation log for everyone — desktop Claude sessions and VPS/BMS worker agents alike. There is no longer a separate hand-edited markdown track. Every credential rotation event across the p24-infra ecosystem is recorded here.

Agents never hand-edit any rotation-log markdown file in normal operation. The only markdown file in play is docs/rotation-log-fallback.md, and it is written by the script, not by a human, and only when the Supabase API is unreachable (see §Offline fallback below).

Each row captures:

Column	Type	Description
`id`	UUID	Auto-generated row identifier
`secret_name`	TEXT	Key name being rotated (never the value)
`reason`	TEXT	Free-text reason / issue reference
`reason_category`	TEXT	Optional coarse label — `scheduled` / `exposure` / `preventive` / `manual` — for reporting only
`rotation_type`	TEXT	Optional — `manual` / `auto` / `playwright`
`rotator`	TEXT	`"claude"`, `"human"`, a role name, or agent name
`status`	TEXT	`pending` → `completed` or `failed`; `human-action` for rows awaiting a human
`log_lines`	TEXT	Full text log of rotation steps
`error_message`	TEXT	Error description on failure
`sops_commit`	TEXT	Git commit SHA of the SOPS update
`gh_secret`	TEXT/BOOL	GH Secret name updated (or whether one was)
`verify_result`	TEXT	Post-rotation verification outcome
`created_at`	TIMESTAMPTZ	Row creation time (pending opened)
`completed_at`	TIMESTAMPTZ	Time the row was closed (completed/failed)
`rotated_at`	TIMESTAMPTZ	Optional explicit rotation time

Why it matters:

Grafana dashboard surfaces stalled rotations and recent failures
Alertmanager fires on stalled rows (status='pending' AND created_at < NOW() - 2h)
Provides the audit trail for compliance and incident review

The write path — `scripts/rotation-log-entry.py`

This is the canonical CLI every rotation must call. Open a pending entry before touching the key; close it after distribution.

Lifecycle — which entry point to use

The correct first call depends on whether the rotation performs a live server-side write:

Rotation kind	Entry point	Lifecycle
Live-write — pairs a SOPS write with a non-idempotent live write (MongoDB `changeUserPassword`/`updateUser`, Wasabi/S3 IAM create/rotate, a GitHub-PAT/provider “regenerate”, any provider API that mints a value in place)	`acquire` (ADR 004)	`acquire` → (Layer-2 `check` immediately before the live write) → `close`/`fail`
SOPS-only — new key, non-live config edit, re-encrypt, distribution-only sync (no live server-side write)	plain `open`	`open` → `close`/`fail`

acquire opens the pending row and takes the per-(repo, secret_name) advisory lock in one fail-closed step, so a live-write rotation calls acquire instead of open, never both. A SOPS-only edit is git-mergeable (a second author just resolves a normal PR conflict), so per ADR 004 it deliberately stays on plain open — do not gate SOPS-only work behind acquire. See the acquire/check verb reference in §The write path below.

# Open a pending rotation entry — prints the row id to stdout
# (a uuid, or "fallback:<uuid>" if the API was down):
ROW_ID=$(python3 scripts/rotation-log-entry.py open --secret KEY_NAME \
    [--reason "free text / #issue"] [--rotator NAME] [--repo p24-infra] \
    [--reason-category scheduled|exposure|preventive|manual] \
    [--rotation-type manual|auto|playwright])
 
# Close it as completed:
python3 scripts/rotation-log-entry.py close "$ROW_ID" \
    [--log-lines TEXT] [--sops-commit SHA] [--gh-secret-name NAME] \
    [--verify-result TEXT] [--rotated-at ISO8601]
 
# Or mark it failed:
python3 scripts/rotation-log-entry.py fail "$ROW_ID" [--error TEXT]
 
# Drain any offline fallback entries back into Supabase (idempotent):
python3 scripts/rotation-log-entry.py reimport [--dry-run]
 
# Inspect recent / pending rows:
python3 scripts/rotation-log-entry.py list [--limit N] [--pending-only] [--repo NAME]
 
# ── Advisory-lock verbs (ADR 004, #5987/PR #5994) — LIVE-WRITE rotations only ──
# Acquire the per-(repo,secret) advisory lock BEFORE a live-write rotation. Prints the lock row id
# (which IS a `pending` row — use it in place of an `open` id for close/fail). fail-CLOSED exit codes:
#   0 acquired · 3 denied (another session holds it → STOP, reconcile) · 4 could-not-verify (DB down)
LOCK_ID=$(python3 scripts/rotation-log-entry.py acquire --secret KEY_NAME [--repo NAME] \
    [--reason "#issue"] [--rotator NAME] [--reason-category CAT] [--rotation-type TYPE] \
    [--sops-file PATH] [--force])
 
# Layer-2 pre-write re-check — run immediately before the non-idempotent live write. Exit:
#   0 still-held (safe to write) · 3 not-held (reclaimed/stolen → abort the write, reconcile)
python3 scripts/rotation-log-entry.py check "$LOCK_ID"

reason is free text; reason_category is a coarse label for reporting only. Status values: pending / completed / failed / human-action.

acquire/check vs open/close/fail — the fail-open/fail-closed split. The logging verbs (open/close/fail/reimport/list/status) are fail-open: they always exit 0 so logging can never block a rotation. The lock verbs (acquire/check) are fail-closed: they exit non-zero on denial/not-held/unverified so the caller halts. acquire opens the pending row itself — it is the open for a live-write rotation, so never pair it with a separate open for the same rotation. --force on acquire is an emergency override for the could-not-verify (rc=4) case only — a genuine held lock still blocks it. Full design: ADR 004.

Python (inline rotation scripts)

import sys; sys.path.insert(0, '/opt/p24-infra')
from scripts.lib.rotation_log import open_rotation, close_rotation, fail_rotation
 
# 1. Open BEFORE touching any key
row_id = open_rotation(
    "GRAFANA_ADMIN_PASSWORD",
    reason="#2620 incident — password exposed in Mezmo log",
    rotator="claude",
)
 
try:
    # 2. Do the actual rotation work
    # ... SOPS edit, secrets-sync, container restart, verification ...
 
    # 3. Close on success
    close_rotation(
        row_id,
        log_lines="SOPS updated, synced to vps-i1, Grafana restart OK",
        sops_commit="abc1234",        # git rev-parse HEAD after SOPS commit
        verify_result="Grafana login confirmed with new password",
    )
 
except Exception as e:
    fail_rotation(row_id, error_message=str(e))
    raise

Fail-open: if Supabase is unreachable at open, row_id is a fallback:<uuid> sentinel (or None for the Python helpers) written to docs/rotation-log-fallback.md; the close/fail calls correlate against the same fallback entry. Logging never blocks the rotation.

Both files read SUPABASE_URL + SUPABASE_SERVICE_KEY from the environment.

Offline fallback — `docs/rotation-log-fallback.md`

When the Supabase API is unreachable, rotation-log-entry.py (and the Python helpers) transparently write the entry to docs/rotation-log-fallback.md instead, and return a fallback:<uuid> id. A later close/fail with that id updates the same file entry. Never hand-edit this file — it is a machine-managed spill buffer, not a human audit log.

Draining fallback entries back into Supabase

On the next successful API call the script drains pending fallback entries automatically (opportunistic reimport). You can also drain explicitly:

python3 scripts/rotation-log-entry.py reimport            # idempotent — safe to re-run
python3 scripts/rotation-log-entry.py reimport --dry-run  # preview what would be imported

A daily reimport-check cron is the safety-net backstop, in case an opportunistic drain never runs (e.g. no further rotations occur on that host).

Committing a fallback write so the cron can see it

A fallback write only reaches the reimport cron if the file lands in git. When a rotation produced a fallback entry, stage docs/rotation-log-fallback.md in the rotation’s own SOPS commit — i.e. git add secrets/<file>.env.sops docs/rotation-log-fallback.md — but only when the file has pending (uncommitted) entries. For a rotation with no SOPS commit to piggyback on, open a small dedicated PR containing just the fallback-file update. See secret-manager.md §Handoff and static-api-key-incident-rotation.md Step 3 for the exact staging step in each flow.

Querying pending entries

python3 scripts/rotation-log-entry.py list --pending-only

SQL (Grafana / Alertmanager alert pattern):

SELECT
    id, secret_name, reason, rotator, created_at,
    EXTRACT(EPOCH FROM (NOW() - created_at)) / 60 AS minutes_pending
FROM dev_r_rotation_log
WHERE status = 'pending'
  AND created_at < NOW() - INTERVAL '2 hours'
ORDER BY created_at ASC;

Via REST API (for script checks):

curl -s "${SUPABASE_URL}/rest/v1/dev_r_rotation_log" \
  -H "apikey: ${SUPABASE_KEY}" \
  -H "Authorization: Bearer ${SUPABASE_KEY}" \
  -G \
  --data-urlencode "status=eq.pending" \
  --data-urlencode "created_at=lt.$(date -u -d '2 hours ago' +%Y-%m-%dT%H:%M:%SZ)" \
  --data-urlencode "select=id,secret_name,reason,rotator,created_at"

Escalation: stalled rotations

A rotation row is considered stalled when:

status = 'pending'
AND created_at < NOW() - INTERVAL '2 hours'

The definition keys on status='pending' specifically, so human-action rows — a distinct status for rotations deliberately parked awaiting a human — are already excluded and never trigger the stalled alert.

What this means: the rotation was started but never closed. Possible causes:

Worker crashed mid-rotation
Script exited without calling close / fail (or close_rotation / fail_rotation)
Supabase was unreachable when closing (but rotation completed — so actual credential state is unknown)

Escalation steps:

Check the GH issue linked in reason — is there a PR or comment indicating the rotation completed?
If rotation completed but logging failed: close the pending row with the real outcome (rotation-log-entry.py close "$ROW_ID" --verify-result "..."), or drain the fallback file if the entry lives there (rotation-log-entry.py reimport).
If rotation status is genuinely unknown: treat as potentially partial rotation:
- Verify current key works in all consumers (service health checks)
- If consumers reject the key: the rotation failed — re-rotate immediately
- If consumers accept: rotation succeeded — close the row with rotator="human" and a note
If a worker crashed: check Mezmo logs for the worker session around created_at
Update the stalled row: rotation-log-entry.py fail "$ROW_ID" --error "stalled — manually resolved at <timestamp>" (or PATCH /dev_r_rotation_log?id=eq.{uuid} with status=failed + error_message).

Alertmanager integration: If Alertmanager fires the RotationStalled alert, follow this playbook. The alert fires when count(*) WHERE status='pending' AND created_at < NOW() - 2h > 0.

Module reference

scripts/lib/rotation_log.py — Pure stdlib Python 3, no pip deps. Writes Supabase; falls back to docs/rotation-log-fallback.md when the API is unreachable.

Function	Signature	Returns
`open_rotation`	`(secret_name, *, reason="", rotator="claude", ...)`	`str \| None` — UUID / `fallback:<uuid>` / None
`close_rotation`	`(row_id, *, log_lines=None, sops_commit=None, gh_secret=None, verify_result=None)`	`None`
`fail_rotation`	`(row_id, *, error_message="")`	`None`
`acquire_lock`	`(secret_name, *, repo="p24-infra", reason="", rotator="claude", rotation_type="manual", ..., force=False)`	`AcquireResult(outcome, row_id, message)` — `outcome` ∈ `acquired`/`denied`/`unverified` (fail-CLOSED)
`holds_lock`	`(row_id)`	`bool` — Layer-2 pre-write re-check (still-held?)

scripts/rotation-log-entry.py — CLI wrapper. Logging subcommands (open, close, fail, reimport, list, status) are fail-open (always exit 0). Lock subcommands (acquire, check — ADR 004) are fail-closed: acquire → 0 acquired / 3 denied / 4 unverified; check → 0 held / 3 not-held. Reads SUPABASE_URL + SUPABASE_SERVICE_KEY from the environment.

Related:

docs/adr/004-credential-rotation-mutual-exclusion.md — the two-layer advisory-lock design (acquire/check)
docs/playbooks/secret-manager.md — full rotation operating manual
docs/playbooks/static-api-key-incident-rotation.md — incident rotation procedure
docs/rotation-log-fallback.md — offline fallback spill buffer (machine-managed; never hand-edit)
Migration 046 — dev_r_rotation_log schema + Grafana alert rules

p24-infra Docs

Explorer

secret-manager-rotation-log

Playbook: Secret-Manager Rotation Log

The unified rotation log

The write path — `scripts/rotation-log-entry.py`

Lifecycle — which entry point to use

Python (inline rotation scripts)

Offline fallback — `docs/rotation-log-fallback.md`

Draining fallback entries back into Supabase

Committing a fallback write so the cron can see it

Querying pending entries

Escalation: stalled rotations

Module reference

Graph View

Table of Contents

Backlinks

p24-infra Docs

Explorer

secret-manager-rotation-log

Playbook: Secret-Manager Rotation Log

The unified rotation log

The write path — scripts/rotation-log-entry.py

Lifecycle — which entry point to use

Python (inline rotation scripts)

Offline fallback — docs/rotation-log-fallback.md

Draining fallback entries back into Supabase

Committing a fallback write so the cron can see it

Querying pending entries

Escalation: stalled rotations

Module reference

Graph View

Table of Contents

Backlinks

The write path — `scripts/rotation-log-entry.py`

Offline fallback — `docs/rotation-log-fallback.md`