zebbern/claude-code-guide / skills/audit-flow
audit-flow
VerifiedInteractive system flow tracing across CODE, API, AUTH, DATA, NETWORK layers with SQLite persistence and Mermaid export. Use for security audits, compliance documentation, flow tracing, feature ideation, brainstorming, debugging, architecture reviews, or incident post-mortems. Triggers on audit, trace flow, document flow, security review, debug flow, brainstorm, architecture review, post-mortem, incident review.
| File | Kind | Size |
|---|---|---|
| COMMANDS.md | markdown | 9.2 KB |
| EXAMPLES.md | markdown | 10.1 KB |
| SKILL.md | skill md | 16.4 KB |
| schema.sql | other | 6.7 KB |
| scripts/audit.py | script | 58.9 KB |
| setup.sh | script | 2.5 KB |
COMMANDS.md
View on GitHub# Audit Flow Commands
Detailed SQL commands for the audit-flow skill.
**CRITICAL: Read `schema.sql` before running ANY SQLite command.** Understand tables, columns, constraints, CHECK values, and views FIRST. Never assume schema from context.
## Database Setup
```bash
python .claude/skills/audit-flow/scripts/audit.py init
# Or manually:
mkdir -p .audit && sqlite3 .audit/audit.db < .claude/skills/audit-flow/schema.sql
```
## Session Management
### Create Session
First capture git context:
```bash
git_commit=$(git rev-parse HEAD 2>/dev/null || echo "")
git_branch=$(git branch --show-current 2>/dev/null || echo "")
git_dirty=$(test -n "$(git status --porcelain 2>/dev/null)" && echo 1 || echo 0)
```
Then create session:
```sql
INSERT INTO sessions (id, name, purpose, description, granularity, git_commit, git_branch, git_dirty)
VALUES (
lower(hex(randomblob(8))),
'{name}',
'{purpose}', -- security-audit | documentation | compliance | ideation | brainstorming | debugging | architecture-review | incident-review
'{description}',
'{granularity}', -- fine | coarse
'{git_commit}',
'{git_branch}',
{git_dirty} -- 0 or 1
);
```
### List Sessions
```sql
SELECT * FROM v_session_summary ORDER BY created_at DESC;
```
## Flow Management
### Create Flow
```sql
INSERT INTO flows (session_id, name, entry_point, description)
VALUES (
'{session_id}',
'{flow-name}',
'{entry point description}',
'{optional description}'
);
```
### List Flows in Session
```sql
SELECT * FROM v_flow_summary WHERE session_id = '{session_id}';
```
## Tuple Operations
### Add Tuple
```sql
INSERT INTO tuples (flow_id, layer, action, subject, file_ref, props, notes, status)
VALUES (
{flow_id},
'{layer}', -- CODE | API | NETWORK | AUTH | DATA
'{action}', -- e.g., "onClick handler"
'{subject}', -- e.g., "LoginButton"
'{file_ref}', -- e.g., "src/components/LoginButton.tsx:24"
'{}', -- JSON props
'{notes}',
'traced' -- traced | verified | concern | deleted
);
```
### View Tuples
```sql
-- All tuples in flow
SELECT id, layer, action, subject, file_ref, status
FROM tuples WHERE flow_id = {flow_id} AND status != 'deleted'
ORDER BY timestamp;
-- Filter by layer
SELECT * FROM tuples WHERE flow_id = {flow_id} AND layer = 'AUTH';
-- View concerns
SELECT * FROM v_concerns WHERE flow_name = '{flow_name}';
```
### Update Tuple Status
```sql
UPDATE tuples SET status = 'concern' WHERE id = {id};
UPDATE tuples SET status = 'deleted' WHERE id = {id}; -- Soft delete
```
## Edge Operations
### Linear Edge
```sql
INSERT INTO edges (from_tuple, to_tuple, relation)
VALUES ({from_id}, {to_id}, '{relation}');
-- Relations: TRIGGERS | READS | WRITES | VALIDATES | TRANSFORMS
```
### Branching Edge (with condition)
```sql
INSERT INTO edges (from_tuple, to_tuple, relation, condition)
VALUES
({branch_point}, {path_a}, 'BRANCHES', 'condition A'),
({branch_point}, {path_b}, 'BRANCHES', 'condition B');
```
### Merging Edge
```sql
INSERT INTO edges (from_tuple, to_tuple, relation)
VALUES ({converging_tuple}, {merge_point}, 'MERGES');
```
### View Edges
```sql
SELECT e.*, t1.action as from_action, t2.action as to_action
FROM edges e
JOIN tuples t1 ON e.from_tuple = t1.id
JOIN tuples t2 ON e.to_tuple = t2.id
WHERE t1.flow_id = {flow_id};
```
### Detect Non-Linear Points
```sql
SELECT * FROM v_branch_merge_points WHERE flow_name = '{flow_name}';
```
## Findings
### Add Finding
```sql
INSERT INTO findings (session_id, flow_id, severity, category, description, tuple_refs)
VALUES (
'{session_id}',
{flow_id}, -- Optional: NULL for session-level finding
'{severity}', -- critical | high | medium | low | info
'{category}', -- e.g., "token-storage", "auth-bypass"
'{description}',
'[1, 2, 3]' -- JSON array of tuple IDs
);
```
### View Findings
```sql
-- By severity
SELECT * FROM findings WHERE session_id = '{session_id}'
ORDER BY CASE severity
WHEN 'critical' THEN 1 WHEN 'high' THEN 2
WHEN 'medium' THEN 3 WHEN 'low' THEN 4 ELSE 5
END;
-- Flow-specific findings
SELECT * FROM findings WHERE flow_id = {flow_id};
```
## Export
### Using CLI
```bash
# Export all flows in session
python .claude/skills/audit-flow/scripts/audit.py export {session}
# Export specific flow
python .claude/skills/audit-flow/scripts/audit.py export {session} -f {flow}
# Export specific format
python .claude/skills/audit-flow/scripts/audit.py export {session} -F mermaid
```
## Validation
### Validate Flow Data
```bash
python .claude/skills/audit-flow/scripts/audit.py validate {session}
```
Checks for common issues before export:
| Check | Level | Description |
| ----------------- | ----- | -------------------------------------------- |
| Branch conditions | ERROR | BRANCHES edges must have condition labels |
| Orphan nodes | WARN | Nodes with no edges (disconnected from flow) |
| Duplicate labels | WARN | Multiple nodes with same action text |
| Node count > 40 | WARN | Diagram readability threshold |
| Node count > 60 | ERROR | Diagram must be split into sub-flows |
| No entry point | WARN | No node with zero in-degree |
### Output Structure
```
docs/audits/{session-name}/
├── README.md # Session summary
├── session.json # Full session data
├── session.yaml # Full session data (YAML)
├── {flow-name}.md # Flow report
└── {flow-name}.mermaid # Flow diagram
```
### Mermaid Output Features
- **Step numbering**: Topological BFS order from entry point
- **Entry point marker**: Green stadium-shape node (`([label]):::entryPoint`)
- **Layer subgraphs**: Nodes sorted by step number within each layer
- **Relation-specific arrows**:
- `-->` solid: TRIGGERS, VALIDATES, TRANSFORMS, BRANCHES, MERGES
- `-.->` dotted: READS (data consumption, no mutation)
- `==>` thick: WRITES (data mutation)
- **Observation separation**: Pure concern chains in OBSERVATIONS subgraph
- **Branch conditions**: Always shown on edge labels
- **Label deduplication**: Duplicate actions auto-suffixed with subject
- **Legend**: Style key at bottom of diagram
- **Direction**: Default TD, override with `--direction LR`
### Export with Direction
```bash
# Default top-down
python .claude/skills/audit-flow/scripts/audit.py export {session} -F mermaid
# Horizontal layout for wide decision trees
python .claude/skills/audit-flow/scripts/audit.py export {session} -F mermaid --direction LR
```
Example output:
```mermaid
flowchart TD
subgraph CODE
T1(["1. fetch API"]):::entryPoint
end
subgraph AUTH
T2["2. check token"]
T3["3. proceed"]
T4["4. refresh token"]
end
subgraph API
T5["5. call endpoint"]
end
T1 -->|"TRIGGERS"| T2
T2 -->|"BRANCHES<br/>token valid"| T3
T2 -->|"BRANCHES<br/>token expired"| T4
T3 -->|"TRIGGERS"| T5
T4 -->|"MERGES"| T5
classDef entryPoint fill:#2ecc71,stroke:#27ae60,stroke-width:3px
classDef concern fill:#ff6b6b,stroke:#c92a2a
classDef observation fill:#ffd43b,stroke:#fab005,stroke-dasharray:5 5
subgraph Legend
L1(["Entry point"]):::entryPoint
L2["Flow step"]
L3["Concern"]:::concern
end
```
## Git Merge Driver
### Setup (once per clone)
```bash
python .claude/skills/audit-flow/scripts/audit.py git-setup
```
Configures:
- `merge.sqlite-audit.driver` — auto-merges `audit.db` on `git merge`
- `diff.sqlite.textconv` — shows SQL dump in `git diff`
Requires `.gitattributes` (already in repo):
```
.audit/audit.db diff=sqlite merge=sqlite-audit
```
### How the merge driver works
Git calls: `audit.py db-merge %O %A %B`
- `%O` = common ancestor (unused — full union merge)
- `%A` = ours (result written here)
- `%B` = theirs
The driver:
1. Opens both SQLite DBs directly
2. Merges sessions by `name` (later `updated_at` wins)
3. Flows follow parent session winner
4. Tuples/edges follow parent flow
5. Findings deduped by `(session_name, category, description)`
6. All INTEGER PKs remapped; foreign keys updated
7. Result written to `%A`, exit 0 = success
### Manual merge (if needed)
```bash
# If the auto-driver fails (exit 1), merge manually:
python .claude/skills/audit-flow/scripts/audit.py db-merge ancestor.db ours.db theirs.db
```
## CSV Backup (Optional)
CSV commands still available for backup/portability:
```bash
python .claude/skills/audit-flow/scripts/audit.py csv-export # DB → .audit/csv/*.csv
python .claude/skills/audit-flow/scripts/audit.py csv-import # CSV → DB
python .claude/skills/audit-flow/scripts/audit.py csv-merge <theirs_dir> # Merge CSV sets
```
CSV uses `QUOTE_ALL`, deterministic PK sort, multiline-safe (RFC 4180).
## Views Reference
| View | Purpose |
| ----------------------- | --------------------------------------- |
| `v_session_summary` | Sessions with flow/tuple/finding counts |
| `v_flow_summary` | Flows with tuple/concern counts |
| `v_layer_distribution` | Layer breakdown per flow |
| `v_concerns` | All tuples marked as concern |
| `v_branch_merge_points` | Non-linear nodes (branches/merges) |
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| rea-analysis-optimizationmorluto/reaOptimize REA providers and shared analysis workflows for agent context, latency, CPU, and memory while preserving correctness and Evidence. Use for analysis bottlenecks and cache or query design; CI/test-suite optimization belongs to rea-ci-tests-audit. | Stars: 469k | Updated | Claude CodeCodex | Verified | Install |
| rea-ci-tests-auditmorluto/reaAudit REA CI and test suites for correctness, fixture drift, false passes, and measured performance; use for suite or workflow investigations and efficiency changes, rather than ordinary feature testing or a single test failure. | Stars: 469k | Updated | Claude CodeCodex | Verified | Install |
| rea-tool-designmorluto/reaAudit, investigate, design, or change REA investigation tools, CLI/MCP contracts, provider capabilities, and Evidence semantics. | Stars: 469k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-auditDietrichGebert/ponytailQuality audit of the whole repo: bugs, security, real load, missing tests, speed, and what to delete. Most important first. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |