Node.js Checkout Failure Alerts (Polling Postgres Metrics With Cost Attribution)

For a Node.js checkout worker, a failure count alert should come from Postgres metrics that preserve who incurred each API error and what the attempt cost. A global counter hides both.

Short answer: record one sanitized failure fact per checkout attempt, aggregate those facts in Postgres by tenant and error class, and let a Node.js cron worker alert only when a count and cost threshold are crossed inside the same polling window.

Keep the first version boring. One query. One decision function. One deduplication key. The hard part isn't sending a notification; it's making sure retries, overlapping polls, and missing attribution don't turn that notification into fiction.

Start with the data you refuse to collect

The obvious design is a single checkout_failures_total counter. It answers “is checkout failing?” but not “which account should own the investigation cost?” In a multi-tenant healthtech workflow, an alert that combines a validation rejection for one tenant with a database timeout for another creates a number that nobody can act on. It can also encourage a bad response: treating expected user-input failures like infrastructure failures. Cost attribution therefore changes the unit of observation. The useful row is not merely “an error happened.” It carries a stable tenant identifier, a bounded error class, the attempted operation, an integer cost in the smallest currency unit, and a timestamp. Patient data, free-form exception messages, card details, and request bodies don't belong in this metrics path. They increase cardinality, weaken grouping, and create data that an on-call engineer should not need to see. Event facts and alert state stay separate: Postgres owns the durable facts, while the worker owns a small cursor and the record of alerts already emitted. A dashboard can query the same facts, but the dashboard is not the alerting mechanism. If the browser has to stay open, the system is unfinished.

Use two thresholds rather than pretending every failure has equal operational weight: a minimum failure count and a minimum attributed cost. Requiring both is conservative. Using either is more sensitive. That choice is policy, so keep it outside the SQL and test it with fixed inputs.

No magic here.

How should a Node.js cron worker poll Postgres metrics for API failure alerts?

Start with half-open windows: include window_start, exclude window_end. That convention gives adjacent polls a clean boundary. The worker should receive both timestamps from its scheduler instead of asking the wall clock several times during a run. It should then group only bounded dimensions and sort the result so tests and logs remain predictable.

The query below assumes a narrow internal table named checkout_failure_fact. cost_minor is an integer chosen by the application; no exchange-rate or currency conversion is implied. tenant_id should be an internal pseudonymous identifier, while error_class should come from a small application-owned set such as validation, dependency, or database.

type FailureMetric = {
  tenantId: string;
  errorClass: "validation" | "dependency" | "database";
  failureCount: number;
  attributedCostMinor: number;
};

interface Db {
  query<T>(sql: string, parameters: readonly unknown[]): Promise<readonly T[]>;
}

const failureMetricsSql = `
  SELECT
    tenant_id AS "tenantId",
    error_class AS "errorClass",
    COUNT(*)::int AS "failureCount",
    COALESCE(SUM(cost_minor), 0)::int AS "attributedCostMinor"
  FROM checkout_failure_fact
  WHERE occurred_at >= $1
    AND occurred_at < $2
  GROUP BY tenant_id, error_class
  ORDER BY tenant_id, error_class
`;

async function pollFailureMetrics(
  db: Db,
  windowStart: Date,
  windowEnd: Date,
): Promise<readonly FailureMetric[]> {
  return db.query<FailureMetric>(failureMetricsSql, [windowStart, windowEnd]);
}

There is a subtle failure mode in that tiny query. A retry may create a second fact for the same checkout attempt, which means the aggregate counts attempts rather than failed checkouts. Sometimes that is exactly the desired metric because every attempt consumes capacity or incurs cost. Sometimes it is wrong. Decide explicitly, then either retain attempt-level facts or enforce a unique attempt identifier at ingestion. Don't “fix” duplication in the poller with a guessed time range.

Polling frequency is another trade. A one-minute schedule gives quicker detection than a five-minute schedule, but it performs five times as many queries and makes sparse windows more common. There is no honest universal interval. The checkout's response objective, normal traffic, and acceptable database load determine it; a replay against representative, sanitized data is what resolves the uncertainty.

One timestamp pair controls the entire run

Keep notification delivery behind a generic interface. The decision stays pure, while the scheduler, database driver, and destination remain replaceable. That is the DX test I care about: can the behavior be exercised without booting an entire observability stack?

type Threshold = {
  minimumFailures: number;
  minimumCostMinor: number;
};

type Alert = FailureMetric & {
  windowStart: string;
  windowEnd: string;
  deduplicationKey: string;
};

interface AlertSink {
  hasSent(key: string): Promise<boolean>;
  send(alert: Alert): Promise<void>;
  markSent(key: string): Promise<void>;
}

function crossesThreshold(metric: FailureMetric, threshold: Threshold): boolean {
  return metric.failureCount >= threshold.minimumFailures
    && metric.attributedCostMinor >= threshold.minimumCostMinor;
}

function makeDeduplicationKey(
  metric: FailureMetric,
  windowStart: Date,
  windowEnd: Date,
): string {
  return [
    "checkout-failure",
    metric.tenantId,
    metric.errorClass,
    windowStart.toISOString(),
    windowEnd.toISOString(),
  ].join(":");
}

async function runFailureAlertPoll(
  db: Db,
  sink: AlertSink,
  threshold: Threshold,
  windowStart: Date,
  windowEnd: Date,
): Promise<void> {
  const metrics = await pollFailureMetrics(db, windowStart, windowEnd);

  for (const metric of metrics) {
    if (!crossesThreshold(metric, threshold)) continue;

    const deduplicationKey = makeDeduplicationKey(
      metric,
      windowStart,
      windowEnd,
    );
    if (await sink.hasSent(deduplicationKey)) continue;

    await sink.send({
      ...metric,
      windowStart: windowStart.toISOString(),
      windowEnd: windowEnd.toISOString(),
      deduplicationKey,
    });
    await sink.markSent(deduplicationKey);
  }
}

The sink needs durable deduplication. An in-memory set passes a demo and fails as soon as the process restarts. A database record keyed by the deduplication key is the simple option, although the send and the record cannot be made atomic unless the destination participates in the same transaction. Delivery is therefore normally at least once. The receiver should accept the key and collapse duplicates too.

Duplicates count.

A duplicate alert is a state transition

Test the awkward boundaries. A metric at minimumFailures - 1 must not alert. A metric exactly at both thresholds must alert. Two executions for the same window must result in one logical notification. A failure exactly at windowEnd belongs to the next window. Validation and database errors for the same tenant must stay separate. These tests are small, fast, and more valuable than a large snapshot of formatted alert text. Add one ingestion test for the chosen attempt identity as well: either two retry attempts create two billable facts by policy, or the table rejects the second occurrence of the same attempt identifier. Leaving that behavior accidental poisons both the failure count and its attributed cost.

For operational logs, use consistent severity meanings instead of inventing a private scale. RFC 5424 defines numeric severity values, including Error and Critical, but severity still should describe the condition rather than the destination of a message. A threshold crossing may be an error signal; reserve a critical signal for a policy that genuinely requires immediate action. Log the window, tenant identifier, error class, counts, cost total, and deduplication key. Leave sensitive checkout payloads out.

When polling has to graduate

The direct Postgres query is suitable when failure volume is moderate, the facts already live there, and a delayed alert on the cron interval is acceptable. It is not suitable when detection must happen in seconds, the aggregation query competes with checkout traffic, or the event rate makes repeated range scans expensive. In those cases, publish sanitized failure facts to a stream, maintain windowed aggregates in a separate metrics system, and retain Postgres as an audit source rather than the hot alert query path.

The catch is extra machinery. A streaming path adds consumer lag, partitioning choices, replay behavior, and another place to preserve tenant attribution. That may be justified, but it is config bloat until measurements prove the polling query is a problem. Benchmark the actual grouped query with representative row counts and indexes, record its latency and rows scanned, then choose. I'm not sure what the right crossover point is for an unknown workload; nobody can derive it from a framework name.

I would also separate policy rollout from code deployment. A feature toggle can enable the new alert decision for a controlled tenant set while the old observation path remains available. Fowler's feature-toggle guidance is useful here because it treats toggle decisions as a design concern, not scattered conditionals. Keep the toggle short-lived, give it an owner, and test both states. Otherwise a temporary safety switch becomes permanent configuration debt.

Thresholds eventually need traffic context. Ten failures in ten requests and ten failures in a million requests are different conditions, so a later version may add a denominator and alert on both an absolute floor and a failure ratio. Cost attribution should remain a first-class dimension rather than a label added after aggregation; once tenant identity is discarded, no dashboard query can reconstruct it honestly.

The options differ in operational ownership, not in the event fields they need:

Path	Use it when	The catch
Postgres polling	The cron delay is acceptable and the measured query stays within budget	Range scans share capacity with application work
Streamed aggregation	Seconds matter or repeated scans are measured bottlenecks	Consumers, replay, and lag become on-call concerns
Dedicated metrics service	The team should not own retention and alert delivery	Tenant and cost dimensions still need deliberate controls

Stick with polling when it meets the response objective and the database benchmark stays inside its budget. Move to streaming when measured alert delay or query load breaks that objective. Choose a managed metrics backend when operating storage, retention, and alert delivery is outside the team's remit. Choose a self-hosted path when control over deployment and data location outweighs that operational work. None of those choices repairs poor event semantics.

Ship the cron worker only after a replay test proves three things: each failed attempt has the intended identity, adjacent windows neither overlap nor leave a gap, and the same window can be processed twice without two logical alerts. Then watch query latency, scanned rows, alert delay, duplicate delivery, and unattributed cost as separate signals.

The threshold is the easy line of code. Trustworthy attribution is the system.

References

• https://datatracker.ietf.org/doc/html/rfc5424
• https://martinfowler.com/articles/feature-toggles.html