# AGENTS.md - Fansifter Backoffice App

## Project Overview

**Application Type**: Streamlit multi-page application  
**Purpose**: Collection of Streamlit apps to handle Fansifter/CRM-related background activities  
**Application Family**: Fansifter  
**Team**: @theorchard/fansifter

---

## Technology Stack

| Component | Technology | Version |
|-----------|------------|---------|
| Runtime | Python | 3.11.x (>=3.11, <3.12) |
| UI Framework | Streamlit | >=1.53.1 |
| Data Platform | Snowflake | - |
| Snowflake SDK | snowflake-snowpark-python | 1.45.0 |
| Data Validation | Pydantic | (implicit via snowflake) |
| Email Validation | email-validator | >=2.0.0 |
| Package Manager | UV | (primary) / Poetry (legacy) |
| Linting | Ruff | >=0.15.0 |
| Type Checking | MyPy | >=1.19.1 (strict mode) |
| Testing | Pytest | >=9.0.2 |

---

## Directory Structure

```
streamlit-fansifter-backoffice-app/
├── app/                           # Main application code
│   ├── main.py                    # Streamlit entry point
│   ├── pages/                     # Streamlit pages (multi-page navigation)
│   │   ├── app_fan_profile_lookup.py
│   │   ├── app_ccpa_file_upload.py
│   │   └── app_fileupload.py      # Subscription file upload (tabs: Upload/Delete/Status)
│   ├── backend/                   # Business logic layer
│   │   ├── actions/               # Use-case handlers (orchestration)
│   │   │   ├── fan_lookup.py      # DSAR lookup entry point
│   │   │   ├── dsar_report.py     # DSAR report building (DataFrames, Excel)
│   │   │   ├── ccpa_upload.py
│   │   │   └── file_upload.py     # Subscription file upload/delete/status
│   │   ├── adapters/              # External service integrations
│   │   │   └── snowflake.py       # Snowflake session + resolve_env()
│   │   ├── dtos.py                # Data Transfer Objects (Pydantic models)
│   │   └── queries.py             # Database query functions
│   ├── configuration/             # Configuration management
│   │   ├── local_connection.py    # Local Snowflake connection helper
│   │   ├── connections.json       # Local connection params (gitignored)
│   │   └── connections.json.shadow # Template for connections.json
│   ├── environment.yml            # Snowflake/Anaconda dependencies
│   └── snowflake.yml              # Snowflake CLI deployment config
├── objectives/                    # New app specifications (for AI agents)
├── tests/                         # Test suite
│   ├── conftest.py                # Pytest fixtures (session management)
│   └── unit/                      # Unit tests
│       ├── test_page_helpers.py   # Tests for dsar_report helpers
│       └── backend/
│           ├── actions/
│           │   ├── test_ccpa_upload.py
│           │   └── test_fan_lookup.py
│           ├── adapters/
│           │   └── test_snowflake.py
│           ├── test_permissions.py
│           └── test_queries.py
├── .githooks/
│   └── pre-push                   # Runs fmt + lint before push
├── pyproject.toml                 # Project metadata & tool configs
├── uv.lock                        # UV lockfile
├── Makefile                       # Development automation
└── README.md                      # Project documentation
```

---

## Architecture & Design Patterns

### Layered Architecture

The backend follows a clean layered architecture:

```
┌─────────────────────────────────────┐
│            pages/ (UI)              │  Streamlit pages - presentation layer
├─────────────────────────────────────┤
│         backend/actions/            │  Use-case orchestration layer
├─────────────────────────────────────┤
│         backend/queries.py          │  Data access layer
├─────────────────────────────────────┤
│         backend/adapters/           │  External service adapters
└─────────────────────────────────────┘
```

### Key Design Decisions

1. **Separation of Concerns**:
   - `pages/` - UI logic only, imports from backend
   - `actions/` - Business logic orchestration
   - `queries.py` - Pure data access functions
   - `adapters/` - External service integrations (Snowflake)

2. **Session Management Pattern** (`adapters/snowflake.py`):
   - Attempts `get_active_session()` first (for deployed Streamlit in Snowflake)
   - Falls back to local connection parameters (for local development)
   - This dual-mode pattern enables same code to run locally and in Snowflake
   - `resolve_env(session)` maps the current schema to `"PROD"` or `"QA"`. Any non-PROD schema (including personal dev schemas like `DEV_RBOMBERG`) resolves to `"QA"` to avoid missing-schema errors locally.

3. **DTO Pattern** (`dtos.py`):
   - Pydantic BaseModel classes for data validation
   - Type-safe data transfer between layers

---

## Snowflake Integration

### Connection Modes

| Environment | Connection Method |
|-------------|-------------------|
| Deployed (Snowflake SiS) | `get_active_session()` - Snowflake manages connection |
| Local Development | `Session.builder.configs()` via `connections.json` |
| Testing (Local) | `Session.builder.config("local_testing", True)` |
| Testing (Live) | Uses `connections.json` credentials |

### Database Schema Convention

- Database: `PREFERENCE_CENTER`
- Schema: Environment-based (e.g., `QA`, `DEV_<username>`)
- Table pattern: `PREFERENCE_CENTER.{env}.FAN_PROFILE`
- The `app_fileupload.py` page is the one exception to the `FANSIFTER_APP_REPORTING.{env}` pattern:
  it uses the `{env}_CRM_FANS` schema (i.e. `QA_CRM_FANS`/`PROD_CRM_FANS`, built by
  `queries._crm_fans_schema()`), inherited from the older `streamlit-fansifter-fileupload-app` it
  replicates. It reads/writes `FANSIFTER_APP_REPORTING.{env}_CRM_FANS.EVENT_FILE_UPLOAD` (table),
  calls `FANSIFTER_APP_REPORTING.{env}_CRM_FANS.DELETE_FILE_UPLOAD` (procedure), reads
  `FANSIFTER_APP_REPORTING.{env}_CRM_FANS.EVENT_FANRESPONSE_VALIDATED`/`EVENT_FANRESPONSE_ERROR`
  (status tables), and calls `FANSIFTER_APP_REPORTING.{env}_CRM_FANS.COUNTRY_TO_ISO2` (UDF). Its
  dropdown filter query intentionally hardcodes `.prod.` schemas for shared reference tables
  (`artist_roster_main_rep`, `artist_roster_local_rep`, `facts.prod.vendor`,
  `facts.prod.global_participant`, `PREFERENCE_CENTER.PROD.FAN_MAILING_LIST`), matching the
  existing precedent in `query_fan_exports`.

### Snowpark Query Pattern

All queries in `queries.py` use raw `session.sql()` with f-strings. Snowpark DataFrame API is **not** used because cross-database joins and SQL functions like `sha2()` and `lower()` require it.

```python
# Standard pattern used in queries.py
escaped = email.lower().replace("'", "''")  # always lowercase + escape user input
results = session.sql(
    f"SELECT COL1, COL2 FROM DATABASE.{env}.TABLE "
    f"WHERE lower(EMAIL) = lower('{escaped}')"
).collect()
```

Emails must be lowercased before interpolation (Fansifter stores them lowercase; Salesforce is case-insensitive via `lower()` in SQL).

---

## Code Conventions

### Naming Conventions

| Element | Convention | Example |
|---------|------------|---------|
| Files | snake_case | `fan_lookup.py` |
| Page files | `app_` prefix | `app_fan_profile_lookup.py` |
| Functions | snake_case | `find_fan_profile()` |
| Classes | PascalCase | `Profile` |
| Constants | UPPER_SNAKE | `GET_SCHEMA` |
| Snowflake columns | UPPER_SNAKE | `EMAIL`, `FIRST_NAME` |

### Import Organization

```python
# Standard library
from typing import Optional

# Third-party
from snowflake.snowpark import Session
from pydantic import BaseModel

# Local - relative imports from app root
from backend.dtos import Profile
from backend.queries import query_fan_profile
```

### Type Hints

- **Strict MyPy** is enforced (`strict = true` in pyproject.toml)
- All functions must have type hints
- Use `Optional[T]` or `T | None` for nullable types
- Pydantic models define schema with type hints

---

## Testing

### Test Configuration

```bash
# Run with local Snowflake mock (no credentials needed)
uv run pytest tests/unit/ --snowflake-session=local

# Run with live Snowflake connection
uv run pytest tests/unit/ --snowflake-session=live
```

### Testing Pattern

Tests in `test_queries.py` use `MagicMock` sessions because `session.sql()` is not supported in Snowflake's local testing framework. A helper sets up sequential `collect()` return values:

```python
def _mock_session(*sql_return_sequences):
    session = MagicMock()
    session.sql.return_value.collect.side_effect = list(sql_return_sequences)
    return session

def test_example():
    row = Row(EMAIL="fan@example.com", FIRST_NAME="Jane")
    session = _mock_session([], [row])  # first sql call → [], second → [row]
    result = query_fan_profile(session, "fan@example.com")
    assert result.first_name == "Jane"
```

Tests for action/helper functions (e.g. `test_page_helpers.py`) import directly from `backend.actions.dsar_report` and test logic in isolation without a session.

### Test Directory Structure

```
tests/
├── conftest.py              # Shared fixtures (session)
└── unit/
    ├── test_page_helpers.py # Tests for dsar_report helpers (profile_rows, build_excel)
    └── backend/
        ├── actions/
        │   ├── test_ccpa_upload.py
        │   └── test_fan_lookup.py
        ├── adapters/
        │   └── test_snowflake.py
        ├── test_permissions.py
        └── test_queries.py
```

---

## Development Commands

### Makefile Commands

| Command | Description |
|---------|-------------|
| `make lint` | Run mypy, ruff check, and ruff format check |
| `make fmt` | Auto-fix linting issues and format code |
| `make test` | Run unit tests with local Snowflake session |
| `make run_streamlit_locally` | Start Streamlit app locally |
| `make deploy_dev_streamlit_app` | Deploy to personal dev schema |
| `make drop_dev_streamlit_app` | Remove deployed app and stage |
| `make qa_deploy_streamlit_app` | Deploy to QA schema (uses `FANSIFTER_ENGINEERING_PRIVACY` role) |

### Manual Commands

```bash
# Type checking
uv run mypy app

# Linting
uv run ruff check
uv run ruff check --fix

# Formatting
uv run ruff format
uv run ruff format --check

# Deploy via Snowflake CLI
cd app && uv run snow streamlit deploy --replace --connection=streamlit_backoffice_connection
```

> **Note**: The Makefile uses `poetry run` for `run_streamlit_locally` (legacy), but `uv run` elsewhere. Both work if you have the respective tool configured.

### Git Hooks

A pre-push hook lives in `.githooks/pre-push`. Activate it once per clone:

```bash
git config core.hooksPath .githooks
```

The hook runs `make fmt`, fails if the formatter modified files (requiring you to stage and re-push), then runs `make lint`.

---

## Configuration Files

### pyproject.toml

- **MyPy**: Strict mode, Python 3.11, Pydantic plugin enabled
- **Pytest**: pythonpath includes `app/`, testpaths is `tests/`
- **UV**: Default groups include `dev` dependencies

### environment.yml (Snowflake Deployment)

Dependencies for Snowflake's Anaconda channel (pip packages NOT supported):
- streamlit
- snowflake-snowpark-python
- cryptography
- pydantic
- email-validator
- pandas
- openpyxl

### snowflake.yml (Snowflake CLI)

Defines the Streamlit app entity for deployment:
- Main file: `main.py`
- Pages directory: `pages/`
- Stage: `fansifter_backoffice_app_stage`

---

## Environment & Secrets

### Local Development Setup

1. Copy `connections.json.shadow` to `connections.json`
2. Fill in your Snowflake credentials:
   - `account`: Snowflake account identifier
   - `user`: Your Snowflake username
   - `role`: Your assigned role
   - `warehouse`: Development warehouse
   - `database`: Target database
   - `schema`: Your dev schema (e.g., `DEV_<username>`)
   - `private_file`: Path to your RSA private key
   - `password`: Private key passphrase

### Security Notes

- `connections.json` is gitignored - never commit credentials
- Uses RSA key-pair authentication (not password)
- Private key files should be stored in `~/.ssh/snowflake/`

---

## Deployment

### Environments

| Environment | Deployment Method |
|-------------|-------------------|
| Local Dev | `make run_streamlit_locally` |
| Personal Dev | `make deploy_dev_streamlit_app` (deploys to your schema) |
| QA | TBD (see Makefile TODO) |
| Production | Jenkins pipeline |

### Deployment Artifacts

The following are bundled when deploying to Snowflake:
- `main.py`
- `environment.yml`
- `pages/` directory
- `backend/` directory

---

## Adding New Features

### Adding a New Page

1. Create `app/pages/app_<feature_name>.py`
2. Follow existing page pattern:
   ```python
   import streamlit as st
   from backend.adapters.snowflake import get_session
   from backend.actions.<feature> import <action_function>
   
   st.title("Page Title")
   # UI code...
   session = get_session()
   result = <action_function>(session, args)
   ```

### Adding a New Backend Action

1. Create action in `app/backend/actions/<action_name>.py`
2. Create DTOs in `app/backend/dtos.py` if needed
3. Create queries in `app/backend/queries.py`
4. Add corresponding tests in `tests/unit/backend/`

### Query Function Pattern

```python
def query_<entity>(session: Session, email: str, env: str = "QA") -> Optional[DTO]:
    escaped = email.lower().replace("'", "''")
    results = session.sql(
        f"SELECT COL1, COL2 FROM DATABASE.{env}.TABLE "
        f"WHERE lower(EMAIL) = lower('{escaped}')"
    ).collect()
    if not results:
        return None
    row = results[0]
    return DTO(field1=row["COL1"], field2=row["COL2"])
```

For queries that span multiple external data sources (e.g. `query_fan_exports`), wrap each source in a try/except and return `(results, warnings)` so partial failures surface as UI warnings rather than silent empty results.

---

## Common Issues & Solutions

### Issue: MyPy can't find Snowflake modules
**Solution**: Snowflake modules are in `[[tool.mypy.overrides]]` with `ignore_missing_imports = true`

### Issue: Tests fail with connection error
**Solution**: Ensure you're using `--snowflake-session=local` flag for unit tests

### Issue: Streamlit can't find backend modules
**Solution**: Run from `app/` directory or ensure `app/` is in PYTHONPATH

### Issue: Deployment fails
**Solution**: Check `snowflake.yml` configuration and ensure Snowflake CLI connection is configured

### Issue: "Unsupported statement type 'temporary VIEW'" in deployed app
**Solution**: Streamlit in Snowflake (SiS) does not support creating temporary views or tables. Use inline `VALUES` in SQL instead:
```python
# DON'T do this in SiS:
source_df.create_or_replace_temp_view("MY_VIEW")
session.sql("SELECT * FROM MY_VIEW")

# DO this instead:
values_list = ", ".join([f"('{val}')" for val in data])
session.sql(f"SELECT column1 FROM VALUES {values_list}")
```

### Issue: "Unsupported Anaconda feature" in deployed app
**Solution**: The `environment.yml` only supports packages from Snowflake's Anaconda channel. Do NOT use `pip:` section or `conda-forge` channel.

### Issue: Export History tab shows data from one source but not another in PROD
**Solution**: The PROD Streamlit role (`FANSIFTER_STREAMLIT_DEPLOYMENT_ROLE`) may not have SELECT access to the missing source's database. `query_fan_exports` catches per-source errors and surfaces them as `st.warning()` banners in the Export History tab — check the warning message for the exact error. Fix by granting the required database role in Terraform.

### Issue: `st.user` has no attribute `user_name` when running locally
**Solution**: `permissions.py` wraps `st.user.user_name` in a try/except and falls back to `session.get_current_user()`. This is intentional — `st.user` is only available in Snowflake-hosted Streamlit.

### Issue: `Schema 'PREFERENCE_CENTER.DEV_RBOMBERG' does not exist` when running locally
**Solution**: `resolve_env()` in `adapters/snowflake.py` maps any non-PROD schema (including personal dev schemas) to `"QA"`. Always pass `resolve_env(session)` as the `env` argument when calling query functions.

### Issue: `TypeError: Excel does not support timezones in datetimes`
**Solution**: openpyxl requires timezone-naive datetimes. Use the `_naive()` helper in `dsar_report.py` to strip tzinfo before writing to DataFrames or Excel sheets.

---

## Gitignore Summary

Key entries in `.gitignore`:
- `app/configuration/connections.json` - Local credentials (use `.shadow` as template)
- `.venv/`, `venv/`, `.env` - Virtual environments
- `__pycache__/` - Python bytecode
- `.idea/` - IDE settings
- `app.zip`, `/app/output/` - Build artifacts

---

## Maintaining This File

**When to update AGENTS.md:**
- Adding new directories or changing project structure
- Introducing new design patterns or conventions
- Adding new dependencies or tools
- Changing database schemas or table naming conventions
- Modifying deployment processes or environments
- Adding new Makefile commands
- Discovering new common issues and solutions

**What to update:**
| Change Type | Sections to Update |
|-------------|-------------------|
| New directory/module | Directory Structure, Architecture |
| New dependency | Technology Stack |
| New pattern/convention | Code Conventions, Architecture |
| New Snowflake table/schema | Snowflake Integration |
| New test pattern | Testing |
| New Make command | Development Commands |
| New config file | Configuration Files |
| New env variable | Environment & Secrets |

**Keep it accurate** - outdated documentation is worse than no documentation. When making significant changes, verify this file still reflects reality.