[{'Text': '# AGENTS.md - Point Topic MCP Codebase Guide\n# UK broadband data analysis MCP server (Python 3.12+, FastMCP)\n\n# Purpose: Single source of truth for AI agents working on this codebase.\n# Keep concise: agents parse this on every task.\n# For full README: see ./README.md\n```\n\n# Architecture (Mermaid)\n\n```mermaid\nflowchart TB\n    subgraph Clients\n        CD[Claude Desktop]\n        CR[Cursor]\n        MI[MCP Inspector]\n    end\n    \n    subgraph Deployment\n        ST[server_local.py<br/>Stdio Transport]\n        HT[server_http.py<br/>HTTP + Auth0 OAuth]\n    end\n    \n    subgraph Core\n        TD[Tools Discovery<br/>__init__.py]\n        PD[Prompts Discovery<br/>__init__.py]\n        TM[ToolManager<br/>Dynamic registration]\n        MM[PermissionMiddleware<br/>Auth0 RBAC]\n    end\n    \n    subgraph Tools\n        DB[Database Tools<br/>Snowflake queries]\n        CH[Chart Tools<br/>Public/private charts]\n        GB[GBS Tools<br/>MongoDB statistics]\n        GH[GitHub Tools<br/>Org management]\n        SI[Server Info Tools<br/>Capabilities]\n    end\n    \n    subgraph Context\n        GA[Context Assembly]\n        DS[Datasets<br/>upc, tariffs, etc.]\n        GI[General DB Instructions]\n    end\n    \n    subgraph Connectors\n        SF[snowflake.py<br/>Async SQL executor]\n        MO[mongodb_utils.py<br/>mongosh subprocess]\n        S3[s3_utils.py<br/>Source file archival]\n    end\n    \n    subgraph Auth\n        D[decorators.py<br/>@public/@requires_permissions]\n        MW[middleware.py<br/>PermissionMiddleware]\n        AH[auth0_helpers.py<br/>JWT verifier]\n    end\n    \n    CD --> ST\n    CR --> ST\n    MI --> ST\n    HT --> AH\n    \n    ST --> TD --> DB\n    ST --> TD --> CH\n    ST --> TD --> GB\n    ST --> TD --> GH\n    ST --> TD --> SI\n    ST --> PD\n    ST --> MM\n    \n    DB --> SF\n    GB --> MO\n    GB --> S3\n    \n    DB --> GA --> DS\n    GA --> GI\n```\n\n# Directory Map\n\n```\npoint-topic-mcp/\n├── src/point_topic_mcp/\n│   ├── server_local.py         # Stdio entry (default)\n│   ├── server_http.py          # HTTP entry (Auth0)\n│   ├── install_mongosh.py      # mongosh installer script\n│   ├── tools/                  # Auto-discovered tool modules\n│   │   ├── __init__.py         # Tool discovery & registration\n│   │   ├── database_tools.py   # Snowflake SQL tools\n│   │   ├── chart_tools.py      # Chart API tools\n│   │   ├── gbs_tools.py        # MongoDB statistics tools\n│   │   ├── github_tools.py     # GitHub org tools\n│   │   └── server_info_tools.py# Capability reporting\n│   ├── prompts/                # Auto-discovered prompt modules\n│   │   ├── __init__.py         # Prompt discovery & registration\n│   │   └── upc_prompts.py      # UPC analysis templates\n│   ├── core/                   # Shared utilities\n│   │   ├── utils.py            # check_env_vars, dynamic_docstring\n│   │   ├── tool_manager.py     # Dynamic tool registration\n│   │   ├── context_assembly.py # Dataset discovery & assembly\n│   │   ├── mongodb_utils.py    # mongosh subprocess wrapper\n│   │   └── s3_utils.py         # S3 archival for sources\n│   ├── connectors/\n│   │   └── snowflake.py        # SnowflakeDB: safe async queries\n│   ├── auth/                   # Auth0 RBAC\n│   │   ├── decorators.py       # @public, @requires_permissions\n│   │   ├── middleware.py       # PermissionMiddleware\n│   │   └── auth0_helpers.py    # JWT verification\n│   └── context/\n│       ├── general_db_instructions.py\n│       └── datasets/           # Auto-discovered datasets\n│           ├── upc.py          # Availability (postcode-level)\n│           ├── upc_take_up.py  # Adoption/subscribers\n│           ├── upc_forecast.py # Coverage projections\n│           ├── tariffs.py      # ISP pricing\n│           ├── ontology.py     # Tech classification\n│           └── bi_reports.py   # Report metadata\n├── tests/                      # pytest test suite\n│   ├── eval_set.py             # Eval question bank\n│   ├── test_gbs_tools.py\n│   ├── test_mongodb_utils.py\n│   ├── test_notifications.py\n│   ├── test_permissions.py\n│   ├── test_registration.py\n│   ├── test_sql_statements_split.py\n│   └── test_tool_manager.py\n├── infrastructure/             # Auth0 deployment scripts\n│   ├── auth0-setup.sh\n│   ├── auth0-cheat-sheet.sh\n│   └── auth0-credentials.json\n└── pyproject.toml              # v0.1.85, Python >=3.12\n```\n\n# Key Design Patterns\n\n## 1. Auto-Discovery (tools, prompts, datasets)\n\n| Component | Location Pattern | Registration | Name Convention |\n|-----------|-----------------|--------------|-----------------|\n| Tools | `tools/*_tools.py` | `tools/__init__.py:register_tools()` | `{module}_{func_name}` (prefixed) |\n| Prompts | `prompts/*_prompts.py` | `prompts/__init__.py:register_prompts()` | Function name only |\n| Datasets | `context/datasets/*.py` | `core/context_assembly.py` | Module name only |\n\n**Just create a file with public functions** — no decorators needed for registration.\n\n## 2. Conditional Tool Registration\n\n```python\n# tools/database_tools.py\nfrom point_topic_mcp.core.utils import check_env_vars\n\nif check_env_vars(\'snowflake_tools\', [\'SNOWFLAKE_USER\', \'SNOWFLAKE_PASSWORD\']):\n    def execute_query(sql_query: str, ctx=None) -> str:\n        """Execute safe read-only SQL."""\n        ...\n```\n\n- Tools only appear when required env vars are set.\n- `check_env_vars()` tracks requirements for `get_mcp_server_capabilities()`.\n- Skipped tools log: `[MCP] Skipping {name}: missing env vars [...]`.\n\n## 3. Dynamic Docstrings\n\n```python\nfrom point_topic_mcp.core.utils import dynamic_docstring\nfrom point_topic_mcp.core.context_assembly import list_datasets\n\n@dynamic_docstring([("{DATASETS}", list_datasets)])\ndef assemble_dataset_context(dataset_names: list[str], ctx=None) -> str:\n    """Full context for one or more datasets.\\n{DATASETS}"""\n```\n\nPlaceholders replaced at module load time (not at call time).\n\n## 4. Dynamic Tool Registration (Runtime)\n\n```python\nfrom point_topic_mcp.core.tool_manager import ToolManager\ntool_manager = ToolManager(mcp)\n\nasync def my_analyzer(dataset_id: str) -> dict: ...\ntool_manager.register_tool(name="analyze_dataset", description="...", function=my_analyzer)\n\n# Notify clients (call from within a tool)\nawait ctx.send_notification(mcp.types.ToolListChangedNotification())\n```\n\n## 5. Permission Middleware (Three-Tier Access)\n\n| Level | What They See |\n|-------|--------------|\n| **Unauthenticated** | Only `@public` tools (stdio: admin via ADMIN_API_KEY) |\n| **Authenticated** | `@public` + tools matching their `@requires_permissions` |\n| **ptAdmin** | All tools |\n\nStdio mode: `ADMIN_API_KEY` grants ptAdmin. HTTP mode: Auth0 JWT.\n\n```python\n# auth/decorators.py\n@public                          # Everyone\n@requires_permissions(["read:data"])  # Specific scope\n```\n\n## 6. Dataset System (Lazy Loading)\n\n```python\n# context/datasets/upc.py\ndef get_dataset_summary():\n    """Short. Always visible in agent prompt."""\n    return "UK broadband availability at postcode level."\n\ndef get_db_info():\n    """Full schema + instructions + examples. Only loaded on demand."""\n    return f"{DB_INFO}\\n\\n{DB_SCHEMA}\\n\\n{SQL_EXAMPLES}"\n```\n\n# Tool Reference\n\n## Database Tools (requires: `SNOWFLAKE_USER`, `SNOWFLAKE_PASSWORD`)\n\n| Function | Description | Key Args |\n|----------|-------------|----------|\n| `database_tools_assemble_dataset_context` | Get schema/instructions/examples | `dataset_names: list[str]` |\n| `database_tools_execute_query` | Safe SQL (SELECT/WITH/SHOW/DESCRIBE/EXPLAIN only) | `sql_query: str` |\n| `database_tools_execute_multi_query` | Multiple semicolon-separated queries | `queries: str` |\n| `database_tools_get_query_status` | Check query state | `query_id: str` |\n| `database_tools_cancel_query` | Cancel by query_id | `query_id: str` |\n| `database_tools_describe_table` | Get table schema | `table_name: str` |\n| `database_tools_get_la_code` | LA lookup | various |\n| `database_tools_get_la_list_full` | All local authorities | - |\n\n**Async execution**: Queries submit async, return `query_id:` immediately. Poll with `get_query_status`.\n\n## Chart Tools (public: always; private: `CHART_API_KEY`)\n\n| Function | Auth | Description |\n|----------|------|-------------|\n| `chart_tools_get_point_topic_public_chart_catalog` | None | Browse public charts |\n| `chart_tools_get_point_topic_public_chart_csv` | None | Get chart CSV |\n| `chart_tools_get_point_topic_chart_catalog` | Key | Private catalog (currently disabled) |\n| `chart_tools_get_point_topic_chart_csv` | Key | Private chart CSV (currently disabled) |\n| `chart_tools_generate_authenticated_chart_url` | Key | Signed URLs (currently disabled) |\n\n## GBS Tools (requires: `PT_RESEARCH_DATABASE_URI`, mongosh)\n\n| Function | Description |\n|----------|-------------|\n| `gbs_tools_list_operators` | List by country, returns Operator collection docs |\n| `gbs_tools_get_gbs_status` | Statistics status for operator (with quarter distance) |\n| `gbs_tools_add_statistic` | Insert Statistics document |\n| `gbs_tools_create_source` | Create Source record (with optional S3 archival) |\n\nRequires mongosh on PATH (install: `point-topic-mcp-install-mongosh`).\n\nValid enums: types=`[broadband, mobile, iptv]`, channels=`[Infrastructure, Retail]`, domains=`[Residential, Business, Total]`\n\n## GitHub Tools (requires: `GITHUB_TOKEN`)\n\nAdmin-only tools for Point-Topic org: list repos, manage issues, PRs.\n\n## Server Info (always available)\n\n| Function | Description |\n|----------|-------------|\n| `server_info_tools_get_mcp_server_capabilities` | Debug missing credentials |\n\n# Data Models\n\n## Snowflake (UPC_CORE database, REPORTS schema)\n\n| Table (prefix: `upc_core.reports.`) | Key Columns | Granularity |\n|--------------------------------------|-------------|-------------|\n| `upc_output` | postcode, premises, households, reported_at, la_name | Postcode |\n| `fact_operator` | postcode, operator, tech, reported_at | Postcode+operator+tech |\n| `upc_take_up` | operator, subscribers, reported_at | Operator+LA+period |\n| `upc_forecast` | operator, premises, year | Operator+year |\n| `tariffs` | operator, package_name, price | Operator+package |\n| `ontology` | tech_category, tech_subcategory, operator | Tech+operator |\n\n**CRITICAL**: `fact_operator` joins to `upc_output` can duplicate rows (multiple techs per postcode). Always deduplicate by postcode for operator footprint.\n\n## MongoDB (pt-research-app via Prisma)\n\n| Collection | Key Fields | Used By |\n|-----------|------------|---------|\n| `Operator` | name, country, technologies, _id | list_operators |\n| `Statistics` | operatorId, period, type, channel, domain, subscribers, tech | get_gbs_status, add_statistic |\n| `Sources` | operatorId, year, quarter, type, url, fileUrl | create_source |\n| `GlobalVariables` | isCurrent, ... | find_global_variables_current |\n\n# Environment Variables\n\n| Variable | Required For | Notes |\n|----------|-------------|-------|\n| `SNOWFLAKE_USER` | database_tools | Snowflake login |\n| `SNOWFLAKE_PASSWORD` | database_tools | Snowflake password |\n| `CHART_API_KEY` | chart_tools (disabled) | Charts API key |\n| `PT_RESEARCH_DATABASE_URI` | gbs_tools | MongoDB connection string |\n| `PT_RESEARCH_TLS_SKIP_VERIFY` | gbs_tools | Set to `1` to skip TLS verify |\n| `GITHUB_TOKEN` | github_tools | GitHub PAT |\n| `AUTH0_DOMAIN` | server_http | Default: `point-topic.eu.auth0.com` |\n| `AUTH0_CLIENT_ID` | server_http | Auth0 app client ID |\n| `AUTH0_CLIENT_SECRET` | server_http | Auth0 app secret |\n| `AUTH0_AUDIENCE` | server_http | Default: `https://point-topic-mcp.fastmcp.app/` |\n| `MCP_BASE_URL` | server_http | Public server URL |\n| `ADMIN_API_KEY` | stdio auth | Grants ptAdmin in local mode |\n\n# Adding New Features\n\n## New Tool\n1. Create `tools/your_feature_tools.py`\n2. Public function → auto-discovered as `your_feature_tools_{func_name}`\n3. For conditional: wrap in `if check_env_vars(\'name\', [\'REQUIRED_ENV\'])`\n4. Add clear docstring (agents see it)\n\n## New Prompt\n1. Create `prompts/your_analysis_prompts.py`\n2. Function returning `list[PromptMessage]` (with `role` + `TextContent`)\n3. Auto-discovered: no registration needed\n\n## New Dataset\n1. Create `context/datasets/your_db.py`\n2. Implement: `get_dataset_summary()` (concise, always visible) and `get_db_info()` (schema + instructions + examples)\n3. Auto-discovered by `assemble_dataset_context`\n\n# Key Business Rules (UK Broadband)\n\n| Rule | Value |\n|------|-------|\n| UK premises | ~33M |\n| UK households | ~30M |\n| UK population | ~67M |\n| Altnet exclusion list | BT, Sky, TalkTalk, Vodafone, Virgin Cable, Virgin Media RFOG, KCOM Lightstream |\n| Virgin altnet variant | `nexfibre Virgin Media` IS altnet |\n| CityFibre canonicalization | `operator like \'%CityFibre%\' → \'CityFibre\'` |\n| Fact operator dedup | `distinct operator_canonical` NOT `count(distinct operator)` |\n| Total premises | `SUM(premises) FROM upc_output` directly, never from `fact_operator` join |\n\n# Commands Reference\n\n```bash\nuv sync                          # Development setup\nuv run point-topic-mcp           # Run stdio server\nuv run python -m point_topic_mcp.server_http  # Run HTTP server\nuv run mcp dev src/point_topic_mcp/server_local.py  # MCP Inspector\nuv run mcp install src/point_topic_mcp/server_local.py --with "snowflake-connector-python[pandas]" -f .env  # Claude Desktop\nuv build                         # Build wheel\npip install dist/point_topic_mcp-*.whl  # Test install\npoint-topic-mcp-install-mongosh  # Install mongosh (for GBS)\n```\n\n# Testing\n\n```bash\nuv run pytest tests/ -v         # All tests\nuv run pytest tests/test_gbs_tools.py -v --run-gbs  # GBS tests (needs PT_RESEARCH_DATABASE_URI)\n```\n\nTest files: 8 total covering SQL parsing, tool manager, permissions, notifications, registration, GBS, MongoDB.\n\n# Publishing (Maintainers)\n\n```bash\naws secretsmanager get-secret-value --secret-id pypirc --query SecretString --output text > ~/.pypirc\n./publish_to_pypi.sh\n```\n\n---\n\n**For questions**: Check `README.md`, `pyproject.toml` (v0.1.85), or the specific module\'s docstring. This file is the architecture overview only.'}]