# MNGT RO views — design pack (TW-309)

Engineering design pack for DBA: **six read-only SQL views** + RO grants so `lucos-business-mcp` can implement Phase-1 MNGT tools (TW-310–313) without base-table `SELECT`.

| Item | Value |
| ---- | ----- |
| Ticket | [TW-309](https://admedia-jira.atlassian.net/browse/TW-309) |
| Parent | [TW-288](https://admedia-jira.atlassian.net/browse/TW-288) |
| Harvest source | `catalogs/schemas/staging/admin.json` (env `staging`, schema **`Admin`**, harvested 2026-08-10) |
| Entity allow/deny | `catalogs/entities/*.json` |
| PHP reference only | `mngt.admedia.com` — **do not call** at runtime |

---

## Locked decisions

| Decision | Lock |
| -------- | ---- |
| Agreement tool status | `signed` \| `missing` only |
| SPAF tool status | `uploaded` \| `missing` only |
| RO grants | `SELECT` on these **views only** — not base tables / full schema |
| Staging first | Create + smoke in staging; prod later |
| No PHP auth changes | Connectors talk to DB views only |
| No generic SQL tool | Named MCP tools with fixed params |
| Numeric status maps | Return raw codes where unconfirmed; **do not invent** int→label maps |

Entity dictionary updated to match: `publisher_agreement.json`, `publisher_spaf.json`.

---

## View ↔ tool map

Proposed view names (DBA may rename with `lucos_ro_*` prefix preserved). All in schema **`Admin`** unless DBA confirms otherwise.

| # | View | Tool | Primary key param |
| - | ---- | ---- | ----------------- |
| 1 | `lucos_ro_advertiser` | `get_advertiser` | `adv_id` (int) |
| 2 | `lucos_ro_publisher` | `get_publisher`, `list_publishers_onboarded` (needs `onboarded_at`) | `publisher_id` (= `Affiliate.name` / `Affiliate_info.Affiliate`, varchar) |
| 3 | `lucos_ro_campaign_budget` | `get_campaign_budget`, `list_campaigns`, `list_campaigns_at_cap`, `list_advertisers_without_recent_campaigns` (join; needs `created_at`), `list_campaigns_capped_frequently` (join) | `campaign_id` and/or `adv_id` |
| 4 | `lucos_ro_publisher_caps` | `get_publisher_caps` | `publisher_id` (+ optional `campaign_id`) |
| 5 | `lucos_ro_publisher_agreement` | `get_publisher_agreement_status` | `publisher_id` |
| 6 | `lucos_ro_publisher_spaf` | `get_publisher_spaf_status` | `publisher_id` |
| 7 | `lucos_ro_traffic_channels` | `list_traffic_channels` | (catalog; no key) |
| 8 | `lucos_ro_campaign_product_targeting` | `list_campaigns_by_product_tag`, `search_campaigns_by_domain_targeting` | `traffic_id` (+ optional `adv_id` / `domain`) |
| 9 | `lucos_ro_domain_list_members` | (join partner for domain search) | `domain`, `list_id` |
| 10 | `lucos_ro_oos_site_mapping` | `search_oos_site_mapping` | `q` (serp_site substring) |
| 11 | `lucos_ro_campaign_daily_budget` | `list_campaigns_capped_frequently` (needs DBA CREATE) | `adv_id` + `stat_date` + `campaign_id` |

---

## Identity / join keys (from harvest + PHP)

| Concept | Column | Notes |
| ------- | ------ | ----- |
| Publisher id | `Affiliate.name` = `Affiliate_info.Affiliate` | PHP: `a.name = ai.Affiliate` (`affiliates_show_stats.php`) |
| Advertiser id | `Advertiser_info.adv_id` (PK, int) | Login/name: `Advertiser_info.Advertiser` (unique varchar) |
| Advertiser budget row | `Advertiser_budget.advertiser` | Matches **name** (`Advertiser_info.Advertiser`), not `adv_id` |
| Campaign | `campaign.id` PK; `campaign.advertiser_id` | PHP `budgetApi.php` filters `WHERE advertiser_id=…` |
| Cap report | `affiliate_cap_report.affiliate_id` | varchar; treat as publisher id string |
| Product tag | `traffic_channels.traffic_id` (e.g. `Search_31`) | Mapped via `campaign_product_type_mapping.prod_type` (varchar) = `traffic_channels.id` |
| Domain whitelist | `campaign_targeting.json` → `$.domain_lists.include` | List ids resolved against `domain_list_names` + `ads_domain_list` |
| OOS site | `serp_oos_site_mapping.serp_site` | No `adv_id` / `campaign_id` — not a campaign list |

`Admin.Advertiser` base table has **0 columns** in staging harvest — use `Advertiser_info` as the advertiser source.

---

## Global PII / deny (views must omit)

Exclude from **all** views (aligns handoff + entity `fieldDeny` + TW-298 sensitivity):

| Category | Columns (non-exhaustive) |
| -------- | ------------------------ |
| Tax / identity | `SSN`, `Tax_ID`, `w2upload`, `irs_form`, `form1099` |
| Payment | `paypal_email`, `paypal_status`, `payment_wire`, `check_address`, `check_payable`, `cc` |
| Contact PII | `email`, `telephone`, `ad_phone`, `fax`, `address1`, `address2`, `zip`, `slack_email`, `skype`, IM ids |
| Secrets / auth | `pass_clr`, `pass_hash`, `reset_token*`, `auth_code`, `auth_file`, `pub_aws_access_key`, `pub_aws_secret_key` |
| Contract / evidence bodies | `agreement` (**longtext**), `spafupload` (**path**), `docusign_envelop` (**id**), payment docs |
| Cap-report contact | `affiliate_cap_report.emaillist` |
| Tracking secrets | `campaign.pixel`, `pixel_conv`, `linktrust_pixel` |

Redaction defense-in-depth: omit in view (preferred) + MCP/gateway deny lists.

---

## 1. `lucos_ro_advertiser` → `get_advertiser`

### Sources

- `Admin.Advertiser_info` (required)
- Optional left join: `Admin.Advertiser_budget` ON `ab.advertiser = ai.Advertiser`

### Suggested projection

| View column | Source | Notes |
| ----------- | ------ | ----- |
| `adv_id` | `ai.adv_id` | Param / PK |
| `advertiser_name` | `ai.Advertiser` | |
| `company` | `ai.company` | |
| `alias` | `ai.alias` | |
| `status` | `ai.status` | tinyint — **map unconfirmed** |
| `trusted` | `ai.trusted` | tinyint — unconfirmed |
| `role` | `ai.role` | enum `superadmin\|admin\|viewer` |
| `website` | `ai.website` | |
| `sales_rep` | `ai.sales_rep` | |
| `account_type` | `ai.account_type` | |
| `admin_type` | `ai.admin_type` | |
| `currency` | `ai.currency` | |
| `daily_cap` | `ai.daily_cap` | |
| `budget_copy` | `ai.budget_copy` | |
| `credit_limit` | `ai.credit_limit` | |
| `auto_refill_budget` | `ai.auto_refill_budget` | |
| `refill_threshold` | `ai.refill_threshold` | |
| `country` / `state` / `city` | `ai.*` | geo labels OK; no street address |
| `advertiser_budget` | `ab.budget` | nullable if no budget row |
| `advertiser_budget_updated` | `ab.last_update` | |

### Tool params (connector)

- Required: `adv_id` int  
- Optional later: lookup by `advertiser_name` (not required for Phase 1)

### Sketch SQL

```sql
CREATE OR REPLACE VIEW Admin.lucos_ro_advertiser AS
SELECT
  ai.adv_id,
  ai.Advertiser AS advertiser_name,
  ai.company,
  ai.alias,
  ai.status,
  ai.trusted,
  ai.role,
  ai.website,
  ai.sales_rep,
  ai.account_type,
  ai.admin_type,
  ai.currency,
  ai.daily_cap,
  ai.budget_copy,
  ai.credit_limit,
  ai.auto_refill_budget,
  ai.refill_threshold,
  ai.country,
  ai.state,
  ai.city,
  ab.budget AS advertiser_budget,
  ab.last_update AS advertiser_budget_updated
FROM Admin.Advertiser_info ai
LEFT JOIN Admin.Advertiser_budget ab
  ON ab.advertiser = ai.Advertiser;
-- RO caller: SELECT … FROM lucos_ro_advertiser WHERE adv_id = ?
```

---

## 2. `lucos_ro_publisher` → `get_publisher`

TW-331 adds `onboarded_at` (`FROM_UNIXTIME(ai.timedate)`). Apply `CREATE OR REPLACE VIEW` before `list_publishers_onboarded` can filter on it. Existing tool SELECTs list columns explicitly and do not return `onboarded_at`. MNGT reports (`reports_export2qb_time.php`) treat `Affiliate_info.timedate` as affiliate create/onboard date — not later approval.

### Sources

- `Admin.Affiliate` `a`
- `Admin.Affiliate_info` `ai` ON `ai.Affiliate = a.name`
- Optional: `Admin.publisher_account_status` ON `pas.status_name = ai.account_status` (label only; join unconfirmed)

### Suggested projection

| View column | Source | Notes |
| ----------- | ------ | ----- |
| `publisher_id` | `a.name` / `ai.Affiliate` | Tool param |
| `company` | `ai.company` | |
| `domain` | `a.domain` | |
| `sitename` | `a.sitename` | |
| `status` | `a.status` | smallint — **map unconfirmed** |
| `approved` | `ai.approved` | int — conceptual gate; codes unconfirmed |
| `suspended` | `ai.suspended` | |
| `disapproved` | `ai.disapproved` | |
| `account_status` | `ai.account_status` | varchar |
| `account_state` | `ai.account_state` | |
| `cap_status` | `a.cap_status` | also used by caps tool |
| `click_cap` / `cap_count` / `clicks_per_day` | `a.*` | summary; detail in caps view |
| `sales_rep` | `ai.sales_rep` | |
| `account_manager` | `ai.account_manager` | |
| `account_secondary_manager` | `ai.account_secondary_manager` | |
| `account_sales_manager` | `ai.account_sales_manager` | |
| `traffic_type` / `traffic_geo` / `traffic_channel` | `ai.*` | |
| `campaign_eligibility` | `ai.campaign_eligibility` | |
| `country` / `state` / `city` | `ai.*` | no street |
| `onboarded_at` | `FROM_UNIXTIME(ai.timedate)` (TW-331). Unix int onboard/create timestamp. |

### Sketch SQL

```sql
CREATE OR REPLACE VIEW Admin.lucos_ro_publisher AS
SELECT
  a.name AS publisher_id,
  ai.company,
  a.domain,
  a.sitename,
  a.status,
  ai.approved,
  ai.suspended,
  ai.disapproved,
  ai.account_status,
  ai.account_state,
  a.cap_status,
  a.click_cap,
  a.cap_count,
  a.clicks_per_day,
  ai.sales_rep,
  ai.account_manager,
  ai.account_secondary_manager,
  ai.account_sales_manager,
  ai.traffic_type,
  ai.traffic_geo,
  ai.traffic_channel,
  ai.campaign_eligibility,
  ai.country,
  ai.state,
  ai.city,
  FROM_UNIXTIME(ai.timedate) AS onboarded_at
FROM Admin.Affiliate a
INNER JOIN Admin.Affiliate_info ai
  ON ai.Affiliate = a.name;
-- RO caller: WHERE publisher_id = ?
```

---

## 3. `lucos_ro_campaign_budget` → `get_campaign_budget`

TW-330 adds `created_at` (`c.creation`). Apply `CREATE OR REPLACE VIEW` before `list_advertisers_without_recent_campaigns` can filter on it. Existing tool SELECTs list columns explicitly and do not return `created_at`.

### Sources

- `Admin.campaign` `c`
- Left join `Admin.Advertiser_info` `ai` ON `ai.adv_id = c.advertiser_id`
- Left join `Admin.Advertiser_budget` `ab` ON `ab.advertiser = ai.Advertiser`

`Admin.adv_budget` (monthly / multi-campaign) is **out of Phase-1 view** unless AdOps asks; keep campaign + advertiser-level budget only.

### Suggested projection

| View column | Source |
| ----------- | ------ |
| `campaign_id` | `c.id` |
| `adv_id` | `c.advertiser_id` |
| `campaign_name` | `c.name` |
| `status` | `c.status` (char(3) — map unconfirmed) |
| `approved` | `c.approved` |
| `budget` | `c.budget` |
| `budget_spent` | `c.budget_spent` |
| `unlimited_budget` | `c.unlimited_budget` |
| `bid` | `c.bid` |
| `impression_cap` | `c.impression_cap` |
| `freq_cap` | `c.freq_cap` |
| `created_at` | `c.creation` (TW-330). Campaign create timestamp — not cap/status-change history. |
| `advertiser_name` | `ai.Advertiser` |
| `advertiser_daily_cap` | `ai.daily_cap` |
| `advertiser_budget` | `ab.budget` |
| `advertiser_budget_updated` | `ab.last_update` |

### Tool params

- Preferred: `campaign_id`  
- Or: `adv_id` → return all campaigns for advertiser (connector must enforce row limit)

### Sketch SQL

```sql
CREATE OR REPLACE VIEW Admin.lucos_ro_campaign_budget AS
SELECT
  c.id AS campaign_id,
  c.advertiser_id AS adv_id,
  c.name AS campaign_name,
  c.status,
  c.approved,
  c.budget,
  c.budget_spent,
  c.unlimited_budget,
  c.bid,
  c.impression_cap,
  c.freq_cap,
  c.creation AS created_at,
  ai.Advertiser AS advertiser_name,
  ai.daily_cap AS advertiser_daily_cap,
  ab.budget AS advertiser_budget,
  ab.last_update AS advertiser_budget_updated
FROM Admin.campaign c
LEFT JOIN Admin.Advertiser_info ai ON ai.adv_id = c.advertiser_id
LEFT JOIN Admin.Advertiser_budget ab ON ab.advertiser = ai.Advertiser;
```

PHP reference: `Advertisers/budgetApi.php` reads `campaign` by `advertiser_id` and joins `Advertiser_budget` by advertiser **name**.

---

## 4. `lucos_ro_publisher_caps` → `get_publisher_caps`

### Sources

Two shapes (recommend **one view with nullable cap-report columns**, or two views if DBA prefers):

**A. Publisher-level (always one row per publisher)** from `Affiliate`:

| Column | Source |
| ------ | ------ |
| `publisher_id` | `a.name` |
| `cap_status` | `a.cap_status` |
| `click_cap` | `a.click_cap` |
| `cap_count` | `a.cap_count` |
| `clicks_per_day` | `a.clicks_per_day` |

**B. Campaign-scoped rows** from `affiliate_cap_report`:

| Column | Source | Notes |
| ------ | ------ | ----- |
| `cap_report_id` | `acr.id` | |
| `publisher_id` | `acr.affiliate_id` | |
| `campaign_id` | `acr.campaign_id` | varchar in harvest |
| `advertiser_id` | `acr.advertiser_id` | varchar in harvest |
| `click_cap` | `acr.click_cap` | |
| `conversion_cap` | `acr.conversion_cap` | |
| `cap_type` | `acr.cap_type` | enum `Weekly\|Daily` |
| `status` | `acr.status` | tinyint unconfirmed |
| `created_at` | `acr.created_at` | |
| `advertiser_name` / `affiliate_name` | `acr.*` | OK |

**Deny:** `emaillist`, `added_by` (operator identity — optional deny).

### Suggested approach for Phase 1

Ship **`lucos_ro_publisher_caps`** as:

```sql
CREATE OR REPLACE VIEW Admin.lucos_ro_publisher_caps AS
SELECT
  a.name AS publisher_id,
  a.cap_status AS publisher_cap_status,
  a.click_cap AS publisher_click_cap,
  a.cap_count AS publisher_cap_count,
  a.clicks_per_day,
  acr.id AS cap_report_id,
  acr.campaign_id,
  acr.advertiser_id,
  acr.click_cap AS report_click_cap,
  acr.conversion_cap,
  acr.cap_type,
  acr.status AS report_status,
  acr.created_at,
  acr.advertiser_name,
  acr.affiliate_name
FROM Admin.Affiliate a
LEFT JOIN Admin.affiliate_cap_report acr
  ON acr.affiliate_id = a.name;
-- Caller: WHERE publisher_id = ? [AND campaign_id = ?]
```

If LEFT JOIN fan-out is undesirable, DBA may split into `lucos_ro_publisher_caps` + `lucos_ro_publisher_cap_reports`.

---

## 5. `lucos_ro_publisher_agreement` → `get_publisher_agreement_status`

### Phase-1 status lock

| `agreement_status` | Rule |
| ------------------ | ---- |
| `signed` | `NULLIF(TRIM(ai.agreement), '') IS NOT NULL` **OR** `NULLIF(TRIM(ai.docusign_envelop), '') IS NOT NULL` |
| `missing` | else |

Never project `agreement`, `docusign_envelop`, or file paths.

### Suggested projection

| View column | Source / expression |
| ----------- | ------------------- |
| `publisher_id` | `ai.Affiliate` |
| `company` | `ai.company` |
| `agreement_status` | CASE expression → `'signed'` \| `'missing'` |
| `approved` | `ai.approved` (context only) |
| `contract_form` | `ai.contract_form` (raw tinyint; map unconfirmed) |

### Sketch SQL

```sql
CREATE OR REPLACE VIEW Admin.lucos_ro_publisher_agreement AS
SELECT
  ai.Affiliate AS publisher_id,
  ai.company,
  CASE
    WHEN NULLIF(TRIM(ai.agreement), '') IS NOT NULL
      OR NULLIF(TRIM(ai.docusign_envelop), '') IS NOT NULL
    THEN 'signed'
    ELSE 'missing'
  END AS agreement_status,
  ai.approved,
  ai.contract_form
FROM Admin.Affiliate_info ai;
```

### PHP reference (logic only)

- Uploads append paths into `Affiliate_info.agreement` (comma-separated); delete removes a path (`affiliates_show_stats.php` / `affiliates_manage_new.php`).
- SPAF/agreement files land under `/download/agreement/` — **not** readable via Lucos.
- `onboarding_checklist.is_agreement` is a **separate ops checklist** — **not** Phase-1 source of truth.

---

## 6. `lucos_ro_publisher_spaf` → `get_publisher_spaf_status`

### Phase-1 status lock

| `spaf_status` | Rule |
| ------------- | ---- |
| `uploaded` | `NULLIF(TRIM(ai.spafupload), '') IS NOT NULL` |
| `missing` | else |

Never project `spafupload` path. Upload ≠ traffic approval (document in tool description).

### Suggested projection

| View column | Source |
| ----------- | ------ |
| `publisher_id` | `ai.Affiliate` |
| `company` | `ai.company` |
| `spaf_status` | CASE → `'uploaded'` \| `'missing'` |
| `traffic_type` / `traffic_type_other` / `traffic_geo` / `traffic_source` / `traffic_channel` / `verticles` | `ai.*` |
| `campaign_eligibility` | `ai.campaign_eligibility` |
| `approved` | `ai.approved` |

### Sketch SQL

```sql
CREATE OR REPLACE VIEW Admin.lucos_ro_publisher_spaf AS
SELECT
  ai.Affiliate AS publisher_id,
  ai.company,
  CASE
    WHEN NULLIF(TRIM(ai.spafupload), '') IS NOT NULL THEN 'uploaded'
    ELSE 'missing'
  END AS spaf_status,
  ai.traffic_type,
  ai.traffic_type_other,
  ai.traffic_geo,
  ai.traffic_source,
  ai.traffic_channel,
  ai.verticles,
  ai.campaign_eligibility,
  ai.approved
FROM Admin.Affiliate_info ai;
```

### PHP reference

- SPAF upload sets `Affiliate_info.spafupload = <filename>` (`affiliates_show_stats.php` ~451; manage UI downloads via `/download/agreement/<spafupload>`).
- UI treats `!empty(spafupload)` as “has document” — matches Phase-1 `uploaded`.

---

## RO grants (acceptance shape)

```sql
-- names illustrative — DBA chooses final username
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'@'%';
-- Explicitly NO grants on Affiliate, Affiliate_info, Advertiser_info, campaign, …
```

Acceptance (from TW-309):

- [ ] Staging views queryable as RO user
- [ ] RO user cannot write / cannot read non-granted tables
- [ ] PII columns absent from view defs
- [ ] DDL stored in controlled DBA location (link back here)
- [ ] Fixture IDs listed (see below)

---

## Open DBA / AdOps questions

1. Confirm staging + prod schema name remains **`Admin`**.
2. One shared Lucos RO user vs per-domain users.
3. Preferred view naming / DDL repo path.
4. Statement timeout / max rows for RO session (PDF mentions limits; not in ticket).
5. Whether `lucos_ro_publisher_caps` should be split (publisher summary vs cap-report rows).
6. AdOps: staging fixture **publisher_id**, **adv_id**, **campaign_id** → write `docs/fixtures/mngt-staging.json` (placeholder below).

---

## Fixtures (placeholder)

AdOps to fill — **no live PII**. Mirror pattern of `docs/fixtures/adcenter-staging.json`.

```json
{
  "environment": "staging",
  "ticket": "TW-309",
  "schema": "Admin",
  "synthetic": {
    "publisher_id": null,
    "adv_id": null,
    "campaign_id": null,
    "notes": "Request from AdOps: one staging publisher with known agreement_status + spaf_status, one advertiser + campaign with budget rows"
  },
  "expected_gates": {
    "agreement_status": ["signed", "missing"],
    "spaf_status": ["uploaded", "missing"]
  }
}
```

Create file when IDs arrive: `docs/fixtures/mngt-staging.json`.

---

## PHP → SQL mapping notes (reference only)

| MNGT PHP | What it does | Lucos mapping |
| -------- | ------------ | ------------- |
| `Affiliates/affiliates_show_stats.php` | Loads affiliate profile via `Affiliate` ⋈ `Affiliate_info` on `a.name = ai.Affiliate`; reads caps, contract_form, agreement, spafupload | `lucos_ro_publisher`, `_agreement`, `_spaf`, `_caps` |
| `Affiliates/affiliates_manage_new.php` | Same entity fields; SPAF download UI | Same; never expose paths |
| `Affiliates/onboarded_checklist_logs.php` | `onboarding_checklist.is_agreement` | **Out of scope** for Phase-1 status |
| `Advertisers/budgetApi.php` | `SELECT … FROM campaign WHERE advertiser_id=…`; joins `Advertiser_budget` by name | `lucos_ro_campaign_budget` |

Do **not** replicate write paths (`UPDATE Affiliate_info SET agreement/spafupload…`).

---

## Downstream (do not implement entity tools in TW-309)

| Ticket | Work |
| ------ | ---- |
| TW-310 | RO SQL connector + catalog entries — **done in-repo** (`connectors/sql/`, `catalogs/queries/mngt-ro-queries.json`, `catalogs/views/mngt-ro-views.json`) |
| TW-311 | `get_advertiser` + `get_publisher` |
| TW-312 | `get_campaign_budget` + `get_publisher_caps` |
| TW-313 | `get_publisher_agreement_status` + `get_publisher_spaf_status` |
| TW-328+ | Product-tag / domain targeting: `list_traffic_channels`, `list_campaigns_by_product_tag`, `search_campaigns_by_domain_targeting`, `search_oos_site_mapping` |

---

## 7–10. Product-tag / domain targeting views (MySQL 5.7)

Admin is **MySQL 5.7.44**. Use `JSON_EXTRACT` / `JSON_CONTAINS` only — **no `JSON_TABLE`**.

### 7. `lucos_ro_traffic_channels` → `list_traffic_channels`

| View column | Source |
| ----------- | ------ |
| `prod_type` | `traffic_channels.id` |
| `traffic_id` | e.g. `Search_31` |
| `traffic_name` | e.g. Contextual JS Banner Publisher |
| `status` | raw int |

### 8. `lucos_ro_campaign_product_targeting` → tag + domain tools

Sources: `campaign` ⋈ `campaign_product_type_mapping` ⋈ `traffic_channels` LEFT JOIN `campaign_targeting` LEFT JOIN `Advertiser_info`.

| View column | Notes |
| ----------- | ----- |
| `campaign_id`, `adv_id`, `advertiser_name`, `campaign_name`, `status`, `approved` | Campaign identity |
| `prod_type`, `traffic_id`, `traffic_name` | Product tag |
| `include_list_ids` | `JSON_EXTRACT(json, '$.domain_lists.include')` — **never return wholesale `json`** |
| `exclude_list_ids` | same for `.exclude` |

Never project `campaign.pixel` / `pixel_conv` / full targeting longtext.

### 9. `lucos_ro_domain_list_members`

`domain_list_names` ⋈ `ads_domain_list` → `list_id`, `list_name`, `adv_id` (`aid`), `domain`.

### 10. `lucos_ro_oos_site_mapping` → `search_oos_site_mapping`

`serp_oos_site_mapping` → `id`, `serp_site`, `rep_cms_site_id`, `theme`, `status`. Omit `site_logo` / `site_favicon`.

### DBA smoke (after CREATE + GRANT)

```sql
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;

SELECT id, serp_site FROM Admin.lucos_ro_oos_site_mapping
WHERE serp_site LIKE '%insuranceandleisure%' LIMIT 20;
```

---

## Refs

- [TW-309](https://admedia-jira.atlassian.net/browse/TW-309) · [TW-288](https://admedia-jira.atlassian.net/browse/TW-288) · [TW-327](https://admedia-jira.atlassian.net/browse/TW-327)
- [MNGT Documentation Index](https://admedia-jira.atlassian.net/wiki/spaces/EN/pages/633372693/MNGT+Documentation+Index)
- [MNGT Affiliates Documentation Index](https://admedia-jira.atlassian.net/wiki/spaces/EN/pages/634159113/MNGT+Affiliates+Documentation+Index)
- [Publisher Onboarding and Launch SOP](https://admedia-jira.atlassian.net/wiki/spaces/PM1/pages/758743051/Publisher+Onboarding+and+Launch+SOP)
- Handoff: `docs/HANDOFF-TW-309.md`