Cosmos DB Document Approval State Machine

Overview

This flow adds a governed approval workflow over business documents stored in Azure Cosmos DB. On an hourly schedule it finds every document whose status field equals a pending-review value, runs a Microsoft Approvals (Teams/Outlook) approval for each one, writes the decision back onto the document (status, approver, decision date, correlation id) via an upsert, and posts the outcome to a Microsoft Teams channel.

Why it matters: Cosmos-backed apps usually have no built-in approval/sign-off mechanism. Power Automate layers a routable, auditable approval state machine over the documents without changing the application - the document itself carries its own workflow state. Shipped Off as a reference implementation; going live requires only authorizing the three connections and setting the six environment variable values.

Use Case

A Cosmos-backed application stores business documents (orders, content items, change requests, etc.). Each document carries a status field. When a document is set to a pending-review value, it must be approved by a designated reviewer before it can proceed. This flow polls for those pending documents, gets a decision, persists it back onto the document, and notifies the team - turning the status field into a durable approval state machine.

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Different status model

Change varStatusField, varPendingValue, varApprovedValue and varRejectedValue to match your document schema; the Cosmos query and write-backs follow automatically.

Multi-stage approval

Chain a second StartAndWaitForAnApproval inside the approved branch for sequential sign-off.

Avoid re-polling in-flight documents

Set an interim status (e.g. InReview) on the document before starting the approval so the next hourly poll skips it.

Timeout / escalation

Add a parallel Delay plus reminder branch to auto-escalate stale reviews.

Optimistic concurrency

Pass the document _etag on the upsert (If-Match) to prevent lost updates under concurrent edits.

Notify the submitter directly

Extend the Teams message (or add an Outlook SendEmailV2) using the varSubmitterField value captured from each document.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

concat('SELECT * FROM c WHERE c["', variables('varStatusField'), '"] = ''', variables('varPendingValue'), '''')

@outputs('Start_and_Wait_for_Approval')?['body/outcome']

setProperty(setProperty(setProperty(setProperty(items('Apply_to_each_Pending_Document'), variables('varStatusField'), variables('varApprovedValue')), 'approver', variables('varApproverEmail')), 'decisionDate', utcNow()), 'correlationId', variables('varCorrelationId'))

items('Apply_to_each_Pending_Document')?[variables('varTitleField')]

@guid()