Skip to content
Sign up

flagon-io/g1t

public
00

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

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