Open-source status page, incident response and uptime monitoring
TypeScript alertingincident-managementmcpmcp-servermonitoringmonitoring-as-codeobservabilityon-callopen-sourcesrestatus-pagestatuspagesynthetic-monitoringterraformtinybirdtursouptimeuptime-checkeruptime-monitor
import { and, count, db, eq, gte, inArray } from "@openstatus/db";
import {
notification,
notificationTrigger,
selectWorkspaceSchema,
workspace,
} from "@openstatus/db/src/schema";
import { withBusyRetry } from "@openstatus/services";
const THIRTY_DAYS_MS = 30 * 24 * 60 * 60 * 1000;
/**
* `cron_timestamp` is stored in milliseconds; comparing it against a
* seconds-resolution cutoff made the window unbounded, so every workspace that
* had ever passed its limit stayed blocked.
*
* Two round trips regardless of how many workspaces are asked about.
*/
export async function loadSmsQuotaBlocked(
workspaceIds: number[],
): Promise<Map<number, boolean>> {
const blocked = new Map<number, boolean>();
if (workspaceIds.length === 0) return blocked;
const [workspaceRows, notificationRows] = await withBusyRetry(() =>
db.batch([
db.select().from(workspace).where(inArray(workspace.id, workspaceIds)),
db
.select({ id: notification.id, workspaceId: notification.workspaceId })
.from(notification)
.where(
and(
inArray(notification.workspaceId, workspaceIds),
eq(notification.provider, "sms"),
),
),
]),
);
const notificationsByWorkspace = new Map<number, number[]>();
for (const row of notificationRows) {
if (row.workspaceId === null) continue;
const ids = notificationsByWorkspace.get(row.workspaceId) ?? [];
ids.push(row.id);
notificationsByWorkspace.set(row.workspaceId, ids);
}
const notificationIds = notificationRows.map((row) => row.id);
const sentRows =
notificationIds.length === 0
? []
: await withBusyRetry(() =>
db
.select({
notificationId: notificationTrigger.notificationId,
total: count(),
})
.from(notificationTrigger)
.where(
and(
inArray(notificationTrigger.notificationId, notificationIds),
gte(
notificationTrigger.cronTimestamp,
Date.now() - THIRTY_DAYS_MS,
),
),
)
.groupBy(notificationTrigger.notificationId)
.all(),
);
const sentByNotification = new Map<number, number>();
for (const row of sentRows) {
if (row.notificationId === null) continue;
sentByNotification.set(row.notificationId, row.total);
}
for (const row of workspaceRows) {
const parsed = selectWorkspaceSchema.safeParse(row);
if (!parsed.success) {
blocked.set(row.id, true);
continue;
}
const ids = notificationsByWorkspace.get(row.id) ?? [];
const sent = ids.reduce(
(total, id) => total + (sentByNotification.get(id) ?? 0),
0,
);
blocked.set(row.id, sent > parsed.data.limits["sms-limit"]);
}
return blocked;
}