jcode/telemetry-worker/schema.sql at master · connect2aameer/jcode · GitHub - github.com

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
-- 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,
    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,
    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,
    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,
    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,
    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_turn_index ON events(turn_index);
CREATE INDEX IF NOT EXISTS idx_events_session_start_hour_utc ON events(session_start_hour_utc);
CREATE INDEX IF NOT EXISTS idx_events_multi_sessioned ON events(multi_sessioned);

CREATE TABLE IF NOT EXISTS session_details (
    event_id TEXT PRIMARY KEY,
    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,
    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)
);