schema.sql

-- Schema for jcode telemetry D1 database

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telemetry_id TEXT NOT NULL,
    event TEXT NOT NULL,
    version TEXT NOT NULL,
    os TEXT NOT NULL,
    arch TEXT NOT NULL,
    provider_start TEXT,
    provider_end TEXT,
    model_start TEXT,
    model_end TEXT,
    provider_switches INTEGER DEFAULT 0,
    model_switches INTEGER DEFAULT 0,
    duration_mins INTEGER,
    duration_secs INTEGER,
    turns INTEGER,
    had_user_prompt INTEGER DEFAULT 0,
    had_assistant_response INTEGER DEFAULT 0,
    assistant_responses INTEGER DEFAULT 0,
    first_assistant_response_ms INTEGER,
    first_tool_call_ms INTEGER,
    first_tool_success_ms INTEGER,
    tool_calls INTEGER DEFAULT 0,
    tool_failures INTEGER DEFAULT 0,
    executed_tool_calls INTEGER DEFAULT 0,
    executed_tool_successes INTEGER DEFAULT 0,
    executed_tool_failures INTEGER DEFAULT 0,
    tool_latency_total_ms INTEGER DEFAULT 0,
    tool_latency_max_ms INTEGER DEFAULT 0,
    file_write_calls INTEGER DEFAULT 0,
    tests_run INTEGER DEFAULT 0,
    tests_passed INTEGER DEFAULT 0,
    input_tokens INTEGER DEFAULT 0,
    output_tokens INTEGER DEFAULT 0,
    cache_read_input_tokens INTEGER DEFAULT 0,
    cache_creation_input_tokens INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    feature_memory_used INTEGER DEFAULT 0,
    feature_swarm_used INTEGER DEFAULT 0,
    feature_web_used INTEGER DEFAULT 0,
    feature_email_used INTEGER DEFAULT 0,
    feature_mcp_used INTEGER DEFAULT 0,
    feature_side_panel_used INTEGER DEFAULT 0,
    feature_goal_used INTEGER DEFAULT 0,
    feature_selfdev_used INTEGER DEFAULT 0,
    feature_background_used INTEGER DEFAULT 0,
    feature_subagent_used INTEGER DEFAULT 0,
    unique_mcp_servers INTEGER DEFAULT 0,
    session_success INTEGER DEFAULT 0,
    abandoned_before_response INTEGER DEFAULT 0,
    session_stop_reason TEXT,
    agent_role TEXT,
    parent_session_id TEXT,
    agent_active_ms_total INTEGER DEFAULT 0,
    agent_model_ms_total INTEGER DEFAULT 0,
    agent_tool_ms_total INTEGER DEFAULT 0,
    session_idle_ms_total INTEGER DEFAULT 0,
    agent_blocked_ms_total INTEGER DEFAULT 0,
    time_to_first_agent_action_ms INTEGER,
    time_to_first_useful_action_ms INTEGER,
    spawned_agent_count INTEGER DEFAULT 0,
    background_task_count INTEGER DEFAULT 0,
    background_task_completed_count INTEGER DEFAULT 0,
    subagent_task_count INTEGER DEFAULT 0,
    subagent_success_count INTEGER DEFAULT 0,
    swarm_task_count INTEGER DEFAULT 0,
    swarm_success_count INTEGER DEFAULT 0,
    user_cancelled_count INTEGER DEFAULT 0,
    transport_https INTEGER DEFAULT 0,
    transport_persistent_ws_fresh INTEGER DEFAULT 0,
    transport_persistent_ws_reuse INTEGER DEFAULT 0,
    transport_cli_subprocess INTEGER DEFAULT 0,
    transport_native_http2 INTEGER DEFAULT 0,
    transport_other INTEGER DEFAULT 0,
    resumed_session INTEGER DEFAULT 0,
    end_reason TEXT,
    auth_provider TEXT,
    auth_method TEXT,
    -- Failure reason label for onboarding_step step='auth_failed' events
    -- (classify_auth_failure_message labels, e.g. callback_timeout,
    -- validation_failed, oauth_rate_limited). Added in migration 0015.
    auth_failure_reason TEXT,
    from_version TEXT,
    event_id TEXT,
    session_id TEXT,
    schema_version INTEGER DEFAULT 1,
    build_channel TEXT,
    is_git_checkout INTEGER DEFAULT 0,
    is_ci INTEGER DEFAULT 0,
    ran_from_cargo INTEGER DEFAULT 0,
    step TEXT,
    milestone_elapsed_ms INTEGER,
    feedback_rating TEXT,
    feedback_reason TEXT,
    feedback_text TEXT,
    -- NOTE: schema-v5 per-turn fields (turn_index, turn timings, turn_success,
    -- turn_abandoned, turn_end_reason) and session cadence fields (hour/weekday,
    -- previous_session_gap_secs, sessions_started_24h/7d, concurrency) live in
    -- turn_details / session_details, NOT here. D1 caps tables at 100 columns
    -- and events sits at 96 in production, so it has no headroom. See
    -- migrations/0013_detail_table_turn_session_fields.sql.
    error_provider_timeout INTEGER DEFAULT 0,
    error_auth_failed INTEGER DEFAULT 0,
    error_tool_error INTEGER DEFAULT 0,
    error_mcp_error INTEGER DEFAULT 0,
    error_rate_limited INTEGER DEFAULT 0,
    -- Token subscription plan fields (migration 0016). These two are the only
    -- subscription columns on events because the table is near D1's
    -- 100-column cap (96 in production before 0016); web-only fields live in
    -- web_details below.
    account_id TEXT,
    tier TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_events_telemetry_id ON events(telemetry_id);
CREATE INDEX IF NOT EXISTS idx_events_event ON events(event);
CREATE INDEX IF NOT EXISTS idx_events_created_at ON events(created_at);
CREATE INDEX IF NOT EXISTS idx_events_event_created_telemetry ON events(event, created_at, telemetry_id);
CREATE INDEX IF NOT EXISTS idx_events_event_telemetry_created ON events(event, telemetry_id, created_at);
CREATE UNIQUE INDEX IF NOT EXISTS idx_events_event_id ON events(event_id);
CREATE INDEX IF NOT EXISTS idx_events_session_id ON events(session_id);
CREATE INDEX IF NOT EXISTS idx_events_step ON events(step);
CREATE INDEX IF NOT EXISTS idx_events_feedback_rating ON events(feedback_rating);
CREATE INDEX IF NOT EXISTS idx_events_account_id ON events(account_id);
CREATE INDEX IF NOT EXISTS idx_events_event_tier_created ON events(event, tier, created_at);

-- Website beacon detail rows (web_pageview / web_cta_click), keyed by
-- event_id like session_details / turn_details. Added in migration 0016.
CREATE TABLE IF NOT EXISTS web_details (
    event_id TEXT PRIMARY KEY,
    path TEXT,
    referrer TEXT,
    visitor_id TEXT,
    utm_source TEXT,
    utm_medium TEXT,
    utm_campaign TEXT,
    cta TEXT,
    FOREIGN KEY (event_id) REFERENCES events(event_id)
);

CREATE INDEX IF NOT EXISTS idx_web_details_visitor_id ON web_details(visitor_id);
CREATE INDEX IF NOT EXISTS idx_web_details_path ON web_details(path);
CREATE INDEX IF NOT EXISTS idx_web_details_cta ON web_details(cta);

CREATE TABLE IF NOT EXISTS session_details (
    event_id TEXT PRIMARY KEY,
    session_start_hour_utc INTEGER,
    session_start_weekday_utc INTEGER,
    session_end_hour_utc INTEGER,
    session_end_weekday_utc INTEGER,
    previous_session_gap_secs INTEGER,
    sessions_started_24h INTEGER DEFAULT 0,
    sessions_started_7d INTEGER DEFAULT 0,
    active_sessions_at_start INTEGER DEFAULT 0,
    other_active_sessions_at_start INTEGER DEFAULT 0,
    max_concurrent_sessions INTEGER DEFAULT 0,
    multi_sessioned INTEGER DEFAULT 0,
    first_file_edit_ms INTEGER,
    first_test_pass_ms INTEGER,
    tool_cat_read_search INTEGER DEFAULT 0,
    tool_cat_write INTEGER DEFAULT 0,
    tool_cat_shell INTEGER DEFAULT 0,
    tool_cat_web INTEGER DEFAULT 0,
    tool_cat_memory INTEGER DEFAULT 0,
    tool_cat_subagent INTEGER DEFAULT 0,
    tool_cat_swarm INTEGER DEFAULT 0,
    tool_cat_email INTEGER DEFAULT 0,
    tool_cat_side_panel INTEGER DEFAULT 0,
    tool_cat_goal INTEGER DEFAULT 0,
    tool_cat_mcp INTEGER DEFAULT 0,
    tool_cat_other INTEGER DEFAULT 0,
    command_login_used INTEGER DEFAULT 0,
    command_model_used INTEGER DEFAULT 0,
    command_usage_used INTEGER DEFAULT 0,
    command_resume_used INTEGER DEFAULT 0,
    command_memory_used INTEGER DEFAULT 0,
    command_swarm_used INTEGER DEFAULT 0,
    command_goal_used INTEGER DEFAULT 0,
    command_selfdev_used INTEGER DEFAULT 0,
    command_feedback_used INTEGER DEFAULT 0,
    command_other_used INTEGER DEFAULT 0,
    workflow_chat_only INTEGER DEFAULT 0,
    workflow_coding_used INTEGER DEFAULT 0,
    workflow_research_used INTEGER DEFAULT 0,
    workflow_tests_used INTEGER DEFAULT 0,
    workflow_background_used INTEGER DEFAULT 0,
    workflow_subagent_used INTEGER DEFAULT 0,
    workflow_swarm_used INTEGER DEFAULT 0,
    project_repo_present INTEGER DEFAULT 0,
    project_lang_rust INTEGER DEFAULT 0,
    project_lang_js_ts INTEGER DEFAULT 0,
    project_lang_python INTEGER DEFAULT 0,
    project_lang_go INTEGER DEFAULT 0,
    project_lang_markdown INTEGER DEFAULT 0,
    project_lang_mixed INTEGER DEFAULT 0,
    days_since_install INTEGER,
    active_days_7d INTEGER DEFAULT 0,
    active_days_30d INTEGER DEFAULT 0,
    FOREIGN KEY (event_id) REFERENCES events(event_id)
);

CREATE TABLE IF NOT EXISTS turn_details (
    event_id TEXT PRIMARY KEY,
    turn_index INTEGER,
    turn_started_ms INTEGER,
    turn_active_duration_ms INTEGER,
    idle_before_turn_ms INTEGER,
    idle_after_turn_ms INTEGER,
    turn_success INTEGER DEFAULT 0,
    turn_abandoned INTEGER DEFAULT 0,
    turn_end_reason TEXT,
    input_tokens INTEGER DEFAULT 0,
    output_tokens INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    assistant_responses INTEGER DEFAULT 0,
    first_assistant_response_ms INTEGER,
    first_tool_call_ms INTEGER,
    first_tool_success_ms INTEGER,
    first_file_edit_ms INTEGER,
    first_test_pass_ms INTEGER,
    tool_calls INTEGER DEFAULT 0,
    tool_failures INTEGER DEFAULT 0,
    executed_tool_calls INTEGER DEFAULT 0,
    executed_tool_successes INTEGER DEFAULT 0,
    executed_tool_failures INTEGER DEFAULT 0,
    tool_latency_total_ms INTEGER DEFAULT 0,
    tool_latency_max_ms INTEGER DEFAULT 0,
    file_write_calls INTEGER DEFAULT 0,
    tests_run INTEGER DEFAULT 0,
    tests_passed INTEGER DEFAULT 0,
    feature_memory_used INTEGER DEFAULT 0,
    feature_swarm_used INTEGER DEFAULT 0,
    feature_web_used INTEGER DEFAULT 0,
    feature_email_used INTEGER DEFAULT 0,
    feature_mcp_used INTEGER DEFAULT 0,
    feature_side_panel_used INTEGER DEFAULT 0,
    feature_goal_used INTEGER DEFAULT 0,
    feature_selfdev_used INTEGER DEFAULT 0,
    feature_background_used INTEGER DEFAULT 0,
    feature_subagent_used INTEGER DEFAULT 0,
    unique_mcp_servers INTEGER DEFAULT 0,
    tool_cat_read_search INTEGER DEFAULT 0,
    tool_cat_write INTEGER DEFAULT 0,
    tool_cat_shell INTEGER DEFAULT 0,
    tool_cat_web INTEGER DEFAULT 0,
    tool_cat_memory INTEGER DEFAULT 0,
    tool_cat_subagent INTEGER DEFAULT 0,
    tool_cat_swarm INTEGER DEFAULT 0,
    tool_cat_email INTEGER DEFAULT 0,
    tool_cat_side_panel INTEGER DEFAULT 0,
    tool_cat_goal INTEGER DEFAULT 0,
    tool_cat_mcp INTEGER DEFAULT 0,
    tool_cat_other INTEGER DEFAULT 0,
    workflow_chat_only INTEGER DEFAULT 0,
    workflow_coding_used INTEGER DEFAULT 0,
    workflow_research_used INTEGER DEFAULT 0,
    workflow_tests_used INTEGER DEFAULT 0,
    workflow_background_used INTEGER DEFAULT 0,
    workflow_subagent_used INTEGER DEFAULT 0,
    workflow_swarm_used INTEGER DEFAULT 0,
    FOREIGN KEY (event_id) REFERENCES events(event_id)
);

CREATE TABLE IF NOT EXISTS daily_active_users (
    activity_date TEXT NOT NULL,
    telemetry_id TEXT NOT NULL,
    first_seen_at TEXT DEFAULT (datetime('now')),
    last_seen_at TEXT DEFAULT (datetime('now')),
    raw_active INTEGER DEFAULT 0,
    meaningful_active INTEGER DEFAULT 0,
    release_active INTEGER DEFAULT 0,
    meaningful_release_active INTEGER DEFAULT 0,
    session_start_count INTEGER DEFAULT 0,
    turn_end_count INTEGER DEFAULT 0,
    session_end_count INTEGER DEFAULT 0,
    session_crash_count INTEGER DEFAULT 0,
    ci_active INTEGER DEFAULT 0,
    last_is_ci INTEGER DEFAULT 0,
    last_build_channel TEXT,
    PRIMARY KEY (activity_date, telemetry_id)
);

CREATE INDEX IF NOT EXISTS idx_daily_active_date
    ON daily_active_users(activity_date);

CREATE INDEX IF NOT EXISTS idx_daily_active_date_release
    ON daily_active_users(activity_date, release_active, meaningful_release_active);

CREATE INDEX IF NOT EXISTS idx_daily_active_date_ci
    ON daily_active_users(activity_date, last_is_ci, meaningful_release_active);