| 1 | -- Phase 2: Spark capacity accounting, lifecycle state, and generation leases. |
| 2 | -- The phase-1 lease table remains in place for an old Worker during rollout. |
| 3 | CREATE TABLE IF NOT EXISTS firebase_crash_group_state ( |
| 4 | fingerprint TEXT PRIMARY KEY, |
| 5 | sample_state TEXT NOT NULL DEFAULT 'active' |
| 6 | CHECK (sample_state IN ('active', 'compacted', 'archiving', 'archived')), |
| 7 | sample_epoch INTEGER NOT NULL DEFAULT 1 CHECK (sample_epoch >= 1), |
| 8 | epoch_first_event_id TEXT NOT NULL DEFAULT '', |
| 9 | reserved_bytes INTEGER NOT NULL DEFAULT 655360 CHECK (reserved_bytes >= 0), |
| 10 | last_seen TEXT NOT NULL, |
| 11 | compacted_at TEXT NOT NULL DEFAULT '', |
| 12 | archived_at TEXT NOT NULL DEFAULT '', |
| 13 | archive_reason TEXT NOT NULL DEFAULT '' |
| 14 | CHECK (archive_reason IN ('', 'retention', 'admin')), |
| 15 | lease_owner TEXT NOT NULL DEFAULT '', |
| 16 | lease_generation INTEGER NOT NULL DEFAULT 0 CHECK (lease_generation >= 0), |
| 17 | lease_expires_at TEXT NOT NULL DEFAULT '' |
| 18 | ); |
| 19 | |
| 20 | CREATE INDEX IF NOT EXISTS firebase_crash_group_state_lifecycle |
| 21 | ON firebase_crash_group_state (sample_state, last_seen); |
| 22 | |
| 23 | CREATE INDEX IF NOT EXISTS firebase_crash_group_state_lease |
| 24 | ON firebase_crash_group_state (lease_expires_at); |
| 25 | |
| 26 | -- Seed existing D1 groups with the same 30/60-day classification used by the |
| 27 | -- migration command. Its preflight enforces the 700 MiB budget before this |
| 28 | -- statement runs. |
| 29 | INSERT OR IGNORE INTO firebase_crash_group_state ( |
| 30 | fingerprint, sample_state, sample_epoch, reserved_bytes, last_seen, |
| 31 | compacted_at, archived_at, archive_reason |
| 32 | ) |
| 33 | SELECT fingerprint, |
| 34 | CASE |
| 35 | WHEN status IN ('resolved', 'ignored') AND datetime(last_seen) <= datetime('now', '-60 days') |
| 36 | THEN 'archived' |
| 37 | WHEN status IN ('resolved', 'ignored') AND datetime(last_seen) <= datetime('now', '-30 days') |
| 38 | THEN 'compacted' |
| 39 | ELSE 'active' |
| 40 | END, |
| 41 | 1, |
| 42 | CASE |
| 43 | WHEN status IN ('resolved', 'ignored') AND datetime(last_seen) <= datetime('now', '-60 days') THEN 0 |
| 44 | WHEN status IN ('resolved', 'ignored') AND datetime(last_seen) <= datetime('now', '-30 days') THEN 131072 |
| 45 | ELSE 655360 |
| 46 | END, |
| 47 | last_seen, |
| 48 | CASE WHEN status IN ('resolved', 'ignored') AND datetime(last_seen) <= datetime('now', '-30 days') |
| 49 | AND datetime(last_seen) > datetime('now', '-60 days') THEN datetime('now') ELSE '' END, |
| 50 | CASE WHEN status IN ('resolved', 'ignored') AND datetime(last_seen) <= datetime('now', '-60 days') |
| 51 | THEN datetime('now') ELSE '' END, |
| 52 | CASE WHEN status IN ('resolved', 'ignored') AND datetime(last_seen) <= datetime('now', '-60 days') |
| 53 | THEN 'retention' ELSE '' END |
| 54 | FROM groups; |
| 55 |