# 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
```

## 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.