371 lines
12 KiB
TypeScript
371 lines
12 KiB
TypeScript
import { d1BlobToUint8Array } from "./crypto";
|
||
import type { Protocol } from "./probe";
|
||
|
||
const THIRTY_DAYS_SEC = 30 * 24 * 60 * 60;
|
||
|
||
export async function pruneProbeEvents(db: D1Database): Promise<void> {
|
||
const cutoff = Math.floor(Date.now() / 1000) - THIRTY_DAYS_SEC;
|
||
await db.prepare("DELETE FROM probe_events WHERE ts < ?").bind(cutoff).run();
|
||
}
|
||
|
||
export type AppSettingsRow = {
|
||
id: number;
|
||
probe_prompts: string;
|
||
probe_interval_minutes: number;
|
||
};
|
||
|
||
export async function getAppSettings(db: D1Database): Promise<AppSettingsRow> {
|
||
const r = await db
|
||
.prepare("SELECT id, probe_prompts, probe_interval_minutes FROM app_settings WHERE id = 1")
|
||
.first<AppSettingsRow>();
|
||
if (r) return r;
|
||
await db
|
||
.prepare("INSERT INTO app_settings (id, probe_prompts, probe_interval_minutes) VALUES (1, '', 5)")
|
||
.run();
|
||
return { id: 1, probe_prompts: "", probe_interval_minutes: 5 };
|
||
}
|
||
|
||
export async function updateAppSettings(
|
||
db: D1Database,
|
||
patch: Partial<Pick<AppSettingsRow, "probe_prompts" | "probe_interval_minutes">>
|
||
): Promise<void> {
|
||
const cur = await getAppSettings(db);
|
||
const next = {
|
||
probe_prompts: patch.probe_prompts !== undefined ? patch.probe_prompts : cur.probe_prompts,
|
||
probe_interval_minutes:
|
||
patch.probe_interval_minutes !== undefined ? patch.probe_interval_minutes : cur.probe_interval_minutes,
|
||
};
|
||
await db
|
||
.prepare("UPDATE app_settings SET probe_prompts = ?, probe_interval_minutes = ? WHERE id = 1")
|
||
.bind(next.probe_prompts, next.probe_interval_minutes)
|
||
.run();
|
||
}
|
||
|
||
/** 库中存证的监控行（不含全站探测间隔，间隔在 app_settings） */
|
||
export type MonitorRow = {
|
||
id: string;
|
||
display_name: string;
|
||
api_base_url: string;
|
||
model: string;
|
||
protocol: Protocol;
|
||
enabled: number;
|
||
category: string;
|
||
created_at: number;
|
||
api_key_ciphertext: ArrayBuffer;
|
||
api_key_nonce: ArrayBuffer;
|
||
last_run_at: number | null;
|
||
next_run_at: number;
|
||
/** 1 = 流式；0 = 非流式 */
|
||
probe_stream: number;
|
||
/** 1 = 首页卡片展示；0 = 仅后台可见 */
|
||
show_on_dashboard: number;
|
||
};
|
||
|
||
/** 列表 API 在合并 interval_minutes 之前的行 */
|
||
export type MonitorPublic = {
|
||
id: string;
|
||
display_name: string;
|
||
api_base_url: string;
|
||
model: string;
|
||
protocol: Protocol;
|
||
enabled: number;
|
||
category: string;
|
||
created_at: number;
|
||
last_run_at: number | null;
|
||
next_run_at: number;
|
||
probe_stream: number;
|
||
show_on_dashboard: number;
|
||
};
|
||
|
||
export async function listMonitorsPublic(
|
||
db: D1Database,
|
||
visibility: "all" | "dashboard" = "all"
|
||
): Promise<MonitorPublic[]> {
|
||
const where = visibility === "dashboard" ? " WHERE show_on_dashboard = 1" : "";
|
||
const r = await db
|
||
.prepare(
|
||
`SELECT id, display_name, api_base_url, model, protocol, enabled, category,
|
||
created_at, last_run_at, next_run_at, probe_stream, show_on_dashboard
|
||
FROM monitors${where} ORDER BY display_name ASC`
|
||
)
|
||
.all();
|
||
return (r.results ?? []) as unknown as MonitorPublic[];
|
||
}
|
||
|
||
export async function listAllMonitors(db: D1Database): Promise<MonitorRow[]> {
|
||
const r = await db
|
||
.prepare(
|
||
`SELECT id, display_name, api_base_url, model, protocol, enabled, category,
|
||
created_at, api_key_ciphertext, api_key_nonce, last_run_at, next_run_at, probe_stream,
|
||
show_on_dashboard
|
||
FROM monitors ORDER BY display_name ASC`
|
||
)
|
||
.all();
|
||
return (r.results ?? []) as unknown as MonitorRow[];
|
||
}
|
||
|
||
export async function listDueMonitors(db: D1Database, nowSec: number): Promise<MonitorRow[]> {
|
||
const r = await db
|
||
.prepare(
|
||
`SELECT id, display_name, api_base_url, model, protocol, enabled, category,
|
||
created_at, api_key_ciphertext, api_key_nonce, last_run_at, next_run_at, probe_stream,
|
||
show_on_dashboard
|
||
FROM monitors WHERE enabled = 1 AND next_run_at <= ? ORDER BY next_run_at ASC`
|
||
)
|
||
.bind(nowSec)
|
||
.all();
|
||
return (r.results ?? []) as unknown as MonitorRow[];
|
||
}
|
||
|
||
export async function getMonitor(db: D1Database, id: string): Promise<MonitorRow | null> {
|
||
const r = await db
|
||
.prepare(
|
||
`SELECT id, display_name, api_base_url, model, protocol, enabled, category,
|
||
created_at, api_key_ciphertext, api_key_nonce, last_run_at, next_run_at, probe_stream,
|
||
show_on_dashboard
|
||
FROM monitors WHERE id = ?`
|
||
)
|
||
.bind(id)
|
||
.first();
|
||
return (r as unknown as MonitorRow) ?? null;
|
||
}
|
||
|
||
export async function insertMonitor(
|
||
db: D1Database,
|
||
row: Omit<MonitorRow, "last_run_at"> & { last_run_at: number | null }
|
||
): Promise<void> {
|
||
const ct = d1BlobToUint8Array(row.api_key_ciphertext as unknown);
|
||
const nn = d1BlobToUint8Array(row.api_key_nonce as unknown);
|
||
await db
|
||
.prepare(
|
||
`INSERT INTO monitors (id, display_name, api_base_url, model, protocol, enabled, category,
|
||
created_at, api_key_ciphertext, api_key_nonce, last_run_at, next_run_at, probe_stream,
|
||
show_on_dashboard)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
|
||
)
|
||
.bind(
|
||
row.id,
|
||
row.display_name,
|
||
row.api_base_url,
|
||
row.model,
|
||
row.protocol,
|
||
row.enabled,
|
||
row.category,
|
||
row.created_at,
|
||
ct,
|
||
nn,
|
||
row.last_run_at,
|
||
row.next_run_at,
|
||
row.probe_stream,
|
||
row.show_on_dashboard
|
||
)
|
||
.run();
|
||
}
|
||
|
||
export async function updateMonitorMeta(
|
||
db: D1Database,
|
||
id: string,
|
||
patch: Partial<{
|
||
display_name: string;
|
||
api_base_url: string;
|
||
model: string;
|
||
protocol: Protocol;
|
||
category: string;
|
||
enabled: number;
|
||
api_key_ciphertext: ArrayBuffer;
|
||
api_key_nonce: ArrayBuffer;
|
||
next_run_at: number;
|
||
probe_stream: number;
|
||
show_on_dashboard: number;
|
||
}>
|
||
): Promise<void> {
|
||
const cur = await getMonitor(db, id);
|
||
if (!cur) return;
|
||
const next = { ...cur, ...patch };
|
||
const ct = d1BlobToUint8Array(next.api_key_ciphertext as unknown);
|
||
const nn = d1BlobToUint8Array(next.api_key_nonce as unknown);
|
||
await db
|
||
.prepare(
|
||
`UPDATE monitors SET display_name=?, api_base_url=?, model=?, protocol=?,
|
||
enabled=?, category=?, api_key_ciphertext=?, api_key_nonce=?, next_run_at=?, probe_stream=?,
|
||
show_on_dashboard=?
|
||
WHERE id=?`
|
||
)
|
||
.bind(
|
||
next.display_name,
|
||
next.api_base_url,
|
||
next.model,
|
||
next.protocol,
|
||
next.enabled,
|
||
next.category,
|
||
ct,
|
||
nn,
|
||
next.next_run_at,
|
||
next.probe_stream,
|
||
next.show_on_dashboard,
|
||
id
|
||
)
|
||
.run();
|
||
}
|
||
|
||
export async function deleteMonitor(db: D1Database, id: string): Promise<void> {
|
||
await db.prepare("DELETE FROM probe_events WHERE monitor_id = ?").bind(id).run();
|
||
await db.prepare("DELETE FROM monitors WHERE id = ?").bind(id).run();
|
||
}
|
||
|
||
export async function insertProbeEvent(
|
||
db: D1Database,
|
||
e: {
|
||
monitor_id: string;
|
||
ts: number;
|
||
ok: number;
|
||
first_token_ms: number | null;
|
||
http_status: number | null;
|
||
error_message: string | null;
|
||
probe_input: string | null;
|
||
probe_output: string | null;
|
||
}
|
||
): Promise<void> {
|
||
await db
|
||
.prepare(
|
||
`INSERT INTO probe_events (monitor_id, ts, ok, first_token_ms, http_status, error_message, probe_input, probe_output)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`
|
||
)
|
||
.bind(
|
||
e.monitor_id,
|
||
e.ts,
|
||
e.ok,
|
||
e.first_token_ms,
|
||
e.http_status,
|
||
e.error_message,
|
||
e.probe_input,
|
||
e.probe_output
|
||
)
|
||
.run();
|
||
}
|
||
|
||
export async function updateMonitorRunTimes(
|
||
db: D1Database,
|
||
id: string,
|
||
last_run_at: number,
|
||
next_run_at: number
|
||
): Promise<void> {
|
||
await db
|
||
.prepare("UPDATE monitors SET last_run_at = ?, next_run_at = ? WHERE id = ?")
|
||
.bind(last_run_at, next_run_at, id)
|
||
.run();
|
||
}
|
||
|
||
export type AvailabilityTimelineStats = {
|
||
availability24h: number | null;
|
||
availability30d: number | null;
|
||
probe_count: number;
|
||
daily: Array<{ dayStart: number; ok: number; total: number }>;
|
||
lastProbe: {
|
||
ts: number;
|
||
ok: number;
|
||
first_token_ms: number | null;
|
||
http_status: number | null;
|
||
error_message: string | null;
|
||
probe_input: string | null;
|
||
probe_output: string | null;
|
||
} | null;
|
||
};
|
||
|
||
type LastProbeRow = NonNullable<AvailabilityTimelineStats["lastProbe"]>;
|
||
|
||
function finalizeAvailabilityTimeline(
|
||
lastProbe: LastProbeRow | null | undefined,
|
||
stats24: { okc: number | null; total: number | null } | null | undefined,
|
||
stats30: { okc: number | null; total: number | null } | null | undefined,
|
||
dailyRaw: unknown[],
|
||
countRow: { c: number | null } | null | undefined
|
||
): AvailabilityTimelineStats {
|
||
const daily = dailyRaw.map((r) => ({
|
||
dayStart: Number((r as { day_start: number }).day_start),
|
||
ok: Number((r as { okc: number | null }).okc ?? 0),
|
||
total: Number((r as { cnt: number | null }).cnt ?? 0),
|
||
}));
|
||
|
||
const availability24h =
|
||
stats24?.total && stats24.total > 0 ? (Number(stats24.okc ?? 0) / Number(stats24.total)) * 100 : null;
|
||
const availability30d =
|
||
stats30?.total && stats30.total > 0 ? (Number(stats30.okc ?? 0) / Number(stats30.total)) * 100 : null;
|
||
|
||
return {
|
||
availability24h,
|
||
availability30d,
|
||
probe_count: Number(countRow?.c ?? 0),
|
||
daily,
|
||
lastProbe: lastProbe ?? null,
|
||
};
|
||
}
|
||
|
||
/** 与 SQL bind / Map 查找统一，避免首尾空白或类型不一致导致 stats 对不上 */
|
||
export function normMonitorId(id: unknown): string {
|
||
return String(id ?? "").trim();
|
||
}
|
||
|
||
/** 每个监控单独 batch（5 条语句），多监控并发；避免单次 mega-batch 下结果与语句顺序偶发错位 */
|
||
const AV_STATS_PARALLEL_MONITORS = 12;
|
||
|
||
/**
|
||
* 批量拉取多个监控的可用率与时间线（每位监控 1 次 `db.batch`，含 5 条 SELECT）。
|
||
*/
|
||
export async function availabilityAndTimelineBatch(
|
||
db: D1Database,
|
||
monitorIds: string[]
|
||
): Promise<Map<string, AvailabilityTimelineStats>> {
|
||
const out = new Map<string, AvailabilityTimelineStats>();
|
||
const unique = [...new Set(monitorIds.map(normMonitorId).filter((id) => id.length > 0))];
|
||
if (unique.length === 0) return out;
|
||
|
||
const now = Math.floor(Date.now() / 1000);
|
||
const start24 = now - 86400;
|
||
const start30 = now - THIRTY_DAYS_SEC;
|
||
|
||
const sqlLast = `SELECT ts, ok, first_token_ms, http_status, error_message, probe_input, probe_output FROM probe_events WHERE monitor_id = ? ORDER BY ts DESC LIMIT 1`;
|
||
const sqlRange = `SELECT SUM(ok) AS okc, COUNT(*) AS total FROM probe_events WHERE monitor_id = ? AND ts >= ?`;
|
||
const sqlDaily = `SELECT (CAST((ts + 28800) / 43200 AS INTEGER) * 43200 - 28800) AS day_start,
|
||
SUM(ok) AS okc,
|
||
COUNT(*) AS cnt
|
||
FROM probe_events WHERE monitor_id = ? AND ts >= ?
|
||
GROUP BY day_start ORDER BY day_start ASC`;
|
||
const sqlCount = `SELECT COUNT(*) AS c FROM probe_events WHERE monitor_id = ?`;
|
||
|
||
async function fetchOne(monitorId: string): Promise<void> {
|
||
const batchResults = await db.batch([
|
||
db.prepare(sqlLast).bind(monitorId),
|
||
db.prepare(sqlRange).bind(monitorId, start24),
|
||
db.prepare(sqlRange).bind(monitorId, start30),
|
||
db.prepare(sqlDaily).bind(monitorId, start30),
|
||
db.prepare(sqlCount).bind(monitorId),
|
||
]);
|
||
|
||
const lastProbe = batchResults[0].results?.[0] as LastProbeRow | undefined;
|
||
const stats24 = batchResults[1].results?.[0] as { okc: number | null; total: number | null } | undefined;
|
||
const stats30 = batchResults[2].results?.[0] as { okc: number | null; total: number | null } | undefined;
|
||
const dailyRaw = batchResults[3].results ?? [];
|
||
const countRow = batchResults[4].results?.[0] as { c: number | null } | undefined;
|
||
|
||
out.set(monitorId, finalizeAvailabilityTimeline(lastProbe, stats24, stats30, dailyRaw, countRow));
|
||
}
|
||
|
||
for (let off = 0; off < unique.length; off += AV_STATS_PARALLEL_MONITORS) {
|
||
const slice = unique.slice(off, off + AV_STATS_PARALLEL_MONITORS);
|
||
await Promise.all(slice.map((id) => fetchOne(id)));
|
||
}
|
||
|
||
return out;
|
||
}
|
||
|
||
/** Timeline bucket: half-day slots aligned to Asia/Shanghai 00:00 and 12:00. */
|
||
export async function availabilityAndTimeline(
|
||
db: D1Database,
|
||
monitorId: string
|
||
): Promise<AvailabilityTimelineStats> {
|
||
const id = normMonitorId(monitorId);
|
||
const m = await availabilityAndTimelineBatch(db, [id]);
|
||
return m.get(id) ?? finalizeAvailabilityTimeline(null, null, null, [], null);
|
||
}
|