PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS raw_events (

id INTEGER PRIMARY KEY AUTOINCREMENT,

tool TEXT NOT NULL CHECK (tool IN ('claude-code','codex','opencode')),

event_type TEXT NOT NULL,

session_id TEXT NOT NULL,

project_path TEXT,

cwd TEXT NOT NULL,

ts_ms INTEGER NOT NULL,

source_ts_ms INTEGER,

is_closing INTEGER NOT NULL DEFAULT 0,

raw_json TEXT NOT NULL,

created_at INTEGER NOT NULL DEFAULT (unixepoch('now','subsec') * 1000)

);

CREATE INDEX IF NOT EXISTS idx_raw_events_session ON raw_events (tool, session_id, ts_ms);

CREATE INDEX IF NOT EXISTS idx_raw_events_ts ON raw_events (ts_ms);

CREATE TABLE IF NOT EXISTS work_sessions (

id INTEGER PRIMARY KEY AUTOINCREMENT,

tool TEXT NOT NULL,

session_id TEXT NOT NULL,

sub_index INTEGER NOT NULL,

project_path TEXT,

cwd TEXT NOT NULL,

start_ms INTEGER NOT NULL,

end_ms INTEGER NOT NULL,

closed_reason TEXT NOT NULL,

event_count INTEGER NOT NULL,

UNIQUE (tool, session_id, sub_index)

);

CREATE INDEX IF NOT EXISTS idx_work_sessions_window ON work_sessions (start_ms, end_ms);

CREATE TABLE IF NOT EXISTS projects (

id INTEGER PRIMARY KEY AUTOINCREMENT,

key TEXT NOT NULL UNIQUE,

name TEXT NOT NULL,

client TEXT,

hourly_rate REAL,

currency TEXT DEFAULT 'USD',

path_prefix TEXT,

git_remote TEXT

);

CREATE TABLE IF NOT EXISTS git_commits (

id INTEGER PRIMARY KEY AUTOINCREMENT,

repo_path TEXT NOT NULL,

commit_hash TEXT NOT NULL,

author_name TEXT,

author_email TEXT,

committed_ms INTEGER NOT NULL,

subject TEXT,

UNIQUE (repo_path, commit_hash)

);

CREATE INDEX IF NOT EXISTS idx_git_commits_window ON git_commits (repo_path, committed_ms);

PRAGMA user_version = 1;