{
  "nbformat": 4,
  "nbformat_minor": 5,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.13.0"
    },
    "blog_metadata": {
      "topic": "Turning Power App data into governed Copilot experiences with MCP",
      "slug": "turning-power-app-data-into-governed-copilot-experiences-wit",
      "generated_by": "LinkedIn Post Generator + Azure OpenAI",
      "generated_at": "2026-07-06T15:20:01.661Z"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Turning Power App data into governed Copilot experiences with MCP\n",
        "\n",
        "This notebook turns the blog post into a hands-on validation workflow using Python. The core idea is that MCP is a protocol boundary, not a policy boundary, so governance must be enforced through identity, environment controls, authorization, response shaping, metadata, and auditability. Each section below demonstrates a compact pattern you can run and inspect locally."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install pandas networkx matplotlib"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import json\n",
        "from typing import Dict, Any, List, Set\n",
        "\n",
        "import pandas as pd\n",
        "import networkx as nx\n",
        "import matplotlib.pyplot as plt"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Architecture pattern: MCP facade with policy checks and response shaping\n",
        "\n",
        "The blog argues that the MCP facade is not the trust anchor. Policy checks are the trust anchor, and response shaping is a required control before data is returned to Copilot. This cell visualizes that architecture as a simple directed graph."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import networkx as nx\n",
        "import matplotlib.pyplot as plt\n",
        "\n",
        "G = nx.DiGraph()\n",
        "edges = [\n",
        "    (\"Copilot user\", \"Copilot experience\"),\n",
        "    (\"Copilot experience\", \"MCP facade\"),\n",
        "    (\"MCP facade\", \"Policy checks\"),\n",
        "    (\"Policy checks\", \"Dataverse / Power App data\"),\n",
        "    (\"Policy checks\", \"Safe refusal\"),\n",
        "    (\"Dataverse / Power App data\", \"Response shaping\"),\n",
        "    (\"Response shaping\", \"Copilot experience\"),\n",
        "]\n",
        "G.add_edges_from(edges)\n",
        "\n",
        "pos = {\n",
        "    \"Copilot user\": (0, 0),\n",
        "    \"Copilot experience\": (1.5, 0),\n",
        "    \"MCP facade\": (3, 0),\n",
        "    \"Policy checks\": (4.5, 0),\n",
        "    \"Dataverse / Power App data\": (6.5, 0.8),\n",
        "    \"Safe refusal\": (6.5, -0.8),\n",
        "    \"Response shaping\": (8.5, 0.8),\n",
        "}\n",
        "\n",
        "plt.figure(figsize=(12, 4))\n",
        "nx.draw(\n",
        "    G,\n",
        "    pos,\n",
        "    with_labels=True,\n",
        "    node_size=3200,\n",
        "    node_color=\"#DCEEFF\",\n",
        "    font_size=9,\n",
        "    arrows=True,\n",
        "    arrowstyle=\"-|>\",\n",
        "    arrowsize=18,\n",
        ")\n",
        "plt.title(\"Governed Copilot pattern: policy checks before data access\")\n",
        "plt.axis(\"off\")\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Validate intent and restrict allowed operations\n",
        "\n",
        "This example implements a thin MCP facade authorization check. It demonstrates the recommended pattern of exposing narrow business verbs such as `get_account_summary` and `list_open_cases`, while denying unknown tools or disallowed intents."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from typing import Dict, Any\n",
        "\n",
        "ALLOWED = {\n",
        "    \"get_account_summary\": {\"read\"},\n",
        "    \"list_open_cases\": {\"read\"},\n",
        "}\n",
        "\n",
        "def authorize(request: Dict[str, Any]) -> Dict[str, Any]:\n",
        "    tool = request.get(\"tool\", \"\")\n",
        "    intent = request.get(\"intent\", \"read\")\n",
        "    if tool not in ALLOWED:\n",
        "        return {\"allowed\": False, \"reason\": \"unknown_tool\"}\n",
        "    if intent not in ALLOWED[tool]:\n",
        "        return {\"allowed\": False, \"reason\": \"operation_not_permitted\"}\n",
        "    return {\"allowed\": True, \"tool\": tool, \"intent\": intent}\n",
        "\n",
        "requests = [\n",
        "    {\"tool\": \"get_account_summary\", \"intent\": \"read\"},\n",
        "    {\"tool\": \"create_service_request\", \"intent\": \"write\"},\n",
        "    {\"tool\": \"list_open_cases\", \"intent\": \"write\"},\n",
        "]\n",
        "\n",
        "results = [authorize(r) for r in requests]\n",
        "print(json.dumps(results, indent=2))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Shape Dataverse records before returning them to Copilot\n",
        "\n",
        "Response shaping is a governance control, not just formatting. This example removes sensitive fields before records are returned, reducing oversharing risk even when the underlying source contains more data than the Copilot experience needs."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from typing import Dict, Any, List\n",
        "\n",
        "SENSITIVE_FIELDS = {\"emailaddress1\", \"telephone1\", \"creditlimit\"}\n",
        "\n",
        "def shape_account(record: Dict[str, Any]) -> Dict[str, Any]:\n",
        "    return {k: v for k, v in record.items() if k not in SENSITIVE_FIELDS}\n",
        "\n",
        "def shape_response(rows: List[Dict[str, Any]]) -> Dict[str, Any]:\n",
        "    safe_rows = [shape_account(r) for r in rows]\n",
        "    return {\"count\": len(safe_rows), \"items\": safe_rows}\n",
        "\n",
        "sample = [\n",
        "    {\n",
        "        \"name\": \"Contoso\",\n",
        "        \"emailaddress1\": \"a@b.com\",\n",
        "        \"telephone1\": \"+1-555-0100\",\n",
        "        \"creditlimit\": 250000,\n",
        "        \"accountnumber\": \"A-100\",\n",
        "        \"status\": \"Active\",\n",
        "    }\n",
        "]\n",
        "\n",
        "print(\"Original record:\")\n",
        "print(json.dumps(sample, indent=2))\n",
        "print(\"\\nShaped response:\")\n",
        "print(json.dumps(shape_response(sample), indent=2))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Sequence validation: request flow from Copilot to Dataverse and back\n",
        "\n",
        "The original post also included a sequence diagram. This Python version prints the same flow as an ordered sequence so you can validate the control points in a notebook-friendly way."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "sequence_steps = [\n",
        "    (\"User\", \"Copilot\", \"Ask for account summary\"),\n",
        "    (\"Copilot\", \"MCP Facade\", \"tool=get_account_summary\"),\n",
        "    (\"MCP Facade\", \"Policy\", \"Validate tool, intent, environment\"),\n",
        "    (\"Policy\", \"MCP Facade\", \"allow\"),\n",
        "    (\"MCP Facade\", \"Dataverse\", \"Query approved columns only\"),\n",
        "    (\"Dataverse\", \"MCP Facade\", \"Raw record\"),\n",
        "    (\"MCP Facade\", \"Copilot\", \"Shaped response\"),\n",
        "]\n",
        "\n",
        "for i, (src, dst, msg) in enumerate(sequence_steps, start=1):\n",
        "    print(f\"{i}. {src} -> {dst}: {msg}\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Inventory Power Platform environments for governance review\n",
        "\n",
        "The blog used PowerShell as an illustrative governance pattern. Here, the same idea is implemented in Python so you can inspect environments, regions, and types, and treat environment strategy as part of the runtime trust model."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import pandas as pd\n",
        "\n",
        "environments = [\n",
        "    {\"Name\": \"Prod\", \"Region\": \"unitedstates\", \"Type\": \"Production\"},\n",
        "    {\"Name\": \"UAT\", \"Region\": \"europe\", \"Type\": \"Sandbox\"},\n",
        "]\n",
        "\n",
        "env_df = pd.DataFrame(environments)\n",
        "print(\"Environment inventory:\")\n",
        "print(env_df.to_string(index=False))\n",
        "\n",
        "approved_types = {\"Production\", \"Sandbox\"}\n",
        "env_df[\"ApprovedType\"] = env_df[\"Type\"].isin(approved_types)\n",
        "print(\"\\nValidation view:\")\n",
        "print(env_df.to_string(index=False))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Inventory connectors related to Copilot or MCP exposure\n",
        "\n",
        "This example highlights that broad access paths often matter more than obvious AI components. Dataverse, SQL Server, and custom connectors can define the real business-data blast radius, so they should be reviewed before any Copilot exposure."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import pandas as pd\n",
        "\n",
        "connectors = [\n",
        "    {\"Name\": \"shared_commondataserviceforapps\", \"DisplayName\": \"Dataverse\", \"Tier\": \"Standard\"},\n",
        "    {\"Name\": \"shared_openai\", \"DisplayName\": \"Azure OpenAI\", \"Tier\": \"Custom\"},\n",
        "    {\"Name\": \"shared_sql\", \"DisplayName\": \"SQL Server\", \"Tier\": \"Standard\"},\n",
        "]\n",
        "\n",
        "conn_df = pd.DataFrame(connectors)\n",
        "filtered = conn_df[conn_df[\"DisplayName\"].str.contains(r\"Dataverse|OpenAI|Copilot|MCP\", case=False, regex=True)]\n",
        "sorted_df = filtered.sort_values(\"DisplayName\")\n",
        "print(sorted_df.to_string(index=False))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Minimal MCP tool manifest for governed Copilot exposure\n",
        "\n",
        "Metadata is treated here as a security dependency. Even a small manifest makes environment and intent explicit, which supports publication decisions, discoverability controls, and ownership review."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import json\n",
        "\n",
        "manifest = {\n",
        "    \"server\": \"power-platform-governed-mcp\",\n",
        "    \"tools\": [\n",
        "        {\"name\": \"get_account_summary\", \"intent\": \"read\", \"environment\": \"Prod\"},\n",
        "        {\"name\": \"list_open_cases\", \"intent\": \"read\", \"environment\": \"Prod\"},\n",
        "    ],\n",
        "}\n",
        "\n",
        "print(json.dumps(manifest, indent=2))\n",
        "\n",
        "manifest_df = pd.DataFrame(manifest[\"tools\"])\n",
        "print(\"\\nManifest as table:\")\n",
        "print(manifest_df.to_string(index=False))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Audit connection references that may expose Copilot-facing paths\n",
        "\n",
        "Auditability is what separates a demo from an operating model. This example creates a mock audit dataset, filters likely Copilot or MCP-related paths, and exports the result so ownership and connector usage can be reviewed."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import pandas as pd\n",
        "from pathlib import Path\n",
        "\n",
        "connection_references = [\n",
        "    {\"Environment\": \"Prod\", \"Flow\": \"CopilotCaseSummary\", \"Connector\": \"Dataverse\", \"Owner\": \"admin@contoso.com\"},\n",
        "    {\"Environment\": \"Prod\", \"Flow\": \"McpFacadeSync\", \"Connector\": \"Azure OpenAI\", \"Owner\": \"platform@contoso.com\"},\n",
        "    {\"Environment\": \"UAT\", \"Flow\": \"TestFlow\", \"Connector\": \"SQL Server\", \"Owner\": \"maker@contoso.com\"},\n",
        "]\n",
        "\n",
        "cr_df = pd.DataFrame(connection_references)\n",
        "mask = (\n",
        "    cr_df[\"Flow\"].str.contains(r\"Copilot|Mcp\", case=False, regex=True)\n",
        "    | cr_df[\"Connector\"].str.contains(r\"OpenAI|Dataverse\", case=False, regex=True)\n",
        ")\n",
        "audit_df = cr_df[mask].copy()\n",
        "\n",
        "output_path = Path(\"copilot-mcp-audit.csv\")\n",
        "audit_df.to_csv(output_path, index=False)\n",
        "\n",
        "print(audit_df.to_string(index=False))\n",
        "print(\"\\nExported:\")\n",
        "print({\"path\": str(output_path.resolve()), \"bytes\": output_path.stat().st_size})"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## End-to-end request handler with safe refusal\n",
        "\n",
        "A governed agent should fail safely and predictably. This example combines tool allow-listing, environment approval, and response shaping so denied actions and unapproved environments are handled explicitly."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from typing import Dict, Any\n",
        "\n",
        "def handle_request(request: Dict[str, Any]) -> Dict[str, Any]:\n",
        "    allowed_tools = {\"list_open_cases\"}\n",
        "    if request.get(\"tool\") not in allowed_tools:\n",
        "        return {\"status\": \"denied\", \"message\": \"This action is not available in Copilot.\"}\n",
        "    if request.get(\"environment\") not in {\"Prod\", \"UAT\"}:\n",
        "        return {\"status\": \"denied\", \"message\": \"Environment is not approved.\"}\n",
        "    raw = [{\"caseid\": \"C-101\", \"title\": \"Login issue\", \"internalnotes\": \"VIP user\"}]\n",
        "    shaped = [{\"caseid\": r[\"caseid\"], \"title\": r[\"title\"]} for r in raw]\n",
        "    return {\"status\": \"ok\", \"data\": shaped}\n",
        "\n",
        "examples = [\n",
        "    {\"tool\": \"list_open_cases\", \"environment\": \"Prod\"},\n",
        "    {\"tool\": \"delete_case\", \"environment\": \"Prod\"},\n",
        "    {\"tool\": \"list_open_cases\", \"environment\": \"Dev\"},\n",
        "]\n",
        "\n",
        "for req in examples:\n",
        "    print(\"Request:\", req)\n",
        "    print(json.dumps(handle_request(req), indent=2))\n",
        "    print(\"-\" * 60)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Decision guide: choose connectors, MCP, or a governed API layer\n",
        "\n",
        "The blog distinguishes three complementary patterns rather than interchangeable ones. This cell turns that decision guide into a table you can inspect and extend for your own architecture reviews."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "decision_guide = pd.DataFrame([\n",
        "    {\n",
        "        \"Pattern\": \"Copilot connectors\",\n",
        "        \"Best for\": \"Governed retrieval, search grounding, Microsoft 365 reasoning\",\n",
        "        \"Control focus\": \"Source permissions, indexing/federation, connector governance\",\n",
        "        \"Typical risk\": \"Broad discoverability without enough curation\",\n",
        "    },\n",
        "    {\n",
        "        \"Pattern\": \"MCP\",\n",
        "        \"Best for\": \"Tool invocation and real-time actions\",\n",
        "        \"Control focus\": \"Identity, authorization, safe refusal, response shaping, logging\",\n",
        "        \"Typical risk\": \"Overexposed actions or mixed trust profiles\",\n",
        "    },\n",
        "    {\n",
        "        \"Pattern\": \"Governed API / semantic layer\",\n",
        "        \"Best for\": \"Normalization when source maturity is weak or inconsistent\",\n",
        "        \"Control focus\": \"Contract design, schema control, policy centralization\",\n",
        "        \"Typical risk\": \"Added delivery overhead, but often lower long-term risk\",\n",
        "    },\n",
        "])\n",
        "\n",
        "print(decision_guide.to_string(index=False))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Production identity review: detect maker-owned credentials\n",
        "\n",
        "One of the strongest claims in the post is that maker-owned connections should usually be treated as a production-blocking exception for Copilot exposure unless explicitly risk-accepted. This validation cell flags likely personal ownership patterns in mock connection references."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "identity_review = pd.DataFrame([\n",
        "    {\"Environment\": \"Prod\", \"Flow\": \"CopilotCaseSummary\", \"Owner\": \"admin@contoso.com\", \"IdentityType\": \"shared-admin\"},\n",
        "    {\"Environment\": \"Prod\", \"Flow\": \"McpFacadeSync\", \"Owner\": \"platform@contoso.com\", \"IdentityType\": \"service-account\"},\n",
        "    {\"Environment\": \"Prod\", \"Flow\": \"CaseEscalation\", \"Owner\": \"jane.doe@contoso.com\", \"IdentityType\": \"personal\"},\n",
        "    {\"Environment\": \"UAT\", \"Flow\": \"TestFlow\", \"Owner\": \"maker@contoso.com\", \"IdentityType\": \"personal\"},\n",
        "])\n",
        "\n",
        "blocked = identity_review[\n",
        "    (identity_review[\"Environment\"] == \"Prod\") &\n",
        "    (identity_review[\"IdentityType\"] == \"personal\")\n",
        "].copy()\n",
        "\n",
        "print(\"All identities:\")\n",
        "print(identity_review.to_string(index=False))\n",
        "print(\"\\nProduction-blocking exceptions:\")\n",
        "print(blocked.to_string(index=False) if not blocked.empty else \"None\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Publication model: private team, domain, and enterprise tools\n",
        "\n",
        "Discoverability should be earned, not automatic. This example tiers MCP-exposed capabilities into publication scopes so approval paths and discoverability can be reasoned about explicitly."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "publication_model = pd.DataFrame([\n",
        "    {\"Tool\": \"get_account_summary\", \"Tier\": \"domain\", \"Approval\": \"domain owner\", \"Discoverability\": \"sales agents only\"},\n",
        "    {\"Tool\": \"list_open_cases\", \"Tier\": \"enterprise\", \"Approval\": \"platform governance board\", \"Discoverability\": \"approved support copilots\"},\n",
        "    {\"Tool\": \"create_service_request\", \"Tier\": \"private team\", \"Approval\": \"team lead\", \"Discoverability\": \"single team agent\"},\n",
        "])\n",
        "\n",
        "print(publication_model.sort_values([\"Tier\", \"Tool\"]).to_string(index=False))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Summary\n",
        "\n",
        "This notebook validated the blog's main governance patterns with runnable Python examples: policy-first architecture, narrow tool authorization, response shaping, metadata manifests, environment and connector inventory, audit exports, safe refusal, identity review, and publication tiers. The practical takeaway is to use connectors for governed retrieval, use MCP for narrow actions, and add a governed API or semantic layer when source maturity is weak.\n",
        "\n",
        "## Next Steps\n",
        "\n",
        "- Replace the mock inventories with real tenant exports from your approved admin tooling.\n",
        "- Add data classification, owner, and deprecation metadata to every MCP-exposed tool.\n",
        "- Enforce service principals or managed identities for production paths where supported.\n",
        "- Add structured logging for who invoked what, under which identity, against which source, and with what result.\n",
        "- Review whether any current MCP endpoint mixes retrieval and transactions and split those trust profiles if needed."
      ]
    }
  ]
}