epic: Microsoft Ecosystem Integration (Shared Authentication Architecture)

[jrosskopf]

Summary

This epic tracks the comprehensive integration of Microsoft ecosystem services into DuckDB via erpl-web. All Microsoft integrations share a common authentication layer using Microsoft Entra ID (Azure AD) OAuth2.

Shared Authentication Architecture

All Microsoft services use the same OAuth2 flow with Microsoft Entra ID:

Token Endpoint: https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token

Proposed Secret Types

Option A: Unified microsoft_entra secret type

CREATE SECRET ms_secret (
    TYPE microsoft_entra,
    PROVIDER client_credentials,  -- or 'authorization_code'
    CLIENT_ID 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
    CLIENT_SECRET 'your-client-secret',
    TENANT_ID 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
    SCOPE 'https://graph.microsoft.com/.default'  -- varies by service
);

Option B: Service-specific secrets with shared auth

-- Each service type maps to its scope internally
CREATE SECRET bc_secret (TYPE business_central, ...);      -- scope: api.businesscentral.dynamics.com
CREATE SECRET crm_secret (TYPE dataverse, ...);            -- scope: {env}.crm.dynamics.com
CREATE SECRET graph_secret (TYPE microsoft_graph, ...);    -- scope: graph.microsoft.com

Scope Mapping

Service	OAuth2 Scope
Business Central	https://api.businesscentral.dynamics.com/.default
Dynamics 365 CRM / Dataverse	https://{org}.crm.dynamics.com/.default
Microsoft Graph (all services)	https://graph.microsoft.com/.default

Shared Components to Implement

1. MicrosoftEntraAuthProvider - Core OAuth2 client credentials and authorization code flows
2. TokenStorage - Secure token caching with automatic refresh
3. ScopeResolver - Map service types to OAuth2 scopes

Related Issues

Microsoft Dynamics 365

• feat: Microsoft Dynamics 365 Business Central Integration #5 - Business Central (ERP: customers, vendors, items, GL entries)
• feat: Microsoft Dynamics 365 CRM / Dataverse Integration #6 - Dynamics 365 CRM / Dataverse (CRM: accounts, contacts, leads, opportunities)

Microsoft Graph API

• feat: Microsoft Graph API - Excel Workbooks (OneDrive/SharePoint) #7 - Excel Workbooks (tables, worksheets, ranges from OneDrive/SharePoint)
• feat: Microsoft Graph API - SharePoint Lists #8 - SharePoint Lists (list items with OData queries)
• feat: Microsoft Graph API - Planner (Tasks & Projects) #9 - Planner (tasks, plans, buckets for project analytics)
• feat: Microsoft Graph API - Outlook (Calendar, Contacts, Mail) #10 - Outlook (calendar events, contacts, mail metadata)
• feat: Microsoft Graph API - Entra ID (Users, Groups, Directory) #11 - Entra ID (users, groups, devices, sign-in logs)
• feat: Microsoft Graph API - Teams (Channels, Messages, Members) #12 - Teams (teams, channels, members, messages)

Implementation Priority

Phase 1: Foundation

1. Implement shared MicrosoftEntraAuthProvider
2. Implement feat: Microsoft Dynamics 365 Business Central Integration #5 (Business Central) as first integration
3. Validate OAuth2 flows work correctly

Phase 2: Dynamics 365

1. Implement feat: Microsoft Dynamics 365 CRM / Dataverse Integration #6 (CRM/Dataverse) using shared auth
2. Refine shared components based on learnings

Phase 3: Microsoft Graph

1. Implement feat: Microsoft Graph API - Excel Workbooks (OneDrive/SharePoint) #7 (Excel) and feat: Microsoft Graph API - SharePoint Lists #8 (SharePoint Lists) - most common use cases
2. Implement feat: Microsoft Graph API - Entra ID (Users, Groups, Directory) #11 (Entra ID) - foundation for user/group context
3. Implement remaining: feat: Microsoft Graph API - Planner (Tasks & Projects) #9 (Planner), feat: Microsoft Graph API - Outlook (Calendar, Contacts, Mail) #10 (Outlook), feat: Microsoft Graph API - Teams (Channels, Messages, Members) #12 (Teams)

Testing Strategy

• Use Microsoft 365 Developer tenant for testing
• Mock responses for CI/CD (no live credentials in tests)
• Integration tests with environment variables for credentials

Documentation

Each integration should document:

• Required Azure app registration permissions
• Example secret creation
• Function catalog with examples
• Rate limits and pagination behavior

References

• Microsoft Identity Platform Documentation
• OAuth 2.0 Client Credentials Flow
• Microsoft Graph API Reference
• Business Central API Reference
• Dataverse Web API Reference