| 1 | -- Diagnostics v2: additive cross-platform attribution and 30-day installation counts. |
| 2 | -- Apply after a D1 backup and before deploying the matching Worker. |
| 3 | |
| 4 | ALTER TABLE reports ADD COLUMN webview2 TEXT NOT NULL DEFAULT ''; |
| 5 | ALTER TABLE reports ADD COLUMN web_runtime TEXT NOT NULL DEFAULT ''; |
| 6 | |
| 7 | ALTER TABLE pings ADD COLUMN os_build INTEGER NOT NULL DEFAULT 0; |
| 8 | ALTER TABLE pings ADD COLUMN os_revision INTEGER NOT NULL DEFAULT 0; |
| 9 | ALTER TABLE pings ADD COLUMN channel TEXT NOT NULL DEFAULT ''; |
| 10 | ALTER TABLE pings ADD COLUMN distro_id TEXT NOT NULL DEFAULT ''; |
| 11 | ALTER TABLE pings ADD COLUMN distro_version TEXT NOT NULL DEFAULT ''; |
| 12 | ALTER TABLE pings ADD COLUMN kernel_version TEXT NOT NULL DEFAULT ''; |
| 13 | ALTER TABLE pings ADD COLUMN session_type TEXT NOT NULL DEFAULT ''; |
| 14 | ALTER TABLE pings ADD COLUMN runtime_engine TEXT NOT NULL DEFAULT ''; |
| 15 | ALTER TABLE pings ADD COLUMN runtime_version TEXT NOT NULL DEFAULT ''; |
| 16 | ALTER TABLE pings ADD COLUMN gpu_mode TEXT NOT NULL DEFAULT ''; |
| 17 | ALTER TABLE cli_pings ADD COLUMN os_build INTEGER NOT NULL DEFAULT 0; |
| 18 | ALTER TABLE cli_pings ADD COLUMN os_revision INTEGER NOT NULL DEFAULT 0; |
| 19 | ALTER TABLE cli_pings ADD COLUMN channel TEXT NOT NULL DEFAULT ''; |
| 20 | ALTER TABLE cli_pings ADD COLUMN distro_id TEXT NOT NULL DEFAULT ''; |
| 21 | ALTER TABLE cli_pings ADD COLUMN distro_version TEXT NOT NULL DEFAULT ''; |
| 22 | ALTER TABLE cli_pings ADD COLUMN kernel_version TEXT NOT NULL DEFAULT ''; |
| 23 | ALTER TABLE cli_pings ADD COLUMN session_type TEXT NOT NULL DEFAULT ''; |
| 24 | ALTER TABLE cli_pings ADD COLUMN runtime_engine TEXT NOT NULL DEFAULT ''; |
| 25 | ALTER TABLE cli_pings ADD COLUMN runtime_version TEXT NOT NULL DEFAULT ''; |
| 26 | ALTER TABLE cli_pings ADD COLUMN gpu_mode TEXT NOT NULL DEFAULT ''; |
| 27 | |
| 28 | -- metric_users and cli_metric_users were retired in #9379 and must not be recreated. |
| 29 | CREATE TABLE IF NOT EXISTS report_daily ( |
| 30 | date TEXT NOT NULL, |
| 31 | fingerprint TEXT NOT NULL, |
| 32 | events INTEGER NOT NULL DEFAULT 0, |
| 33 | identified_events INTEGER NOT NULL DEFAULT 0, |
| 34 | PRIMARY KEY (date, fingerprint) |
| 35 | ); |
| 36 | |
| 37 | CREATE TABLE IF NOT EXISTS report_installations ( |
| 38 | date TEXT NOT NULL, |
| 39 | fingerprint TEXT NOT NULL, |
| 40 | install_id TEXT NOT NULL, |
| 41 | version TEXT NOT NULL, |
| 42 | os TEXT NOT NULL, |
| 43 | arch TEXT NOT NULL, |
| 44 | os_build INTEGER NOT NULL DEFAULT 0, |
| 45 | os_revision INTEGER NOT NULL DEFAULT 0, |
| 46 | distro_id TEXT NOT NULL DEFAULT '', |
| 47 | distro_version TEXT NOT NULL DEFAULT '', |
| 48 | kernel_version TEXT NOT NULL DEFAULT '', |
| 49 | session_type TEXT NOT NULL DEFAULT '', |
| 50 | channel TEXT NOT NULL DEFAULT '', |
| 51 | runtime_engine TEXT NOT NULL DEFAULT '', |
| 52 | runtime_version TEXT NOT NULL DEFAULT '', |
| 53 | failure_kind TEXT NOT NULL DEFAULT '', |
| 54 | failure_reason TEXT NOT NULL DEFAULT '', |
| 55 | exit_code INTEGER, |
| 56 | recovery TEXT NOT NULL DEFAULT '', |
| 57 | gpu_mode TEXT NOT NULL DEFAULT 'unknown', |
| 58 | events INTEGER NOT NULL DEFAULT 0, |
| 59 | PRIMARY KEY (date, fingerprint, install_id) |
| 60 | ); |
| 61 | |
| 62 | CREATE INDEX IF NOT EXISTS report_installations_fingerprint_date |
| 63 | ON report_installations (fingerprint, date); |
| 64 | |
| 65 | -- Empty install_id preserves unlinked events for filtered event totals without |
| 66 | -- inventing a device identity; device counts always exclude the empty sentinel. |
| 67 | CREATE TABLE IF NOT EXISTS report_event_dimensions ( |
| 68 | date TEXT NOT NULL, |
| 69 | fingerprint TEXT NOT NULL, |
| 70 | install_id TEXT NOT NULL, |
| 71 | version TEXT NOT NULL, |
| 72 | os TEXT NOT NULL, |
| 73 | arch TEXT NOT NULL, |
| 74 | os_build INTEGER NOT NULL DEFAULT 0, |
| 75 | os_revision INTEGER NOT NULL DEFAULT 0, |
| 76 | distro_id TEXT NOT NULL DEFAULT '', |
| 77 | distro_version TEXT NOT NULL DEFAULT '', |
| 78 | kernel_version TEXT NOT NULL DEFAULT '', |
| 79 | session_type TEXT NOT NULL DEFAULT '', |
| 80 | channel TEXT NOT NULL DEFAULT '', |
| 81 | runtime_engine TEXT NOT NULL DEFAULT '', |
| 82 | runtime_version TEXT NOT NULL DEFAULT '', |
| 83 | failure_kind TEXT NOT NULL DEFAULT '', |
| 84 | failure_reason TEXT NOT NULL DEFAULT '', |
| 85 | exit_code TEXT NOT NULL DEFAULT 'unknown', |
| 86 | recovery TEXT NOT NULL DEFAULT '', |
| 87 | gpu_mode TEXT NOT NULL DEFAULT 'unknown', |
| 88 | events INTEGER NOT NULL DEFAULT 0, |
| 89 | PRIMARY KEY ( |
| 90 | date, fingerprint, install_id, version, os, arch, os_build, os_revision, |
| 91 | distro_id, distro_version, kernel_version, session_type, channel, |
| 92 | runtime_engine, runtime_version, failure_kind, failure_reason, exit_code, recovery, gpu_mode |
| 93 | ) |
| 94 | ); |
| 95 | |
| 96 | CREATE INDEX IF NOT EXISTS report_event_dimensions_fingerprint_date |
| 97 | ON report_event_dimensions (fingerprint, date); |
| 98 | |
| 99 | CREATE INDEX IF NOT EXISTS pings_diagnostics_window |
| 100 | ON pings (date, os, os_build, distro_id, session_type, channel); |
| 101 | |
| 102 | CREATE TABLE IF NOT EXISTS diagnostics_meta ( |
| 103 | key TEXT PRIMARY KEY, |
| 104 | value TEXT NOT NULL |
| 105 | ); |
| 106 | |
| 107 | INSERT OR IGNORE INTO diagnostics_meta (key, value) |
| 108 | VALUES ('installation_linked_since', date('now')); |
| 109 |