# Business Metrics MCP — agent / contributor guide

Repo on disk / git: **`business-mcp.lucos.com`**  
Product name in Jira / Phase 1 docs: **`lucos-business-mcp`** (same service)

## Purpose

ChatGPT- and Cursor-facing **MCP protocol surface** for AdMedia **business metrics** (advertiser, publisher, campaign, budget, agreement, SPAF, reporting).

This repo owns:

- MCP runtime (SSE — TW-323)
- Tool registry + enablement flags (disabled by default)
- Gateway client to `api.lucos.com` (TW-325 — implemented; fail-closed HTTP authz)
- Connectors + schema harvest (Phase 1 first cut — live here, not in the gateway)

`api.lucos.com` owns **token auth + tool RBAC + audit** in Phase 1. This repo is a **credential forwarder** — not a second permissions broker, and **not** a key store (no Mongo here).

```
ChatGPT / Cursor MCP client
  → Bearer MCP API key (`lucos_mcp_live_…`) on /mcp (or /sse)
  → business-mcp.lucos.com (transport gate, tools, connectors, harvest)
  → api.lucos.com (authz + audit)
```

Transport gate (`server/mcp-auth.ts`): `/mcp`, `/sse`, `/messages` require a token via `Authorization: Bearer …` **or** (temporary ChatGPT workaround) `?api_key=` / `?access_token=`. `/healthz` is public. ChatGPT often sends the token only on session open; `server/session-bearer.ts` remembers it for later tools/call. Hosted (`LUCOS_BROKER_MOCK_ALLOW=false`) also `GET {LUCOS_GATEWAY_BASE_URL}/api/v1/tools` on initialize / SSE open — non-200 → 401, no session. Mock on: token must still be present; gateway validation is skipped (local ngrok wiring).

## Hard rules

- **No** production row/PII ingest into Git catalogs or Qdrant business catalog
- Schema harvest = **`INFORMATION_SCHEMA` only**
- **No** generic `execute_sql` tool
- Ad-hoc query tool is **spec-compiled, staging-only** — never accepts
  caller-authored SQL, never runs in prod, never reads non-`public`/`internal` columns
- AdCenter: level-1 `X-API-KEY` only — **never** Basic auth + `X-API-ADVID`, never UI level-2
- AdCenter: level-1 `X-API-KEY` only — **never** Basic auth + `X-API-ADVID`, never UI level-2
- AdCenter keys: MCP mints level-1 via `PUT /api/key` `{ adv_id, level: 1 }` using `ADCENTER_ADMIN_API_KEY` in host `.env` (same as `api.lucos.com`). Empty `ADCENTER_LEVEL1_KEYS_*={}` is OK. **Never commit** the admin key. **Never** return admin or minted keys to ChatGPT. Gateway ensure-key remains a fallback when local mint is off.
- Tools **disabled by default** until reviewed and enabled
- **Never** commit secrets, DSNs, or API keys
- Separate workload identity from Engineering MCP

## AdCenter staging (TW-301)

- Synthetic advertiser_id: **19880** (`lucos_synth_adv`)
- **Primary path:** `ADCENTER_ENSURE_KEY_ENABLED=true` + `ADCENTER_ADMIN_API_KEY` in MCP `.env` (staging and prod; `ADCENTER_ENSURE_KEY_PROD` is ignored). `ensureAdCenterLevel1Key` (cache → local `PUT /api/key` by advertiser_id → gateway hop → env map last). Do **not** pin advertiser IDs in MCP key maps.
- Admin mint key (`ADCENTER_ADMIN_API_KEY`) lives in host `.env` (gitignored). Never commit it. Never return it (or minted keys) in ChatGPT envelopes. Mint is always `level: 1`.
- Env map `ADCENTER_LEVEL1_KEYS_*` is break-glass / migration only (host env / vault — not git). **Never invent or hardcode keys in PRs.**
- Base URL: `ADCENTER_API_BASE_URL_STAGING` (default `https://apiad.admedia.com/v1/`) — **not** `stagingadcenter.admedia.com` (UI only)
- Local fixture (no minted key / no gateway): `ADCENTER_FIXTURE=true` — never on hosted staging/prod
- Name-first: pass `advertiser` (e.g. `lucos_synth_adv` / sireesha) to `adcenter_list_campaigns` — the tool resolves `adv_id`
- Consumer notes: `docs/ADCENTER-LEVEL1.md`
- Fixture IDs: `docs/fixtures/adcenter-staging.json`
- Ops ensure-key / mint/smoke: `api.lucos.com` (`docs/ADCENTER-LEVEL1-OPS.md`, `scripts/mint-adcenter-level1-key.sh` is break-glass, `scripts/smoke-adcenter-level1.sh`)
- HTTP connector (TW-302): `connectors/adcenter/` — `getCampaign` / `getCreative` / `getReportSummary` / `getCampaignReport` (level-1 only)
- MCP AdCenter read tools: display (`adcenter_get_*` / `adcenter_list_*`), SM (`adcenter_sm_*`), AdWords (`adcenter_adwords_*`). Pass `advertiser` with the advertiser name (e.g. sireesha); the tool resolves `adv_id`. Optionally pass numeric `advertiser_id` if already known. POST-only report routes (`/sm/report/*`, `/adwords/reports/*`), uploads, and link/assign are not exposed. Do **not** register `adcenter_ensure_level1_key` as an MCP tool.

## Schema harvest (TW-297)

- Job: `jobs/schema_harvest/` → `catalogs/schemas/{env}/{target}.json`
- **INFORMATION_SCHEMA only** — never add row-dump queries
- Do **not** expand `allowlist.json` without ticket + DBA/security review
- Targets: `admin` (Admin), `shorty`, `adcenter`, `whale`, `keywords` — real schema names in allowlist
- Fixture (no DB): `npm run harvest:schemas -- --env staging --target admin --fixture tests/fixtures/schema-harvest-admin.json`
- Live staging: e.g. `SCHEMA_HARVEST_ADMIN_DSN_STAGING` then `--env staging --target admin`
- Live prod: `SCHEMA_HARVEST_ALLOW_PROD=true` + e.g. `SCHEMA_HARVEST_ADMIN_DSN_PROD`
- Details: `jobs/schema_harvest/README.md`

## Sensitivity review (TW-298)

- Job: `jobs/sensitivity_review/` — name rules + overrides → apply → index gate
- Overrides: `catalogs/sensitivity/{env}/{target}.overrides.json`
- Apply: `npm run sensitivity:apply -- --catalog catalogs/schemas/staging/admin.json --overrides catalogs/sensitivity/staging/admin.overrides.json`
- Gate (must pass before TW-299): `npm run sensitivity:gate -- --catalog catalogs/schemas/staging/admin.json`
- Details: `jobs/sensitivity_review/README.md`

## Catalog index / Qdrant (TW-299)

- Job: `jobs/catalog_index/` → collection **`lucos_business_catalog`** (never `adpilot_embeddings`)
- Requires TW-298 gate pass; skips `pii` / `secret` / `deny_index` / `unknown` columns
- Dry-run: `npm run catalog:index -- --catalog catalogs/schemas/staging/admin.json --dry-run`
- Fixture (no network): `npm run catalog:index -- --catalog catalogs/schemas/staging/admin.json --fixture`
- Live defaults: `QDRANT_URL=http://127.0.0.1:6333`, collection `lucos_business_catalog`, model `text-embedding-3-small` / 1536 — set `OPENAI_API_KEY` for live
- Details: `jobs/catalog_index/README.md`

## catalog_search (TW-300)

- MCP tool → embed query → Qdrant `lucos_business_catalog` (schema metadata hits only)
- Still gated by enablement + `authorizeToolCall` (broker mock until real gateway client lands in this repo)
- Disabled by default; local smoke:

```bash
export LUCOS_POLICY_ENABLED=true
export LUCOS_TOOL_ENABLE_CATALOG_SEARCH=true
export LUCOS_BROKER_MOCK_ALLOW=true
export OPENAI_API_KEY=sk-...   # or CATALOG_SEARCH_FIXTURE=true for offline
npm start
```

- Implementation: `connectors/catalog/search.ts`, `jobs/catalog_index/search.ts`

## Engineering code tools (TW-307 / TW-308)

- MCP tools: `code_search`, `get_file`, `get_file_lines`
- **Execute-on-gateway** (not authorize-then-local): full-body `POST` to `api.lucos.com/api/v1/tools/{tool}` via `executeGatewayTool` / `runGatewayToolCall`
- Gateway owns RAG adapter + engineering RBAC + secret filter + audit (see `api.lucos.com/docs/ENGINEERING-CODE-TOOLS.md`)
- Never mix AdCenter level-1 keys or business RO DSNs on this path
- Disabled by default; local mock smoke (empty citations fixture):

```bash
export LUCOS_POLICY_ENABLED=true
export LUCOS_TOOL_ENABLE_CODE_SEARCH=true
export LUCOS_TOOL_ENABLE_GET_FILE=true
export LUCOS_TOOL_ENABLE_GET_FILE_LINES=true
export LUCOS_BROKER_MOCK_ALLOW=true
npm start
```

- Live (gateway + RAG): `LUCOS_BROKER_MOCK_ALLOW=false`, `LUCOS_GATEWAY_BASE_URL=…`, Bearer MCP API key on session open / tools/call; gateway kill-switches + `RAG_QUERY_SERVICE_URL` must be enabled on `api.lucos.com`
- Timeout: `LUCOS_GATEWAY_EXECUTE_TIMEOUT_MS` (default 65000)

## Code orchestrator tools (TW-337)

- MCP tools: `request_code_change`, `get_code_change_status`
- **In-process HTTP** to Lucos Code Orchestrator (`mcp-code-editor.lucos.com`) — not execute-on-gateway. Jira issue creation stays in collab tools; these only submit a change job and poll status.
- Same-host default when co-located with business-mcp: `LUCOS_CODE_ORCHESTRATOR_URL=http://127.0.0.1:3340` (+ `LUCOS_CODE_ORCHESTRATOR_TOKEN` from vault).
- `request_code_change` needs `requirement`. Create the Jira issue first (`jira_create_issue`) and pass `jira_key` so the orchestrator branch is `ai/<KEY>`. Omit `jira_key` only as an escape hatch (orchestrator random `ai/run-*`). Returns a `changeJob` with `id`. Poll with `get_code_change_status` + `change_job_id`. If submit **times out**, recover by polling — the job may still be running. Local simulators (`simulate:code-change`, `simulate:direqt-security`) create the ticket themselves unless `--jira-key` or `--skip-jira`.
- **Disabled by default** (`policies/tool_enablement.json`); enable with `LUCOS_TOOL_ENABLE_REQUEST_CODE_CHANGE=true` and `LUCOS_TOOL_ENABLE_GET_CODE_CHANGE_STATUS=true` (+ `LUCOS_POLICY_ENABLED=true`).
- Implementation: `connectors/engineering/code-orchestrator.ts`
- Local E2E simulator (no MCP server): `npm run simulate:code-change` — see `scripts/README-SIMULATE-CODE-CHANGE.md`

## Collaboration tools (TW-338)

- MCP tools: `jira_search_issues`, `jira_get_issue`, `jira_list_projects`, `jira_get_project`,
  `jira_list_boards`, `jira_list_sprints`, `jira_list_sprint_issues`, `jira_list_users`,
  `jira_list_versions`, `jira_create_issue`, `jira_modify_issue`, `jira_upload_attachment`, `jira_edit_comment`, `confluence_search_pages`, `confluence_get_page`,
  `confluence_get_comments`, `confluence_list_children`, `confluence_list_spaces`,
  `confluence_list_attachments`, `confluence_get_versions`, `confluence_search_users`,
  `confluence_get_space_permissions`, `confluence_get_user_space_access`,
  `confluence_create_page`, `confluence_update_page`, `confluence_add_comment`, `confluence_edit_comment`, `confluence_upload_attachment`,
  `slack_search_messages`, `slack_list_channels`, `slack_ask`, `slack_get_latest_messages`, `slack_get_file`, `slack_list_users`, `slack_get_user`, `slack_check_scopes`, `user_map`, `gdrive_search_files`,
  `gdrive_read_file`, `gdrive_query_sheet`, `gdrive_list_workspaces`, `gdrive_drive_overview`, `gdrive_user_drive_info`,
  `gdrive_find_duplicates`, `gdrive_lookup_owned_file`, `gdrive_list_recent_activity`, `gdrive_list_permissions`, `gdrive_list_revisions`, `gdrive_list_comments`, `gcal_list_events`, `gcal_get_event`,
  `gcal_list_resources`, `gcal_propose_rsvp`, `gcal_confirm_write`, `gcal_cancel_write`,
  `gmail_list_users`, `gmail_get_user`, `gmail_list_labels`, `gmail_list_messages`, `gmail_get_message`, `gmail_list_threads`,
  `gmail_get_thread`, `gmail_get_attachment`, `gmail_propose_send`, `gmail_propose_reply`,
  `gmail_propose_forward`, `gmail_propose_draft`, `gmail_propose_modify_labels`,
  `gmail_confirm_write`, `gmail_cancel_write`,
  `fireflies_search_meetings`, `fireflies_get_meeting`, `fireflies_list_users`, `fireflies_ingest_recent`,
  `fireflies_list_indexed`, `fireflies_find_duplicates`, `fireflies_correlate_meeting`, `fireflies_ensure_fred_invite`,
  `bb_list_projects`, `bb_list_repos`, `bb_list_prs`, `bb_get_pr`,
  `bb_list_branches`, `bb_list_commits`, `bb_file_diff`,
  `hubspot_list_accounts`, `hubspot_search_contacts`, `hubspot_get_contact`, `hubspot_get_contact_related`,
  `hubspot_search_companies`, `hubspot_get_company`, `hubspot_search_deals`, `hubspot_get_deal`,
  `hubspot_search_tickets`, `hubspot_get_ticket`, `hubspot_list_owners`, `hubspot_get_owner`,
  `hubspot_list_properties`, `hubspot_list_pipelines`, `hubspot_list_lists`, `hubspot_list_list_members`,
  `hubspot_search_notes`, `hubspot_get_note`, `hubspot_list_associations`, `hubspot_get_timeline`,
  `hubspot_search_activities`, `hubspot_get_attachment`,
  `hubspot_propose_create_note`, `hubspot_confirm_write`, `hubspot_cancel_write`
- Do **not** expose slack-bot multiplexers (`jira_search_issues.entity`, `bitbucket_search`) as MCP tools — use the dedicated names above.
- `jira_search_issues` accepts `query` and/or filters (`reporter`, `watcher`, `priority`, `issue_type`, `labels`, `sprint`, `epic_key`, `period`, `date_range`, `date_field`, `stale_days`, `sort`, `start_at`, `next_page_token`, `aggregate`, `count_only`, `is_pr_request`, history: `assignee_from`/`assignee_to`/`assigned_by`, `changed_field`+`changed_from`/`changed_to`/`changed_by`, `closed_by`, `priority_from`/`priority_to`, `commented_by`, `mentioned`). `query` is not required when a filter is set — e.g. `{ period: "yesterday", project: "TAS", limit: 5 }`. `period` tokens: `yesterday`, `today`, `this_week`, `last_month`, `last_7_days`, `last_N_days`.
- `jira_get_issue` optional `include_changelog` / `include_comments` / `include_subtasks` (default true) / `is_pr_request` / `comments_start` / `comments_limit` / `history_start` / `history_limit` / `history_order`. `jira_list_boards` optional `user`. Write tools: `jira_create_issue` (requires `project`, `title`; optional `watchers`/`cc`/`watchers_to_add`) and `jira_modify_issue` (requires `issue_key` + at least one field; optional `watchers_to_add`/`cc`/`watchers_to_remove`/`cc_to_remove`/`unwatch`; `comment` adds, `jira_edit_comment` edits), `jira_upload_attachment` (`issue_key` + `content_url`), `jira_edit_comment` (`issue_key` + `comment_id` + `comment`). Confluence write tools: `confluence_create_page` (requires `space_key`, `title`, `body`; optional `body_format` plain|storage, `parent_id`, `labels`), `confluence_update_page` (`page_id` or `page_url` + at least one of `title`/`body`/`add_labels`/`remove_labels`; optional `body_mode` replace|append), `confluence_add_comment` (`page_id` or `page_url` + `body`), `confluence_edit_comment` (`comment_id` + `body`), `confluence_upload_attachment` (`page_id` or `page_url` + `content_url`). `bb_list_prs` optional `merged_by` / `period` / `count_only`. `bb_list_commits` optional `path` / `first`. File diffs are `bb_file_diff`, not `get_file`.
- `confluence_search_pages` accepts `query` and/or filters (`title`, `creator`, `labels`, `space_key`, dates, `ancestor`). Do not pass CQL. Slack screenshots / images: `slack_ask` / `slack_get_latest_messages` / `slack_search_messages` return `images[]` (file id + name) and inline up to 3 thumbnail bytes as MCP image content so you can see them; call `slack_get_file` with `file_id` for more or full-size. Slack people: `slack_get_user` (id/email/username) or `slack_list_users`. Scope/token diagnostics: `slack_check_scopes` only when Slack tools fail — do not mention OAuth scopes to the user. Drive sidebar / Shared drives → `gdrive_drive_overview` (or `gdrive_list_workspaces`); per-user Drive → `gdrive_user_drive_info`; owner lookup → `gdrive_lookup_owned_file`; duplicates → `gdrive_find_duplicates`; latest folder opened / Drive Activity → `gdrive_list_recent_activity` (`mime_type=folder`); file sharing/ACL → `gdrive_list_permissions`; version history → `gdrive_list_revisions`; Drive comments → `gdrive_list_comments` (all need `file_id`); then `gdrive_search_files` with `scope` / `drive_id` / `as_user`; recursive folder listing (`folder_id`/`folder_name` + `recursive=true`); `owned_by`; `mime_type` video|image|audio|exe. Spreadsheets: `gdrive_read_file` (preview or one tab CSV via `sheet_name`); filtered JSON rows → `gdrive_query_sheet`. Calendar → `gcal_list_events` then `gcal_get_event`; rooms → `gcal_list_resources`; RSVP → `gcal_propose_rsvp` then `gcal_confirm_write`. Gmail → `gmail_list_messages` then `gmail_get_message` / `gmail_get_thread`; labels `gmail_list_labels`; attachments `gmail_get_attachment`; writes `gmail_propose_send|reply|forward|draft|modify_labels` then `gmail_confirm_write`. Default user=requester; pass `user` name/email. Folders inbox|sent|trash|archive|drafts|spam. Writes gated by slack-bot `GMAIL_WRITES_ENABLED`. Google Vault out of scope. Fireflies: search live transcripts → `fireflies_search_meetings` then `fireflies_get_meeting`; confirm API key owner → `fireflies_list_users`; local index → `fireflies_ingest_recent` then `fireflies_list_indexed`; duplicate detection → `fireflies_find_duplicates`; cross-system bundle (Calendar + Drive) → `fireflies_correlate_meeting`; auto-invite Fred → `fireflies_ensure_fred_invite`. HubSpot CRM → GENERIC questions (no portal named): omit `account` on hubspot_search_* / list / get so all 4 portals are queried in one call; answer with every portal from `by_account[]` (account_label + total/result_count), including zeros. Named portal (Ad.com → adcom, Advertising.com → advertising, Influential.com → influential, Monetize.com → monetize): pass `account`. Do not call `hubspot_list_accounts` first for generic searches. Writes (`hubspot_propose_create_note`) still require `account`. Pagination `after` requires a specific account. Default date timezone **Asia/Kolkata** (GMT+5:30); use `timezone` or `use_portal_timezone=true` for portal US/Eastern. Deal search: never put `closedwon` in query — use `closed_only` or `dealstage=closedwon`. Search convenience filters: `owner_id`, `email`, `domain`, `lifecyclestage`, `hs_lead_status`, `pipeline`, `filters` (raw CRM Search `{ propertyName, operator, value }`). Property/stage names → `hubspot_list_properties` / `hubspot_list_pipelines`. Saved lists → `hubspot_list_lists` then `hubspot_list_list_members` (list membership is not a CRM Search filter). Writes: `hubspot_propose_create_note` → `hubspot_confirm_write` (or `hubspot_cancel_write`); tokens and `HUBSPOT_WRITES_ENABLED` live on slack-bot only. Do not expose `bitbucket_search`. Do not send a collab S2S Bearer from MCP clients — gateway/direct-hop injects it.
- **Direct slack-bot hop** when `LUCOS_COLLAB_SERVICE_URL` is set: MCP Bearer
  (`lucos_mcp_live_…`) is the auth middleware, then
  `POST {LUCOS_COLLAB_SERVICE_URL}/internal/v1/tools/{tool}` with `{ environment, args }`.
  Does **not** go through `api.lucos.com`. Write tools skip slack-bot S2S (`jira-no-auth`).
  Reads send `LUCOS_COLLAB_S2S_TOKEN` + `X-Lucos-Org-Id` when configured.
- If `LUCOS_COLLAB_SERVICE_URL` is unset, collab tools still use execute-on-gateway
  (`POST /api/v1/tools/{tool}`) as a fallback. Gateway prefixes (`jira_`, `slack_`, `gdrive_`, `gcal_`, `gmail_`, `hubspot_`, …) must be on api.lucos.com `COLLABORATION_TOOL_PREFIX_RE`.
- Integration service owns JQL/CQL/Slack/Drive translation; MCP passes plain `query` + filters only
- Contract: `docs/COLLAB-INTEGRATION-SERVICE-API.md`; design: `docs/COLLAB-TOOL-INTEGRATION.md`
- Local mock smoke (`LUCOS_BROKER_MOCK_ALLOW=true`):

```bash
export LUCOS_POLICY_ENABLED=true
export LUCOS_TOOL_ENABLE_JIRA_SEARCH_ISSUES=true
export LUCOS_TOOL_ENABLE_JIRA_GET_ISSUE=true
export LUCOS_BROKER_MOCK_ALLOW=true
npm start
```

- Live: `LUCOS_BROKER_MOCK_ALLOW=false`, `LUCOS_GATEWAY_BASE_URL=…`, Bearer MCP API key on session open / tools/call.
  Gateway needs `COLLAB_INTEGRATION_SERVICE_URL` (slack-bot-service) + `COLLAB_INTEGRATION_SERVICE_TOKEN`
  matching slack-bot `COLLAB_S2S_TOKEN`.

## Entity / status dictionary (TW-327)

- Path: `catalogs/entities/` — status meanings + field allowlists/denylists for Phase-1 entities
- **Reviewed definitions only** — never live row/PII payloads
- Loader: `catalogs/entities/load.ts` (`loadAllEntityDefinitions`, `findEntityDefinition`)
- Feeds planned `get_entity_definition` + MNGT RO tools / TW-309 column notes
- Mark `reviewStatus: "reviewed"` only after AdOps/privacy confirmation of DB status codes
- Details: `catalogs/entities/README.md`

## MNGT RO SQL connector (TW-310)

- Connector: `connectors/sql/` — `runApprovedQuery({ queryId, params })` only
- Approved SQL: `catalogs/queries/mngt-ro-queries.json` (no caller-authored SQL / no `execute_sql`)
- View registry: `catalogs/views/mngt-ro-views.json`
- DSN env: `MNGT_RO_DSN_STAGING` / `MNGT_RO_DSN_PROD` (prod gated by `MNGT_RO_ALLOW_PROD`)
- Offline tests: pass `fixtureRows` or set `MNGT_RO_FIXTURE=true`
- Design / DDL: `docs/MNGT-RO-VIEWS.md`, `catalogs/queries/mngt-ro-views-staging.sql`
- Deploy runbook: `docs/MNGT-DEPLOYMENT.md` (staging → prod, DBA + MCP service)
- Staging PM2 + full pipeline: `docs/STAGING-DEPLOY.md`, `deploy/`
- MCP tools TW-311: `get_advertiser` + `get_publisher` (disabled by default; `connectors/mngt/`)
- MCP tools TW-312: `get_campaign_budget` + `get_publisher_caps`
- MCP tools TW-313: `get_publisher_agreement_status` + `get_publisher_spaf_status` (signed|missing, uploaded|missing)
- MCP tools TW-328 Phase-2: `summarize_*_by_status`, `search_*`, `list_publishers_pending_approval`, `list_publishers_missing_*`, `list_campaigns`
- MCP tools (product-tag / domain targeting): `list_traffic_channels`, `list_campaigns_by_product_tag` (`contextual_banner` → `Search_31`), `search_campaigns_by_domain_targeting` (requires `adv_id`), `search_oos_site_mapping`
- Handlers: `connectors/mngt/targeting.ts`; DDL views 7–10 in `catalogs/queries/mngt-ro-views-staging.sql` (DBA must CREATE + GRANT before live reads)
- MCP tool TW-330: `list_advertisers_without_recent_campaigns` (requires `lucos_ro_campaign_budget.created_at` from `campaign.creation`)
- MCP tool TW-331: `list_publishers_onboarded` (requires `lucos_ro_publisher.onboarded_at` from `Affiliate_info.timedate`)
- MCP tool TW-334: `list_campaigns_at_cap` (current budget_spent >= budget; derived `cap_status=at_budget_cap`)
- MCP tool TW-335: `list_campaigns_capped_frequently` (requires `lucos_ro_campaign_daily_budget` from `daily_budget_stats`; `adv_id` optional — omit for unscoped top 50 by `days_at_cap`)
- Policy default: tools **enabled** in `policies/tool_enablement.json` (kill with `LUCOS_POLICY_ENABLED=false`)

## Ad-hoc read-only query tool (TW-XXX) — STAGING ONLY, DISABLED BY DEFAULT

- **Not** a generic `execute_sql`. The caller sends a natural-language `question`; the model emits a
  **structured QuerySpec** (never SQL); our code **compiles** it to parameterized SQL. Name is
  `adhoc_explore` (must pass `isDenylistedToolName` — never a `*_sql` name).
- Connector: `connectors/adhoc/` — `runAdhocExplore({ question, environment })`.
  Pipeline: retrieve (Qdrant) → plan (LLM) → validate spec → compile → static AST guard →
  EXPLAIN cost gate → hardened RO execute → redact → respond-with-provenance.
- **Staging only.** `environment:'prod'` hard-fails (`prod_gated`). No prod DSN var exists.
- **Security boundary is the DB grant** (DBA-owned, prod-applicable): dedicated SELECT-only RO user,
  column/view-scoped to `public`/`internal` columns only; `pii/secret/deny_index/unknown` never
  granted and never indexed. See `docs/ADHOC-QUERY-TOOL.md` §5.
- Six code-enforced guardrail layers (gate → input → spec → compile → AST → EXPLAIN → runtime),
  all with safe defaults so the tool is fully guarded with zero extra env. See §8.
- SQL is generated server-side only; every value is a bind param; identifiers are allowlisted;
  always ends `LIMIT ?`; server-side `max_execution_time`; EXPLAIN rejects full scans.
- Every call audits the generated SQL + params + EXPLAIN summary + rowCount + duration; the response
  returns `sql`/`tables`/`explain` so results are verifiable. Output is **exploratory, not reportable**.
- DSN: per-target `ADHOC_{ADMIN,SHORTY,ADCENTER,WHALE,KEYWORDS}_RO_DSN_STAGING` (SELECT-only user, read replica). Until DBA creates `lucos_adhoc_ro`, **reuse the same staging URLs as existing tools**: `MNGT_RO_DSN_STAGING` (Admin), `CAKE_{ADMIN,WHALE,SHORTY}_RO_DSN_STAGING`. Harvest DSNs are last-resort for adcenter/keywords (no prior SQL tool). No prod DSN vars. Cross-instance joins are rejected.
- Enable (staging): on by default in `policies/tool_enablement.json`. Still staging-only (prod is rejected).
- **Client routing:** approved tools first. Portfolio Cake spend is `get_advertiser_report`
  `group_by=total` / `summary.spend`. Cross-advertiser affiliate conversion rank is
  `group_by=affiliate` + `sort_by=advertiser_conversions` (omit `advertiser_id`; do not N× loop).
  `adhoc_explore` is the fallback when an approved tool errors, returns empty rows, or cannot
  express the grain (including campaign rank with no `advertiser_id`). `analyst_agent` auto-invokes it.
- Design + reference implementation: `docs/ADHOC-QUERY-TOOL.md`.

## recommend_actions (TW-XXX) — STAGING ONLY

- Optimization recommendations from approved Cake/MNGT reads + deterministic solvers.
  Caller sends an **intent enum**, never SQL. Output is advisory (`causal: false`).
- Connector: `connectors/recommend/` — `runRecommendActions({ intent, horizon, … })`.
- Staging only (`prod_gated`). Prefer this over `analyst_agent` looping for the Q1–Q10
  classes in `docs/ADCENTER-QUESTION-PLAYBOOK.md`. P1 = `recommend_heuristics_v1`.
  P2 forecast = `holt_damped_v1`; incrementality = `half_window_saturation_v1` (not geo-lift).
  See `docs/RECOMMEND-ACTIONS.md`.

## cake reporting tools (TW-314 / TW-316 / TW-317 / TW-329)

- Connector: `connectors/cake/` — `runGetAdvertiserReport`, `runGetAffiliateReport`,
  `runGetPostbackReport`, `runGetIvtReport` (mirrors `connectors/mngt/`)
- Approved queries: `catalogs/queries/get_*_report.json` — window/row/timeout bounds live here,
  not in code, so a limit change is a reviewed catalog diff
- Views (TW-314): `catalogs/queries/cake-ro-views-staging.sql` (static) +
  `cake-ro-shard-views-staging.sql` (day-sharded, created nightly).
  Registry `catalogs/views/cake-ro-views.json`. Design `docs/CAKE-RO-VIEWS.md`
- Naming follows TW-309: `<schema>.lucos_ro_*`. cake spans **three** schemas
  (`whale`, `Admin`, `Shorty`) on separate instances, so `schema` is per view
- DSNs: `CAKE_{ADMIN,WHALE,SHORTY}_RO_DSN_{STAGING,PROD}`; prod needs `CAKE_RO_ALLOW_PROD=true`
- Redaction: `redaction/postback-url.ts` (URL + IP), `redaction/field-policy.ts` (allow/deny,
  throws rather than silently dropping a denied field)
- Day-sharded reads are expanded into a bounded `UNION ALL`; missing shards are reported as
  `partial`, never silently skipped
- MCP tools TW-314 / TW-316 / TW-317 / TW-329 / TW-332 / TW-333 / TW-336 / TW-337: `get_advertiser_report`
  (`group_by`: publisher default / advertiser / total; always includes unbounded `summary` totals),
  `get_affiliate_report`, `get_postback_report`, `get_ivt_report`,
  `get_campaign_performance_report` (campaign grain + optional `analysis` modes:
  `volatile_conversions`, `stable_traffic_decline`, `historical_deterioration`,
  `budget_increase_allocation`, `spend_opportunity` — see
  `docs/ADCENTER-QUESTION-PLAYBOOK.md` §5.8),
  `compare_campaign_performance` (unscoped period-over-period predicates:
  `roas_underperform`, `spend_up_conversions_down` (estimated_conversions
  down; campaign CPA `cpa` emitted as spend/estimated_conversions; campaign
  CPA vs previous month uses mtd vs prior_mtd), `spend_increase`; omit
  `advertiser_id`; scan-then-filter so underperformers are not dropped by spend LIMIT;
  not for advertiser-grain average CPA last-30-vs-previous-30 / this-month-vs-last-month —
  that is two `get_advertiser_report` `group_by=advertiser` reads, rolling last 30 vs
  previous 30 or MTD vs last_month, CPA = spend / estimated_conversions),
  `list_affiliates_on_multiple_advertisers` (Q3 COUNT DISTINCT adv_id on whale advertiser_stats, min 2, row-capped 50),
  `list_publishers_by_active_campaigns` (Q5 whale affiliate×camp merged with directory status=A, row-capped 50; needs lucos_ro_campaign_directory.status),
  `list_publishers_traffic_to_inactive_campaigns` (Cake clicks on campaigns with current status<>A, row-capped 50),
  `list_campaigns_without_traffic` (Q4 Admin directory anti-joined to whale click set, default 7d, active only, row-capped 50),
  `search_affiliates` (Cake affiliate_id lookup on lucos_ro_affiliate_directory / AffiliateIDs; q or publisher_id or affiliate_id; no CPC scan),
  `list_publisher_offers` (assigned live offers: AffiliateIDs → affiliate_offers_mappings → publisher_advertiser_offers_from_accounts; not CPC),
  `list_advertisers_low_traffic_concentration` (many active camps with CPC traffic, ranked by low top_campaign_share; needs lucos_ro_campaign_directory.status),
  `list_advertisers_high_revenue_concentration` (campaign-revenue concentration + server-derived revenue_risk_20pct; TW-340; never invert the low-traffic tool),
  `list_advertisers_with_affiliates` (top advertisers by sales_revenue with nested per-advertiser affiliates; do not join group_by=affiliate onto an advertiser rank),
  `get_campaign_affiliate_report` (campaign×affiliate CPC clicks + affiliate_earning; optional `active_only=1` for currently-active campaigns with CPC; no sales_revenue at this grain; TW-341; not assigned offers)
- MCP surface takes `date_range: {from,to}` (house style); the catalog uses flat
  `date_from`/`date_to` and `connectors/cake/index.ts` maps between them
- Rules: `docs/CAKE-SHARD-AND-DATE-RANGE-RULES.md`, `docs/CAKE-POSTBACK-URL-REDACTION-RULES.md`,
  `docs/CAKE-IVT-AGGREGATION-BOUND.md`; open items `docs/CAKE-OPEN-DECISIONS.md`
- **Open:** sharded views need a new GRANT nightly and MySQL cannot wildcard table grants —
  `docs/CAKE-RO-VIEWS.md` § "Grants for sharded views"

## Local run

```bash
nvm use          # reads .nvmrc → Node 24.19.0
npm install
npm start        # MCP SSE on http://127.0.0.1:3333/sse (+ /mcp Streamable HTTP)
npm run typecheck
npm test
npm run lint
```

`/healthz` is public. `/mcp`, `/sse`, `/messages` require a token (`Authorization: Bearer …` or `?api_key=`) even when mock is on.

Enable health stub for local smoke:

```bash
export LUCOS_POLICY_ENABLED=true
export LUCOS_TOOL_ENABLE_HEALTH_STUB=true
export LUCOS_BROKER_MOCK_ALLOW=true
npm start
```

Send any Bearer (dummy `lucos_mcp_live_local` is fine under mock), or `?api_key=` for ChatGPT. Hosted staging sets `LUCOS_BROKER_MOCK_ALLOW=false` so session open validates the key via `GET /api/v1/tools`.

Gateway token **verification** stays on `api.lucos.com`. This repo forwards the caller's Bearer (see `server/auth-header.ts` + `server/session-bearer.ts`). ChatGPT OAuth / Google SSO is unused (later).

Live AdCenter HTTP calls mint a level-1 key via `PUT /api/key` (`ADCENTER_ADMIN_API_KEY` in `.env`) or fall back to gateway ensure-key / `ADCENTER_FIXTURE=true`. Do not invent keys.

## What not to implement here

- JWT / RBAC / audit middleware (→ `api.lucos.com` / TW-284). The MCP transport gate only checks token presence (header or `api_key` query) + (when mock is off) gateway `GET /api/v1/tools`.
- Mongo or MCP key storage — this repo stays a credential forwarder
- Inventing AdCenter keys in PRs or returning them to ChatGPT. Mint uses `ADCENTER_ADMIN_API_KEY` in gitignored `.env` only.
- Registering `adcenter_ensure_level1_key` as an MCP tool (ChatGPT never mints; MCP/gateway do)
- Synthetic advertiser fixture registry (IDs only → `api.lucos.com/docs/fixtures/adcenter-staging.json`)
- MNGT RO view DDL (→ TW-309)

Connectors resolve AdCenter keys via cache → local mint by advertiser_id → gateway hop → env map. Never commit `ADCENTER_ADMIN_API_KEY`. Agents must not invent or hardcode keys in PRs.