| 1 | -- Apply: wrangler d1 execute reasonix-crash --remote --file=schema.sql |
| 2 | CREATE TABLE IF NOT EXISTS groups ( |
| 3 | fingerprint TEXT PRIMARY KEY, |
| 4 | kind TEXT NOT NULL, |
| 5 | count INTEGER NOT NULL, |
| 6 | first_seen TEXT NOT NULL, |
| 7 | last_seen TEXT NOT NULL, |
| 8 | first_version TEXT NOT NULL DEFAULT '', |
| 9 | last_version TEXT NOT NULL, |
| 10 | status TEXT NOT NULL DEFAULT 'open', |
| 11 | note TEXT NOT NULL DEFAULT '', |
| 12 | title TEXT NOT NULL DEFAULT '', |
| 13 | source TEXT NOT NULL DEFAULT 'legacy', |
| 14 | label TEXT NOT NULL DEFAULT '', |
| 15 | error_type TEXT NOT NULL DEFAULT '', |
| 16 | top_frame TEXT NOT NULL DEFAULT '', |
| 17 | severity TEXT NOT NULL DEFAULT 'medium', |
| 18 | last_os TEXT NOT NULL DEFAULT '', |
| 19 | last_arch TEXT NOT NULL DEFAULT '', |
| 20 | last_build_commit TEXT NOT NULL DEFAULT '', |
| 21 | last_channel TEXT NOT NULL DEFAULT '', |
| 22 | resolved_in TEXT NOT NULL DEFAULT '', |
| 23 | resolved_at TEXT NOT NULL DEFAULT '', |
| 24 | regressed_at TEXT NOT NULL DEFAULT '', |
| 25 | regression_review TEXT NOT NULL DEFAULT '', |
| 26 | resolution_platform TEXT NOT NULL DEFAULT '', |
| 27 | resolution_runtime TEXT NOT NULL DEFAULT '', |
| 28 | resolution_basis TEXT NOT NULL DEFAULT '', |
| 29 | last_category TEXT NOT NULL DEFAULT '', |
| 30 | last_sample_at TEXT NOT NULL DEFAULT '' |
| 31 | ); |
| 32 | |
| 33 | CREATE TABLE IF NOT EXISTS reports ( |
| 34 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 35 | fingerprint TEXT NOT NULL, |
| 36 | kind TEXT NOT NULL, |
| 37 | version TEXT NOT NULL, |
| 38 | os TEXT NOT NULL, |
| 39 | arch TEXT NOT NULL, |
| 40 | message TEXT NOT NULL, |
| 41 | device TEXT NOT NULL DEFAULT '', |
| 42 | created_at TEXT NOT NULL, |
| 43 | source TEXT NOT NULL DEFAULT 'legacy', |
| 44 | label TEXT NOT NULL DEFAULT '', |
| 45 | error_type TEXT NOT NULL DEFAULT '', |
| 46 | error_message TEXT NOT NULL DEFAULT '', |
| 47 | error_family TEXT NOT NULL DEFAULT '', |
| 48 | top_frame TEXT NOT NULL DEFAULT '', |
| 49 | build_commit TEXT NOT NULL DEFAULT '', |
| 50 | channel TEXT NOT NULL DEFAULT '', |
| 51 | language TEXT NOT NULL DEFAULT '', |
| 52 | view TEXT NOT NULL DEFAULT '', |
| 53 | breadcrumbs TEXT NOT NULL DEFAULT '', |
| 54 | component_stack TEXT NOT NULL DEFAULT '', |
| 55 | stack TEXT NOT NULL DEFAULT '', |
| 56 | occurred_at TEXT NOT NULL DEFAULT '', |
| 57 | webview2 TEXT NOT NULL DEFAULT '', |
| 58 | web_runtime TEXT NOT NULL DEFAULT '', |
| 59 | event_id TEXT NOT NULL DEFAULT '', |
| 60 | incident_id TEXT NOT NULL DEFAULT '', |
| 61 | diagnostics TEXT NOT NULL DEFAULT '' |
| 62 | ); |
| 63 | |
| 64 | CREATE INDEX IF NOT EXISTS reports_fingerprint ON reports (fingerprint); |
| 65 | CREATE INDEX IF NOT EXISTS reports_fingerprint_id ON reports (fingerprint, id DESC); |
| 66 | CREATE INDEX IF NOT EXISTS reports_event_id ON reports (event_id); |
| 67 | |
| 68 | CREATE TABLE IF NOT EXISTS report_events ( |
| 69 | event_id TEXT PRIMARY KEY, |
| 70 | incident_id TEXT NOT NULL DEFAULT '', |
| 71 | fingerprint TEXT NOT NULL, |
| 72 | received_at TEXT NOT NULL, |
| 73 | projected_at TEXT NOT NULL |
| 74 | ); |
| 75 | CREATE INDEX IF NOT EXISTS report_events_fingerprint ON report_events (fingerprint, received_at); |
| 76 | CREATE INDEX IF NOT EXISTS report_events_incident ON report_events (incident_id, received_at); |
| 77 | |
| 78 | CREATE TABLE IF NOT EXISTS report_attribution_daily ( |
| 79 | date TEXT NOT NULL, |
| 80 | fingerprint TEXT NOT NULL, |
| 81 | subject_version TEXT NOT NULL DEFAULT '', |
| 82 | observer_version TEXT NOT NULL DEFAULT '', |
| 83 | subject_channel TEXT NOT NULL DEFAULT '', |
| 84 | observer_channel TEXT NOT NULL DEFAULT '', |
| 85 | category TEXT NOT NULL DEFAULT '', |
| 86 | evidence TEXT NOT NULL DEFAULT '', |
| 87 | events INTEGER NOT NULL DEFAULT 0, |
| 88 | PRIMARY KEY (date, fingerprint, subject_version, observer_version, subject_channel, observer_channel, category, evidence) |
| 89 | ); |
| 90 | CREATE INDEX IF NOT EXISTS report_attribution_fingerprint_date ON report_attribution_daily (fingerprint, date); |
| 91 | |
| 92 | CREATE TABLE IF NOT EXISTS report_incidents ( |
| 93 | date TEXT NOT NULL, |
| 94 | fingerprint TEXT NOT NULL, |
| 95 | incident_id TEXT NOT NULL, |
| 96 | subject_version TEXT NOT NULL DEFAULT '', |
| 97 | PRIMARY KEY (date, fingerprint, incident_id) |
| 98 | ); |
| 99 | CREATE INDEX IF NOT EXISTS report_incidents_fingerprint_date ON report_incidents (fingerprint, date); |
| 100 | |
| 101 | -- Firebase Spark delivery is coordinated through D1 so an unavailable or |
| 102 | -- quota-limited Realtime Database never loses a sanitized report. The payload |
| 103 | -- is deleted immediately after Firebase accepts the group/sample projection. |
| 104 | CREATE TABLE IF NOT EXISTS firebase_crash_outbox ( |
| 105 | event_id TEXT PRIMARY KEY, |
| 106 | fingerprint TEXT NOT NULL, |
| 107 | payload TEXT NOT NULL, |
| 108 | state TEXT NOT NULL DEFAULT 'queued' CHECK (state IN ('queued', 'processing', 'projected')), |
| 109 | attempts INTEGER NOT NULL DEFAULT 0, |
| 110 | next_attempt_at TEXT NOT NULL, |
| 111 | created_at TEXT NOT NULL, |
| 112 | updated_at TEXT NOT NULL |
| 113 | ); |
| 114 | |
| 115 | CREATE INDEX IF NOT EXISTS firebase_crash_outbox_retry |
| 116 | ON firebase_crash_outbox (state, next_attempt_at, created_at); |
| 117 | CREATE INDEX IF NOT EXISTS firebase_crash_outbox_fingerprint |
| 118 | ON firebase_crash_outbox (fingerprint); |
| 119 | |
| 120 | CREATE TABLE IF NOT EXISTS firebase_crash_receipts ( |
| 121 | event_id TEXT PRIMARY KEY, |
| 122 | projected_at TEXT NOT NULL, |
| 123 | group_count INTEGER NOT NULL, |
| 124 | latest_slot INTEGER NOT NULL, |
| 125 | first_sample INTEGER NOT NULL DEFAULT 0 |
| 126 | ); |
| 127 | |
| 128 | CREATE INDEX IF NOT EXISTS firebase_crash_receipts_projected |
| 129 | ON firebase_crash_receipts (projected_at); |
| 130 | |
| 131 | -- Serializes each group's D1 projection, Firebase ring update, metadata sync, |
| 132 | -- and administrative deletion across Worker isolates. Expired owners are |
| 133 | -- recoverable, while owner-checked release prevents an old request from |
| 134 | -- deleting a newer lease. |
| 135 | CREATE TABLE IF NOT EXISTS firebase_crash_group_leases ( |
| 136 | fingerprint TEXT PRIMARY KEY, |
| 137 | owner TEXT NOT NULL, |
| 138 | expires_at TEXT NOT NULL |
| 139 | ); |
| 140 | |
| 141 | -- Durable Firebase lifecycle, capacity reservation, and fencing state. The |
| 142 | -- older lease table above remains for rolling-deployment compatibility only. |
| 143 | CREATE TABLE IF NOT EXISTS firebase_crash_group_state ( |
| 144 | fingerprint TEXT PRIMARY KEY, |
| 145 | sample_state TEXT NOT NULL DEFAULT 'active' |
| 146 | CHECK (sample_state IN ('active', 'compacted', 'archiving', 'archived')), |
| 147 | sample_epoch INTEGER NOT NULL DEFAULT 1 CHECK (sample_epoch >= 1), |
| 148 | epoch_first_event_id TEXT NOT NULL DEFAULT '', |
| 149 | reserved_bytes INTEGER NOT NULL DEFAULT 655360 CHECK (reserved_bytes >= 0), |
| 150 | last_seen TEXT NOT NULL, |
| 151 | compacted_at TEXT NOT NULL DEFAULT '', |
| 152 | archived_at TEXT NOT NULL DEFAULT '', |
| 153 | archive_reason TEXT NOT NULL DEFAULT '' |
| 154 | CHECK (archive_reason IN ('', 'retention', 'admin')), |
| 155 | lease_owner TEXT NOT NULL DEFAULT '', |
| 156 | lease_generation INTEGER NOT NULL DEFAULT 0 CHECK (lease_generation >= 0), |
| 157 | lease_expires_at TEXT NOT NULL DEFAULT '' |
| 158 | ); |
| 159 | |
| 160 | CREATE INDEX IF NOT EXISTS firebase_crash_group_state_lifecycle |
| 161 | ON firebase_crash_group_state (sample_state, last_seen); |
| 162 | |
| 163 | CREATE INDEX IF NOT EXISTS firebase_crash_group_state_lease |
| 164 | ON firebase_crash_group_state (lease_expires_at); |
| 165 | |
| 166 | CREATE TABLE IF NOT EXISTS pings ( |
| 167 | date TEXT NOT NULL, |
| 168 | install_id TEXT NOT NULL, |
| 169 | version TEXT NOT NULL, |
| 170 | os TEXT NOT NULL, |
| 171 | arch TEXT NOT NULL, |
| 172 | os_version TEXT NOT NULL DEFAULT '', |
| 173 | os_build INTEGER NOT NULL DEFAULT 0, |
| 174 | os_revision INTEGER NOT NULL DEFAULT 0, |
| 175 | channel TEXT NOT NULL DEFAULT '', |
| 176 | distro_id TEXT NOT NULL DEFAULT '', |
| 177 | distro_version TEXT NOT NULL DEFAULT '', |
| 178 | kernel_version TEXT NOT NULL DEFAULT '', |
| 179 | session_type TEXT NOT NULL DEFAULT '', |
| 180 | runtime_engine TEXT NOT NULL DEFAULT '', |
| 181 | runtime_version TEXT NOT NULL DEFAULT '', |
| 182 | gpu_mode TEXT NOT NULL DEFAULT '', |
| 183 | opens INTEGER NOT NULL DEFAULT 1, |
| 184 | PRIMARY KEY (date, install_id) |
| 185 | ); |
| 186 | |
| 187 | -- CLI telemetry stays in additive tables so either the schema migration or the |
| 188 | -- Worker deployment can happen first without changing the Desktop contract. |
| 189 | CREATE TABLE IF NOT EXISTS cli_pings ( |
| 190 | date TEXT NOT NULL, |
| 191 | install_id TEXT NOT NULL, |
| 192 | version TEXT NOT NULL, |
| 193 | os TEXT NOT NULL, |
| 194 | arch TEXT NOT NULL, |
| 195 | os_version TEXT NOT NULL DEFAULT '', |
| 196 | os_build INTEGER NOT NULL DEFAULT 0, |
| 197 | os_revision INTEGER NOT NULL DEFAULT 0, |
| 198 | channel TEXT NOT NULL DEFAULT '', |
| 199 | distro_id TEXT NOT NULL DEFAULT '', |
| 200 | distro_version TEXT NOT NULL DEFAULT '', |
| 201 | kernel_version TEXT NOT NULL DEFAULT '', |
| 202 | session_type TEXT NOT NULL DEFAULT '', |
| 203 | runtime_engine TEXT NOT NULL DEFAULT '', |
| 204 | runtime_version TEXT NOT NULL DEFAULT '', |
| 205 | gpu_mode TEXT NOT NULL DEFAULT '', |
| 206 | opens INTEGER NOT NULL DEFAULT 1, |
| 207 | PRIMARY KEY (date, install_id) |
| 208 | ); |
| 209 | |
| 210 | -- Single-row checkpoint used by the scheduled ingest sentinel to detect when |
| 211 | -- launch totals stop advancing between runs. The worker also creates this |
| 212 | -- table at runtime so existing databases need no manual migration. |
| 213 | CREATE TABLE IF NOT EXISTS ingest_sentinel_state ( |
| 214 | id INTEGER PRIMARY KEY CHECK (id = 1), |
| 215 | day TEXT NOT NULL, |
| 216 | ping_count INTEGER NOT NULL, |
| 217 | open_count INTEGER NOT NULL, |
| 218 | checked_at TEXT NOT NULL |
| 219 | ); |
| 220 | |
| 221 | -- Opt-in aggregate Desktop metrics: anonymous per-day (signal, bucket) |
| 222 | -- counters, no content. Generic shape so a new signal is just new rows. |
| 223 | CREATE TABLE IF NOT EXISTS metrics ( |
| 224 | date TEXT NOT NULL, |
| 225 | version TEXT NOT NULL, |
| 226 | os TEXT NOT NULL, |
| 227 | signal TEXT NOT NULL, |
| 228 | bucket TEXT NOT NULL, |
| 229 | count INTEGER NOT NULL DEFAULT 0, |
| 230 | PRIMARY KEY (date, version, os, signal, bucket) |
| 231 | ); |
| 232 | |
| 233 | CREATE TABLE IF NOT EXISTS cli_metrics ( |
| 234 | date TEXT NOT NULL, |
| 235 | version TEXT NOT NULL, |
| 236 | os TEXT NOT NULL, |
| 237 | signal TEXT NOT NULL, |
| 238 | bucket TEXT NOT NULL, |
| 239 | count INTEGER NOT NULL DEFAULT 0, |
| 240 | PRIMARY KEY (date, version, os, signal, bucket) |
| 241 | ); |
| 242 | |
| 243 | CREATE TABLE IF NOT EXISTS report_daily ( |
| 244 | date TEXT NOT NULL, |
| 245 | fingerprint TEXT NOT NULL, |
| 246 | events INTEGER NOT NULL DEFAULT 0, |
| 247 | identified_events INTEGER NOT NULL DEFAULT 0, |
| 248 | PRIMARY KEY (date, fingerprint) |
| 249 | ); |
| 250 | CREATE INDEX IF NOT EXISTS report_daily_fingerprint_date |
| 251 | ON report_daily (fingerprint, date); |
| 252 | |
| 253 | CREATE TABLE IF NOT EXISTS report_installations ( |
| 254 | date TEXT NOT NULL, |
| 255 | fingerprint TEXT NOT NULL, |
| 256 | install_id TEXT NOT NULL, |
| 257 | version TEXT NOT NULL, |
| 258 | os TEXT NOT NULL, |
| 259 | arch TEXT NOT NULL, |
| 260 | os_build INTEGER NOT NULL DEFAULT 0, |
| 261 | os_revision INTEGER NOT NULL DEFAULT 0, |
| 262 | distro_id TEXT NOT NULL DEFAULT '', |
| 263 | distro_version TEXT NOT NULL DEFAULT '', |
| 264 | kernel_version TEXT NOT NULL DEFAULT '', |
| 265 | session_type TEXT NOT NULL DEFAULT '', |
| 266 | channel TEXT NOT NULL DEFAULT '', |
| 267 | runtime_engine TEXT NOT NULL DEFAULT '', |
| 268 | runtime_version TEXT NOT NULL DEFAULT '', |
| 269 | failure_kind TEXT NOT NULL DEFAULT '', |
| 270 | failure_reason TEXT NOT NULL DEFAULT '', |
| 271 | exit_code INTEGER, |
| 272 | recovery TEXT NOT NULL DEFAULT '', |
| 273 | gpu_mode TEXT NOT NULL DEFAULT 'unknown', |
| 274 | events INTEGER NOT NULL DEFAULT 0, |
| 275 | PRIMARY KEY (date, fingerprint, install_id) |
| 276 | ); |
| 277 | |
| 278 | CREATE INDEX IF NOT EXISTS report_installations_fingerprint_date |
| 279 | ON report_installations (fingerprint, date); |
| 280 | |
| 281 | -- Event facts preserve every observed diagnostic environment. Empty install_id |
| 282 | -- is the sentinel for an event that did not carry an anonymous installation ID; |
| 283 | -- it contributes to event totals and dimension filters but never device counts. |
| 284 | CREATE TABLE IF NOT EXISTS report_event_dimensions ( |
| 285 | date TEXT NOT NULL, |
| 286 | fingerprint TEXT NOT NULL, |
| 287 | install_id TEXT NOT NULL, |
| 288 | version TEXT NOT NULL, |
| 289 | os TEXT NOT NULL, |
| 290 | arch TEXT NOT NULL, |
| 291 | os_build INTEGER NOT NULL DEFAULT 0, |
| 292 | os_revision INTEGER NOT NULL DEFAULT 0, |
| 293 | distro_id TEXT NOT NULL DEFAULT '', |
| 294 | distro_version TEXT NOT NULL DEFAULT '', |
| 295 | kernel_version TEXT NOT NULL DEFAULT '', |
| 296 | session_type TEXT NOT NULL DEFAULT '', |
| 297 | channel TEXT NOT NULL DEFAULT '', |
| 298 | runtime_engine TEXT NOT NULL DEFAULT '', |
| 299 | runtime_version TEXT NOT NULL DEFAULT '', |
| 300 | failure_kind TEXT NOT NULL DEFAULT '', |
| 301 | failure_reason TEXT NOT NULL DEFAULT '', |
| 302 | exit_code TEXT NOT NULL DEFAULT 'unknown', |
| 303 | recovery TEXT NOT NULL DEFAULT '', |
| 304 | gpu_mode TEXT NOT NULL DEFAULT 'unknown', |
| 305 | events INTEGER NOT NULL DEFAULT 0, |
| 306 | PRIMARY KEY ( |
| 307 | date, fingerprint, install_id, version, os, arch, os_build, os_revision, |
| 308 | distro_id, distro_version, kernel_version, session_type, channel, |
| 309 | runtime_engine, runtime_version, failure_kind, failure_reason, exit_code, recovery, gpu_mode |
| 310 | ) |
| 311 | ); |
| 312 | |
| 313 | CREATE INDEX IF NOT EXISTS report_event_dimensions_fingerprint_date |
| 314 | ON report_event_dimensions (fingerprint, date); |
| 315 | |
| 316 | CREATE INDEX IF NOT EXISTS pings_diagnostics_window |
| 317 | ON pings (date, os, os_build, distro_id, session_type, channel); |
| 318 | |
| 319 | CREATE TABLE IF NOT EXISTS diagnostics_meta ( |
| 320 | key TEXT PRIMARY KEY, |
| 321 | value TEXT NOT NULL |
| 322 | ); |
| 323 | |
| 324 | INSERT OR IGNORE INTO diagnostics_meta (key, value) |
| 325 | VALUES ('installation_linked_since', date('now')); |
| 326 | INSERT OR IGNORE INTO diagnostics_meta (key, value) |
| 327 | VALUES ('structured_attribution_since', date('now')); |
| 328 | |
| 329 | -- Legacy local auth — superseded by id.reasonix.io identity + the `access` |
| 330 | -- table below. Kept during the transition; migrate-access.sql copies roles over. |
| 331 | CREATE TABLE IF NOT EXISTS users ( |
| 332 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 333 | email TEXT NOT NULL UNIQUE, |
| 334 | password_hash TEXT NOT NULL, |
| 335 | role TEXT NOT NULL DEFAULT 'pending', |
| 336 | created_at TEXT NOT NULL, |
| 337 | approved_at TEXT, |
| 338 | approved_by INTEGER |
| 339 | ); |
| 340 | |
| 341 | CREATE TABLE IF NOT EXISTS sessions ( |
| 342 | token TEXT PRIMARY KEY, |
| 343 | user_id INTEGER NOT NULL, |
| 344 | created_at TEXT NOT NULL, |
| 345 | expires_at TEXT NOT NULL |
| 346 | ); |
| 347 | |
| 348 | CREATE INDEX IF NOT EXISTS sessions_user ON sessions (user_id); |
| 349 | |
| 350 | -- Dashboard authorization keyed by the shared account email. Identity (login, |
| 351 | -- password, verification) lives in id.reasonix.io; this only maps email → role. |
| 352 | CREATE TABLE IF NOT EXISTS access ( |
| 353 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 354 | email TEXT NOT NULL UNIQUE, |
| 355 | role TEXT NOT NULL DEFAULT 'pending', |
| 356 | created_at TEXT NOT NULL, |
| 357 | approved_at TEXT, |
| 358 | approved_by TEXT |
| 359 | ); |
| 360 | |
| 361 | CREATE TABLE IF NOT EXISTS audit_log ( |
| 362 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 363 | at TEXT NOT NULL, |
| 364 | actor_id INTEGER, |
| 365 | actor_email TEXT NOT NULL, |
| 366 | action TEXT NOT NULL, |
| 367 | target TEXT NOT NULL DEFAULT '', |
| 368 | detail TEXT NOT NULL DEFAULT '' |
| 369 | ); |
| 370 |