Image of Analyzing Claude Code Interaction Logs with DuckDB

Analyzing Claude Code Interaction Logs with DuckDB

Text by Hirotaka Miyagi

Published

This article explains how to analyze Claude Code interaction logs (in jsonl format) with DuckDB to understand the AI's behavior and improve the development process.
Table of Contents

Introduction

Hello, I'm Miyagi, the product manager for Liam.

Are you using Claude Code? Lately, I've been doing almost all of my development with Claude Code. Recently, the Claude Code Action was also announced, allowing various tasks on GitHub Actions to be handled by Claude Code.

However, a challenge with Claude Code Action is the proper configuration of allowed_tools. Claude Code Action runs in non-interactive mode, meaning humans cannot intervene during the process. For example, if pnpm lint:* is not permitted in allowed_tools, the session cannot run the linter, and it will consider the task complete even if the CI fails.
Furthermore, even when Claude Code lacks the permissions to perform a desired action, it tries to find alternative ways to reach the goal. While this is impressive, these workarounds increase the number of interactions, leading to more time and cost.

To improve permission setting mistakes, I wanted to analyze errors from the Claude Code (and Claude Code Action) interaction logs. However, searching through vast jsonl logs manually is a daunting task. While one could use an LLM with a large context window like Gemini for analysis, I wondered if there was a more effective method. This led me to the idea of analyzing the jsonl logs with DuckDB.

This article explains how to analyze Claude Code interaction logs (in jsonl format) with DuckDB to understand the AI's behavior and improve the development process for Claude Code.

Claude Code Interaction Logs

Claude Code interaction logs are saved under ~/.claude/projects/ in directories corresponding to each project. The files are in jsonl format, where each line is a JSON object representing a single event (user utterance, AI thought process, tool usage, etc.).

{
  "parentUuid": null,
  "isSidechain": false,
  "userType": "external",
  "cwd": "/Users/mh4gf/ghq/github.com/liam-hq/liam",
  "sessionId": "797df13f-41e5-4ccd-9f00-d6f6b9bee0b3",
  "version": "1.0.38",
  "type": "user",
  "message": {
    "role": "user",
    "content": "<command-message>implement-regression-tests is running…</command-message>\n<command-name>/implement-regression-tests</command-name>\n<command-args>db-structure package</command-args>"
  },
  "uuid": "d02cab21-cc42-407e-80cb-6305ac542803",
  "timestamp": "2025-07-01T10:43:40.323Z"
}
{
  "parentUuid": "2e7836df-0d59-428f-b193-8a07732705c7",
  "isSidechain": false,
  "userType": "external",
  "cwd": "/Users/mh4gf/ghq/github.com/liam-hq/liam",
  "sessionId": "797df13f-41e5-4ccd-9f00-d6f6b9bee0b3",
  "version": "1.0.38",
  "message": {
    "id": "msg_01SS3c1HZmneNCpZf5WazgHq",
    "type": "message",
    "role": "assistant",
    "model": "claude-opus-4-20250514",
    "content": [
      {
        "type": "text",
        "text": "I'll implement the regression tests for the db-structure package by finding and implementing all `it.skip` tests. Let me start by searching for these tests."
      }
    ],
    "stop_reason": null,
    "stop_sequence": null,
    "usage": {
      "input_tokens": 4,
      "cache_creation_input_tokens": 6462,
      "cache_read_input_tokens": 14187,
      "output_tokens": 1,
      "service_tier": "standard"
    }
  },
  "requestId": "req_011CQgAJYLtgENSsjEPSFFGu",
  "type": "assistant",
  "uuid": "02f2a73e-d98f-4931-a22f-98cb7a9d4f56",
  "timestamp": "2025-07-01T10:43:45.885Z"
}

Why DuckDB?

jsonl files are not well-suited for human reading and analysis as they are. This is where DuckDB, an in-process OLAP database, comes in handy. DuckDB can directly query local files (CSV, Parquet, JSON, etc.) using SQL, allowing for interactive analysis without prior data loading.
Another advantage is that if you have the DuckDB CLI installed, Claude Code can handle DuckDB directly. This means that instead of a human writing SQL, you can delegate the entire task to Claude Code.

Purpose of the Analysis

The main objectives of this analysis are twofold:

  1. Investigate the cause of unexpected results: Understand why Claude Code executed a specific command and why it failed.
  2. Optimize permission settings: Identify the tools that Claude Code tends to use and grant appropriate permissions in advance to eliminate rework and streamline development.

Analysis Example with DuckDB

Now, let's have Claude Code analyze its own interaction logs using DuckDB. The following is an almost direct transcript of a Claude Code session output, with some formatting adjustments by the author.

> Use the duckdb cli to analyze the tool permission errors in the Claude Code conversation log for 797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl
 
17:43
 
⏺ I'll help you analyze the permission errors in the Claude Code conversation log using DuckDB CLI. Let me start by examining the JSONL file to
  understand its structure and then identify permission-related tool errors.
 
⏺ Update Todos
  ⎿ 	☐ Examine the structure of the JSONL file to understand the data format (P0)
     ☐ Use DuckDB CLI to analyze permission errors in tool usage (P0)
     ☐ Identify which tools encountered permission errors and their context (P1)
 
⏺ Read(797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl)
  ⎿ 	Read 20 lines (ctrl+r to expand)
 
⏺ Let me examine the file structure to understand what I'm working with, then use DuckDB to analyze the permission errors:
 
⏺ Update Todos
  ⎿ 	☐ Check the structure of the full JSONL file to understand the data format (P0)
     ☐ Set up DuckDB CLI to analyze the JSONL file (P0)
     ☐ Query for tool usage patterns and permission errors (P0)
     ☐ Analyze which tools had permission errors and their context (P1)
 
⏺ Bash(wc -l 797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl)
  ⎿ 	     837 797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl
 
⏺ Bash(head -1 797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl | jq keys)
  ⎿ 	[
       "leafUuid",
       "summary",
     "type"
     ]
 
⏺ Bash(tail -n +5 797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl | head -1 | jq keys)
  ⎿ 	[
       "cwd",
       "isSidechain",
       "message",
       "parentUuid",
       "sessionId",
       "timestamp",
       "type",
       "userType",
       "uuid",
     "version"
     ]
 
⏺ Update Todos
  ⎿ 	☒ Check the structure of the full JSONL file to understand the data format (P0)
     ☐ Set up DuckDB CLI to analyze the JSONL file (P0)
     ☐ Query for tool usage patterns and permission errors (P0)
     ☐ Analyze which tools had permission errors and their context (P1)
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');
      SELECT DISTINCT type FROM conversation;
      ")
  ⎿ 	┌───────────┐
     │   type    │
     │  varchar  │
     ├───────────┤
     │ summary   │
     │ assistant │
     │ user      │
     └───────────┘
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');
      SELECT
          COUNT(*) as total_messages,
          COUNT(CASE WHEN type = 'assistant' THEN 1 END) as assistant_messages,
          COUNT(CASE WHEN type = 'user' THEN 1 END) as user_messages
      FROM conversation;
      ")
  ⎿ 	┌────────────────┬────────────────────┬───────────────┐
     │ total_messages │ assistant_messages │ user_messages │
     │     int64      │       int64        │     int64     │
     ├────────────────┼────────────────────┼───────────────┤
     │            837 │                532 │           299 │
     └────────────────┴────────────────────┴───────────────┘
 
⏺ Update Todos
  ⎿ 	☒ Check the structure of the full JSONL file to understand the data format (P0)
     ☒ Set up DuckDB CLI to analyze the JSONL file (P0)
     ☐ Query for tool usage patterns and permission errors (P0)
     ☐ Analyze which tools had permission errors and their context (P1)
 
17:43
 
⏺ Now let me look for permission errors in the tool usage:
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');
      -- Look for tool usage in assistant messages
      SELECT
          COUNT(*) as messages_with_tools
      FROM conversation
      WHERE type = 'assistant'
        AND json_extract_string(message, '$.content') LIKE '%tool_use%';
      ")
  ⎿ 	┌─────────────────────┐
     │ messages_with_tools │
     │        int64        │
     ├─────────────────────┤
     │                 286 │
     └─────────────────────┘
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');…)
  ⎿
    … +264 lines (ctrl+r to expand)
 
 
17:43
 
⏺ I can see there are entries with errors. Let me search more specifically for tool errors and permission issues:
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');…)
  ⎿
    … +78 lines (ctrl+r to expand)
 
17:43
 
⏺ Now let me look for specific tool permission errors:
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');…)
  ⎿ 	┌──────────────────────────┬──────────────────────────────────────┬───────────┬──────────────┐
     │        timestamp         │                 uuid                 │ tool_name │ content_type │
     │         varchar          │                 uuid                 │  varchar  │   varchar    │
     … +24 lines (ctrl+r to expand)
 
17:43
 
⏺ Let me look for any errors in the tool results with a specific pattern:
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');…)
  ⎿ ┌──────────────────────────┬───────────────────────────────────────────────────────────────────────────┐
    ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────
    ─────────────────────────────────────────┐
    … +117 lines (ctrl+r to expand)
 
17:43
 
⏺ Perfect! I found the permission errors. Now let me get a summary of the different types of errors:
 
⏺ Update Todos
  ⎿ 	☒ Check the structure of the full JSONL file to understand the data format (P0)
     ☒ Set up DuckDB CLI to analyze the JSONL file (P0)
     ☒ Query for tool usage patterns and permission errors (P0)
     ☐ Analyze which tools had permission errors and their context (P1)
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');…)
  ⎿ 	┌───────────────────────────┬───────┐
     │        error_type         │ count │
     │          varchar          │ int64 │
     … +16 lines (ctrl+r to expand)
 
⏺ Bash(duckdb -c "
      CREATE TABLE conversation AS SELECT * FROM read_json_auto('797df13f-41e5-4ccd-9f00-d6f6b9bee0b3.jsonl');…)
  ⎿ 	┌──────────────────────────┬───────────────────────────────────────────────────────────────────────────┐
     │        timestamp         │                               error_message                               │
     │         varchar          │                                  varchar                                  │
     … +5 lines (ctrl+r to expand)
 
⏺ Update Todos
  ⎿ 	☒ Check the structure of the full JSONL file to understand the data format (P0)
     ☒ Set up DuckDB CLI to analyze the JSONL file (P0)
     ☒ Query for tool usage patterns and permission errors (P0)
     ☒ Analyze which tools had permission errors and their context (P1)
 
17:43
 
⏺ Analysis Results
 
  Permission error analysis results for the Claude Code conversation log:
 
  Main types of permission errors and their occurrences
 
  1. Bash Permission Required (3 times)
    - Claude requested permissions to use Bash, but you haven't granted it yet.
    - Occurrence times: 2025-07-01 10:56:15, 11:00:53, 11:04:43
  2. Other Errors (9 times) - Various technical errors
  3. Test Failure (8 times) - Test execution failures
  4. No Changes Error (3 times) - No changes made during file editing
  5. Directory Not Found Error (2 times) - Directory not found
  6. String Not Found Error (2 times) - String replacement error
 
  Details of Permission Errors
 
  The main permission issue is with the Bash tool, occurring a total of 3 times. These happened in the following situations:
 
  - Claude Code attempted to execute a Bash command, but the user had not yet granted permission to use the Bash tool.
  - Situations where the user needs to explicitly grant access to the Bash tool during the conversation.
 
  Conversation Characteristics
 
  - Total messages: 837
  - Assistant messages: 532
  - User messages: 299
  - Tool usage messages: 286
 
  Permission errors mainly occurred during Bash command execution for file operations and test runs, indicating that Claude Code's safety features are working correctly.

At this point, we can see that several permission errors exist. It's also clear that even when Claude Code fails a DuckDB query during the session, it autonomously recovers and reaches its goal.
Let's have it investigate the details of the permission errors.

> To add the necessary permissions, please identify the commands that failed due to permission errors. The current settings are: "Read,Write,Edit,MultiEdit,Bash(pnpm fmt:_),Bash(pnpm install),Bash(pnpm lint:_),Bash(pnpm test:*)"
 
~~ Omitted ~~
 
⏺ I have identified the three Bash commands that failed due to permission errors:
 
Commands that failed due to permission errors
 
1. `node -e "console.log(/^\/tables\/([^/]+)\/indexes\/([^/]+)$/.test('/tables/users/indexes/idx_email/unique'))`
 
   - Executes JavaScript code with Node.js
   - Tests a regular expression
 
2. `npx vitest run src/diff/constraints/**tests**/buildConstraintNameDiffItem.test.ts`
 
   - Runs vitest using npx
   - Directly executes a specific test file
 
3. `cd /Users/mh4gf/ghq/github.com/liam-hq/liam-worktree/coverage && find . -name "*.ts" -type f | xargs grep -l "foreignKeyConstraintReferenceOptionSchema" | head -10`
 
   - Changes directory and searches for files
   - Searches for a string using grep
 
Recommended Additional Permissions
 
I recommend adding the following to your current permissions:
 
Bash(node:\*),Bash(npx:\*),Bash(cd:\*),Bash(find:\*),Bash(grep:\*)
 
The most important ones are:
 
- Bash(npx:\*) - To execute npx commands (necessary for tools like vitest)
- Bash(node:\*) - For executing scripts with Node.js
- Bash(cd:\*) - For changing directories (frequently used in development workflows)

As you can see, it analyzes the log files efficiently.

Conclusion

In this article, I introduced a method for analyzing Claude Code interaction logs (in jsonl format) using Claude Code and DuckDB. While I used it for debugging permission settings this time, I believe it can also be effective for use cases like having a separate session review the task afterward and update documentation.

At Liam, our development team not only actively leverages AI development tools but also works on developing challenging AI features. We look forward to sharing more of these initiatives in the future.

Text byHirotaka Miyagi

Hirotaka Miyagi is Tech Lead at ROUTE06. A full-stack engineer specializing in web development, he has built extensive experience through multiple startups before joining ROUTE06 to lead its technical initiatives.

Last edited on

Previous
Forcing Claude Code to Reliably Pass Lint with Lefthook
Next
What We Did to Gain 3,000 GitHub Stars for the Liam Repository
Categories
Glossary

There are currently no glossary entries for this blog post.

Related Blogs