269 lines
9.2 KiB
TypeScript
269 lines
9.2 KiB
TypeScript
export const BETA_OPERATIONS_WINDOW_DAYS = 30;
|
|
|
|
export const BETA_ACCOUNTS_SQL = `
|
|
SELECT
|
|
COUNT(*) AS total,
|
|
SUM(CASE WHEN created_at >= ? THEN 1 ELSE 0 END) AS new_accounts,
|
|
SUM(CASE WHEN EXISTS (
|
|
SELECT 1 FROM hosts
|
|
WHERE hosts.account_id = accounts.id AND hosts.lifecycle = 'active'
|
|
) THEN 1 ELSE 0 END) AS with_active_host
|
|
FROM accounts`;
|
|
|
|
export const BETA_PAIRINGS_SQL = `
|
|
SELECT
|
|
COUNT(*) AS created,
|
|
SUM(CASE WHEN status = 'claimed' THEN 1 ELSE 0 END) AS claimed,
|
|
SUM(CASE WHEN status = 'waiting' AND expires_at > ? THEN 1 ELSE 0 END) AS waiting,
|
|
SUM(CASE WHEN status = 'waiting' AND expires_at <= ? THEN 1 ELSE 0 END) AS expired,
|
|
SUM(CASE
|
|
WHEN status = 'waiting' AND (expires_at <= ? OR locked_at IS NOT NULL)
|
|
THEN 1 ELSE 0
|
|
END) AS attention_required,
|
|
SUM(CASE WHEN locked_at IS NOT NULL THEN 1 ELSE 0 END) AS locked,
|
|
AVG(CASE
|
|
WHEN status = 'claimed' AND claimed_at IS NOT NULL
|
|
THEN (julianday(claimed_at) - julianday(created_at)) * 86400
|
|
END) AS average_claim_seconds
|
|
FROM pairing_requests
|
|
WHERE created_at >= ?`;
|
|
|
|
export const BETA_CLAIM_ATTEMPTS_SQL = `
|
|
SELECT
|
|
SUM(CASE WHEN outcome = 'rejected' THEN 1 ELSE 0 END) AS rejected,
|
|
SUM(CASE WHEN outcome = 'rate_limited' THEN 1 ELSE 0 END) AS rate_limited
|
|
FROM pairing_claim_attempts
|
|
WHERE created_at >= ?`;
|
|
|
|
export const BETA_PROVISIONING_SQL = `
|
|
SELECT
|
|
COUNT(*) AS created,
|
|
SUM(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS succeeded,
|
|
SUM(CASE WHEN state = 'failed' THEN 1 ELSE 0 END) AS failed,
|
|
SUM(CASE WHEN state IN ('provisioning', 'quiescing', 'copying', 'switching', 'draining') THEN 1 ELSE 0 END) AS in_progress,
|
|
AVG(CASE
|
|
WHEN state = 'active'
|
|
THEN (julianday(updated_at) - julianday(created_at)) * 86400
|
|
END) AS average_completion_seconds
|
|
FROM tenant_placements
|
|
WHERE created_at >= ?`;
|
|
|
|
export const BETA_SUPPORT_SQL = `
|
|
SELECT
|
|
COUNT(*) AS created,
|
|
SUM(CASE WHEN category = 'connection_issue' THEN 1 ELSE 0 END) AS connection_issues,
|
|
SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open,
|
|
SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) AS resolved,
|
|
AVG(CASE
|
|
WHEN status = 'resolved' AND resolved_at IS NOT NULL
|
|
THEN (julianday(resolved_at) - julianday(created_at)) * 86400
|
|
END) AS average_resolution_seconds
|
|
FROM beta_feedback
|
|
WHERE created_at >= ?`;
|
|
|
|
export const BETA_ACCESS_REQUESTS_SQL = `
|
|
SELECT
|
|
COUNT(*) AS submitted,
|
|
SUM(requested_slots) AS requested_slot_demand,
|
|
AVG(requested_slots) AS average_requested_slots,
|
|
SUM(CASE WHEN preferred_os = 'windows' THEN 1 ELSE 0 END) AS windows,
|
|
SUM(CASE WHEN preferred_os = 'linux' THEN 1 ELSE 0 END) AS linux,
|
|
SUM(CASE WHEN preferred_os = 'both' THEN 1 ELSE 0 END) AS both,
|
|
SUM(CASE WHEN status = 'requested' THEN 1 ELSE 0 END) AS pending,
|
|
SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved,
|
|
SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) AS declined,
|
|
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
|
|
SUM(CASE
|
|
WHEN status = 'approved' AND resolved_at IS NOT NULL AND EXISTS (
|
|
SELECT 1 FROM hosts
|
|
WHERE hosts.account_id = beta_access_requests.account_id
|
|
AND hosts.claimed_at >= beta_access_requests.resolved_at
|
|
) THEN 1 ELSE 0
|
|
END) AS approved_with_post_approval_claim,
|
|
AVG(CASE
|
|
WHEN status IN ('approved', 'declined') AND resolved_at IS NOT NULL
|
|
THEN (julianday(resolved_at) - julianday(requested_at)) * 86400
|
|
END) AS average_review_seconds
|
|
FROM beta_access_requests
|
|
WHERE requested_at >= ?`;
|
|
|
|
type AggregateRow = Record<string, number | null>;
|
|
|
|
export type BetaOperationsSnapshot = {
|
|
generatedAt: string;
|
|
windowDays: number;
|
|
accounts: {
|
|
total: number;
|
|
newAccounts: number;
|
|
withActiveHost: number;
|
|
};
|
|
pairings: {
|
|
created: number;
|
|
claimed: number;
|
|
waiting: number;
|
|
expired: number;
|
|
attentionRequired: number;
|
|
locked: number;
|
|
rejectedAttempts: number;
|
|
rateLimitedAttempts: number;
|
|
claimRatePercent: number | null;
|
|
averageClaimSeconds: number | null;
|
|
};
|
|
provisioning: {
|
|
created: number;
|
|
succeeded: number;
|
|
failed: number;
|
|
inProgress: number;
|
|
successRatePercent: number | null;
|
|
averageCompletionSeconds: number | null;
|
|
};
|
|
support: {
|
|
created: number;
|
|
connectionIssues: number;
|
|
open: number;
|
|
resolved: number;
|
|
resolutionRatePercent: number | null;
|
|
averageResolutionSeconds: number | null;
|
|
};
|
|
accessRequests: {
|
|
submitted: number;
|
|
requestedSlotDemand: number;
|
|
averageRequestedSlots: number | null;
|
|
windows: number;
|
|
linux: number;
|
|
both: number;
|
|
pending: number;
|
|
approved: number;
|
|
declined: number;
|
|
cancelled: number;
|
|
decided: number;
|
|
approvalRatePercent: number | null;
|
|
averageReviewSeconds: number | null;
|
|
approvedWithPostApprovalClaim: number;
|
|
postApprovalClaimRatePercent: number | null;
|
|
};
|
|
unavailable: readonly [
|
|
"relay_reconnect_rate",
|
|
"relay_latency",
|
|
"runtime_resource_cost",
|
|
"support_effort",
|
|
];
|
|
};
|
|
|
|
function count(row: AggregateRow, key: string): number {
|
|
const value = Number(row[key] ?? 0);
|
|
return Number.isFinite(value) && value > 0 ? Math.floor(value) : 0;
|
|
}
|
|
|
|
function duration(row: AggregateRow, key: string): number | null {
|
|
const value = Number(row[key]);
|
|
return Number.isFinite(value) && value >= 0 ? Math.round(value) : null;
|
|
}
|
|
|
|
function average(row: AggregateRow, key: string): number | null {
|
|
const value = Number(row[key]);
|
|
return Number.isFinite(value) && value >= 0
|
|
? Math.round(value * 10) / 10
|
|
: null;
|
|
}
|
|
|
|
function percent(numerator: number, denominator: number): number | null {
|
|
if (denominator <= 0) return null;
|
|
return Math.round((numerator / denominator) * 1000) / 10;
|
|
}
|
|
|
|
export function deriveBetaOperationsSnapshot(input: {
|
|
generatedAt: string;
|
|
accounts: AggregateRow;
|
|
pairings: AggregateRow;
|
|
claimAttempts: AggregateRow;
|
|
provisioning: AggregateRow;
|
|
support: AggregateRow;
|
|
accessRequests: AggregateRow;
|
|
}): BetaOperationsSnapshot {
|
|
const pairingCreated = count(input.pairings, "created");
|
|
const pairingClaimed = count(input.pairings, "claimed");
|
|
const provisioningCreated = count(input.provisioning, "created");
|
|
const provisioningSucceeded = count(input.provisioning, "succeeded");
|
|
const supportCreated = count(input.support, "created");
|
|
const supportResolved = count(input.support, "resolved");
|
|
const accessSubmitted = count(input.accessRequests, "submitted");
|
|
const accessApproved = count(input.accessRequests, "approved");
|
|
const accessDeclined = count(input.accessRequests, "declined");
|
|
const accessDecided = accessApproved + accessDeclined;
|
|
const approvedWithPostApprovalClaim = count(
|
|
input.accessRequests,
|
|
"approved_with_post_approval_claim",
|
|
);
|
|
|
|
return {
|
|
generatedAt: input.generatedAt,
|
|
windowDays: BETA_OPERATIONS_WINDOW_DAYS,
|
|
accounts: {
|
|
total: count(input.accounts, "total"),
|
|
newAccounts: count(input.accounts, "new_accounts"),
|
|
withActiveHost: count(input.accounts, "with_active_host"),
|
|
},
|
|
pairings: {
|
|
created: pairingCreated,
|
|
claimed: pairingClaimed,
|
|
waiting: count(input.pairings, "waiting"),
|
|
expired: count(input.pairings, "expired"),
|
|
attentionRequired: count(input.pairings, "attention_required"),
|
|
locked: count(input.pairings, "locked"),
|
|
rejectedAttempts: count(input.claimAttempts, "rejected"),
|
|
rateLimitedAttempts: count(input.claimAttempts, "rate_limited"),
|
|
claimRatePercent: percent(pairingClaimed, pairingCreated),
|
|
averageClaimSeconds: duration(input.pairings, "average_claim_seconds"),
|
|
},
|
|
provisioning: {
|
|
created: provisioningCreated,
|
|
succeeded: provisioningSucceeded,
|
|
failed: count(input.provisioning, "failed"),
|
|
inProgress: count(input.provisioning, "in_progress"),
|
|
successRatePercent: percent(provisioningSucceeded, provisioningCreated),
|
|
averageCompletionSeconds: duration(
|
|
input.provisioning,
|
|
"average_completion_seconds",
|
|
),
|
|
},
|
|
support: {
|
|
created: supportCreated,
|
|
connectionIssues: count(input.support, "connection_issues"),
|
|
open: count(input.support, "open"),
|
|
resolved: supportResolved,
|
|
resolutionRatePercent: percent(supportResolved, supportCreated),
|
|
averageResolutionSeconds: duration(
|
|
input.support,
|
|
"average_resolution_seconds",
|
|
),
|
|
},
|
|
accessRequests: {
|
|
submitted: accessSubmitted,
|
|
requestedSlotDemand: count(input.accessRequests, "requested_slot_demand"),
|
|
averageRequestedSlots: average(input.accessRequests, "average_requested_slots"),
|
|
windows: count(input.accessRequests, "windows"),
|
|
linux: count(input.accessRequests, "linux"),
|
|
both: count(input.accessRequests, "both"),
|
|
pending: count(input.accessRequests, "pending"),
|
|
approved: accessApproved,
|
|
declined: accessDeclined,
|
|
cancelled: count(input.accessRequests, "cancelled"),
|
|
decided: accessDecided,
|
|
approvalRatePercent: percent(accessApproved, accessDecided),
|
|
averageReviewSeconds: duration(input.accessRequests, "average_review_seconds"),
|
|
approvedWithPostApprovalClaim,
|
|
postApprovalClaimRatePercent: percent(
|
|
approvedWithPostApprovalClaim,
|
|
accessApproved,
|
|
),
|
|
},
|
|
unavailable: [
|
|
"relay_reconnect_rate",
|
|
"relay_latency",
|
|
"runtime_resource_cost",
|
|
"support_effort",
|
|
],
|
|
};
|
|
}
|