Sign in

flagon-io/g1t

public

g1t/apps/status/src/store.ts

749 lines32,550 bytesCodeBlame

Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.

status.g1t.sh with incident management, invites that land you in the workspace, settings as pages, usage without quotas1/**
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 { Streak } 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
708export async function saveStreaks(db: D1Database, streaks: Streak[]): Promise<void> {
709 const keep = streaks.map((s) => s.component);
710 await db.batch([
711 db.prepare(`DELETE FROM streak WHERE component NOT IN (${inList(keep)})`).bind(...keep),
712 ...streaks.map((s) =>
713 db
714 .prepare(
715 `INSERT INTO streak (component, state, count, since, alerted) VALUES (?1, ?2, ?3, ?4, ?5)
716 ON CONFLICT (component) DO UPDATE SET state = ?2, count = ?3, since = ?4, alerted = ?5`,
717 )
718 .bind(s.component, s.state, s.count, s.since, s.alerted ? 1 : 0),
719 ),
720 ]);
721}
722
723/** Open incidents, drafts included, with the parts they affect: what detection checks against. */
724export async function openRefs(db: D1Database): Promise<{ id: string; components: string[] }[]> {
725 const list = rows<{ id: string; component: string | null }>(
726 (await db
727 .prepare(
728 `SELECT i.id, c.component FROM incident i LEFT JOIN incident_component c ON c.incident_id = i.id AND c.impact != 'operational'
729 WHERE i.resolved_at IS NULL AND i.visibility IN ('draft', 'public')`,
730 )
731 .all()) as D1Result,
732 );
733 const map = new Map<string, string[]>();
734 for (const r of list) {
735 const parts = map.get(r.id) ?? [];
736 if (r.component) parts.push(r.component);
737 map.set(r.id, parts);
738 }
739 return [...map].map(([id, components]) => ({ id, components }));
740}
741
742// --- Audit ---------------------------------------------------------------------------------
743
744export async function auditLog(db: D1Database, before: string | null, limit = 100): Promise<StatusAuditEntry[]> {
745 const statement = before
746 ? db.prepare(`SELECT * FROM audit WHERE at < ?1 ORDER BY at DESC LIMIT ?2`).bind(before, limit)
747 : db.prepare(`SELECT * FROM audit ORDER BY at DESC LIMIT ?1`).bind(limit);
748 return rows<StatusAuditEntry>((await statement.all()) as D1Result);
749}