Workflow and state-machine analytics

Applications often contain workflows that are more important than page views: an application moves through review, a document awaits approval, a claim is adjudicated, or an order moves from accepted to fulfilled.

Model those workflows as immutable transitions. GraphJSON can then explain flow, delay, rework, and outcomes while the application or workflow engine remains authoritative for current state and execution.

Define the workflow before the events#

Write the allowed state machine:

draft
  → submitted
  → under_review
  → approved
  → completed

under_review → changes_requested → submitted
under_review → rejected
submitted → cancelled

For every workflow type, document:

• workflow entity and stable ID
• allowed states and terminal states
• allowed transitions
• actor types
• clocks and pause rules
• reopen and cancellation semantics
• SLA or business-calendar policy
• definition version

Do not infer a state machine from whichever labels happen to appear in production data.

Emit immutable transitions#

{
  "event": "workflow_transitioned",
  "event_id": "evt_transition_01J...",
  "workflow_id": "application_782",
  "workflow_type": "merchant_application",
  "transition_id": "transition_18",
  "from_state": "submitted",
  "to_state": "under_review",
  "transition": "review_started",
  "actor_type": "operations_user",
  "actor_id": "usr_opaque_91",
  "reason_code": null,
  "workflow_version": "merchant-v4",
  "schema_version": 1
}

The transition should be emitted after the authoritative state change commits. Use an outbox when losing the analytical copy would materially affect operations.

Avoid free-form reason text. Use a bounded reason code and keep sensitive case notes in the operational system.

Preserve transition identity and order#

Every transition needs:

• unique event_id
• stable workflow_id
• unique transition_id
• occurrence timestamp
• previous and next state
• workflow definition version

When a source provides a monotonic workflow revision, include it:

{
  "workflow_revision": 18
}

Revision order is safer than assuming events always arrive in occurrence order. Delivery can be delayed or retried. Deduplicate by stable source identity before calculating state.

Validate the transition contract#

Find mismatched chains:

WITH ordered AS (
  SELECT
    JSONExtractString(json, 'workflow_id') AS workflow_id,
    timestamp,
    JSONExtractString(json, 'from_state') AS from_state,
    JSONExtractString(json, 'to_state') AS to_state,
    lagInFrame(to_state, 1, '') OVER (
      PARTITION BY workflow_id
      ORDER BY timestamp, JSONExtractString(json, 'transition_id')
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS previous_to_state
  FROM workflow_events
  WHERE JSONExtractString(json, 'event') = 'workflow_transitioned'
)
SELECT
  workflow_id,
  timestamp,
  previous_to_state,
  from_state,
  to_state
FROM ordered
WHERE previous_to_state != '' AND previous_to_state != from_state
LIMIT 100

Also validate:

• unknown states
• impossible transitions
• duplicate transition IDs
• missing workflow IDs
• events before workflow creation
• transitions after a terminal state without an explicit reopen
• revision gaps or regressions

An impossible analytical sequence may indicate a producer defect, late event, or a real emergency transition missing from the documented state machine.

Query the latest known state#

For an operationally convenient analytical view:

SELECT
  JSONExtractString(json, 'workflow_id') AS workflow_id,
  argMax(
    JSONExtractString(json, 'to_state'),
    tuple(timestamp, JSONExtractString(json, 'transition_id'))
  ) AS latest_state,
  max(timestamp) AS last_transition_at
FROM workflow_events
WHERE JSONExtractString(json, 'event') = 'workflow_transitioned'
GROUP BY workflow_id

This is the latest state observed by GraphJSON, not a transactional lookup. Do not use it to authorize work, execute a transition, or promise the current status to a customer without a suitable reconciliation contract.

Measure transition duration#

For a known pair such as submission to review:

WITH workflow_times AS (
  SELECT
    JSONExtractString(json, 'workflow_id') AS workflow_id,
    minIf(
      timestamp,
      JSONExtractString(json, 'to_state') = 'submitted'
    ) AS submitted_at,
    minIf(
      timestamp,
      JSONExtractString(json, 'to_state') = 'under_review'
    ) AS review_started_at
  FROM workflow_events
  WHERE JSONExtractString(json, 'event') = 'workflow_transitioned'
  GROUP BY workflow_id
)
SELECT
  quantileExact(0.50)(review_started_at - submitted_at) AS p50_seconds,
  quantileExact(0.90)(review_started_at - submitted_at) AS p90_seconds,
  count() AS workflows
FROM workflow_times
WHERE
  submitted_at > 0
  AND review_started_at >= submitted_at

This measures elapsed time, not necessarily working time. If weekends, holidays, or paused states do not count, calculate the authoritative service clock in the workflow system and emit a compact result:

{
  "event": "workflow_clock_completed",
  "workflow_id": "application_782",
  "clock": "submission_to_first_review",
  "elapsed_ms": 86400000,
  "counted_ms": 28800000,
  "calendar_version": "support-us-2026",
  "outcome": "within_sla"
}

Measure work in progress as a stock#

Starts and completions are flows. Work in progress is the number of workflows in non-terminal states at a point in time.

For the latest observed stock:

WITH latest AS (
  SELECT
    JSONExtractString(json, 'workflow_id') AS workflow_id,
    argMax(
      JSONExtractString(json, 'to_state'),
      tuple(timestamp, JSONExtractString(json, 'transition_id'))
    ) AS state
  FROM workflow_events
  WHERE JSONExtractString(json, 'event') = 'workflow_transitioned'
  GROUP BY workflow_id
)
SELECT state, count() AS workflows
FROM latest
WHERE state NOT IN ('completed', 'rejected', 'cancelled')
GROUP BY state
ORDER BY workflows DESC

For historical backlog, publish periodic workflow snapshots or reconstruct state as of each reporting boundary carefully. Do not chart daily transition counts and label them backlog.

Detect rework and loops#

Rework is product-specific:

changes_requested → submitted
approved → under_review
completed → reopened

Count workflows with repeated entry into the same state:

SELECT
  JSONExtractString(json, 'workflow_id') AS workflow_id,
  JSONExtractString(json, 'to_state') AS state,
  count() AS entries
FROM workflow_events
WHERE JSONExtractString(json, 'event') = 'workflow_transitioned'
GROUP BY workflow_id, state
HAVING entries > 1
ORDER BY entries DESC

Segment rework by bounded reason, workflow version, source, and team. Never rank individual employees from an uncontrolled workflow metric without a reviewed, fair-use policy.

Find bottlenecks#

Combine:

• volume entering each state
• current work in progress
• median and tail time in state
• rework rate
• cancellation or rejection after the state
• SLA breach rate
• oldest open work

High work in progress with long age suggests constraint. High volume alone may simply indicate a popular path. Tail time often matters more than the average.

Define terminal outcomes and maturity#

Record terminal states explicitly:

completed
rejected
cancelled
expired

A recent workflow that is still open is not a failed workflow. For completion or rejection rates, include only workflows with a complete observation window or report open status separately.

For reopening, decide whether it:

• continues the original workflow
• creates a new workflow linked by parent_workflow_id
• invalidates the earlier terminal outcome

Document the choice before writing the metric.

Build the operating dashboard#

Recommended sections:

1. arrivals, completions, and terminal outcomes
2. current work in progress by state
3. oldest open workflows
4. time in state and end-to-end duration
5. transition and rework rates
6. SLA outcomes
7. breakdown by workflow version and bounded reason
8. transition-contract health

Use Targets, forecasts, and plan-versus-actual reporting when capacity planning requires an explicit staffing or throughput plan.

Workflow checklist#

• The state machine and allowed transitions are written first.
• Stable workflow, transition, and event IDs are present.
• Transitions are emitted after the authoritative commit.
• Previous and next state are included.
• Delivery order is not mistaken for occurrence order.
• Impossible transitions and duplicate IDs are monitored.
• Time in state distinguishes elapsed and counted business time.
• Work in progress is modeled as a stock, not transition volume.
• Rework and reopen semantics are explicit.
• Open workflows are not classified as failed before maturity.
• Sensitive notes and payloads remain in the operational system.
• GraphJSON's latest state is not used as the workflow authority.