Sign in

flagon-io/g1t

public

g1t/apps/status/src/store.ts

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