PRD audit trail

Role

Utility command that queries the SQLite audit database (.planning/pwdev-audit.db) and presents results in readable format.

How data gets here (v2.0)

Recording is deterministic — no inline INSERTs:

• Plugin hooks (hooks/hooks.json → scripts/audit-hook.sh): session start/stop and .planning/ artifact writes, with session_id.
• Commands call scripts/audit-log.sh at milestones (event) and for configuration changes (config → config_changes table). Schema reference: <plugin-root>/references/audit-schema.md. The database is SHARED with pwdev-code/pwdev-feat — filter this plugin's rows with WHERE plugin='pwdev-prd'.

Pre-check

1. Read .planning/config.json — if audit is not true, stop with:

   • PT-BR: Trilha de auditoria nao esta ativada. Execute /pwdev-prd:init para habilitar.
   • EN: Audit trail is not enabled. Run /pwdev-prd:init to enable it.

2. Check if database exists:

   [ -f ".planning/pwdev-audit.db" ] || echo "NOT_FOUND"
   

   If NOT_FOUND, stop with:

   • PT-BR: Banco de auditoria nao encontrado em .planning/pwdev-audit.db
   • EN: Audit database not found at .planning/pwdev-audit.db

3. Verify sqlite3 is available:

   command -v sqlite3 >/dev/null 2>&1 || echo "NO_SQLITE3"
   

   If NO_SQLITE3, stop with:

   • PT-BR: sqlite3 nao encontrado. Instale o SQLite para usar este comando.
   • EN: sqlite3 not found. Install SQLite to use this command.

STEP 0 — Language

Follow <plugin-root>/references/language.md (resolve lang from .planning/config.json; ask only if unset).

STEP 1 — Parse Sub-command

Parse the arguments to determine the sub-command:

Argument	Action
(empty) or summary	Go to STEP 2 — Summary Dashboard
events	Go to STEP 3 — Event Log
decisions	Go to STEP 4 — Decision Log
artifacts	Go to STEP 5 — Artifact Tracker
stats	Go to STEP 6 — Statistics
export	Go to STEP 7 — Export PDF Report
query <SQL>	Go to STEP 8 — Custom Query

If unrecognized, show help:

## /pwdev-prd:audit

Available sub-commands:

  summary    — Dashboard with key metrics and recent activity (default)
  events     — Full event log with filters
  decisions  — All architectural/product decisions with rationale
  artifacts  — Files tracked by the framework
  stats      — Command frequency, durations, phase distribution
  export     — Generate a full PDF audit report
  query <SQL> — Run a custom SQL query against the audit database

Examples:
  /pwdev-prd:audit
  /pwdev-prd:audit events
  /pwdev-prd:audit decisions
  /pwdev-prd:audit stats
  /pwdev-prd:audit export
  /pwdev-prd:audit query "SELECT * FROM events WHERE action='failed'"

---

STEP 2 — Summary Dashboard (default)

Run the following queries:

# Total events
sqlite3 .planning/pwdev-audit.db "SELECT COUNT(*) FROM events;"

# Events by action
sqlite3 .planning/pwdev-audit.db "SELECT action, COUNT(*) as count FROM events GROUP BY action ORDER BY count DESC;"

# Last 10 events
sqlite3 -header -column .planning/pwdev-audit.db "SELECT timestamp, plugin, command, agent, action, target FROM events ORDER BY timestamp DESC LIMIT 10;"

# Total decisions
sqlite3 .planning/pwdev-audit.db "SELECT COUNT(*) FROM decisions;"

# Active artifacts count
sqlite3 .planning/pwdev-audit.db "SELECT COUNT(*) FROM artifacts WHERE status='active';"

# Failed commands (if any)
sqlite3 -header -column .planning/pwdev-audit.db "SELECT timestamp, plugin, command, detail FROM events WHERE action='failed' ORDER BY timestamp DESC LIMIT 5;"

# Config changes count
sqlite3 .planning/pwdev-audit.db "SELECT COUNT(*) FROM config_changes;"

Present as:

## Audit Trail — Summary

**Total events:** N | **Decisions:** N | **Active artifacts:** N | **Config changes:** N

### Events by Action
| Action | Count |
|--------|-------|
| completed | N |
| started | N |
| ... | ... |

### Last 10 Events
| Timestamp | Plugin | Command | Agent | Action | Target |
|-----------|--------|---------|-------|--------|--------|
| ... | ... | ... | ... | ... | ... |

### Failed Commands (last 5)
[None | table]

---

STEP 3 — Event Log

sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT id, timestamp, plugin, command, agent, model, phase, action, target,
         SUBSTR(detail, 1, 80) as detail_preview
  FROM events
  ORDER BY timestamp DESC
  LIMIT 50;
"

Present as a formatted table. If there are more than 50 events, note:

• PT-BR: Mostrando os 50 eventos mais recentes. Use /pwdev-prd:audit query para consultas customizadas.
• EN: Showing the 50 most recent events. Use /pwdev-prd:audit query for custom queries.

---

STEP 4 — Decision Log

sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT d.id, d.timestamp, d.phase, d.decision, d.rationale,
         d.alternatives, CASE d.reversible WHEN 1 THEN 'Yes' ELSE 'No' END as reversible
  FROM decisions d
  ORDER BY d.timestamp DESC;
"

Present as:

## Decisions

| # | Timestamp | Phase | Decision | Rationale | Alternatives | Reversible |
|---|-----------|-------|----------|-----------|--------------|------------|
| ... | ... | ... | ... | ... | ... | ... |

If no decisions, note:

• PT-BR: Nenhuma decisao registrada na trilha de auditoria.
• EN: No decisions recorded in the audit trail.

---

STEP 5 — Artifact Tracker

# Active artifacts grouped by type
sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT type, COUNT(*) as count
  FROM artifacts
  WHERE status='active'
  GROUP BY type
  ORDER BY count DESC;
"

# Full artifact list
sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT id, path, type, phase, status, created_at, archived_at
  FROM artifacts
  ORDER BY created_at DESC;
"

Present as:

## Artifacts

### By Type
| Type | Count |
|------|-------|
| ... | ... |

### All Artifacts
| Path | Type | Phase | Status | Created | Archived |
|------|------|-------|--------|---------|----------|
| ... | ... | ... | ... | ... | ... |

---

STEP 6 — Statistics

# Command frequency
sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT command, COUNT(*) as runs
  FROM events
  WHERE action='completed'
  GROUP BY command
  ORDER BY runs DESC;
"

# Average duration per command (when available)
sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT command, COUNT(*) as runs,
         ROUND(AVG(duration_ms)/1000.0, 1) as avg_sec,
         ROUND(MIN(duration_ms)/1000.0, 1) as min_sec,
         ROUND(MAX(duration_ms)/1000.0, 1) as max_sec
  FROM events
  WHERE duration_ms IS NOT NULL AND action='completed'
  GROUP BY command
  ORDER BY avg_sec DESC;
"

# Events by phase
sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT phase, COUNT(*) as count
  FROM events
  WHERE phase IS NOT NULL
  GROUP BY phase
  ORDER BY count DESC;
"

# Events by plugin
sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT plugin, COUNT(*) as count
  FROM events
  GROUP BY plugin
  ORDER BY count DESC;
"

# Activity timeline (events per day, last 14 days)
sqlite3 -header -column .planning/pwdev-audit.db "
  SELECT DATE(timestamp) as day, COUNT(*) as events
  FROM events
  WHERE timestamp >= datetime('now', '-14 days')
  GROUP BY day
  ORDER BY day DESC;
"

# Success rate
sqlite3 .planning/pwdev-audit.db "
  SELECT
    ROUND(100.0 * SUM(CASE WHEN action='completed' THEN 1 ELSE 0 END) / COUNT(*), 1) as success_pct,
    SUM(CASE WHEN action='completed' THEN 1 ELSE 0 END) as completed,
    SUM(CASE WHEN action='failed' THEN 1 ELSE 0 END) as failed
  FROM events
  WHERE action IN ('completed', 'failed');
"

Present as:

## Statistics

### Command Frequency
| Command | Runs |
|---------|------|
| ... | ... |

### Performance (avg duration)
| Command | Runs | Avg (s) | Min (s) | Max (s) |
|---------|------|---------|---------|---------|
| ... | ... | ... | ... | ... |

### Events by Phase
| Phase | Count |
|-------|-------|
| ... | ... |

### Events by Plugin
| Plugin | Count |
|--------|-------|
| ... | ... |

### Activity (last 14 days)
| Day | Events |
|-----|--------|
| ... | ... |

### Success Rate
**N%** (N completed / N failed)

---

STEP 7 — Export PDF Report

Generate a comprehensive audit report as PDF.

STEP 7.1 — Check Dependencies

command -v python3 >/dev/null 2>&1 && python3 -c "import weasyprint" 2>/dev/null && echo "WEASYPRINT_OK"
command -v python3 >/dev/null 2>&1 && python3 -c "import markdown" 2>/dev/null && echo "MARKDOWN_OK"
command -v pandoc >/dev/null 2>&1 && echo "PANDOC_OK"
command -v wkhtmltopdf >/dev/null 2>&1 && echo "WKHTMLTOPDF_OK"

Determine the PDF generation strategy based on available tools (in priority order):

1. pandoc (preferred) — pandoc available
2. weasyprint — python3 + weasyprint + markdown available
3. wkhtmltopdf — wkhtmltopdf available
4. None — fallback to Markdown export only

If no PDF tool is available, inform:

• PT-BR: Nenhuma ferramenta de PDF encontrada. Gerando relatorio em Markdown. Para PDF, instale: pandoc (recomendado), weasyprint, ou wkhtmltopdf.
• EN: No PDF tool found. Generating Markdown report. For PDF, install: pandoc (recommended), weasyprint, or wkhtmltopdf.

STEP 7.2 — Collect All Data

Run all queries from STEP 2 (Summary), STEP 3 (Events), STEP 4 (Decisions), STEP 5 (Artifacts), and STEP 6 (Statistics) and store results.

Additionally, collect project metadata:

# Project name (from git or directory)
basename "$(git rev-parse --show-toplevel 2>/dev/null || pwd)"

# Current date
date +"%Y-%m-%d %H:%M"

# Total time span of audit data
sqlite3 .planning/pwdev-audit.db "SELECT MIN(timestamp) || ' to ' || MAX(timestamp) FROM events;"

# Git branch
git branch --show-current 2>/dev/null

STEP 7.3 — Generate Markdown Report

Write the full report to .planning/audit-report.md:

# Audit Trail Report

**Project:** {project_name}
**Branch:** {branch}
**Generated:** {current_date}
**Period:** {first_event_date} to {last_event_date}
**Language:** {lang}

---

## 1. Executive Summary

- **Total events:** {count}
- **Total decisions:** {count}
- **Active artifacts:** {count}
- **Configuration changes:** {count}
- **Success rate:** {pct}% ({completed} completed / {failed} failed)

### Events by Action
| Action | Count |
|--------|-------|
{rows}

---

## 2. Activity Timeline (last 14 days)

| Day | Events |
|-----|--------|
{rows}

---

## 3. Event Log (last 50)

| # | Timestamp | Plugin | Command | Agent | Phase | Action | Target |
|---|-----------|--------|---------|-------|-------|--------|--------|
{rows}

---

## 4. Decisions

| # | Timestamp | Phase | Decision | Rationale | Alternatives | Reversible |
|---|-----------|-------|----------|-----------|--------------|------------|
{rows}

---

## 5. Artifacts

### By Type
| Type | Count |
|------|-------|
{rows}

### All Artifacts
| Path | Type | Phase | Status | Created |
|------|------|-------|--------|---------|
{rows}

---

## 6. Statistics

### Command Frequency
| Command | Runs |
|---------|------|
{rows}

### Performance
| Command | Runs | Avg (s) | Min (s) | Max (s) |
|---------|------|---------|---------|---------|
{rows}

### Events by Phase
| Phase | Count |
|-------|-------|
{rows}

### Events by Plugin
| Plugin | Count |
|--------|-------|
{rows}

---

## 7. Failed Commands

| Timestamp | Plugin | Command | Agent | Detail |
|-----------|--------|---------|-------|--------|
{rows}

---

## 8. Configuration Change History

| Timestamp | Field | Old Value | New Value | Changed By |
|-----------|-------|-----------|-----------|------------|
{rows}

---

*Report generated by /pwdev-prd:audit export*

STEP 7.4 — Convert to PDF

Based on the available tool detected in STEP 7.1:

Option 1 — pandoc:

pandoc .planning/audit-report.md \
  -o .planning/audit-report.pdf \
  --pdf-engine=pdflatex \
  -V geometry:margin=2cm \
  -V fontsize=10pt \
  -V mainfont="Inter" \
  --highlight-style=tango \
  2>/dev/null || \
pandoc .planning/audit-report.md \
  -o .planning/audit-report.pdf \
  -V geometry:margin=2cm \
  -V fontsize=10pt \
  2>/dev/null

If pdflatex is not available, try with wkhtmltopdf engine:

pandoc .planning/audit-report.md \
  -o .planning/audit-report.pdf \
  --pdf-engine=wkhtmltopdf \
  2>/dev/null

Option 2 — weasyprint:

python3 -c "
import markdown, weasyprint

with open('.planning/audit-report.md', 'r') as f:
    md_content = f.read()

html = markdown.markdown(md_content, extensions=['tables', 'fenced_code'])

styled_html = '''<!DOCTYPE html>
<html><head><meta charset=\"utf-8\">
<style>
  body { font-family: Inter, system-ui, sans-serif; font-size: 10pt; margin: 2cm; color: #1a1a1a; line-height: 1.5; }
  h1 { font-size: 20pt; border-bottom: 2px solid #333; padding-bottom: 8px; }
  h2 { font-size: 14pt; color: #333; margin-top: 24px; border-bottom: 1px solid #ddd; padding-bottom: 4px; }
  h3 { font-size: 11pt; color: #555; }
  table { border-collapse: collapse; width: 100%; margin: 12px 0; font-size: 9pt; }
  th { background: #f4f4f5; font-weight: 600; text-align: left; padding: 6px 10px; border: 1px solid #ddd; }
  td { padding: 5px 10px; border: 1px solid #eee; }
  tr:nth-child(even) { background: #fafafa; }
  code { background: #f4f4f5; padding: 1px 4px; border-radius: 3px; font-size: 9pt; }
  hr { border: none; border-top: 1px solid #eee; margin: 20px 0; }
  strong { color: #111; }
</style>
</head><body>''' + html + '</body></html>'

weasyprint.HTML(string=styled_html).write_pdf('.planning/audit-report.pdf')
"

Option 3 — wkhtmltopdf:

python3 -c "
import markdown

with open('.planning/audit-report.md', 'r') as f:
    md_content = f.read()

html = markdown.markdown(md_content, extensions=['tables', 'fenced_code'])

styled_html = '''<!DOCTYPE html>
<html><head><meta charset=\"utf-8\">
<style>
  body { font-family: Inter, system-ui, sans-serif; font-size: 10pt; margin: 0; color: #1a1a1a; line-height: 1.5; }
  h1 { font-size: 20pt; border-bottom: 2px solid #333; padding-bottom: 8px; }
  h2 { font-size: 14pt; color: #333; margin-top: 24px; border-bottom: 1px solid #ddd; padding-bottom: 4px; }
  table { border-collapse: collapse; width: 100%; margin: 12px 0; font-size: 9pt; }
  th { background: #f4f4f5; font-weight: 600; text-align: left; padding: 6px 10px; border: 1px solid #ddd; }
  td { padding: 5px 10px; border: 1px solid #eee; }
  tr:nth-child(even) { background: #fafafa; }
</style>
</head><body>''' + html + '</body></html>'

with open('.planning/audit-report.html', 'w') as f:
    f.write(styled_html)
" && wkhtmltopdf --quiet --margin-top 20mm --margin-bottom 20mm --margin-left 20mm --margin-right 20mm .planning/audit-report.html .planning/audit-report.pdf && rm -f .planning/audit-report.html

Fallback — Markdown only: Skip PDF generation. The .planning/audit-report.md file is still available.

STEP 7.5 — Confirm Output

Check if PDF was generated:

[ -f ".planning/audit-report.pdf" ] && echo "PDF_OK" || echo "PDF_FAIL"

If PDF_OK:

• PT-BR: Relatorio de auditoria gerado com sucesso:
• EN: Audit report generated successfully:

  - PDF: .planning/audit-report.pdf
  - Markdown: .planning/audit-report.md

If PDF_FAIL (but Markdown exists):

• PT-BR: Falha ao gerar PDF. Relatorio disponivel em Markdown: .planning/audit-report.md
• EN: PDF generation failed. Report available as Markdown: .planning/audit-report.md

Ensure both files are in .gitignore:

if ! grep -q "audit-report" .gitignore 2>/dev/null; then
  printf '\n# Audit reports (not versioned)\n.planning/audit-report.md\n.planning/audit-report.pdf\n' >> .gitignore
fi

---

STEP 8 — Custom Query

Extract the SQL from the arguments after query .

Safety rules (ALL must pass — otherwise reject as a read-only violation):

1. Trim whitespace; strip at most ONE trailing ;.
2. The remaining SQL must start with SELECT (case-insensitive).
3. Reject if it still CONTAINS ; anywhere (multi-statement injection, e.g. SELECT 1; DELETE FROM events).
4. Reject if it contains ATTACH or PRAGMA (case-insensitive).

Rejection message:

• PT-BR: Apenas consultas SELECT (statement unico) sao permitidas. O banco de auditoria e somente leitura.
• EN: Only single-statement SELECT queries are allowed. The audit database is read-only.

Execute:

sqlite3 -header -column .planning/pwdev-audit.db "<USER_SQL>"

Present the raw result in a formatted table. If the query fails, show the SQLite error message.

Quick Reference (show with results)

### Tables: events, decisions, artifacts, config_changes

**events columns:** id, timestamp, session_id, plugin, command, agent, model, phase, action, target, detail, duration_ms
**decisions columns:** id, event_id, timestamp, phase, decision, rationale, alternatives, reversible
**artifacts columns:** id, event_id, path, type, phase, status, created_at, archived_at
**config_changes columns:** id, timestamp, field, old_value, new_value, changed_by

(Queries are not self-logged — the plugin's Stop hook already records the turn.)

Language: resolve lang per references/language.md before any human-facing output. Paths <plugin-root>/..., references/, scripts/, templates/ are relative to the plugin root; tool names, subagent dispatch and the command form to show the user depend on the runtime (references/runtime.md).