# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

---

## 🚨 CRITICAL: Session Workflow & Tracking

### Use Notion for Project Tracking

**IMPORTANT:** Always use Notion to track project progress and tasks throughout our sessions.

**Notion Board:** https://www.notion.so/2fe87274f6d74b4ca587fc45e9be2398

The project uses a Kanban-style board organized by:
- **Status**: To Do, In Progress, Done, Blocked
- **Phase**: Setup, Core Infrastructure, Spotify Auth, Data Fetching, Streamlit UI, Testing
- **Priority**: High, Medium, Low

**Workflow:**
1. At the start of each session, review the Notion board to understand current state
2. Update task status as work progresses (move to "In Progress", "Done", etc.)
3. Create new tasks for discovered work
4. Document blockers and add context to task cards
5. Use comments on cards to track decisions and discoveries

### Git Workflow (Fork-Based Development)

**CRITICAL:** This is a fork of an upstream repository. Always sync before starting work and before creating PRs.

**Repository Structure:**
- **Upstream**: `theorchard/collab` (main repository)
- **Fork**: `mymac80/collab` (your personal fork)
- **Remotes**: `origin` → your fork, `upstream` → main repo

**Before Starting New Work:**
```bash
git fetch upstream
git checkout master
git rebase upstream/master
git push origin master
git checkout -b feature/your-feature-name
```

**Before Creating a Pull Request:**
```bash
git fetch upstream
git rebase upstream/master
git push origin feature/your-feature-name --force-with-lease
```

**Creating the PR:**
```bash
gh pr create --repo theorchard/collab \
  --title "Your PR title" \
  --body "PR description"
```

**Why This Matters:**
- Prevents merge conflicts by rebasing against upstream
- Ensures clean, linear commit history
- PR shows correct diff against upstream master

### Development Best Practices

**ALWAYS:**
- Check Notion board at session start
- Sync with upstream before starting work
- Update Notion as you complete tasks
- Rebase against upstream before creating PRs
- Document decisions and blockers in Notion

**NEVER:**
- Skip syncing with upstream before work
- Create PRs without rebasing against upstream/master
- Forget to update Notion task status

---

## Project Overview

This is a POC for identifying "Heavy Rotation" listeners using Spotify API data. The goal is to determine which fans have a specific artist appearing in their Spotify Top Artists list, based on listening affinity calculated from total plays, recency, and listening patterns over configurable time ranges (4 weeks, 6 months, or 1 year).

**Business Context:** Part of the "Listening Behavior Collector" initiative to analyze listening behavior across ~73M fans. This POC validates the technical approach with a small dataset (5 artists × 10,000 fans) before building the full-scale production system.

**Current Focus:** Implementing the Streamlit app in `streamlit-test/` using Snowflake Streamlit Container Runtime.

**Future Architecture:** The POC currently uses Node.js/TypeScript for API testing. The production system will be built entirely in Snowflake using Python UDFs, Serverless Tasks, and Streamlit for the UI.

---

## Quick Reference: Test Environments

### `api-test/` - Local Development Tests
```bash
cd api-test
npm install              # Install dependencies
npm run auth            # Generate OAuth tokens (opens browser)
npm test                # Run single-user test
npm run test:multi      # Run multi-user test (5 fans from .env)
```

### `dynamo-test/` - Production Data Tests
```bash
cd dynamo-test
npm install              # Install dependencies
awsume prod             # Authenticate with MFA
awsume songwhip -o default  # Assume songwhip role
npm test                # Run test with 50 fans from DynamoDB
```

### `streamlit-test/` - Snowflake Streamlit App (Current Focus)
- Uses Snowflake Streamlit Container Runtime (compute pool-based)
- Integrates with Spotify API using refresh tokens from DynamoDB
- **Wave-based parallel processing** for batch API calls (~2.3x speedup)
- **Phase 1: API Integration POC** - ✅ Complete
- **Phase 2: Database Integration** - 🚧 In Progress (tracked in Notion)
- See [streamlit-test/plan.md](streamlit-test/plan.md) for Phase 1 architecture reference
- See [Notion Board](https://www.notion.so/2fe87274f6d74b4ca587fc45e9be2398) for Phase 2 tasks and current status

---

## Documentation Structure

### Project Planning & Tracking
- **[Notion Board](https://www.notion.so/2fe87274f6d74b4ca587fc45e9be2398)** - 🎯 **PRIMARY SOURCE** for Phase 2 tasks, status, and planning
- **CLAUDE.md** (this file) - Context and workflow guidance for Claude Code sessions

### Architecture & Implementation Reference
- **streamlit-test/plan.md** - Phase 1 architecture and implementation patterns
- **streamlit-test/README.md** - Setup instructions and usage guide
- **docs/poc-plan.md** - POC objectives, scope, and Snowflake architecture overview
- **docs/implementation.md** - Detailed Snowflake implementation (tables, UDFs, stored procedures)
- **docs/spotify-api-test-plan.md** - Original API test planning document

### Component Documentation
- **dynamo-test/README.md** - DynamoDB test setup and usage guide
- **dynamo-test/PLAN.md** - Implementation plan for DynamoDB test

---

## Snowflake MCP Tool Usage

### Role Permissions

**CRITICAL:** When using Snowflake MCP tools (`mcp__snowflake__*`), always follow these role guidelines:

**Default Role:** `FANSIFTER_ENGINEERING`
- Use this role for all standard operations
- Table creation, data queries, Streamlit app management
- No permission required from user

**Privileged Role:** `ACCOUNTADMIN`
- **⚠️ ALWAYS ASK USER PERMISSION before using ACCOUNTADMIN**
- Required for: Creating secrets, external access integrations, network rules, granting cross-role permissions
- Never assume ACCOUNTADMIN access without explicit user approval

**Examples requiring ACCOUNTADMIN (ask first):**
- `CREATE SECRET ...`
- `CREATE EXTERNAL ACCESS INTEGRATION ...`
- `CREATE NETWORK RULE ...`
- `GRANT ... TO ROLE ...` (cross-role grants)
- `SHOW SECRETS ...` (viewing secrets)

**Examples using FANSIFTER_ENGINEERING (no permission needed):**
- `CREATE TABLE ...`
- `SELECT ... FROM ...`
- `INSERT INTO ...`
- `SHOW TABLES ...`
- `SHOW STREAMLITS ...`
- `ALTER STREAMLIT ... SET EXTERNAL_ACCESS_INTEGRATIONS ...`

---

## Snowflake Configuration (Streamlit Container Runtime)

### Secrets Management

The project uses `secrets.toml` for managing Spotify API credentials.

**Create `secrets.toml` in your app directory:**
```toml
SPOTIFY_CLIENT_ID = "your_client_id_here"
SPOTIFY_CLIENT_SECRET = "your_client_secret_here"
```

**Access in Python:**
```python
import streamlit as st
client_id = st.secrets.get("SPOTIFY_CLIENT_ID")
client_secret = st.secrets.get("SPOTIFY_CLIENT_SECRET")
```

**Setup:**
1. Create `secrets.toml` file with credentials
2. Upload to Snowflake stage with app code
3. Restart Streamlit app

**Available Snowflake Secrets** (DEV schema: `FANSIFTER_APP_REPORTING.DEV_MMACHADO`):
- `spotify_client_id`
- `spotify_client_secret`
- Access granted to: `FANSIFTER_ENGINEERING` role

### External Access Integration

**CRITICAL:** Streamlit Container Runtime requires External Access Integration for external API calls.

**Development Setup (Already Configured):**
- **Network Rule:** `heavy_rotation_external_access_rule`
  - Allows: `api.spotify.com:443`, `accounts.spotify.com:443`
  - Allows: `pypi.org:443`, `files.pythonhosted.org:443`
  - Allows: `dynamodb.us-east-1.amazonaws.com:443`, `sts.us-east-1.amazonaws.com:443`
- **Integration:** `HEAVY_ROTATION_EXTERNAL_ACCESS`

**Attach to New Streamlit App:**
```sql
SHOW STREAMLITS IN SCHEMA FANSIFTER_APP_REPORTING.DEV_MMACHADO;

ALTER STREAMLIT FANSIFTER_APP_REPORTING.DEV_MMACHADO.<YOUR_APP_NAME>
SET EXTERNAL_ACCESS_INTEGRATIONS = (HEAVY_ROTATION_EXTERNAL_ACCESS);
```

**After Changes:**
1. **Full app reboot required** (not just browser refresh)
2. Stop and restart app in Snowsight
3. Container Runtime caches network configs

### Common Troubleshooting

**DNS Resolution Failures:**
- Ensure external access integration is attached
- Verify network rule includes specific endpoints (no wildcards)
- Fully restart app after configuration changes

**Stale Credentials:**
- Never use `@st.cache_resource` for credentials or boto3 clients
- Never use `@st.cache_data` for API calls with short-lived credentials
- Only cache static data

### Streamlit Theming with config.toml

**IMPORTANT:** Use `config.toml` for theming instead of CSS hacks. This is the recommended Streamlit pattern.

**Location:** `.streamlit/config.toml` in your app directory

**Example (Spotify-inspired dark theme):**
```toml
# Heavy Rotation App - Streamlit Theme Configuration
[theme]
base = "dark"
primaryColor = "#1ed760"           # Spotify green
backgroundColor = "#121212"
secondaryBackgroundColor = "#2a2a2a"
textColor = "#FFFFFF"
borderColor = "#3d3d3d"
showWidgetBorder = true
baseRadius = "0.3rem"
buttonRadius = "full"

[theme.sidebar]
backgroundColor = "#000000"
secondaryBackgroundColor = "#1a1a1a"
borderColor = "#696969"
```

**Benefits:**
- Native Streamlit support (no CSS injection)
- Works correctly in Snowflake Container Runtime
- Consistent across all widgets
- Easy to maintain and modify

**Reference:** https://github.com/streamlit/docs/tree/main/python/concept-source/theming-overview-spotify-inspired

---

## Streamlit App Architecture (Python)

### Module Structure (`streamlit-test/lib/`)

```
lib/
├── __init__.py
├── spotify_auth.py         # OAuth token management
├── spotify_client.py       # HTTP client with auto-refresh
├── heavy_rotation_fetcher.py  # Data fetcher
├── wave_processor.py       # Parallel batch processing
└── dynamodb_client.py      # AWS DynamoDB integration
```

### SpotifyAuth (`lib/spotify_auth.py`)
Manages OAuth tokens with automatic refresh:
```python
from lib.spotify_auth import SpotifyAuth

auth = SpotifyAuth(
    refresh_token=token,
    client_id=client_id,
    client_secret=client_secret
)
auth.ensure_valid_token()  # Refreshes if needed
```

### SpotifyClient (`lib/spotify_client.py`)
HTTP client with auto-refresh on 401 and rate limit handling:
```python
from lib.spotify_client import SpotifyClient, SpotifyRateLimitError

client = SpotifyClient(auth)
data = client.get('/me/top/artists', {'time_range': 'short_term', 'limit': 50})
```

**Custom Exceptions:**
- `SpotifyRateLimitError` - Raised on 429 with `retry_after` attribute from HTTP header

### Wave Processing (`lib/wave_processor.py`)
Parallel batch processing using `ThreadPoolExecutor`:
```python
from lib.wave_processor import WaveProcessor, WaveConfig

config = WaveConfig(
    wave_size=10,           # Fans per wave (5-20)
    concurrency=3,          # Parallel workers (1-5)
    inter_wave_delay_ms=500 # Delay between waves (200-1000)
)

processor = WaveProcessor(
    config=config,
    client_id=client_id,
    client_secret=client_secret,
    time_range='short_term',
    target_artist_id=artist_id
)

# Progress callback runs in main thread (safe for Streamlit)
def update_progress(current_fan, total_fans, current_wave, total_waves):
    progress_bar.progress(current_fan / total_fans)

result = processor.process_all(tokens, progress_callback=update_progress)
# result.results - List of fan results
# result.total_time - Processing duration
# result.wave_count - Number of waves
# result.errors_by_wave - Dict of wave_num -> error_count
```

**Key Design Decisions:**
- Each worker creates fresh `SpotifyAuth`/`SpotifyClient` (thread isolation)
- Results collected with `threading.Lock()` for thread safety
- Progress callback called from main thread (via `as_completed()`)
- Rate limit (429) handled with `Retry-After` header and single retry

**Performance:**
| Batch Size | Sequential | Wave Processing | Speedup |
|------------|------------|-----------------|---------|
| 10 fans    | ~3.5s      | ~1.5s           | 2.3x    |
| 50 fans    | ~17.5s     | ~8s             | 2.2x    |
| 200 fans   | ~70s       | ~30s            | 2.3x    |

### Thread Safety in Streamlit Container Runtime
```python
# SAFE: Create UI elements before processing
progress_bar = st.progress(0)
status_text = st.empty()

# SAFE: Callback runs in main thread (as_completed iteration)
def update_progress(...):
    progress_bar.progress(...)  # OK - main thread

# UNSAFE: Never call st.* from worker threads
def process_fan(...):
    st.write("...")  # WRONG - worker thread, will crash
```

---

## Architecture Details

### Spotify OAuth 2.0 Flow

1. **SpotifyAuth** (`auth.ts`) - Token management
   - Validates access tokens via `/me` endpoint
   - Auto-refreshes expired tokens using refresh token
   - Uses client credentials for token refresh

2. **SpotifyClient** (`spotify-client.ts`) - HTTP wrapper
   - Request interceptor: Adds Bearer token
   - Response interceptor: Auto-refreshes on 401, retries once
   - Uses `X-Retry` header to prevent infinite loops

3. **Token Refresh Pattern**: Transparent handling
   - On 401 error → refresh token → retry request
   - No manual token management in application code

### Data Fetching

**HeavyRotationFetcher** (`fetch-heavy-rotation.ts`):
- Implements paginated API calls (max 50 items/request)
- Parallel fetching of tracks and artists using `Promise.all()`
- Batches large requests automatically

**Spotify API Endpoints:**
- `GET /v1/me/top/tracks` - User's top tracks
- `GET /v1/me/top/artists` - User's top artists
- Parameters:
  - `time_range`: `short_term` (4 weeks), `medium_term` (6 months), `long_term` (years)
  - `limit`: 1-50 items per request
  - `offset`: For pagination

### Multi-User Configuration

**multi-fan-config.ts** - Convention-based config loader:
- Reads `FAN_1_REFRESH_TOKEN`, `FAN_2_REFRESH_TOKEN`, etc. from `.env`
- Auto-detects fan count (stops when `FAN_N_REFRESH_TOKEN` missing)
- Each fan needs only refresh token (access tokens generated on-demand)

### AWS Integration (dynamo-test)

**DynamoDB Client:**
- Queries `songwhip-release-tasks-production` table
- Partition key: `group:album{ALBUMID}`
- Sort key: `task:spotify-presave`
- Fetches up to 50 refresh tokens per query

**IAM Requirements:**
- `songwhip` IAM group membership
- MFA for role assumption
- Uses `awsume` for standard team AWS access

**Shared Components:**
- `auth.ts` - OAuth token management
- `spotify-client.ts` - HTTP client with auto-refresh
- `fetch-heavy-rotation.ts` - Data fetcher
- `config.ts` - Type definitions

---

## Key Design Patterns

### Token Management
Never pass access tokens directly. Always use `SpotifyAuth` to ensure freshness:
```typescript
const auth = new SpotifyAuth(accessToken, refreshToken);
await auth.ensureValidToken();  // Validates and refreshes if needed
const client = new SpotifyClient(auth);  // Client handles all auth
```

### Error Handling
- `SpotifyAuth.refreshAccessToken()` throws on failure (no retry)
- `SpotifyClient.get()` throws descriptive errors from API
- Test scripts catch and display user-friendly messages

### Type Safety
All Spotify API responses are typed (see `config.ts`):
- `TopTracksResponse`, `TopArtistsResponse` with full structures
- `TimeRange` union: `'short_term' | 'medium_term' | 'long_term'`

---

## Git Workflow Details

### Initial Setup (One-time)
```bash
git remote add upstream https://github.com/theorchard/collab.git
git remote -v  # Verify remotes
```

### Development Workflow

**1. Before Starting New Work:**
```bash
git fetch upstream
git checkout master
git rebase upstream/master
git push origin master
git checkout -b feature/your-feature-name
```

**2. During Development:**
```bash
git add <files>
git commit -m "Your commit message"
```

**3. Before Creating a Pull Request:**
```bash
git fetch upstream
git rebase upstream/master
# Resolve conflicts if needed:
#   1. Fix conflicts in files
#   2. git add <files>
#   3. git rebase --continue
git push origin feature/your-feature-name --force-with-lease
```

**4. Creating the Pull Request:**
```bash
gh pr create --repo theorchard/collab \
  --title "Your PR title" \
  --body "PR description"
```

### Common Issues

**PR shows conflicts:**
```bash
git fetch upstream
git rebase upstream/master
git push origin <branch-name> --force-with-lease
```

**Duplicate commits in PR:**
Rebase against upstream/master (Git auto-skips duplicates)

**"Already up to date" but PR shows conflicts:**
Always use `upstream/master`, not `origin/master`

### Quick Reference
```bash
# Start new work
git fetch upstream && git checkout master && git rebase upstream/master
git checkout -b feature/new-feature

# Before PR
git fetch upstream && git rebase upstream/master
git push origin feature/new-feature --force-with-lease

# Create PR
gh pr create --repo theorchard/collab
```

---

## Snowflake Secrets Reference

### Secret Management Commands

```sql
-- View existing secrets (ACCOUNTADMIN required)
SHOW SECRETS IN SCHEMA FANSIFTER_APP_REPORTING.DEV_MMACHADO;

-- Grant access
GRANT READ ON SECRET FANSIFTER_APP_REPORTING.DEV_MMACHADO.spotify_client_id TO ROLE <ROLE_NAME>;

-- Rotate secret
ALTER SECRET FANSIFTER_APP_REPORTING.DEV_MMACHADO.spotify_client_id
  SET SECRET_STRING = '<new_client_id>';
```

### Production Deployment

**Create Production Secrets:**
```sql
CREATE SECRET FANSIFTER_APP_REPORTING.PROD.spotify_client_id
  TYPE = GENERIC_STRING
  SECRET_STRING = '<prod_client_id>'
  COMMENT = 'Spotify API Client ID for Heavy Rotation app (PROD)';
```

**Create Production Network Rule:**
```sql
CREATE OR REPLACE NETWORK RULE FANSIFTER_APP_REPORTING.PROD.spotify_network_rule
  TYPE = HOST_PORT
  MODE = EGRESS
  VALUE_LIST = (
    'api.spotify.com:443',
    'accounts.spotify.com:443',
    'pypi.org:443',
    'files.pythonhosted.org:443'
  )
  COMMENT = 'Network access for Spotify API and PyPI';
```

**Create Production External Access Integration:**
```sql
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION prod_heavy_rotation_external_access
  ALLOWED_NETWORK_RULES = (FANSIFTER_APP_REPORTING.PROD.spotify_network_rule)
  ENABLED = TRUE
  COMMENT = 'External access for Heavy Rotation production app';

GRANT USAGE ON INTEGRATION prod_heavy_rotation_external_access TO ROLE <PROD_ROLE>;
```

**Attach to Production App:**
```sql
ALTER STREAMLIT FANSIFTER_APP_REPORTING.PROD.<PROD_APP_NAME>
SET EXTERNAL_ACCESS_INTEGRATIONS = (prod_heavy_rotation_external_access);
```

---

## Environment Configuration

### Single User Mode (api-test/test.ts)
```env
SPOTIFY_CLIENT_ID=...
SPOTIFY_CLIENT_SECRET=...
SPOTIFY_ACCESS_TOKEN=...
SPOTIFY_REFRESH_TOKEN=...
```

### Multi-User Mode (api-test/multi-fan-test.ts)
```env
SPOTIFY_CLIENT_ID=...
SPOTIFY_CLIENT_SECRET=...
FAN_1_REFRESH_TOKEN=...
FAN_2_REFRESH_TOKEN=...
FAN_N_REFRESH_TOKEN=...
```

Generate tokens: `npm run auth` (starts local OAuth callback server)

---

## External Access Integration Details

### Components Required
1. **Network Rule** - Defines allowed external endpoints
2. **External Access Integration** - Uses the network rule
3. **Attach to Streamlit App** - Binds integration to app

### Verification
```sql
-- Check integration
SHOW EXTERNAL ACCESS INTEGRATIONS LIKE 'HEAVY_ROTATION_EXTERNAL_ACCESS';

-- Check network rules
SHOW NETWORK RULES IN SCHEMA FANSIFTER_APP_REPORTING.DEV_MMACHADO;

-- List Streamlit apps
SHOW STREAMLITS IN SCHEMA FANSIFTER_APP_REPORTING.DEV_MMACHADO;
```

### Security Best Practices
- Always specify exact endpoints (e.g., `api.spotify.com:443`)
- Never use wildcards (e.g., `*.amazonaws.com:443`)
- Grant minimal permissions
- Use separate integrations for dev/prod environments