p24-infra Docs

agent-session-audit-operations

4 min read

Agent Session Audit — Operations Reference

Table: dev_r_agent_sessions Supabase project: mwkqmgadqnkkihjdeqsi Migration: supabase/migrations/027_dev_r_agent_sessions.sql See also: agent-session-audit.md — full spec, lifecycle, Grafana panels

What the table records

Every Claude Code session that runs in the p24-infra ecosystem writes one row to dev_r_agent_sessions. This provides a complete audit trail:

  • Which worker ran (IO1, BMS4-1, local)
  • Which environment (vps-i1, bms-4, local)
  • Which skill was invoked
  • Which issues were touched and which PR was opened
  • Parent → subagent hierarchy (full tree via self-referencing FK)
  • Session duration and outcome (in_progress / completed / failed)

Schema field reference

ColumnTypeNullableDefaultDescription
idUUIDNOgen_random_uuid()Primary key
session_idTEXTNO—Claude Code session ID (unique)
parent_session_idTEXTYESNULLFK → session_id; NULL for top-level sessions
typeTEXTNO'worker''worker' (top-level) or 'subagent' (spawned via Agent tool)
workerTEXTYESNULLWorker label: IO1, BMS4-1, local (from P24_WORKER_LABEL)
environmentTEXTYESNULLHost: vps-i1, bms-4, local (from P24_ENVIRONMENT)
skill_invokedTEXTYESNULLSkill name if known: hourly-devops-triage, issues-review, worker-issue
branchTEXTYESNULLGit branch worked on; set at session end
issues_workedINT[]YESNULLArray of GH issue numbers touched
pr_createdINTYESNULLPR number opened, if any
summaryTEXTYESNULLOne-paragraph summary written at session end
statusTEXTNO'in_progress''in_progress', 'completed', 'failed'
started_atTIMESTAMPTZNONOW()Session start timestamp
ended_atTIMESTAMPTZYESNULLSession end timestamp; set by Stop/StopFailure hook
tagsTEXT[]YESNULLFree-form tags for filtering

How sessions are registered

Automatic (hooks — preferred)

The .claude/settings.json hooks fire on every Claude Code session:

"hooks": {
  "SessionStart": [{ "type": "command", "command": "bash .claude/hooks/agent-session-start.sh" }],
  "Stop":         [{ "type": "command", "command": "bash .claude/hooks/agent-session-end.sh completed" }],
  "StopFailure":  [{ "type": "command", "command": "bash .claude/hooks/agent-session-end.sh failed" }]
}

The hooks call scripts/agent-session-start.py and scripts/agent-session-end.py, which read environment variables to populate the row.

Required env vars (set in .claude/settings.json env block per host):

Variablevps-i1bms-4local
P24_WORKER_LABELIO1BMS4-1local
P24_ENVIRONMENTvps-i1bms-4local

No secrets — safe to commit in settings.json.

Manual INSERT

For backfilling or scripted one-off records:

INSERT INTO dev_r_agent_sessions (session_id, type, worker, environment, skill_invoked, status)
VALUES ('manual-2026-06-23', 'worker', 'local', 'local', 'hourly-devops-triage', 'completed');

Example queries

All sessions for a specific issue

SELECT session_id, type, worker, environment, skill_invoked, status, started_at, ended_at, summary
FROM dev_r_agent_sessions
WHERE issues_worked @> ARRAY[723]
ORDER BY started_at DESC;

All sessions by a specific worker in the last 24 hours

SELECT session_id, skill_invoked, branch, status,
       started_at,
       ended_at - started_at AS duration,
       summary
FROM dev_r_agent_sessions
WHERE worker = 'IO1'
  AND started_at > NOW() - INTERVAL '24 hours'
ORDER BY started_at DESC;

Parent → child session tree

WITH RECURSIVE session_tree AS (
  -- Anchor: top-level sessions
  SELECT id, session_id, parent_session_id, type, worker, skill_invoked, status, 0 AS depth
  FROM dev_r_agent_sessions
  WHERE parent_session_id IS NULL
 
  UNION ALL
 
  -- Recursive: subagents
  SELECT s.id, s.session_id, s.parent_session_id, s.type, s.worker, s.skill_invoked, s.status,
         st.depth + 1
  FROM dev_r_agent_sessions s
  JOIN session_tree st ON s.parent_session_id = st.session_id
)
SELECT depth, repeat('  ', depth) || type AS level, session_id, worker, skill_invoked, status
FROM session_tree
ORDER BY depth, session_id;

Failed sessions in the last 7 days

SELECT session_id, worker, environment, skill_invoked, started_at, summary
FROM dev_r_agent_sessions
WHERE status = 'failed'
  AND started_at > NOW() - INTERVAL '7 days'
ORDER BY started_at DESC;

Average session duration by worker

SELECT worker,
       COUNT(*) AS total,
       AVG(ended_at - started_at) AS avg_duration,
       COUNT(*) FILTER (WHERE status = 'completed') AS completed,
       COUNT(*) FILTER (WHERE status = 'failed') AS failed
FROM dev_r_agent_sessions
WHERE ended_at IS NOT NULL
GROUP BY worker
ORDER BY total DESC;

Reading via Grafana

Datasource: Supabase (direct PostgreSQL, db.mwkqmgadqnkkihjdeqsi.supabase.co:5432, role: grafana_readonly)

Suggested panels:

PanelTypeQuery hint
Sessions timelineTime series / State timelinestarted_at on X, group by worker, color by status
Subagent treeTableparent_session_id IS NOT NULL, join to parent for context
Session summaryTableAll columns, last 50 rows, ordered by started_at DESC
Failed sessionsStat + AlertWHERE status = 'failed' AND started_at > NOW() - '1h'::interval

Access control

RoleAccess
service_roleFull (INSERT, UPDATE, SELECT, DELETE) — bypasses RLS by design
grafana_readonlySELECT only — via grafana_readonly_select RLS policy
anon / authenticatedDenied — no anon policies

Compliance

  • RLS: enabled (migration 027)
  • Policies: service_role_all (ALL), grafana_readonly_select (SELECT)
  • dev_r_services row: service_name = 'agent-session-audit', compliance_workbook = 'yes'
  • No secrets stored in this table — session metadata only
  • EU AI Act: this table records AI agent activity; classified as limited-risk tooling (internal audit, no user-facing decisions)

Graph View