| 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 | last_sample_at TEXT NOT NULL DEFAULT '' |
| 26 | ); |
| 27 | |
| 28 | CREATE TABLE IF NOT EXISTS reports ( |
| 29 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 30 | fingerprint TEXT NOT NULL, |
| 31 | kind TEXT NOT NULL, |
| 32 | version TEXT NOT NULL, |
| 33 | os TEXT NOT NULL, |
| 34 | arch TEXT NOT NULL, |
| 35 | message TEXT NOT NULL, |
| 36 | device TEXT NOT NULL DEFAULT '', |
| 37 | created_at TEXT NOT NULL, |
| 38 | source TEXT NOT NULL DEFAULT 'legacy', |
| 39 | label TEXT NOT NULL DEFAULT '', |
| 40 | error_type TEXT NOT NULL DEFAULT '', |
| 41 | error_message TEXT NOT NULL DEFAULT '', |
| 42 | top_frame TEXT NOT NULL DEFAULT '', |
| 43 | build_commit TEXT NOT NULL DEFAULT '', |
| 44 | channel TEXT NOT NULL DEFAULT '', |
| 45 | language TEXT NOT NULL DEFAULT '', |
| 46 | view TEXT NOT NULL DEFAULT '', |
| 47 | breadcrumbs TEXT NOT NULL DEFAULT '', |
| 48 | component_stack TEXT NOT NULL DEFAULT '', |
| 49 | stack TEXT NOT NULL DEFAULT '', |
| 50 | occurred_at TEXT NOT NULL DEFAULT '' |
| 51 | ); |
| 52 | |
| 53 | CREATE INDEX IF NOT EXISTS reports_fingerprint ON reports (fingerprint); |
| 54 | |
| 55 | CREATE TABLE IF NOT EXISTS pings ( |
| 56 | date TEXT NOT NULL, |
| 57 | install_id TEXT NOT NULL, |
| 58 | version TEXT NOT NULL, |
| 59 | os TEXT NOT NULL, |
| 60 | arch TEXT NOT NULL, |
| 61 | os_version TEXT NOT NULL DEFAULT '', |
| 62 | opens INTEGER NOT NULL DEFAULT 1, |
| 63 | PRIMARY KEY (date, install_id) |
| 64 | ); |
| 65 | |
| 66 | -- CLI telemetry stays in additive tables so either the schema migration or the |
| 67 | -- Worker deployment can happen first without changing the Desktop contract. |
| 68 | CREATE TABLE IF NOT EXISTS cli_pings ( |
| 69 | date 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_version TEXT NOT NULL DEFAULT '', |
| 75 | opens INTEGER NOT NULL DEFAULT 1, |
| 76 | PRIMARY KEY (date, install_id) |
| 77 | ); |
| 78 | |
| 79 | -- Single-row checkpoint used by the scheduled ingest sentinel to detect when |
| 80 | -- launch totals stop advancing between runs. The worker also creates this |
| 81 | -- table at runtime so existing databases need no manual migration. |
| 82 | CREATE TABLE IF NOT EXISTS ingest_sentinel_state ( |
| 83 | id INTEGER PRIMARY KEY CHECK (id = 1), |
| 84 | day TEXT NOT NULL, |
| 85 | ping_count INTEGER NOT NULL, |
| 86 | open_count INTEGER NOT NULL, |
| 87 | checked_at TEXT NOT NULL |
| 88 | ); |
| 89 | |
| 90 | -- Opt-in aggregate Desktop metrics: anonymous per-day (signal, bucket) |
| 91 | -- counters, no content. Generic shape so a new signal is just new rows. |
| 92 | CREATE TABLE IF NOT EXISTS metrics ( |
| 93 | date TEXT NOT NULL, |
| 94 | version TEXT NOT NULL, |
| 95 | os TEXT NOT NULL, |
| 96 | signal TEXT NOT NULL, |
| 97 | bucket TEXT NOT NULL, |
| 98 | count INTEGER NOT NULL DEFAULT 0, |
| 99 | PRIMARY KEY (date, version, os, signal, bucket) |
| 100 | ); |
| 101 | |
| 102 | CREATE TABLE IF NOT EXISTS cli_metrics ( |
| 103 | date TEXT NOT NULL, |
| 104 | version TEXT NOT NULL, |
| 105 | os TEXT NOT NULL, |
| 106 | signal TEXT NOT NULL, |
| 107 | bucket TEXT NOT NULL, |
| 108 | count INTEGER NOT NULL DEFAULT 0, |
| 109 | PRIMARY KEY (date, version, os, signal, bucket) |
| 110 | ); |
| 111 | |
| 112 | -- Deduplicated Desktop DAU for opt-in metric buckets. install_id is the same |
| 113 | -- random anonymous install id used by launch pings; it is not an account id. |
| 114 | CREATE TABLE IF NOT EXISTS metric_users ( |
| 115 | date TEXT NOT NULL, |
| 116 | signal TEXT NOT NULL, |
| 117 | bucket TEXT NOT NULL, |
| 118 | install_id TEXT NOT NULL, |
| 119 | version TEXT NOT NULL, |
| 120 | os TEXT NOT NULL, |
| 121 | PRIMARY KEY (date, signal, bucket, install_id) |
| 122 | ); |
| 123 | |
| 124 | CREATE TABLE IF NOT EXISTS cli_metric_users ( |
| 125 | date TEXT NOT NULL, |
| 126 | signal TEXT NOT NULL, |
| 127 | bucket TEXT NOT NULL, |
| 128 | install_id TEXT NOT NULL, |
| 129 | version TEXT NOT NULL, |
| 130 | os TEXT NOT NULL, |
| 131 | PRIMARY KEY (date, signal, bucket, install_id) |
| 132 | ); |
| 133 | |
| 134 | -- Cron-built answer to the preferences module's 30-day deduplication, which |
| 135 | -- spans ~28M rows of metric_users: too many to count per request, and not |
| 136 | -- summable from daily totals. refreshMetricUserRollup fills it one signal at a |
| 137 | -- time. The worker creates both tables at runtime, so existing databases need |
| 138 | -- no manual migration. |
| 139 | CREATE TABLE IF NOT EXISTS metric_user_rollup ( |
| 140 | window_days INTEGER NOT NULL, |
| 141 | signal TEXT NOT NULL, |
| 142 | bucket TEXT NOT NULL, |
| 143 | total INTEGER NOT NULL, |
| 144 | computed_at TEXT NOT NULL, |
| 145 | PRIMARY KEY (window_days, signal, bucket) |
| 146 | ); |
| 147 | |
| 148 | CREATE TABLE IF NOT EXISTS metric_user_rollup_state ( |
| 149 | id INTEGER PRIMARY KEY CHECK (id = 1), |
| 150 | next_signal INTEGER NOT NULL, |
| 151 | updated_at TEXT NOT NULL |
| 152 | ); |
| 153 | |
| 154 | -- Legacy local auth — superseded by id.reasonix.io identity + the `access` |
| 155 | -- table below. Kept during the transition; migrate-access.sql copies roles over. |
| 156 | CREATE TABLE IF NOT EXISTS users ( |
| 157 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 158 | email TEXT NOT NULL UNIQUE, |
| 159 | password_hash TEXT NOT NULL, |
| 160 | role TEXT NOT NULL DEFAULT 'pending', |
| 161 | created_at TEXT NOT NULL, |
| 162 | approved_at TEXT, |
| 163 | approved_by INTEGER |
| 164 | ); |
| 165 | |
| 166 | CREATE TABLE IF NOT EXISTS sessions ( |
| 167 | token TEXT PRIMARY KEY, |
| 168 | user_id INTEGER NOT NULL, |
| 169 | created_at TEXT NOT NULL, |
| 170 | expires_at TEXT NOT NULL |
| 171 | ); |
| 172 | |
| 173 | CREATE INDEX IF NOT EXISTS sessions_user ON sessions (user_id); |
| 174 | |
| 175 | -- Dashboard authorization keyed by the shared account email. Identity (login, |
| 176 | -- password, verification) lives in id.reasonix.io; this only maps email → role. |
| 177 | CREATE TABLE IF NOT EXISTS access ( |
| 178 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 179 | email TEXT NOT NULL UNIQUE, |
| 180 | role TEXT NOT NULL DEFAULT 'pending', |
| 181 | created_at TEXT NOT NULL, |
| 182 | approved_at TEXT, |
| 183 | approved_by TEXT |
| 184 | ); |
| 185 | |
| 186 | CREATE TABLE IF NOT EXISTS audit_log ( |
| 187 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 188 | at TEXT NOT NULL, |
| 189 | actor_id INTEGER, |
| 190 | actor_email TEXT NOT NULL, |
| 191 | action TEXT NOT NULL, |
| 192 | target TEXT NOT NULL DEFAULT '', |
| 193 | detail TEXT NOT NULL DEFAULT '' |
| 194 | ); |
| 195 |