# Seated List Export - Streamlit in Snowflake App

## Problem Statement

Seated fan opt-in data lands automatically in `FAN_LIVE_EVENTS.PROD.RAW_SEATED_OPT_INS` (SME-DELPHI) via the `swf-feed-ingestion` Jenkins pipeline, but getting it into the CRM (Fansifter) requires a manual file upload using a specific template format. Currently, CX Ops must manually export data, reformat columns, and import into CRM forms. This is error-prone and doesn't scale across artists.

Additionally, PII columns (Email, First Name, Last Name, Phone Number) are protected by masking policies (`SEATED_VARCHAR_MASK`). The Sigma connection role (`PROD_SIGMA_SERVICE_ROLE`) cannot unmask this data, making a Sigma-based export useless for CRM upload. A Streamlit app running under a role with unmasking privileges solves both the formatting and PII access problems.

## Data Source

- **Database**: `FAN_LIVE_EVENTS.PROD.RAW_SEATED_OPT_INS` (SME-DELPHI)
- **Row count**: ~3.98M total, ~24K for Slayyyter
- **Owner**: `JENKINS_CLI_ROLE`
- **Ingestion**: S3 (`s3://prod-orcdbucket/feed-drop/Seated/`) -> `swf-feed-ingestion` Jenkins job -> Snowflake

### Source Columns

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| ROW_UUID | VARCHAR(36) | No | Unique row identifier |
| OPTIN_ID | VARCHAR | No | Seated internal opt-in ID (UUID) |
| OPTIN_NAME | VARCHAR | No | Name of the opt-in in Seated |
| ARTIST_NAME | VARCHAR | No | Artist name |
| ARTIST_ID | VARCHAR | No | Seated internal artist ID (UUID) |
| USER_ID | VARCHAR | No | Seated internal user ID (UUID) |
| FIRST_NAME | VARCHAR | Yes | User first name (PII masked) |
| LAST_NAME | VARCHAR | Yes | User last name (PII masked) |
| EMAIL | VARCHAR | No | Email (PII masked, not populated for SMS opt-ins) |
| PHONE_NUMBER | VARCHAR | Yes | Phone (PII masked, not populated for email opt-ins) |
| VENUE | VARCHAR | Yes | Venue name (null for follower rows) |
| VENUE_CITY | VARCHAR | Yes | Venue city |
| VENUE_STATE | VARCHAR | Yes | Venue state/province |
| VENUE_COUNTRY | VARCHAR(2) | Yes | 2-letter country code |
| VENUE_POSTAL_CODE | VARCHAR | Yes | Venue postal code |
| EVENT_DATE | DATE | Yes | Event date (local to venue) |
| ORDER_QUANTITY | VARCHAR | Yes | Ticket quantity for order rows |
| SOURCE | VARCHAR | No | Activity type: reminder, order, waitlist, follower |
| COUNTRY_CODE | VARCHAR(2) | No | User's reporting country |
| INSERTED_AT | TIMESTAMP_TZ | No | When record was created in Seated (UTC) |
| REPORT_DATE | DATE | Yes | Report date |
| REPORT_FILENAME | VARCHAR | Yes | S3 report filename |
| LOAD_TIMESTAMP | TIMESTAMP_LTZ | Yes | When data was loaded into Snowflake |

### Masking Policies

- `FAN_LIVE_EVENTS.PROD.SEATED_VARCHAR_MASK` - applied to PII columns for general access
- `FAN_LIVE_EVENTS.PROD.SEATED_VARCHAR_MASK_ETL` - ETL-specific masking policy
- Unmasking requires `APPLY` privilege on these policies, which `PROD_ETL_SEATED_ROLE` has

## CRM Upload Template Format

The target format is the Fansifter "New File Upload Template" (`.xlsx`), with these columns:

| # | Column | Required | Source Mapping | Notes |
|---|--------|----------|---------------|-------|
| 1 | Email (Required) | Yes | EMAIL | Direct |
| 2 | Opt-in (Required) | Yes | Static: `TRUE` | All records are opt-ins |
| 3 | Mailing List | No | ARTIST_NAME | Artist name as mailing list |
| 4 | File Source Description | No | Static: `Seated List` | |
| 5 | Territory(2 digit ISO) (Required) | Yes | COALESCE(VENUE_COUNTRY, COUNTRY_CODE) | Venue country with fan country fallback |
| 6 | Label (Required) | Yes | User input | Label varies per artist |
| 7 | First Name | No | FIRST_NAME | PII - needs unmasking |
| 8 | Last Name | No | LAST_NAME | PII - needs unmasking |
| 9 | Mobile Phone | No | PHONE_NUMBER | PII - needs unmasking |
| 10 | Birthdate (MM/DD/YYYY) | No | NULL | Not in Seated data |
| 11 | Birthday (MM/DD) US ONLY | No | NULL | Not in Seated data |
| 12 | Gender | No | NULL | Not in Seated data |
| 13 | Address 1 | No | NULL | Not in Seated data |
| 14 | City | No | VENUE_CITY | Venue location, not fan |
| 15 | State | No | VENUE_STATE | Venue location, not fan |
| 16 | Country/Region | No | VENUE_COUNTRY | Venue location, not fan |
| 17 | Postal Code | No | VENUE_POSTAL_CODE | Venue location, not fan |
| 18 | Preferred Language | No | NULL | Not in Seated data |
| 19 | Facebook Page | No | NULL | Not in Seated data |
| 20 | Twitter Handle | No | NULL | Not in Seated data |

## Downstream Context

### How Seated data flows into the audience platform (dbt-audience)

Once uploaded to CRM, the data enters the dbt-audience pipeline as acquisition channel `SEATED_LIST`:

1. CRM forms (`form_c` / `form_response_c`) with `FORM_TOOL_NAME = 'seated list'`
2. `INT_ARTIST_EVENT_COLLECTION` (intermediate model)
3. CASE statement normalizes `'seated list'` -> `UPPER(REPLACE(...))` -> `SEATED_LIST`
4. Output models:
   - `FAN_ARTIST_ACQUISITION_CHANNEL_DBT`
   - `FAN_CUSTOM_LIST_ACQUISITION_CHANNEL_DBT`
   - `GLOBAL_FAN_ARTIST_ACQUISITION_CHANNEL_DBT`
   - `CRM_CAMPAIGN_ID_DBT`

Current state: ~1.1M fans total with SEATED_LIST channel. Uploads happen manually per artist (e.g., $uicideboy$ batch on 2026-04-15 = 44,815 fans). Slayyyter has 24K raw records but 0 in SEATED_LIST — not yet uploaded.

### Related repos
- `theorchard/swf-feed-ingestion` - Ingestion pipeline (`feed_ingestion/flows/seated/`)
- `theorchard/database` - DDL (`delphi/FAN_LIVE_EVENTS/build/changelog/ddl/DS-9839_seated.sql`)
- `theorchard/terraform-infra` - Roles and infra (`prod/snowflake/delphi/roles/`, `prod/swf-feed-ingestion/seated/`)
- `theorchard/dbt-audience` - Downstream models
- `theorchard/feed-status-monitor` - Feed monitoring (`seated_opt_ins` feed)

## Streamlit App Design

### Snowflake Role

The app needs a role that can:
- Read `FAN_LIVE_EVENTS.PROD.RAW_SEATED_OPT_INS`
- Unmask PII via `SEATED_VARCHAR_MASK` / `SEATED_VARCHAR_MASK_ETL`
- Use a warehouse

Options:
- **Use `PROD_ETL_SEATED_ROLE`** directly (already has all needed grants)
- **Create a new role** (e.g., `SEATED_EXPORT_APP_ROLE`) that inherits from `PROD_ETL_SEATED_ROLE` — better for least-privilege and audit trail

### App Features

1. **Artist Selector** - Dropdown populated from `SELECT DISTINCT ARTIST_NAME FROM RAW_SEATED_OPT_INS ORDER BY ARTIST_NAME`
2. **Date Range Filter** - Filter on `REPORT_DATE` (or `INSERTED_AT`)
3. **Source Filter** - Optional filter on SOURCE (reminder, order, waitlist, follower)
4. **Label Input** - Text input for the required Label field (per artist)
5. **Preview Table** - Show transformed data in template format with row count
6. **Download Button** - Export as `.csv` (or `.xlsx`) with exact template column headers including parentheses
7. **Row Count Summary** - Show total rows, breakdown by source type

### SQL Query (core transformation)

```sql
SELECT
    EMAIL                       AS "Email (Required)",
    'TRUE'                      AS "Opt-in (Required)",
    ARTIST_NAME                 AS "Mailing List",
    'Seated List'               AS "File Source Description",
    COUNTRY_CODE                AS "Territory(2 digit ISO) (Required)",
    :label_input                AS "Label (Required)",
    FIRST_NAME                  AS "First Name",
    LAST_NAME                   AS "Last Name",
    PHONE_NUMBER                AS "Mobile Phone",
    NULL                        AS "Birthdate (MM/DD/YYYY)",
    NULL                        AS "Birthday (MM/DD) US ONLY",
    NULL                        AS "Gender",
    NULL                        AS "Address 1",
    VENUE_CITY                  AS "City",
    VENUE_STATE                 AS "State",
    VENUE_COUNTRY               AS "Country/Region",
    VENUE_POSTAL_CODE           AS "Postal Code",
    NULL                        AS "Preferred Language",
    NULL                        AS "Facebook Page",
    NULL                        AS "Twitter Handle"
FROM FAN_LIVE_EVENTS.PROD.RAW_SEATED_OPT_INS
WHERE ARTIST_NAME = :artist_name
  AND REPORT_DATE BETWEEN :start_date AND :end_date
```

### Deployment

- Deploy to Snowflake as a Streamlit in Snowflake app (SiS)
- Database/schema TBD (likely `FAN_LIVE_EVENTS.PROD` or a dedicated app schema)
- Warehouse: `PROD_ETL_WH` (already granted to `PROD_ETL_SEATED_ROLE`)

## Open Questions

- [ ] Should we create a dedicated app role or reuse `PROD_ETL_SEATED_ROLE`?
- [ ] Which database/schema should the Streamlit app live in?
- [ ] Should venue address fields (City, State, Country, Postal Code) be included or left blank since they're venue locations not fan addresses?
- [ ] Should we support `.xlsx` export or is `.csv` sufficient for CRM upload?
- [ ] Who should have access to the app? Just CX Ops or broader?
- [ ] Should the app also show a summary of what's already been uploaded (SEATED_LIST in FAN_ARTIST_ACQUISITION_CHANNEL_DBT) vs. what's pending?

## Implementation Steps

- [ ] 1. Decide on role strategy (new role vs. reuse) and app location
- [ ] 2. Create Terraform PR for any new role/grants needed
- [ ] 3. Build Streamlit app locally
- [ ] 4. Test with masked/unmasked data
- [ ] 5. Deploy to Snowflake
- [ ] 6. Grant access to CX Ops users
- [ ] 7. Document the workflow for CX Ops handoff