Multi-Stage Approval Workflows: Designing Dynamic Escalation Logic for Enterprise Sales

Every enterprise sales organization eventually builds an approval process, and almost every one builds it badly the first time. It starts as a Slack message ("can someone approve 22% on this one?"), graduates to a CRM field called Approval_Status__c, and ends as a tangle of nested rules that nobody dares touch. A discount over 15% goes to the regional VP, unless the account is strategic, unless the VP is on leave, unless the deal is a renewal, unless finance has flagged the customer for credit risk.

The failure mode is rarely the first approval. It is the fourth stage, the one that fires when a human is slow, absent, or conflicted, and the deal is due to close on the last day of the quarter. Static approval chains do not handle this. They stall, and a stalled approval costs revenue.

This article describes how to design multi-stage approval workflows with dynamic escalation logic in a custom CRM or business portal: a policy model that lives as data, a routing engine that evaluates it deterministically, SLA timers that escalate on their own, and an audit trail that survives a compliance review. The examples use PostgreSQL, YAML policy files, and TypeScript, but the structure applies to any stack.

Why Static Approval Chains Break#

A static chain hard-codes "who approves what" into workflow definitions. It works for a team of ten and fails for an enterprise for four reasons:

1. Routing depends on context, not just amount. Discount depth, contract term, payment terms, non-standard legal clauses, product mix, region, and customer tier all change who must sign off.
2. Approvers are not always available. Vacations, role changes, and overloaded queues are normal. A chain that assumes the named person responds will block.
3. Policy changes weekly. Finance changes a threshold, a new product line needs a specialist reviewer, a region is reorganized. If each change is a code deploy, policy lags reality and teams route around the system.
4. Auditors ask "why". "Why did this deal skip legal?" must be answerable from stored facts, not from reading code at a particular commit.

The design goal follows from these: separate policy (data) from mechanism (engine), make every routing decision reproducible, and make time a first-class input.

The Core Model: Approval as a State Machine#

Treat each approval request as a finite state machine with explicit, enumerated transitions. Avoid boolean flags like is_approved and is_escalated, which allow impossible combinations.

Data Model#

The schema below keeps requests, frozen stages, individual decisions, and an append-only event log separate.

400 font-semibold">CREATE TYPE approval_state AS ENUM (
  400 font-semibold">class="text-emerald-300">'DRAFT',400 font-semibold">class="text-emerald-300">'PENDING',400 font-semibold">class="text-emerald-300">'ESCALATED',400 font-semibold">class="text-emerald-300">'APPROVED',400 font-semibold">class="text-emerald-300">'REJECTED',400 font-semibold">class="text-emerald-300">'CANCELLED'
);

400 font-semibold">CREATE 400 font-semibold">TABLE approval_request (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  opportunity_id uuid NOT NULL,
  revision      int  NOT NULL DEFAULT 1,
  state         approval_state NOT NULL DEFAULT 400 font-semibold">class="text-emerald-300">'DRAFT',
  policy_version text NOT NULL,          -- git SHA or semver of the policy bundle
  context       jsonb NOT NULL,          -- frozen deal facts used 400 font-semibold">for routing
  current_stage int  NOT NULL DEFAULT 0,
  created_by    uuid NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  version       int  NOT NULL DEFAULT 0, -- optimistic lock
  UNIQUE (opportunity_id, revision)
);

400 font-semibold">CREATE 400 font-semibold">TABLE approval_stage (
  request_id    uuid NOT NULL REFERENCES approval_request(id),
  stage_no      int  NOT NULL,
  name          text NOT NULL,
  mode          text NOT NULL CHECK (mode IN (400 font-semibold">class="text-emerald-300">'any_of',400 font-semibold">class="text-emerald-300">'all_of')),
  sla_seconds   int  NOT NULL,
  approver_role text NOT NULL,
  due_at        timestamptz,             -- set when the stage activates
  escalation_level int NOT NULL DEFAULT 0,
  PRIMARY KEY (request_id, stage_no)
);

400 font-semibold">CREATE 400 font-semibold">TABLE approval_decision (
  id          bigserial PRIMARY KEY,
  request_id  uuid NOT NULL,
  stage_no    int  NOT NULL,
  approver_id uuid NOT NULL,
  decision    text NOT NULL CHECK (decision IN (400 font-semibold">class="text-emerald-300">'approve',400 font-semibold">class="text-emerald-300">'reject')),
  comment     text,
  decided_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (request_id, stage_no, approver_id)
);

400 font-semibold">CREATE 400 font-semibold">TABLE approval_event (
  id          bigserial PRIMARY KEY,
  request_id  uuid NOT NULL,
  kind        text NOT NULL,             -- submitted, routed, escalated, delegated, decided...
  actor_id    uuid,                      -- 400">null 400 font-semibold">for system actions
  payload     jsonb NOT NULL,
  at          timestamptz NOT NULL DEFAULT now()
);

400 font-semibold">CREATE 400 font-semibold">INDEX approval_stage_due_idx
  ON approval_stage (due_at)
  400 font-semibold">WHERE due_at IS NOT NULL;

Notes on the choices: policy_version plus the frozen context is what makes a decision reproducible. The UNIQUE (request_id, stage_no, approver_id) constraint makes double-clicks and retried webhooks harmless. The partial index on due_at keeps the escalation sweep cheap even when the table is large. In production, also revoke UPDATE and DELETE on approval_event from the application role.

Policy as Data: A Declarative Rule File#

Express routing rules in a versioned file reviewed through pull requests, not in a UI that edits live rows. A YAML policy might look like this:

version: 400 font-semibold">class="text-emerald-300">"2026.10.1"
stages:
  - id: manager
    name: Sales Manager Review
    role: sales_manager
    mode: any_of
    sla: 4h
    when: 400 font-semibold">class="text-emerald-300">"deal.discount_pct > 10"
  - id: regional_vp
    name: Regional VP Review
    role: regional_vp
    mode: any_of
    sla: 8h
    when: 400 font-semibold">class="text-emerald-300">"deal.discount_pct > 20 or deal.acv_usd >= 250000"
  - id: deal_desk
    name: Deal Desk (Non-standard terms)
    role: deal_desk
    mode: any_of
    sla: 8h
    when: 400 font-semibold">class="text-emerald-300">"deal.payment_terms_days > 60 or deal.custom_clauses"
  - id: finance
    name: Finance and Credit
    role: finance_controller
    mode: all_of
    sla: 24h
    when: 400 font-semibold">class="text-emerald-300">"deal.discount_pct > 30 or account.credit_hold"

escalation:
  ladder:
    - after_breaches: 1
      action: remind
    - after_breaches: 2
      action: reassign
      target: manager_of_approver
    - after_breaches: 3
      action: reassign
      target: role_fallback
  business_hours:
    timezone: approver
    calendar: org_default
  freeze_on:
    - quarter_end_cutoff

Rules are expressions over a typed context object. Use a sandboxed expression language such as CEL rather than eval. CEL is non-Turing-complete, side-effect free, and has implementations in Go, Java, and C++ (and community ports for other languages), which makes policy evaluation safe to run on untrusted-ish data.

Policy files get a CI step that type-checks every when expression against the context schema and rejects unknown fields. A typo like deal.discout_pct should fail the pipeline, not silently evaluate to false and skip a stage.

The Routing Engine#

The engine takes (context, policy) and returns an ordered list of stages. It must be a pure function: no database reads, no clock, no randomness. Purity makes it trivially testable and replayable.

400 font-semibold">class=400 font-semibold">class="text-emerald-300">"text-slate-500 italic">// 400 font-semibold">class="text-emerald-300">`Env` is a thin wrapper over whichever CEL implementation you adopt
400 font-semibold">import { Env } 400 font-semibold">from 400 font-semibold">class="text-emerald-300">"./cel-env";

400 font-semibold">interface StageDef {
  id: 400">string; name: 400">string; role: 400">string;
  mode: 400 font-semibold">class="text-emerald-300">"any_of" | 400 font-semibold">class="text-emerald-300">"all_of"; sla: 400">string; when: 400">string;
}

400 font-semibold">export 400 font-semibold">function routeStages(
  ctx: DealContext,
  policy: { version: 400">string; stages: StageDef[] },
): FrozenStage[] {
  400 font-semibold">const env = 400 font-semibold">new Env().registerVariable(400 font-semibold">class="text-emerald-300">"deal", 400 font-semibold">class="text-emerald-300">"map").registerVariable(400 font-semibold">class="text-emerald-300">"account", 400 font-semibold">class="text-emerald-300">"map");
  400 font-semibold">const bindings = { deal: ctx.deal, account: ctx.account };

  400 font-semibold">const matched = policy.stages
    .filter((s) => env.evaluate(s.when, bindings) === 400">true)
    .map((s, i) => ({
      stageNo: i + 1,
      id: s.id,
      name: s.name,
      role: s.role,
      mode: s.mode,
      slaSeconds: parseDuration(s.sla),
    }));

  400 font-semibold">if (matched.length === 0) 400 font-semibold">return []; 400 font-semibold">class=400 font-semibold">class="text-emerald-300">"text-slate-500 italic">// auto-approved by policy; still log an event
  400 font-semibold">return matched;
}

Two design decisions deserve emphasis. A deal that matches no stage is auto-approved by policy, and that outcome is logged as an event, because "no approval needed" is also a decision an auditor may ask about. And stage order comes from policy order, which is deliberately simple: put cheap, high-volume reviewers first so expensive reviewers (finance, legal) only see deals that already survived earlier gates.

Parallel versus sequential stages#

Sequential routing is easy to reason about but slow. If Deal Desk and Finance do not depend on each other's outcome, add a parallel_group key and activate all stages in a group together. The tradeoff: parallel review risks wasted work when an early reviewer rejects, so use it only where rejection rates are low or where cycle time dominates.

Dynamic Escalation: Time as an Input#

Escalation is where most implementations become fragile. The principle: escalation is a scheduled, idempotent state transition, not a cron job that sends emails.

SLA clocks and business hours#

When a stage activates, compute due_at by adding the SLA in business time to the activation instant, evaluated in the approver's time zone against a holiday calendar. An 8-hour SLA that starts at 16:00 Friday should not breach at midnight. Use a real calendar library (for example date-fns-tz with a holiday table) and store the result as a UTC timestamptz.

The escalation sweep#

A worker polls for overdue stages and applies the ladder. Using FOR UPDATE SKIP LOCKED lets many workers run concurrently without double-processing:

WITH overdue AS (
  400 font-semibold">SELECT s.request_id, s.stage_no
  400 font-semibold">FROM approval_stage s
  400 font-semibold">JOIN approval_request r ON r.id = s.request_id
  400 font-semibold">WHERE s.due_at < now()
    AND r.state IN (400 font-semibold">class="text-emerald-300">'PENDING',400 font-semibold">class="text-emerald-300">'ESCALATED')
    AND r.current_stage = s.stage_no
  400 font-semibold">ORDER BY s.due_at
  LIMIT 100
  FOR 400 font-semibold">UPDATE OF s SKIP LOCKED
)
400 font-semibold">UPDATE approval_stage s
SET escalation_level = s.escalation_level + 1,
    due_at = now() + make_interval(secs => s.sla_seconds / 2)
400 font-semibold">FROM overdue o
400 font-semibold">WHERE s.request_id = o.request_id AND s.stage_no = o.stage_no
RETURNING s.request_id, s.stage_no, s.escalation_level;

The returned escalation_level drives the ladder in application code: level 1 sends a reminder, level 2 reassigns to the approver's manager, level 3 reassigns to a fallback role. Halving the SLA on each breach is one reasonable tightening policy; make it configurable rather than hard-coding it.

For workloads where polling is undesirable, a durable workflow engine such as Temporal gives you the same behavior with timers that survive restarts. Either way, the transition must be idempotent: the event insert and the stage update happen in one transaction, and a retry that finds escalation_level already advanced is a no-op.

Context-sensitive escalation#

"Dynamic" means the ladder itself responds to context. Examples worth encoding:

• Deal urgency: a deal closing within 48 hours skips the reminder rung and reassigns immediately.
• Quarter-end mode: SLAs are halved in the final week of the fiscal quarter, because response time matters most then.
• Value weighting: higher-ACV deals add a notification to the executive sponsor at the first breach.
• Conflict of interest: an approver who is the requester, or reports directly to a requester, is excluded at routing time and the next eligible approver is chosen.

Each is expressed as a condition on the same context object, not as special-case code.

Delegation and Out-of-Office Handling#

Absence is the most common reason approvals stall. Handle it before the SLA breaches, at assignment time:

1. Resolve the stage's role to a candidate set of users via your identity provider or a role table.
2. Filter out users with an active out-of-office record or an exhausted queue limit.
3. Apply explicit delegations (delegator_id, delegate_id, valid_from, valid_until), which must be time-bounded and recorded in the event log.
4. If the set is empty, escalate immediately to the fallback role rather than waiting.

Two safeguards apply. Delegation must not be transitive without limit; cap the chain at one or two hops to avoid loops. And a delegate may not approve a request they created or would benefit from. Run the same conflict check as in routing.

Concurrency, Idempotency, and Failure Modes#

Approval systems are small but sit on contentious rows. Common bugs and their fixes:

400 font-semibold">async 400 font-semibold">function recordDecision(tx: Tx, reqId: 400">string, stageNo: 400">number,
                              approverId: 400">string, decision: 400 font-semibold">class="text-emerald-300">"approve" | 400 font-semibold">class="text-emerald-300">"reject") {
  400 font-semibold">const req = 400 font-semibold">await tx.one(
    400 font-semibold">class="text-emerald-300">`400 font-semibold">SELECT * 400 font-semibold">FROM approval_request 400 font-semibold">WHERE id = $1 FOR 400 font-semibold">UPDATE`, [reqId]);
  400 font-semibold">if (req.state !== 400 font-semibold">class="text-emerald-300">"PENDING" && req.state !== 400 font-semibold">class="text-emerald-300">"ESCALATED") 400 font-semibold">return; 400 font-semibold">class=400 font-semibold">class="text-emerald-300">"text-slate-500 italic">// idempotent no-op
  400 font-semibold">if (req.current_stage !== stageNo) 400 font-semibold">return;

  400 font-semibold">await tx.none(
    400 font-semibold">class="text-emerald-300">`400 font-semibold">INSERT INTO approval_decision (request_id, stage_no, approver_id, decision)
     VALUES ($1,$2,$3,$4) ON CONFLICT DO NOTHING`,
    [reqId, stageNo, approverId, decision]);

  400 font-semibold">if (decision === 400 font-semibold">class="text-emerald-300">"reject") 400 font-semibold">return transition(tx, req, 400 font-semibold">class="text-emerald-300">"REJECTED");

  400 font-semibold">const stage = 400 font-semibold">await tx.one(
    400 font-semibold">class="text-emerald-300">`400 font-semibold">SELECT mode 400 font-semibold">FROM approval_stage 400 font-semibold">WHERE request_id=$1 AND stage_no=$2`, [reqId, stageNo]);
  400 font-semibold">const approvals = 400 font-semibold">await tx.one(
    400 font-semibold">class="text-emerald-300">`400 font-semibold">SELECT count(*)::int AS n 400 font-semibold">FROM approval_decision
     400 font-semibold">WHERE request_id=$1 AND stage_no=$2 AND decision='approve'`, [reqId, stageNo]);
  400 font-semibold">const required = stage.mode === 400 font-semibold">class="text-emerald-300">"any_of" ? 1 : 400 font-semibold">await requiredApprovers(tx, reqId, stageNo);

  400 font-semibold">if (approvals.n >= required) 400 font-semibold">await advanceStage(tx, req);
}

Taking SELECT ... FOR UPDATE on the request row serializes every mutation for that request, which is cheap because contention per request is low.

Auditability and Observability#

Compliance regimes such as SOX expect you to show who approved what, under which rules, and when. The design above gives you that if you hold to three practices:

• Append-only events. Every state change, routing decision, escalation, and delegation is an approval_event row. The current state in approval_request is a projection that can be rebuilt from events.
• Reproducible routing. Given the stored context and policy_version, you can re-run routeStages and confirm it yields the same stages. Build this as a nightly consistency check.
• Policy provenance. Policy files live in Git; the deployed bundle's commit SHA is stamped onto each request.

For operations, export a few metrics (for example via OpenTelemetry or Prometheus): time-in-stage by stage and role, escalation rate per stage, auto-approval rate, and the age of the oldest overdue stage. The last one is the best alerting signal; if it exceeds a few multiples of the longest SLA, the sweep is not running.

A worked illustration, with assumptions stated: suppose a team handles 400 approval requests a month, and 30% breach their first SLA under a static chain, each costing on average one extra business day. That is roughly 120 stalled requests and 120 delay-days a month. If a tiered ladder with out-of-office filtering cuts first-breach rates to 10%, the same volume produces about 40 stalled requests. These numbers are illustrative inputs to a model you should fill with your own data, not benchmarks.

Testing the Policy#

Because routing is pure, test it like a function. Table-driven tests cover the policy matrix cheaply:

400 font-semibold">const cases = [
  { name: 400 font-semibold">class="text-emerald-300">"small discount, no approval", deal: { discount_pct: 5, acv_usd: 40000 },
    expect: [] },
  { name: 400 font-semibold">class="text-emerald-300">"mid discount, manager only", deal: { discount_pct: 14, acv_usd: 60000 },
    expect: [400 font-semibold">class="text-emerald-300">"manager"] },
  { name: 400 font-semibold">class="text-emerald-300">"large ACV adds VP", deal: { discount_pct: 12, acv_usd: 300000 },
    expect: [400 font-semibold">class="text-emerald-300">"manager", 400 font-semibold">class="text-emerald-300">"regional_vp"] },
  { name: 400 font-semibold">class="text-emerald-300">"deep discount hits finance", deal: { discount_pct: 35, acv_usd: 90000 },
    expect: [400 font-semibold">class="text-emerald-300">"manager", 400 font-semibold">class="text-emerald-300">"regional_vp", 400 font-semibold">class="text-emerald-300">"finance"] },
];

test.each(cases)(400 font-semibold">class="text-emerald-300">"$name", ({ deal, expect: ids }) => {
  400 font-semibold">const stages = routeStages({ deal, account: { credit_hold: 400">false } }, policy);
  expect(stages.map((s) => s.id)).toEqual(ids);
});

Add property-based tests (for example with fast-check) asserting invariants: raising the discount never removes a stage, and no stage list contains the requester as the sole approver. Finally, run a shadow mode when changing policy: evaluate the new bundle against the last 90 days of real requests and diff the routing outcomes before it goes live.

Conclusion#

Reliable multi-stage approvals come from a few disciplined decisions: model requests as explicit state machines, keep policy declarative and versioned, make routing a pure function, treat time as an input with idempotent escalation transitions, and record everything in an append-only log. None of this is exotic, but together it turns approvals from a source of end-of-quarter anxiety into a predictable, auditable subsystem that adapts as policy changes.

If you are designing or rebuilding approval flows inside a custom CRM or business portal, our team at knetwork.live is happy to run an architecture consultation to review your routing rules, data model, and escalation design.