Sign in

flagon-io/g1t

public

Where people and agents ship software together. The open-source git platform for the whole job: issues, agents, checks and deploys to the edge.

gitagentscloudflarerustforgemcp

g1t/apps/status/migrations/0002_incident_management.sql

163 lines6,188 bytesCodeBlame
1-- Incident management: severity, a lifecycle with timestamps, each part's
2-- impact, roles, an internal timeline beside the public updates,
3-- follow-ups, postmortems, scheduled maintenance, email subscribers,
4-- detection streaks, and an audit log of every staff change.
5--
6-- Idempotent: every table and index is made only if missing, and the
7-- incidents posted before this (the `incidents` and `incident_updates`
8-- tables of 0001) are copied in with INSERT OR IGNORE, so running it
9-- again changes nothing. The 0001 tables are left in place, unused, and
10-- can be dropped by a later migration.
11
12CREATE TABLE IF NOT EXISTS incident (
13 id TEXT PRIMARY KEY,
14 title TEXT NOT NULL,
15 severity TEXT NOT NULL CHECK (severity IN ('sev1', 'sev2', 'sev3', 'sev4')),
16 status TEXT NOT NULL CHECK (status IN ('investigating', 'identified', 'monitoring', 'resolved')),
17 -- draft: not on the status page yet (detected, or saved); dismissed: a
18 -- draft closed as a false alarm, never shown.
19 visibility TEXT NOT NULL CHECK (visibility IN ('draft', 'public', 'dismissed')),
20 source TEXT NOT NULL CHECK (source IN ('declared', 'detected')),
21 -- When the impact began; declared_at is when staff (or the checks) said so.
22 started_at TEXT NOT NULL,
23 declared_at TEXT NOT NULL,
24 acknowledged_at TEXT,
25 mitigated_at TEXT,
26 resolved_at TEXT,
27 published_at TEXT,
28 commander TEXT,
29 communications TEXT,
30 created_by TEXT NOT NULL
31);
32CREATE INDEX IF NOT EXISTS incident_started ON incident (started_at);
33CREATE INDEX IF NOT EXISTS incident_open ON incident (resolved_at, visibility);
34
35-- What an incident does to each part, while it is open.
36CREATE TABLE IF NOT EXISTS incident_component (
37 incident_id TEXT NOT NULL,
38 component TEXT NOT NULL,
39 impact TEXT NOT NULL CHECK (impact IN ('operational', 'degraded', 'partial_outage', 'major_outage')),
40 PRIMARY KEY (incident_id, component)
41);
42
43-- Everything that happened, in order: public updates (public = 1, what
44-- the status page shows) and, for staff only, notes and every change.
45CREATE TABLE IF NOT EXISTS incident_timeline (
46 id TEXT PRIMARY KEY,
47 incident_id TEXT NOT NULL,
48 at TEXT NOT NULL,
49 by TEXT NOT NULL,
50 kind TEXT NOT NULL,
51 public INTEGER NOT NULL DEFAULT 0,
52 status TEXT,
53 text TEXT NOT NULL,
54 -- Subscribers emailed about it; null when none were.
55 notified INTEGER
56);
57CREATE INDEX IF NOT EXISTS incident_timeline_incident ON incident_timeline (incident_id, at);
58CREATE INDEX IF NOT EXISTS incident_timeline_public ON incident_timeline (public, at);
59
60CREATE TABLE IF NOT EXISTS incident_followup (
61 id TEXT PRIMARY KEY,
62 incident_id TEXT NOT NULL,
63 title TEXT NOT NULL,
64 owner TEXT,
65 done_at TEXT,
66 done_by TEXT,
67 created_at TEXT NOT NULL,
68 created_by TEXT NOT NULL
69);
70CREATE INDEX IF NOT EXISTS incident_followup_incident ON incident_followup (incident_id, created_at);
71
72CREATE TABLE IF NOT EXISTS postmortem (
73 incident_id TEXT PRIMARY KEY,
74 summary TEXT NOT NULL DEFAULT '',
75 impact TEXT NOT NULL DEFAULT '',
76 timeline TEXT NOT NULL DEFAULT '',
77 root_cause TEXT NOT NULL DEFAULT '',
78 went_well TEXT NOT NULL DEFAULT '',
79 went_badly TEXT NOT NULL DEFAULT '',
80 action_items TEXT NOT NULL DEFAULT '',
81 updated_at TEXT NOT NULL,
82 updated_by TEXT NOT NULL,
83 published_at TEXT,
84 published_by TEXT
85);
86
87CREATE TABLE IF NOT EXISTS maintenance (
88 id TEXT PRIMARY KEY,
89 title TEXT NOT NULL,
90 message TEXT NOT NULL,
91 -- A JSON array of component keys.
92 components TEXT NOT NULL,
93 starts_at TEXT NOT NULL,
94 ends_at TEXT NOT NULL,
95 state TEXT NOT NULL CHECK (state IN ('scheduled', 'in_progress', 'completed', 'cancelled')),
96 -- Whether subscribers hear about it when it starts and ends.
97 notify INTEGER NOT NULL DEFAULT 0,
98 created_at TEXT NOT NULL,
99 created_by TEXT NOT NULL
100);
101CREATE INDEX IF NOT EXISTS maintenance_window ON maintenance (state, starts_at);
102
103CREATE TABLE IF NOT EXISTS maintenance_update (
104 id TEXT PRIMARY KEY,
105 maintenance_id TEXT NOT NULL,
106 at TEXT NOT NULL,
107 by TEXT NOT NULL,
108 text TEXT NOT NULL,
109 notified INTEGER
110);
111CREATE INDEX IF NOT EXISTS maintenance_update_maintenance ON maintenance_update (maintenance_id, at);
112
113-- Email subscribers. Only hashes of tokens are kept: confirm_hash is the
114-- SHA-256 of the confirmation link's token; unsubscribe links are signed
115-- with STATUS_SECRET and not stored. `components` is a JSON array of keys,
116-- or null for everything; `pending_components` is what the unconfirmed
117-- request asked for, applied on confirming.
118CREATE TABLE IF NOT EXISTS subscriber (
119 id TEXT PRIMARY KEY,
120 email TEXT NOT NULL UNIQUE,
121 components TEXT,
122 pending_components TEXT,
123 confirm_hash TEXT,
124 confirm_expires_at TEXT,
125 confirm_sent_at TEXT,
126 confirmed_at TEXT,
127 created_at TEXT NOT NULL
128);
129CREATE INDEX IF NOT EXISTS subscriber_confirm ON subscriber (confirm_hash);
130
131-- A part's current run of failed or slow checks, for detection.
132CREATE TABLE IF NOT EXISTS streak (
133 component TEXT PRIMARY KEY,
134 state TEXT NOT NULL,
135 count INTEGER NOT NULL,
136 since TEXT NOT NULL,
137 alerted INTEGER NOT NULL DEFAULT 0
138);
139
140-- Every staff change, with who made it; sudo's audit log reads it.
141CREATE TABLE IF NOT EXISTS audit (
142 id TEXT PRIMARY KEY,
143 at TEXT NOT NULL,
144 by TEXT NOT NULL,
145 action TEXT NOT NULL,
146 target TEXT NOT NULL,
147 detail TEXT NOT NULL
148);
149CREATE INDEX IF NOT EXISTS audit_at ON audit (at);
150
151-- The incidents posted before this migration.
152INSERT OR IGNORE INTO incident
153 (id, title, severity, status, visibility, source, started_at, declared_at, acknowledged_at, mitigated_at, resolved_at, published_at, created_by)
154SELECT id, title, CASE impact WHEN 'down' THEN 'sev2' ELSE 'sev3' END, status, 'public', 'declared',
155 started_at, started_at, started_at, resolved_at, resolved_at, started_at, created_by
156FROM incidents;
157
158INSERT OR IGNORE INTO incident_component (incident_id, component, impact)
159SELECT i.id, j.value, CASE i.impact WHEN 'down' THEN 'major_outage' ELSE 'partial_outage' END
160FROM incidents i, json_each(i.components) j;
161
162INSERT OR IGNORE INTO incident_timeline (id, incident_id, at, by, kind, public, status, text)
163SELECT id, incident_id, at, by, 'update', 1, status, message FROM incident_updates;