Audit Flow

---
name: audit-flow
description: "Interactive system flow tracing across CODE, API, AUTH, DATA, NETWORK layers with SQLite persistence and Mermaid export."
license: MIT
compatibility: Requires Python 3.8+ (stdlib only, zero dependencies). Optional pyyaml for YAML export. Git for merge/diff driver features.
metadata:
  author: ArunJRK
  version: "1.0.0"
---

## ⚠️ MANDATORY ENTRY POINT — Execute Before ANY Other Action

**Step 1: Read schema.sql**

```bash
# ALWAYS read the schema first to understand tables, constraints, views
cat .claude/skills/audit-flow/schema.sql
```

**Step 2: Check if DB exists — NEVER recreate**

```bash
# Check for existing database
ls -la .audit/audit.db 2>/dev/null && echo "DB EXISTS - DO NOT RECREATE" || echo "No DB - safe to init"
```

**Step 3: If DB exists, show current state**

```bash
python .claude/skills/audit-flow/scripts/audit.py list
```

### 🚫 FORBIDDEN ACTIONS

| Action                                                | Why Forbidden            |
| ----------------------------------------------------- | ------------------------ |
| `rm .audit/audit.db`                                  | Destroys audit history   |
| `audit.py init` when DB exists                        | Overwrites existing data |
| `DROP TABLE`                                          | Destroys audit history   |
| `sqlite3 .audit/audit.db < schema.sql` when DB exists | Overwrites existing data |

**Rule:** If `.audit/audit.db` exists, ONLY use `audit.py list`, `show`, `export`, or INSERT operations. NEVER recreate.

---

# Audit Flow

Interactive tracing of system flows with SQLite persistence. Supports multiple named flows per session, non-linear flows (branching/merging), and multi-format exports.

## Organization Principles

**Directory structure by purpose:**

- **Audits/Documentation/Compliance:** `docs/audits/{name}-{YYYY-MM-DD}/`
- **Ideation/Brainstorming:** `docs/ideation/{name}-{YYYY-MM-DD}.md` (single file, no subdirectory unless artifacts needed)
- **Debugging/Incident Review:** `docs/audits/{name}-{YYYY-MM-DD}/` (same as audits — captures evidence)
- **Architecture Review:** `docs/audits/{name}-{YYYY-MM-DD}/` (same as audits — captures structural analysis)

**Required files:**

- INDEX.md (manifest, entry point)
- README.md (executive summary)
- {name}-audit.md (flow trace)

**Lazy initialization:** Create subdirectories only when artifacts exist

- `screenshots/` `network-traces/` `diagrams/` `code-samples/` `test-results/` `evidence/`

**Naming:** `{audit-name}-{type}.md`

## DB-First Discipline

**Invariant:** SQLite = sole source of truth. Context window: volatile, compacts without notice, hallucinates state.

**🚨 CRITICAL: NEVER DESTROY EXISTING DATA**

- If `.audit/audit.db` exists → it contains irreplaceable audit history
- NEVER run `init` when DB exists — use `list` to see what's there
- NEVER delete, drop, or recreate — only append

**Constraints:**

| Operation   | Rule                                                                                      | Blocked rationalization              |
| ----------- | ----------------------------------------------------------------------------------------- | ------------------------------------ |
| **Entry**   | Read `schema.sql` FIRST, check if DB exists SECOND                                        | "I'll just start working"            |
| **Init**    | ONLY if `.audit/audit.db` does NOT exist                                                  | "Let me reinitialize to start fresh" |
| Schema      | Read `schema.sql` BEFORE any SQLite command — understand tables, constraints, views first | "I know the schema from context"     |
| Write       | INSERT each tuple/edge/finding before moving to the next code location                    | "I'll batch-insert at the end"       |
| Read        | SELECT from DB before referencing tuple IDs, counts, or flow structure                    | "I remember the flow so far"         |
| Export      | `audit.py export` only — never generate mermaid/markdown from context                     | "Let me generate mermaid directly"   |
| Resume      | `audit.py show <session>` before any operation that references prior tuples               | "I have the full trace in context"   |
| Reference   | Query tuple IDs from DB — IDs are DB-assigned, never inferred                             | "The tuple ID should be N"           |
| **Default** | **When uncertain of flow state → query DB before proceeding**                             | _(any unlisted rationalization)_     |

**Checkpoint:** Every 5 tuples → `audit.py show <session> <flow>`

---

## Interactive Workflow - ALWAYS ASK USER

### 1. Session Start - Ask:

```
Name: ___
Purpose: security-audit | documentation | compliance | ideation | brainstorming | debugging | architecture-review | incident-review
Description: ___ (optional)
```

Initialize directory immediately. Lazily create subdirectories when artifacts are generated.

### 2. Granularity - Ask:

```
[fine]   Function-level trace (~50-200 tuples)
         Use: Security audits, debugging

[coarse] Boundary-level trace (~10-30 tuples)
         Use: Documentation, high-level flows

Choose: fine / coarse
```

### 3. During Trace:

Ask at decision points: trace deeper? mark concern? add finding (severity)? note?

### 4. On Export:

Ask format: `json | yaml | md | mermaid | all`

Post-export: Generate INDEX.md manifest. Organize artifacts by type. Prune empty directories.

## Quick Reference

| Command                              | Purpose                                        |
| ------------------------------------ | ---------------------------------------------- |
| `/audit-flow start`                  | New session (name, purpose, granularity)       |
| `/audit-flow flow {name}`            | Add new flow to session                        |
| `/audit-flow add {layer} {desc}`     | Add tuple to current flow                      |
| `/audit-flow link {from} {to} {rel}` | Create edge (supports conditions for branches) |
| `/audit-flow finding {desc}`         | Record finding                                 |
| `/audit-flow show`                   | View session/flow details                      |
| `/audit-flow export`                 | Export (json/yaml/md/mermaid)                  |
| `/audit-flow git-setup`              | Configure git merge/diff drivers (once)        |

**Layers:** `CODE` | `API` | `NETWORK` | `AUTH` | `DATA`

**Relations:** `TRIGGERS` | `READS` | `WRITES` | `VALIDATES` | `TRANSFORMS` | `BRANCHES` | `MERGES`

## Semantic Rules for Relations

| Relation     | Meaning                      | Use When                                      | NOT For                       |
| ------------ | ---------------------------- | --------------------------------------------- | ----------------------------- |
| `TRIGGERS`   | A causes B to execute        | Function calls, event handlers, HTTP requests | Static observations           |
| `READS`      | A consumes data from B       | Cookie reads, DB queries, config lookups      | Mutations                     |
| `WRITES`     | A mutates data in B          | Cookie writes, DB inserts, state updates      | Read-only access              |
| `VALIDATES`  | A checks/verifies B          | Auth checks, input validation, expiry checks  | Chaining analyst observations |
| `TRANSFORMS` | A converts/maps data for B   | Token exchange, response formatting           | Unrelated processing          |
| `BRANCHES`   | A has conditional paths      | if/else, switch, error vs success             | **Must have condition label** |
| `MERGES`     | Multiple paths converge at B | Parallel paths rejoin, error recovery         | Single-path flow              |

**CRITICAL: BRANCHES Must Have Conditions.** Every BRANCHES edge requires a `condition` describing which path. Example: `BRANCHES [token expired]` vs `BRANCHES [token valid]`.

## Observations vs Flow Steps

**Flow steps** = things the SYSTEM DOES (function calls, data reads, network requests). Verified by tracing code.

**Observations** = things the ANALYST NOTES (missing features, potential risks). Record as **findings**, not tuples.

**Wrong pattern:**

```
T50 "NO cross-tab sync"       ← observation, not a system action
T51 "React state NOT shared"  ← observation
T50 --VALIDATES--> T51         ← chaining observations as flow
```

**Correct pattern:**

```sql
-- Record as finding instead:
INSERT INTO findings (flow_id, session_id, severity, category, description)
VALUES (?, ?, 'medium', 'state-management',
        'No cross-tab sync: React state not shared across tabs');
```

**Rule:** NEVER chain observations with VALIDATES. If describing what the system DOESN'T do, use a finding.

## Data Model

```
Session (audit container)
  └── Flow (named DAG with entry point)
       └── Tuple (node: layer + action + subject)
            └── Edge (relation + optional condition)
```

## Storage & CLI

```bash
# Core
python .claude/skills/audit-flow/scripts/audit.py init              # Initialize DB
python .claude/skills/audit-flow/scripts/audit.py list              # List sessions
python .claude/skills/audit-flow/scripts/audit.py show <session>    # Show flows
python .claude/skills/audit-flow/scripts/audit.py show <session> <flow>  # Show flow details
python .claude/skills/audit-flow/scripts/audit.py export <session>  # Export all
python .claude/skills/audit-flow/scripts/audit.py export <session> -f <flow>  # Export one flow
python .claude/skills/audit-flow/scripts/audit.py validate <session>         # Validate flows

# Git integration
python .claude/skills/audit-flow/scripts/audit.py git-setup         # Configure merge/diff drivers (once)
python .claude/skills/audit-flow/scripts/audit.py db-merge %O %A %B # Git merge driver (auto-called)

# CSV backup/portability (optional)
python .claude/skills/audit-flow/scripts/audit.py csv-export               # DB → .audit/csv/*.csv
python .claude/skills/audit-flow/scripts/audit.py csv-import               # .audit/csv/*.csv → DB
python .claude/skills/audit-flow/scripts/audit.py csv-merge <theirs_dir>   # Merge two CSV sets
```

## Non-Linear Flows

**Branching:** One tuple → multiple outgoing edges with conditions

```sql
INSERT INTO edges (from_tuple, to_tuple, relation, condition)
VALUES (5, 6, 'BRANCHES', 'token valid'),
       (5, 7, 'BRANCHES', 'token expired');
```

**Merging:** Multiple tuples → one tuple

```sql
INSERT INTO edges (from_tuple, to_tuple, relation)
VALUES (6, 8, 'TRIGGERS'),
       (9, 8, 'MERGES');  -- refresh path merges back
```

## Files

- [scripts/audit.py](scripts/audit.py) - CLI for all commands (init, list, show, export, validate, db-merge, git-setup, csv-\*)
- [COMMANDS.md](COMMANDS.md) - Detailed SQL reference
- [EXAMPLES.md](EXAMPLES.md) - Full examples with non-linear flows
- [schema.sql](schema.sql) - Database schema
- `.gitattributes` - Git merge/diff driver config for audit.db

## Git Context

Capture on session start: commit hash, branch, working tree status. Include in all exports.

## Mermaid Validation

Run `python .claude/skills/audit-flow/scripts/audit.py validate <session>` before export.

| Check                      | Severity | Description                                     |
| -------------------------- | -------- | ----------------------------------------------- |
| BRANCHES without condition | ERROR    | Every BRANCHES edge needs a condition label     |
| Node count >= 60           | ERROR    | Split into sub-flows                            |
| Node count >= 40           | WARN     | Consider splitting                              |
| Orphan nodes               | WARN     | Node with no edges (disconnected)               |
| Duplicate labels           | WARN     | Same action text without subject disambiguation |
| No entry point             | WARN     | All nodes have incoming edges                   |

**Post-export features (automatic):**

- **Step numbers**: BFS topological order from entry point (`1. action`, `2. action`)
- **Entry point**: Stadium shape with green styling
- **Edge arrows**: `-->` solid (TRIGGERS/VALIDATES/TRANSFORMS/BRANCHES/MERGES), `-.->` dotted (READS), `==>` thick (WRITES)
- **Observations**: Separated into dashed-border OBSERVATIONS subgraph
- **Direction**: `--direction LR` flag for horizontal layouts

## Diagram Readability Requirements

**All diagrams MUST be produced by `audit.py export`.** Never hand-craft mermaid. The exporter enforces:

1. **Entry point marker** — Green stadium-shape node `([label]):::entryPoint`
2. **Step numbers** — BFS topological order: `1. action`, `2. action`, `3. action`
3. **Legend block** — classDef styles for entryPoint, concern, observation
4. **Observation separation** — Concern-only chains go to OBSERVATIONS subgraph, not main flow
5. **Label safety** — HTML entities for `()`, `""`, `<>`, `|`, `[]` characters (auto-sanitized)

**Reading flow must be obvious.** A reader opening the diagram cold must immediately see:

- WHERE to start (green entry node)
- WHAT ORDER to read (step numbers)
- WHICH PATH is happy vs error (branch conditions on edges)
- WHAT THE COLORS MEAN (legend)

**Node label rules:**

- Use concrete nouns/verbs: `handleCallback()`, `exchangeCodeForTokens()`
- NOT bare verbs: ~~"Configure"~~, ~~"Select"~~, ~~"Enable"~~
- Disambiguate duplicates: auto-suffixed with subject when action repeats

**Size limits:**

- 40+ nodes → warning, consider splitting
- 60+ nodes → error, MUST split into sub-flows
- If flow has 5+ independent sub-flows → split by purpose

---

## Git Workflow — Custom Merge Driver

**Problem:** SQLite is binary — `git merge` can't auto-resolve `.audit/audit.db`.

**Solution:** Custom git merge driver. `audit.db` stays in git (small, single file). On conflict, git calls `audit.py db-merge` to auto-merge using SQL.

### One-Time Setup

```bash
python .claude/skills/audit-flow/scripts/audit.py git-setup
```

This configures (in `.git/config`):

- **Merge driver:** `merge.sqlite-audit` — calls `audit.py db-merge %O %A %B` on conflict
- **Diff driver:** `diff.sqlite` — `sqlite3 .dump` for readable `git diff` output

Also requires `.gitattributes` (already in repo):

```
.audit/audit.db diff=sqlite merge=sqlite-audit
```

### How It Works

1. You commit `audit.db` normally — `git add .audit/audit.db && git commit`
2. `git diff` shows SQL text (via textconv)
3. On `git merge` with conflict → git calls the merge driver automatically
4. Driver opens both DBs, merges sessions by name (later `updated_at` wins), remaps IDs
5. Result written to ours — merge completes cleanly

### Merge Strategy

| Table    | Merge Key                               | Conflict Resolution               |
| -------- | --------------------------------------- | --------------------------------- |
| sessions | `name` (unique)                         | Keep later `updated_at`           |
| flows    | `(session_name, flow_name)`             | Follow parent session winner      |
| tuples   | Parent flow                             | All tuples from winning flow kept |
| edges    | Both endpoint tuples                    | Kept if both endpoints survive    |
| findings | `(session_name, category, description)` | Dedup by content                  |

All INTEGER PKs remapped sequentially. Foreign keys updated.

### CSV Backup (Optional)

CSV export/import still available for portability and backup:

```bash
python .claude/skills/audit-flow/scripts/audit.py csv-export   # DB → .audit/csv/*.csv (QUOTE_ALL)
python .claude/skills/audit-flow/scripts/audit.py csv-import   # CSV → DB (recreates from scratch)
```

---

## Output Quality

**Principles:**

- ASCII sequence diagrams for complex flows
- Side-by-side tables for alternatives
- Real code from traced files with `file:line` references
- What/Why/Example pattern
- No generic templates

## Completion Checklist

- [ ] Directory exists with ISO date suffix
- [ ] INDEX.md manifest generated
- [ ] README.md (executive summary)
- [ ] Naming convention: `{name}-{type}.md`
- [ ] Artifacts in typed subdirectories (lazy init)
- [ ] Git context captured
- [ ] Git merge driver configured (`audit.py git-setup`)
- [ ] No orphaned files
- [ ] Diagrams pass `audit.py validate`

## Anti-Patterns

Flat structure, empty directories, orphan nodes, unlabeled branches, generic identifiers, missing git context, no manifest, hand-crafted mermaid, bare-verb labels.

## Activation Triggers

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.