Database Schema — Code Insights

Reference for the Code Insights SQLite database schema — projects, sessions, messages, and insights tables at ~/.code-insights/data.db.

The local SQLite database is stored at ~/.code-insights/data.db. It uses WAL mode for concurrent reads during CLI sync. All timestamps are ISO 8601 strings. Arrays and nested objects are stored as JSON strings.

Tables

projects

Stores project metadata. The project ID is derived from the git remote URL (stable across devices) or a hash of the working directory path.

Column	Type	Default	Description
`id`	TEXT PRIMARY KEY	—	Hash of git remote URL or directory path
`name`	TEXT NOT NULL	—	Project display name
`path`	TEXT NOT NULL	—	Working directory path
`git_remote_url`	TEXT	NULL	Git remote URL (nullable)
`project_id_source`	TEXT NOT NULL	`'path-hash'`	How the ID was derived: `'git-remote'` or `'path-hash'`
`session_count`	INTEGER NOT NULL	`0`	Number of synced sessions
`last_activity`	TEXT NOT NULL	—	ISO 8601 timestamp of most recent session
`created_at`	TEXT NOT NULL	`datetime('now')`	ISO 8601 timestamp
`updated_at`	TEXT NOT NULL	`datetime('now')`	ISO 8601 timestamp
`total_input_tokens`	INTEGER	`0`	Cumulative input tokens across all sessions
`total_output_tokens`	INTEGER	`0`	Cumulative output tokens
`cache_creation_tokens`	INTEGER	`0`	Cumulative cache write tokens
`cache_read_tokens`	INTEGER	`0`	Cumulative cache read tokens
`estimated_cost_usd`	REAL	`0`	Cumulative estimated cost in USD

sessions

Stores session metadata with generated titles, character classification, token usage, and device info.

Column	Type	Default	Description
`id`	TEXT PRIMARY KEY	—	Session ID from source tool
`project_id`	TEXT NOT NULL	—	Foreign key to `projects.id`
`project_name`	TEXT NOT NULL	—	Denormalized project name
`project_path`	TEXT NOT NULL	—	Denormalized project path
`git_remote_url`	TEXT	NULL	Git remote URL at sync time
`summary`	TEXT	NULL	LLM-generated session summary
`custom_title`	TEXT	NULL	User-provided title
`generated_title`	TEXT	NULL	Auto-generated title
`title_source`	TEXT	NULL	Which tier generated the title
`session_character`	TEXT	NULL	Classification: `deep_focus`, `bug_hunt`, `feature_build`, `exploration`, `refactor`, `learning`, `quick_task`
`started_at`	TEXT NOT NULL	—	ISO 8601 session start time
`ended_at`	TEXT NOT NULL	—	ISO 8601 session end time
`message_count`	INTEGER NOT NULL	`0`	Total messages
`user_message_count`	INTEGER NOT NULL	`0`	User messages only
`assistant_message_count`	INTEGER NOT NULL	`0`	Assistant messages only
`tool_call_count`	INTEGER NOT NULL	`0`	Total tool calls
`git_branch`	TEXT	NULL	Git branch at session time
`claude_version`	TEXT	NULL	Claude version string (Claude Code only)
`source_tool`	TEXT NOT NULL	`'claude-code'`	`claude-code`, `cursor`, `codex-cli`, `copilot-cli`
`device_id`	TEXT	NULL	Stable anonymous device identifier
`device_hostname`	TEXT	NULL	Machine hostname
`device_platform`	TEXT	NULL	`darwin`, `linux`, `win32`
`synced_at`	TEXT NOT NULL	`datetime('now')`	ISO 8601 sync timestamp
`total_input_tokens`	INTEGER	NULL	Total input tokens for session
`total_output_tokens`	INTEGER	NULL	Total output tokens
`cache_creation_tokens`	INTEGER	NULL	Cache write tokens
`cache_read_tokens`	INTEGER	NULL	Cache read tokens
`estimated_cost_usd`	REAL	NULL	Estimated cost in USD
`models_used`	TEXT	NULL	JSON array of all model IDs used
`primary_model`	TEXT	NULL	Most-used model ID
`usage_source`	TEXT	NULL	How usage was calculated

Indexes: project_id, started_at DESC, source_tool

messages

Stores full message content for all sessions.

Column	Type	Default	Description
`id`	TEXT PRIMARY KEY	—	Message ID
`session_id`	TEXT NOT NULL	—	Foreign key to `sessions.id`
`type`	TEXT NOT NULL	—	`user` or `assistant`
`content`	TEXT NOT NULL	`''`	Full message content
`thinking`	TEXT	NULL	Extended thinking content (JSON)
`tool_calls`	TEXT	NULL	JSON array of tool calls
`tool_results`	TEXT	NULL	JSON array of tool results
`usage`	TEXT	NULL	JSON token usage for this message
`timestamp`	TEXT NOT NULL	—	ISO 8601 message timestamp
`parent_id`	TEXT	NULL	Parent message ID

Indexes: session_id, (session_id, timestamp ASC)

insights

Stores LLM-generated insights. Written by the embedded dashboard server, not the CLI.

Column	Type	Default	Description
`id`	TEXT PRIMARY KEY	—	UUID
`session_id`	TEXT NOT NULL	—	Foreign key to `sessions.id`
`project_id`	TEXT NOT NULL	—	Foreign key to `projects.id`
`project_name`	TEXT NOT NULL	—	Denormalized project name
`type`	TEXT NOT NULL	—	`summary`, `decision`, `learning`, `technique`, `prompt_quality`
`title`	TEXT NOT NULL	—	Insight title
`content`	TEXT NOT NULL	—	Full insight text
`summary`	TEXT NOT NULL	—	One-line summary
`bullets`	TEXT	NULL	JSON array of bullet points
`confidence`	REAL NOT NULL	—	Confidence score 0.0–1.0
`source`	TEXT NOT NULL	`'llm'`	Always `llm`
`metadata`	TEXT	NULL	JSON metadata blob
`timestamp`	TEXT NOT NULL	—	ISO 8601 insight timestamp
`created_at`	TEXT NOT NULL	`datetime('now')`	ISO 8601 creation timestamp
`scope`	TEXT NOT NULL	`'session'`	Always `session` currently
`analysis_version`	TEXT NOT NULL	`'1.0.0'`	Analysis pipeline version
`linked_insight_ids`	TEXT	NULL	JSON array of related insight IDs

Indexes: session_id, project_id, type, timestamp DESC

usage_stats

A singleton row (id = 1) tracking cumulative totals across all sessions. Updated by the CLI on each sync.

Column	Type	Default	Description
`id`	INTEGER PRIMARY KEY	—	Always 1 (singleton)
`total_input_tokens`	INTEGER	`0`	Cumulative input tokens
`total_output_tokens`	INTEGER	`0`	Cumulative output tokens
`cache_creation_tokens`	INTEGER	`0`	Cumulative cache write tokens
`cache_read_tokens`	INTEGER	`0`	Cumulative cache read tokens
`estimated_cost_usd`	REAL	`0`	Cumulative cost in USD
`sessions_with_usage`	INTEGER	`0`	Sessions that have token data
`last_updated_at`	TEXT	`datetime('now')`	ISO 8601 last update time

Accessing the Database

You can query the database directly with any SQLite client:

sqlite3 ~/.code-insights/data.db
 
.tables
SELECT generated_title, session_character, estimated_cost_usd FROM sessions ORDER BY started_at DESC LIMIT 10;
SELECT SUM(estimated_cost_usd) FROM sessions;
SELECT source_tool, COUNT(*) FROM sessions GROUP BY source_tool;