# CLAUDE.md

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

## Project Overview

TikTok data collection and analytics platform using Apify scrapers. Two main components:
- **CLI tool** (`src/`) - TypeScript Node.js backend for scraping TikTok data
- **UI** (`ui/`) - React/Vite frontend dashboard for visualization

## Commands

### CLI (root directory)
```bash
npm start -- --mode <creator|trend> --target <username|sound_id>
npm run spike    # Run experimental scripts (spike.ts)
```

### UI (ui/ directory)
```bash
cd ui
npm run dev      # Start Vite dev server
npm run build    # Build production bundle (runs tsc -b && vite build)
npm run lint     # ESLint check
npm run preview  # Preview production build
```

## Architecture

### CLI Backend (`src/`)
- `index.ts` - Entry point, parses CLI args for `--mode` and `--target`
- `scrapers.ts` - Apify client wrapper using `clockworks/tiktok-scraper` actor
- `analytics.ts` - Median/aggregation calculations for creator and trend metrics
- `types.ts` - TypeScript interfaces (Video, CreatorMetrics, TrendMetrics)

### React UI (`ui/src/`)
- `layouts/Dashboard.tsx` - Main layout with sidebar navigation (Creator/Trend tabs)
- `pages/CreatorAnalysis.tsx` - Creator performance analysis page
- `pages/TrendAnalysis.tsx` - Sound/trend tracking page
- `api/apify.ts` - Browser-side Apify client (mirrors `src/scrapers.ts`)
- `api/analytics.ts` - Analytics calculations for UI
- `api/db.ts` - IndexedDB caching using Dexie
- `api/utils.ts` - URL parsing utilities (extract sound ID from TikTok URLs)

### Data Flow
1. Scrapers call Apify's `clockworks/tiktok-scraper` actor
2. Raw video data mapped to `Video` interface
3. Analytics functions compute 7-day medians (creator) or daily aggregations (trend)
4. UI caches results in IndexedDB for history

## Environment Variables

### CLI (`.env`)
```
APIFY_API_TOKEN=<your_token>
```

### UI (`ui/.env.local`)
```
VITE_APIFY_API_TOKEN=<your_token>
```

## Key Metrics

**Creator Analysis (7-day rolling medians):**
- Views, Likes, Comments, Shares
- Engagement Rate: (Likes + Comments + Shares) / Views * 100

**Trend Analysis (daily aggregations per sound):**
- Daily creations, views, likes, comments, shares
- **Virality Rate**: Day-over-day change in creations `((Today - Yesterday) / Yesterday) * 100`
  - Positive (green arrow) = creations growing
  - Negative (red arrow) = creations declining
- **Engagement Rate**: Total Likes / Total Views * 100
  - Arrow indicates day-over-day trend (today's daily rate vs yesterday's)
  - Tooltip explains the daily comparison on hover

## Testing

### Local Development
Start the UI dev server and test at http://localhost:5173/

```bash
cd ui && npm run dev
```

### Sample Test Data

**Creator Analysis:**
- Username: `fallontonight` - The Tonight Show's TikTok account
  - High engagement account with millions of views per video
  - Good for testing metric calculations and chart rendering

**Trend Tracking (Licensed Music - Full Snowflake Data):**
- Sound ID: `7528974314329393153` - "Sugar On My Tongue" by Tyler, The Creator
  - Direct PLATFORM_SONG_ID match in FACTS table
  - Tests full engagement data from Snowflake (no API needed)
- Sound ID: `7449924369173760016` - "ME JALO" by Fuerza Regida
  - **Tests ISRC fallback**: Chartmetric ID differs from FACTS PLATFORM_SONG_ID
  - Linked via ISRC `QZ9QQ2400744`

**Trend Tracking (Original Sounds - Requires API Fallback):**
- Sound ID: `7537176505709808415` - User-uploaded "original sound"
  - NOT in FACTS.PROD.STAGING_RAW_TIKTOK (only licensed music there)
  - Falls back to CHARTMETRIC.TIKTOK_STAT (posts only) + manual "Fetch from API" for engagement
  - Good for testing hybrid data flow

### Testing Notes
- Snowflake queries are instant (~1-2 sec)
- Apify API calls take 30-60 seconds (manual fallback button)
- Results are cached in IndexedDB for history
- Both pages have CSV download functionality

## Task Tracking

For current tasks and development roadmap, see [`docs/todo.md`](docs/todo.md).

## Recent Changes

### TikTok ASSET_ID Migration (Feb 2026)
**Critical:** As of Dec 30, 2025, TikTok stopped sending `PLATFORM_SONG_ID` in their reporting API and switched to `ASSET_ID`. There is currently **no mapping** between these identifiers.

**Impact:**
- Pre-2026 data: Has `PLATFORM_SONG_ID`, no `ASSET_ID`
- Post-2026 data: Has `ASSET_ID`, `PLATFORM_SONG_ID` is NULL

**Workaround implemented:** Query by **ISRC** instead of PLATFORM_SONG_ID:
1. Look up ISRC from old FACTS data (using PLATFORM_SONG_ID)
2. If not found, look up ISRC from Chartmetric (~38% have ISRC)
3. Query FACTS by ISRC to get both old and new data

**Limitation for new releases:** Songs released after Dec 30, 2025 that are:
- NOT in Chartmetric, AND
- Have no historical FACTS data with PLATFORM_SONG_ID
- → Cannot be found in FACTS (will fall back to Apify)

**Future:** Waiting for TikTok to provide PLATFORM_SONG_ID → ASSET_ID mapping (uncertain timeline).

### Snowflake Connection Improvements (Feb 2026)
- **Token expiry detection**: `isConnected()` now checks token expiry, not just existence
- **Auto-refresh**: Component automatically refreshes tokens before they expire
- **Periodic checks**: Connection status checked every 30 seconds

### Trend Analysis Redesign (Feb 2026)
- **Chartmetric as Primary Source**: Now queries Chartmetric AND FACTS in parallel
- **Side-by-Side View**: When both sources available, toggle between data views
- **URL Parsing**: Input field now accepts full TikTok music URLs (extracts sound ID automatically)
- **Licensed Sound Badge**: Visual indicator when FACTS data is available
- **New file**: `ui/src/api/utils.ts` - URL parsing utilities

### Trend Analysis Fixes (Jan 2026)
- **Total Creations bug fixed**: Was showing 50 (Apify sample size) instead of actual count
  - Switched to `clockworks/tiktok-sound-scraper` which returns `searchMusic.videos` (real platform count)
  - Added `parseFormattedCount()` in `analytics.ts` to handle "80.3K", "2.2M" formats
- **Sampled data disclaimer**: Chart and table now show "Based on X sampled videos" since daily breakdown is from sample, not actual daily stats

### Snowflake Integration (Feb 2026)
**Plan:** [`docs/snowflake-integration-plan.md`](docs/snowflake-integration-plan.md)

Implemented direct browser → Snowflake SQL API connection for historical trend data.

**Migrated to SME-ORCHARD account** (previously SME-DELPHI):
- `DELPHI_EXPLORATION.MAIN.TIKTOK_TRENDS_DAILY` → `FACTS.PROD.STAGING_RAW_TIKTOK`
- `DS_CHARTMETRIC.RAW_DATA.*` → `CHARTMETRIC.RAW_DATA.*`

**ISRC-Based Lookup** (required since Dec 2025 ASSET_ID migration):
1. Look up ISRC from old FACTS data using `PLATFORM_SONG_ID`
2. If not found, look up ISRC from `CHARTMETRIC.RAW_DATA.TIKTOK` (~38% have ISRC)
3. Query FACTS by `ISRC` to get full date range (works for both old and new data)

**Key files:**
- `ui/src/api/snowflake.ts` - OAuth + SQL API client with PKCE + ISRC fallback
- `ui/src/components/SnowflakeConnect.tsx` - Connection UI in toolbar

**Snowflake tables (SME-ORCHARD account):**

1. `FACTS.PROD.STAGING_RAW_TIKTOK` - **Primary source for engagement** (40B rows, daily)
   - Full daily engagement: `CREATIONS`, `VIDEO_VIEWS`, `LIKES`, `COMMENTS`, `SHARES`
   - `PLATFORM_SONG_ID`, `ASSET_ID`, `DATE`, `SONG_TITLE`, `ARTIST`, `ISRC`
   - Data by territory - aggregate with `SUM()` for global totals
   - ⚠️ **As of Dec 30, 2025**: `PLATFORM_SONG_ID` is NULL, use `ISRC` for lookups

2. `CHARTMETRIC.RAW_DATA.TIKTOK_STAT` - Posts history (572M rows, updated daily)
   - `TIMESTP`, `POSTS` (cumulative creations)
   - Requires join to `TIKTOK` table for metadata

3. `CHARTMETRIC.RAW_DATA.TIKTOK_TOP_VIDEOS_STAT` - Top videos engagement (5.7B rows)
   - `VIEWS`, `LIKES`, `COMMENTS`, `SHARES`, `SAVES` per video
   - Periodic snapshots of top ~300 videos per sound

4. `CHARTMETRIC.RAW_DATA.TIKTOK` - Sound metadata (24M rows)
   - `TIKTOK_ID`, `TRACK`, `ARTIST`, `ARTWORK_URL`, `ISRC`
   - `POSTS_LATEST`, `ORIGINAL`, `OFFICIAL` flags

**Creator Analysis tables:**
- `CHARTMETRIC.RAW_DATA.TIKTOK_USER` - Creator profiles (71M rows)
- `CHARTMETRIC.RAW_DATA.TIKTOK_USER_STAT` - Historical stats (856M rows)

**Environment variables** (`ui/.env.local`):
```
VITE_SNOWFLAKE_ACCOUNT=SME-ORCHARD
VITE_SNOWFLAKE_WAREHOUSE=DEV_OWS_WAREHOUSE
VITE_SNOWFLAKE_ROLE=FANSIFTER_ENGINEERING
```

## Data Sources & Limitations

### Trend Analysis Data Flow

The UI queries **Chartmetric AND FACTS in parallel**, then shows the best available data:

```
User Input (URL or Sound ID)
        ↓
   Parse/Extract Sound ID from URL (supports full TikTok music URLs)
        ↓
   "Searching Chartmetric and licensed music databases..."
        ↓
   Query CHARTMETRIC + FACTS in parallel
        ↓
   ┌─────────────────────────────────────────────────────────┐
   │ Chartmetric? │ FACTS?  │ Result                         │
   ├──────────────┼─────────┼────────────────────────────────┤
   │     Yes      │   Yes   │ Show both (tabs to toggle)     │
   │     Yes      │   No    │ Chartmetric only               │
   │     No       │   Yes   │ FACTS + Apify supplemental     │
   │     No       │   No    │ Apify only (full API fallback) │
   └─────────────────────────────────────────────────────────┘
```

**URL Parsing:** The input field accepts:
- Plain numeric IDs: `7528974314329393153`
- Music URLs: `https://www.tiktok.com/music/Sugar-On-My-Tongue-7528974314329393153`
- Music-ID format: `https://www.tiktok.com/music/music-7528974314329393153`
- Query params: `?music_id=7528974314329393153`

**UI Features:**
- **Data Source Toggle**: When multiple sources available, switch between them with tabs
- **Licensed Sound Badge**: Shows when FACTS data is available (official TikTok reporting)
- **Original Sound Badge**: Shows for user-uploaded sounds not in any database

### Sound ID Mismatch Handling

⚠️ **Important:** The `TIKTOK_ID` in Chartmetric may differ from the `PLATFORM_SONG_ID` in FACTS.
The same song can have different IDs across systems. The app handles this via **ISRC fallback**:

1. Try direct `PLATFORM_SONG_ID` lookup in FACTS
2. If no results, look up `ISRC` from `CHARTMETRIC.RAW_DATA.TIKTOK`
3. Search FACTS by `ISRC` to find matching engagement data

Example: "ME JALO" by Fuerza Regida
- Chartmetric TIKTOK_ID: `7449924369173760016`
- FACTS PLATFORM_SONG_ID: `7449925251968321537`
- Shared ISRC: `QZ9QQ2400744` (used for linking)

### Data Sources (Side-by-Side When Available)

**1. CHARTMETRIC (Primary - Posts/Creations History)**

Primary source for all sounds tracked by Chartmetric. Shows cumulative post growth over time.

| Metric | Source | Type | Notes |
|--------|--------|------|-------|
| Historical Posts | `TIKTOK_STAT.POSTS` | Actual | Daily cumulative totals |
| Track/Artist | `TIKTOK` table | Actual | Chartmetric metadata |
| Top Video Engagement | `TIKTOK_TOP_VIDEOS_STAT` | Sampled | From ~300 tracked videos |

**2. FACTS.PROD.STAGING_RAW_TIKTOK (Licensed Music - Full Engagement)**

Shown alongside Chartmetric for licensed/commercial music. Toggle between views.

⚠️ **Coverage:** Only **licensed/commercial music** from TikTok's label reporting API.

| Metric | Column | Type | Notes |
|--------|--------|------|-------|
| Creations | `CREATIONS` | Actual | Daily new video creations |
| Views | `VIDEO_VIEWS` | Actual | Daily views |
| Likes | `LIKES` | Actual | Daily likes |
| Comments | `COMMENTS` | Actual | Daily comments |
| Shares | `SHARES` | Actual | Daily shares |
| Track/Artist | `SONG_TITLE`, `ARTIST` | Actual | From TikTok reporting |

**3. Apify (Fallback - ~30-60s)**

Used when sound not in Chartmetric. Also shown as supplemental for FACTS-only sounds.

| Metric | Source | Type | Notes |
|--------|--------|------|-------|
| Total Creations | `searchMusic.videos` | Actual | e.g., "3100000" = 3.1M |
| Views/Likes/etc | Sampled videos | Sampled | Sum of ~50 scraped videos |

⚠️ **Apify does NOT provide ISRC** - TikTok doesn't expose ISRC in their public pages/API.
This means Apify cannot help bridge new releases to FACTS data.

### Available TikTok Tables in Snowflake (SME-ORCHARD)

| Table | Rows | Key Columns | Use Case |
|-------|------|-------------|----------|
| `FACTS.PROD.STAGING_RAW_TIKTOK` | 40B | `ASSET_ID`, `ISRC`, `VIDEO_VIEWS`, `LIKES`, `COMMENTS`, `SHARES` | **Primary** - Full engagement (query by ISRC) |
| `CHARTMETRIC.RAW_DATA.TIKTOK_STAT` | 572M | `TIKTOK`, `POSTS`, `TIMESTP` | Posts history |
| `CHARTMETRIC.RAW_DATA.TIKTOK` | 24M | `TIKTOK_ID`, `TRACK`, `ARTIST`, `ISRC` | Sound metadata + ISRC for linking |
| `CHARTMETRIC.RAW_DATA.TIKTOK_TOP_VIDEOS_STAT` | 5.7B | `VIEWS`, `LIKES`, `COMMENTS`, `SHARES` | Video-level stats |
| `CHARTMETRIC.RAW_DATA.TIKTOK_VIDEO` | 623M | `VIDEO_ID`, `TRACK_ID`, `USERNAME` | Video metadata |
| `CHARTMETRIC.RAW_DATA.TIKTOK_USER` | 71M | User profiles | Creator analysis |
| `CHARTMETRIC.RAW_DATA.TIKTOK_USER_STAT` | 856M | User follower/engagement stats | Creator tracking |
| `FACTS.PROD.STAGING_RAW_TIKTOK_TRENDS_TOPSONG` | 11M | Weekly snapshots with country breakdown | Weekly trends by country |

### Apify API Field Reference

**`clockworks/tiktok-sound-scraper` searchMusic object:**
```json
{
  "musicTag": "music-7537176505709808415",
  "videos": "3100000"
}
```
- `musicTag` - Sound identifier
- `videos` - Actual total creations (formatted string, use `parseFormattedCount()`)
- **No fields for total views/likes** - TikTok API doesn't expose sound-level engagement aggregates

**Video item fields:** `playCount`, `diggCount`, `commentCount`, `shareCount`, `createTime`, `musicMeta`