DEV Community: Abel

Building FeedbackForge a Multi-Agent AI System on Azure with MAF, Foundry & AI Gateway (Part 2)

Abel — Mon, 30 Mar 2026 15:20:07 +0000

A practical guide to architecture, implementation, and lessons learned from a multi-agent AI system

Posts in this Series

Building FeedbackForge a Multi-Agent AI System on Azure with MAF, Foundry & AI Gateway (Part 1)

Building FeedbackForge a Multi-Agent AI System on Azure with MAF, Foundry & AI Gateway (Part 2) (This Post)

Microsoft Foundry

The whole project uses Microsoft Foundry , which is a unified PaaS platform for designing, customizing, and deploying AI applications and agents. It offers access to over 11,000 models, including OpenAI, Meta Llama, and Mistral, alongside tools like Foundry Agent Service and Foundry IQ to build, manage, and secure AI apps from prototype to production.

Foundry Control Plane

Microsoft Foundry Control Plane is a unified management interface that provides visibility, governance, and control for AI agents, models, and tools across your Foundry enterprise. Foundry Control Plane centralizes management for your AI agent fleet, from build to production.

Manages multiple AI agents across different projects or teams.
Requires centralized compliance visibility and policy enforcement across an AI fleet.
Integrates Microsoft Defender and Microsoft Purview for AI governance and threat protection.
Operates agents from multiple platforms, including Foundry, Microsoft, and non-Microsoft sources.
Needs to track cost, token usage, and resource consumption across an entire AI environment.

Without Foundry Control Plane, you manage agents, models, and compliance through individual Azure portal blades and separate per-project views. Foundry Control Plane adds cross-project visibility, unified compliance enforcement, and integrated security signals in a single interface.

Security

For agents, we will use a typical Managed Identity , which will act as its identity.

Observability

AI observability refers to the ability to monitor, understand, and troubleshoot AI systems throughout their lifecycle. Teams can trace, evaluate, integrate automated quality gates into CI/CD pipelines, and collect signals such as evaluation metrics, logs, traces, and model outputs to gain visibility into performance, quality, safety, and operational health.

Monitoring

Production monitoring ensures your deployed AI applications maintain quality and performance in real-world conditions. Integrated with Azure Monitor Application Insights, Microsoft Foundry delivers real-time dashboards tracking operational metrics, token consumption, latency, error rates, and quality scores. Teams can set up alerts when outputs fail quality thresholds or produce harmful content, enabling rapid issue resolution.

The Agent Monitoring Dashboard in Microsoft Foundry tracks operational metrics and evaluation results for your agents. This dashboard helps you understand token usage, latency, success rates, and evaluation outcomes for production traffic.

Tracing

Distributed tracing captures the execution flow of AI applications, providing visibility into LLM calls, tool invocations, agent decisions, and inter-service dependencies. Built on OpenTelemetry standards and integrated with Application Insights, tracing enables debugging complex agent behaviors, identifying performance bottlenecks, and understanding multi-step reasoning chains. Microsoft Foundry supports tracing for popular frameworks including LangChain, Semantic Kernel, and the OpenAI Agents SDK.

Guardrails and Controls

Microsoft Foundry provides safety and security guardrails that you can apply to core models and agents. Guardrails consist of a set of controls. The controls define a risk to be detected, intervention points to scan for the risk, and the response action to take in the model or agent when the risk is detected.

The created guardrail contains controls for:

Jailbreak
Indirect Prompt Injections
Sensitive Data Leakage (PII)
Content Safety
Protected Materials

These controls are assigned to all the agents used in the system.

Evaluators

Evaluators measure the quality, safety, and reliability of AI responses throughout development. Microsoft Foundry provides built-in evaluators for general-purpose quality metrics (coherence, fluency), RAG-specific metrics (groundedness, relevance), safety and security (hate/unfairness, violence, protected materials), and agent-specific metrics (tool call accuracy, task completion). Teams can also build custom evaluators tailored to their domain-specific requirements.

An evaluator was created with the following JSON structure:

{
   "query":"What's the weekly summary?",
   "response":"",
   "context":"User is requesting a weekly feedback summary. Expected to use get_weekly_summary tool and return sentiment, top issues, feedback count.",
   "ground_truth":"The response should include sentiment breakdown, top issues, feedback count, and distinguish between positive and negative feedback. Required fields: total_feedback, sentiment_breakdown.",
   "conversation_id":"conv_001",
   "response_id":"test_001",
   "previous_response_id":null,
   "latency":null,
   "response_length":null
}

The criteria for the evaluator were left as the default ones. They could be adjusted since many do not apply. In this case, they were left unchanged for simplicity.

Purview, Defender, and Entra

Scaling AI agents securely requires combining development capabilities with strong governance, security, and compliance controls.

AI systems introduce new risks such as data leakage, prompt injection, and agent misuse.
Security must be applied across all layers: agents, data, models, and interactions.
Microsoft Foundry acts as the control plane to manage, observe, and secure AI workloads.

✔️ Purview focuses on data governance, compliance, and protection (e.g., sensitivity labels, DLP, auditing).

✔️ Defender provides security posture management and runtime threat protection for AI workloads.

✔️ Entra introduces the concept of managing agents as identities with controlled access.

AI security is not a single tool but a combination of:

Governance (Purview)
Threat protection (Defender)
Identity and access control (Entra)
Control plane and observability (Foundry)

This enables organizations to move from experimental AI solutions to secure, governed, and production-ready agent systems.

Enabling Purview or Defender for this project was considered overkill, but in a real production scenario they should be mandatory.

AI Gateway

The AI gateway in Azure API Management provides a set of capabilities to manage AI backends effectively. It enables control over security, reliability, observability, and cost.

The following sections describe the main capabilities, combining traditional API gateway features with AI-specific functionality.

Governance

Token Rate Limiting and Quotas

You can configure token-based limits on LLM APIs to control usage per consumer based on token consumption.

This allows defining:

Tokens per minute (TPM)
Token quotas over time (hourly, daily, monthly, etc.)

<llm-token-limit counter-key="@(context.Subscription.Id)"
                 tokens-per-minute="500"
                 estimate-prompt-tokens="false"
                 remaining-tokens-variable-name="remainingTokens">
</llm-token-limit>

Security and Safety

Security

All authentication is performed through the API Management subscription key, without relying on JWT or any Entra ID OAuth mechanism.

Content Safety

In this case, Content Safety is not configured at the AI Gateway level. Instead, it is managed through Microsoft Foundry.

Observability

Token Metrics

Token usage can be emitted using the llm-emit-token-metric policy, with custom dimensions for filtering in Azure Monitor. The following example emits token metrics with dimensions for client IP address, API ID, and user ID (from a custom header):

<llm-emit-token-metric namespace="llm-metrics">
     <dimension name="Client IP" value="@(context.Request.IpAddress)" />
     <dimension name="API ID" value="@(context.Api.Id)" />
     <dimension name="User ID" value="@(context.Request.Headers.GetValueOrDefault("x-user-id", "N/A"))" />
</llm-emit-token-metric>

Prompt and Completion Logging

Logging can be enabled to track:

Token usage
Prompts and completions
API consumption patterns

This data can be analyzed in Application Insights and visualized through built-in dashboards in API Management.

Scalability and Performance

Semantic Caching

Semantic caching is a technique that improves the performance of LLM APIs by caching the results (completions) of previous prompts and reusing them by comparing the vector proximity of the prompt to prior requests.

Benefits:

Reduced calls to backend AI services
Lower latency
Cost optimization

It can be implemented using Azure Managed Redis or any compatible cache.

<policies>
<inbound>
<base />
<llm-semantic-cache-lookup 
     score-threshold="0.05"
     embeddings-backend-id="azure-openai-backend"
     embeddings-backend-auth="system-assigned">
     <vary-by>@(context.Subscription.Id)</vary-by>
</llm-semantic-cache-lookup>
<rate-limit calls="10" renewal-period="60" />
</inbound>
<outbound>
<llm-semantic-cache-store duration="60" />
<base />
</outbound>
</policies>

Note: A rate-limit policy should be applied after the cache lookup to prevent backend overload if the cache is unavailable.

Weight or Session-Aware Load Balancing

The backend load balancer supports round-robin, weighted, priority-based, and session-aware load balancing. You can define a load distribution strategy that meets your specific requirements.

Priority Routing to Provisioned Capacity Models

Enabling priority processing at the request level is optional. Both the chat completions API and responses API have an optional attribute service_tier that specifies the processing type to use when serving a request. In this case, we will not use it.

Velocity

Import Azure OpenAI as an API

Azure API Management allows importing Azure OpenAI endpoints as APIs with a single action. This includes:

Automatic OpenAPI schema generation
Managed identity authentication
Simplified onboarding

External Models and Integrations

No models outside Azure are used in this project. MCP servers and A2A agents are fully implemented within the Python backend rather than managed through the gateway.

Full Policy Example

Below is the full AI Gateway policy used in FeedbackForge. It uses policy fragments to simplify configuration and improve maintainability.

<policies>
<inbound>
<base />
<!--
FeedbackForge Policy v2
Purpose: Routes requests to appropriate backend pools based on model selection and RBAC permissions
Flow:
1. Validate Entra ID authentication (optional, controlled by entra-validate named value)
2. Extract and validate model parameter from request payload
3. Configure backend pools and routing rules
4. Determine target backend pool based on model and permissions
5. Set up authentication and route to selected backend
6. Configure collecting usage metrics
Configuration:
- Modify allowedBackendPools for RBAC (comma-separated pool IDs, empty = all allowed)
- Set defaultBackendPool for unmapped models (empty = return error)
- Backend pool definitions are in frag-set-backend-pools fragment -->

<set-backend-service id="apim-generated-policy" backend-id="feedbackforgev2-ai-endpoint" /> 
<!.. - Step 1: Validate Entra ID authentication (if enabled) -->
<!-- - <include-fragment fragment-id="aad-auth" /> -->
<!-- - Step 2: Extract and validate model parameter from request -->
<include-fragment fragment-id="set-llm-requested-model" />
<!-- - Remove api-key header to prevent it from being passed to backend endpoints -->
<set-header name="api-key" exists-action="delete" />
<!-- - Step 4: Configure RBAC and default routing behavior -->
<!-- - RBAC: Set allowed backend pools (comma-separated pool IDs, empty = all pools allowed) -->
<set-variable name="allowedBackendPools" value="" />
<!-- - Set default backend pool (empty = return error for unmapped models) -->
<set-variable name="defaultBackendPool" value="" />
<!-- - Step 4: Load backend pool configurations -->
<include-fragment fragment-id="set-backend-pools" />
<!-- - Step 5: Determine target backend pool based on model and permissions -->
<include-fragment fragment-id="set-target-backend-pool" />
<!-- - Step 6: Configure authentication and route to selected backend -->
<include-fragment fragment-id="set-backend-authorization" />
<!-- - Step 7: Configure collecting usage metrics -->
<include-fragment fragment-id="set-llm-usage" />
<!-- - CORS Configuration for AI Foundry Compatibility -->
<include-fragment fragment-id="ai-foundry-compatibility" />
<llm-semantic-cache-lookup score-threshold="0.0" embeddings-backend-id="AI-SC-text-embedding-aokaqomw6tctxm5" />
<rate-limit calls="10" renewal-period="60" />
<llm-token-limit remaining-quota-tokens-header-name="remaining-tokens" remaining-tokens-header-name="remaining-tokens" tokens-per-minute="1000" token-quota="100" token-quota-period="Hourly" counter-key="@(context.Subscription.Id)" estimate-prompt-tokens="true" tokens-consumed-header-name="consumed-tokens" />
<llm-emit-token-metric namespace="llm-metrics">
<dimension name="Client IP" value="@(context.Request.IpAddress)" />
<dimension name="API ID" value="@(context.Api.Id)" />
<dimension name="User ID" value="@(context.Request.Headers.GetValueOrDefault("x-user-id", "N/A"))" />
</llm-emit-token-metric>
</inbound>
<backend>
<!-- 
- Backend Retry Logic
Purpose: Implements retry mechanism for transient failures (429 throttling, 503 service unavailable)
Configuration:
- Retry count: Set to one less than number of backends in pool to try all backends
- Condition: Retries on 429 (throttling) or 503 (except when backend pool is unavailable)
- Strategy: First fast retry with zero interval, buffer request body for replay
-->
<retry count="2" interval="0" first-fast-retry="true" condition="@(context.Response.StatusCode == 429 || (context.Response.StatusCode == 503 && !context.Response.StatusReason.Contains("Backend pool") && !context.Response.StatusReason.Contains("is temporarily unavailable")))">
<forward-request buffer-request-body="true" />
</retry>
</backend>
<outbound>
<base />
</outbound>
<on-error>
<base />
<!-- 
- Error Handling and Custom Metrics
Purpose: Pushes custom metrics for 429 throttling errors to enable Azure Monitor alerts
Variables Set:
- service-name: Identifies the service generating the error
- target-deployment: The model that was requested when error occurred
Integration: Uses throttling-events fragment to send metrics to monitoring system
 -->
<set-variable name="service-name" value="FeedbackForgev2" />
<set-variable name="target-deployment" value="@((string)context.Variables["requestedModel"])" />
<include-fragment fragment-id="throttling-events" />
</on-error>
</policies>

Azure Well-Architected Framework

The Azure Well-Architected Framework AI workload assessment is a review tool that can be used to self-assess the readiness of an AI workload for production.

Running AI workloads on Azure can be complex, and this assessment helps evaluate how well the system aligns with the best practices defined in the Well-Architected Framework pillars.

Although this is not a full professional project following all stages of a typical SDLC, this assessment highlights several missing areas and helps identify gaps that would need to be addressed in a real production scenario.

Assessment Results

The result of applying the assessment is not particularly strong, with most areas marked as Critical.

However, this is expected given the scope of the project, which focuses on architecture and experimentation rather than production readiness.

Vibe Coding

All the Python and React development for the agents, backend, and frontend systems was done using Claude Code from Anthropic.

This significantly accelerated the project, reducing the implementation time from months to weeks.

This was the first time building a non-trivial project of this scale using an LLM in a declarative way, and the results were a big surprise in terms of quality.

The project initially started using Github Copilot but later transitioned to Claude Code due to its stronger ability to understand context and generate more accurate and complete code.

It also proved to be a very effective companion for problem-solving, helping identify complex errors, suggest fixes, and resolve issues related to libraries and version compatibility.

Lessons Learned

Feature overlap across MAF, Foundry, and AI Gateway Some capabilities such as memory management and content safety appear across multiple layers, which can lead to duplication and requires clear architectural decisions on where to implement each concern.
Vibe Coding Refer to the previous section. While highly effective, it may not be suitable for everyone due to cost considerations. In this case, the total cost was approximately 260 euros over 3 months of development. Using a different model might reduce costs.
MAF complexity MAF provides multiple ways to instantiate and interact with agents, which can feel somewhat convoluted when deciding which approach to use.
AI Gateway setup is non-trivial Designing and implementing an AI Gateway landing zone requires significant effort and understanding of policies, security, and routing.
Multi-agent orchestration Complex orchestrations involving multiple agents are challenging by nature, but MAF significantly simplifies their implementation.
Production readiness requires more effort As highlighted in the Azure Well-Architected Framework assessment, a real production-grade system would require additional work across governance, security, and operational maturity.
Observability is critical Observability using OpenTelemetry (logs, traces, and metrics) is essential in AI systems to understand agent behavior and system execution at any point in time.

Source code:

https://github.com/zodraz/feedback-forge

https://github.com/zodraz/ai-hub-gateway-feedbackforge

Building FeedbackForge a Multi-Agent AI System on Azure with MAF, Foundry & AI Gateway (Part 1)

Abel — Mon, 30 Mar 2026 06:04:19 +0000

A practical guide to architecture, implementation, and lessons learned from a multi-agent AI system

Posts in this Series

Building FeedbackForge a Multi-Agent AI System on Azure with MAF, Foundry & AI Gateway (Part 1) (This Post)

Building FeedbackForge a Multi-Agent AI System on Azure with MAF, Foundry & AI Gateway (Part 2)

Introduction

FeedbackForge is an AI-powered feedback analysis system designed to transform raw customer feedback into actionable insights.

Modern enterprises collect feedback from multiple sources such as web forms and platforms like Zendesk, but most of it remains underutilized due to the effort required to analyze it effectively.

FeedbackForge addresses this by using a multi-agent architecture to:

Analyze customer feedback at scale
Detect anomalies and emerging issues
Generate actionable recommendations
Create tickets in systems like Jira
Provide an executive dashboard for interactive insights

This project is a side initiative aimed at exploring agentic AI systems using Microsoft technologies such as Microsoft Foundry, Microsoft Agent Framework (MAF), and Azure AI services.

Use Case

To put FeedbackForge into context, let’s imagine a scenario based on a fictional enterprise: Contoso Inc.

Contoso is a global company that collects customer feedback through:

Traditional web forms
Zendesk support tickets
Mail-based feedback

Despite having access to large amounts of feedback, they face several challenges:

Feedback is fragmented across systems
Analysis is manual and time-consuming
Issues are difficult to prioritize
No clear connection between feedback and actions
Insights arrive too late to be useful

As a result, critical issues often go unnoticed, impacting customer satisfaction and retention.

Goals

FeedbackForge aims to solve these problems by enabling:

Automatic classification and analysis of feedback
Real-time anomaly detection and issue tracking
Generation of actionable recommendations
Automatic creation of Jira tickets
Dynamic FAQ generation from customer feedback
An executive dashboard with a conversational interface
Competitive intelligence and churn risk detection

Architecture

FeedbackForge is designed as a multi-agent system, where multiple specialized agents collaborate to analyze feedback and generate insights.

Each agent focuses on a specific task (e.g., sentiment analysis, anomaly detection, action generation…), allowing the system to be modular, scalable, and easier to maintain.

Agents communicate externally using the Agent-to-Agent (A2A) protocol and interact with systems through Model Context Protocol (MCP) servers, enabling integration with tools such as Zendesk and Jira.

AI Hub Gateway

To ensure governance, security, and observability, FeedbackForge uses a centralized AI Gateway based on Azure API Management.

This gateway acts as a control plane for all AI interactions, enforcing:

Security and access control
Usage monitoring and cost management
Policy enforcement and compliance
Resiliency and load balancing

The architecture follows a hub-and-spoke model, where a central AI gateway manages traffic while individual application environments operate independently within defined guardrails.

This layer is implemented through the Citadel Governance Hub , an enterprise-grade AI landing zone that provides a unified control plane for all AI workloads.

Why this matters

Instead of allowing each application to directly call AI models, all traffic flows through a centralized gateway. This enables:

Governance & Security Consistent access control, identity management, and policy enforcement
Observability & Compliance Centralized logging, metrics, and near real-time usage analytics
Developer Velocity Standardized onboarding and reusable configuration patterns

Hub-and-Spoke Architecture

The system follows a hub-and-spoke model:

Hub (Control Plane) → Centralized governance and AI Gateway
Spokes (Applications) → Independent workloads and agents

This allows teams to innovate independently while remaining within enterprise guardrails.

🎯 Citadel Governance Hub — Central Control Plane

The central governance layer with unified AI Gateway that all AI workloads route through.

Spoke Layer (Applications)

The spoke layer contains the actual applications and agents that implement business logic.

Key Components

Azure Container Apps Hosts all frontend, backendservices and agents with serverless scaling
Azure Cosmos DB : Stores feedback data, generated FAQS, alerts and workflow reports
Redis: Stores session data and conversational memory
Azure AI Search: Enables RAG-based FAQ generation and retrieval
Zendesk Integration: Source of customer support tickets and feedback
Jira Integration: Destination for automatically generated action items

Project Structure

FeedbackForge is composed of multiple components implemented in Python (backend) and TypeScript with React (frontend). The system is designed to be modular, allowing different execution modes depending on the use case.

Backend

The backend is built around a single Python project (feedbackforge) that can run in multiple modes. Each mode represents a different way of interacting with the system.

Operating Modes

FeedbackForge supports five main operating modes:

1. Chat Mode (DevUI)-Development Interface

An interactive chat interface for testing agents and workflows.

Best for: development, testing, and exploration
Default port: 8090
Command:

python -m feedbackforge chat

DevUI is a lightweight application built on the Microsoft Agent Framework that provides:

A web-based interface for interacting with agents
An OpenAI-compatible API backend
Tools for debugging and iterating on workflows

2. Serve Mode (AG-UI)-Production Server

A production-ready FastAPI server implementing the AG-UI protocol.

Best for: frontend integration and production deployments
Default port: 8081
Command:

python -m feedbackforge serve --port 8081

AG-UI (Agent User Interaction Protocol) is an event-driven protocol that standardizes communication between user-facing applications and AI agents. It enables:

Bi-directional communication
Streaming responses
Decoupling between frontend and backend

3. Workflow Mode-Batch Analysis

Executes the full multi-agent pipeline for large-scale feedback analysis.

Best for: scheduled processing of survey or feedback data
Command:

python -m feedbackforge workflow --max-surveys 50

4. FAQ Mode-RAG-Based FAQ Generation

Generates FAQs automatically from customer feedback using Azure AI Search.

Uses hybrid search (keyword + vector + semantic)
Applies clustering to identify common themes
Best for: customer support knowledge bases and documentation generation
Command:

python -m feedbackforge faq --days 7 --max-faqs 20

5. MCP Server Mode — External Integration

Runs a Model Context Protocol (MCP) server to integrate external feedback sources.

Best for: connecting to real systems like Zendesk, replacing mock data with production data and CI/CD and automation scenarios
Command:

python -m feedbackforge mcp

Action Planner

The Action Planner is an autonomous agent responsible for converting insights into actionable tasks. It integrates directly with systems like Jira to create and track issues.

Key Features

A2A Protocol → Seamless communication with other agents
Multi-platform support → Create tickets in Jira
Intelligent prioritization → Based on severity and impact
Auto-assignment → Suggests responsible teams
Effort estimation → T-shirt sizing (S/M/L/XL)
Traceability → Links tickets back to original feedback

Frontend

The frontend consists of two React applications.

FeedbackForge FAQ Viewer: A web interface for browsing auto-generated FAQs stored in Azure Cosmos DB. Designed for:

Customer support teams
End-user self-service

FeedbackForge Dashboard: A React application integrated with AG-UI for real-time interaction with agents. Features:

Streaming responses from agents
Interactive exploration of insights
Executive-level dashboard experience

Microsoft Agent Framework (MAF)

FeedbackForge is built using the Microsoft Agent Framework (MAF), a multi-language framework for building, orchestrating, and deploying AI agents.

MAF provides the foundational building blocks required to create both simple and complex multi-agent systems.

Core Capabilities

Agent runtime → Execution environment for agents
Model clients → Chat completions and response handling
Session management → Stateful conversations
Context providers → Memory and context injection
Middleware → Interception and customization of agent behavior
MCP clients → Integration with external tools and services
Workflow runtime → Multi-agent workflows with graph-based orchestration

Agents in FeedbackForge

FeedbackForge defines multiple specialized agents.

🤖 Core Agents

Action Planning Agent: Converts feedback insights into trackable Jira action items.
Dashboard Agent: Executive Dashboard Assistant for analyzing customer feedback.
Data Sync Agent: Syncs external feedback sources (Zendesk) into the data store.

🤖 Workflow Agents

Initial Orchestrator Agent: Validates survey data, assesses quality, plans analysis.
Data Preprocessor Agent: Cleans and prepares survey data.
Sentiment Analyzer Agent: Analyzes sentiment.
Topic Extractor Agent: Extracts topics.
Anomaly Detector Agent: Detects anomalies.
Competitive Intelligence Agent: Extracts competitor info.
Insight Miner Agent: Synthesizes analyses.
Priority Ranker Agent: Ranks priorities.
Action Generator Agent: Generates actions.
Report Generator Agent: Executive report generator which analyzes all the data provided and creates a comprehensive report.
Final Orchestrator Agent: Reviews results.

Each agent focuses on a specific responsibility, enabling modular and scalable processing.

Tools

The Microsoft Agent Framework supports many different types of tools that extend agent capabilities. Tools allow agents to interact with external systems, execute code, search data, and more. Tools are invoked dynamically by agents during execution, enabling them to move from reasoning to action.

Agent Tools

Action Planning Agent : Analyze Issue, Create Jira Ticket, Get Available Systems
Data Sync Agent : Zendesk via MCP, Check Sync Status
Dashboard Agent : Get Weekly Summary, Get Issue Details, Get Competitor Insights, Get Customer Context, Check for Anomalies, Set Alert, Generate Action Items, Escalate to Team, Create Action Plan, Run Workflow Analysis, Get Latest Workflow Report, Get Workflow Reports History

Example Tool: Get Weekly Summary

@ai_function
def get_weekly_summary() -> str:
    """Get weekly feedback summary with sentiment, top issues, and urgent items."""
    try:
        logger.debug("📊 Calling get_weekly_summary tool")
        summary = feedback_store.get_weekly_summary()
        result = json.dumps({
            "total_responses": summary["total_responses"],
            "sentiment": summary["sentiment"],
            "top_issues": [
                {"issue": i[0], "mentions": i[1], "priority": "P0" if i[1] > 40 else "P1" if i[1] > 25 else "P2"}
                for i in summary["top_issues"]
            ],
            "urgent_items": summary["urgent_count"],
        }, indent=2)
        logger.debug("✅ get_weekly_summary completed")
        return result
    except Exception as e:
        logger.error(f"❌ Error in get_weekly_summary: {e}", exc_info=True)
        return json.dumps({"error": str(e), "message": "Failed to retrieve weekly summary"}, indent=2)

MCP (Model Context Protocol)

MCP enables agents to interact with external systems as tools. In FeedbackForge, an MCP server is used as a tool for the Data Sync Agent to communicate with Zendesk. This MCP server uses SSE transport over HTTP and includes:

Tools : Fetch Zendesk Tickets, Ingest Feedback to Store, Create Zendesk Ticket
Resources : Structured data endpoints (e.g., feedbackforge://feedback/recent)
Prompts : Predefined reasoning templates (e.g., Analyze feedback trends, Generate executive summary)

A2A (Agent-to-Agent Protocol)

FeedbackForge uses the A2A protocol, an open standard designed to enable seamless communication and collaboration between AI agents. It enables the Dashboard Agent to communicate with the Action Planning Agent, which analyzes the issue, determines priority, category, and effort, and generates a Jira issue.

Below we can see how the Dashboard agent transparently calls the ActionPlanner agent telling to create a ticket from an action item.

Agent Cards

The A2A Protocol standardizes the data format shared during the discovery process via Agent Cards. These cards contain standardized descriptions that agents use to advertise their capabilities to other agents.

Example of an Agent Card (JSON format):

{
  "capabilities": {
    "pushNotifications": false,
    "streaming": true
  },
  "defaultInputModes": [
    "text"
  ],
  "defaultOutputModes": [
    "text"
  ],
  "description": "Converts customer feedback insights into trackable tickets in Jira",
  "name": "ActionPlanningAgent",
  "preferredTransport": "JSONRPC",
  "protocolVersion": "0.3.0",
  "skills": [
    {
      "description": "Analyzes customer feedback issues and creates actionable tickets with proper prioritization, categorization, and effort estimation. Supports Jira.",
      "examples": [
        "Create tickets for iOS crash issue affecting 45 users",
        "Analyze payment failure feedback and create action plan",
        "Generate tickets for login performance complaints"
      ],
      "id": "action_planner_ticket_creation",
      "name": "ActionPlanner",
      "tags": [
        "action-planning",
        "ticket-creation",
        "jira",
        "feedback",
        "agent-framework"
      ]
    }
  ],
  "url": "http://0.0.0.0:8084/",
  "version": "1.0.0"
}

Agent Discovery

For agents to collaborate, they first need to discover each other. A2A supports multiple discovery methods:

DNS-based Discovery
Registry-based Discovery
Private Discovery

In our case, we will use private discovery , which means directly configuring known agent endpoints through the already known ACA URL.

Task Processing Flow

The A2A protocol defines a standard flow for task processing between agents:

Communication Protocols

A2A is built on established communication protocols:

HTTP/HTTPS: For basic request/response communication
JSON-RPC: For structured method calls
Server-Sent Events (SSE): For streaming responses
JSON: As the standard data exchange format

Memory and Sessions

FeedbackForge, through the Microsoft Agent Framework (MAF), uses a strategy for storing and managing conversations through sessions in two modes: in-memory and persistent. The persistent mode uses short-term storage, which can be configured through TTL.

This ensures that when we refresh a page on the Dashboard UI, we do not lose the current conversations.

RAG and Azure AI Search

Retrieval Augmented Generation (RAG) is used to create FAQs based on common topics found in customer issues. It leverages Azure AI Search by creating a search index over the fields stored in Cosmos DB, supporting vector and semantic search capabilities.

For semantic search, the fields platform, sentiment and topics are used. Embeddings are generated for the text field using the text-embedding-3-small model. A hybrid search approach (keyword + vector + semantic) is still applied to achieve the best query accuracy.

# Use hybrid search for better accuracy
# Search for question/problem patterns
search_queries = [
    ("how to use feature", "usage questions"),
    ("why not working error", "error reports"),
    ("can I do this", "capability questions"),
    ("problem with issue", "problems"),
    ("confused about unclear", "clarity issues"),
    ("how do I configure", "configuration"),
      ("where is located", "navigation"),
      ("when will available", "availability"),
  ]

  all_feedback = {} # id -> feedback dict
  feedback_to_query = {} # id -> query that found it

  for query, category in search_queries:
      logger.info(f" Searching: '{query}' ({category})")

      # Use HYBRID search with Azure AI Search (keyword + vector + semantic)
      results = self.rag_client.hybrid_search(
          query=query,
          get_embeddings_func=get_embeddings,
          top=100,
          filters=time_filter
      )

      for result in results:
          feedback_id = result.get('id')
          text = result.get('text', '')

          # Only include question-like or problem feedback
          if self._is_question_like(text):
              if feedback_id not in all_feedback:
                  all_feedback[feedback_id] = {
                      'id': feedback_id,
                      'text': text,
                      'customer': result.get('customer_name'),
                      'segment': result.get('customer_segment'),
                      'platform': result.get('platform'),
                      'rating': result.get('rating'),
                      'timestamp': result.get('timestamp'),
                      'search_score': result.get('reranker_score', result.get('search_score', 0)),
                      'text_vector': result.get('text_vector'), # For clustering
                  }
              feedback_to_query[feedback_id] = category

  logger.info(f" Found {len(all_feedback)} question/problem feedback items")

Clustering is performed using vector similarity and cosine similarity on the embeddings of the feedback items. The themes variable in the code below is a list derived from the all_feedback dictionary, containing only question-like or problem-related feedback items that were retrieved through hybrid search.

 def _cluster_themes_with_vectors(self, themes: List[Dict]) -> List[Dict[str, Any]]:
    """
    Cluster similar themes using VECTOR SIMILARITY from Azure AI Search.

    This uses the actual embeddings stored in Azure AI Search for semantic clustering.

    Args:
        themes: List of theme dictionaries (with text_vector if available)

    Returns:
        List of clustered themes with counts
    """
    if not themes:
        return []

    logger.info(f" Clustering {len(themes)} items using vector similarity...")

    # Sort by search score (highest relevance first)
    themes = sorted(themes, key=lambda x: x.get('search_score', 0), reverse=True)

    clusters = []
    used_indices = set()
    similarity_threshold = 0.75 # Cosine similarity threshold

    for i, theme in enumerate(themes):
        if i in used_indices:
            continue

        # Create new cluster
        cluster = {
            'representative_text': theme['text'],
            'count': 1,
            'samples': [theme],
            'platforms': [theme.get('platform')],
            'segments': [theme.get('segment')],
            'avg_rating': theme.get('rating') or 3,
            'avg_search_score': theme.get('search_score', 0)
        }

        # Get embedding for this theme (if available)
        theme_vector = theme.get('text_vector')

        # Find similar themes using vector similarity
        for j, other in enumerate(themes):
            if j <= i or j in used_indices:
                continue

            # Use vector similarity if available
            is_similar = False
            if theme_vector and other.get('text_vector'):
                similarity = self._cosine_similarity(theme_vector, other['text_vector'])
                is_similar = similarity >= similarity_threshold
            else:
                # Fallback to text similarity
                is_similar = self._are_similar(theme['text'], other['text'], threshold=0.6)

            if is_similar:
                cluster['count'] += 1
                cluster['samples'].append(other)
                cluster['platforms'].append(other.get('platform'))
                cluster['segments'].append(other.get('segment'))
                used_indices.add(j)

        used_indices.add(i)

        # Calculate averages
        ratings = [s.get('rating') for s in cluster['samples'] if s.get('rating')]
        if ratings:
            cluster['avg_rating'] = sum(ratings) / len(ratings)

        scores = [s.get('search_score', 0) for s in cluster['samples']]
        if scores:
            cluster['avg_search_score'] = sum(scores) / len(scores)

        clusters.append(cluster)

    logger.info(f" Created {len(clusters)} clusters")
    return clusters

Workflows

The project uses MAF workflows, which are designed to handle complex business processes that may involve multiple agents, human interactions, and integrations with external systems.

Our survey analysis workflow can be executed in two ways: as a scheduled job on Azure Container Apps, or synchronously on demand from the dashboard.

All agents involved in the workflow and their responsibilities are described in the Agents section. The workflow uses sequential, fan-out, and fan-in patterns, as shown in the following image.

In this workflow, all issues stored in the Cosmos DB database are processed to generate an executive summary. In async mode, the result is returned in JSON format.

{
   "key_metrics": {
     "total_responses": 20,
     "sentiment_breakdown": {
   "executive_summary": "All 20 survey responses are unanimously negative, indicating a severe, platform\u2011wide iOS app failure triggered immediately after the iOS 17 update. Feedback highlights two dominant crash modes\u2014app launch and settings\u2011screen access\u2014supported by high-volume, repetitive reports across all customer segments, including high\u2011value enterprise accounts. Key themes include iOS 17 compatibility breakage, widespread functional instability, repeat crashes, and issues consistently tied to the settings screen. Anomalies such as identical crash patterns, uniformly low ratings, and repeated complaints signal an active crisis with P0-level urgency. No competitive threats or mentions were identified, confirming that the problem is strictly product\u2011related. Overall sentiment reflects total user frustration and high churn risk if remediation is delayed.",
       "positive": 0,
       "neutral": 0,
       "negative": 100
     },
     "top_topics": [
       "ios17_update_breakage: 8",
       "general_crashing_on_iphone: 4",
       "settings_screen_crashes: 6",
       "repeat_crashes_unacceptable: 2"
     ],
     "critical_count": 2,
     "high_priority_count": 2
   },
   "critical_issues": [
     {
       "issue": "App crashes immediately on launch for all iOS users after iOS 17 update",
       "priority": "P0",
       "impact": "Affects all iOS customer segments including enterprise accounts",
       "category": "ios17_update_breakage"
     },
     {
       "issue": "Settings screen consistently triggers crashes due to likely unhandled iOS 17 changes",
       "priority": "P0",
       "impact": "High-volume identical reports across users",
       "category": "settings_screen_crashes"
     },
     {
       "issue": "Repeated identical crash submissions indicate systemic failure and heightened customer frustration",
       "priority": "P1",
       "impact": "Multiple reports per user and wave of 1\u2011star ratings",
       "category": "repeat_crashes_unacceptable"
     },
     {
       "issue": "Lack of pre\u2011release compatibility testing for major iOS updates",
       "priority": "P2",
       "impact": "Increased likelihood of future outages and regressions",
       "category": "process_gap"
     }
   ],
   "recommendations": [
     {
       "action": "Deploy an emergency hotfix for iOS 17 launch and settings\u2011screen crashes",
       "priority": "Immediate",
       "owner": "Mobile Engineering",
       "rationale": "P0 failures blocking basic app access for all iOS users"
     },
     {
       "action": "Run targeted debugging on iOS 17 devices to identify memory, permissions, and deprecated API triggers",
       "priority": "Immediate",
       "owner": "Mobile Engineering",
       "rationale": "Likely compatibility issues introduced with iOS 17"
     },
     {
       "action": "Conduct full compatibility testing across iOS 17 devices and minor versions",
       "priority": "High",
       "owner": "QA",
       "rationale": "Prevent additional hidden crash paths"
     },
     {
       "action": "Publish user\u2011facing communication acknowledging the issue and outlining hotfix progress",
       "priority": "High",
       "owner": "Customer Support / Comms",
       "rationale": "Mitigate churn and reassure customers during platform\u2011wide outage"
     },
     {
       "action": "Implement pre\u2011release OS testing workflows for future major iOS versions",
       "priority": "Medium",
       "owner": "Mobile Engineering / QA",
       "rationale": "Addresses known process gap and prevents recurrence"
     },
     {
       "action": "Conduct proactive outreach to enterprise customers affected by the outage",
       "priority": "High",
       "owner": "Account Management",
       "rationale": "Prevent churn among high\u2011value accounts experiencing total app failure"
     }
   ]
 }

When called from the Dashboard UI, the system displays a human-readable response:

Observability

The Microsoft Agent Framework provides built-in support for observability, allowing you to monitor the behavior of your agents.

It integrates with OpenTelemetry, emitting traces, logs, and metrics, and uses the Azure Monitor exporter to send them through Application Insights.

We have the new (in preview) Agent Monitoring Dashboard in Microsoft Foundry:

Also a new brand Grafana view:

The following spans and metrics are created automatically:

Spans:

invoke_agent
chat
execute_tool

Metrics:

gen_ai.client.operation.duration (histogram)
gen_ai.client.token.usage (histogram)
feedback_processed (counter)
sessions_created (counter)
agent_executions (counter)
agent_duration (counter)
agent_framework.function.invocation.duration (histogram)

We also trace several operations by decorating methods with the @trace_operation attribute:

@trace_operation("memory.get_latest_workflow_report")
    def get_latest_workflow_report(self) -> Optional[Dict[str, Any]]:
        """Get the most recent workflow analysis report."""
        try:
            reports = self.get_workflow_reports(limit=1)
            return reports[0] if reports else None
        except Exception as e:
            logger.error(f"Failed to retrieve latest workflow report: {e}")
            return None

ACA (Azure Container Apps)

Azure Container Apps are used to deploy the entire system, including both backend and frontend components. Key benefits include simplified deployments and application management, built-in load-based scaling, and serverless scaling down to zero or revisions.

The following image shows a list of the deployed container apps:

Jobs

Two special Azure Container Apps are used in a cron-scheduled manner. These are called Azure Container Jobs.

Azure Cosmos DB

Cosmos DB is the primary database. It was chosen for its fast and productive application development, as well as its high availability, high throughput, low latency, and tunable consistency.

The system uses a database called feedbackforge with the following containers:

feedback: stores all the raw feedback items.
faqs: stores generated FAQs derived from feedback.
alerts: stores alerts created by the Dashboard Agent.
workflow_reports: stores the results of the survey workflows.

Coming Soon

📌In the next post, I’ll dig into Microsoft Foundry and AI Gateway.

👀 Follow me here on Medium to catch Part 2.

💻 Source code:

https://github.com/zodraz/feedback-forge

https://github.com/zodraz/ai-hub-gateway-feedbackforge

Tenark: Architecting a Scalable SaaS Multi-Tenant Platform with GitOps

Abel — Sat, 27 Sep 2025 19:27:12 +0000

A cloud native platform showcasing how AKS, Crossplane, Terraform, vClusters and ArgoCD enable scalable, secure, and efficient multi-tenant management with GitOps principles.

Introduction

This article presents a proof of concept (POC) for Tenark, a multi-tenant Software as a Service (SaaS) system designed as an online bookstore. The POC implements a SaaS control plane using the GitOps Bridge pattern, leveraging technologies like Azure Kubernetes Service (AKS), Crossplane, Terraform, Virtual Clusters (vCluster), and ArgoCD. This architecture provides a scalable, secure, and cost-effective solution for managing multi-tenant environments, balancing isolation and resource efficiency.

The goal is to demonstrate how modern cloud-native tools can simplify tenant provisioning, management, and application deployment while maintaining flexibility for future scalability, such as supporting premium offerings.

Use Case

In many enterprises, multi-tenant SaaS systems are critical for delivering scalable, service-centric solutions. Tenark addresses the need for seamless tenant creation, isolation, and management in a SaaS environment. As an online bookstore, Tenark allows customers to browse, purchase, and manage books, with features like inventory tracking, order processing, and personalized recommendations. The system supports enterprise needs for multi-tenant management with features like:

Security : Ensuring tenant data and operations are isolated and protected.
Tenant Provisioning : Streamlining the creation and configuration of new tenants.
Tenant Data Storage : Managing tenant-specific databases and caches.
Metrics, Logging, and Monitoring : Providing insights into system performance and usage.

A planned premium tier for high-value customers will offer exclusive books, fully isolated Kubernetes clusters for better performance and security, and advanced analytics. Though not implemented in this POC, this feature highlights the architecture’s flexibility.

Architecture Overview

The following diagram illustrates Tenark’s high-level architecture:

Key Components

GitHub and GitHub Actions : Hosts source code and automates deployment workflows for AKS, applications, and infrastructure.
Terraform Cloud : Provisions the main AKS cluster and bootstraps system components.
AKS : Hosts two node pools:
System Pool : Runs system components like ArgoCD and Crossplane.
User Pool : Hosts the SaaS Control Plane and Application Plane via vClusters.
Tenant Data : Managed in Azure Resource Groups, containing tenant-specific infrastructure like Redis caches and SQL Server databases.
Azure Platform Services : Includes Entra ID for identity management, Azure Monitor for observability, KeyVault for secrets, and DNS for routing.

These components are detailed in the following sections.

What is SaaS?

Software as a Service (SaaS) is a delivery model where software is hosted centrally and provided to customers over the internet on a subscription basis. Unlike traditional software, SaaS emphasizes a shared experience, reducing the need for customer-specific customizations. This approach lowers maintenance costs and enables rapid scaling.

For Tenark, SaaS means delivering a seamless bookstore experience to multiple tenants (e.g., individual users, libraries, or bookstores) while maintaining a single codebase and infrastructure core.

Multi-Tenancy in Tenark

Multi-tenancy allows a single application instance to serve multiple customers (tenants) while ensuring data and configuration isolation. Tenark employs a hybrid multi-tenancy model, balancing shared and isolated components based on business and performance needs.

Multi-Tenancy Approaches

Common multi-tenancy strategies include:

Single Application, Single Database : All tenants share one application and database.
Single Application, Multiple Databases : One application, but each tenant has a dedicated database.
Multiple Applications, Single Database : Each tenant has a dedicated application instance sharing a database.

Tenark combines these approaches for different components:

SaaS Control Plane:

✅ Deployment : Shared across all tenants in a single vCluster.

✅ Database : Shared database in a common Azure Resource Group.

SaaS Web Portal :

✅ Deployment : Shared across all tenants in a single vCluster.

✅ Database : Shared database in a common Azure Resource Group.

SaaS Identity Server :

✅ Deployment : Shared across all tenants in a single vCluster.

✅ Database : Shared database in a common Azure Resource Group.

Tenant Microservices (Ordering, Inventory, Recommendation):

✅ Deployment : Each microservice per tenant runs in a dedicated vCluster.

✅ Database : Separate databases per tenant and service in distinct Azure Resource Groups, with a shared Redis cache per tenant.

Benefits of Tenark’s Approach

Enhanced Security: Dedicated vClusters and isolated databases for tenant-specific microservices ensure robust data separation and protection against unauthorized access.
Data and Performance Isolation : Tenant-specific microservices and databases prevent “noisy neighbor” issues, especially for resource-intensive services like recommendations.
Cost Efficiency : Shared components reduce maintenance overhead.
Scalability : The architecture supports premium tiers with fully isolated clusters for high-value tenants.
Flexibility : A common codebase simplifies updates and feature rollouts.

For example, the recommendation microservice, which may experience usage spikes, benefits from isolated deployments to ensure performance stability across tenants. By running in dedicated vClusters, each tenant’s recommendation engine operates independently, preventing one tenant’s high demand (e.g., during a promotional campaign) from impacting others. This isolation also allows for tenant-specific customization, such as tailored machine learning models, without affecting the shared infrastructure, and ensures dedicated resource allocation for consistent response times.

GitOps Bridge Pattern

The GitOps Bridge pattern, a community-driven best practice, streamlines Kubernetes cluster provisioning and management using GitOps principles. It integrates tools like Terraform, ArgoCD, and Crossplane to automate infrastructure and application deployments.

This was originally created for AWS, but as a Cloud Native pattern can be extended to any HyperScaler.

In Tenark, the GitOps Bridge pattern provisions the AKS control plane and orchestrates tenant-specific infrastructure and applications.

Control Plane

Tenark leverages the GitOps Bridge pattern to establish a primary AKS control plane, which orchestrates the deployment of all infrastructure and applications required for the system. This main control plane, hosted on an Azure Kubernetes Service (AKS) cluster, serves as the central management layer, coordinating resources and ensuring seamless operation of the SaaS platform. It is based on the AKS Platform Engineering project.

Microsoft and AWS provide specific guidelines for building SaaS control planes. In Tenark, it’s critical to distinguish that the AKS control plane acts as the global orchestrator, managing all deployments. Within these deployments, we define two key SaaS components: the SaaS Control Plane, which handles tenant management, and the SaaS Application Plane, which delivers the user-facing experience. These components, detailed in the following sections, operate within the broader AKS control plane, leveraging tools like ArgoCD and Crossplane to automate and streamline their deployment.

SaaS Control Plane

The SaaS Control Plane is foundational to any multi-tenant SaaS model. It provides a centralized set of services that enable operators to manage and operate all tenants through a single, unified interface, regardless of the underlying deployment or isolation model. This control plane handles critical tasks like tenant provisioning or user authentication ensuring a consistent and scalable experience for managing the SaaS environment.

This control plane can be visualized as shown in the following diagram:

Implementation

Tenark’s SaaS Control Plane is implemented via the Tenark.Web.ControlPlane administration application, built using the ABP.io framework.

ABP.io is an open-source framework for building modular, multi-tenant applications with features like:

Built-in multi-tenancy support with UI for tenant management.
Role-based access control (RBAC) and user management.
Extensible product and service management.

The Tenark.AuthServer , based on IdentityServer , handles the identity, admin user management, authentication and authorization, ensuring secure tenant and user access.

All of these components are deployed in a shared vCluster named tenant-common. Below we can see the isolation for all the pods on just that vCluster.

Future Enhancements

While the POC focuses on core functionality, future iterations could include:

Onboarding : Automating tenant creation with predefined YAML templates and connection strings.
Billing Integration : Supporting subscription models for premium tiers.
Metrics and Monitoring : Integrating Azure Monitor for tenant-specific analytics.

Onboarding, while ideal, requires complex dynamic automation (e.g., generating tenant-specific YAMLs, configurations and infrastructure). For this POC, operators manually configure tenant properties, which is sufficient for demonstration purposes.

SaaS Application Plane

The SaaS Application Plane is central to delivering the user-facing functionality in a multi-tenant SaaS model. It provides the services and interfaces that enable tenants to interact with the application, ensuring a seamless and customized experience while leveraging the underlying multi-tenant architecture.

This application plane can be visualized as shown in the following diagram:

Implementation

The Tenark.Web.Portal implements the SaaS application which consumes a set of tenant-specific application services, implemented as microservices running as Kubernetes pods. Each microservice operates in a dedicated vCluster, ensuring complete isolation between tenants, as detailed in later sections. These microservices include:

Inventory : Manages book stock and availability, ensuring accurate tracking of inventory for each tenant.
Ordering : Processes purchases and order tracking, handling tenant-specific transactions.
Recommendation : Generates personalized book suggestions using tenant-specific data, leveraging isolated compute resources for performance.

Each microservice is supported by its own SQL Server database and a shared, tenant-specific Redis cache for optimized performance.

And as before we can all the pods deployed in an isolated vCluster with only the needed services for this application plane.

Virtual Clusters

Virtual Clusters (vClusters) are lightweight, isolated Kubernetes clusters running within a single physical AKS cluster. Each vCluster has its own API server, providing stronger isolation than namespaces without the overhead of separate clusters.

Benefits

Robust Isolation :

✅Granular permissions per tenant.

✅Isolated control planes and networking.

✅Customizable security policies.

Cost Efficiency :

✅Lightweight infrastructure reduces resource consumption.

✅Simplified management compared to dedicated clusters.

Scalability :

✅Reduced API server load.

✅Conflict-free Custom Resource Definition (CRD) management.

Flexibility :

✅Supports diverse Kubernetes environments.

✅Runs on any Kubernetes-compatible platform.

Tenark uses vClusters for:

Tenant-Common : Hosts shared components like the SaaS Control Plane and Web Portal.
Tenant-Specific : Isolates microservices (Ordering, Inventory, Recommendation) per tenant.

ArgoCD

ArgoCD is the GitOps engine for deploying applications and infrastructure. It uses ApplicationSets and Kustomize manifests to manage tenant deployments across environments (Dev, QA, Prod). For the POC, only the Dev environment is implemented, but the system is designed for multi-environment support.

Example: ApplicationSet for Tenant Deployment

The following ApplicationSet deploys the tenant-common vCluster and tenant-specific vClusters (tenant1, tenant2, tenant3) using a matrix generator:

This setup allows operators to add or remove tenants by updating the elements list, ensuring flexibility in tenant management.

Kustomize Overlay

The Kustomize overlay defines environment-specific configurations, such as the Dev environment:

Tenant Folder Structure

Each tenant (e.g., tenant2) and the tenant-common follows a Kustomize-based folder structure for environment-specific deployments:

Terraform

While most infrastructure in Tenark is provisioned through Crossplane, the initial setup of the AKS Control Plane, which hosts Crossplane and other system components, is created using Terraform via the GitOps Bridge pattern. This pattern enables the provisioning of the AKS cluster with all necessary add-ons, such as ArgoCD and Crossplane, to support the SaaS platform.

Tenark leverages Terraform Cloud , a managed service platform designed to streamline Terraform usage in collaborative, team-oriented environments. Terraform Cloud enhances team workflows with features like state management and automated runs, though the system could also operate with the standard version of Terraform if needed.

Policies

Terraform Cloud includes Sentinel , a policy-as-code tool that enforces rules to control what users of HashiCorp products can do. Sentinel proactively prevents unauthorized changes by validating infrastructure configurations before deployment. In Tenark, Sentinel policies enforce requirements such as mandatory tags on all Azure resources, ensuring compliance and consistency across the infrastructure.

Crossplane

To create all the infrastructure resources needed for Tenark (except for the AKS Control Plane), Crossplane is used, which is a Kubernetes-native control plane framework for platform engineering. Crossplane enables the creation of custom control planes to manage cloud-native software, allowing the design of APIs and abstractions that users interact with to provision and manage infrastructure.

This notion of a control plane differs from the main AKS control plane and the SaaS Control Plane but similarly serves as a component that manages and orchestrates the delivery of the desired infrastructure in Tenark’s scenario. This aligns with the Kubernetes concept of a control plane, which oversees resource management.

A key advantage of Crossplane is its use of a reconciliation loop, leveraging Kubernetes to continuously ensure infrastructure matches the desired state. Unlike Terraform’s push-based model, which requires explicit terraform plan and apply commands and state management, Crossplane automates this process, eliminating the need for such manual operations.

Crossplane uses YAML as its declaration language and is integrated with ArgoCD to deploy these configurations seamlessly.

Managed Resources

A managed resource in Crossplane represents an external service in a provider, in this case, the Upbound Azure Provider. Upbound, the primary maintainer of Crossplane, offers providers for various platforms, including AWS and Google Cloud. For Tenark, managed resources define Azure services like Redis caches and SQL databases.

Here is an example of a Redis cache configuration:

Compositions

Crossplane’s Compositions are a powerful feature, allowing multiple Kubernetes resources to be represented as a single Kubernetes object. Composite resources are created when users interact with a custom API defined in a CompositeResourceDefinition.

Tenark uses compositions to create the common tenant infrastructure stack, which includes a SQL Server database and a Redis cache. An example of such a composition is shown below:

Lessons Learned

GitOps Bridge Pattern : Simplifies AKS provisioning and integrates seamlessly with ArgoCD and Crossplane.
vClusters : Provide robust isolation for multi-tenancy, balancing cost and performance compared to namespaces or dedicated clusters.
ArgoCD and vClusters : Enable flexible tenant management with minimal configuration changes.
Crossplane Reconciliation : Offers powerful automation but requires time to stabilize; occasional sync delays were observed.
Crossplane Compositions : Complex to design but really helpful for abstracting infrastructure stacks. Tools like ClaudeCode assisted in their creation.
Crossplane Learning Curve : Steeper than Terraform due to its Kubernetes-native approach, but its reconciliation model reduces operational overhead.

Git Repositories

https://github.com/zodraz/tenark-identity-server

https://github.com/zodraz/tenark-services

https://github.com/zodraz/tenark-webs

https://github.com/zodraz/tenark-gitops-bridge

OmniSync: Dynamics 365 Integration with Salesforce and Fabric (Part 4)

Abel — Sun, 11 May 2025 13:23:47 +0000

A hands-on walkthrough of syncing Dynamics 365 using Dataverse, PowerApps, Plugins, and Azure Logic Apps as part of a multi-platform integration with Salesforce and Microsoft Fabric.

Posts in this Series

OmniSync: A Real-World Architecture for Syncing Salesforce, D365, and Fabric in Near Real-Time (Part 1)
OmniSync: Real-Time Lakehouse, Spark Streaming, and Power BI in Microsoft Fabric (Part 2)
OmniSync: Integrating Salesforce with Microsoft Fabric and Dynamics 365 (Part 3)
OmniSync: Dynamics 365 Integration with Salesforce and Fabric (Part 4)(This Post)

Introduction

Dynamics 365 Sales was used as the second CRM in the OmniSync integration architecture, synchronized alongside Salesforce. While both platforms offer rich and overlapping CRM functionality, Dynamics 365 was chosen for this role due to its tight integration with Microsoft ecosystem on Power Platform, Dataverse and its extensive market usage.

As with Salesforce, the goal wasn’t to cover the full feature set, but rather to align a core retail centric subset of entities across systems.

Retail Entities & Tradeoffs

Instead of using Dynamics 365 Commerce (the retail vertical specialized product with features like POS, checkout, payment features amongst others), we used the standard Sales module , which still offered the necessary capabilities with the exception of Stores, which we added manually.

To simplify the implementation and reduce system coupling, several tradeoffs were made, much like in the Salesforce setup:

Orders were created directly, bypassing Opportunities or Leads for simplicity.
Order Numbers are aligned manually with Salesforce to avoid complex sequencing logic.
Product Custom Fields were used (e.g., Size, Weight, Brand) avoiding the built-in Product Properties model.
Price Books include standard pricing and cost fields and Price List Items were not used.
Product Families and Product Groups were skipped, instead, categories were stored as simple product-level fields.
Discounts and many optional fields were excluded from synchronization.
Multi-currency support was simplified by using EUR only.
Unused fields were ignored to keep the system lightweight and avoid unnecessary sync complexity.

Customization

PowerApps & Dataverse

Dynamics 365 Sales is built on top of Microsoft’s Power Platform , which includes Dataverse (as the backend database) and PowerApps for customizing tables and UI. This architecture allows for easy and scalable customization without needing to write full applications.

In our case, Dynamics 365 didn’t include a Retail Store entity by default, since it’s part of more specialized modules like D365 Commerce. So we created it from scratch using Dataverse and customized its forms, menus, and behavior through PowerApps.

Dataverse Table

Using the PowerApps interface, we defined a new Retail Store table in Dataverse with all necessary fields. This is done visually through a form-based interface where you can drag, drop, and define field types quickly.

You can also use Quick Create for a faster setup, or even Copilot , which suggests fields based on prompts accelerating the initial setup.

Forms

After creating the table, PowerApps automatically generates default forms and views, which can then be tailored to fit your needs.

This is part of building a Model-Driven App , where the UI is dynamically generated based on the underlying data structure in Dataverse. You can adjust layouts, control visibility, define validation rules, and group fields as needed.

A customized form for the Retail Store form was created and later updated, with clearly organized sections for General Info and a brand new customized Address Details tabs.

Views

Views were also created to define how lists of records are presented. Those are displayed in a table or grid format with options for sorting, filtering, column selection, and search functionality.

Menus

To make this new entity usable within the Dynamics 365 Sales Hub, we added it to the app’s main navigation on the left pane. This lets users browse and manage retail stores directly, just like any standard CRM entity.

In addition, a Quick Create menu was set up to allow rapid record creation from anywhere in the app, streamlining the user experience.

UI Result

All these configurations resulted in a fully functional and visually integrated Retail Store form inside Dynamics 365. Users can now view, create, and edit these records natively, just like standard entities like Accounts or Products.

In-App Notifications

Logic was added to display a notification when a synchronization conflict is detected between Salesforce and Dynamics 365.

This is handled by a JavaScript function tied to the OnLoad event of the form. It checks the value of a hidden field Status_Sync. If the value is “Conflict”, a warning banner is displayed at the top of the form, and the field becomes visible for transparency.

This helps inform users that a manual review or correction should be needed to review and fix.

function SyncStatus(executionContext) {
    try {
        var formContext = executionContext.getFormContext();
        var status = formContext.getAttribute("omnisync_syncstatus").getValue();
        var fieldControl = formContext.getControl("omnisync_syncstatus");

        if (status === "Conflict") {
            formContext.ui.setFormNotification(
              "There has been a Conflict Syncing. Review the fields or contact the Administrator!", 
              "WARNING", 
              "conflictNotification"
            );
            fieldControl.setVisible(true);
        } else {
            formContext.ui.clearFormNotification("conflictNotification");
            fieldControl.setVisible(false);
        }
    } catch (error) {
        console.error("Error in displayFormNotification: ", error.message);
    }
}

Plugins

To maintain feature parity with Salesforce, we implemented a Dataverse Plugin to calculate the Sales Order Line Number on creation.

Plugins in Dataverse are written in C# and deployed to execute during specific events, like Create or Update. In our case:

The plugin runs in the Post Operation stage (after the record is created).
It is configured as Synchronous , so the line number is set immediately and is available in subsequent operations.

This mirrors the logic used in Salesforce and ensures consistency across systems.

Plugins are powerful tools, and for developers with a C# background, they provide a familiar and efficient way to enforce server-side business logic that goes beyond what low-code flows can achieve.

Logic Apps

As with the Salesforce integration, Logic Apps serves as the central iPaaS (Integration Platform as a Service) for Dynamics 365.

A Dataverse trigger which relies on the Dataverse connector , on the When a row is added, modified or deleted event is used. The action type (Insert, Update, or Delete) is then filtered within Logic Apps and handled by three separate Logic Apps , each dedicated to a specific type of change.

Why This Approach?

This design decision ( to split Logic Apps by action type ) was based on challenges encountered with Dataverse’s webhook-based triggering system like:

Some change events were duplicated
Others were missed entirely

By assigning one Logic App per change type (Insert, Update, Delete), the integration became far more stable and predictable. Additionally, this setup makes it easier to integrate Change Data Capture (CDC) logic for forwarding events to Microsoft Fabric via Event Hub.

Example: Insert — Order Product (Sales Order Line Item)

Let’s walk through a typical Logic App that listens for Order Product (Sales Order Line item) inserts:

Trigger The Logic App is triggered when a new Order Products record is inserted in Dataverse.
Integration User Check A condition checks if the change originated from an integration user to avoid triggering synchronizations from system to system updates.
Retrieve Related Entities If valid, the Logic App retrieves related records: the product, the parent order, the synced Salesforce Order from the mapping table
Parallel Branching Two parallel operations begin: Send CDC to Fabric through Event Hub and Create Salesforce Order Product if not already synced
Conflict Check Before inserting in Salesforce, it checks if a line item already exists (via MasterDataMapping). If so, it sets the Sync_Status to conflict and exits.
Salesforce Item Creation If not in conflict, the Salesforce PriceBookEntry is fetched, and the Order Product is created using the Salesforce connector.
Sending CDC Finally, a new CDC record is generated and sent to Fabric to reflect this change in the Lakehouse system.

Data Transformations

Just like in the Salesforce integration, data transformations are a key part of the Logic Apps workflows. These ensure each system receives the correct structure and format of data.

Liquid Templates for Fabric CDC

To construct CDC messages for Fabric, like it was done in the SalesForce integration Liquid templates were used. These templates convert Dataverse outputs into the JSON schema expected by Fabric’s Event Stream.

Here’s an example template for syncing a Retail Store ** ** entity:

{
  "Operation": "{{ content.SdkMessage | capitalize }}",
  "Entity": "Store",
  "Values": {
    "StoreCode": "{{ content.cr989_storecode }}",
    "CustomerKey": "{{ content._cr989_account_value }}",
    "StoreTypeID": "{{ content.cr989_storetype }}",
    "StoreType": "{{ content._cr989_storetype_label }}",
    "StoreName": "{{ content.cr989_storename }}",
    "StoreDescription": "{{ content.cr989_storedescription }}",
    "StorePhone": "{{ content.cr989_storephone }}",
    "StoreFax": "{{ content.cr989_storefax }}",
    "AddressLine1": "{{ content.cr989_addressline1 }}",
    "EmployeeCount": "{{ content.cr989_employeecount }}",
    "Latitude": "{{ content.address1_latitude }}",
    "Longitude": "{{ content.address1_longitude }}",
    "CreatedDate": "{{ content.createdon }}",
    "UpdatedDate": "{{ content.modifiedon }}",
    "D365Id": "{{ content.ItemInternalId }}"
  },
  "CreatedDate": "{{ "now" | date: "%Y-%m-%d %H:%M" }}",
  "UpdatedDate": "{{ "now" | date: "%Y-%m-%d %H:%M" }}"
}

This lets us dynamically construct messages that Fabric can ingest through Spark Streaming, keeping the transformation logic declarative and lightweight.

Direct Mapping via Connectors

While Liquid templates are used for constructing CDC payloads to Fabric, most of the remaining data transformations are done directly via Logic Apps connectors , particularly when syncing between Dataverse and Salesforce.

Because these connectors expose the full set of fields and metadata, mapping becomes a matter of:

Mapping fields from source to destination
Applying expression functions when necessary (e.g., formatting dates, converting units)
Structuring nested data (e.g., lookups or relationships)

This approach keeps the integration visual, low-code, and easy to maintain, especially when no complex transformation logic is required.

Deletions

Handling deletions in Dynamics 365 introduces a particular challenge: you don’t automatically get information about who deleted the record in the Logic App trigger (i.e., there’s no DeletedBy field in the payload).

This creates a problem, especially in a system like OmniSync, where we need to distinguish between:

Deletions made by users
Deletions triggered by integration logic

Without that information, the system could potentially enter a sync loop or process deletions unnecessarily. To prevent this, all Logic Apps include a consistent check to determine whether the change came from an Integration User.

Solution: Auditing via Audit Table

To work around this limitation, the Dataverse Audit table was used. By enabling auditing on the entities you want to monitor, you can retrieve historical changes, including who performed a Delete , Insert , or Update.

The process works like this:

Enable auditing for each table where you want to track deletes or updates.
After a deletion trigger fires, query the Audit table to identify the actor behind the change.
If the user is an integration identity, the Logic App will skip further action to avoid recursion.

This same pattern is also applied to Insert and Update operations, helping reinforce idempotency and cycle prevention across the system.

Viewing Audit Logs

You can quickly inspect audit logs using the Audit Summary View in Power Platform’s admin interface.

For more detailed filtering and querying, using FetchXML is a better option, which we’ll explore in an upcoming section.

OmniSync Configuration Table

To avoid repetitive lookups and keep Logic Apps as streamlined as possible, a central configuration table was introduced in Dataverse: OmniSyncConfiguration.

This table holds key constants and settings used across the sync process, for example:

Dynamics 365 Integration User ID
Salesforce Standard Price Book ID
Other sync-related identifiers or constants that would otherwise require querying each time

Instead of hardcoding these values into Logic Apps (which can be harder to maintain), those are stored once and reused so can be used wherever needed via simple lookups.

This approach:

Keeps the logic clean and centralized
Improves performance by reducing connector calls
Makes the solution easier to maintain or extend in future

Tools Used in Dynamics 365 Integration

To support the setup, customization, and data management of Dynamics 365 during the OmniSync project, several key tools were used, most notably from the XrmToolBox.

XrmToolBox

XrmToolBox is a Windows-based tool that connects to Dataverse and offers over 30 built-in plugins, along with more than 300 additional plugins available for installation, supporting tasks ranging from customization and configuration to data manipulation.

It’s an essential tool for speeding up admin and integration tasks, especially in environments like Dynamics 365 where multiple entities and relationships are involved.

FetchXML Builder

This XrmToolBox plugin was incredibly helpful for exploring Dataverse tables quickly. With FetchXML , you can:

View rows and filter by field values
Select only relevant columns
Inspect relationships between entities visually

FetchXML is an XML-based query language for Dataverse that allows users to retrieve, filter, and sort data. It’s commonly used in Power Platform tools for advanced data querying and working with entity relationships.

While Power Apps Studio offers similar features, FetchXML Builder is far faster and more powerful when you’re exploring the data model or debugging issues.

SQL 4 CDS

For more advanced queries, SQL 4 CDS lets you write and run familiar SQL-style queries (SELECT, UPDATE, INSERT, DELETE) directly against your Dataverse instance.

This is perfect when working with complex joins or bulk-edit scenarios, and ideal if you’re already comfortable with SQL.

Configuration Migration Tool (CMT)

To prepare the system for deployment and migration across environments, we used the Configuration Migration Tool (CMT) via the Power Platform CLI using the command:

pac tool cmt

This tool lets you:

Export schema and data from your current Dataverse environment
Re-import that structure into a new one (e.g., a QA or Production instance)

This was essential for seeding initial values like Stores, Accounts, especially to keep the systems aligned with Salesforce and Fabric.

⚠️While this project used a single environment, in a multi-environment setup (Dev/QA/Prod), each system would need coordinated seed data to prevent mismatches.

There are other of plenty plugins within XMlToolBox to do this task too, so you have lots of choices here.

Security

As mentioned in earlier parts of this series, an Integration User pattern is used across platforms to avoid synchronization loops and isolate system activity from human interaction.

In Dynamics 365, this same approach was applied. A dedicated integration user account was created and assigned a custom security role called Integration Role. This role was created using the least privilege principle , granting only the necessary permissions to access and modify the required entities (e.g., Accounts, Orders, Products, Retail Stores).

Client Applications (Microsoft Entra)

Two client applications were registered in Microsoft Entra ID to handle authentication for external services:

Azure Functions — Salesforce Updates

These apps are responsible for pushing updates to Salesforce (CDC responses) and were covered in detail in the previous article on SalesForce.

GitHub CI/CD Integration

This app is used to authenticate GitHub workflows during the CI/CD pipeline for deploying changes to Dynamics 365.

It will be described in more detail in the next section.

Git CI/CD Solutions

All Dynamics 365 customizations and configurations are stored in a Power Platform Solution , which is the standard packaging format for Dataverse based apps. This solution format allows you to version, export, and deploy your app components consistently across environments.

The source code for the solution is tracked in GitHub , enabling CI/CD automation.

Deployment Pipeline

A basic CI/CD setup was created using GitHub Actions , following a simple flow:

Export to Git : Manual actions export the solution from Dynamics 365 to our GitHub source code
Pull Request Workflow : Changes are reviewed and merged into the main or master branch via a pull request
Deployment to Production : Once merged, GitHub Actions deploy the solution to a target environment (e.g., Production) using managed solution packaging

⚠️ Important Notes:

This pipeline requires a non-trial environment , as GitHub Actions create managed solutions , which aren’t supported in trial instances.
These actions do not include data by default. If you need to migrate seed data (e.g., stores, categories, users), use a tool like the Configuration Migration Tool (CMT) described earlier.

Monitoring

As detailed in the Salesforce article, all OmniSync components are monitored through Azure Monitor , with centralized logs and metrics available via a Log Analytics Workspace.

This approach offers both platform-native observability and extended diagnostics when needed.

Logic Apps Monitoring

Logic Apps include built-in run history tracking. From the Azure Portal, you can easily review:

Trigger invocations
Action steps
Success/failure details
Execution timings

This is particularly useful for spotting transient failures, checking retry logic, or auditing historical executions.

Plugin Trace Viewer (Power Platform)

To monitor plugin executions (like those used for setting Sales Order Line Numbers), we used the Plugin Trace viewer in Power Platform.

This built-in viewer lets you:

Inspect plugin events
See exceptions and execution order
Trace variable values and processing time

Plugin Trace Viewer (XrmToolBox)

For more advanced filtering and visibility, we also used the XrmToolBox Plugin Trace Viewer. Compared to the default Power Platform view, this plugin allows:

Full-text search across logs
Time range filters
Easy export and comparison

Application Insights Limitation (Trial Environment)

Since this OmniSync instance used a Trial Dataverse environment , integration with Application Insights wasn’t supported.

In a production or managed solution scenario, Application Insights could be used for deeper monitoring, telemetry, and diagnostics, including tracking performance metrics, usage patterns, and exceptions.

Lessons Learned

Working with Dynamics 365 as part of the OmniSync architecture highlighted several important technical and practical takeaways, especially in the context of integrating with external systems like Salesforce and Fabric.

UI Customization: one of the best surprises I found in this project was how easy it was to customize screens and forms in Dynamics 365 using PowerApps and Dataverse. Creating entirely new entities like Retail Stores from scratch complete with custom fields, forms, and navigation menus experiences was entirely visual, fast to iterate, and required no code.The Power Platform’s integration with Dataverse makes it seamless to link UI components directly to backend tables, and define user flows with precision, all from the same interface.
Permissions: even with a minimal integration user role, fine-tuning permissions over time was necessary, especially for less obvious operations, such as reading dynamic product properties or creating order-related entities. For example, enabling privileges like prvReadDynamicProperty wasn’t something that was immediately apparent. Despite this, using a least-privilege model for the integration user proved to be a solid approach, although it did require iteration and updates whenever permission related errors occurred.
Complex Lookups: for simple entities, Logic Apps with basic field mapping worked well. But more complex scenarios like Sales Order syncing required multi-step lookups to fetch like previously synced entities from the MasterDataMapping table related values like PriceBook entries or foreign key references like Account or Product IDs. Trying to avoid these lookups often led to loss of accuracy or sync integrity. In the end, it was better to embrace complexity where necessary and structure the workflows accordingly.
Dataverse plugins: provided a robust way and easy way through C# to implement logic (like calculating line numbers) where out-of-the-box workflows fell short.
Tools: XrmToolBox and its plugins FetchXML Builder and SQL 4 CDS dramatically accelerated development and debugging. Without them, exploring relationships, building queries, or inspecting tables would have taken much longer through standard web interfaces.

Final Thoughts and What’s Next

This concludes the current series of articles. While this marks the end for now, there may be a near future installment expanding on the topics covered, potentially including a video and an integration with SAP. Stay tuned for updates!

👀 Follow me here on Medium

💻 Source code: https://github.com/zodraz/omnisync-d365

OmniSync: Integrating Salesforce with Microsoft Fabric and Dynamics 365 (Part 3)

Abel — Tue, 06 May 2025 12:40:12 +0000

A real-world walkthrough of custom objects, outbound messages, CDC, and Logic Apps used to sync Salesforce with Fabric and Dynamics 365.

Posts in this Series

OmniSync: A Real-World Architecture for Syncing Salesforce, D365, and Fabric in Near Real-Time (Part 1)
OmniSync: Near Real-Time Lakehouse, Spark Streaming, and Power BI in Microsoft Fabric (Part 2)
OmniSync: Integrating Salesforce with Microsoft Fabric and Dynamics 365 (Part 3) (This Post)
OmniSync: Dynamics 365 Integration with Salesforce and Fabric (Part 4)

Introduction

This is Part 3 of the OmniSync series, where we deep dive into how Salesforce was integrated with Microsoft Fabric and Dynamics 365 for near real-time data synchronization.

While previous parts focused on the overall architecture and Fabric implementation, this post explains Salesforce specific challenges and solutions including:

How custom objects and fields were used to fit a retail oriented model
What tricks were needed to work around Salesforce limitations
How Outbound Messages, Change Data Capture, and Logic Apps were combined to trigger and route changes
And finally, how this all connects to the rest of the OmniSync ecosystem

Salesforce and Entity Mapping

OmniSync uses Salesforce as the retail-facing front end, but instead of relying on the highly specialized vertical like Consumer Goods Cloud, it was opted for a more flexible approach: starting from the Standard Salesforce model and tailoring it to fit retail and integration needs.

Since this is a Proof of Concept (PoC), the focus was on simplicity and interoperability, particularly with Dynamics 365 and Microsoft Fabric. That meant reshaping and mapping a core set of entities without introducing unnecessary complexity.

Core Entities and Mapping

A lightweight model centered around real-world retail flows was defined:

Accounts : Representing customer companies
Products : Standard Salesforce products, enhanced with custom attributes
Orders and Order Lines : Standard Salesforce products that reflect actual retail sales orders
Retail Stores : Stores for physical locations
Geography : Used in addresses in Standard Salesforce objects and expanded on Fabric
Currencies and Categories: Mapped internally for simplicity

Changes and Tradeoffs

A brand new custom Retail Store object (Retail_Store__c) was created to represent physical store locations
Store Assortments (which products are available in which stores) were excluded from this version and treated as out of scope
Orders were created directly without going through Opportunities or Leads to simplify the sales flow, although you can use that business workflow if you want anyway
Products were enhanced with attributes like Units of Measure (Size, Weight), Brand , Color , and lifecycle fields such as Available For Sale Date
Only a Standard Price Book was used, which includes both the selling price and a cost price field
Product Categories relied on the existing Product Family structure, without using subcategories like those defined in Fabric
Discounts , promotions, and complex pricing logic were excluded for simplicity
Although the ecosystem supports multiple currencies, we standardized everything on EUR to avoid conversion logic
Many standard Salesforce fields were left optional or unused , and excluded from synchronization logic

This approach helped streamline the implementation and keep the data model focused. It also ensured that only relevant fields were tracked and synchronized between systems, reducing noise and increasing maintainability.

Custom Fields

To support the integration logic and ensure proper mapping across systems, we added several custom fields to key Salesforce entities. Most of these fields help enforce unique codes, synchronization statuses, or carry metadata not available in the standard schema.

These fields are used mainly for mapping and syncing with external systems especially in cases where a specific code or identifier must be sent.

Field Overview per Entity

✅ Account

Email: Reference for communication
SyncStatus__c: Sync conflict state

✅ Product

Size__c, SizeUnitOfMeasure__c, SizeUnitOfMeasureId__c: Physical dimension details and its unit of measure (name+identifier)
Weight__c, WeightUnitOfMeasure__c, WeightUnitOfMeasureId__c: Weight and its unit of measure (name+identifier)
Brand__c, Class__c, ClassId__c, Color__c, ColorId__c: Classification data used for categorization(name+identifier)
AvailableForSaleDate__c, StopForSaleDate__c: Lifecycle Sale dates indicators
SyncStatus__c: Sync conflict state

✅ Price Book

CostPrice__c: Price at which the product was bought

✅ Order

SyncStatus__c: Sync conflict state
RetailStore__c: Connects the order with a physical store

✅Order Line (Order Product)

OrderItemLineNumber__c: Autonumeric line item number

✅Retail Store (Retail_Store__c custom object)

StoreCode__c: A unique identifier for the retail store used for system integration
Name: The name of the retail store as displayed to users
Description: A free-text field providing additional information about the store
EmployeeCount: Total number of employees currently working at the store location
Fax: The store’s fax number
Phone: The store’s main contact phone number
StoreType__c: (Picklist) Categorization of the store type (e.g., Store, Catalog, Online, Reseller)
StoreTypeId__c: Identifier for the StoreType__c field
Account__c: (Lookup) A reference to the Account associated with this store.
Address: The store’s physical address, including street, city, state, postal code, and country
SyncStatus__c: Sync conflict state

Triggers

We use Salesforce triggers in two main ways, depending on the use case:

Custom Code Triggers (Apex)

Apex triggers enable you to perform custom actions before or after events to records in Salesforce, such as insertions, updates, or deletions. Just like database systems support triggers, Apex provides trigger support for managing records.

These are used for critical integration logic where visual tools aren’t enough. For example:

✅ Populating External System References: Triggers automatically fill in fields like external IDs which could be colors, unit of measures, store types needed by downstream systems like Dynamics 365 and Fabric.

✅ Deletion Workaround: Since deletions on Flows are not supported on SalesForce (more on this later) we use that as workaround.

Declarative (No-Code) Triggers via Flows

For simpler automation (like when a record is inserted in an entity), we use Salesforce Flows. These let us configure behavior visually without writing Apex code, making maintenance easier when business rules change.

Together, these approaches allow for a flexible and maintainable integration logic mixing low-code with code when necessary.

The following is an example of a trigger that populates the ID value when a color is selected from the map option field:

trigger ColorTrigger on Product2 (before insert,before Update) {
map<string,integer> oppMap = new map<string,integer>();
 oppMap.put('Azure', 1);
 oppMap.put('Black', 2);
 oppMap.put('Blue', 3);
 oppMap.put('Brown', 4);
 oppMap.put('Purple', 5);
 oppMap.put('Red', 6);
 oppMap.put('Silver', 7);
 oppMap.put('White', 8);
 oppMap.put('Orange', 9);
 oppMap.put('Pink', 10);
 oppMap.put('Grey', 11);
 oppMap.put('Silver Grey', 12);
 oppMap.put('Yellow', 13);
 oppMap.put('Green', 14);
 oppMap.put('Gold', 15);
 oppMap.put('Transparent', 16);

 for(Product2 prod: trigger.new ) {
    if(prod.Color__c != null) {
       prod.ColorId__c= oppMap.get(prod.Color__c);
     }
  }
}

UI Customization: Lightning Pages & Field Visibility

Since OmniSync also serves as a learning exercise in customizing Salesforce for real-world use, we made a few UI enhancements to improve usability and align with integration needs

Lightning Record Pages

The default page for Retail Store object (Retail_Store__c) was customized using with Lightning Record Pages using the Lightning App Builder :

Lightning Record Pages are configurable layouts in Salesforce that define how records are displayed to users. Built using Lightning App Builder , they let admins arrange components, tabs, fields, and related lists without writing code — making it easy to tailor the UI for different roles, devices, and business scenarios.

A header to highlight the Retail Store code and its name (on the read-only header).
A detail section with the Retail Store Information fields
A detail section with Address Information fields
Integration related fields such as SyncStatus__c was added as the first visible field to see on the top in case there is a Synchronization conflict.

Below is an example of the Retail Store screen, which was built entirely from scratch using Lightning App Builder.

And its results when published:

Conditional Field Visibility

Salesforce allows low-code visibility rules to control which fields appear under specific conditions. No Apex or Flow needed.

For example:

The SyncStatus__c field was made visible only when there was a Conflict value on that and added a warning icon to that.

This visual shows an image showing how the field becomes visible when a conflict is detected during synchronization:

Even with just low-code tools, Salesforce provided enough flexibility to create a user-friendly and sync-aware UI.

Flows: Automation Without Code

Salesforce Flows are powerful, declarative tools that let you automate business logic and integrations with minimal to no code. Designed using Flow Builder , they offer a drag-and-drop interface for building everything from simple field updates to complex multi-step logic.

In OmniSync, all automation was implemented using Record-Triggered Flows , these are flows that run automatically when a record is created, updated, or deleted.

Here are the key Flow-based use cases implemented:

In-App Notifications

When a sync fails or a conflict is detected on any entity, a notification is automatically sent to the relevant user. This was handled entirely via Record-Triggered Flow and Salesforce’s notification framework, no Apex or email alerts needed.

And the result of this custom notification can be seen below. Alongside the alert, the same Flow also updates the StatusSync__c field to reflect the latest sync status, in case notifications are ignored or missed.

Line Number Calculation

For child objects like product order items, we used a Record-Triggered Flow to auto-assign incremental Line Numbers. This ensures consistency and makes integration with external systems cleaner and more deterministic.

And the result of this custom notification can be seen below. Alongside the alert, the same Flow also updates the StatusSync__c field to reflect the latest sync status — in case notifications are ignored or missed.

Outbound Messages

Used to synchronize entities with external systems in real time, using XML-based SOAP messages. These were implemented entirely through Record-Triggered Flows, eliminating the need for custom Apex.

Each time a record is inserted, updated, or deleted , the Flow automatically sends an outbound message containing key business data to an external system acting as a Webhook.

These flows not only automated key parts of the data lifecycle but also ensured the UI remained responsive, synced, and reliable, all without a single line of Apex.

Outbound Messages (Webhooks via SOAP)

Salesforce provides several integration mechanisms, and one of the most useful for real-time scenarios is Outbound Messaging, which uses SOAP-based webhooks.

In OmniSync, we use Outbound Messages to detect changes (insert/update) on key entities like RetailStore__c. When such changes occur:

An Outbound Message is configured to include the necessary fields.
A Salesforce Flow picks up the change and sends the message via a SOAP notification.
This notification is received by a Logic App endpoint , which handles the next steps in the integration pipeline.

This method is particularly useful when Change Data Capture (CDC) isn’t available for certain standard or custom objects. While not technically streaming, this approach is near real-time, reliable, and easy to configure, no code required.

It provides a practical balance between simplicity and speed for triggering external workflows on data changes.

As shown in the figure below the RetailStoreChanges outbound message which happens when a record on the RetailStore is inserted or updated. Also the flow triggered from this outbound message can be seen on the previous section.

And a partial schema of the SOAP notification for this outbound message:

<schema elementFormDefault="qualified" targetNamespace="http://soap.sforce.com/2005/09/outbound"
          xmlns:xsd="http://www.w3.org/2001/XMLSchema"
          xmlns:ent="urn:enterprise.soap.sforce.com"
          xmlns:ens="urn:sobject.enterprise.soap.sforce.com"
          xmlns:tns="http://soap.sforce.com/2005/09/outbound"
          xmlns="http://www.w3.org/2001/XMLSchema">
  <import namespace="urn:enterprise.soap.sforce.com" schemaLocation="ID.xsd"/>
  <import namespace="urn:sobject.enterprise.soap.sforce.com" schemaLocation="RetailStore.xsd"/>
    <!-- <element name="notifications"> -->
  <element name="notifications" type="tns:NotificationsType"/>
      <complexType name="NotificationsType">
        <sequence>
          <element name="OrganizationId" type="ent:ID"/>
          <element name="ActionId" type="ent:ID"/>
          <element name="SessionId" type="xsd:string" nillable="true"/>
          <element name="EnterpriseUrl" type="xsd:string"/>
          <element name="PartnerUrl" type="xsd:string"/>
          <element name="Notification" maxOccurs="100" type="tns:RetailStoreNotification"/>
        </sequence>
      </complexType>
    <!-- </element> -->
    <complexType name="RetailStoreNotification">
      <sequence>
        <element name="Id" type="ent:ID"/>
        <element name="sObject" type="ens:RetailStore"/>
      </sequence>
    </complexType>
    <!-- <element name="notificationsResponse"> -->
      <complexType name="notificationsResponse">
        <sequence>
          <element name="Ack" type="xsd:boolean"/>
        </sequence>
      </complexType>
    <!-- </element> -->
</schema>

The Delete Workaround

This snippet shows a piece of that code where the custom object is a RetailStoreDeletedEvent__c created on that situation:

trigger AfterDeleteRetailStore on RetailStore (after delete) 
{
    List<RetailStoreDeletedEvent __c > co = new List<RetailStoreDeletedEvent__ c >();
    for(RetailStore o : Trigger.old)
    {
        RetailStoreDeletedEvent __c c = new RetailStoreDeletedEvent__ c();
        c.Name = o.Name;
        c.DeletedId__c = o.Id;

        co.add(c);
    }

    insert co;
}

And its associated flow:

Change Data Capture (CDC)

While outbound messages worked well for basic events, OmniSync also uses Salesforce Change Data Capture (CDC) for streaming change events , including inserts, updates, and deletes in near real time.

CDC events are published to a PushTopic based streaming API , which is accessed via Salesforce’s Pub/Sub API and consumed downstream using the CDC Ingestor App.

Change Data Capture (CDC) via Pub/Sub API

To capture real-time changes in Salesforce data, OmniSync leverages Change Data Capture (CDC) a native Salesforce feature that publishes events when records are created, updated, deleted, or undeleted.

In earlier implementations, external clients had to rely on the Streaming API to subscribe to CDC events. However, OmniSync uses the more modern Pub/Sub API , which provides several advantages:

Built on gRPC and HTTP/2 , making it more efficient and scalable than traditional Streaming API.
Delivers binary event messages , reducing payload size and improving speed.
Supports multiple languages (Java, Go, Node.js, etc.), making it easier to integrate into modern backend systems.
Reliable message delivery with built-in acknowledgment and replay support.

🧩 How It Works in OmniSync

Insert, Update, and Delete events are published for key objects (like Orders, Accounts, and Products)
The CDC Ingestor App , which is an Azure Container App implemented on Javascript receives these events and pushes them into Azure Event Grid
From there, Logic Apps which are subscribed as Webhooks and filtered by subject, execute the transformations logic syncing to the downstream systems.

Here’s a partial example of a JSON CDC changed object:

{
  "replayId": 211583,
  "payload": {
    "ChangeEventHeader": {
      "entityName": "Account",
      "recordIds": [
        "001WU00000vBSOLYA4"
      ],
      "changeType": "UPDATE",
      "changeOrigin": "",
      "transactionKey": "0000dece-b24f-fd96-1649-167b8ad0cbfa",
      "sequenceNumber": 1,
      "commitTimestamp": 1746061521000,
      "commitNumber": 0,
      "commitUser": "005WU000009oVebYAE",
      "nulledFields": [],
      "diffFields": [],
      "changedFields": []
    },
    "Name": null,
    "Type": null,
    "ParentId": null,
    "BillingAddress": null,
    "ShippingAddress": {
      "Street": "Belleveu Main Street",
      "City": "Belleveu",
      "State": null,
      "PostalCode": "23232",
      "Country": "USA",
      "Latitude": 37.5624,
      "Longitude": -77.4467,
      "GeocodeAccuracy": "Zip"
    },
    "Phone": null,
    "Fax": null,
    "AccountNumber": "CS424",
    "Website": null,
    "Sic": null,
  ......
}

Event Enrichment

To make the CDC data more useful Salesforce was configured to include extra fields in CDC payloads since for Updates only the changed fields were sent, and to avoid extra calls on the Logic Apps integration layer to find lookups some fields were additionally sent to overcome this need.

The REST API call can be seen following as an example of how to create a subscription named OmniSync_Channel_chn_AccountChangeEvent on the OmniSync_Channel__chn channel for the Account entity, with additional fields included to enrich the event message.

Delete Handling with CDC

Unlike Outbound Messages, CDC supports deletes natively , which made it the preferred approach for full bi-directional sync.

Logic Apps

Logic Apps serve as the core integration engine in OmniSync, bridging Salesforce, Dynamics 365, and Microsoft Fabric. They handle data transformation , validation , and routing , ensuring that only clean and properly formatted data reaches the destination systems.

We use two main categories of Logic Apps based on their direction and purpose:

Salesforce to Dynamics 365: Handles the creation, update, or deletion of retail-related entities like Orders, Products, and Accounts in D365.
Salesforce to Fabric (Lakehouse): Pushes changes from Salesforce into Microsoft Fabric via Event Hub, powering near real-time analytics.

Each Logic App is triggered by a different integration pattern depending on the nature of the Salesforce event:

SOAP Webhook (Outbound Message) — Triggered by Salesforce outbound notifications for objects like RetailStore, which send data to a Logic App endpoint via SOAP.
Event Grid Webhook — Used for handling CDC events or platform events forwarded through Azure Event Grid.
Polling via Salesforce Connector — A fallback or scheduled mechanism to periodically check for new or updated data when webhooks are not available or reliable.

Let’s walk through a few concrete scenarios to illustrate how different types of Logic Apps work across various triggers and systems.

Currency Inserted (Salesforce → Fabric)

In this scenario, a Salesforce connector polls the CurrencyType object every minute for changes. When a new currency is detected:

A Liquid template is used to transform the CurrencyType format into a simplified Currency object.
Some fields (like datetime formats) can’t be transformed using Liquid due to its limitations, so they’re adjusted directly within the Logic App using expressions.
The transformed object is then sent to Event Hub, which pushes it into the Event Stream in Microsoft Fabric for analytics.

Retail Store Deleted (Salesforce → Fabric via SOAP)

When a Retail Store is deleted, Salesforce sends an Outbound Message ( SOAP -based webhook) to the Logic App:

The incoming message is validated against the OutboundRetailStoreDeletedEvent.xsd schema.
A check is performed to confirm this change is part of an integration sync (using user context, as described in Part 1).
After parsing and a manual field transformation (via a Compose action), the data is sent to Event Hub to deliver the new CDC deletion event into Fabric.

Dynamics 365 Account Sync (Fabric → Salesforce via Event Grid)

This is a more advanced use case that handles create, update, and delete events in a single Logic App, all triggered by a subscription to Event Grid.

Since subscriptions are scoped per entity (not per action), we handle branching within the Logic App itself:

➕ Create:

The Logic App checks if the incoming account already exists in Dynamics 365 by querying a MasterDataMapping table via a Lakehouse SQL endpoint.
If it exists (e.g., an account code like A001 was already synced from Salesforce), a conflict is detected. The Salesforce record is updated with a StatusSync = conflict.
Otherwise, a new Dynamics 365 Account is created via the Dataverse connector , with fields mapped accordingly and its id is sent to Fabric mapping table.

🔁 Update:

A check is made to confirm the record exists in the mapping table.
If not found, a 404 is returned.
If it does exist, we call an Azure Function to enrich the update, since Salesforce CDC events may not contain all fields needed for a proper sync.

❌ Delete:

Again, existence is verified via the mapping table.
If the account is found, we perform a cleanup of dependencies (e.g., Sales Orders ) that must be removed before deleting the Account from Dynamics 365 using the Dataverse connector.
If not found, a 404 is returned, indicating a potential sync issue.

Transformations

Inside the Logic Apps, data transformations are a key step to map and reshape fields coming from Salesforce into a format expected by the destination systems (such as Dynamics 365 or Fabric). Several transformation mechanisms depending on the complexity and context of the data are used. All approaches aim to produce the same result: a well-structured, consumable object on the other side.

DataMapper (Visual XSLT Mapping)

We used DataMapper to transform objects like RetailStore from Salesforce to Store objects consumed by Fabric.

DataMapper is Microsoft’s visual tool for mapping and transforming data across schemas. It allows users to build mappings between JSON or XML structures via a drag-and-drop interface, generating an XSLT file that can later be applied within Logic Apps.

However, the tool currently has significant limitations:

It behaves unpredictably when handling complex or mixed-schema scenarios (e.g., identical schema names in XML vs. JSON)
XML-to-XML transformations often fail to display properly or get lost
Some edits are lost when not saved properly due to the use of temporal files
It lacks advanced customization, requiring manual XSLT editing in many cases

Because of these constraints, DataMapper was used only when absolutely necessary. While promising, it’s not stable enough yet for this project.

Liquid Templates

Liquid is a lightweight template engine originally developed by Shopify. In Logic Apps, we use Liquid templates primarily to transform incoming Salesforce events into CDC-style JSON objects to be sent to Fabric Lakehouse via Event Hub.

These templates allow field filtering and conditional logic, which makes them perfect for reshaping Salesforce payloads into clean, minimal representations.

Example: Salesforce Account to Customer

{%- assign changedFields = content.payload.ChangeEventHeader.changedFields -%}

{
   "Operation": "{{ content.payload.ChangeEventHeader.changeType | capitalize }}",
   "Entity": "Customer",
   "Values": "{
    \"CustomerCode\": \"{{ content.payload.AccountNumber }}\",    
    {%- for changedField in changedFields -%}
     {% if changedField == "Name" %}
      \"CompanyName\": \"{{ content.payload.Name }}\",
     {% elsif changedField == "Email__c" %}
      \"EmailAddress\": \"{{ content.payload.Email__c }}\",
     {% elsif changedField == "Phone" %}
      \"Phone\": \"{{ content.payload.Phone }}\",
     {% elsif changedField == "LastModifiedDate" %}
      \"UpdatedDate\": \"{{ content.payload.LastModifiedDate }}\",
     {% endif %}
    {%- endfor -%}
    \"AddressLine1\": \"{{ content.payload.ShippingAddress.Street }} {{ content.payload.ShippingAddress.City }} {{ content.payload.ShippingAddress.PostalCode }} {{ content.payload.ShippingAddress.State }} {{ content.payload.ShippingAddress.Country}}\",
    \"Latitude\": \"{{ content.payload.ShippingAddress.Latitude }}\",
    \"Longitude\": \"{{ content.payload.ShippingAddress.Longitude }}\",
    \"CustomerType\": \"Company\",
    \"SalesForceId\": \"{{ content.payload.ChangeEventHeader.recordIds | first }}\"
     }",
 "CreatedDate": "{{ "now" | date: "%Y-%m-%d %H:%M" }}",
    "UpdatedDate": "{{ "now" | date: "%Y-%m-%d %H:%M" }}"
}

This approach works well for real-time event streaming, although it has limitations, especially with datetime transformations, which must be handled elsewhere in the Logic App.

Manual Mapping using Compositions

In many cases, particularly when data is already in a usable format, we perform transformations manually using Compose actions inside Logic Apps. This is a straightforward method to restructure or rename fields without involving external tools or templates.

It’s especially useful for:

Light field remapping.
SOAP/XML payload extractions.
Intermediate enrichments.

Manual Mapping Using Connectors

When synchronizing with Dynamics 365, we rely on the DataVerse connector and its built-in CRUD operations. This makes it straightforward to map previously decoded values from Salesforce to their counterparts in Dynamics 365. Thanks to the rich set of expression functions available in Logic Apps’ Workflow Definition Language, we can apply transformations directly, such as string manipulation, conditional logic, and date formatting.

For instance, when mapping Salesforce Products to Dynamics 365, we convert date fields from Salesforce’s CDC (which are in epoch format) into standard UTC timestamps using expression functions before sending them downstream.

Azure Functions for Advanced Logic

When field transformations are too complex or when we only receive partial data (as is the case with Salesforce CDC updates) logic is offloaded to an Azure Function.

These functions:

Read the incoming JSON payload.
Identify only the changed fields.
Query our master data mapping layer if needed.
Apply transformations and send updates to Dynamics 365 through the Dataverse SDK.

Example Snippet:

using (ServiceClient svc = new ServiceClient(ConnectionStr))

if (svc.IsReady && inputAccountValue != null)
{
    var account = new Entity("account");

    account.Attributes["accountnumber"] = inputAccountValue.payload.cgcloud __Account_Number__ c;

    foreach (var changedField in inputAccountValue.changedFields)
    {
        if (changedField == "name")
        {
            account.Attributes["name"] = inputAccountValue.payload.Name;
        }
        else if (changedField == "cgcloud __Account_Email__ c")
        {
            account.Attributes["emailaddress1"] = inputAccountValue.payload.cgcloud __Account_Email__ c;
        }
        else if (changedField == "Phone")
        {
            account.Attributes["telephone1"] = inputAccountValue.payload.Phone;
        }
    }

    if (!string.IsNullOrEmpty(inputAccountValue.payload.ShippingAddress.Street))

This gives us full flexibility and control, especially when dealing with partial updates and nested logic.

Event Grid Integration

As part of our CDC integration model, Event Grid is used as the main pub/sub mechanism for CDC events emitted by our custom CDC Ingestor running as a Container App.

The CDC Ingestor is responsible for:

Listening to entities’ changes from Salesforce CDC Pub/Sub API
Publishing them into Event Grid Topics

Logic Apps are then subscribed to these topics using Webhook-based filters (based on the subject field). For example, a Logic App subscribed with a filter on subject = Account will only trigger on Account events, regardless of the operation type.

This model avoids over subscribing and helps isolate business logic per entity.

Monitoring

All components in the OmniSync architecture are monitored using Azure Monitor , with additional diagnostics enabled through a Log Analytics Workspace solution.

Logic Apps Monitoring

Logic Apps include native history tracking, where you can review runs, triggers, and failures directly in the Azure Portal.

Container Apps Monitoring

For monitoring the CDC Ingestor or other custom container-based services, Container Apps Logs can be queried through Azure Monitor using Kusto queries. These logs include both system diagnostics and custom error messages emitted from the application code.

Salesforce Monitoring

Salesforce provides its own Monitoring tool to track outbound messages, such as SOAP or Event-driven webhooks. This includes:

Viewing the delivery status of outbound messages.
Monitoring failed retries or invalid endpoint responses.
Debugging SOAP/XML payloads directly from the monitoring queue.

This ensures that any integration failures between Salesforce and Logic Apps (e.g., due to schema mismatches or endpoint issues) can be proactively addressed from both sides.

Salesforce CLI

All Salesforce metadata used for this integration is version-controlled in a Git repository. To extract and manage this metadata, we use the Salesforce CLI (sf), which supports robust project management and deployment automation.

The CLI was used to retrieve key components from the source Salesforce org, including:

Custom Objects
Apex Triggers
Flows and Flow Definitions
Layouts and Applications
Workflow Rules
Connected Apps

sf project retrieve start --metadata CustomObject \
                          --metadata ApexTrigger \
                          --metadata Flow \
                          --metadata WorkFlow \
                          --metadata Layout \
                          --metadata FlowDefinition \
                          --metadata CustomApplication \
                          -m ConnectedApp

This allows easy export of Salesforce configuration and logic to be deployed across environments (e.g., staging, QA, production) via CI/CD pipelines or manual promotion.

Lessons Learned

Logic Apps proved to be a powerful iPaaS tool, allowing to connect Salesforce, Dynamics 365, and Microsoft Fabric with minimal custom code. By handling transformations, validations, and routing logic visually, writing full applications was avoided while still delivering complex integration flows.
Organizing Logic Apps by direction Salesforce to Dynamics 365 and Salesforce to Fabric made the architecture easier to manage and extend. Using a mix of triggers (polling, EventGrid, SOAP webhooks) gave the flexibility to respond to different Salesforce behaviors and system needs.
For data transformation Liquid templates gave a powerful template and transformation engine.
Some complex cases needed the usage of Azure Functions were essential when deeper logic was needed, since trying to do in a visual way on Logic Apps would have been terribly complicated.
The Pub/Sub API for CDC was a really efficient and easy way to provide a scalable event delivery over HTTP/2 and gRPC.
Although at the start of the project DataMapper looked promising, it had limitations and bugs for more complex use cases, so manual Compose actions, Liquid templates or XSLT updates were more reliable in those cases.

Coming Next

📌 In the next post, I’ll dig into Dynamics 365 Integration

👀 Follow me here on Medium to catch Part 4.

💻 Source code: https://github.com/zodraz/salesforce

OmniSync: Near Real-Time Lakehouse, Spark Streaming and Power BI in Microsoft Fabric (Part 2)

Abel — Tue, 29 Apr 2025 15:38:59 +0000

Building near real-time reports and analytics with Fabric’s Lakehouse, Spark, and Power BI.

Posts in this Series

OmniSync: A Real-World Architecture for Syncing Salesforce, D365, and Fabric in Near Real-Time (Part 1)
OmniSync: Real-Time Lakehouse, Spark Streaming, and Power BI in Microsoft Fabric (Part 2) (This Post)
OmniSync: Integrating Salesforce with Microsoft Fabric and Dynamics 365 (Part 3)
OmniSync: Dynamics 365 Integration with Salesforce and Fabric (Part 4)

Introduction

This second part of the OmniSync series focuses on how Microsoft Fabric was used to build the analytics and reporting layer, fed by both legacy data and near real-time sync flows from Salesforce and Dynamics 365.

The goal wasn’t just to centralize data, but to apply real architectural principles:

Medallion layer structuring (Bronze, Silver, Gold)
Real-time streaming with Spark
Unified models powering live Power BI reports

Fabric provided a strong foundation for this — combining Dataflows Gen2 , Lakehouse , Spark Notebooks , and Power BI into a single analytics platform.

While this phase required learning new tools, it gave complete control over ingestion, transformation, and reporting, all within a single platform.

Microsoft Fabric Workspaces

To keep things organized, OmniSync uses a dedicated Fabric workspace to manage all components involved in ingestion, transformation, and reporting.

This workspace contains:

Lakehouses for storing raw and curated data
Environments for custom libraries like log4Java
API For GraphQL for SPA accessing externally
Event Stream to get data externally and store on LakeHouse
Semantic Models as a way to model entities, relationships and measures
Dataflows Gen2 for batch ingestion (like the initial load)
Spark Notebooks for transformations, CDC logic, and business rules
Spark Jobs for streaming notebooks
Power BI reports for final dashboards and metrics
Monitoring Eventhouse for monitoring workspace
Deployment Pipelines for CI/CD amongst different environments

Each workspace is linked to a Microsoft OneLake storage container behind the scenes, where all the data (Delta tables, logs, artifacts) is physically stored.

Why Lakehouse Instead of Data Warehouse?

OmniSync uses a Lakehouse as its main data store instead of a traditional Data Warehouse or even the newer SQL Database option in Microsoft Fabric.

This decision was primarily driven by the goal of exploring and learning about the Lakehouse architecture and the use of Spark within Fabric. It gave a chance to evaluate its benefits and limitations compared to:

A more traditional operational database like SQL
A classic OLAP-style Data Warehouse

While this may not have been the most production-optimized option for a real-time scenario (an Eventhouse architecture might have been more suitable), it fit the PoC’s purpose perfectly, allowing to experiment with modern patterns, event-driven ingestion, and Spark-based transformations in a controlled and flexible environment.

Microsoft provides a detailed guide to help decide between these storage types in Fabric:

👉Choosing a data store in Microsoft Fabric

In this case, the Lakehouse provided the right mix of flexibility and hands-on Spark learning, while also supporting the real-time ingestion patterns needed from CDC.

SQL Analytics Endpoint

The SQL Analytics Endpoin t in Microsoft Fabric allows you to query Lakehouse Delta tables using standard T-SQL, so there is no need to write Spark code for everything.

For OmniSync, it was primarily used to:

Run ad hoc SQL queries to inspect raw and cleaned data
Query externally from Logic Apps and validate integration data

This endpoint gives you a familiar SQL interface to the Lakehouse, which is especially useful when building reports or debugging synchronization issues without needing to open a notebook.

It’s worth noting that while SQL Endpoint is great for reporting and light analytics, complex transformation logic is still handled more efficiently in Spark Notebooks.

Medallion Architecture

Before diving into the Medallion setup, keep in mind that OmniSync’s design draws on ideas from Kappa and Lambda architectures, adapted into a simplified event-driven model.

As explained in Part 1, OmniSync first runs an initial ETL process using Dataflows Gen2 to load legacy SQL Server data into Fabric. After that, all operations are handled via streaming basis.

This Medallion structure builds on that foundation: starting with batch ingestion, but fully shifting into real-time sync after the initial load.

A Medallion Architecture is a common pattern in modern data engineering that organizes data into structured layers — typically Bronze , Silver , and Gold — to improve clarity, scalability, and data quality as it flows from raw ingestion to final reporting.

Bronze : Raw, unprocessed data landed exactly as received
Silver : Cleaned, filtered, and enriched data, ready for modeling
Gold : Business-ready data, often aggregated or transformed for analytics and BI tools

This layered approach allows teams to decouple ingestion from transformation and reporting, while also enabling better debugging, governance, and performance optimization at each stage.

OmniSync uses a Medallion architecture with a little difference: Bronze and Silver layers are used primarily for initial load, while the Gold layer is continuously updated with real-time streaming data from Salesforce and Dynamics 365.

Bronze Layer

The Bronze layer ingests legacy data from a SQL Server database which in this case is Microsoft’s sample ContosoDataWarehouse, adapted for our own entities and structure.

Ingestion is handled via Dataflows Gen2 , which provides a fast, no-code way to move data into the Bronze Lakehouse without applying any transformations. It preserves the structure and contents exactly as received.

Silver Layer

The Silver layer is where we start to prepare the data for actual use.

A Spark Notebook reads from the Bronze layer and performs several operations:

Cleansing and normalizing data
Seeding initial structures
Applying business transformations
Creating new tables and adding derived columns
Mapping values using reference tables (like MasterDataMapping)

For example, when processing Stores :

A new table is created with cleaned schema
Geolocation values are extracted into latitude and longitude
Store-to-customer relationships are inferred and added

Another example is Account seeding, where incoming Salesforce and Dynamics 365 Id’s are mapped using the MasterDataMapping table, aligning cross-platform references for future synchronization logic.

After this layer, data is structured and aligned with OmniSync’s data model, but not yet real-time.

Gold Layer

The Gold layer is where real-time streaming meets analytics.

Initially, this layer is populated via a notebook that copies cleaned tables from the Silver layer. But more importantly, Gold becomes the landing zone for live CDC events, handled by streaming Spark notebooks that process changes from Salesforce and Dynamics 365 in near real-time.

This layer includes the final Sales fact table , which joins and flattens multiple sources and becomes the main source for Power BI reporting.

In OmniSync, the Gold layer is the main data layer. It combines past data with new updates, making the system simple, scalable, and ready for analysis.

Star Schema Model

The final Gold layer in OmniSync follows a Star Schema, a widely used and performant model for analytics tools like Power BI.

This wasn’t designed from scratch. The starting point was a legacy SQL Server Data Warehouse, which already had well-defined fact and dimension tables. For the PoC, we simplified that model and focused on what mattered most: clean reporting, easier maintenance, and compatibility with real-time updates.

At the center of the model is the SalesOrder fact table , adapted from the original FactOnlineSales fact table in Contoso’s warehouse. This is materialized in another fact table Sales becoming the main table consumed by Power BI, and the foundation for all reporting.

Surrounding it are simplified dimension tables , created and enriched through Spark Notebooks:

Products
Product (Sub)Categories
Stores
Customers (Accounts)
Geography
Date
Currency

Each of these dimensions is either loaded during the initial data load or kept fresh through real-time CDC synchronization from Salesforce and Dynamics 365.

In this model:

Fact tables hold the transactional data
Dimensions describe the context (e.g., which product, store, customer, or region)
Relationships are built using foreign keys , enabling fast filtering and slicing in Power BI

The semantic layer was built using:

Spark Notebooks to clean and prepare dimensions
Manual modeling in Power BI to define relationships, hierarchies, and joins
DAX measures to define key metrics and calculated columns

This setup offers:

Fast cross-filtering across dimensions
Reusable metrics that stay consistent across reports
A clean separation between transformation logic and report logic

By applying a classic Star Schema, we keep the Power BI model both performant and intuitive, even with real-time data flowing in from multiple platforms.

Power BI

Power BI serves as the primary reporting tool for OmniSync, running directly on top of the Gold Lakehouse tables.

Thanks to Microsoft Fabric’s native integration with Power BI, reporting is seamless, with no complex external connections needed.

Reports connect to the Gold Lakehouse tables using DirectLake for high performance with cached data
Data model is shaped using the Star Schema built in Fabric
DAX is used to define measures and KPIs
Reports were modeled and created in Power BI Desktop.

Implemented Reports

Several Power BI reports were created to visualize synced data from Salesforce and Dynamics 365 into our new LakeHouse built directly on top of the Gold layer in Fabric.

Key Reports Included

📈Sales Report

Displays total sales volume by region, stores, product categories, dates, COGS, Net Profit, % Change in Sales amongst others.

📈Sales By Country

Displays total sales volume and LY comparison by selectable continent, country, brand, class and product category amongst others.

📈 Sales By Store

Displays total sales by store, LY by month, product categories and geography of the stores.

📈Margin Analysis

Shows profit analysis, net profit comparisons and those margins by month, city and product category.

📈 Sales By Product

Displays total sales by product with last year comparison by country, brand, class, manufacturer, color and category amongst others.

Each report is built with:

Slicers for key dimensions (time, region)
DAX measures for dynamic KPIs (e.g., % Net Profit, COGS)
Visual filtering via field parameters

Semantic Model & DAX Measures

On top of the Gold Lakehouse tables, OmniSync builds a Fabric semantic model to define relationships, and key performance metrics all using DAX which then Power BI reports can query clean, efficiently and business stakeholders can consume.

Data Analysis Expressions (DAX) is the formula language used in Power BI to define calculations, aggregations, and business logic across tables and models.

Semantic model includes:

Relationships between fact and dimension tables are defined, following the Star Schema built inside Fabric.
Calculated columns are used for derived attributes, such as Store Type or Category Group.
Hierarchies are added for drilldowns, like navigating from Country → Region → City in Geography analysis.
Business calculations and KPIs are created using DAX

Several measures were used for the different reports some are relatively complex like the one below which calculates the Total Sales by Store and Date selected.

Store Sales (Selected) = 
CALCULATE( 
 [Total Sales] , 
 FILTER(
        ALLSELECTED('Date'[DateKey]),
        'Date'[DateKey] <= MAX('Date'[DateKey])),
    GROUPBY( Store, Store[StoreName])
)

Other are simple like the Total Sales which is basically the sum of Sales

Total Sales = SUM(Sales[Sales])

Real-Time Hub: Streaming Data into Fabric

After the initial batch load, OmniSync shifts entirely into near real-time data ingestion, powered by Azure services and Spark Streaming inside Microsoft Fabric.

At the core of this live data flow is Fabric’s Real-Time Hub, a specialized engine that enables continuous event ingestion directly into Lakehouse tables without requiring heavy batch processing.

How the Streaming Happens

Salesforce and Dynamics 365 emit change events (via CDC streams or webhooks).
Events are routed through a middleware layer involving EventGrid , Logic Apps , and Azure Event Hub.
A Fabric Spark Streaming Notebook listens continuously to Event Hub using the Real-Time Hub.
Incoming events are processed, enriched, and immediately written into the Gold layer of the Lakehouse.

In OmniSync, the Real-Time Hub makes it possible to continuously push changes from Salesforce and Dynamics 365 directly into Fabric with minimal infrastructure setup.

Benefits of this Setup

Near real-time updates for key business entities (Accounts, Orders, Products)
Automatic enrichment (resolving relationships, IDs, lookups) during ingestion
Fresh Power BI dashboards without relying on heavy batch refreshes
Low operational overhead : No manual Spark cluster management needed

While actual latency can vary slightly (due to retries, cold starts, or checkpoint delays), most updates flow through and appear in the Gold Lakehouse layer within 5–20 seconds after a change is made in the source system.

Thanks to Fabric’s Real-Time Hub, OmniSync achieves true near-real-time syncing, not just for analytics, but for live operational data across systems.

CDC Table Format

To make real-time ingestion manageable, each event captured from Salesforce or Dynamics 365 is normalized into a consistent CDC (Change Data Capture) format before being processed in Fabric.

Each incoming record includes fields like:

Entity: The type of entity (e.g., Account, Order)
Operation: Type of change (insert, update, delete)
Values: JSON object representing the record state
CreatedDate: Creation timestamp of when the row was created
UpdateDate: Update timestamp of when the was updated

This consistent schema makes it easier for Spark notebooks to:

Apply insert/update/delete logic
Route each entity type to the right table

Streaming Notebook for CDC Processing

As explained earlier, we use a streaming technique to capture and process CDC data from external systems into Fabric. All raw CDC events are first stored in a centralized ExternalCDC Delta table , which acts as the landing zone for real-time changes coming from Azure Event Hub.

To process these changes continuously, we use Spark Structured Streaming , and run it as a long-running Spark Job Definition in Fabric. This keeps the notebook running in the background, monitoring for new events and processing them as they arrive in near real time.

Here’s the basic Spark streaming code:

df.writeStream \
    .outputMode("append") \
    .trigger(processingTime='10 seconds') \
    .option("checkpointLocation","Files/cdc_checkpoint") \
    .format("delta") \
    .foreachBatch(sendToSinkTable) \
    .start() \
    .awaitTermination()

This stream is scheduled and executed as a Fabric Job Definition , allowing it to run continuously, restart on failure without manual triggering. Job is rescheduled every night at 1:00 am when nobody is working and the retry policy is to do indefinitely, on failure, every 10 seconds.

The function sendToSinkTable is a custom PySpark routine that decodes each batch of CDC events and applies them to the correct Lakehouse tables.

It handles all Create , Update , and Delete operations by:

Parsing the raw JSON payloads into DataFrames
Determining the type of operation
Routing the data to the appropriate destination tables

It also performs key logic like:

Creating auxiliary data (Dates, Geography)
Generating identity primary keys where needed
Mapping relationships (e.g., Store → Account)
Decoding epoch timestamps
Aligning cross-system IDs (Salesforce, Dynamics, Fabric)
Materializing sales data for Power BI reporting

Below we can see an image of the Sales materialization PySpark notebook code:

Geolocation Mapping via GeoApify

To enrich location-based entities like Stores and Accounts, OmniSync integrates with the GeoApify API to resolve geographic coordinates and standardize location data within the Geography dimension.

This enrichment is handled by a dedicated Geography CDC Spark notebook, which is triggered as part of the real-time synchronization flow. The notebook analyzes each CDC event and determines the best way to enrich location fields based on the input data.

Depending on the payload:

If latitude and longitude are present, it performs reverse geocoding using GeoApify to extract: street, city, postal code, region and country
If coordinates are not available , the notebook uses available address components (street, city, country, etc.) to perform forward geocoding , resolving coordinates instead.

The notebook then merges the enriched fields (like resolved_country, lat, lon) back into the corresponding entity record before writing to the Delta table.

GeoApify Provides:

Address normalization and cleaning
Reverse geocoding (latitude/longitude → full address info)
Forward geocoding (partial address → coordinates)
A consistent geographic hierarchy : Country > Region > City > Postal Code > Street

The API is called directly from within Spark using an HTTP client, and the GeoApify API key is stored securely in Azure Key Vault, injected into the notebook environment at runtime.

This enrichment is especially useful for region-based reporting in Power BI and for filtering and aggregating KPIs by geography, ensuring accurate and consistent location data across the entire reporting layer.

Below we can see an image of the PySpark notebook code of this:

Master Data Mapping

As mentioned earlier, OmniSync uses a dedicated table called MasterDataMapping to maintain consistent references between entities across different systems.

This table plays a critical role in aligning data between Fabric , Salesforce , Dynamics 365 , and potentially SAP (in future phases). It supports mapping for key business entities like Customers, Products, Stores, SalesOrders, and Currencies , ensuring that records from different platforms can be reliably matched.

🔧 Table Structure:

MasterDataMapping: Identity-based primary key (auto-incremented)
FabricId: Internal identifier used within Fabric
SalesForceId: External key referencing the Salesforce ID
D365Id: External key referencing the Dynamics 365 ID
SAPId: Reserved for future SAP integration
Entity: The type of entity (e.g., Currency, Customer, Product, SalesOrder, Store)
Name: Natural key or label to help identify the external entity
IsDeleted: Soft-delete flag
CreatedDate: When the record was created
UpdateDate: When the record was last modified

Previous Synced Systems

This table is pre-seeded with mapping values during the Silver layer processing, as explained earlier. These seed values allow the system to function properly from the start by:

Establishing relationships across systems for historical data
Preventing sync conflicts
Enabling lookups during real-time CDC operations

This table is essential for ensuring that all references remain aligned, especially when syncing multiple systems that don’t share a common key structure.

CRUD

Supported operations in OmniSync are Create , Update , and Delete (obviating Read). While UnDelete was also explored, mainly because Salesforce supports it, it was ultimately left out due to its complexity and the following reasons:

Dynamics 365 does have an UnDelete feature, but it’s still in Preview.
SAP, which we may support later, doesn’t offer this capability at all.
Dynamics 365 also requires a non-trial environment to fully use it.
Recover synced deleted entities in SAP and Dynamics 365 was not feasible in an easy way.

So to avoid overcomplicating the logic, ensure broader compatibility, and easy to test the system the decision was to not implement UnDelete in OmniSync.

Salesforce Deletes

Even without UnDelete, Salesforce handles deletions as soft deletes. To align with that behavior, OmniSync implements a soft delete strategy across all entities.

Every table includes an IsDeleted column, and delete operations only mark the record as deleted, rather than removing it.

This decision leaves the door open to reintroduce the UnDelete logic in the future, if needed.

Idempotency

One critical part of the design is idempotency. In a distributed, event-driven system, “ exactly once ” delivery can’t be guaranteed, due to retries, delays, or duplicated events amogst others.

To handle this, OmniSync enforces idempotent logic during CDC processing:

If a record already exists, it won’t be created again
Deletes won’t be applied multiple times
Updates are allowed if the incoming change is newer

This is all handled in PySpark notebooks, since working with Lakehouse Delta tables means we don’t have traditional database guarantees like primary keys, unique constraints, or foreign keys. Those must be managed at the application logic level.

While not bulletproof, this approach is solid enough for near real-time sync scenarios and avoids common pitfalls in distributed data processing.

Monitoring in Microsoft Fabric

Microsoft Fabric offers a comprehensive suite of monitoring tools to help track performance, resource usage, and activity across your entire data ecosystem, from ingestion flows to Power BI and Spark jobs.

These tools are especially important in scenarios like OmniSync, where streaming jobs, resource capacity, and real-time sync health must be monitored closely.

We will detail now those used in Fabric for OmniSync.

Monitor Hub

Purpose : Centralized activity tracking across all workspaces

Provides a real-time view of active tasks, such as pipelines , dataflows , and notebooks
Fabric users can see activities for items they have access to useful for quick status checks
Great for monitoring job completion, failure states, or troubleshooting long-running tasks

Workspace Monitoring

Purpose : In-depth logging and metric collection within a specific workspace

Collects logs and performance data for all workspace-level items
Helps with troubleshooting , performance tuning , and resource optimization
Useful for capacity planning and identifying usage patterns over time

Microsoft Fabric Capacity Metrics App

Purpose : : Enables monitoring of compute and storage usage for Microsoft Fabric and Power BI Premium capacities.

Tracks Capacity Units (CU) consumption offering insights into compute resource usage.
Provides storage metrics usage data including OneLake and tenant-level storage.
Identifies peak demand, usage patterns, and potential throttling issues to optimize performance.
Helps admins decide on resource scaling or enabling autoscale for efficient capacity management.

Lakehouse & Spark Monitoring (with Log Analytics)

Purpose : Track activity, performance, and health of Spark notebooks and streaming jobs running inside Fabric Lakehouses.

Because OmniSync relies heavily on Spark Structured Streaming to ingest CDC events, it’s essential to monitor Spark jobs at a granular level, beyond just workspace or capacity views.

Fabric provides two main ways to monitor Spark activity:

🔎 Native Spark Monitoring

Track Spark session start/end times
Monitor executor memory and job durations
Inspect checkpointing behavior for streaming pipelines
Detect job failures, retries, and streaming lags

This basic monitoring is available through Fabric UI — but for deeper observability, integration with Azure Log Analytics is recommended.

🛠️ Log Analytics Integration for Spark Logs

To have proper, centralized log monitoring, Azure Log Analytics is connected to Fabric through Environment configuration.

This setup allows you to:

Capture custom logs and metrics directly from Spark notebooks
Retain detailed execution history
Run advanced queries to analyze job behavior, memory usage, and exceptions

To enable this it is needed to create a Fabric Environment linked to the Omnisync workspace and configure with a Log Analytics Workspace and Key ID. After that Spark jobs and notebooks can be attached to run inside that Environment.

More detailed instructions are provided here:

🔗 Configure Fabric Diagnostic Emitters for Log Analytics

Once configured, you can query Spark execution logs in Log Analytics using Kusto Query Language (KQL).

Kusto Query Language (KQL) is a powerful, query language used to analyze large volumes of structured, semi-structured, and unstructured data in Microsoft Azure services like Azure Data Explorer, Azure Monitor, and Microsoft Fabric.

SparkListenerEvent_CL
| where fabricWorkspaceId_g == "{FabricWorkspaceId}" 
  and artifactId_g == "{ArtifactId}" 
  and fabricLivyId_g == "{LivyId}"
| order by TimeGenerated desc
| limit 100

This query lets you monitor:

When jobs start and finish
Memory and execution stats per notebook run
Errors, retries, and warnings inside Spark pipelines

This level of deep monitoring is essential for projects like OmniSync, where continuous ingestion reliability and performance are critical.

GraphQL Monitoring API

Purpose : Monitors GraphQL API activity, providing metrics on request rates, latency, and errors for performance optimization.

Dashboard & Logs: Features a visual dashboard and stores detailed logs in Kusto tables, retained for 30 days.
Troubleshooting: Identifies slow queries and issues, requiring workspace monitoring and incurring storage costs.

Security

Microsoft Fabric uses a three-level security model , evaluated sequentially when users access data:

Microsoft Entra ID authentication : Verifies the user can authenticate to Azure Active Directory (now called Entra ID).
Fabric access : Checks whether the user has access rights to Fabric itself.
Data security : Controls what actions the user can perform on specific tables, files, or reports.

At the data security level , Fabric offers several access controls:

Workspace roles (Admin, Contributor, Viewer)
Item-level permissions (datasets, notebooks, reports)
Compute and granular permissions (control over Spark, SQL resources)
OneLake data access controls (still in preview)

How Security Was Handled in OmniSync

Since OmniSync was a PoC developed by a single user , security was intentionally kept very simple :

Only the Admin role was used across the Fabric workspace.
Item-level permissions and granular compute permissions were not configured.
OneLake access controls were not enabled.

This allowed faster development without worrying about permission conflicts.

In a production environment, OmniSync would require full role separation, proper item-level security, compute-level permissions, and centralized identity management via Entra ID.

Client App Permissions

For external integrations (like the SPA dashboard or Power BI REST calls), a Microsoft Entra App Registration was created with only:

“Power BI Service” permission
“Run Queries and Mutations” (for GraphQL-based access)

This limits the app to read-only query access and prevents modification of workspace artifacts.

Key Vault Integration

Key Vault was used to:

Securely store sensitive tokens
Inject secrets dynamically into Spark notebooks or environment variables at runtime

In OmniSync, GeoApify external API keys were stored securely in Key Vault and pulled when needed by streaming notebooks.

SPA Client App: GraphQL and Embedded Power BI

OmniSync includes a lightweight Single Page Application (SPA) designed to provide a flexible and user-friendly way to interact with the system’s data.

The SPA serves two main purposes:

🔎 Querying Fabric Lakehouse via GraphQL

The app connects to Microsoft Fabric’s GraphQL endpoint to directly query real-time data from the Lakehouse.

SPA can run GraphQL queries against views and tables.
This allows lightweight, low-latency access to live data without needing to always use Power BI dashboards (if needed more complicated stuff).

Example GraphQL query:

query {
  lakehouseTable(tableName: "salesorders") {
    rows(limit: 10) {
      OrderId
      TotalAmount
      StoreName
    }
  }
}

Publishing Embedded Power BI Reports

Besides querying raw data, the SPA also embeds full Power BI reports.

This lets users interact with pre-built dashboards without needing to navigate the Fabric portal directly.

Authentication is done through:

Microsoft Entra App Registration using OAuth 2.0.
MSAL client library

This approach combines flexibility (GraphQL) and rich visualization (Power BI) in one unified front-end.

By combining direct data access and embedded visual analytics, the SPA provides a complete real-time monitoring and reporting solution without needing to depend entirely on the Fabric UI.

CI/CD and Deployment

Even though OmniSync was built as a simple PoC with a single main environment, it still took the opportunity to experiment with Fabric’s Git integration and deployment pipelines to simulate how Dev-to-Prod promotion and version control would work in a real-world scenario.

Git Integration in Fabric

Fabric’s Git integration allows:

Versioning of artifacts (reports, datasets, notebooks, etc.)
Branch separation between development and production workspaces

Fabric Workspace Pipelines

A deployment pipeline was created inside Fabric, based on the OmniSync workspace.
Two stages were defined:

✔️ Development (default workspace)

✔️ Production (new workspace connected to a different Git branch)

The pipeline was linked to Git integration , assigning each workspace to its own branch:

✔️The Development workspace mapped to a dev Git branch.

✔️The Production workspace mapped to the main Git branch.

However, Fabric’s promotion between stages is manual :

Developers push changes into the Development branch.
Inside Fabric, you must manually press the “Deploy” button to move artifacts from Development to Production stages.

Still a Pull Request strategy could be implemented but as said to keep things simply this approach was chosen.

Lessons Learned

Working with Microsoft Fabric for real-time and its pipelines helped underastand more about an Analytics platform like Fabric. Here are the main takeaways from this phase of the project:

Lakehouse + Spark Work : Getting started with Spark notebooks and Delta tables inside Fabric was not easy at first, especially coming from more traditional SQL environments. But once the basics were in place, the flexibility of Spark for transformation and control logic was a huge win.
Medallion Architecture: Organizing data into Bronze, Silver, and Gold layers helped keep everything clearly separated, even if it felt a bit heavy for a PoC where only the initial load used the full structure and only the Gold layer stayed active. Still, the separation brought real benefits in keeping the architecture clean and organized.
Streaming in Fabric: Streaming data into Fabric Lakehouse using Spark notebooks works well, but it’s not as plug-and-play as typical streaming services. Features like CDC merge logic, idempotency all need to be implemented manually.
Power BI: Is an amazing tool to display and work with Dashboards and makes super easy when you structure properly with STAR schema, geography and dates to display data through DirectLake. Still DAX measures somehow are not as comfortable and easy to learn if you are more used to a SQL syntaxis.

Coming Next

📌 In the next post, I’ll dig into SalesForce Integration

👀 Follow me here on Medium to catch Part 3.

💻 Source code: https://github.com/zodraz/omnisync-fabric

OmniSync: A Real-World Architecture for Syncing Salesforce, D365 and Fabric in Near Real-Time (Part…

Abel — Thu, 24 Apr 2025 17:22:35 +0000

OmniSync: A Real-World Architecture for Syncing Salesforce, D365 and Fabric in Near Real-Time (Part 1)

A practical breakdown of building near real-time data sync across cloud CRMs and analytics using serverless Azure services.

Posts in this Series

Introduction

OmniSync started as a side project for building a near real-time, multi-system data synchronization using mostly Azure-native tools. It connects systems like Salesforce , Dynamics 365 (Sales), and Microsoft Fabric , with future plans to bring in SAP S/4HANA.

OmniSync is meant to sync with clean, scalable patterns that avoid tight coupling and support near real-time updates.

OmniSync uses:

Logic Apps to orchestrate
Event Hub + Event Grid for messaging
Azure Functions for lightweight transformation
Power BI and Fabric for analytics

Use Case

To put OmniSync into context, let’s imagine a scenario based on a fictional enterprise: Contoso Inc.

They’re a global company with:

Salesforce as the CRM for North America
Dynamics 365 Sales running in EMEA
SAP running backend finance in APAC
A legacy SQL Server warehouse stitched together with SSIS

And have these issues:

Different systems have different versions of the same customer
Sales data is duplicated, delayed, or just plain wrong
Nobody trusts the reports, especially executives

Contoso wants to fix this. So instead of rebuilding from scratch, the idea is to sync just enough data across platforms in near real time and centralize it for analytics.

Here’s what they’re aiming for:

Near real-time sync between Salesforce and Dynamics
Unification of customer and sales dat a across platforms
Power BI dashboard s driven by Microsoft Fabric

Minimal coupling — each system should still work on its own

Architecture

OmniSync is built on a loosely coupled, event-driven architecture, systems communicate through events or APIs.

Here’s how it works:

Integration Layer

Logic Apps handle orchestration. Each system has its own Logic App that listens to changes (via webhooks, APIs or CDC) and pushes updates to other systems
Azure Event Hub sits in the middle to send events to Fabric
Event Grid routes system-generated events like function triggers or retries
Azure Functions take care of small, stateless transformation or filtering jobs

Data Platform

Microsoft Fabric handles the analytics side. It ingests data , stores it in a Lakehouse , and exposes it through Power BI
Fabric follows a medallion architecture :

✅ Bronze: Legacy SQL Server warehouse

✅ Silver: Cleaned business objects (Accounts, Orders, etc.)

✅ Gold: Dashboard-ready data with measures and KPIs

Identity & Security

SSO on Azure and proper OAuth implementations
Integration users in Salesforce and D365 prevent update loops
Azure Key Vault stores secrets and credentials securely

Monitoring and observability

Azure Monitor as the overall platform for Monitoring
App Insights as detailed view for different applications
Log Analytics to see pertinent Logs

Event-Driven

When syncing systems like Salesforce and Dynamics 365, the challenge isn’t just moving data, it’s doing it without collisions, loops, or delays. OmniSync handles this using a clean event-driven architecture (EDA) that combines:

Change Data Capture (CDC) or webhook-based triggers
Sync origin tracking to avoid loops
Integration users to track synchronizations
Light transformation logic in Azure Functions or inline Logic App steps
Event Hub to route and decouple publishers from subscribers

Loop Prevention & Conflict Handling

When you’re syncing systems in both directions, the biggest problem is to avoid update loops and collisions between records that were never meant to sync.

OmniSync solves this in two key ways:

Loop prevention using tracking
Conflict detection using unique identifiers (like customer numbers)

How Loop Prevention Works

Every record that gets synced carries a hidden field:

If Salesforce sends an update to D365, the Logic App tags it with the integration user.
Then, when a Logic App monitoring D365 sees that record later, it checks if the user is the integration one . If that is true, the synchronization is skipped. This completely stops update loops without relying on timestamps or hashes.

Conflict Handling

But what if the same account gets created in both systems independently?

For instance, when a new Account is created in D365, the corresponding Logic App detects the change. Before inserting the record into Salesforce, a check is performed against the Fabric DataLake to determine if the record already exists. If a match is found, the sync_status field in D365 is updated to “Conflict”.

ETL

OmniSync uses a single ETL process to migrate legacy data from a SQL Server Data Warehouse into the new environment. This is done using Microsoft Fabric’s Dataflow Gen2 , a visual, user-friendly tool that makes it easy to extract and stage structured data.

From there, the data enters the Bronze layer of the Medallion architecture and flows through to the Golden Lakehouse layer.

This ETL step is just the starting point , once the historical data is loaded, all updates and sync operations are fully event-driven.

We’ll go deeper into this data flow in the follow-up article focused on Fabric + Medallion implementation.

Kappa & Lambda Architectures

Ideas from both architectures are taken, adapting them to fit on OmniSync.

Let’s break down what that means in practice.

Lambda Architecture

The Lambda architecture was designed to combine:

A batch layer (slow, reliable processing)
A speed layer (real-time but eventually consistent)
A serving layer (where queries run)

It works well for big data pipelines — but it’s overkill for sync. Managing two parallel paths (real-time and batch) adds complexity and technical debt that OmniSync doesn’t need. Plus, it assumes you’re rebuilding and querying from large volumes of data — not syncing discrete, structured entities like Accounts and Orders.

Kappa Architecture

The Kappa model removes the batch layer and handles everything via streaming. You store events once, replay them as needed, and build everything on top of that.

In OmniSync, that’s closer to the truth , especially with Event Hub acting as the event source of record. But OmniSync still isn’t doing full stream processing, continuously transforming big scale data. We’re handling discrete change events with lightweight logic and routing.

OmniSync’s Actual Model: Event + Delta + Lakehouse

What OmniSync really does is combine:

Event-based triggers (CDC, webhooks)
Per-record transformation (Functions, Logic Apps)
Push-based syncing across systems
“Append-only” storage in Fabric Lakehouse

This gives us the best parts of:

Kappa’s streaming mindset
Lambda’s layered thinking (but simpler)
Fabric’s native support for medallion architecture

And most importantly: it’s a model that works for business systems , not just analytics engines.

Medallion Architecture

Once data is flowing between systems like Salesforce and Dynamics 365 OmniSync uses Microsoft Fabric with a Lakehouse model and Power BI for its Data Analytics.

We’ll use Dataflows Gen2 for the initial load, Notebooks to move data across the Bronze, Silver, and Gold layers, and Streaming Spark for CDC to materialize entities in real time. Power BI then connects directly to the Gold layer to visualize data coming from Salesforce and Dynamics 365.

More on the Lakehouse structure and Fabric in the next article.

Scope Simplification

The project intentionally narrowed its scope to keep things manageable.

In a full-scale retail implementation, you’d have to deal with dozens of interconnected business processes, entities, dependencies, and flows.

But OmniSync is a PoC designed to explore new technologies and prove that near real-time, cross-system sync can work.

So instead of modeling everything, is focused on a smaller, representative set of entities that still reflects real-world complexity:

Currencies
Product Categories
Products
Stores
Sales Orders
Accounts
Geography

Each of these entities was used to build and test sync flows between Salesforce, Dynamics 365, and Microsoft Fabric, with the goal of validating patterns, not rebuilding an entire enterprise data model.

API Usage

Most of the API interactions in OmniSync are handled indirectly. Thanks to Azure Logic Apps connectors and the built-in abstractions in Salesforce and Dynamics 365, there’s usually no need to manually call APIs or manage HTTP flows yourself.

Still there were a few cases where direct API calls were unavoidable:

Salesforce CDC Setup

Salesforce’s Change Data Capture requires manual setup steps via API specifically to:

Create Streaming Channels
Register Channel Members

A Postman REST collection has been used to setup those:

Salesforce CDC API Reference (Postman)

GeoApify: Geolocation for the Lakehouse

To enrich location-based data like Stores and Geography in the DataLake, OmniSync used GeoApify, which is a lightweight, reliable geolocation API that converts addresses or coordinates inputs into normalized location data.

GeoApify Docs

Below you can see a sample of the JSON returned for the GeoCoding API passing a latitude and longitude parameters.

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "datasource": {
          "sourcename": "openstreetmap",
          "attribution": "© OpenStreetMap contributors",
          "license": "Open Database License",
          "url": "https://www.openstreetmap.org/copyright"
        },
        "country": "United Kingdom",
        "country_code": "gb",
        "state": "England",
        "county": "Greater London",
        "city": "London",
        "postcode": "W1H 1LJ",
        "suburb": "Marylebone",
        "street": "Upper Montagu Street",
        "housenumber": "38",
        "iso3166_2": "GB-ENG",
        "lon": -0.160306360235508,
        "lat": 51.52016005,
        "state_code": "ENG",
        "result_type": "building",
        "formatted": "38 Upper Montagu Street, London, W1H 1LJ, United Kingdom",
        "address_line1": "38 Upper Montagu Street",
        "address_line2": "London, W1H 1LJ, United Kingdom",
        "category": "building.residential",
        "timezone": {
          "name": "Europe/London",
          "offset_STD": "+00:00",
          "offset_STD_seconds": 0,
          "offset_DST": "+01:00",
          "offset_DST_seconds": 3600,
          "abbreviation_STD": "GMT",
          "ab

Logic Apps as the Workflow Engine

OmniSync uses Azure Logic as the low-code orchestrator that ties everything together.

Every synchronization operation from listening to Salesforce events to pushing data into Dataverse runs through a Logic App.

Why Logic Apps?

Low-code, but powerful: Easily hook into HTTP endpoints, Dataverse, Event Hub, SQL, and more
Built-in error handling: Retry policies, scopes, and parallel branches make it easy to isolate failures
Easy to understand: Non developers like Business analysts can see workflows without needing to read code

Use Cases in OmniSync

Listening to Salesforce platform events or Dataverse change notifications
Tranformations (XML to JSON…)
Formatting data (datettimes…)
Constraints on checking data (data already inserted…)
Validation logic on synchronization cycles
Enriching payloads with metadata (sync_status…)
Routing data into Event Hub, Event Grid or Azure Functions

Logic Apps as a Low code tool made for orchestration is a great tool with flexibility and simplicity.

CI/CD

All source code used in OmniSync is stored in several public GitHub repositories and deployed using GitLab Actions across multiple projects. Each GitLab project is responsible for deploying a specific part of the architecture:

Main infrastructure is provisioned via Bicep templates
Azure Container Apps are deployed using Bicep as well
Azure Functions are built and published from source using CI pipelines

This setup automates infrastructure and app deployment from code to cloud.

Fabric, SalesForce and Dynamics 365 source code can be deployed manually through CLI or for better use integrated and deployed in their respective Platforms since they provide connectors to GitHub on their side.

For the purposes of this PoC, we’re not managing separate Dev, Staging, and Production environments. However in a real-world scenario, a proper promotion model with multiple environments would be essential — and this architecture is designed to support that when needed.

Costs

OmniSync was designed to be cost-effective, especially on the Azure side.

Of course, you’ll still need valid licenses for Salesforce, Dynamics 365, Microsoft Fabric, and GeoApify, but most of these platforms offer free basic trials, which are more than enough to test and validate the system.

Azure Cost Optimizations

To keep Azure costs under control, OmniSync uses:

Azure Consumption Logic Apps — pay only when a workflow runs
Azure Consumption Functions — event-driven, billed per execution
Azure Container Apps — deployed with minimum CPU and memory, and limited to one replica
All supporting Azure services (Event Hub, App Insights, Key Vault…) are configured using Free or Basic SKUs

Even Microsoft Fabric’s free trial includes access to an F64 SKU, which far exceeds the compute needs of this PoC and allows you to explore the entire Lakehouse and Power BI setup at full scale for free.

Scalability

OmniSync is built entirely on serverless and event-driven components, scalability is not an issue by design. The architecture can grow automatically with demand, without needing any manual scaling logic, complex provisioning, or infrastructure refactoring.

On the Azure side, all core services scale seamlessly:

Azure Logic Apps can run multiple parallel instances of the same workflow with built-in throttling and retry logic.
Azure Functions automatically spin up additional compute to handle bursts of sync events or large payloads.
Azure Container Apps are configured with a minimal baseline (1 replica, low CPU/memory)
Azure Event Hub and Event Grid are designed for high-throughput, low-latency messaging and can absorb tens of thousands of events per second if needed.

Even though the PoC traffic is minimal, this same stack could support enterprise scale volumes without changes to the architecture.

On the Microsoft Fabric side, scaling is easy. Since it’s a fully managed SaaS platform, you don’t need to worry about infrastructure. If things get heavy, like high sync volume or refresh rate, you can just increase the capacity (e.g., F8 to F16) with a few clicks.

Reliability

Since the project prioritized cost over high availability, the reliability approach was intentionally kept lean, focusing only on zone redundancy only (not region wise).

Here’s how redundancy was handled across key Azure services:

Azure Logic Apps: ❌ Not zone-redundant
Azure Consumption Functions: ✅ Zone-redundant
Azure Event Grid: ✅ Zone-redundant
Azure Container Apps: ❌ Not zone-redundant
Azure Event Hub: ✅ Zone-redundant
Azure Storage Accounts: ✅ Zone-redundant
Azure Container Registry: ✅ Zone-redundant
Integration Account: ❌ Not zone-redundant
Azure Key Vault: ✅ Zone-redundant
Azure Monitor: ✅ Zone-redundant
Log Analytics: ✅ Zone-redundant
Application Insights: ✅ Zone-redundant
Microsoft Entra (Azure AD): ✅ Globally available

Since zone redundancy was considered sufficient for the scope and scale of the PoC, no separate DR strategy was implemented.

For a production deployment, a more solid decision should be revisited and evaluate things like multi-region failover, backup strategies, and high-availability tiers.

Performance

While this project prioritized cost optimization, performance was never ignored. Even with the most basic SKUs across the Azure stack, OmniSync delivers solid real-world responsiveness thanks to its event-driven and auto-scaling architecture.

No formal performance or stress testing was conducted, but manual tests syncing entities across the system typically complete in just a few seconds or up to ~20 seconds in edge cases; depending on factors like cold starts (Functions, Container Apps), retry logic or latencies.

Even without tuning for performance, the architecture has proven to be fast enough for operational needs while keeping costs low.

Note: Fabric’s free F64 SKU was not considered as part of the performance baseline, since it vastly exceeds this project’s actual requirements.

If this were to move into production, full load testing and performance profiling would be the logical next step — but that’s outside the scope of this PoC.

Monitoring & Error Handling

OmniSync uses Azure-native monitoring tools to track activity, detect issues, and collect performance metrics across all key components — from Logic Apps to Container Apps to Microsoft Fabric.

Monitoring Setup

All apps and services are wired into Azure Monitor, with the following setup:

Application Insights is enabled across Azure Functions and Container Apps for telemetry, dependency tracking, and live logs.
A Log Analytics Workspace is used to centralize monitoring across services.
Logic Apps Monitoring Solution is deployed and connected to the workspace for advanced Logic App logging beyond the default run history.

This provides visibility across every layer of the sync process: API calls, retries, internal events, and more.

Logs & Metrics

Different services push logs and metrics in different ways:

Logic Apps: Errors and run history can be viewed directly in the Logic App blade or queried through Log Analytics via the monitoring solution.
Container Apps: Logs are streamed into Azure Monitor and viewable via the Log stream or Log Analytics.
Fabric: Metrics (especially for Spark executions) can be queried using KQL inside the Fabric workspace.

Here’s a sample query used to inspect memory usage of Spark workloads:

SparkMetrics_CL
| where fabricWorkspaceId_g == "{FabricWorkspaceId}"
and artifactId_g == "{ArtifactId}"
and fabricLivyId_g == "{LivyId}"
| where name_s endswith "jvm.total.used"
| summarize max(value_d) by bin(TimeGenerated, 30s), executorId_s
| order by TimeGenerated asc

Error Detection & Diagnostics

OmniSync includes multiple built-in mechanisms to track and detect errors:

Dead Letter Queues (DLQ) on Event Grid handle failures in event delivery when downstream handlers fail.
Application Insights provides rich exception tracking and telemetry for Azure Functions and Container Apps.
Azure Monitor Logs (via KQL) are used for deeper insights across services, especially for correlating multi-step sync issues.
Logic App Run History allows quick inspection of individual failed runs, including step-by-step trace.

These tools offer both real-time observability and post-mortem diagnostics without adding external tooling.

Security

OmniSync implements security with simplicity and control in mind, sticking to platform standards, but also making practical decisions to keep the setup lightweight for a PoC.

Authentication

There’s no unified SSO across all platforms. Ideally, a single Microsoft Entra ID (Azure AD) user would be used to sign in across Salesforce, Dynamics 365, and Fabric using SSO (Single Sign-On).

But to keep things simple:

Each platform uses its own authentication system
Even Azure , Dynamics 365 , and Fabric don’t share the same Entra user (Still they should but the project and trials evolved with 3 different Entra users)
Platform specific users were configured instead of federating identity across systems

Authorization

Authorization was kept minimal for this PoC. In a production setup, roles would be more granular, but here most systems used default admin users or minimally scoped integration users:

Dynamics 365 Integration User : Assigned the Basic User and Audit roles.
Salesforce Integration User : Assigned a custom Integration Role.
Salesforce Client App : Connected via OAuth client credentials with minimum required permissions.
Fabric Client App : Entra App Registration with only: “Run Queries and Mutations” and “Power BI Service” access

Other Secrets & Access Keys

The architecture also relied on a few platform specific keys and credentials:

Event Grid : Accessed using access tokens
Event Hub : Secured with SAS tokens
Salesforce : OAuth 2.0 client credentials flow for integrations
SPA : OAuth 2.0 Delegated Entra permissions
GeoApify : External API key for resolving geolocation data
Azure services : Use Managed Identity when possible for secure inter-service authentication

Most secrets were not stored in Azure Key Vault , intentionally, to simplify the PoC. However:

The GeoApify API key and Container App certificates were securely stored in Azure Key Vault

Network Security & Private Access

Private endpoints were not used in OmniSync. Since the architecture didn’t require private networking or integrate with on-prem/IaaS resources, all traffic was routed publicly over the internet.

This choice simplified the infrastructure and avoided unnecessary VNET configurations which is acceptable for a proof-of-concept like this.

Lessons Learned

Working on OmniSync provided a lot of insight into what it takes to integrate multiple enterprise systems together using native Azure tools. Here are some of the key lessons from building the PoC:

Event-Driven Patterns and Preventions: while Logic Apps and Event Grid make near real-time sync simple, loop prevention is essential. Without these, you can quickly end up in an infinite update cycle between systems.
Loosely Coupled architecture: by not using coupled services (Logic Apps, Event Hub, Functions) it makes easy to add or change systems (like SAP later) without rewriting the whole flows.
Monitoring: even the project is at a small scale monitoring becomes important anyhow. Azure Monitor, App Insights, and simple KQL queries made it easy to trace errors and understand behavior without needing external tools.
(Near) Real-Time Sync ≠ Instant Synchronization: Even in an event-driven system, things like cold starts, retries, and API latency mean synchronizations that won’t always be sub-second. But 20 seconds at max for this end-to-end scenario is still very usable for operational needs.
Platform ecosystem: to integrate with SalesForce, Dynamics 365 and Fabric you are forced at least to understand and implement few things on those not familiar (until now) features like Flows on SalesForce, Power Apps or Lakehouses and DAX queries on Power BI.
Low Code tools: most of the project was built without writing much code thanks to the low-code and visual tools provided by each platform. Logic Apps acting as the iPaaS layer handled most of the orchestration. Overall, these low-code tools significantly reduced development effort and complexity.
Fabric: t urned out to be a strong choice for the analytics layer. While getting started with Lakehouse concepts and Spark notebooks wasn’t easy at first, it paid off quickly. It took time to ramp up, but in the end, Fabric offered a modern, scalable, and efficient platform for centralized reporting and analytics.

Future Plans

OmniSync already connects Salesforce, Dynamics 365 (Sales), and Microsoft Fabric still on the roadmap there’s still SAP S/4HANA Integration

Future plan is to use:

SAP Event Mesh to publish change events (e.g. Product, Order, Customer)
Azure Logic Apps for SAP to consume and route those events
Reuse existing OmniSync sync patterns: conflict detection, and integration users

This will complete the loop across CRM, ERP, and analytics.

Coming Soon

📌In the next post, I’ll dig into Fabric Integration.

👀 Follow me here on Medium to catch Part 2.

💻 Source code: https://github.com/zodraz/omnisync-docs

TimeWarden: An autonomous multi-agent with Copilot Studio

Abel — Sat, 08 Feb 2025 00:21:46 +0000

Time-Off Management with Autonomous AI in Microsoft Copilot Studio

After watching Ignite 2024 this October, I was amazed by the capabilities showcased in the Copilot autonomous agent presentations. Intrigued by their potential, I set out to explore and test these features, which led me to create a project designed to deepen my understanding of this innovative technology. This is how TimeWarden came to life: a multi-agent solution developed using Copilot Studio.

Copilot Studio

Copilot Studio provides a powerful platform for building autonomous AI agents that understand work processes and act on behalf of users. By combining managed SaaS infrastructure, advanced AI models, a low-code design interface, and a vast library of prebuilt connectors, these agents can automate tasks, support business roles, and enhance efficiency across various applications and data sources.

Key Benefits

Efficiency: Autonomous agents handle repetitive tasks, freeing up employees to focus on more complex and creative work.
Accuracy: By automating processes, agents reduce the risk of human error, ensuring more accurate and consistent outcomes.
Scalability: Agents can be scaled to support large organizations, handling high volumes of tasks without additional human resources.
Cost Savings: Automating routine tasks can lead to significant cost savings by reducing the need for manual labor.
Improved Customer Experience: Agents can provide real-time support and information, enhancing customer satisfaction.

Characteristics

Autonomous Functionality: Operating continuously, they proactively monitor business events and execute actions without manual intervention.
Dynamic Workflow Planning: Their ability to create and adjust execution strategies in real time enables flexibility in complex environments.
User-Friendly Integration: With low-code customization, organizations of all sizes can tailor these agents to address specific operational challenges.

These innovations mark a significant step toward creating AI-first business processes, promising to streamline operations and drive measurable business value.

TimeWarden

TimeWarden is an autonomous multi-agent system designed to manage employee time-off requests by cross-referencing them with a Jira project. It intelligently determines whether to approve or reject requests automatically or escalate them for project manager approval based on specific conditions.

It is not intended for production use but serves as a proof of concept (POC) for exploring the potential of this technology. Due to this, it has multiple limitations, and the scenario is constrained.

Additionally, TimeWarden can be extended or updated as needed. For example, while the current trigger is an email-based time-off request, it could be replaced with a Power Platform Form App or any other trigger supported by Copilot Studio. In this case, I chose email as a simplified scenario, allowing TimeWarden and its supporting agents to interpret and categorize the message.

Description

One of the first steps in creating a Copilot is providing a description of the agent, outlining its purpose, target audience, and desired outcomes.

Instructions

The Instructions section defines how the agent behaves, outlining its tasks and the approach it takes to complete them. Below are the instructions for TimeWarden:

Step 1: Checking the Email

Verifies if the email is a time-off request. If not, it stops processing.
Checks if the sender belongs to the Contoso project team. If not, it sends a rejection email explaining why.
Ensures all required details (dates, hours, reason, type of leave) are provided. If anything is missing, the requester receives an email listing the missing fields.

Step 2: Reviewing the Sprint Schedule

Retrieves the latest sprint schedule from Jira to assess how the request fits into the project timeline.
Categorizes the request into: – Current Sprint (happening now) – Future Sprint (upcoming) – Outside Sprint (not affecting any sprint)

Step 3: Handling Current Sprint Requests

Checks if the employee has sufficient time-off balance.
If everything is fine, the request is sent to the manager for approval.
If approved, the system updates the records and notifies the requester.

Step 4: Managing Requests Outside a Sprint

If no sprint is affected and the leave balance is sufficient, the request is automatically approved.
If the balance is insufficient, the request is rejected, and the employee is notified.

Step 5: Handling Future Sprint Requests

Assesses whether the absence impacts sprint capacity.
If the request does not reduce sprint availability below 80%, it is approved automatically.
Otherwise, it is escalated to a manager for review.

At every step, TimeWarden ensures employees and managers stay informed via automated emails, streamlining time-off management.

Notice that intentionally a simplified version is provided rather than the exact instructions from Copilot Studio. One key lesson learnt through experimentation was prompt engineering , which let me to rework and refine multiple times those instructions to make them more precise and detailed so that Copilot Studio could accurately understand and execute them.

Prompt engineering is the practice of designing and refining inputs (prompts) to guide AI models, like ChatGPT or Copilot, to generate accurate, relevant, and useful outputs. It involves structuring questions, instructions, or contextual information in a way that optimizes the AI’s response.

Autonomous Triggers

Autonomous Triggers in Copilot Studio are event-driven mechanisms that allow AI agents to take action automatically, without requiring direct user input. These triggers detect specific conditions-such as an incoming email, a database update, or a scheduled event-and initiate workflows or responses accordingly, having Copilot Studio hundreds of those.

TimeWarden detects incoming emails that are likely time-off requests and parses their content to extract all the necessary information to create a time-off request.

Dynamic Agent Plans

Copilot Studio allow AI agents to create and adjust execution strategies on the fly based on real-time data and evolving conditions. Instead of following a fixed workflow, these agents analyze the situation, determine the best course of action, and adapt as needed.

So depending on the use case on the instructions they will do one thing or another for example let’s see a TimeWarden example:

An employee requests time off.
The agent checks sprint schedules and workload balance dynamically.
If the request fits within capacity, it auto-approves it.
If it risks project delays, the agent escalates it to a manager for review.
If a manager rejects the request, the agent suggests alternative dates based on team availability.

Activity

When testing your agent Copilot Studio provides an Activity Map for every session, offering a visual representation of an agent’s sequence of inputs, decisions, and outputs. This helps in identifying issues and improving workflows.

Users can also access the Activity page to review:

Session details
Step-by-step activities within a session

Actions

Actions in Copilot Studio are pre-built functionalities that can be used to extend and automate workflows. They allow you to perform specific tasks, such as generating code, making API calls, manipulating data, or integrating with other services, all through a simple interface.

There are different type of actions and hundreds which could be used, but the ones used by TimeWarden are custom Power Automate flows (except for the Send Mail action)

Power Automate

As mentioned before, TimeWarden’s actions use Power Automate flows, as well as other features from Power Automate like custom connectors, AI Hub, and custom prompts, which we will explain later. This integration allows Copilot Studio to automate tasks, connect with external services, and enhance AI-driven workflows, enabling Copilots to fetch data, update records, and execute complex actions seamlessly.

Custom Connectors

A Custom Connector in Power Automate allows you to create your own connectors to integrate services or APIs that aren’t already available in Power Automate’s built-in connectors.

Since the Jira connector didn’t provide the needed actions needed and BambooHR connector didn’t exist, 2 custom connectors were created with the needed actions to integrate those APIs with the flows.

Flows

A flow in Power Automate is an automated process that connects different apps and services to perform tasks without manual intervention. There are different flows but the ones used are Cloud Flows which connect TimeWarden Copilot through events which map to those Copilot actions, properly specifying input and output parameters (if specified).

Approvals

TimeWarden uses Microsoft Teams Approvals (powered by Power Platform), allowing users to create, manage, and track approval requests directly within Teams.

Depending on the scenario, a manager approval may be required to create a time-off request. In this case, the manager will receive a Teams notification containing all the relevant details provided by the user, allowing them to approve or reject the request.

Multi Agent

One of the challenges with the initial version of TimeWarden was that it attempted to handle all tasks within the Instructions prompt. This approach proved ineffective in certain cases, such as categorizing emails or identifying missing fields. The solution was to introduce specialized agents dedicated to specific tasks, working together under the coordination of TimeWarden, which serves as the main orchestrator. This led to the creation of three additional agents:

Time Off Request Categorizer
Email Time Off field extractor
Sprint classifier

All these agents are available in the AI Hub of Power Automate, a centralized platform that allows users to integrate AI-driven capabilities into their automation workflows. It offers access to pre-built AI models, custom AI models, and Azure OpenAI services.

Time Off Request Categorizer

Below is a picture displaying the actual prompt. This prompt essentially analyzes a text (such as an email) and classifies it into various categories, including Vacation, Sick, etc. The prompt’s response is provided in natural language.

In the next picture, you can see that it is activated within the email trigger flow, and its output is directed to the TimeWarden Copilot.

Email Time Off field extractor

As we can see in the following prompt image, this agent analyzes an email to determine if any fields are missing, like Start Date or End Date. Additionally, it takes into account whether a Time off Type parameter is specified, without needing to check if that field is missing.

As before, it is triggered within the email flow, and its output is sent to TimeWarden Copilot.

Sprint classifier

Another agent with an interesting functionality classifies a sprint as current, future, or outside based on a JSON containing all sprint data, along with the start and end dates of a time-off request.

The following image depicts a simple flow triggered by a Copilot. The Sprint Classifier agent is invoked with parameters received from the Copilot, and its output is then sent back to TimeWarden.

Lessons Learnt

During the development apart from learning about the technologies some lessons applying to LLM and AI were learnt.

Attempting to perform specialized actions, like recognizing whether an email was a time-off request with established categories, didn’t work as expected. This led to the creation of additional agents (prompts) that specialized in these tasks, enabling them to be successfully completed.
Prompt engineering provided the opportunity to explore and evaluate other LLMs, such as DeepSeek, Qwen, Copilot, and OpenAI. I leveraged them to help describe instructions for Copilot when the initial instructions were unclear or too vague for Copilot Studio to understand.
Even though Copilot Studio allows up to 8,000 characters for instructions, TimeWarden was limited to 4,400 characters, not even accounting for the prompts of other agents. This made me realize that for complex projects, instructions must be broken down into many smaller agents. Despite that, it still feels like not enough input space for a complex system. I hope Microsoft will increase this limit in the future.
Copilot Studio requires a high level of clarity for certain tasks, such as forcing it to stop the entire process or not execute specific parts of the instructions (i.e., conditional branches).
As Copilot Studio is still in preview, some tasks required workarounds, like disabling topics, as they should never be invoked by an autonomous agent.

Conclusions

TimeWarden demonstrates the transformative potential of integrating autonomous agents into everyday business processes. By leveraging Copilot Studio and some specialized agents, TimeWarden automates time-off request management. Throughout this project, several important insights have come to light:

Multi-Agent Collaboration: Breaking down complex tasks into specialized agents not only improved performance but also underscored the importance of a modular approach in designing scalable AI systems.
Prompt Engineering Mastery: The iterative refinement of prompts was crucial for aligning AI outputs with specific business logic. This process highlighted both the power and the current limitations of AI-driven instructions, prompting continuous enhancements.
Scalability and Flexibility Challenges: Although Copilot Studio provides powerful automation features, its limitation contraints and the requirement for explicit control in workflows pose challenges that need to be overcome to scale more complex solutions.

Overall, TimeWarden stands as a compelling proof of concept that not only streamlines time-off management but also lays the groundwork for next-generation AI-powered business automation. Additionally, the continuous evolution of Copilot Studio and autonomous agents is likely to inspire further innovation in how organizations harness AI to drive measurable business value.

Source code

You can find all the source code with a detailed install instructions on:

GitHub - zodraz/TimeWarden: A multi-agent Copilot