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:

Opens both SQLite DBs directly
Merges sessions by name (later updated_at wins)
Flows follow parent session winner
Tuples/edges follow parent flow
Findings deduped by (session_name, category, description)
All INTEGER PKs remapped; foreign keys updated
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)

Audit Flow Commands ​

Database Setup ​

Session Management ​

Create Session ​

List Sessions ​

Flow Management ​

Create Flow ​

List Flows in Session ​

Tuple Operations ​

Add Tuple ​

View Tuples ​

Update Tuple Status ​

Edge Operations ​

Linear Edge ​

Branching Edge (with condition) ​

Merging Edge ​

View Edges ​

Detect Non-Linear Points ​

Findings ​

Add Finding ​

View Findings ​

Export ​

Using CLI ​

Validation ​

Validate Flow Data ​

Output Structure ​

Mermaid Output Features ​

Export with Direction ​

Git Merge Driver ​

Setup (once per clone) ​

How the merge driver works ​

Manual merge (if needed) ​

CSV Backup (Optional) ​

Views Reference ​

Audit Flow Commands

Database Setup

Session Management

Create Session

List Sessions

Flow Management

Create Flow

List Flows in Session

Tuple Operations

Add Tuple

View Tuples

Update Tuple Status

Edge Operations

Linear Edge

Branching Edge (with condition)

Merging Edge

View Edges

Detect Non-Linear Points

Findings

Add Finding

View Findings

Export

Using CLI

Validation

Validate Flow Data

Output Structure

Mermaid Output Features

Export with Direction

Git Merge Driver

Setup (once per clone)

How the merge driver works

Manual merge (if needed)

CSV Backup (Optional)

Views Reference