Skip to content
Sign in

flagon-io/g1t

public
0
951 linesCodeBlameRaw
1/**
2 * The status page's D1 database: each part's last check and daily tally
3 * (migrations/0001), and incidents, maintenance, postmortems, subscribers,
4 * detection and the audit log (migrations/0002).
5 */
6import type {
7 AdminIncident,
8 AdminIncidentDetail,
9 AdminMaintenance,
10 CheckHistory,
11 CheckSample,
12 ComponentImpact,
13 FollowUp,
14 ImpactInput,
15 IncidentSeverity,
16 IncidentStatus,
17 IncidentVisibility,
18 MaintenanceState,
19 Postmortem,
20 PostmortemFields,
21 StatusAuditEntry,
22 StatusIncident,
23 StatusMaintenance,
24 TimelineEntry,
25 TimelineKind,
26} from "@g1t/contracts";
27
28import { type DeployWindow, type Streak, type WatchedDraft, upgradeStreak } from "./detect.ts";
29import { type Entry, type IncidentFacts, durations, shortId } from "./incidents.ts";
30import { type Current, type DayRow, HISTORY_DAYS, dayOf, overallImpact } from "./model.ts";
31import { postmortemDraft } from "./postmortem.ts";
32import { parseParts, wants } from "./subscribers.ts";
33
34const DAY_MS = 24 * 60 * 60 * 1000;
35const iso = (ms: number) => new Date(ms).toISOString();
36
37/** One part's check, ready to keep. */
38export type Observation = Current & {
39 component: string;
40 /** The data centre the check ran from (cf-ray), when known. */
41 colo?: string | null;
42 /** A slow answer asked again (probe.ts `confirmSlow`): the first try's time. */
43 first_ms?: number | null;
44};
45
46/** How long every single check is kept (`check_history`), for sudo's incident pages. */
47export const CHECK_HISTORY_DAYS = 7;
48
49/**
50 * Keeps one round of checks: each part's state, today's tally, every
51 * check for CHECK_HISTORY_DAYS, and the time. Parts under maintenance keep
52 * their last check but are left out of the tally: failures in a planned
53 * window do not count against uptime.
54 */
55export async function record(db: D1Database, observations: Observation[], at: Date, maintenance: Set<string> = new Set()): Promise<void> {
56 const when = at.toISOString();
57 const day = dayOf(at);
58 const oldest = dayOf(at.getTime() - HISTORY_DAYS * DAY_MS);
59 const statements: D1PreparedStatement[] = [];
60 for (const o of observations) {
61 statements.push(
62 db
63 .prepare(
64 `INSERT INTO current (component, state, detail, latency_ms, checked_at) VALUES (?1, ?2, ?3, ?4, ?5)
65 ON CONFLICT (component) DO UPDATE SET state = ?2, detail = ?3, latency_ms = ?4, checked_at = ?5`,
66 )
67 .bind(o.component, o.state, o.detail, o.latency_ms, when),
68 );
69 if (o.state === "unmonitored") continue;
70 statements.push(
71 db
72 .prepare(`INSERT OR REPLACE INTO check_history (component, at, ms, outcome, colo, first_ms) VALUES (?1, ?2, ?3, ?4, ?5, ?6)`)
73 .bind(o.component, when, o.latency_ms, o.state, o.colo ?? null, o.first_ms ?? null),
74 );
75 if (maintenance.has(o.component)) continue;
76 const up = o.state === "up" ? 1 : 0;
77 const degraded = o.state === "degraded" ? 1 : 0;
78 const down = o.state === "down" ? 1 : 0;
79 // A latency only counts when it answered: a timeout is not a speed.
80 const latency = o.state !== "down" && o.latency_ms != null ? o.latency_ms : 0;
81 const counted = o.state !== "down" && o.latency_ms != null ? 1 : 0;
82 statements.push(
83 db
84 .prepare(
85 `INSERT INTO daily (component, day, checks, up, degraded, down, latency_total, latency_count)
86 VALUES (?1, ?2, 1, ?3, ?4, ?5, ?6, ?7)
87 ON CONFLICT (component, day) DO UPDATE SET
88 checks = checks + 1, up = up + ?3, degraded = degraded + ?4, down = down + ?5,
89 latency_total = latency_total + ?6, latency_count = latency_count + ?7`,
90 )
91 .bind(o.component, day, up, degraded, down, latency, counted),
92 );
93 }
94 statements.push(
95 db.prepare(`INSERT INTO meta (key, value) VALUES ('checked_at', ?1) ON CONFLICT (key) DO UPDATE SET value = ?1`).bind(when),
96 );
97 statements.push(db.prepare(`DELETE FROM daily WHERE day < ?1`).bind(oldest));
98 statements.push(db.prepare(`DELETE FROM check_history WHERE at < ?1`).bind(iso(at.getTime() - CHECK_HISTORY_DAYS * DAY_MS)));
99 await db.batch(statements);
100}
101
102/** Every check of `components` from `from` to `to`, oldest first. */
103export async function checkHistory(db: D1Database, components: string[], from: Date, to: Date): Promise<CheckSample[]> {
104 if (!components.length) return [];
105 const list = rows<CheckSample>(
106 (await db
107 .prepare(
108 `SELECT component, at, ms, outcome, colo, first_ms FROM check_history
109 WHERE at >= ? AND at <= ? AND component IN (${inList(components)}) ORDER BY at, component`,
110 )
111 .bind(from.toISOString(), to.toISOString(), ...components)
112 .all()) as D1Result,
113 );
114 return list.map((r) => ({ ...r, ms: r.ms == null ? null : Number(r.ms), first_ms: r.first_ms == null ? null : Number(r.first_ms) }));
115}
116
117/** Before the impact began, and after it ended, the incident page shows this much more. */
118const AROUND_MS = 30 * 60_000;
119/** The most of one incident's checks the incident page shows: the last day of it. */
120const SPAN_MS = DAY_MS;
121
122/** The span of checks an incident's page shows: from before it began to after it ended (or now), at most a day, within what is kept. */
123export function historySpan(started_at: string, resolved_at: string | null, now: Date): { from: Date; to: Date } {
124 const to = Math.min(now.getTime(), resolved_at ? Date.parse(resolved_at) + AROUND_MS : now.getTime());
125 const from = Math.max(Date.parse(started_at) - AROUND_MS, to - SPAN_MS, now.getTime() - CHECK_HISTORY_DAYS * DAY_MS);
126 return { from: new Date(Math.min(from, to)), to: new Date(to) };
127}
128
129// --- Rows ------------------------------------------------------------------------
130
131type IncidentRow = {
132 id: string;
133 title: string;
134 severity: IncidentSeverity;
135 status: IncidentStatus;
136 visibility: IncidentVisibility;
137 source: "declared" | "detected";
138 started_at: string;
139 declared_at: string;
140 acknowledged_at: string | null;
141 mitigated_at: string | null;
142 resolved_at: string | null;
143 published_at: string | null;
144 commander: string | null;
145 communications: string | null;
146 created_by: string;
147};
148type ComponentRow = { incident_id: string; component: string; impact: ComponentImpact };
149type TimelineRow = {
150 id: string;
151 incident_id: string;
152 at: string;
153 by: string;
154 kind: TimelineKind;
155 public: number;
156 status: IncidentStatus | null;
157 text: string;
158 notified: number | null;
159};
160type FollowUpRow = FollowUp & { incident_id: string };
161type PostmortemRow = PostmortemFields & { incident_id: string; updated_at: string; updated_by: string; published_at: string | null };
162type MaintenanceRow = {
163 id: string;
164 title: string;
165 message: string;
166 components: string;
167 starts_at: string;
168 ends_at: string;
169 state: MaintenanceState;
170 notify: number;
171 created_at: string;
172 created_by: string;
173};
174type MaintenanceUpdateRow = { id: string; maintenance_id: string; at: string; by: string; text: string; notified: number | null };
175
176function keys(json: string): string[] {
177 try {
178 const value = JSON.parse(json);
179 return Array.isArray(value) ? value.map(String) : [];
180 } catch {
181 return [];
182 }
183}
184
185const rows = <T>(result: D1Result | undefined) => (result?.results ?? []) as T[];
186
187/** `in (?, ?, ?)` for a list, as bind arguments. */
188function inList(ids: string[]): string {
189 return ids.map(() => "?").join(", ") || "NULL";
190}
191
192export function incidentUrl(origin: string, id: string): string {
193 return `${origin}/incidents/${id}`;
194}
195
196export function maintenanceUrl(origin: string, id: string): string {
197 return `${origin}/maintenance/${id}`;
198}
199
200function components(id: string, all: ComponentRow[]): ImpactInput[] {
201 return all.filter((c) => c.incident_id === id).map((c) => ({ key: c.component, impact: c.impact }));
202}
203
204function toAdmin(row: IncidentRow, comps: ComponentRow[], timeline: TimelineRow[], followups: FollowUpRow[], pm: PostmortemRow | null): AdminIncident {
205 const mine = followups.filter((f) => f.incident_id === row.id);
206 const lastUpdate = timeline.filter((t) => t.incident_id === row.id && t.public === 1).reduce<string | null>((a, t) => (a && a > t.at ? a : t.at), null);
207 return {
208 id: row.id,
209 title: row.title,
210 severity: row.severity,
211 status: row.status,
212 visibility: row.visibility,
213 source: row.source,
214 components: components(row.id, comps),
215 started_at: row.started_at,
216 declared_at: row.declared_at,
217 acknowledged_at: row.acknowledged_at,
218 mitigated_at: row.mitigated_at,
219 resolved_at: row.resolved_at,
220 published_at: row.published_at,
221 commander: row.commander,
222 communications: row.communications,
223 created_by: row.created_by,
224 postmortem_published_at: pm?.published_at ?? null,
225 durations: durations(row),
226 followups_open: mine.filter((f) => !f.done_at).length,
227 followups_done: mine.filter((f) => f.done_at).length,
228 last_update_at: lastUpdate,
229 };
230}
231
232function toPublic(row: IncidentRow, comps: ComponentRow[], timeline: TimelineRow[], origin: string, pmPublished: string | null): StatusIncident {
233 const impacts = components(row.id, comps).filter((c) => c.impact !== "operational");
234 return {
235 id: row.id,
236 title: row.title,
237 impact: overallImpact(impacts),
238 status: row.status,
239 components: impacts.map((c) => c.key),
240 component_impacts: impacts,
241 started_at: row.started_at,
242 resolved_at: row.resolved_at,
243 url: incidentUrl(origin, row.id),
244 postmortem_published_at: pmPublished,
245 updates: timeline
246 .filter((t) => t.incident_id === row.id && t.public === 1)
247 .sort((a, b) => b.at.localeCompare(a.at))
248 .map((t) => ({ id: t.id, at: t.at, status: t.status ?? row.status, text: t.text })),
249 };
250}
251
252function toTimeline(t: TimelineRow): TimelineEntry {
253 return { id: t.id, at: t.at, by: t.by, kind: t.kind, public: t.public === 1, status: t.status, text: t.text, notified: t.notified };
254}
255
256function toMaintenance(row: MaintenanceRow, updates: MaintenanceUpdateRow[], origin: string): AdminMaintenance {
257 return {
258 id: row.id,
259 title: row.title,
260 message: row.message,
261 components: keys(row.components),
262 starts_at: row.starts_at,
263 ends_at: row.ends_at,
264 state: row.state,
265 url: maintenanceUrl(origin, row.id),
266 updates: updates
267 .filter((u) => u.maintenance_id === row.id)
268 .sort((a, b) => b.at.localeCompare(a.at))
269 .map((u) => ({ id: u.id, at: u.at, text: u.text })),
270 created_at: row.created_at,
271 created_by: row.created_by,
272 };
273}
274
275/** The public view of maintenance: no staff emails. */
276export function publicMaintenance(m: AdminMaintenance): StatusMaintenance {
277 const { created_at: _a, created_by: _b, ...rest } = m;
278 return rest;
279}
280
281// --- Public reads ------------------------------------------------------------------
282
283/** Public incidents matching `where`, with their parts and public updates. */
284async function publicIncidents(db: D1Database, where: string, args: unknown[], origin: string): Promise<StatusIncident[]> {
285 const list = rows<IncidentRow>(
286 (await db.prepare(`SELECT * FROM incident WHERE visibility = 'public' AND (${where}) ORDER BY started_at DESC LIMIT 500`).bind(...args).all()) as D1Result,
287 );
288 if (!list.length) return [];
289 const ids = list.map((i) => i.id);
290 const [comps, timeline, pms] = await db.batch([
291 db.prepare(`SELECT * FROM incident_component WHERE incident_id IN (${inList(ids)})`).bind(...ids),
292 db.prepare(`SELECT * FROM incident_timeline WHERE public = 1 AND incident_id IN (${inList(ids)})`).bind(...ids),
293 db.prepare(`SELECT incident_id, published_at FROM postmortem WHERE published_at IS NOT NULL AND incident_id IN (${inList(ids)})`).bind(...ids),
294 ]);
295 const published = new Map(rows<{ incident_id: string; published_at: string }>(pms).map((p) => [p.incident_id, p.published_at]));
296 return list.map((row) => toPublic(row, rows(comps), rows(timeline), origin, published.get(row.id) ?? null));
297}
298
299async function maintenanceWhere(db: D1Database, where: string, args: unknown[], origin: string): Promise<AdminMaintenance[]> {
300 const list = rows<MaintenanceRow>((await db.prepare(`SELECT * FROM maintenance WHERE ${where} ORDER BY starts_at DESC LIMIT 500`).bind(...args).all()) as D1Result);
301 if (!list.length) return [];
302 const ids = list.map((m) => m.id);
303 const updates = rows<MaintenanceUpdateRow>(
304 (await db.prepare(`SELECT * FROM maintenance_update WHERE maintenance_id IN (${inList(ids)})`).bind(...ids).all()) as D1Result,
305 );
306 return list.map((m) => toMaintenance(m, updates, origin));
307}
308
309/** Everything the front page reads. */
310export async function load(db: D1Database, now: Date, origin: string) {
311 const since = dayOf(now.getTime() - HISTORY_DAYS * DAY_MS);
312 const [current, meta, days] = await db.batch([
313 db.prepare(`SELECT component, state, detail, latency_ms FROM current`),
314 db.prepare(`SELECT value FROM meta WHERE key = 'checked_at'`),
315 db.prepare(`SELECT * FROM daily WHERE day >= ?1`).bind(since),
316 ]);
317 const [incidents, maintenance] = await Promise.all([
318 publicIncidents(db, `resolved_at IS NULL OR resolved_at >= ?1`, [iso(now.getTime() - HISTORY_DAYS * DAY_MS)], origin),
319 maintenanceWhere(db, `state IN ('scheduled', 'in_progress')`, [], origin),
320 ]);
321 const currentRows = rows<Current & { component: string }>(current);
322 return {
323 current: new Map(currentRows.map((row) => [row.component, { state: row.state, detail: row.detail, latency_ms: row.latency_ms }])),
324 checkedAt: (rows<{ value?: string }>(meta)[0]?.value ?? null) as string | null,
325 days: rows<DayRow>(days),
326 incidents,
327 maintenance: maintenance.map(publicMaintenance),
328 };
329}
330
331/** One public incident, with its postmortem once published. */
332export async function loadPublicIncident(db: D1Database, id: string, origin: string) {
333 const [incident] = await publicIncidents(db, `id = ?1`, [id], origin);
334 if (!incident) return null;
335 const pm = (await db.prepare(`SELECT * FROM postmortem WHERE incident_id = ?1 AND published_at IS NOT NULL`).bind(id).first()) as PostmortemRow | null;
336 return { incident, postmortem: pm };
337}
338
339export async function loadPublicMaintenance(db: D1Database, id: string, origin: string): Promise<StatusMaintenance | null> {
340 const [m] = await maintenanceWhere(db, `id = ?1`, [id], origin);
341 return m ? publicMaintenance(m) : null;
342}
343
344/** Incidents and finished maintenance since `since`, for the history page and the feeds. */
345export async function loadHistory(db: D1Database, since: Date, origin: string) {
346 const at = since.toISOString();
347 const [incidents, maintenance] = await Promise.all([
348 publicIncidents(db, `started_at >= ?1 OR resolved_at IS NULL`, [at], origin),
349 maintenanceWhere(db, `starts_at >= ?1 OR state IN ('scheduled', 'in_progress')`, [at], origin),
350 ]);
351 return { incidents, maintenance: maintenance.map(publicMaintenance) };
352}
353
354// --- Staff reads -------------------------------------------------------------------
355
356async function adminIncidents(db: D1Database, where: string, args: unknown[]): Promise<{ list: IncidentRow[]; comps: ComponentRow[]; timeline: TimelineRow[]; followups: FollowUpRow[]; pms: PostmortemRow[] }> {
357 const list = rows<IncidentRow>((await db.prepare(`SELECT * FROM incident WHERE ${where} ORDER BY started_at DESC LIMIT 500`).bind(...args).all()) as D1Result);
358 if (!list.length) return { list, comps: [], timeline: [], followups: [], pms: [] };
359 const ids = list.map((i) => i.id);
360 const [comps, timeline, followups, pms] = await db.batch([
361 db.prepare(`SELECT * FROM incident_component WHERE incident_id IN (${inList(ids)})`).bind(...ids),
362 db.prepare(`SELECT * FROM incident_timeline WHERE incident_id IN (${inList(ids)}) ORDER BY at, rowid`).bind(...ids),
363 db.prepare(`SELECT * FROM incident_followup WHERE incident_id IN (${inList(ids)}) ORDER BY created_at, rowid`).bind(...ids),
364 db.prepare(`SELECT * FROM postmortem WHERE incident_id IN (${inList(ids)})`).bind(...ids),
365 ]);
366 return { list, comps: rows(comps), timeline: rows(timeline), followups: rows(followups), pms: rows(pms) };
367}
368
369/** Sudo's board: open and draft incidents, the last 180 days, maintenance, and subscribers. */
370export async function board(db: D1Database, now: Date, origin: string) {
371 const since = iso(now.getTime() - 180 * DAY_MS);
372 const [data, maintenance, count] = await Promise.all([
373 adminIncidents(db, `resolved_at IS NULL OR resolved_at >= ?1`, [since]),
374 maintenanceWhere(db, `state IN ('scheduled', 'in_progress') OR ends_at >= ?1`, [iso(now.getTime() - HISTORY_DAYS * DAY_MS)], origin),
375 db.prepare(`SELECT COUNT(*) AS n FROM subscriber WHERE confirmed_at IS NOT NULL`).first<{ n: number }>(),
376 ]);
377 return {
378 incidents: data.list.map((row) => toAdmin(row, data.comps, data.timeline, data.followups, data.pms.find((p) => p.incident_id === row.id) ?? null)),
379 maintenance,
380 subscribers: count?.n ?? 0,
381 };
382}
383
384export async function openCount(db: D1Database): Promise<number> {
385 const row = await db.prepare(`SELECT COUNT(*) AS n FROM incident WHERE resolved_at IS NULL AND visibility IN ('draft', 'public')`).first<{ n: number }>();
386 return row?.n ?? 0;
387}
388
389export async function incidentDetail(
390 db: D1Database,
391 id: string,
392 origin: string,
393 names: Map<string, string>,
394 { limits = new Map<string, number>(), now = new Date() }: { limits?: Map<string, number>; now?: Date } = {},
395): Promise<AdminIncidentDetail | null> {
396 const data = await adminIncidents(db, `id = ?1`, [id]);
397 const row = data.list[0];
398 if (!row) return null;
399 // Every check of its parts around it, for the latency chart.
400 const keys = [...new Set(data.comps.filter((c) => c.incident_id === row.id).map((c) => c.component))];
401 const span = historySpan(row.started_at, row.resolved_at, now);
402 const samples = await checkHistory(db, keys, span.from, span.to);
403 const checks: CheckHistory[] = keys.map((key) => ({
404 key,
405 slow_ms: limits.get(key) ?? null,
406 from: span.from.toISOString(),
407 to: span.to.toISOString(),
408 samples: samples.filter((s) => s.component === key),
409 }));
410 const pmRow = data.pms[0] ?? null;
411 const incident = toAdmin(row, data.comps, data.timeline, data.followups, pmRow);
412 const timeline = data.timeline.map(toTimeline);
413 const followups: FollowUp[] = data.followups.map(({ incident_id: _, ...f }) => ({
414 id: f.id,
415 title: f.title,
416 owner: f.owner,
417 done_at: f.done_at,
418 created_at: f.created_at,
419 created_by: f.created_by,
420 }));
421 const postmortem: Postmortem | null = pmRow
422 ? {
423 summary: pmRow.summary,
424 impact: pmRow.impact,
425 timeline: pmRow.timeline,
426 root_cause: pmRow.root_cause,
427 went_well: pmRow.went_well,
428 went_badly: pmRow.went_badly,
429 action_items: pmRow.action_items,
430 updated_at: pmRow.updated_at,
431 updated_by: pmRow.updated_by,
432 published_at: pmRow.published_at,
433 }
434 : null;
435 return {
436 ...incident,
437 timeline,
438 followups,
439 postmortem,
440 postmortem_draft: postmortemDraft(incident, timeline, followups, names),
441 url: incidentUrl(origin, id),
442 checks,
443 };
444}
445
446export function facts(i: AdminIncident): IncidentFacts {
447 return {
448 status: i.status,
449 severity: i.severity,
450 visibility: i.visibility,
451 components: i.components,
452 started_at: i.started_at,
453 acknowledged_at: i.acknowledged_at,
454 mitigated_at: i.mitigated_at,
455 resolved_at: i.resolved_at,
456 commander: i.commander,
457 communications: i.communications,
458 };
459}
460
461export async function maintenanceById(db: D1Database, id: string, origin: string): Promise<AdminMaintenance | null> {
462 return (await maintenanceWhere(db, `id = ?1`, [id], origin))[0] ?? null;
463}
464
465// --- Writes ------------------------------------------------------------------------
466
467export function auditStatement(db: D1Database, at: string, by: string, action: string, target: string, detail: string): D1PreparedStatement {
468 return db
469 .prepare(`INSERT INTO audit (id, at, by, action, target, detail) VALUES (?1, ?2, ?3, ?4, ?5, ?6)`)
470 .bind(crypto.randomUUID(), at, by, action, target, detail.slice(0, 500));
471}
472
473/** Timeline lines, written a millisecond apart so their order survives. */
474function timelineStatements(db: D1Database, incident: string, entries: (Entry & { notified?: number | null })[], at: Date, by: string): D1PreparedStatement[] {
475 return entries.map((e, i) =>
476 db
477 .prepare(`INSERT INTO incident_timeline (id, incident_id, at, by, kind, public, status, text, notified) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)`)
478 .bind(shortId(), incident, iso(at.getTime() + i), by, e.kind, e.public ? 1 : 0, e.status, e.text, e.notified ?? null),
479 );
480}
481
482function componentStatements(db: D1Database, incident: string, list: ImpactInput[]): D1PreparedStatement[] {
483 return list.map((c) =>
484 db
485 .prepare(`INSERT INTO incident_component (incident_id, component, impact) VALUES (?1, ?2, ?3) ON CONFLICT (incident_id, component) DO UPDATE SET impact = ?3`)
486 .bind(incident, c.key, c.impact),
487 );
488}
489
490export type NewIncidentRow = {
491 title: string;
492 severity: IncidentSeverity;
493 status: IncidentStatus;
494 visibility: IncidentVisibility;
495 source: "declared" | "detected";
496 components: ImpactInput[];
497 started_at: string;
498 acknowledged_at: string | null;
499 commander: string | null;
500 communications: string | null;
501 by: string;
502};
503
504/** A new incident with its first lines. Returns its id. */
505export async function createIncident(
506 db: D1Database,
507 input: NewIncidentRow,
508 entries: (Entry & { notified?: number | null })[],
509 now: Date,
510 audit: { action: string; detail: string } | null,
511 id = shortId(),
512): Promise<string> {
513 const at = now.toISOString();
514 await db.batch([
515 db
516 .prepare(
517 `INSERT INTO incident (id, title, severity, status, visibility, source, started_at, declared_at, acknowledged_at, published_at, commander, communications, created_by)
518 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)`,
519 )
520 .bind(
521 id,
522 input.title,
523 input.severity,
524 input.status,
525 input.visibility,
526 input.source,
527 input.started_at,
528 at,
529 input.acknowledged_at,
530 input.visibility === "public" ? at : null,
531 input.commander,
532 input.communications,
533 input.by,
534 ),
535 ...componentStatements(db, id, input.components),
536 ...timelineStatements(db, id, entries, now, input.by),
537 ...(audit ? [auditStatement(db, at, input.by, audit.action, id, audit.detail)] : []),
538 ]);
539 return id;
540}
541
542/** Saves an incident's changed facts and the lines that say so. */
543export async function saveIncident(
544 db: D1Database,
545 id: string,
546 next: IncidentFacts & { title?: string; published_at?: string | null },
547 entries: (Entry & { notified?: number | null })[],
548 now: Date,
549 by: string,
550 audit: { action: string; detail: string },
551): Promise<void> {
552 await db.batch([
553 db
554 .prepare(
555 `UPDATE incident SET status = ?2, severity = ?3, visibility = ?4, acknowledged_at = ?5, mitigated_at = ?6, resolved_at = ?7,
556 commander = ?8, communications = ?9, title = COALESCE(?10, title), published_at = COALESCE(published_at, ?11) WHERE id = ?1`,
557 )
558 .bind(
559 id,
560 next.status,
561 next.severity,
562 next.visibility,
563 next.acknowledged_at,
564 next.mitigated_at,
565 next.resolved_at,
566 next.commander,
567 next.communications,
568 next.title ?? null,
569 next.published_at ?? null,
570 ),
571 ...componentStatements(db, id, next.components),
572 ...timelineStatements(db, id, entries, now, by),
573 auditStatement(db, now.toISOString(), by, audit.action, id, audit.detail),
574 ]);
575}
576
577/** Lines the checks write on open incidents: failing again, or answering again. */
578export async function addSystemLines(db: D1Database, lines: { incident: string; kind: TimelineKind; text: string }[], now: Date): Promise<void> {
579 if (!lines.length) return;
580 await db.batch(lines.flatMap((l) => timelineStatements(db, l.incident, [{ kind: l.kind, public: false, status: null, text: l.text }], now, "status")));
581}
582
583export async function addFollowUp(db: D1Database, incident: string, input: { title: string; owner: string | null; by: string }, now: Date): Promise<FollowUp> {
584 const f: FollowUp = { id: shortId(), title: input.title, owner: input.owner, done_at: null, created_at: now.toISOString(), created_by: input.by };
585 await db.batch([
586 db
587 .prepare(`INSERT INTO incident_followup (id, incident_id, title, owner, created_at, created_by) VALUES (?1, ?2, ?3, ?4, ?5, ?6)`)
588 .bind(f.id, incident, f.title, f.owner, f.created_at, f.created_by),
589 ...timelineStatements(db, incident, [{ kind: "followup", public: false, status: null, text: `Follow-up: ${f.title}${f.owner ? ` (${f.owner})` : ""}.` }], now, input.by),
590 auditStatement(db, f.created_at, input.by, "incident_followup", incident, `Added follow-up: ${f.title}`),
591 ]);
592 return f;
593}
594
595export async function setFollowUp(db: D1Database, incident: string, id: string, done: boolean, by: string, now: Date): Promise<FollowUp | null> {
596 const row = (await db.prepare(`SELECT * FROM incident_followup WHERE id = ?1 AND incident_id = ?2`).bind(id, incident).first()) as FollowUpRow | null;
597 if (!row) return null;
598 const at = now.toISOString();
599 await db.batch([
600 db.prepare(`UPDATE incident_followup SET done_at = ?2, done_by = ?3 WHERE id = ?1`).bind(id, done ? at : null, done ? by : null),
601 ...timelineStatements(db, incident, [{ kind: "followup", public: false, status: null, text: `${done ? "Done" : "Reopened"}: ${row.title}.` }], now, by),
602 auditStatement(db, at, by, "incident_followup", incident, `${done ? "Done" : "Reopened"}: ${row.title}`),
603 ]);
604 return { id, title: row.title, owner: row.owner, done_at: done ? at : null, created_at: row.created_at, created_by: row.created_by };
605}
606
607export async function savePostmortem(db: D1Database, incident: string, fields: PostmortemFields, by: string, now: Date): Promise<void> {
608 const at = now.toISOString();
609 await db.batch([
610 db
611 .prepare(
612 `INSERT INTO postmortem (incident_id, summary, impact, timeline, root_cause, went_well, went_badly, action_items, updated_at, updated_by)
613 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)
614 ON CONFLICT (incident_id) DO UPDATE SET summary = ?2, impact = ?3, timeline = ?4, root_cause = ?5, went_well = ?6, went_badly = ?7,
615 action_items = ?8, updated_at = ?9, updated_by = ?10`,
616 )
617 .bind(incident, fields.summary, fields.impact, fields.timeline, fields.root_cause, fields.went_well, fields.went_badly, fields.action_items, at, by),
618 auditStatement(db, at, by, "postmortem_saved", incident, "Saved the postmortem draft"),
619 ]);
620}
621
622export async function publishPostmortem(db: D1Database, incident: string, publish: boolean, by: string, now: Date): Promise<void> {
623 const at = now.toISOString();
624 await db.batch([
625 db.prepare(`UPDATE postmortem SET published_at = ?2, published_by = ?3 WHERE incident_id = ?1`).bind(incident, publish ? at : null, publish ? by : null),
626 ...timelineStatements(db, incident, [{ kind: "postmortem", public: false, status: null, text: publish ? "Postmortem published." : "Postmortem taken down." }], now, by),
627 auditStatement(db, at, by, publish ? "postmortem_published" : "postmortem_unpublished", incident, publish ? "Published the postmortem" : "Took the postmortem down"),
628 ]);
629}
630
631// --- Maintenance ---------------------------------------------------------------------
632
633export async function scheduleMaintenance(
634 db: D1Database,
635 input: { title: string; message: string; components: string[]; starts_at: string; ends_at: string; notify: boolean; by: string },
636 notified: number | null,
637 now: Date,
638 id = shortId(),
639): Promise<string> {
640 const at = now.toISOString();
641 await db.batch([
642 db
643 .prepare(
644 `INSERT INTO maintenance (id, title, message, components, starts_at, ends_at, state, notify, created_at, created_by)
645 VALUES (?1, ?2, ?3, ?4, ?5, ?6, 'scheduled', ?7, ?8, ?9)`,
646 )
647 .bind(id, input.title, input.message, JSON.stringify(input.components), input.starts_at, input.ends_at, input.notify ? 1 : 0, at, input.by),
648 db
649 .prepare(`INSERT INTO maintenance_update (id, maintenance_id, at, by, text, notified) VALUES (?1, ?2, ?3, ?4, ?5, ?6)`)
650 .bind(shortId(), id, at, input.by, input.message, notified),
651 auditStatement(db, at, input.by, "maintenance_scheduled", id, `${input.title} (${input.starts_at} to ${input.ends_at})`),
652 ]);
653 return id;
654}
655
656/** Moves maintenance along and posts its update. Null `state` keeps it where it is. */
657export async function maintenanceUpdate(
658 db: D1Database,
659 id: string,
660 state: MaintenanceState | null,
661 text: string,
662 by: string,
663 notified: number | null,
664 now: Date,
665 audit: { action: string; detail: string } | null,
666): Promise<void> {
667 const at = now.toISOString();
668 await db.batch([
669 ...(state ? [db.prepare(`UPDATE maintenance SET state = ?2 WHERE id = ?1`).bind(id, state)] : []),
670 db
671 .prepare(`INSERT INTO maintenance_update (id, maintenance_id, at, by, text, notified) VALUES (?1, ?2, ?3, ?4, ?5, ?6)`)
672 .bind(shortId(), id, at, by, text, notified),
673 ...(audit ? [auditStatement(db, at, by, audit.action, id, audit.detail)] : []),
674 ]);
675}
676
677/** Maintenance whose window has started or ended but whose state has not caught up. */
678export async function dueMaintenance(db: D1Database, now: Date, origin: string): Promise<(AdminMaintenance & { notify: boolean })[]> {
679 const at = now.toISOString();
680 const list = rows<MaintenanceRow>(
681 (await db
682 .prepare(`SELECT * FROM maintenance WHERE (state = 'scheduled' AND starts_at <= ?1) OR (state = 'in_progress' AND ends_at <= ?1)`)
683 .bind(at)
684 .all()) as D1Result,
685 );
686 return list.map((row) => ({ ...toMaintenance(row, [], origin), notify: row.notify === 1 }));
687}
688
689// --- Subscribers -----------------------------------------------------------------------
690
691type SubscriberRow = {
692 id: string;
693 email: string;
694 components: string | null;
695 pending_components: string | null;
696 confirm_hash: string | null;
697 confirm_expires_at: string | null;
698 confirm_sent_at: string | null;
699 confirmed_at: string | null;
700};
701
702/**
703 * Asks for a subscription (or a change of parts): a new confirmation,
704 * unless one went out to this address in the last `resendAfterMs`. The
705 * caller answers the same either way, so the form never tells whether an
706 * address is subscribed.
707 */
708export async function requestSubscription(
709 db: D1Database,
710 email: string,
711 parts: string[] | null,
712 hash: string,
713 now: Date,
714 ttlMs: number,
715 resendAfterMs: number,
716): Promise<{ send: boolean }> {
717 const at = now.toISOString();
718 const existing = (await db.prepare(`SELECT * FROM subscriber WHERE email = ?1`).bind(email).first()) as SubscriberRow | null;
719 if (existing?.confirm_sent_at && now.getTime() - Date.parse(existing.confirm_sent_at) < resendAfterMs) return { send: false };
720 const pending = parts ? JSON.stringify(parts) : null;
721 if (existing) {
722 await db
723 .prepare(`UPDATE subscriber SET pending_components = ?2, confirm_hash = ?3, confirm_expires_at = ?4, confirm_sent_at = ?5 WHERE id = ?1`)
724 .bind(existing.id, pending, hash, iso(now.getTime() + ttlMs), at)
725 .run();
726 } else {
727 await db
728 .prepare(
729 `INSERT INTO subscriber (id, email, components, pending_components, confirm_hash, confirm_expires_at, confirm_sent_at, created_at)
730 VALUES (?1, ?2, NULL, ?3, ?4, ?5, ?6, ?6)`,
731 )
732 .bind(crypto.randomUUID(), email, pending, hash, iso(now.getTime() + ttlMs), at)
733 .run();
734 }
735 return { send: true };
736}
737
738/** Confirms the subscription a token's hash belongs to; null when none or expired. */
739export async function confirmSubscription(db: D1Database, hash: string, now: Date): Promise<{ email: string; parts: string[] | null } | null> {
740 const row = (await db.prepare(`SELECT * FROM subscriber WHERE confirm_hash = ?1`).bind(hash).first()) as SubscriberRow | null;
741 if (!row || !row.confirm_expires_at || Date.parse(row.confirm_expires_at) < now.getTime()) return null;
742 await db
743 .prepare(
744 `UPDATE subscriber SET components = pending_components, pending_components = NULL, confirm_hash = NULL, confirm_expires_at = NULL,
745 confirmed_at = COALESCE(confirmed_at, ?2) WHERE id = ?1`,
746 )
747 .bind(row.id, now.toISOString())
748 .run();
749 return { email: row.email, parts: parseParts(row.pending_components) };
750}
751
752export async function unsubscribe(db: D1Database, id: string): Promise<boolean> {
753 const result = await db.prepare(`DELETE FROM subscriber WHERE id = ?1`).bind(id).run();
754 return (result.meta?.changes ?? 0) > 0;
755}
756
757/** Confirmed subscribers who want news about these parts. */
758export async function recipients(db: D1Database, about: string[]): Promise<{ id: string; email: string }[]> {
759 const all = rows<SubscriberRow>((await db.prepare(`SELECT id, email, components FROM subscriber WHERE confirmed_at IS NOT NULL`).all()) as D1Result);
760 return all.filter((s) => wants(parseParts(s.components), about)).map((s) => ({ id: s.id, email: s.email }));
761}
762
763// --- Detection ---------------------------------------------------------------------------
764
765export async function loadStreaks(db: D1Database): Promise<Map<string, Streak>> {
766 const list = rows<{ component: string; state: "degraded" | "down"; count: number; checks: number | null; recent: string | null; since: string; alerted: number }>(
767 (await db.prepare(`SELECT component, state, count, checks, recent, since, alerted FROM streak`).all()) as D1Result,
768 );
769 // Runs kept before migration 0003 have no `checks` or `recent`: upgradeStreak fills them in.
770 return new Map(list.map((s) => [s.component, upgradeStreak({ ...s, alerted: s.alerted === 1 })]));
771}
772
773/**
774 * Keeps exactly these runs: every other part's run is over. With none
775 * left, every row goes (`NOT IN (NULL)` matches nothing in SQL, which once
776 * kept a finished run alive and let its old start leak into the next one).
777 */
778export async function saveStreaks(db: D1Database, streaks: Streak[]): Promise<void> {
779 const keep = streaks.map((s) => s.component);
780 await db.batch([
781 keep.length ? db.prepare(`DELETE FROM streak WHERE component NOT IN (${inList(keep)})`).bind(...keep) : db.prepare(`DELETE FROM streak`),
782 ...streaks.map((s) =>
783 db
784 .prepare(
785 `INSERT INTO streak (component, state, count, since, alerted, checks, recent) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
786 ON CONFLICT (component) DO UPDATE SET state = ?2, count = ?3, since = ?4, alerted = ?5, checks = ?6, recent = ?7`,
787 )
788 .bind(s.component, s.state, s.count, s.since, s.alerted ? 1 : 0, s.checks, s.recent),
789 ),
790 ]);
791}
792
793/** Open incidents, drafts included, with the parts they affect: what detection checks against. */
794export async function openRefs(db: D1Database): Promise<{ id: string; components: string[] }[]> {
795 const list = rows<{ id: string; component: string | null }>(
796 (await db
797 .prepare(
798 `SELECT i.id, c.component FROM incident i LEFT JOIN incident_component c ON c.incident_id = i.id AND c.impact != 'operational'
799 WHERE i.resolved_at IS NULL AND i.visibility IN ('draft', 'public')`,
800 )
801 .all()) as D1Result,
802 );
803 const map = new Map<string, string[]>();
804 for (const r of list) {
805 const parts = map.get(r.id) ?? [];
806 if (r.component) parts.push(r.component);
807 map.set(r.id, parts);
808 }
809 return [...map].map(([id, components]) => ({ id, components }));
810}
811
812/** The last deploy the deploy tool reported, kept in `meta`. */
813export async function loadDeploy(db: D1Database): Promise<DeployWindow | null> {
814 const row = (await db.prepare(`SELECT value FROM meta WHERE key = 'deploy'`).first()) as { value?: string } | null;
815 if (!row?.value) return null;
816 try {
817 const w = JSON.parse(row.value) as Partial<DeployWindow>;
818 if (typeof w.started_at !== "string") return null;
819 const finished_at = w.finished_at ?? null;
820 return {
821 id: w.id ?? null,
822 started_at: w.started_at,
823 finished_at,
824 // Kept before deploys were counted: one, unless it finished.
825 running: typeof w.running === "number" ? w.running : finished_at ? 0 : 1,
826 last_started_at: typeof w.last_started_at === "string" ? w.last_started_at : w.started_at,
827 };
828 } catch {
829 return null;
830 }
831}
832
833export async function saveDeploy(db: D1Database, window: DeployWindow): Promise<void> {
834 await db
835 .prepare(`INSERT INTO meta (key, value) VALUES ('deploy', ?1) ON CONFLICT (key) DO UPDATE SET value = ?1`)
836 .bind(JSON.stringify(window))
837 .run();
838}
839
840const HEALTHY = "draft_healthy:";
841const REMINDED = "draft_reminded:";
842
843/**
844 * Detected drafts no one has picked up (unacknowledged, never published),
845 * with their parts, since when those have been healthy, and when the
846 * alert last went out again for them.
847 */
848export async function watchedDrafts(db: D1Database): Promise<WatchedDraft[]> {
849 const list = rows<{
850 id: string;
851 title: string;
852 started_at: string;
853 declared_at: string;
854 healthy_since: string | null;
855 reminded_at: string | null;
856 component: string | null;
857 }>(
858 (await db
859 .prepare(
860 `SELECT i.id, i.title, i.started_at, i.declared_at, m.value AS healthy_since, r.value AS reminded_at, c.component FROM incident i
861 LEFT JOIN incident_component c ON c.incident_id = i.id AND c.impact != 'operational'
862 LEFT JOIN meta m ON m.key = '${HEALTHY}' || i.id
863 LEFT JOIN meta r ON r.key = '${REMINDED}' || i.id
864 WHERE i.source = 'detected' AND i.visibility = 'draft' AND i.acknowledged_at IS NULL AND i.resolved_at IS NULL AND i.published_at IS NULL`,
865 )
866 .all()) as D1Result,
867 );
868 const map = new Map<string, WatchedDraft>();
869 for (const r of list) {
870 const d = map.get(r.id) ?? {
871 id: r.id,
872 title: r.title,
873 started_at: r.started_at,
874 declared_at: r.declared_at,
875 healthy_since: r.healthy_since ?? null,
876 reminded_at: r.reminded_at ?? null,
877 components: [],
878 };
879 if (r.component) d.components.push(r.component);
880 map.set(r.id, d);
881 }
882 return [...map.values()];
883}
884
885/** Keeps each watched draft's healthy-since, and forgets drafts no longer watched. */
886export async function saveHealthy(db: D1Database, healthy: { id: string; since: string | null }[]): Promise<void> {
887 const keys = healthy.filter((h) => h.since).map((h) => `${HEALTHY}${h.id}`);
888 await db.batch([
889 keys.length
890 ? db.prepare(`DELETE FROM meta WHERE key LIKE '${HEALTHY}%' AND key NOT IN (${inList(keys)})`).bind(...keys)
891 : db.prepare(`DELETE FROM meta WHERE key LIKE '${HEALTHY}%'`),
892 ...healthy
893 .filter((h) => h.since)
894 .map((h) =>
895 db
896 .prepare(`INSERT INTO meta (key, value) VALUES (?1, ?2) ON CONFLICT (key) DO UPDATE SET value = excluded.value WHERE meta.value != excluded.value`)
897 .bind(`${HEALTHY}${h.id}`, h.since),
898 ),
899 ]);
900}
901
902/**
903 * Notes that the alert went out again for these drafts (`staleDrafts`),
904 * with a line on each one's timeline, and forgets the drafts no longer
905 * watched (`watching`: every watched draft's id).
906 */
907export async function saveReminders(db: D1Database, watching: string[], reminded: { id: string; text: string }[], now: Date): Promise<void> {
908 const keys = watching.map((id) => `${REMINDED}${id}`);
909 await db.batch([
910 keys.length
911 ? db.prepare(`DELETE FROM meta WHERE key LIKE '${REMINDED}%' AND key NOT IN (${inList(keys)})`).bind(...keys)
912 : db.prepare(`DELETE FROM meta WHERE key LIKE '${REMINDED}%'`),
913 ...reminded.flatMap((r) => [
914 db
915 .prepare(`INSERT INTO meta (key, value) VALUES (?1, ?2) ON CONFLICT (key) DO UPDATE SET value = excluded.value`)
916 .bind(`${REMINDED}${r.id}`, now.toISOString()),
917 ...timelineStatements(db, r.id, [{ kind: "note", public: false, status: null, text: r.text }], now, "status"),
918 ]),
919 ]);
920}
921
922/**
923 * Dismisses a detected draft that recovered, as `status`, resolved at the
924 * moment it recovered. Only while it is still an untouched draft: returns
925 * false (and changes nothing) when staff got to it first.
926 */
927export async function autoDismiss(db: D1Database, id: string, recoveredAt: string, text: string, now: Date): Promise<boolean> {
928 const done = await db
929 .prepare(
930 `UPDATE incident SET visibility = 'dismissed', status = 'resolved', resolved_at = ?2
931 WHERE id = ?1 AND visibility = 'draft' AND source = 'detected' AND acknowledged_at IS NULL AND resolved_at IS NULL`,
932 )
933 .bind(id, recoveredAt)
934 .run();
935 if ((done.meta?.changes ?? 0) === 0) return false;
936 await db.batch([
937 ...timelineStatements(db, id, [{ kind: "dismissed", public: false, status: null, text }], now, "status"),
938 db.prepare(`DELETE FROM meta WHERE key IN (?1, ?2)`).bind(`${HEALTHY}${id}`, `${REMINDED}${id}`),
939 auditStatement(db, now.toISOString(), "status", "incident_dismissed", id, `Dismissed automatically: ${text}`),
940 ]);
941 return true;
942}
943
944// --- Audit ---------------------------------------------------------------------------------
945
946export async function auditLog(db: D1Database, before: string | null, limit = 100): Promise<StatusAuditEntry[]> {
947 const statement = before
948 ? db.prepare(`SELECT * FROM audit WHERE at < ?1 ORDER BY at DESC LIMIT ?2`).bind(before, limit)
949 : db.prepare(`SELECT * FROM audit ORDER BY at DESC LIMIT ?1`).bind(limit);
950 return rows<StatusAuditEntry>((await statement.all()) as D1Result);
951}