| 1 | -- The status page (lib/status.ts): incidents site administrators open at /status, each with the |
| 2 | -- components it affects and its updates. An incident is investigating, identified, monitoring or |
| 3 | -- resolved; while it isn't resolved, its impact sets the status of each component it names. |
| 4 | CREATE TABLE IF NOT EXISTS status_incidents ( |
| 5 | id TEXT PRIMARY KEY, |
| 6 | title TEXT NOT NULL, |
| 7 | impact TEXT NOT NULL CHECK (impact IN ('minor', 'major', 'critical', 'maintenance')), |
| 8 | components TEXT NOT NULL, -- JSON array of component ids, e.g. ["web","git"] |
| 9 | state TEXT NOT NULL CHECK (state IN ('investigating', 'identified', 'monitoring', 'resolved')), |
| 10 | created_by TEXT, -- the administrator's login |
| 11 | created_at TEXT NOT NULL, |
| 12 | updated_at TEXT NOT NULL, |
| 13 | resolved_at TEXT -- set while the state is resolved |
| 14 | ); |
| 15 | CREATE INDEX IF NOT EXISTS status_incidents_at ON status_incidents(created_at); |
| 16 | CREATE INDEX IF NOT EXISTS status_incidents_state ON status_incidents(state, resolved_at); |
| 17 | |
| 18 | -- What was said about an incident, newest last; the first is written with the incident. |
| 19 | CREATE TABLE IF NOT EXISTS status_incident_updates ( |
| 20 | id TEXT PRIMARY KEY, |
| 21 | incident_id TEXT NOT NULL, |
| 22 | state TEXT NOT NULL CHECK (state IN ('investigating', 'identified', 'monitoring', 'resolved')), |
| 23 | body TEXT NOT NULL, |
| 24 | author_login TEXT, |
| 25 | created_at TEXT NOT NULL |
| 26 | ); |
| 27 | CREATE INDEX IF NOT EXISTS status_incident_updates_incident ON status_incident_updates(incident_id, created_at); |