Workflow Monitoring Software Development

Persistent, immutable log of every workflow execution stored in a PostgreSQL workflow_executions table: execution_id (UUID v4), workflow_id, trigger_source (webhook/schedule/manual), trigger_payload (JSONB with configurable sensitive-field masking), status (pending/running/completed/failed/timed_out), started_at, completed_at, and duration_ms. A corresponding workflow_execution_steps table records every step: step_id, execution_id (FK), step_name, step_type (action/condition/transform/wait), input_payload (JSONB), output_payload (JSONB), error_message, started_at, completed_at, duration_ms, and external_request_id for correlating with downstream system logs. The workflow_executions table is granted no UPDATE or DELETE permissions on the application database role -- immutability enforced at the database layer, not by convention.

Payload storage: JSONB columns hold complete step I/O up to 1MB per field without truncation. Payloads exceeding 1MB (bulk record updates, large document processing) are written to S3, with the JSONB column storing the S3 URI reference. Step payloads are compressed with zstd at the application layer before storage -- text-heavy JSON payloads typically compress 4-8x. Retention policy: hot storage in PostgreSQL for 90 days; automated S3 lifecycle rule moves executions older than 90 days to S3 Standard-IA; executions older than 365 days archive to S3 Glacier Instant Retrieval. Retention periods are configurable per workflow based on the regulated retention requirement of the underlying business process.

Search and investigation: execution records indexed on (workflow_id, created_at DESC) for time-range queries, (trigger_source, status) for filtered operational queries, and a GIN index on trigger_payload for finding executions by embedded field values -- customer ID, order number, invoice reference -- without full table scans. For free-text search across payload content, step input/output payloads are streamed to Elasticsearch 8.x or OpenSearch 2.x via a change-data-capture pipeline; the relational store handles operational queries and the search index handles investigation queries.

OpenTelemetry trace format: every execution carries a trace_id (W3C Trace Context 16-byte hex), span_id per step, and parent_span_id linking each step to its predecessor. Traces are exported to Jaeger, Zipkin, Datadog APM, or any OTel-compatible backend so workflow execution traces appear alongside application traces in a single observability platform. The full execution trace for any run is reconstructable from stored spans without a live tracing backend -- the data exists in the database even if the tracing backend is unavailable. The investigation UI renders each step as a horizontal timeline bar proportional to its duration, with status colour-coding (green/amber/red) and inline error annotations so the bottleneck step is visible in 3 seconds rather than requiring a log scan.

Alert delivery within 5-15 seconds of a workflow step entering a failed state, routed to Slack, email, or PagerDuty based on workflow criticality rules. Slack alerts use Incoming Webhooks with Block Kit formatting: the primary block shows workflow name, failed step, error summary, and execution ID (hyperlinked to the investigation trace); action buttons in the Slack message allow the on-call engineer to acknowledge the alert or open the DLQ entry directly from Slack without navigating to the monitoring dashboard. PagerDuty integration uses Events API v2 with severity mapped from the error class: integration authentication failure → critical, upstream API 5xx → error, input validation failure → warning. Email delivery uses SendGrid v3 API with an HTML template structured identically to the Slack alert -- same information hierarchy for agents who monitor email rather than Slack.

Alert deduplication: a Redis SETNX key with a 5-minute TTL per (workflow_id, error_fingerprint) prevents re-alerting on the same failure type within 5 minutes of the initial alert. The error fingerprint is a hash of the step name + error type + error code -- not the full message -- so parameterised errors like "Record 12345 not found" and "Record 67890 not found" collapse to the same fingerprint. After the deduplication window expires, a follow-up alert is sent only if the failure is still occurring, not on every individual instance. For high-volume workflows, a consecutive-failure threshold (default: 3 consecutive failures within a 15-minute window) prevents first-occurrence noise while still alerting before a sustained failure compounds.

Failure classification by error taxonomy: 4xx_client (invalid request, auth failure, not found -- typically a data or configuration problem), 5xx_server (downstream API returning 500/502/503/504 -- typically a transient or infrastructure problem), timeout (step exceeded configured timeout -- downstream degradation or oversized payload), validation (step input failed schema or business rule check -- data problem at source), orchestration (workflow engine itself errored, not a downstream system), unknown (uncaught exception). Classification drives default retry behaviour: 4xx_client errors do not auto-retry since retrying an invalid request produces the same error; 5xx_server and timeout errors auto-retry with exponential backoff; validation errors route immediately to the DLQ for human review.

Maintenance window suppression: scheduled suppression windows stored per workflow with cron syntax (e.g., every Sunday 02:00-04:00 UTC) silence failure alerts during planned downtime. Failures during a suppression window are still logged, still appear in the DLQ, and still appear in the health dashboard -- they are not paged. At the end of the suppression window, a single summary alert is sent showing how many failures occurred during the window. Suppression windows are managed via the monitoring UI with optional Linear/JIRA ticket reference linking the suppression to the maintenance record.

Per-workflow SLA configuration defines the expected end-to-end execution duration: warning_threshold_ms (alert when an execution exceeds this value) and breach_threshold_ms (escalate when an execution exceeds this value). For new workflows, the monitoring system auto-proposes thresholds after 7 days of production data: warning_threshold = 1.5x observed p95, breach_threshold = 2x observed p95. These are confirmed manually before alerting activates and can be overridden at any time. Per-step SLA is also configurable for workflows where a specific external call -- a credit check, an ERP write, a document generation -- needs independent duration monitoring separate from the end-to-end SLA.

Latency tracking uses PostgreSQL window functions computing p50, p95, and p99 over rolling time windows (last 1h/6h/24h/7d) per workflow. The p99 figure is the operationally significant one: a workflow with a p50 of 2 seconds but a p99 of 45 seconds has a tail latency problem that the average hides. These are the executions blocking the highest-priority process instances. Prometheus histogram metrics are exported from the workflow engine: workflow_execution_duration_seconds histogram with workflow_id and status labels, and workflow_step_duration_seconds with step_name and step_type labels. Grafana visualises latency distributions as heat maps over time -- a percentile regression shows as a colour shift before it becomes an SLA breach alert.

SLA compliance rate is tracked at daily, weekly, and monthly granularity: the percentage of executions completing within breach_threshold_ms per workflow, shown as a time series. A workflow at 99.8% SLA compliance on Monday but 94.2% on Thursday has a regression worth investigating; a weekly average of 97.0% masks both. SLA compliance reports are generated weekly as a scheduled email (Monday 08:00 local time) covering all monitored workflows sorted by lowest compliance rate -- the workflows most at risk of missing their operational targets surface first rather than requiring a dashboard scan. Degraded-but-not-failing states -- executions completing above warning threshold but below breach threshold -- are flagged separately from outright failures because the investigation path differs: one is a performance problem, the other is a correctness problem, and the two categories should not be reported in the same alert channel.

DLQ schema: dlq_events table with dlq_id (UUID), original_execution_id (FK), workflow_id, workflow_name, trigger_payload (JSONB, original unmodified -- never overwritten), modified_payload (JSONB nullable, populated if the reviewer edits the payload before replay), failure_step, failure_reason, error_class (from the taxonomy), retry_count, status (pending_review/under_review/replaying/resolved/expired), created_at, reviewed_at, reviewed_by, resolved_at, and resolution_note. If a reviewer modifies the payload before replay, the modification is stored in modified_payload and the edit is recorded in dlq_audit_log with a before/after diff and the reviewer's identity. The original payload is permanently preserved regardless of modifications.

Review workflow: the DLQ management interface groups events by (workflow_id, error_class) to surface patterns. Forty-seven events in "Salesforce contact creation / 4xx_client" share a likely common root cause -- visible as a batch with a sample trace, not as 47 individual entries requiring individual review. Opening a group shows the execution trace for a representative event, the specific error codes ranked by frequency within the group, and the affected payload fields. The reviewer classifies root cause (data problem / config problem / third-party outage / bug) before taking action. For data problems -- a required field absent in the source event -- the reviewer edits the payload to correct the field and replays with modified_payload. For system problems -- a third-party API was down -- bulk replay sends the original unmodified payloads now that the downstream system has recovered.

Replay mechanics: replay creates a new workflow_execution record linked to the original via parent_execution_id, preserving lineage between the failed execution and the replay attempt. An idempotency key derived from dlq_id is passed through to all downstream API calls -- systems that support server-side idempotency (Stripe, Salesforce, most REST APIs with an idempotency-key header) will not create duplicate records even if the replay executes a step that succeeded before the failure point. For systems that do not support server-side idempotency, the workflow checks whether the record was already created by the failed execution before re-creating it. Bulk replay executes in rate-limited batches (10 events/second by default, configurable per workflow) to avoid flooding the downstream system with a burst of previously-failed events. Replay progress is shown in real time in the DLQ UI.

DLQ event expiry: events expire after 30 days by default (configurable per workflow). Expiry is a soft delete -- status set to expired, record retained for audit, excluded from the active DLQ view. Events pending review for more than 7 days without action trigger an escalation Slack alert: a DLQ event that sits unreviewed is a process that has not been recovered, and the operational debt compounds every day it remains unresolved.

Real-time operational view of every monitored workflow with a configurable time window selector (last 1h/6h/24h/7d/30d). Each workflow is displayed as a status card with RAG health classification (Green/Amber/Red), execution volume, success rate, failure count, DLQ backlog depth, and current p95 latency. The RAG classification calculates automatically: Green = success rate ≥99% with no SLA breaches in the current hour; Amber = success rate 95-99% or one SLA breach in the current hour; Red = success rate below 95% or three or more SLA breaches or a growing DLQ backlog. Thresholds are configurable per workflow -- a non-critical nightly batch uses different thresholds than a real-time payment processing workflow.

Time-series charts per workflow overlay execution volume and error rate on the same axis. A volume spike with a simultaneous error rate spike indicates a bad batch of events from the trigger source; an error rate spike without a volume change indicates a downstream system or configuration regression. These two patterns require different investigation paths and should not look identical on the dashboard. Cross-workflow correlation view renders all workflows on a shared timeline grid: if 5 workflows show a simultaneous error rate increase at 14:23 UTC, they share a downstream dependency (a specific API, a database, an authentication service). Surfacing this pattern takes 3 seconds on the correlation view; investigating each workflow independently would take an engineer 20-30 minutes to reach the same conclusion.

Dependency map: each workflow is configured with its downstream system dependencies -- Salesforce, Stripe, the ERP, an internal microservice. The health dashboard shows a system dependency panel listing each integration point, its last successful call timestamp, and the aggregate error rate for calls to that system in the current hour. When Salesforce is returning 503 errors, every workflow dependent on Salesforce reflects it in the same dependency panel rather than appearing as independent, unrelated failures. This is the information that distinguishes "we have a Salesforce outage" from "we have a workflow bug" -- and the correct answer determines whether the on-call engineer contacts Salesforce support or deploys a code fix.

Executive summary view: a read-only page (shareable via link with operations managers or non-technical stakeholders) showing an aggregate health score weighted across all workflows, a count of active incidents (workflows currently in Red status), pending DLQ events requiring review, and the three workflows with the lowest success rate in the selected period. Scheduled weekly summary email generated from this view and sent every Monday covers the prior 7-day performance across all workflows, designed to be read in under 2 minutes.

Statistical volume baseline built from a rolling 28-day window of hourly execution counts per workflow. The baseline accounts for time-of-day and day-of-week patterns independently: a workflow that processes 500 events during business hours and 20 events at 3am has two different baselines, not one average that fits neither. For each hourly time slot, the system records the mean and standard deviation of execution volume from the same slot across the prior 28 days. A Z-score is computed on the current hour's observed volume: Z = (observed - mean) / stddev. Z > 2.5 triggers a spike alert; Z < -2.5 triggers a drop alert. The threshold is configurable per workflow -- payment processing workflows use Z = 2.0 for earlier warning; low-priority batch jobs use Z = 3.0 to reduce noise.

Volume drop alerts detect a failure mode that produces zero individual execution failures and therefore generates no failure alert: trigger events are simply not arriving. An invoice processing workflow that normally receives 400 events on a Tuesday morning but has received 0 events since 09:00 has either a trigger source problem (the source system stopped publishing webhooks) or an upstream integration failure (the webhook endpoint is returning errors that are suppressed at the source and never queued for retry). The volume drop alert fires before anyone notices that invoices are not being processed -- detection happens at the infrastructure level, not the business level. For workflows with a known minimum daily volume, a hard floor threshold (alert_if_below: 50) supplements the statistical baseline so zero-execution scenarios always alert even if the baseline would calculate them as within normal variance.

Volume spike detection protects against trigger event floods: a duplicate-event bug in the source system, an accidental bulk replay from an upstream system, or a webhook retry loop can cause a workflow to receive 10-50x normal volume. At normal processing speed, a flood can exhaust Salesforce API rate limits (15,000 API calls per 24 hours on standard orgs), create duplicate records despite idempotency keys if the same event is sent with different IDs each time, or degrade overall system performance. A spike alert fires when observed volume exceeds 3x baseline (configurable) so the on-call engineer can pause the workflow intake, identify the source of the flood, and resume after the root cause is resolved -- rather than discovering the problem after 50,000 duplicate records have been created.

Business impact estimation is configured per workflow as a multiplier: "each unprocessed execution = 1 customer not onboarded, 1 welcome email not sent." When a volume drop alert fires for that workflow, the alert message includes "Estimated impact: 47 customers not onboarded since 09:00 UTC" -- not just "volume dropped below baseline." Business impact context converts monitoring from an engineering instrument into an operational signal that operations managers can act on without technical translation. Weekly health summary: automated report generated every Monday at 07:00 UTC covering total executions, success rate, failure count by type, SLA compliance, DLQ events resolved vs. pending, and volume anomalies detected in the prior 7 days -- surfacing the three workflows that need attention so the week starts with a prioritised list.