A Four-Layer Architecture for an Executive Briefing Agent on Azure

Anyone preparing an executive for a customer meeting knows how scattered the raw material is. CRM records sit in one system, revenue figures in another, product usage in a third, and the strategic story has to be stitched together by hand across all of them. A "chief of staff" briefing agent targets exactly that chore: a person asks in plain language for a briefing, and the platform returns an executive-ready document built from fragmented operational, financial and AI-adoption data.

What makes the design worth studying is how little it leaves to chance. Instead of asking a model to write the briefing from a prompt, it replaces manual aggregation with orchestration bound by contracts. Data is consolidated under governance, aggregated by deterministic SQL, routed through stateless services and delivered through a controlled conversational flow. The architecture draws hard execution boundaries across four layers, namely foundation, data, service and experience, so that results can be reproduced, audited and delivered at enterprise speed within tight security limits.

The design comes out of an internal AI incubation effort in the Microsoft ecosystem, and the performance and outcome claims below are the builders' own, not independently measured. It's most useful for enterprise architects, AI engineers and platform leads who need deterministic AI systems that separate data consolidation, SQL aggregation, stateless orchestration and conversational workflows.

The four-layer design

The agent follows a four-layer orchestration model, each layer having a narrow job and spanning several Azure services and security boundaries:

• Foundation: a Microsoft Fabric Lakehouse that consolidates data under governance.
• Data: Fabric SQL (a Warehouse or SQL endpoint) holding stored procedures for operational queries and aggregation.
• Service: a FastAPI orchestration engine hosted in Azure Functions.
• Experience: a Copilot Studio conversational workflow that mediates user intent.

Together they form a one-directional intelligence pipeline. Data is curated and governed at the base, aggregated deterministically in SQL, assembled by domain logic in the service tier and finally surfaced through natural-language interaction. Every layer keeps an explicit contract with its neighbors, so the AI element adds to decision-making and doesn't muddy it.

Foundation layer: governed data consolidation

Microsoft Fabric acts as the unified analytics platform and the single source of truth for account details, financials, usage metrics and historical trends. The guiding rule is simple: store data once and reuse it many times, so nothing is duplicated between systems.

A pipeline refreshes daily, pulling and merging enterprise data from Dynamics 365, financial systems and outside APIs. The schedule gives predictable processing windows and makes sure every source system has finished its own daily work before consolidation begins. The lakehouse keeps data in its native format together with lineage and governance standards, and refined datasets then move into Fabric SQL, the fast operational query layer whose structures are tuned for how executive briefings get generated.

Data layer: an operational engine built for API access

This layer converts curated foundation data into deterministic, low-latency query contracts for briefing generation. It sets strict aggregation boundaries and the performance guarantees executives expect from a responsive tool.

Architecture. The Fabric SQL Warehouse is a relational interface that wraps every briefing-related data operation inside stored procedures. The service tier never queries tables directly. Complex joins across tables, the meaning of each aggregation and the business logic all run inside the database and not in application code, which keeps response times predictable at large data volumes. The SQL is tuned for operational queries, not analytical exploration, and the design aims for deterministic joins that give consistent results, reusable query plans for stable performance, parameterized execution for safety and encapsulated aggregation that's easier to maintain.

Schema philosophy: query-first. The schema follows the query patterns and not textbook normalization. The Account entity is the main clustering structure, and supporting tables are arranged so joins run efficiently on the business identifier, the TPID.

     --Accounts Table - Primary Business Entity

CREATE TABLE [aieo].[Accounts] (

    [Id] INT IDENTITY(1,1) PRIMARY KEY,

    [TPID] BIGINT NOT NULL,

    [AccountName] NVARCHAR(255) NOT NULL,

    [AccountNumber] NVARCHAR(50),

    [SearchTokens] NVARCHAR(500),

    [Segment] NVARCHAR(100),

    [Industry] NVARCHAR(100),

    [ACR_FY25] DECIMAL(15,2),

    [RevenueRank] INT,

    [CreatedDate] DATETIME2 DEFAULT GETUTCDATE(),

    [LastSync] DATETIME2,

    [IsActive] BIT DEFAULT 1

);



CREATE CLUSTERED INDEX IX_Accounts_TPID ON [aieo].[Accounts]([TPID]);

CREATE NONCLUSTERED INDEX IX_Accounts_Name ON [aieo].[Accounts](

The Accounts table is the central entity, with TPID as its clustering key. That supports roughly logarithmic retrieval for the most common account-specific lookups, and it handles both direct TPID queries and searches by name.

Supporting tables. A few of the supporting tables show how financial metrics, AI usage insights and cross-system enrichment are handled.

-- Revenue Table - Historical and Forecast Data

CREATE TABLE [aieo].[Revenue] (

    [TPID] BIGINT,

    [FiscalYear] NVARCHAR(10),

    [RevenueType] NVARCHAR(20),

    [Amount] DECIMAL(15,2),

    [LastUpdated] DATETIME2

);

CREATE INDEX IX_Revenue_TPID_FY ON [aieo].[Revenue]([TPID], [FiscalYear]);

The Revenue table keeps several years of financial data and uses composite indexes that suit time-series and trend calculations.

-- AI Usage Table - Product Adoption Metrics

CREATE TABLE [aieo].[AIUsage] (

    [TPID] BIGINT,

    [ProductCategory] NVARCHAR(50),

    [UsageLevel] NVARCHAR(20),

    [SeatCount] INT,

    [AdoptionRate] DECIMAL(5,2),

    [LastUpdated] DATETIME2

);

CREATE INDEX IX_AIUsage_TPID_Product ON [aieo].[AIUsage]([TPID], [ProductCategory]);

The AI Usage table records product adoption by category, which enables analysis of engagement patterns and technology uptake that matter for account planning.

Stored procedure strategy: business logic lives in the database. Three core procedures hold all the intelligence generation. SearchAccounts finds accounts and ranks them by revenue, GetAccountDetails aggregates data from many sources into the full context for an executive, and GetBriefingTemplate governs role-based template authorization so only appropriate people get access. The service layer calls procedures and nothing else, and building dynamic SQL there isn't permitted.

--Account Search Procedure - Revenue-Prioritized Discovery

CREATE OR ALTER PROCEDURE [aieo].[SearchAccounts]

    @searchTerm NVARCHAR(255),

    @top INT = 5

AS

BEGIN

    SET NOCOUNT ON;



    DECLARE @likeTerm NVARCHAR(257) = '%' + REPLACE(REPLACE(@searchTerm, '[', '[[]'), '%', '[%]') + '%'



    SELECT TOP (@top)

        a.TPID,

        a.AccountName,

        a.AccountNumber,

        ISNULL(acr.ACR_YTD, 0) as ACR_YTD

    FROM [aieo].[Accounts] a

    LEFT JOIN [aieo].[ACR] acr ON a.TPID = acr.TPID

    WHERE

        a.AccountName LIKE @likeTerm

        OR CAST(a.TPID AS NVARCHAR) = @searchTerm

        OR a.AccountNumber LIKE @likeTerm

    ORDER BY

        ISNULL(acr.ACR_YTD, 0) DESC,

        a.AccountName ASC

    FOR JSON PATH;

END;

--Account Details Retrieval Procedure - Comprehensive Intelligence Aggregation

CREATE PROCEDURE [aieo].[GetAccountDetails]

(@TPID int)

AS

BEGIN

    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;



    SELECT TOP 1

        JSON_QUERY(ap.[AccountProfile]) AS [Account],

        JSON_QUERY(ai.[AIUsage]) AS [AI],

        JSON_QUERY(acr.[Data]) AS [ACR],

        deal.[Deals.AgreementValue],

        deal.[Deals.DealType],

        deal.[Deals.Term],

        deal.[Deals.Remaining],

        deal.[Deals.EndDate],

        ecif.[ECIF.Committed],

        aco.[ACO.IncrementalRevenue],

        aco.[ACO.Discount],

        aco.[ACO.ACO],

        JSON_QUERY(bot.[Data]) AS [BoT],

        JSON_QUERY(inv.[Data]) AS [Investments],

        JSON_QUERY(rev.[Data]) AS [Revenue]

    FROM [dbo].[vw_Customer] P

    OUTER APPLY (

        SELECT TOP 1

            a.[TPID],

            a.[CRMAccountName] AS [AccountName],

            a.[Segment],

            a.[Industry],

            a.[EOU],

            a.[OU],

            LOWER(TRIM(c.[Value])) AS [ATU_Manager.Alias],

            aau.[Mail] AS [ATU_Manager.Email],

            [dbo].[RemoveJobTitle](aau.[DisplayName]) AS [ATU_Manager.DisplayName],

            NULLIF(TRIM(aau.[BusinessPhone]), '') AS [ATU_Manager.PhoneNumber],

            JSON_QUERY([am].[Data]) AS [AM],

            JSON_QUERY([atum].[Data]) AS [ATUM]

        FROM [dbo].[vw_Customer] A

        OUTER APPLY STRING_SPLIT(a.[AM], ',', 1) C

        LEFT JOIN [cm].[vw_AAD_User] AAU ON LOWER(TRIM(C.[Value])) = LOWER(AAU.[UserPrincipalName])

        WHERE A.TPID = P.TPID

    ) ap

    WHERE P.TPID = @TPID;

END;

--Template Management Procedure - Role-Based Access Control

CREATE OR ALTER PROCEDURE [aieo].[GetBriefingTemplate]

(

    @UserAlias NVARCHAR(200),

    @BriefingType NVARCHAR(200)

)

AS

BEGIN

    SET @UserAlias = IIF(CHARINDEX('@', @UserAlias) > 0,

                        LEFT(@UserAlias, CHARINDEX('@', @UserAlias) - 1),

                        @UserAlias)



    SELECT DISTINCT

        [BriefingTemplateId],[TemplateDescription],[BriefingType],

        [UserAlias],FullName,[Filename],[FileType],[PreviewFilename]

    FROM [hr].[DimPerson] p

    INNER JOIN [aieo].[ExecutiveOfficeMember] eom ON eom.[PersonnelNumber] = p.[PersonnelNumber]

    INNER JOIN [aieo].[ExecutiveOffice] o ON o.[ExecutiveOfficeId] = eom.[ExecutiveOfficeId]

    INNER JOIN [aieo].[BriefingTemplate] bt ON bt.[ExecutiveOfficeId] = o.[ExecutiveOfficeId]



    UNION



    SELECT DISTINCT

        [BriefingTemplateId],[TemplateDescription],[BriefingType],

        p.EmailName As UserAlias,p.FullName,[Filename],[FileType],[PreviewFilename]

    FROM [aieo].[BriefingTemplate] bt

    CROSS JOIN [hr].[DimPerson] p

    WHERE bt.[UserAlias] = 'ALL' AND p.EmailName = @UserAlias

        AND bt.BriefingType = @BriefingType;

END;

Performance and security. Transactions use SET NOCOUNT ON for efficiency, and SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for read operations so they don't block. JSON handling relies on SQL Server's native JSON_QUERY and FOR JSON PATH, which handle processing efficiently and serialize directly from the database. Only parameterized execution is allowed, and the application's service identity receives execute permission on the stored procedures alone, with no direct access to tables.

GRANT EXECUTE ON SCHEMA::[aieo] TO [service_identity];
 - No direct table access permitted

Service layer: stateless orchestration and the execution boundary

The service tier does pure data orchestration. It retrieves data and renders templates and applies no business rules of its own. Each service has one responsibility and is loosely coupled, which keeps the data and presentation layers cleanly apart.

API surface and validation. A FastAPI app exposes explicit REST endpoints with strongly typed contracts defined as Pydantic models:

• POST /api/briefing/generate is the main briefing endpoint, with full input validation.
• GET /api/health reports system status.
• GET /api/templates lists the templates available to the calling user.

from pydantic import BaseModel
from typing import Optional
class BriefingRequest(BaseModel):
account_name_or_tpid: str
meeting_datetime: Optional[str] = None
meeting_objective: Optional[str] = None
ms_attendees: Optional[str] = None
template_type: Optional[str] = None

The model enforces strict validation and type safety, produces OpenAPI documentation automatically and fixes the shape of every request. No unstructured input gets past this boundary.

Execution model. The service runs a deterministic five-step sequence:

1. Request validation. Pydantic models make sure types and inputs are acceptable.
2. Account resolution. The input format decides whether to look up a TPID directly or to call SearchAccounts for a name search.
3. Concurrent retrieval. GetAccountDetails and GetBriefingTemplate run at the same time through asyncio.gather().
4. Context assembly. Procedure results map straight onto template-ready objects with no intermediate processing.
5. Template rendering. Jinja2 produces the executive document with deterministic formatting.

Asynchronous execution and connection pooling through a DatabaseService abstraction mean that identical inputs yield identical outputs.

Guiding principles. Five rules govern the service layer. There's no business logic in it, since intelligence is generated only inside stored procedures, which spares the service the rules that complicate testing and upkeep. It does pure data orchestration, coordinating retrieval and rendering without transforming or calculating anything. Context mapping is direct, so procedure results go to template structures without detours, lowering the risk of meaning drifting between data and presentation. Processing is deterministic, giving the same output for the same input whatever the timing or infrastructure state. And concerns stay cleanly separated: data logic lives in SQL procedures, orchestration in Python FastAPI services and presentation in Jinja2 templates.

Experience layer: Copilot Studio as the conversation engine

At the top, the AdaptiveDialog framework in Microsoft Copilot Studio turns a natural-language request into a deterministic workflow. It handles account resolution, template authorization, external data integration, document generation and delivery across several channels. The flow runs sequentially from the first request through account resolution, template authorization, external data gathering and delivery to SharePoint, with decision points and validation steps along every conversation path.

Core dialog. The dialog collects the account identifier automatically, defines the trigger phrases that start a briefing and sets the main entry point of the conversation:

kind: AdaptiveDialog

inputs:

  - kind: AutomaticTaskInput

    propertyName: Account_Name_or_TPID

    name: Account Name or TPID

    description: The account/customer TPID (unique identifier) or name

    shouldPromptUser: true



modelDescription: Create briefing document to prepare for meeting with customer

beginDialog:

  kind: OnRecognizedIntent

  intent:

    displayName: Topic for creating briefing document to prepare for meeting with customer

    triggerQueries:

      - Generate a briefing document for account Accenture

      - I need a briefing for Amazon

      - Create briefing for Apple

      - I have a meeting with JPMorgan Chase at 4PM

Account resolution. An IsNumeric() check classifies the input as either a direct TPID or an account name that needs searching. Skipping the search whenever possible cuts API calls and speeds up the reply:

- kind: SetVariable

  id: setVariable_IsNumeric

  variable: Topic.IsNumeric

  value: =IsNumeric(Topic.UserInput)



- kind: ConditionGroup

  id: conditionGroup_InputType

  conditions:

    - id: condition_IsTPID

      condition: =Topic.IsNumeric

      actions:

        - kind: SetVariable

          variable: Topic.Account_TPID

          value: =Topic.UserInput

Account search call. For names, the flow calls the service layer's search endpoint over REST. It URL-encodes special characters, authenticates with a subscription key and declares a response schema so the data is validated automatically:

- kind: HttpRequestAction

  id: httpRequest_SearchAccounts

  displayName: Search Accounts by Name

  url: =Global.ApiBaseUrl & "/api/mssales/search/" & EncodeUrl(Topic.UserInput)

  headers:

    Ocp-Apim-Subscription-Key: =Global.ApiKey

  response: Topic.SearchResults

  responseSchema:

    kind: Record

    properties:

      accounts:

        type:

          kind: Table

          properties:

            account_name: String

            tpid: String

      total_count: Number

Disambiguation card. If a search finds several accounts, an Adaptive Card shows a dynamic choice set built with ForAll() from the results, each entry displaying its TPID. A vague input therefore doesn't stall the conversation:

- kind: AdaptiveCardPrompt

  id: adaptiveCard_SelectAccount

  card: "=Concatenate('{

    \"$schema\":\"https://adaptivecards.io/schemas/adaptive-card.json\",

    \"type\":\"AdaptiveCard\",\"version\":\"1.5\",

    \"body\":[

      {\"type\":\"TextBlock\",\"text\":\"Multiple accounts found\",\"weight\":\"Bolder\"},

      {\"type\":\"Input.ChoiceSet\",\"id\":\"selectedAccount\",\"isRequired\":true,

       \"choices\":',

       JSON(ForAll(Topic.SearchResults.accounts,

         {title: Concatenate(account_name, \" (TPID: \", tpid, \")\"), value: tpid})),

       '}],

    \"actions\":[{\"type\":\"Action.Submit\",\"title\":\"Continue\"}]

  }')"

Template authorization. Through GetBriefingTemplate, the flow checks whether the user may use executive templates, based on organizational role and membership of the executive office. The principal name verifies identity, the briefing type filters the results, and template governance is secure by default:

- kind: HttpRequestAction

  id: httpRequest_GetUserTemplates

  url: =Global.ApiBaseUrl & "/api/briefings/templates?user_alias=" &

       EncodeUrl(System.User.PrincipalName) & "&briefing_type=Customer"

  headers:

    Ocp-Apim-Subscription-Key: =Global.ApiKey

  response: Topic.UserTemplates

Meeting details card. The next card uses progressive disclosure, showing required fields up front and tucking optional ones behind a toggle. The cognitive load drops while the context needed for a briefing is still collected:

- kind: AdaptiveCardPrompt

  id: adaptiveCard_MeetingDetails

  card: "=Concatenate('{

    \"$schema\":\"https://adaptivecards.io/schemas/adaptive-card.json\",

    \"type\":\"AdaptiveCard\",\"version\":\"1.5\",

    \"body\":[

      {\"type\":\"TextBlock\",\"text\":\"Create Executive Briefing Document\",

       \"weight\":\"Bolder\",\"size\":\"Large\",\"color\":\"Accent\"},

      {\"type\":\"Input.Date\",\"id\":\"meetingDate\",\"isRequired\":true,

       \"label\":\"Meeting Date\"},

      {\"type\":\"Input.Time\",\"id\":\"meetingTime\",\"value\":\"09:00\"},

      {\"type\":\"Input.Text\",\"id\":\"msAttendees\",\"isVisible\":false,

       \"label\":\"Microsoft Attendees\"}

    ],

    \"actions\":[

      {\"type\":\"Action.ToggleVisibility\",\"title\":\"Additional Options ▼\",

       \"targetElements\":[\"msAttendees\"]},

      {\"type\":\"Action.Submit\",\"title\":\"Generate Briefing\"}

    ]

  }')"

Parallel data gathering and document generation. The experience layer calls the Account Details API and Azure AI Foundry simultaneously, each with a 60-second timeout. After both answer, it builds one structured JSON payload from all the sources and passes it to the document generation API, with validation and assembly kept self-contained so the briefing is created deterministically.

Azure AI Foundry: organizational intelligence

The Foundry integration widens the agent's reach beyond external market data to include context from inside the organization, using an "M365 Researcher" component. It uses delegated Microsoft Graph access to search Outlook mail and Teams conversations, and it stays inside enterprise security boundaries and zero-trust principles.

Delegated Graph access without stored secrets. The researcher relies on delegated authentication that removes stored client secrets altogether. It uses connectors managed by Azure Logic Apps, which are first-party Microsoft applications with pre-consented delegated scopes. This skips the burden of managing app registrations while Azure handles the token lifecycle and automatic refresh.

On top of that, short-lived Logic Apps wrap access to a user's data behind HTTP triggers secured with SAS. Every search spins up a fresh Logic App instance, fetches data from Microsoft Graph in parallel and deletes the instance immediately afterward, so a SAS URL can't be replayed and no persistent token stays exposed.

// Ephemeral Logic App Security Pattern
const createSearchLogicApp = async (userAlias, searchQuery) => {
const logicAppName = `Researcher-${userAlias}`;
// Create Logic App with SAS trigger
const logicApp = await armClient.logicApps.createOrUpdate({
resourceGroupName: 'Azure_OpenAI',
workflowName: logicAppName,
definition: buildResearchWorkflow(userAlias)
});
// Execute search via SAS URL
const searchResults = await triggerLogicApp(logicApp.triggerUrl, searchQuery);
// Immediate cleanup for security
await armClient.logicApps.delete(logicAppName);
return searchResults;
};

Parallel retrieval and Graph tuning. Email and Teams data are gathered at the same time, with concurrency limits so many users' sessions don't trigger throttling. For mail, the Office 365 connector's bulk fetch pulls the latest 250 messages per user, and filtering happens on the client to work around Graph's limits on searching message bodies. For Teams, a two-phase approach first discovers chats through paginated calls and then retrieves messages from individual chat threads with a configurable concurrency cap.

# Logic App Parallel Execute Pattern

HTTP_Trigger_SAS_Secured:

  Email_Branch:

    - Get_Emails: Office365_Connector.Mail (top=250)



  Teams_Branch_Parallel:

    - Get_Teams_Chats_Page1: Teams_Connector.Chats (top=50)

    - Get_Teams_Chats_Page2: Teams_Connector.Chats (skip=50, top=50)

    - For_Each_Chat:

        concurrency: 10

        actions:

          - Get_Chat_Messages: Teams_Connector.Messages (top=50)

          - Append_Chat_Data: Variable_Accumulation



  Response_Assembly:

    - Combine: Email_Results + Teams_Results + Metadata

    - Return: JSON_Structured_Response

A typical search makes about 101 Graph API calls: one for mail, two to enumerate Teams chats and an average of 98 to fetch chat messages. That covers a lot of data while staying within Graph's concurrency limits and, according to the design, keeping response times under 60 seconds.

Per-user connections and tokens. Each user needs two managed connections that keep delegated OAuth tokens across search sessions: office365-{alias} for mail with the Mail.ReadWrite scope and teams-{alias} for chat with Chat.Read. Azure's managed connector infrastructure refreshes the tokens automatically and supports compliance. Provisioning follows a consent-driven flow in which the user signs in once through Microsoft's first-party OAuth experience and the delegated permissions last until they're revoked. The Function App manages connection lifecycle through Azure Resource Manager APIs under a managed identity, so no client credentials are stored in the application layer.

// Per-User Connection Provisioning

const provisionUserConnections = async (userAlias) => {

    const connections = [

        {

            name: `office365-${userAlias}`,

            api: '/providers/Microsoft.PowerApps/apis/shared_office365',

            scopes: ['Mail.ReadWrite']

        },

        {

            name: `teams-${userAlias}`,

            api: '/providers/Microsoft.PowerApps/apis/shared_teams',

            scopes: ['Chat.Read']

        }

    ];



    for (const conn of connections) {

        await armClient.connections.createOrUpdate({

            resourceGroupName: 'Azure_OpenAI',

            connectionName: conn.name,

            properties: {

                displayName: conn.name,

                api: { id: conn.api },

                parameterValues: {}

            }

        });

    }



    return generateConsentUrls(connections);

};

Synthesis with a GPT model. Raw communications are turned into briefing-ready organizational intelligence by a model from Azure OpenAI Service (described as GPT-4.1, though the sample code names a GPT-4 preview deployment). The pipeline converts filtered mail and Teams content into structured insights covering stakeholder analysis, communication patterns and actions relevant to the meeting. The prompts aim at executive needs, such as relationship mapping, project status and strategic themes, and the model returns structured JSON that slots into the existing Jinja2 templates with consistent formatting across all documents.

# Organizational Intelligence Synthesis

async def synthesize_organizational_context(filtered_data, account_context):

    synthesis_prompt = """

    Analyze the following organizational communications for executive briefing preparation:



    Account Context: {account_name}

    Email Communications: {email_count} relevant messages

    Teams Discussions: {chat_count} relevant conversations

    Generate executive intelligence focusing on:
    Financial performance, earnings trends, and capital allocation signals.
    Corporate strategy, market positioning, and competitive landscape.
    AI strategy, digital transformation initiatives, and cloud ecosystem alignment.
    Recent material developments (≤ 90 days) with actionable insights for executive engagement.

    Format as structured JSON with executive_summary, stakeholder_analysis, recent_activities, and recommended_actions.

    """



    response = await azure_openai_client.chat.completions.create(

        model="gpt-4-1106-preview",

        messages=[{

            "role": "system",

            "content": "You are an executive intelligence analyst."

        }, {

            "role": "user",

            "content": synthesis_prompt.format(**filtered_data, **account_context)

        }],

        temperature=0.1,

        max_tokens=2000

    )



    return parse_structured_intelligence(response.choices[0].message.content)

Integration into the dialog. The researcher joins the Copilot Studio workflow in parallel with the standard account data pull and the external market intelligence, so the additional organizational context isn't expected to slow overall briefing generation.

# Enhanced Copilot Studio Parallel Execution

- kind: ParallelExecution

  id: parallelExecution_ComprehensiveIntelligence

  branches:

    - account_data:

        kind: HttpRequestAction

        url: =Global.ApiBaseUrl & "/api/accounts/" & Topic.Account_TPID

    - organizational_intelligence:

        kind: HttpRequestAction

        url: =Global.ApiBaseUrl & "/api/search?q=" & EncodeUrl(Topic.Account_Name) & "&alias=" & System.User.PrincipalName

        requestTimeoutInMilliseconds: 60000

        continueOnError: true

    - market_intelligence:

        kind: HttpRequestAction

        url: =Global.FoundryBaseUrl & "/bingnews/api/AgentFunction"



- kind: SetVariable

  id: setVariable_CombinedIntelligence

  variable: Topic.BriefingContext

  value: ={

    account_details: Topic.AccountData,

    organizational_context: Topic.OrganizationalIntelligence,

    market_insights: Topic.MarketIntelligence

  }

Governance and compliance. Users can search only their own mail and Teams messages through delegated permissions, which prevents cross-user access and keeps data boundaries tight. Every Graph API call is logged with user context, search parameters and result metadata. Retention is configurable, and connections that go unused for a configured period, typically 80 days, are cleaned up automatically. Processing stays inside the Azure tenant, and data residency controls support the organization's governance policies.

// Enterprise Governance and Cleanup

const implementGovernanceControls = async () => {

    // Automated stale connection cleanup

    const staleThreshold = 80; // days

    const allConnections = await listManagedConnections();



    const staleConnections = allConnections.filter(connection => {

        const lastUsed = parseISO(connection.properties.lastConnection);

        const daysSinceUse = differenceInDays(new Date(), lastUsed);

        return daysSinceUse > staleThreshold;

    });



    // Compliance audit logging

    for (const connection of staleConnections) {

        await auditLogger.log({

            action: 'CONNECTION_CLEANUP',

            userAlias: connection.userAlias,

            reason: 'AUTOMATED_GOVERNANCE',

            retentionPolicy: `${staleThreshold}_DAYS_INACTIVE`,

            timestamp: new Date().toISOString()

        });



        await deleteUserResources(connection.userAlias);

    }

};

Performance and fault tolerance. The system caches user connection status and verifies connection health before running expensive Graph operations. If Graph is throttled or briefly unavailable, it degrades gracefully, using exponential backoff for transient failures and hard timeout limits so briefing generation isn't held up. When the organizational search fails or times out, the briefing still goes out with account data and external market intelligence, so executives get a consistent experience whether or not Microsoft 365 is available.

// Fault-Tolerant Execution Pattern

const executeOrganizationalIntelligence = async (searchQuery, userAlias) => {

    const executionTimeout = 60000; // 60 second maximum

    const fallbackResponse = { summary: "Organizational context unavailable", status: "fallback" };



    try {

        // Health check before expensive operations

        const connectionsHealthy = await verifyConnectionHealth(userAlias);

        if (!connectionsHealthy) {

            return fallbackResponse;

        }



        // Execute with timeout boundary

        const intelligencePromise = gatherOrganizationalIntelligence(searchQuery, userAlias);

        const timeoutPromise = new Promise((_, reject) =>

            setTimeout(() => reject(new Error('TIMEOUT')), executionTimeout)

        );



        return await Promise.race([intelligencePromise, timeoutPromise]);



    } catch (error) {

        // Graceful degradation logging

        await logger.warn(`M365 Researcher fallback: ${error.message}`, {

            userAlias,

            searchQuery,

            fallbackMode: true

        });



        return fallbackResponse;

    }

};

With the researcher in place, the agent moves from external market intelligence to a combined picture that includes internal stakeholder insight, intended to support better strategic decisions without weakening security, governance or performance standards.

Document generation call. This step creates the final briefing by combining everything gathered in the conversation into one payload for the service layer. It carries user context, meeting details and optional outside AI insights, with a 60-second timeout for the complete document pipeline:

- kind: HttpRequestAction

  id: p4tceX

  method: Post

  url: =Global.ApiBaseUrl & "/api/briefings/" & Topic.templateId & "/accounts/" & Topic.Account_TPID

  body:

    kind: JsonRequestContent

    content: "={

      user_alias: Topic.SenderEmail,

      MeetingDateTime: Topic.MeetingDateTime,

      msAttendees: Topic.msAttendees,

      FoundryResponse: Topic.FoundryResponse

    }"

  requestTimeoutInMilliseconds: 60000

  response: Topic.BriefingDocument

Delivery to SharePoint. All generated documents go to a central repository through Power Platform's SharePoint connector with managed authentication, so every document is kept and reachable whatever secondary delivery happens, which satisfies governance expectations. The target site and folder in the sample below are placeholders.

- kind: InvokeConnectorAction

  id: invokeConnectorAction_YKkMK4

  input:

    binding:

      dataset: https://microsoft.sharepoint.com/teams/MCAPSAIIncubationHub

      folderPath: /Shared Documents/General/AI Prototypes & Solutions/AI Executive Office Use Cases/Published Cust Template/

      name: =Topic.CustomFilename

      file: =Global.File

Filenames are generated to be readable, combining account name, executive identifier and meeting date. Substitute() makes them file-system safe and keeps naming consistent across executive workflows:

- kind: SetVariable

  id: setVariable_CustomFilename

  variable: Topic.CustomFilename

  value: =Concatenate(

    Substitute(Topic.Account_Name, " ", "_"), "_",

    Topic.ExecutiveName, "_Brief_",

    Text(Topic.meetingDate, "yyyy-MM-dd"), ".docx"

  )

Error handling and state. Failures to access a template produce contextual guidance in a warning-styled Adaptive Card and not a generic error. The flow inspects specific error codes from the service API, tells the user what authorization is missing and keeps sensitive system details out of view:

- kind: ConditionGroup

  id: conditionGroup_CheckBriefingError

  conditions:

    - condition: =!IsBlank(Topic.BriefingDocument.error) && Topic.BriefingDocument.error.error_code = "TEMPLATE_ACCESS_DENIED"

      actions:

        - kind: AdaptiveCardPrompt

          card: ={

            "$schema":"https://adaptivecards.io/schemas/adaptive-card.json",

            "type":"AdaptiveCard","version":"1.5",

            "body":[{

              "type":"Container","style":"warning",

              "items":[{

                "type":"TextBlock","text":"Template Access Required",

                "weight":"Bolder","color":"Attention"

              },{

                "type":"TextBlock","wrap":true,

                "text": Topic.BriefingDocument.error.user_message

              }]

            }]

          }

Business validation runs on the client so invalid input never reaches backend services, which improves the experience and avoids needless API calls. A meeting date in the past, for example, is rejected right away with guidance on what to do:

- kind: ConditionGroup

  id: conditionGroup_ValidateInputs

  conditions:

    - condition: =Topic.meetingDate < Today()

      actions:

        - kind: SendActivity

          activity: The meeting date cannot be in the past. Please start over and enter a future date.

        - kind: EndConversation

A security-sensitive cleanup step clears every variable holding account data, API responses and user input when the conversation ends. That meets privacy needs and avoids memory buildup in long-running conversation services that handle confidential business information:

- kind: SetVariable

  id: setVariable_ClearUserInput

  variable: Topic.UserInput

  value: =Blank()



- kind: SetVariable

  id: setVariable_ClearAccountDetails

  variable: Topic.AccountDetails

  value: =Blank()



- kind: SetVariable

  id: setVariable_ClearFoundryResponse

  variable: Topic.FoundryResponse

  value: =Blank()



- kind: SetVariable

  id: setVariable_ClearBriefingDocument

  variable: Topic.BriefingDocument

  value: =Blank()

Taken together, the experience layer delivers conversational orchestration with deterministic workflows, extensive error handling, fault-tolerant calls to external services, role-based authorization and multi-channel delivery through the AdaptiveDialog framework.

Security and implementation details

Federated identity and authentication

The system uses Azure federated identity credentials so that no secrets are stored, with consistent sign-in across environments. A credential selector detects where the code is running and picks the matching mechanism:

# Environment-aware authentication pattern

def get_credential():

    if is_azure_environment():

        return ManagedIdentityCredential(client_id=os.getenv('AZURE_CLIENT_ID'))

    else:

        return AzureCliCredential()

In Azure it uses managed identity, with credentials provisioned and rotated automatically, and local development falls back to Azure CLI credentials. Tokens are cached in a thread-safe way, and expiry is handled automatically.

Service structure and dependency management

The service layer relies on dependency injection, which lets the same code run against mocks in tests and fully configured instances in production. Cross-cutting concerns such as error handling and configuration management are shared by all service components, and each one (SharePoint, database, Power BI) accepts optional dependencies through its constructor while staying loosely coupled to external APIs and Azure resources. That supports thorough testing with mock implementations as well as dependable production behavior through proper abstraction.

Orchestration is asynchronous to get the most from parallel data gathering. When a briefing is generated, several sources are queried together with async/await and careful error handling so the system degrades gracefully when one fails.

Database connections and performance

The database tier uses connection pooling through SQLAlchemy's engine management, with a singleton for efficient resource use. Queries are concentrated in stored procedures that hold the business logic inside the database, which cuts network traffic and simplifies application code. Large result sets are handled by reconstructing JSON intelligently, working around pyodbc limitations with big responses so processing stays reliable whatever the size of the result and memory use stays sensible.

Integrating external services

The SharePoint and Microsoft Graph integration caches properties lazily to cut API calls while keeping data fresh. Site and drive IDs are cached with sensible invalidation and dynamic library selection, which makes uploads dependable. The Dynamics 365 integration parses the Search v2.0 responses and converts the raw results into business-meaningful structures, with retry logic for rate limiting and careful error handling.

Document generation

The generation pipeline combines Jinja2 template processing with context building that turns raw data into executive-ready insight. The template context includes raw data, calculated metrics, formatting helpers and intelligence derived from cross-system analysis. Template management validates syntax and handles errors so generation stays reliable even when templates are edited, and several output formats are supported through MIME type configuration for flexible delivery.

Errors and exceptions

The application has a structured exception hierarchy that gives detailed error information while respecting security boundaries. Custom exception classes carry context that helps debugging without revealing system internals:

Structured exception hierarchy

class AIExecutiveOfficeError(Exception):

    def __init__(self, message: str, error_code: str = None, details: dict = None):

        self.message = message

        self.error_code = error_code or self.__class__.__name__

        self.details = details or {}

        self.timestamp = datetime.utcnow()

A global exception handler converts technical errors into structured API responses that help the caller without exposing too much detail.

Evaluation and quality validation

The system is evaluated with a structured testing framework that checks technical performance and briefing quality across multiple enterprise accounts. Quantitative and qualitative methods are combined in a multi-notebook evaluation pipeline that tests each architectural component in batch.

On the quantitative side, the focus is data accuracy. The metrics in the finished, populated briefing templates are compared with the source data from the foundation layer, and the pipeline computes accuracy scores and mean absolute percentage error (MAPE). These show how faithfully the service layer carries data through the whole pipeline, from stored procedure results to the final document. Batch runs cover many accounts and feed a consolidated performance notebook, giving statistical evidence that a pure-orchestration design preserves data fidelity without introducing calculation errors or semantic drift between foundation and experience layers.

The qualitative side measures a single attribute, company fluency, meaning how well a briefing shows real understanding of the target organization and speaks about it appropriately. An LLM judge scores each briefing against five standard criteria: relevancy (is the content right for executive decisions), coherency (logical flow and structure), accuracy (factual correctness and alignment with source data), clarity (language and presentation suited to executives) and completeness (coverage of the strategic areas). The approach gives consistent, scalable assessment with objective scoring across account types and industries, though as with any LLM-based grading, it inherits the judge model's own biases.

Next steps: moving to Microsoft Agent Framework

The team sees the four-layer architecture as a good base for migrating to Microsoft Agent Framework (MAF), which would strengthen AI orchestration while keeping the deterministic principles. MAF would mostly enter at the service layer, replacing the FastAPI orchestration logic with MAF agents that follow the same pure data orchestration patterns. The experience layer would change completely: Copilot Studio would give way to native Microsoft 365 integration that places executive workflows inside Outlook, Teams and SharePoint, using MAF's language understanding for richer intent recognition and multi-turn conversations while still calling the same stored procedures for business intelligence.

MAF's built-in observability, prompt management and agent lifecycle tooling would remove custom infrastructure and add enterprise-level monitoring and deployment. The migration path keeps existing investments in stored procedures, Jinja2 templates and SharePoint integration while adding capabilities for harder scenarios, such as briefings that span several accounts, synthesis of competitive analysis, and templates generated dynamically from the meeting context and participants. With Microsoft 365 integration, briefing generation would become a capability embedded in existing productivity workflows and not a separate chat interface, with the data and service layers staying deterministic.

What the design shows

The agent turns briefing preparation from manual coordination into automated intelligence generation through four layers that separate data consolidation, operational aggregation, business orchestration and natural-language interaction. The deterministic approach, within Microsoft's integrated ecosystem, is said to deliver fast briefing generation (the claim is "sub-second", though the pipeline's own timeouts run to 60 seconds, so that most plausibly refers to the database and service steps) along with the security, governance and audit properties that purely prompt-based systems struggle to guarantee.

The broader lesson is that successful enterprise AI applications put solving the business problem ahead of technical sophistication, and that they deliver value through sound data integration and process automation more than through advanced machine learning. Layered separation of concerns, dependency injection and deterministic execution boundaries make the design a reusable pattern for customer service automation, financial analysis workflows and operational intelligence in other organizations.