# MNGT deployment guide (staging and production)

How to roll out the Lucos MNGT read-only track end-to-end:

| Layer | Tickets | What ships |
| ----- | ------- | ---------- |
| **Database** | TW-309 (+ targeting cut) | Ten `Admin.lucos_ro_*` views + RO grants |
| **MCP service** | TW-310–313, TW-328 | RO SQL connector + entity + Phase-2 + product-tag/domain tools |

This doc is the operator runbook. View design and column rules live in [`MNGT-RO-VIEWS.md`](./MNGT-RO-VIEWS.md).

---

## Deployment order

Always **staging first**, then production after smoke passes.

```mermaid
flowchart LR
  A[DBA: create views] --> B[DBA: RO user + grants]
  B --> C[Deploy business-mcp]
  C --> D[Set MNGT DSN env]
  D --> E[Enable tools gradually]
  E --> F[Smoke MCP tools]
  F --> G{Pass?}
  G -->|yes| H[Repeat for prod]
  G -->|no| I[Disable tools / fix]
```

---

## Part 1 — Database (DBA)

### 1.1 Create views

DDL is in [`catalogs/queries/mngt-ro-views-staging.sql`](../catalogs/queries/mngt-ro-views-staging.sql).

Apply with a **write** MySQL account on the target Admin schema (harvest RO users cannot `CREATE VIEW`).

**Staging**

```bash
mysql -h STAGING_HOST -u DBA_USER -p Admin < catalogs/queries/mngt-ro-views-staging.sql
```

**Production**

Use the same DDL against prod Admin once staging is verified. Confirm schema name is still `Admin` (see open questions in `MNGT-RO-VIEWS.md`).

Views created:

| View | MCP tool |
| ---- | -------- |
| `lucos_ro_advertiser` | `get_advertiser` |
| `lucos_ro_publisher` | `get_publisher`, `list_publishers_onboarded` (needs `onboarded_at`) |
| `lucos_ro_campaign_budget` | `get_campaign_budget`, `list_campaigns`, `list_campaigns_at_cap`, `list_advertisers_without_recent_campaigns` (needs `created_at`), `list_campaigns_capped_frequently` (join) |
| `lucos_ro_publisher_caps` | `get_publisher_caps` |
| `lucos_ro_publisher_agreement` | `get_publisher_agreement_status` |
| `lucos_ro_publisher_spaf` | `get_publisher_spaf_status` |
| `lucos_ro_traffic_channels` | `list_traffic_channels` |
| `lucos_ro_campaign_product_targeting` | `list_campaigns_by_product_tag`, `search_campaigns_by_domain_targeting` |
| `lucos_ro_domain_list_members` | (join partner for domain search) |
| `lucos_ro_oos_site_mapping` | `search_oos_site_mapping` |
| `lucos_ro_campaign_daily_budget` | `list_campaigns_capped_frequently` |

### 1.2 RO user and grants (optional until live SQL needed)

When ready for live DB reads (not fixture mode), create a dedicated read-only user, e.g. `lucos_mngt_ro`. Grant **SELECT on views only** — never on base tables.

```sql
-- Illustrative — DBA picks final username/host pattern
GRANT SELECT ON Admin.lucos_ro_advertiser TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_publisher TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_campaign_budget TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_publisher_caps TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_publisher_agreement TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_publisher_spaf TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_traffic_channels TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_campaign_product_targeting TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_domain_list_members TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_oos_site_mapping TO 'lucos_mngt_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_campaign_daily_budget TO 'lucos_mngt_ro'@'%';
-- NO grants on Affiliate, Affiliate_info, Advertiser_info, campaign,
-- campaign_targeting, traffic_channels, ads_domain_list, serp_oos_site_mapping, etc.
```

Store the password in vault; wire DSN into host env (below). **Never commit credentials.**

### 1.3 DBA acceptance checks

```sql
-- As lucos_mngt_ro (or equivalent)
SELECT adv_id, advertiser_name, status FROM Admin.lucos_ro_advertiser LIMIT 1;
SELECT publisher_id, agreement_status FROM Admin.lucos_ro_publisher_agreement LIMIT 1;
SELECT publisher_id, spaf_status FROM Admin.lucos_ro_publisher_spaf LIMIT 1;

-- Product-tag / domain targeting
SELECT traffic_id, traffic_name FROM Admin.lucos_ro_traffic_channels WHERE traffic_id = 'Search_31';
SELECT campaign_id, campaign_name FROM Admin.lucos_ro_campaign_product_targeting
  WHERE traffic_id = 'Search_31' LIMIT 20;
SELECT c.campaign_id, c.campaign_name, m.list_name, m.domain
FROM Admin.lucos_ro_campaign_product_targeting c
JOIN Admin.lucos_ro_domain_list_members m
  ON JSON_CONTAINS(c.include_list_ids, CAST(m.list_id AS JSON), '$')
WHERE c.adv_id = 13421 AND c.traffic_id = 'Search_31'
  AND LOWER(m.domain) = 'insuranceandleisure.com' LIMIT 50;

-- Must fail (no base-table grant)
SELECT * FROM Admin.Affiliate_info LIMIT 1;
SELECT * FROM Admin.campaign_targeting LIMIT 1;
```

Confirm:

- [ ] PII columns are absent from view definitions (email, SSN, agreement longtext, spafupload paths, etc.)
- [ ] `agreement_status` is only `signed` or `missing`
- [ ] `spaf_status` is only `uploaded` or `missing`
- [ ] Targeting views never expose full `campaign_targeting.json` or pixel fields
- [ ] RO user cannot write

---

## Part 2 — MCP service (`business-mcp.lucos.com`)

**Staging PM2 runbook (full pipeline):** [`STAGING-DEPLOY.md`](./STAGING-DEPLOY.md) — harvest → sensitivity → index → `catalog_search` → MNGT → PM2.

Deploy the Node service on your staging VM with PM2 (recommended).

### 2.1 Build and verify

```bash
cd business-mcp.lucos.com
nvm use          # Node 24.19.0 — see .nvmrc
npm ci
npm run typecheck
npm test
npm run lint
```

Optional compile: `npm run build` (runtime uses `tsx` via `npm start`).

### 2.2 Network

The MCP host must reach:

| Target | Purpose |
| ------ | ------- |
| MySQL Admin (staging/prod) | MNGT RO views via `MNGT_RO_DSN_*` |
| `api.lucos.com` (when TW-325 live) | JWT authz + audit — not mocked in prod |
| Public clients | SSE `/sse` or Streamable HTTP `/mcp` |

### 2.3 Environment variables

Copy from [`.env.example`](../.env.example) or [`deploy/business-mcp.staging.env.example`](../deploy/business-mcp.staging.env.example) into a repo-root **`.env`** (gitignored). npm / PM2 load it via `--env-file-if-exists=.env` — see [`STAGING-DEPLOY.md`](./STAGING-DEPLOY.md).

**MNGT-specific:**

| Variable | Staging | Production | Notes |
| -------- | ------- | ---------- | ----- |
| `MNGT_RO_DSN_STAGING` | Required for live reads | Set if staging DSN needed from prod host | `mysql://user:pass@host:3306/Admin` |
| `MNGT_RO_DSN_PROD` | Omit | Required for prod reads | Gated — see next row |
| `MNGT_RO_ALLOW_PROD` | `false` / unset | `true` after approval | Connector refuses prod without this |
| `MNGT_RO_FIXTURE` | `true` only for dev | **Must be unset / false** | In-memory test data; no real DB |

**Service bind (typical staging vs prod):**

| Variable | Local dev | Hosted staging/prod |
| -------- | --------- | ------------------- |
| `MCP_HOST` | `127.0.0.1` | `0.0.0.0` or internal bind |
| `MCP_PORT` | `3333` | e.g. `3333` behind reverse proxy |
| `LUCOS_BROKER_MOCK_ALLOW` | `true` (local smoke) | **`false`** when gateway is live |

**Example staging env block** (vault-backed on real hosts):

```bash
export MCP_HOST=0.0.0.0
export MCP_PORT=3333
export LUCOS_BROKER_MOCK_ALLOW=false

export MNGT_RO_DSN_STAGING='mysql://lucos_mngt_ro:***@STAGING_HOST:3306/Admin'
# Do not set MNGT_RO_FIXTURE on hosted staging
```

**Example production env block** (after staging sign-off):

```bash
export MCP_HOST=0.0.0.0
export MCP_PORT=3333
export LUCOS_BROKER_MOCK_ALLOW=false

export MNGT_RO_ALLOW_PROD=true
export MNGT_RO_DSN_PROD='mysql://lucos_mngt_ro:***@PROD_HOST:3306/Admin'
```

### 2.4 Start the server

```bash
npm start
# or: npm run dev   # watch mode — not for production
```

Endpoints:

| Path | Use |
| ---- | --- |
| `GET /healthz` | Liveness |
| `GET /sse` | ChatGPT / legacy MCP clients |
| `POST /messages?sessionId=…` | SSE companion |
| `ALL /mcp` | Cursor Streamable HTTP |

### 2.5 Enable MNGT tools (gradual rollout)

All tools are **disabled by default** ([`policies/tool_enablement.json`](../policies/tool_enablement.json)).

**Option A — environment** (no file edit; good for staged rollout):

```bash
export LUCOS_POLICY_ENABLED=true

# TW-311
export LUCOS_TOOL_ENABLE_GET_ADVERTISER=true
export LUCOS_TOOL_ENABLE_GET_PUBLISHER=true

# TW-312
export LUCOS_TOOL_ENABLE_GET_CAMPAIGN_BUDGET=true
export LUCOS_TOOL_ENABLE_GET_PUBLISHER_CAPS=true

# TW-313
export LUCOS_TOOL_ENABLE_GET_PUBLISHER_AGREEMENT_STATUS=true
export LUCOS_TOOL_ENABLE_GET_PUBLISHER_SPAF_STATUS=true
```

**Option B — policy file** — set `enabled: true` on each tool in `policies/tool_enablement.json`. Policy is re-read on each tool call (no redeploy required for flag changes if only the JSON file changes).

Recommended rollout: enable TW-311 → smoke → TW-312 → smoke → TW-313 → smoke.

**Kill switch:** set `LUCOS_POLICY_ENABLED=false` or disable individual `LUCOS_TOOL_ENABLE_*` flags.

---

## Part 3 — Smoke tests

### 3.1 Health

```bash
curl -fsS "http://HOST:3333/healthz"
```

### 3.2 Direct connector (on MCP host, with env set)

Uses approved catalog queries only — no raw SQL.

```bash
# Fixture mode (no DB) — dev only
export MNGT_RO_FIXTURE=true
node --env-file-if-exists=.env ./node_modules/tsx/dist/cli.mjs -e "
  import { runApprovedQuery } from './connectors/sql/run.ts';
  const r = await runApprovedQuery({ queryId: 'get_advertiser', params: { adv_id: 19880 } });
  console.log(JSON.stringify(r.rows[0], null, 2));
"
```

For **live staging**, omit `MNGT_RO_FIXTURE`, set `MNGT_RO_DSN_STAGING`, and use real AdOps fixture IDs when available (`docs/fixtures/mngt-staging.json` — placeholder in `MNGT-RO-VIEWS.md`).

### 3.3 MCP tool calls

With server running and tools enabled, call via Cursor MCP or an MCP client. Each tool accepts optional `environment`: `"staging"` (default) or `"prod"`.

| Tool | Sample args |
| ---- | ----------- |
| `get_advertiser` | `{ "adv_id": 19880, "environment": "staging" }` |
| `get_publisher` | `{ "publisher_id": "AffiliateName", "environment": "staging" }` |
| `get_campaign_budget` | `{ "campaign_id": 12345 }` or `{ "adv_id": 19880 }` |
| `get_publisher_caps` | `{ "publisher_id": "AffiliateName" }` |
| `get_publisher_agreement_status` | `{ "publisher_id": "AffiliateName" }` |
| `get_publisher_spaf_status` | `{ "publisher_id": "AffiliateName" }` |

**Pass criteria:**

- [ ] `found: true` for known fixture IDs
- [ ] No denied fields in JSON (`email`, `agreement`, `spafupload`, `SSN`, `pass_clr`, etc.)
- [ ] Agreement tool returns only `signed` or `missing`
- [ ] SPAF tool returns only `uploaded` or `missing`
- [ ] Disabled tools return policy denial when flags are off

### 3.4 Automated tests (CI / pre-deploy)

```bash
npm test
```

Covers connector catalog, redaction, binary status locks, and MCP tool registration.

---

## Staging vs production checklist

| Step | Staging | Production |
| ---- | ------- | ---------- |
| Apply `lucos_ro_*` views | Yes — first | After staging smoke |
| RO user + view grants | When going live on DB | Separate prod user/DSN |
| `MNGT_RO_DSN_*` | `MNGT_RO_DSN_STAGING` | `MNGT_RO_DSN_PROD` + `MNGT_RO_ALLOW_PROD=true` |
| `MNGT_RO_FIXTURE` | Dev only | Never |
| `LUCOS_BROKER_MOCK_ALLOW` | Can be true locally | `false` |
| Enable MNGT tools | Staged via env/JSON | Same, after prod DB ready |
| ChatGPT tunnel | Staging tunnel URL | Production tunnel URL |
| Audit / JWT (TW-325) | Staging gateway | Production gateway |

---

## Rollback

1. **Immediate:** `LUCOS_POLICY_ENABLED=false` or disable individual MNGT tool flags.
2. **App:** redeploy previous release / restart previous container image.
3. **Database:** views are `CREATE OR REPLACE` — rollback DDL only if DBA approves; dropping views breaks live tools until restored.

RO grants can remain; disabled tools do not query the database.

---

## What this repo does *not* deploy

| Item | Where it lives |
| ---- | -------------- |
| JWT auth, tool RBAC, audit inserts | `api.lucos.com` (TW-325) |
| ChatGPT Secure MCP Tunnel | Ops / tunnel infra |
| AdCenter keys and campaign tools | Separate AdCenter track (TW-301+) |
| Qdrant / `catalog_search` | TW-299–300 (optional for MNGT) |

---

## References

- Design pack: [`MNGT-RO-VIEWS.md`](./MNGT-RO-VIEWS.md)
- View DDL: [`catalogs/queries/mngt-ro-views-staging.sql`](../catalogs/queries/mngt-ro-views-staging.sql)
- Approved SELECT catalog: [`catalogs/queries/mngt-ro-queries.json`](../catalogs/queries/mngt-ro-queries.json)
- Connector: [`connectors/sql/`](../connectors/sql/)
- Tool handlers: [`connectors/mngt/`](../connectors/mngt/)
- Jira: [TW-309](https://admedia-jira.atlassian.net/browse/TW-309) · [TW-310](https://admedia-jira.atlassian.net/browse/TW-310) · [TW-311](https://admedia-jira.atlassian.net/browse/TW-311) · [TW-312](https://admedia-jira.atlassian.net/browse/TW-312) · [TW-313](https://admedia-jira.atlassian.net/browse/TW-313)