Gan, Jimmy
1ba0014d47
- Create claude-code-tracker service to monitor and parse Claude Code conversations - Parse JSONL format with messages, tool calls, and metadata - Store in SQLite with full-text search support - Generate daily summaries using Claude API - Add Conversations UI with search, stats, and conversation browsing - Integrate with dashboard backend and frontend - Add sync script for Mac to NAS file transfer
216 lines
8.7 KiB
Python
216 lines
8.7 KiB
Python
import os
|
|
import aiosqlite
|
|
|
|
DB_PATH = os.environ.get("DB_PATH", "/app/data/conversations.db")
|
|
|
|
|
|
async def init_db():
|
|
"""Initialize database with schema"""
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
# Conversations table
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id TEXT UNIQUE NOT NULL,
|
|
project_path TEXT NOT NULL,
|
|
git_branch TEXT,
|
|
started_at DATETIME NOT NULL,
|
|
last_updated_at DATETIME NOT NULL,
|
|
message_count INTEGER DEFAULT 0,
|
|
user_message_count INTEGER DEFAULT 0,
|
|
assistant_message_count INTEGER DEFAULT 0,
|
|
total_input_tokens INTEGER DEFAULT 0,
|
|
total_output_tokens INTEGER DEFAULT 0,
|
|
model TEXT,
|
|
slug TEXT,
|
|
file_path TEXT NOT NULL
|
|
)
|
|
""")
|
|
|
|
# Messages table
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id TEXT NOT NULL,
|
|
message_uuid TEXT UNIQUE NOT NULL,
|
|
parent_uuid TEXT,
|
|
role TEXT NOT NULL,
|
|
content_text TEXT,
|
|
timestamp DATETIME NOT NULL,
|
|
prompt_id TEXT,
|
|
model TEXT,
|
|
input_tokens INTEGER,
|
|
output_tokens INTEGER,
|
|
has_tool_use BOOLEAN DEFAULT 0,
|
|
has_thinking BOOLEAN DEFAULT 0,
|
|
stop_reason TEXT
|
|
)
|
|
""")
|
|
|
|
# Tool calls table
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS tool_calls (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
message_uuid TEXT NOT NULL,
|
|
tool_use_id TEXT UNIQUE NOT NULL,
|
|
tool_name TEXT NOT NULL,
|
|
tool_input TEXT,
|
|
tool_result TEXT,
|
|
is_error BOOLEAN DEFAULT 0,
|
|
timestamp DATETIME NOT NULL
|
|
)
|
|
""")
|
|
|
|
# Daily summaries table
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS daily_summaries (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
date TEXT UNIQUE NOT NULL,
|
|
project_path TEXT,
|
|
conversation_count INTEGER,
|
|
total_messages INTEGER,
|
|
total_tokens INTEGER,
|
|
summary_content TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
""")
|
|
|
|
# Checkpoints table
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS checkpoints (
|
|
file_path TEXT PRIMARY KEY,
|
|
last_line_count INTEGER NOT NULL,
|
|
last_modified DATETIME NOT NULL
|
|
)
|
|
""")
|
|
|
|
# Create indexes
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages(timestamp)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_tool_calls_message ON tool_calls(message_uuid)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_conversations_project ON conversations(project_path)")
|
|
await db.execute("CREATE INDEX IF NOT EXISTS idx_conversations_started ON conversations(started_at)")
|
|
|
|
await db.commit()
|
|
|
|
|
|
async def upsert_conversation(session_id, data):
|
|
"""Insert or update conversation"""
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
await db.execute("""
|
|
INSERT INTO conversations (
|
|
session_id, project_path, git_branch, started_at, last_updated_at,
|
|
message_count, user_message_count, assistant_message_count,
|
|
total_input_tokens, total_output_tokens, model, slug, file_path
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(session_id) DO UPDATE SET
|
|
last_updated_at = excluded.last_updated_at,
|
|
message_count = excluded.message_count,
|
|
user_message_count = excluded.user_message_count,
|
|
assistant_message_count = excluded.assistant_message_count,
|
|
total_input_tokens = excluded.total_input_tokens,
|
|
total_output_tokens = excluded.total_output_tokens,
|
|
model = excluded.model,
|
|
slug = excluded.slug,
|
|
git_branch = excluded.git_branch
|
|
""", (
|
|
session_id, data['project_path'], data.get('git_branch'),
|
|
data['started_at'], data['last_updated_at'],
|
|
data['message_count'], data['user_message_count'], data['assistant_message_count'],
|
|
data['total_input_tokens'], data['total_output_tokens'],
|
|
data.get('model'), data.get('slug'), data['file_path']
|
|
))
|
|
await db.commit()
|
|
|
|
|
|
async def insert_message(message_data):
|
|
"""Insert message (skip if exists)"""
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
try:
|
|
await db.execute("""
|
|
INSERT INTO messages (
|
|
session_id, message_uuid, parent_uuid, role, content_text,
|
|
timestamp, prompt_id, model, input_tokens, output_tokens,
|
|
has_tool_use, has_thinking, stop_reason
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
""", (
|
|
message_data['session_id'], message_data['message_uuid'],
|
|
message_data.get('parent_uuid'), message_data['role'],
|
|
message_data.get('content_text'), message_data['timestamp'],
|
|
message_data.get('prompt_id'), message_data.get('model'),
|
|
message_data.get('input_tokens'), message_data.get('output_tokens'),
|
|
message_data.get('has_tool_use', False), message_data.get('has_thinking', False),
|
|
message_data.get('stop_reason')
|
|
))
|
|
await db.commit()
|
|
except aiosqlite.IntegrityError:
|
|
# Message already exists, skip
|
|
pass
|
|
|
|
|
|
async def insert_tool_call(tool_data):
|
|
"""Insert tool call (skip if exists)"""
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
try:
|
|
await db.execute("""
|
|
INSERT INTO tool_calls (
|
|
message_uuid, tool_use_id, tool_name, tool_input,
|
|
tool_result, is_error, timestamp
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?)
|
|
""", (
|
|
tool_data['message_uuid'], tool_data['tool_use_id'],
|
|
tool_data['tool_name'], tool_data.get('tool_input'),
|
|
tool_data.get('tool_result'), tool_data.get('is_error', False),
|
|
tool_data['timestamp']
|
|
))
|
|
await db.commit()
|
|
except aiosqlite.IntegrityError:
|
|
# Tool call already exists, skip
|
|
pass
|
|
|
|
|
|
async def get_checkpoint(file_path):
|
|
"""Get checkpoint for a file"""
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
cursor = await db.execute(
|
|
"SELECT last_line_count, last_modified FROM checkpoints WHERE file_path = ?",
|
|
(file_path,)
|
|
)
|
|
row = await cursor.fetchone()
|
|
return {"last_line_count": row[0], "last_modified": row[1]} if row else None
|
|
|
|
|
|
async def update_checkpoint(file_path, line_count, modified_time):
|
|
"""Update checkpoint for a file"""
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
await db.execute("""
|
|
INSERT INTO checkpoints (file_path, last_line_count, last_modified)
|
|
VALUES (?, ?, ?)
|
|
ON CONFLICT(file_path) DO UPDATE SET
|
|
last_line_count = excluded.last_line_count,
|
|
last_modified = excluded.last_modified
|
|
""", (file_path, line_count, modified_time))
|
|
await db.commit()
|
|
|
|
|
|
async def upsert_summary(date, data):
|
|
"""Insert or update daily summary"""
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
await db.execute("""
|
|
INSERT INTO daily_summaries (
|
|
date, project_path, conversation_count, total_messages,
|
|
total_tokens, summary_content
|
|
) VALUES (?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(date) DO UPDATE SET
|
|
project_path = excluded.project_path,
|
|
conversation_count = excluded.conversation_count,
|
|
total_messages = excluded.total_messages,
|
|
total_tokens = excluded.total_tokens,
|
|
summary_content = excluded.summary_content,
|
|
created_at = CURRENT_TIMESTAMP
|
|
""", (
|
|
date, data.get('project_path'), data['conversation_count'],
|
|
data['total_messages'], data['total_tokens'], data['summary_content']
|
|
))
|
|
await db.commit()
|