# Seated List Export

## Overview

Streamlit in Snowflake (Container Runtime) app that exports Seated fan opt-in data formatted for Fansifter CRM upload. Deployed on SME-DELPHI account.

## Architecture

- `app.py` — Streamlit UI with sidebar filters, preview, and CSV download
- `queries.py` — All SQL lives here. `export_query()` is the main transformation with email dedup via `ROW_NUMBER()`. Territory and location fields use venue data (`VENUE_COUNTRY`, `VENUE_CITY`, `VENUE_STATE`, `VENUE_POSTAL_CODE`)
- Source table: `FAN_LIVE_EVENTS.PROD.RAW_SEATED_OPT_INS`
- CRM destination: uploaded via Fansifter file upload, lands in `FANSIFTER_APP_REPORTING.PROD_CRM_FANS.EVENT_FILE_UPLOAD`

## Deployment

Must deploy as `FANSIFTER_ENGINEERING_PRIVACY` role (owns the Streamlit object; required for PII unmasking via `current_role()` check in masking policy).

Use `mcp__snowflake-delphi__run_snowflake_query` for deployment — the app is on SME-DELPHI, not Orchard.

Steps: PUT files to stage → CREATE OR REPLACE STREAMLIT. See README.md for exact commands.

## Key Decisions

- **Venue location for all address fields**: Territory, Country/Region, City, State, Postal Code all use venue data. Fan's home country (`COUNTRY_CODE`) is available but not used — venue location was chosen for consistency in the export.
- **Dedup strategy**: `ROW_NUMBER() OVER (PARTITION BY EMAIL)` preferring rows with more populated fields (name, phone, venue info), then most recent `INSERTED_AT`.
- **No dedicated app role**: `FANSIFTER_ENGINEERING_PRIVACY` is used directly because masking policy uses `current_role()` not role inheritance.
- **Compute pool**: `SEATED_LIST_EXPORT_POOL` (CPU_X64_XS, 1 node) — separate from other apps.

## Common Tasks

- **Redeploy after code changes**: PUT changed files to stage, then CREATE OR REPLACE STREAMLIT
- **Add new CRM columns**: Update `export_query()` in `queries.py`
- **Change location logic**: Modify the SELECT aliases in `export_query()` and the dedup ranking fields