my own status page
1export async function insertPing(
2 db: D1Database,
3 service_id: string,
4 status: string,
5 latency_ms: number,
6): Promise<void> {
7 await db
8 .prepare(
9 "INSERT INTO pings (service_id, timestamp, status, latency_ms) VALUES (?, ?, ?, ?)",
10 )
11 .bind(service_id, Math.floor(Date.now() / 1000), status, latency_ms)
12 .run();
13}
14
15export async function getLatestPing(
16 db: D1Database,
17 service_id: string,
18): Promise<{ status: string; latency_ms: number | null } | null> {
19 const row = await db
20 .prepare(
21 "SELECT status, latency_ms FROM pings WHERE service_id = ? ORDER BY timestamp DESC LIMIT 1",
22 )
23 .bind(service_id)
24 .first();
25 if (!row) return null;
26 return { status: row.status as string, latency_ms: row.latency_ms as number | null };
27}
28
29export async function getUptime7d(
30 db: D1Database,
31 service_id: string,
32 days = 90,
33): Promise<number> {
34 const since = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
35 const row = await db
36 .prepare(
37 "SELECT COUNT(*) as total, SUM(CASE WHEN status = 'up' THEN 1 ELSE 0 END) as up_count FROM pings WHERE service_id = ? AND timestamp >= ?",
38 )
39 .bind(service_id, since)
40 .first<{ total: number; up_count: number }>();
41
42 if (!row || row.total === 0) return 100;
43 return Math.round((row.up_count / row.total) * 10000) / 100;
44}
45
46export async function getAllLatestPings(
47 db: D1Database,
48): Promise<Map<string, { status: string; latency_ms: number | null }>> {
49 const rows = await db
50 .prepare(
51 `SELECT p.service_id, p.status, p.latency_ms
52 FROM pings p
53 INNER JOIN (SELECT service_id, MAX(timestamp) as max_ts FROM pings GROUP BY service_id) latest
54 ON p.service_id = latest.service_id AND p.timestamp = latest.max_ts`,
55 )
56 .all();
57
58 const map = new Map<string, { status: string; latency_ms: number | null }>();
59 for (const row of rows.results) {
60 map.set(row.service_id as string, {
61 status: row.status as string,
62 latency_ms: row.latency_ms as number | null,
63 });
64 }
65 return map;
66}
67
68export async function getAllUptime7d(
69 db: D1Database,
70 days = 90,
71): Promise<Map<string, number>> {
72 const since = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
73 const rows = await db
74 .prepare(
75 `SELECT service_id, COUNT(*) as total, SUM(CASE WHEN status = 'up' THEN 1 ELSE 0 END) as up_count
76 FROM pings WHERE timestamp >= ?
77 GROUP BY service_id`,
78 )
79 .bind(since)
80 .all();
81
82 const map = new Map<string, number>();
83 for (const row of rows.results) {
84 const total = row.total as number;
85 const up = row.up_count as number;
86 map.set(
87 row.service_id as string,
88 total === 0 ? 100 : Math.round((up / total) * 10000) / 100,
89 );
90 }
91 return map;
92}
93
94export async function getUptimeBuckets(
95 db: D1Database,
96 service_id: string,
97 window_hours: number,
98): Promise<{ timestamp: number; status: "up" | "degraded" | "down" }[]> {
99 const since = Math.floor(Date.now() / 1000) - window_hours * 60 * 60;
100 const rows = await db
101 .prepare(
102 `SELECT
103 (timestamp / 3600) * 3600 AS bucket,
104 status,
105 COUNT(*) AS cnt
106 FROM pings
107 WHERE service_id = ? AND timestamp >= ?
108 GROUP BY bucket, status
109 ORDER BY bucket ASC`,
110 )
111 .bind(service_id, since)
112 .all();
113
114 const bucketMap = new Map<number, Map<string, number>>();
115 for (const row of rows.results) {
116 const b = row.bucket as number;
117 if (!bucketMap.has(b)) bucketMap.set(b, new Map());
118 bucketMap.get(b)!.set(row.status as string, row.cnt as number);
119 }
120
121 const result: { timestamp: number; status: "up" | "degraded" | "down" }[] = [];
122 for (const [bucket, counts] of bucketMap) {
123 let status: "up" | "degraded" | "down" = "up";
124 if (counts.has("down")) status = "down";
125 else if (counts.has("degraded")) status = "degraded";
126 result.push({ timestamp: bucket, status });
127 }
128
129 return result;
130}
131
132export async function getOverallUptimeDays(
133 db: D1Database,
134 days: number,
135 serviceIds?: string[],
136): Promise<{ date: string; status: "up" | "degraded" | "down" | "none" }[]> {
137 const since = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
138 let rows;
139 if (serviceIds && serviceIds.length > 0) {
140 const placeholders = serviceIds.map(() => "?").join(", ");
141 rows = await db
142 .prepare(
143 `SELECT
144 (timestamp / 86400) AS day_bucket,
145 service_id,
146 SUM(CASE WHEN status IN ('down','timeout') THEN 1 ELSE 0 END) AS bad_count,
147 SUM(CASE WHEN status IN ('degraded','misconfigured') THEN 1 ELSE 0 END) AS degraded_count,
148 COUNT(*) AS total
149 FROM pings
150 WHERE timestamp >= ? AND service_id IN (${placeholders})
151 GROUP BY day_bucket, service_id
152 ORDER BY day_bucket ASC`,
153 )
154 .bind(since, ...serviceIds)
155 .all();
156 } else {
157 rows = await db
158 .prepare(
159 `SELECT
160 (timestamp / 86400) AS day_bucket,
161 service_id,
162 SUM(CASE WHEN status IN ('down','timeout') THEN 1 ELSE 0 END) AS bad_count,
163 SUM(CASE WHEN status IN ('degraded','misconfigured') THEN 1 ELSE 0 END) AS degraded_count,
164 COUNT(*) AS total
165 FROM pings
166 WHERE timestamp >= ?
167 GROUP BY day_bucket, service_id
168 ORDER BY day_bucket ASC`,
169 )
170 .bind(since)
171 .all();
172 }
173
174 // bucketMap: day_bucket -> { totalPings, totalBad, anyServiceReallyBad, anyServiceReallyDegraded }
175 const bucketMap = new Map<number, { total: number; bad: number; reallyBad: boolean; reallyDegraded: boolean }>();
176 for (const row of rows.results) {
177 const b = row.day_bucket as number;
178 if (!bucketMap.has(b)) bucketMap.set(b, { total: 0, bad: 0, reallyBad: false, reallyDegraded: false });
179 const bucket = bucketMap.get(b)!;
180 const badCount = row.bad_count as number;
181 const degradedCount = row.degraded_count as number;
182 bucket.total += row.total as number;
183 bucket.bad += badCount;
184 // A service counts as "really" bad/degraded only if it failed ≥2 checks,
185 // matching the 2-consecutive-failures threshold used by the incident system.
186 if (badCount >= 2) bucket.reallyBad = true;
187 if (degradedCount >= 2) bucket.reallyDegraded = true;
188 }
189
190 const now = Math.floor(Date.now() / 1000);
191 const todayBucket = Math.floor(now / 86400);
192 const result: { date: string; status: "up" | "degraded" | "down" | "none" }[] = [];
193
194 for (let i = days - 1; i >= 0; i--) {
195 const bucket = todayBucket - i;
196 const d = new Date(bucket * 86400 * 1000);
197 const date = d.toISOString().slice(0, 10);
198 const counts = bucketMap.get(bucket);
199
200 if (!counts) {
201 result.push({ date, status: "none" });
202 continue;
203 }
204
205 const badRatio = counts.total > 0 ? counts.bad / counts.total : 0;
206
207 let status: "up" | "degraded" | "down" = "up";
208 if (badRatio > 0.05) status = "down";
209 else if (counts.reallyBad || counts.reallyDegraded) status = "degraded";
210
211 result.push({ date, status });
212 }
213
214 return result;
215}
216
217export async function getOverallUptimePct(
218 db: D1Database,
219 days: number,
220 serviceIds?: string[],
221): Promise<number> {
222 const since = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
223 let row: { up_count: number; total: number } | null;
224 if (serviceIds && serviceIds.length > 0) {
225 const placeholders = serviceIds.map(() => "?").join(", ");
226 row = await db
227 .prepare(
228 `SELECT
229 SUM(CASE WHEN status NOT IN ('down','timeout') THEN 1 ELSE 0 END) AS up_count,
230 COUNT(*) AS total
231 FROM pings
232 WHERE timestamp >= ? AND service_id IN (${placeholders})`,
233 )
234 .bind(since, ...serviceIds)
235 .first<{ up_count: number; total: number }>();
236 } else {
237 row = await db
238 .prepare(
239 `SELECT
240 SUM(CASE WHEN status NOT IN ('down','timeout') THEN 1 ELSE 0 END) AS up_count,
241 COUNT(*) AS total
242 FROM pings
243 WHERE timestamp >= ?`,
244 )
245 .bind(since)
246 .first<{ up_count: number; total: number }>();
247 }
248 if (!row || row.total === 0) return 100;
249 return Math.round((row.up_count / row.total) * 10000) / 100;
250}
251
252export async function getLastCheckTime(
253 db: D1Database,
254): Promise<number | null> {
255 const row = await db
256 .prepare("SELECT MAX(timestamp) as ts FROM pings")
257 .first<{ ts: number | null }>();
258 return row?.ts ?? null;
259}
260
261export async function pruneOldPings(
262 db: D1Database,
263 days: number,
264): Promise<void> {
265 const cutoff = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
266 await db
267 .prepare("DELETE FROM pings WHERE timestamp < ?")
268 .bind(cutoff)
269 .run();
270}
271
272import type { Incident, IncidentUpdate, IncidentWithUpdates } from "./types";
273
274export async function createIncident(
275 db: D1Database,
276 data: { service_id: string; title: string; severity: "critical" | "major" | "minor"; github_repo?: string; github_issue_number?: number },
277): Promise<number> {
278 const now = Math.floor(Date.now() / 1000);
279 const result = await db
280 .prepare(
281 "INSERT INTO incidents (service_id, title, status, severity, github_repo, github_issue_number, started_at, created_at, updated_at) VALUES (?, ?, 'investigating', ?, ?, ?, ?, ?, ?)",
282 )
283 .bind(data.service_id, data.title, data.severity, data.github_repo ?? null, data.github_issue_number ?? null, now, now, now)
284 .run();
285 const id = result.meta.last_row_id;
286 await db
287 .prepare(
288 "INSERT INTO incident_updates (incident_id, status, message, created_at) VALUES (?, 'investigating', 'Incident detected automatically', ?)",
289 )
290 .bind(id, now)
291 .run();
292 return id as number;
293}
294
295export async function updateIncident(
296 db: D1Database,
297 id: number,
298 data: { status?: string; resolved_at?: number },
299): Promise<void> {
300 const sets: string[] = [];
301 const values: unknown[] = [];
302 if (data.status) { sets.push("status = ?"); values.push(data.status); }
303 if (data.resolved_at) { sets.push("resolved_at = ?"); values.push(data.resolved_at); }
304 sets.push("updated_at = ?");
305 values.push(Math.floor(Date.now() / 1000));
306 values.push(id);
307 await db
308 .prepare(`UPDATE incidents SET ${sets.join(", ")} WHERE id = ?`)
309 .bind(...values)
310 .run();
311}
312
313export async function addIncidentUpdate(
314 db: D1Database,
315 incident_id: number,
316 status: string,
317 message: string,
318): Promise<void> {
319 const now = Math.floor(Date.now() / 1000);
320 await db
321 .prepare("INSERT INTO incident_updates (incident_id, status, message, created_at) VALUES (?, ?, ?, ?)")
322 .bind(incident_id, status, message, now)
323 .run();
324}
325
326export async function getActiveIncidents(db: D1Database): Promise<Incident[]> {
327 const rows = await db
328 .prepare("SELECT * FROM incidents WHERE status != 'resolved' ORDER BY created_at DESC")
329 .all();
330 return rows.results as unknown as Incident[];
331}
332
333export async function getActiveIncidentsWithUpdates(db: D1Database): Promise<IncidentWithUpdates[]> {
334 const rows = await db
335 .prepare(
336 `SELECT i.*, u.id as update_id, u.status as update_status, u.message as update_message, u.created_at as update_created_at
337 FROM incidents i
338 LEFT JOIN incident_updates u ON u.incident_id = i.id
339 WHERE i.status != 'resolved'
340 ORDER BY i.created_at DESC, u.created_at ASC`,
341 )
342 .all();
343
344 const incidentMap = new Map<number, IncidentWithUpdates>();
345 for (const row of rows.results) {
346 const id = row.id as number;
347 if (!incidentMap.has(id)) {
348 incidentMap.set(id, {
349 id,
350 service_id: row.service_id as string,
351 title: row.title as string,
352 status: row.status as string,
353 severity: row.severity as string,
354 github_repo: row.github_repo as string | null,
355 github_issue_number: row.github_issue_number as number | null,
356 started_at: row.started_at as number,
357 resolved_at: row.resolved_at as number | null,
358 created_at: row.created_at as number,
359 updated_at: row.updated_at as number,
360 updates: [],
361 });
362 }
363 if (row.update_id) {
364 incidentMap.get(id)!.updates.push({
365 id: row.update_id as number,
366 incident_id: id,
367 status: row.update_status as string,
368 message: row.update_message as string,
369 created_at: row.update_created_at as number,
370 });
371 }
372 }
373 return Array.from(incidentMap.values());
374}
375
376export async function getActiveIncidentForService(
377 db: D1Database,
378 service_id: string,
379): Promise<Incident | null> {
380 const row = await db
381 .prepare("SELECT * FROM incidents WHERE service_id = ? AND status != 'resolved' ORDER BY created_at DESC LIMIT 1")
382 .bind(service_id)
383 .first();
384 return (row as unknown as Incident) ?? null;
385}
386
387export async function getRecentIncidents(db: D1Database, days: number): Promise<Incident[]> {
388 const since = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
389 const rows = await db
390 .prepare("SELECT * FROM incidents WHERE resolved_at >= ? OR status != 'resolved' ORDER BY created_at DESC")
391 .bind(since)
392 .all();
393 return rows.results as unknown as Incident[];
394}
395
396export async function getRecentResolvedIncidentsWithUpdates(db: D1Database, days: number): Promise<IncidentWithUpdates[]> {
397 const since = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
398 const rows = await db
399 .prepare(
400 `SELECT i.*, u.id as update_id, u.status as update_status, u.message as update_message, u.created_at as update_created_at
401 FROM incidents i
402 LEFT JOIN incident_updates u ON u.incident_id = i.id
403 WHERE i.status = 'resolved' AND i.resolved_at >= ?
404 ORDER BY i.resolved_at DESC, u.created_at ASC`,
405 )
406 .bind(since)
407 .all();
408
409 const incidentMap = new Map<number, IncidentWithUpdates>();
410 for (const row of rows.results) {
411 const id = row.id as number;
412 if (!incidentMap.has(id)) {
413 incidentMap.set(id, {
414 id,
415 service_id: row.service_id as string,
416 title: row.title as string,
417 status: row.status as string,
418 severity: row.severity as string,
419 github_repo: row.github_repo as string | null,
420 github_issue_number: row.github_issue_number as number | null,
421 started_at: row.started_at as number,
422 resolved_at: row.resolved_at as number | null,
423 created_at: row.created_at as number,
424 updated_at: row.updated_at as number,
425 updates: [],
426 });
427 }
428 if (row.update_id) {
429 incidentMap.get(id)!.updates.push({
430 id: row.update_id as number,
431 incident_id: id,
432 status: row.update_status as string,
433 message: row.update_message as string,
434 created_at: row.update_created_at as number,
435 });
436 }
437 }
438 return Array.from(incidentMap.values());
439}
440
441export async function getIncident(db: D1Database, id: number): Promise<IncidentWithUpdates | null> {
442 const incident = await db
443 .prepare("SELECT * FROM incidents WHERE id = ?")
444 .bind(id)
445 .first();
446 if (!incident) return null;
447 const updates = await db
448 .prepare("SELECT * FROM incident_updates WHERE incident_id = ? ORDER BY created_at ASC")
449 .bind(id)
450 .all();
451 return {
452 ...(incident as unknown as Incident),
453 updates: updates.results as unknown as IncidentUpdate[],
454 };
455}
456
457export async function getIncidentByGitHubIssue(
458 db: D1Database,
459 repo: string,
460 issueNumber: number,
461): Promise<Incident | null> {
462 const row = await db
463 .prepare("SELECT * FROM incidents WHERE github_repo = ? AND github_issue_number = ? LIMIT 1")
464 .bind(repo, issueNumber)
465 .first();
466 return (row as unknown as Incident) ?? null;
467}
468
469export async function setIncidentGitHub(
470 db: D1Database,
471 id: number,
472 repo: string,
473 issueNumber: number,
474): Promise<void> {
475 await db
476 .prepare("UPDATE incidents SET github_repo = ?, github_issue_number = ?, updated_at = ? WHERE id = ?")
477 .bind(repo, issueNumber, Math.floor(Date.now() / 1000), id)
478 .run();
479}
480
481export async function getRecentlyResolvedIncident(
482 db: D1Database,
483 service_id: string,
484 withinSeconds: number,
485): Promise<Incident | null> {
486 const since = Math.floor(Date.now() / 1000) - withinSeconds;
487 const row = await db
488 .prepare("SELECT * FROM incidents WHERE service_id = ? AND status = 'resolved' AND resolved_at >= ? ORDER BY resolved_at DESC LIMIT 1")
489 .bind(service_id, since)
490 .first();
491 return (row as unknown as Incident) ?? null;
492}
493
494export async function getRecentlyResolvedIncidents(
495 db: D1Database,
496 withinSeconds: number,
497): Promise<Incident[]> {
498 const since = Math.floor(Date.now() / 1000) - withinSeconds;
499 const rows = await db
500 .prepare("SELECT * FROM incidents WHERE status = 'resolved' AND resolved_at >= ? ORDER BY resolved_at DESC")
501 .bind(since)
502 .all();
503 return rows.results as unknown as Incident[];
504}