| 1 | import type { Env } from "./env"; |
| 2 | |
| 3 | export const DEVELOPMENT_FINGERPRINT_PREFIX = "dev:"; |
| 4 | export const developmentGroupSQL = `groups.fingerprint LIKE 'dev:%'`; |
| 5 | |
| 6 | type GroupPriorityRow = { |
| 7 | fingerprint: string; |
| 8 | status: string; |
| 9 | severity: string; |
| 10 | regressed_at: string; |
| 11 | first_version: string; |
| 12 | count: number; |
| 13 | seen: string; |
| 14 | title: string; |
| 15 | last_version: string; |
| 16 | last_channel: string; |
| 17 | affected_installs?: number; |
| 18 | }; |
| 19 | |
| 20 | export function isDevelopmentGroup(row: Pick<GroupPriorityRow, "fingerprint">): boolean { |
| 21 | return row.fingerprint.startsWith(DEVELOPMENT_FINGERPRINT_PREFIX); |
| 22 | } |
| 23 | |
| 24 | export function effectiveGroupSeverity( |
| 25 | row: Pick<GroupPriorityRow, "fingerprint" | "severity" | "title">, |
| 26 | ): string { |
| 27 | if (row.severity === "critical") return row.severity; |
| 28 | if (isDevelopmentGroup(row)) return "low"; |
| 29 | if ( |
| 30 | row.title === "[window.error] Script error." || |
| 31 | row.title.includes("ResizeObserver loop ") || |
| 32 | row.title.includes("Minified React error #520") || |
| 33 | row.title.includes("additional File object is not a file on the disk") |
| 34 | ) { |
| 35 | return "low"; |
| 36 | } |
| 37 | return row.severity; |
| 38 | } |
| 39 | |
| 40 | function compareDiagnosticPriority(a: GroupPriorityRow, b: GroupPriorityRow, latestVersion: string): number { |
| 41 | const statusRank = (value: string) => (value === "open" ? 0 : 1); |
| 42 | const severityRank = (value: string) => ({ critical: 0, high: 1, medium: 2, low: 3 })[value] ?? 4; |
| 43 | return ( |
| 44 | Number(b.affected_installs ?? 0) - Number(a.affected_installs ?? 0) || |
| 45 | statusRank(a.status) - statusRank(b.status) || |
| 46 | severityRank(a.severity) - severityRank(b.severity) || |
| 47 | Number(b.first_version === latestVersion) - Number(a.first_version === latestVersion) || |
| 48 | Number(Boolean(b.regressed_at)) - Number(Boolean(a.regressed_at)) || |
| 49 | b.count - a.count || |
| 50 | b.seen.localeCompare(a.seen) |
| 51 | ); |
| 52 | } |
| 53 | |
| 54 | export function currentWindowSince(days: 7 | 30): string { |
| 55 | return `-${days - 1} day`; |
| 56 | } |
| 57 | |
| 58 | export function diagnosticWindowWhere(days: 7 | 30): string { |
| 59 | return `date(last_seen) >= date('now', '${currentWindowSince(days)}')`; |
| 60 | } |
| 61 | |
| 62 | export type DiagnosticBar = { label: string; users: number }; |
| 63 | |
| 64 | export type DiagnosticsQueryObserver = (label: string, durationMs: number, rows: number) => void; |
| 65 | |
| 66 | export type DiagnosticFacets = { |
| 67 | versions: DiagnosticBar[]; |
| 68 | platforms: DiagnosticBar[]; |
| 69 | osBuilds: DiagnosticBar[]; |
| 70 | osRevisions: DiagnosticBar[]; |
| 71 | distros: DiagnosticBar[]; |
| 72 | distroVersions: DiagnosticBar[]; |
| 73 | kernels: DiagnosticBar[]; |
| 74 | sessions: DiagnosticBar[]; |
| 75 | architectures: DiagnosticBar[]; |
| 76 | channels: DiagnosticBar[]; |
| 77 | runtimes: DiagnosticBar[]; |
| 78 | runtimeEngines: DiagnosticBar[]; |
| 79 | failureKinds: DiagnosticBar[]; |
| 80 | failureReasons: DiagnosticBar[]; |
| 81 | exitCodes: DiagnosticBar[]; |
| 82 | recoveries: DiagnosticBar[]; |
| 83 | gpuStates: DiagnosticBar[]; |
| 84 | }; |
| 85 | |
| 86 | const DIAGNOSTIC_FACET_CACHE_MS = 30_000; |
| 87 | const diagnosticFacetCache = new WeakMap<object, Map<7 | 30, { expiresAt: number; value: DiagnosticFacets }>>(); |
| 88 | |
| 89 | async function observedAll<T>( |
| 90 | statement: D1PreparedStatement, |
| 91 | label: string, |
| 92 | observe?: DiagnosticsQueryObserver, |
| 93 | ): Promise<{ results: T[] }> { |
| 94 | const started = performance.now(); |
| 95 | const result = await statement.all<T>(); |
| 96 | observe?.(label, performance.now() - started, result.results?.length ?? 0); |
| 97 | return result; |
| 98 | } |
| 99 | |
| 100 | async function observedFirst<T>( |
| 101 | statement: D1PreparedStatement, |
| 102 | label: string, |
| 103 | observe?: DiagnosticsQueryObserver, |
| 104 | ): Promise<T | null> { |
| 105 | const started = performance.now(); |
| 106 | const result = await statement.first<T>(); |
| 107 | observe?.(label, performance.now() - started, result ? 1 : 0); |
| 108 | return result; |
| 109 | } |
| 110 | |
| 111 | export async function diagnosticFacets( |
| 112 | env: Env, |
| 113 | days: 7 | 30, |
| 114 | observe?: DiagnosticsQueryObserver, |
| 115 | ): Promise<DiagnosticFacets> { |
| 116 | const cacheKey = env.DB as unknown as object; |
| 117 | const cached = diagnosticFacetCache.get(cacheKey)?.get(days); |
| 118 | if (cached && cached.expiresAt > Date.now()) { |
| 119 | observe?.("diagnostic_facets.cache", 0, 17); |
| 120 | return cached.value; |
| 121 | } |
| 122 | const since = currentWindowSince(days); |
| 123 | const facet = (name: string, expression: string, extra = "") => observedAll<DiagnosticBar>(env.DB.prepare( |
| 124 | `SELECT ${expression} AS label, COUNT(DISTINCT NULLIF(install_id, '')) AS users |
| 125 | FROM report_event_dimensions WHERE date >= date('now', '${since}') ${extra} |
| 126 | GROUP BY label ORDER BY users DESC LIMIT 20`, |
| 127 | ), `diagnostic_facets.${name}`, observe).then((result) => result.results); |
| 128 | const [versions, platforms, osBuilds, osRevisions, distros, distroVersions, kernels, sessions, architectures, channels, runtimes, runtimeEngines, failureKinds, failureReasons, exitCodes, recoveries, gpuStates] = await Promise.all([ |
| 129 | facet("version", "version", "AND version <> ''"), |
| 130 | facet("platforms", "os", "AND os <> ''"), |
| 131 | facet("os_builds", "CAST(os_build AS TEXT)", "AND os_build > 0"), |
| 132 | facet("os_revisions", "CAST(os_revision AS TEXT)", "AND os_revision > 0"), |
| 133 | facet("distros", "distro_id", "AND distro_id <> ''"), |
| 134 | facet("distro_versions", "distro_version", "AND distro_version <> ''"), |
| 135 | facet("kernels", "kernel_version", "AND kernel_version <> ''"), |
| 136 | facet("sessions", "session_type", "AND session_type <> ''"), |
| 137 | facet("architectures", "arch", "AND arch <> ''"), |
| 138 | facet("channels", "channel", "AND channel <> ''"), |
| 139 | facet("runtimes", "runtime_version", "AND runtime_version <> ''"), |
| 140 | facet("runtime_engines", "runtime_engine", "AND runtime_engine <> ''"), |
| 141 | facet("failure_kinds", "failure_kind", "AND failure_kind <> ''"), |
| 142 | facet("failure_reasons", "failure_reason", "AND failure_reason <> ''"), |
| 143 | facet("exit_codes", "exit_code", "AND exit_code <> ''"), |
| 144 | facet("recoveries", "recovery", "AND recovery <> ''"), |
| 145 | facet("gpu_states", "gpu_mode", "AND gpu_mode <> ''"), |
| 146 | ]); |
| 147 | const value = { versions, platforms, osBuilds, osRevisions, distros, distroVersions, kernels, sessions, architectures, channels, runtimes, runtimeEngines, failureKinds, failureReasons, exitCodes, recoveries, gpuStates }; |
| 148 | let byWindow = diagnosticFacetCache.get(cacheKey); |
| 149 | if (!byWindow) { |
| 150 | byWindow = new Map(); |
| 151 | diagnosticFacetCache.set(cacheKey, byWindow); |
| 152 | } |
| 153 | byWindow.set(days, { expiresAt: Date.now() + DIAGNOSTIC_FACET_CACHE_MS, value }); |
| 154 | return value; |
| 155 | } |
| 156 | |
| 157 | type DiagnosticsGroupFilters = { |
| 158 | status: string; |
| 159 | source: string; |
| 160 | version: string; |
| 161 | os: string; |
| 162 | platform: string; |
| 163 | osBuild: string; |
| 164 | osRevision?: string; |
| 165 | distroId?: string; |
| 166 | distroVersion?: string; |
| 167 | kernelVersion?: string; |
| 168 | sessionType?: string; |
| 169 | arch: string; |
| 170 | channel: string; |
| 171 | runtimeVersion: string; |
| 172 | runtimeEngine?: string; |
| 173 | failureKind: string; |
| 174 | failureReason: string; |
| 175 | exitCode?: string; |
| 176 | recovery: string; |
| 177 | gpu: string; |
| 178 | newLatest: boolean; |
| 179 | regressed: boolean; |
| 180 | windowDays: 7 | 30; |
| 181 | }; |
| 182 | |
| 183 | export async function crashGroups( |
| 184 | env: Env, |
| 185 | filters: DiagnosticsGroupFilters, |
| 186 | latestVersion: string, |
| 187 | observe?: DiagnosticsQueryObserver, |
| 188 | ) { |
| 189 | const where: string[] = [diagnosticWindowWhere(filters.windowDays)]; |
| 190 | const binds: unknown[] = []; |
| 191 | const add = (sql: string, value?: unknown) => { |
| 192 | where.push(sql); |
| 193 | if (value !== undefined) binds.push(value); |
| 194 | }; |
| 195 | if (filters.status) add("status = ?", filters.status); |
| 196 | if (filters.source) add("source = ?", filters.source); |
| 197 | const installWhere: string[] = [`date >= date('now', '${currentWindowSince(filters.windowDays)}')`]; |
| 198 | const installBinds: unknown[] = []; |
| 199 | const addInstall = (column: string, value: unknown) => { |
| 200 | installBinds.push(value); |
| 201 | installWhere.push(value === null ? `${column} IS NULL` : `${column} = ?`); |
| 202 | if (value === null) installBinds.pop(); |
| 203 | }; |
| 204 | if (filters.version) addInstall("version", filters.version); |
| 205 | if (filters.os) addInstall("os", filters.os); |
| 206 | if (filters.platform) addInstall("os", filters.platform); |
| 207 | if (filters.osBuild) addInstall("os_build", Number(filters.osBuild)); |
| 208 | if (filters.osRevision) addInstall("os_revision", Number(filters.osRevision)); |
| 209 | if (filters.distroId) addInstall("distro_id", filters.distroId); |
| 210 | if (filters.distroVersion) addInstall("distro_version", filters.distroVersion); |
| 211 | if (filters.kernelVersion) addInstall("kernel_version", filters.kernelVersion); |
| 212 | if (filters.sessionType) addInstall("session_type", filters.sessionType); |
| 213 | if (filters.arch) addInstall("arch", filters.arch); |
| 214 | if (filters.channel) addInstall("channel", filters.channel); |
| 215 | if (filters.runtimeVersion) addInstall("runtime_version", filters.runtimeVersion); |
| 216 | if (filters.runtimeEngine) addInstall("runtime_engine", filters.runtimeEngine); |
| 217 | if (filters.failureKind) addInstall("failure_kind", filters.failureKind); |
| 218 | if (filters.failureReason) addInstall("failure_reason", filters.failureReason); |
| 219 | if (filters.exitCode) addInstall("exit_code", filters.exitCode); |
| 220 | if (filters.recovery) addInstall("recovery", filters.recovery); |
| 221 | if (filters.gpu) addInstall("gpu_mode", filters.gpu); |
| 222 | if (installWhere.length > 1) where.push("COALESCE(diagnostics.window_events, 0) > 0"); |
| 223 | if (filters.newLatest && latestVersion) add("first_version = ?", latestVersion); |
| 224 | if (filters.regressed) where.push("regressed_at <> ''"); |
| 225 | let latestOrder = ""; |
| 226 | if (latestVersion) { |
| 227 | latestOrder = `CASE WHEN first_version = ? THEN 0 ELSE 1 END,`; |
| 228 | binds.push(latestVersion); |
| 229 | } |
| 230 | const reportWindow = currentWindowSince(filters.windowDays); |
| 231 | const pingWhere = [`date >= date('now', '${reportWindow}')`]; |
| 232 | const pingBinds: unknown[] = []; |
| 233 | const pingBaseWhere = [`date >= date('now', '${reportWindow}')`]; |
| 234 | const pingBaseBinds: unknown[] = []; |
| 235 | const dimensionKnown: string[] = []; |
| 236 | const addPing = (column: string, value: unknown) => { |
| 237 | pingWhere.push(`${column} = ?`); |
| 238 | pingBinds.push(value); |
| 239 | }; |
| 240 | const addPingBase = (column: string, value: unknown) => { |
| 241 | pingBaseWhere.push(`${column} = ?`); |
| 242 | pingBaseBinds.push(value); |
| 243 | addPing(column, value); |
| 244 | }; |
| 245 | if (filters.version) addPingBase("version", filters.version); |
| 246 | if (filters.os) addPingBase("os", filters.os); |
| 247 | if (filters.platform) addPingBase("os", filters.platform); |
| 248 | if (!filters.os && !filters.platform && (filters.osBuild || filters.osRevision)) addPingBase("os", "windows"); |
| 249 | if (!filters.os && !filters.platform && (filters.distroId || filters.distroVersion || filters.kernelVersion || filters.sessionType)) addPingBase("os", "linux"); |
| 250 | if (filters.osBuild) { addPing("os_build", Number(filters.osBuild)); dimensionKnown.push("os_build > 0"); } |
| 251 | if (filters.osRevision) { addPing("os_revision", Number(filters.osRevision)); dimensionKnown.push("os_revision > 0"); } |
| 252 | if (filters.distroId) { addPing("distro_id", filters.distroId); dimensionKnown.push("distro_id <> ''"); } |
| 253 | if (filters.distroVersion) { addPing("distro_version", filters.distroVersion); dimensionKnown.push("distro_version <> ''"); } |
| 254 | if (filters.kernelVersion) { addPing("kernel_version", filters.kernelVersion); dimensionKnown.push("kernel_version <> ''"); } |
| 255 | if (filters.sessionType) { addPing("session_type", filters.sessionType); dimensionKnown.push("session_type <> ''"); } |
| 256 | if (filters.arch) addPingBase("arch", filters.arch); |
| 257 | if (filters.channel) { addPing("channel", filters.channel); dimensionKnown.push("channel <> ''"); } |
| 258 | if (filters.runtimeVersion) { addPing("runtime_version", filters.runtimeVersion); dimensionKnown.push("runtime_version <> ''"); } |
| 259 | if (filters.runtimeEngine) { addPing("runtime_engine", filters.runtimeEngine); dimensionKnown.push("runtime_engine <> ''"); } |
| 260 | if (filters.gpu) { addPing("gpu_mode", filters.gpu); dimensionKnown.push("gpu_mode <> ''"); } |
| 261 | const activeInstalls = `(SELECT COUNT(DISTINCT install_id) FROM pings WHERE ${pingWhere.join(" AND ")})`; |
| 262 | const baseInstalls = `(SELECT COUNT(DISTINCT install_id) FROM pings WHERE ${pingBaseWhere.join(" AND ")})`; |
| 263 | const coveredInstalls = `(SELECT COUNT(DISTINCT install_id) FROM pings WHERE ${[...pingBaseWhere, ...dimensionKnown].join(" AND ")})`; |
| 264 | const samePingWindow = pingWhere.length === 1 && pingBaseWhere.length === 1 && dimensionKnown.length === 0; |
| 265 | const pingStatsJoin = samePingWindow |
| 266 | ? `CROSS JOIN (SELECT COUNT(DISTINCT install_id) AS installs FROM pings WHERE ${pingWhere.join(" AND ")}) ping_stats` |
| 267 | : ""; |
| 268 | const activeInstallExpr = samePingWindow ? "ping_stats.installs" : activeInstalls; |
| 269 | const baseInstallExpr = samePingWindow ? "ping_stats.installs" : baseInstalls; |
| 270 | const coveredInstallExpr = samePingWindow ? "ping_stats.installs" : coveredInstalls; |
| 271 | const diagnosticJoin = installWhere.length > 1 |
| 272 | ? `LEFT JOIN ( |
| 273 | SELECT fingerprint, |
| 274 | COUNT(DISTINCT NULLIF(install_id, '')) AS affected_installs, |
| 275 | SUM(events) AS window_events, |
| 276 | SUM(CASE WHEN install_id <> '' THEN events ELSE 0 END) AS identified_events |
| 277 | FROM report_event_dimensions WHERE ${installWhere.join(" AND ")} GROUP BY fingerprint |
| 278 | ) diagnostics ON diagnostics.fingerprint = groups.fingerprint` |
| 279 | : `LEFT JOIN ( |
| 280 | SELECT daily.fingerprint, |
| 281 | COALESCE(installs.affected_installs, 0) AS affected_installs, |
| 282 | SUM(daily.events) AS window_events, |
| 283 | SUM(daily.identified_events) AS identified_events |
| 284 | FROM report_daily AS daily |
| 285 | LEFT JOIN ( |
| 286 | SELECT fingerprint, COUNT(DISTINCT install_id) AS affected_installs |
| 287 | FROM report_installations WHERE date >= date('now', '${currentWindowSince(filters.windowDays)}') |
| 288 | GROUP BY fingerprint |
| 289 | ) installs ON installs.fingerprint = daily.fingerprint |
| 290 | WHERE daily.date >= date('now', '${currentWindowSince(filters.windowDays)}') |
| 291 | GROUP BY daily.fingerprint, installs.affected_installs |
| 292 | ) diagnostics ON diagnostics.fingerprint = groups.fingerprint`; |
| 293 | const sql = `SELECT groups.fingerprint, kind, count, first_version, last_version, substr(last_seen, 1, 10) AS seen, |
| 294 | status, title, source, label, error_type, top_frame, severity, last_os, last_arch, last_channel, regressed_at, last_category, |
| 295 | COALESCE(diagnostics.affected_installs, 0) AS affected_installs, |
| 296 | COALESCE(diagnostics.window_events, 0) AS window_events, |
| 297 | COALESCE(diagnostics.identified_events, 0) AS identified_events, |
| 298 | ${activeInstallExpr} AS active_build_installs, |
| 299 | ${baseInstallExpr} AS dimension_base_installs, |
| 300 | ${coveredInstallExpr} AS dimension_covered_installs |
| 301 | FROM groups |
| 302 | ${diagnosticJoin} |
| 303 | ${pingStatsJoin} |
| 304 | ${where.length ? `WHERE ${where.join(" AND ")}` : ""} |
| 305 | ORDER BY |
| 306 | affected_installs DESC, |
| 307 | window_events DESC, |
| 308 | CASE WHEN status = 'open' THEN 0 ELSE 1 END, |
| 309 | CASE |
| 310 | WHEN severity = 'critical' THEN 0 |
| 311 | WHEN ${developmentGroupSQL} |
| 312 | OR title = '[window.error] Script error.' |
| 313 | OR title LIKE '%ResizeObserver loop %' |
| 314 | OR title LIKE '%Minified React error #520%' |
| 315 | OR title LIKE '%additional File object is not a file on the disk%' |
| 316 | THEN 3 |
| 317 | WHEN severity = 'high' THEN 1 |
| 318 | WHEN severity = 'medium' THEN 2 |
| 319 | ELSE 3 |
| 320 | END, |
| 321 | ${latestOrder} |
| 322 | CASE WHEN regressed_at <> '' THEN 0 ELSE 1 END, |
| 323 | count DESC, |
| 324 | last_seen DESC |
| 325 | LIMIT 50`; |
| 326 | const allBinds = [...pingBinds, ...pingBaseBinds, ...pingBaseBinds, ...installBinds, ...binds]; |
| 327 | const stmt = env.DB.prepare(sql); |
| 328 | const query = allBinds.length ? stmt.bind(...allBinds) : stmt; |
| 329 | const started = performance.now(); |
| 330 | const result = await query.all<GroupPriorityRow & { |
| 331 | kind: string; |
| 332 | source: string; |
| 333 | label: string; |
| 334 | error_type: string; |
| 335 | top_frame: string; |
| 336 | last_os: string; |
| 337 | last_arch: string; |
| 338 | window_events: number; |
| 339 | identified_events: number; |
| 340 | active_build_installs: number; |
| 341 | dimension_base_installs: number; |
| 342 | dimension_covered_installs: number; |
| 343 | }>(); |
| 344 | observe?.("diagnostic_groups", performance.now() - started, result.results?.length ?? 0); |
| 345 | result.results = result.results |
| 346 | .map((row) => ({ |
| 347 | ...row, |
| 348 | severity: effectiveGroupSeverity(row), |
| 349 | development: isDevelopmentGroup(row), |
| 350 | identity_coverage: row.window_events ? row.identified_events / row.window_events : 0, |
| 351 | dimension_coverage: row.dimension_base_installs |
| 352 | ? row.dimension_covered_installs / row.dimension_base_installs |
| 353 | : dimensionKnown.length ? 0 : 1, |
| 354 | impact_rate: row.active_build_installs ? Number(row.affected_installs ?? 0) / row.active_build_installs : null, |
| 355 | })) |
| 356 | .sort((a, b) => compareDiagnosticPriority(a, b, latestVersion)); |
| 357 | return result; |
| 358 | } |
| 359 | |
| 360 | type ReportAggregateInput = { |
| 361 | installId?: string; |
| 362 | version: string; |
| 363 | os: string; |
| 364 | arch: string; |
| 365 | device?: { |
| 366 | osBuild?: number; |
| 367 | osRevision?: number; |
| 368 | distroId?: string; |
| 369 | distroVersion?: string; |
| 370 | kernelVersion?: string; |
| 371 | sessionType?: string; |
| 372 | }; |
| 373 | diagnostics?: { |
| 374 | subjectVersion?: string; |
| 375 | subjectChannel?: string; |
| 376 | }; |
| 377 | }; |
| 378 | |
| 379 | type WebRuntimeAggregateInput = { |
| 380 | engine: string; |
| 381 | runtimeVersion: string; |
| 382 | kind: string; |
| 383 | reason: string; |
| 384 | exitCode?: number; |
| 385 | recovery: string; |
| 386 | gpuMode: string; |
| 387 | }; |
| 388 | |
| 389 | export function reportAggregateStatements( |
| 390 | db: Env["DB"], |
| 391 | report: ReportAggregateInput, |
| 392 | fingerprint: string, |
| 393 | channel: string, |
| 394 | webRuntime?: WebRuntimeAggregateInput, |
| 395 | ): D1PreparedStatement[] { |
| 396 | const subjectVersion = report.diagnostics?.subjectVersion || report.version; |
| 397 | const subjectChannel = report.diagnostics?.subjectChannel || channel; |
| 398 | const statements = [ |
| 399 | db.prepare( |
| 400 | `INSERT INTO report_daily (date, fingerprint, events, identified_events) |
| 401 | VALUES (date('now'), ?1, 1, ?2) |
| 402 | ON CONFLICT (date, fingerprint) DO UPDATE SET |
| 403 | events = events + 1, identified_events = identified_events + ?2`, |
| 404 | ).bind(fingerprint, report.installId ? 1 : 0), |
| 405 | ]; |
| 406 | if (report.installId) { |
| 407 | statements.push( |
| 408 | db.prepare( |
| 409 | `INSERT INTO report_installations ( |
| 410 | date, fingerprint, install_id, version, os, arch, os_build, os_revision, |
| 411 | distro_id, distro_version, kernel_version, session_type, channel, |
| 412 | runtime_engine, runtime_version, failure_kind, failure_reason, exit_code, recovery, gpu_mode, events |
| 413 | ) VALUES (date('now'), ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, 1) |
| 414 | ON CONFLICT (date, fingerprint, install_id) DO UPDATE SET |
| 415 | version = ?3, os = ?4, arch = ?5, os_build = ?6, os_revision = ?7, |
| 416 | distro_id = ?8, distro_version = ?9, kernel_version = ?10, session_type = ?11, channel = ?12, |
| 417 | runtime_engine = ?13, runtime_version = ?14, failure_kind = ?15, failure_reason = ?16, |
| 418 | exit_code = ?17, recovery = ?18, gpu_mode = ?19, events = events + 1`, |
| 419 | ).bind( |
| 420 | fingerprint, report.installId, subjectVersion, report.os, report.arch, |
| 421 | report.device?.osBuild ?? 0, report.device?.osRevision ?? 0, |
| 422 | report.device?.distroId ?? "", report.device?.distroVersion ?? "", report.device?.kernelVersion ?? "", |
| 423 | report.device?.sessionType ?? "", subjectChannel, |
| 424 | webRuntime?.engine ?? "", webRuntime?.runtimeVersion ?? "", webRuntime?.kind ?? "", webRuntime?.reason ?? "", |
| 425 | webRuntime?.exitCode ?? null, webRuntime?.recovery ?? "", webRuntime?.gpuMode ?? "unknown", |
| 426 | ), |
| 427 | ); |
| 428 | } |
| 429 | statements.push( |
| 430 | db.prepare( |
| 431 | `INSERT INTO report_event_dimensions ( |
| 432 | date, fingerprint, install_id, version, os, arch, os_build, os_revision, |
| 433 | distro_id, distro_version, kernel_version, session_type, channel, |
| 434 | runtime_engine, runtime_version, failure_kind, failure_reason, exit_code, recovery, gpu_mode, events |
| 435 | ) VALUES (date('now'), ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, 1) |
| 436 | ON CONFLICT ( |
| 437 | date, fingerprint, install_id, version, os, arch, os_build, os_revision, |
| 438 | distro_id, distro_version, kernel_version, session_type, channel, |
| 439 | runtime_engine, runtime_version, failure_kind, failure_reason, exit_code, recovery, gpu_mode |
| 440 | ) DO UPDATE SET events = events + 1`, |
| 441 | ).bind( |
| 442 | fingerprint, report.installId ?? "", subjectVersion, report.os, report.arch, |
| 443 | report.device?.osBuild ?? 0, report.device?.osRevision ?? 0, |
| 444 | report.device?.distroId ?? "", report.device?.distroVersion ?? "", report.device?.kernelVersion ?? "", |
| 445 | report.device?.sessionType ?? "", subjectChannel, |
| 446 | webRuntime?.engine ?? "", webRuntime?.runtimeVersion ?? "", webRuntime?.kind ?? "", webRuntime?.reason ?? "", |
| 447 | webRuntime?.exitCode === undefined ? "unknown" : String(webRuntime.exitCode), webRuntime?.recovery ?? "", |
| 448 | webRuntime?.gpuMode ?? "unknown", |
| 449 | ), |
| 450 | ); |
| 451 | return statements; |
| 452 | } |
| 453 | |
| 454 | export type GroupDiagnosticSummary = { |
| 455 | windowEvents: number; |
| 456 | identifiedEvents: number; |
| 457 | affectedInstalls: number; |
| 458 | linkedIncidents: number; |
| 459 | distributions: { facet: string; value: string; installs: number; events: number }[]; |
| 460 | }; |
| 461 | |
| 462 | export async function groupDiagnosticSummary( |
| 463 | env: Env, |
| 464 | fingerprint: string, |
| 465 | observe?: DiagnosticsQueryObserver, |
| 466 | ): Promise<GroupDiagnosticSummary> { |
| 467 | const [totals, distributions, attribution] = await Promise.all([ |
| 468 | observedFirst<{ |
| 469 | window_events: number; |
| 470 | identified_events: number; |
| 471 | affected_installs: number; |
| 472 | linked_incidents: number; |
| 473 | }>(env.DB.prepare( |
| 474 | `SELECT |
| 475 | COALESCE(daily.window_events, 0) AS window_events, |
| 476 | COALESCE(daily.identified_events, 0) AS identified_events, |
| 477 | COALESCE(installs.affected_installs, 0) AS affected_installs, |
| 478 | COALESCE(incidents.linked_incidents, 0) AS linked_incidents |
| 479 | FROM ( |
| 480 | SELECT SUM(events) AS window_events, SUM(identified_events) AS identified_events |
| 481 | FROM report_daily WHERE fingerprint = ?1 AND date >= date('now', '-29 day') |
| 482 | ) daily |
| 483 | CROSS JOIN ( |
| 484 | SELECT COUNT(DISTINCT install_id) AS affected_installs |
| 485 | FROM report_installations WHERE fingerprint = ?1 AND date >= date('now', '-29 day') |
| 486 | ) installs |
| 487 | CROSS JOIN ( |
| 488 | SELECT COUNT(*) AS linked_incidents FROM report_incidents |
| 489 | WHERE fingerprint = ?1 AND date >= date('now', '-29 day') |
| 490 | ) incidents`, |
| 491 | ).bind(fingerprint), "group_diagnostic_totals", observe), |
| 492 | observedAll<{ facet: string; value: string; installs: number; events: number }>(env.DB.prepare( |
| 493 | `WITH window AS MATERIALIZED ( |
| 494 | SELECT os, arch, os_build, os_revision, runtime_version, runtime_engine, |
| 495 | distro_id, distro_version, kernel_version, session_type, |
| 496 | failure_kind, failure_reason, exit_code, gpu_mode, recovery, |
| 497 | install_id, events |
| 498 | FROM report_event_dimensions |
| 499 | WHERE fingerprint = ?1 AND date >= date('now', '-29 day') |
| 500 | ), facts AS ( |
| 501 | SELECT 'platform' AS facet, os || ' ' || arch AS value, install_id, events FROM window WHERE os <> '' |
| 502 | UNION ALL SELECT 'osBuild', CAST(os_build AS TEXT), install_id, events FROM window WHERE os_build > 0 |
| 503 | UNION ALL SELECT 'osRevision', CAST(os_revision AS TEXT), install_id, events FROM window WHERE os_revision > 0 |
| 504 | UNION ALL SELECT 'runtime', runtime_version, install_id, events FROM window WHERE runtime_version <> '' |
| 505 | UNION ALL SELECT 'runtimeEngine', runtime_engine, install_id, events FROM window WHERE runtime_engine <> '' |
| 506 | UNION ALL SELECT 'distro', distro_id || ' ' || distro_version, install_id, events FROM window WHERE distro_id <> '' |
| 507 | UNION ALL SELECT 'kernel', kernel_version, install_id, events FROM window WHERE kernel_version <> '' |
| 508 | UNION ALL SELECT 'session', session_type, install_id, events FROM window WHERE session_type <> '' |
| 509 | UNION ALL SELECT 'kind', failure_kind, install_id, events FROM window WHERE failure_kind <> '' |
| 510 | UNION ALL SELECT 'reason', failure_reason, install_id, events FROM window WHERE failure_reason <> '' |
| 511 | UNION ALL SELECT 'exitCode', exit_code, install_id, events FROM window |
| 512 | UNION ALL SELECT 'gpu', gpu_mode, install_id, events FROM window |
| 513 | UNION ALL SELECT 'recovery', recovery, install_id, events FROM window WHERE recovery <> '' |
| 514 | ), grouped AS ( |
| 515 | SELECT facet, value, COUNT(DISTINCT NULLIF(install_id, '')) AS installs, SUM(events) AS events |
| 516 | FROM facts GROUP BY facet, value |
| 517 | ), ranked AS ( |
| 518 | SELECT facet, value, installs, events, |
| 519 | ROW_NUMBER() OVER (PARTITION BY facet ORDER BY installs DESC, events DESC, value) AS rank |
| 520 | FROM grouped |
| 521 | ) |
| 522 | SELECT facet, value, installs, events |
| 523 | FROM ranked WHERE rank <= 20 ORDER BY facet, rank`, |
| 524 | ).bind(fingerprint), "group_diagnostic_distributions", observe), |
| 525 | observedAll<{ |
| 526 | facet: string; |
| 527 | value: string; |
| 528 | installs: number; |
| 529 | events: number; |
| 530 | }>( |
| 531 | env.DB.prepare( |
| 532 | `SELECT 'faultVersion' AS facet, subject_version AS value, 0 AS installs, SUM(events) AS events |
| 533 | FROM report_attribution_daily WHERE fingerprint = ?1 AND date >= date('now', '-29 day') AND subject_version <> '' |
| 534 | GROUP BY subject_version |
| 535 | UNION ALL |
| 536 | SELECT 'observerVersion' AS facet, observer_version AS value, 0 AS installs, SUM(events) AS events |
| 537 | FROM report_attribution_daily WHERE fingerprint = ?1 AND date >= date('now', '-29 day') AND observer_version <> '' |
| 538 | GROUP BY observer_version |
| 539 | ORDER BY facet, events DESC LIMIT 40`, |
| 540 | ).bind(fingerprint), |
| 541 | "group_attribution_distributions", |
| 542 | observe, |
| 543 | ), |
| 544 | ]); |
| 545 | return { |
| 546 | windowEvents: Number(totals?.window_events ?? 0), |
| 547 | identifiedEvents: Number(totals?.identified_events ?? 0), |
| 548 | affectedInstalls: Number(totals?.affected_installs ?? 0), |
| 549 | linkedIncidents: Number(totals?.linked_incidents ?? 0), |
| 550 | distributions: [...distributions.results, ...attribution.results], |
| 551 | }; |
| 552 | } |
| 553 |