rfq-sales-dashboard

Read-only tools over rfq_db (Azure Postgres) for the commercial distribution agent: role-scoped dashboards for KAMs, zone managers, and VP Sales, generated from AvoCarbon's live RFQ pipeline. Exposed to ChatGPT two ways at once, on the same deployed URL.

• queries.py - all the SQL / dashboard logic (single source of truth).

• db.py - read-only asyncpg connection pool.

• api.py - FastAPI app: REST/OpenAPI routes (for a Custom GPT Action, bearer-token protected) with the MCP-over-SSE app (mcp_sse.py) mounted alongside at /sse (for ChatGPT's native MCP connector / "Nouvelle application").

• mcp_sse.py - the same 9 tools spoken as MCP over SSE. Unauthenticated by design - see "Security note" below.

Setup (local dev/test)

python -m venv .venv
./.venv/Scripts/python.exe -m pip install -r requirements.txt

Credentials live in .env (git-ignored, never commit it):

DATABASE_URL=postgresql+asyncpg://administrationSTS:...@avo-adb-002.postgres.database.azure.com:5432/rfq_db?ssl=require
API_KEY=...        # bearer token required by every route (REST and MCP/SSE)
PUBLIC_HOST=...    # optional: hostname ChatGPT will connect to, for MCP transport security (defaults to the deployed Azure host)

The pool in db.py opens every connection with default_transaction_read_only=on, so nothing here can write to the database even if a tool's SQL were wrong.

Run locally:

./.venv/Scripts/python.exe -m uvicorn api:app --reload

Related MCP server: MCP SQL Server

Deploy

Push to main on GitHub (STS-Engineer/sales_mcp) - the Azure App Service is connected via Deployment Center / GitHub Actions and redeploys automatically. DATABASE_URL and API_KEY are set as App Service application settings (never committed).

Current deployment: https://sales-mcp-b6dka9c3djgqabbc.francecentral-01.azurewebsites.net

Connect to ChatGPT

Option A: Custom GPT + Action (OpenAPI)

In ChatGPT: Create a GPT -> Configure -> Actions -> Create new action.

• Import from URL: https://sales-mcp-b6dka9c3djgqabbc.francecentral-01.azurewebsites.net/openapi.json

• Authentication: API Key -> Auth Type: Bearer -> paste the API_KEY value from .env.

Option B: Native MCP connector ("Nouvelle application")

• Connexion: URL du serveur -> https://sales-mcp-b6dka9c3djgqabbc.francecentral-01.azurewebsites.net/sse

• Authentification: none - ChatGPT's connector dialog had no bearer/API-key option, so /sse is deliberately unauthenticated (see security note below).

Either way, give the GPT/app instructions describing the three audiences (KAM, zone manager, VP Sales) and which tool/endpoint to call for each - e.g. "when a KAM asks for their pipeline, call getKamDashboard/get_kam_dashboard with their email."

Tools / endpoints

All require Authorization: Bearer <API_KEY>.

• list_users / GET /users - resolve KAM/zone-manager/VP emails and names (role?, region?, search?).

• get_validation_matrix / GET /validation-matrix - KAM/zone/VP auto-approval limits (kEUR) per product line.

• search_rfqs / GET /rfqs - generic filtered search, case-insensitive (customer?, product_line_acronym?, zone_manager_email?, created_by_email?, phase?, sub_status?, min_value_keur?).

• get_rfq_detail / GET /rfqs/{rfq_id} - full RFQ record, audit trail, discussion thread, notification log.

• get_overdue_rfqs / GET /rfqs/overdue - open RFQs stalled past N days (days_threshold, zone_manager_email?, created_by_email?).

• get_pipeline_funnel / GET /pipeline/funnel - count/value by phase (zone_manager_email?, product_line_acronym?).

• get_kam_dashboard / GET /dashboards/kam - one KAM's pipeline: by phase, top customers, stale deals, recent activity (email).

• get_zone_dashboard / GET /dashboards/zone - zone rollup: by KAM, by product line, items nearing the zone approval limit (zone_manager_email).

• get_vp_sales_dashboard / GET /dashboards/vp-sales - company-wide: funnel, by zone, by product line, top deals, deals needing VP approval, recent losses.

Security note

/sse (and its /messages/ companion path) is unauthenticated - anyone with the URL can call all 9 read-only tools and see RFQ/customer/ pipeline data. This was a deliberate tradeoff: ChatGPT's "Nouvelle application" MCP connector dialog offered no bearer/API-key auth option at the time this was set up. The REST/OpenAPI routes (/users, /rfqs, /dashboards/*, etc.) still require Authorization: Bearer <API_KEY>. The database connection itself is read-only regardless (db.py sets default_transaction_read_only=on), so nothing here can be written to or deleted - only read. If ChatGPT adds an auth option for custom MCP connectors later, re-add a bearer check to mcp_sse.py (removed in this commit - see git history).

Data notes

• rfq_db is the live RFQ/quotation pipeline (33 active RFQs as of 2026-07-17), distinct from the KPI_DB_Final database behind the existing "AVO-DB" connector (people/KPI/action-plan data) - same Azure Postgres server, different databases.

• Commercial hierarchy comes from users.role (COMMERCIAL = KAM, ZONE_MANAGER, OWNER = VP Sales tier) and validation_matrix (n3_kam_limit / n2_zone_limit / n1_vp_limit per product line).

• Pipeline value (pipeline_keur) is read from the to_total field inside rfq.rfq_data (jsonb), already expressed in kEUR.

• kpi_opportunity, kpi_new_business, kpi_annual_target tables exist in the schema but are currently empty (0 rows) - a future data source not yet wired into any tool here.

• zone_manager_email is set per-RFQ (not a fixed attribute of a user), so "zone" dashboards are scoped by that field rather than a separate zone table.