# TikTok Music Analytics POC - Implementation Plan
*Using Streamlit Container Runtime on Snowflake with Single-File Architecture*

## Project Overview
Build a lightweight TikTok music analytics tool using Snowflake's Streamlit Container Runtime Private Preview feature and Clockworks APIs from Apify, using a single-file class-based architecture based on Phase 0 validation results.

## Phase 0 Validation Results - COMPLETED ✅

### Test Results Summary

**✅ PASSED Tests:**
- **Test 1**: Basic Streamlit functionality works perfectly
- **Test 3**: External API connectivity works (httpbin.org, Apify endpoints)
- **Test 4**: Package management - all required packages available (pandas, numpy, requests, snowflake-connector-python)
- **Database Connectivity**: Inherent via Snowpark integration (no testing needed)

**❌ FAILED Tests:**
- **Test 5**: Multi-file module imports not supported (files stored compressed in stage)

### Key Technical Findings
1. **Streamlit Version**: Container Runtime uses older Streamlit version but `st.rerun()` works
2. **Network Access**: External API access requires proper network rules (no wildcards like *:443)
3. **Database Integration**: Snowpark integration via `get_active_session()` is the correct pattern
4. **File Structure**: Multi-file Python modules don't work - single file architecture required
5. **Package Availability**: All required packages for POC are available
6. **Compute Pools**: Must be specified for Container Runtime apps

### Architecture Decision: Single-File Design
Based on Phase 0 findings, the implementation will use a **single-file class-based architecture** instead of multi-agent development. All functionality will be contained in one main Python file with class-based modules.

## Technical Architecture

### Platform Stack
- **Frontend**: Streamlit (Container Runtime - validated version)
- **Backend/Database**: Snowflake with Snowpark integration
- **Data Sources**: 
  - Apify Clockworks TikTok APIs (video/engagement data) - validated connectivity
  - Snowflake Chartmetric table (song-to-URL mapping)
- **Scheduling**: Snowflake Tasks
- **Environment**: Streamlit Container Runtime (Private Preview)
- **Architecture**: Single-file class-based design (required by Container Runtime)

### Key Documentation
- **Streamlit Container Runtime Docs**: https://docs.snowflake.com/LIMITEDACCESS/streamlit/container-runtime

### Single-File Architecture Pattern
Based on Phase 0 validation, the application will be structured as:
```python
# main.py - Single file containing all functionality
import streamlit as st
from snowflake.snowpark import Session
import pandas as pd
import requests

class DatabaseManager:
    """Handles all Snowflake database operations"""
    def __init__(self, session):
        self.session = session
    
class APIIntegrator:
    """Handles Apify/Clockworks API interactions"""
    def __init__(self):
        pass
    
class TikTokAnalyzer:
    """Main application logic and data processing"""
    def __init__(self):
        self.db = DatabaseManager(st.session_state.get('snowpark_session'))
        self.api = APIIntegrator()

class StreamlitUI:
    """User interface components and dashboard"""
    def __init__(self, analyzer):
        self.analyzer = analyzer

# Main application entry point
def main():
    session = get_active_session()  # Snowpark session
    app = TikTokAnalyzer()
    ui = StreamlitUI(app)
    ui.run()

if __name__ == "__main__":
    main()
```

### Key Components
1. **Single Python File**: All functionality contained in one file with class-based modules
2. **Snowpark Integration**: Using `get_active_session()` for database connectivity
3. **External API Access**: Validated connectivity to Apify endpoints
4. **Class-Based Design**: Organized functionality without file imports
5. **Container Runtime Compliance**: Designed for compressed stage storage

### Enhanced Data Flow
1. User inputs song title and artist
2. **NEW**: Query `DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK` to find matching songs
3. **NEW**: Generate TikTok music URL from `TIKTOK_TRACK_ID`
4. Use Clockworks TikTok Sound Scraper with discovered URL
5. Extract and aggregate video/creator data
6. Store results in SPARK database
7. Display analytics on dashboard
8. Schedule daily refresh for tracked songs

### Container Runtime Specific Considerations
- **Network Access**: External API calls require explicit network rules (no wildcard patterns)
- **Package Management**: All required packages are pre-installed and available
- **Compute Pools**: Must be specified for Container Runtime applications
- **Database Access**: Use Snowpark `get_active_session()` pattern for connectivity
- **File Architecture**: Single-file design required due to stage compression

## Implementation Phases (Revised Single-File Approach)

### Phase 0: Container Runtime Exploration - COMPLETED ✅
**Status**: COMPLETED - Green light with architecture adjustment
**Duration**: 1 Day

**Test Results**:
- ✅ **Test 1**: Basic Streamlit functionality - PASSED
- ✅ **Test 3**: External API connectivity - PASSED (with network rule requirements)
- ✅ **Test 4**: Package management - PASSED (all packages available)
- ✅ **Database Connectivity**: PASSED (Snowpark integration validated)
- ❌ **Test 5**: Multi-file imports - FAILED (single-file architecture required)

**Key Learnings Applied**:
- Container Runtime requires single-file architecture
- External API access needs explicit network rules
- Snowpark `get_active_session()` is the correct database pattern
- All required Python packages are available
- Compute pools must be specified for apps

**Architecture Adjustment**: Changed from multi-agent development to single-file class-based design

*See [PHASE_0_EXPLORATION.md](PHASE_0_EXPLORATION.md) for detailed test results*

### Phase 1: Foundation & Single-File Structure
**Duration**: 1 day

**Core Tasks**:
- [ ] Set up Streamlit Container Runtime environment with compute pool
- [ ] Create single-file application structure with class-based modules
- [ ] Define data models and database schemas
- [ ] Set up network rules for external API access
- [ ] Validate Snowpark session integration
- [ ] **SOLVED**: Validate access to Chartmetric TikTok table for song-to-URL mapping

**Class Structure Setup**:
- [ ] **DatabaseManager Class**: Snowflake operations using Snowpark session
- [ ] **APIIntegrator Class**: Apify/Clockworks API handling with network rules
- [ ] **TikTokAnalyzer Class**: Core business logic and data processing
- [ ] **StreamlitUI Class**: User interface components and dashboard
- [ ] **Configuration Management**: Environment variables and settings within single file

**Technical Setup**:
- [ ] Container Runtime environment with specified compute pool
- [ ] Network rule configuration for external API access
- [ ] Database schema creation and validation
- [ ] Apify API integration testing

**Deliverables**:
- Working Container Runtime environment
- Single-file application skeleton with all classes
- Database schema and network connectivity validated

### Phase 2: Core Development (Single-File Implementation)
**Duration**: 2-3 days

#### DatabaseManager Class Implementation:
- [ ] Design and create Snowflake schemas using Snowpark session:
  - `TRACKED_SONGS` (song_id, title, artist, tiktok_track_id, tiktok_music_url, chartmetric_id, created_at, last_updated)
  - `SONG_VIDEOS` (video_id, song_id, creator, creator_profile_url, views, likes, shares, hearts, url, scraped_at)
  - `SONG_ANALYTICS` (song_id, total_videos, total_views, total_likes, total_shares, top_video_id, top_creator, date)
- [ ] Create methods to query `DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK` table
- [ ] Build song lookup methods using Chartmetric data (ARTIST/TRACK matching)
- [ ] Implement data validation and error handling methods
- [ ] Build data access layer methods within class

#### APIIntegrator Class Implementation:
- [ ] Build song lookup methods using Chartmetric data:
  - Search `DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK` by ARTIST/TRACK columns
  - Generate TikTok music URLs using `TIKTOK_TRACK_ID` field
  - Handle fuzzy matching for song titles and artist names
- [ ] Build Apify/Clockworks integration methods:
  - Video data extraction using TikTok Sound Scraper with discovered URLs
  - Comprehensive error handling (song not found, API failures, rate limits)  
  - Rate limiting compliance and retry logic
- [ ] Create data transformation methods:
  - Raw TikTok data to structured format
  - Data cleaning and validation
  - Duplicate detection and handling
  - **Graceful degradation** for missing or incomplete data
- [ ] Test with real songs from Chartmetric data

#### StreamlitUI Class Implementation:
- [ ] Create Streamlit app interface methods
- [ ] Build song input form components
- [ ] Design dashboard layout and display methods
- [ ] Implement loading states and error handling
- [ ] Create data visualization methods

#### TikTokAnalyzer Class Implementation:
- [ ] Build main application logic and workflow coordination
- [ ] Integrate DatabaseManager and APIIntegrator classes
- [ ] Implement business logic for analytics processing
- [ ] Create error handling and validation methods

**Deliverables**:
- Complete single-file application with all classes implemented
- Working database operations through Snowpark
- Functional API integration with network rules
- Basic Streamlit UI with data display

### Phase 3: Integration & Dashboard Completion
**Duration**: 1-2 days

**Integration Tasks**:
- [ ] **Class Integration**: Ensure all classes work together seamlessly within single file
- [ ] **Data Flow Testing**: Validate end-to-end data flow from UI to database
- [ ] **Dashboard Completion**: 
  - Key metrics display (total videos, total views)
  - Top 5 videos table with clickable links
  - Top 5 creators table with profile links
  - Data freshness indicators
- [ ] **Error Handling Integration**: Consistent error handling across all classes
- [ ] **Performance Testing**: Validate response times within Container Runtime

**Container Runtime Specific Testing**:
- [ ] Validate single-file deployment works correctly
- [ ] Test network rules for external API access
- [ ] Confirm Snowpark session handling
- [ ] Verify compute pool performance

**Deliverables**:
- Fully integrated MVP application in single file
- Working dashboard with real TikTok data
- Complete error handling and user feedback

### Phase 4: Automation & Polish
**Duration**: 1-2 days

**Automation Tasks**:
- [ ] Create Snowflake Task for daily refresh using stored procedures
- [ ] Implement monitoring and alerting within application
- [ ] Add data retention policies
- [ ] Performance optimization for single-file architecture
- [ ] Create user documentation

**Polish Tasks**:
- [ ] UI/UX improvements using validated Streamlit features
- [ ] Comprehensive error scenario handling
- [ ] Security review (API keys, network access)
- [ ] Code cleanup and optimization within single file
- [ ] Container Runtime deployment testing

**Deliverables**:
- Automated daily data refresh
- Fully tested and optimized single-file POC
- User documentation and Container Runtime deployment guide

## Technical Requirements

### Snowflake Setup
```sql
-- Database and schema creation
CREATE DATABASE SPARK;
CREATE SCHEMA MUSIC_DATA;

-- Required tables (detailed schemas in Phase 2)
CREATE TABLE TRACKED_SONGS (...);
CREATE TABLE SONG_VIDEOS (...);
CREATE TABLE SONG_ANALYTICS (...);
```

### Python Dependencies (Container Runtime - Validated Available)
Based on Phase 0 validation, all required packages are available:
```
streamlit  # Older version but st.rerun() works
requests  # For external API calls
pandas  # For data manipulation
numpy  # For numerical operations  
snowflake-connector-python  # For database connectivity
snowflake-snowpark-python  # For Snowpark sessions
```

**Note**: `apify-client` package availability needs verification, but basic HTTP requests to Apify endpoints work with `requests` library.

### API Configuration (Container Runtime Specific)
- **Network Rules**: Configure explicit network rules for external API access (no wildcards)
- **Apify Integration**: Use direct HTTP requests to Apify endpoints (validated working)
- **API Token Management**: Secure storage within Streamlit secrets or environment variables
- **Rate Limiting**: Compliance with API quotas and proper retry logic
- **Error Handling**: Comprehensive error handling for API failures and network issues
- **Data Validation**: Validate and clean all scraped content before storage

## Success Criteria

### Functional
- [ ] User can input song and artist
- [ ] System finds videos using Clockworks TikTok API
- [ ] Dashboard displays accurate analytics
- [ ] Top 5 lists show correct data with working links
- [ ] Daily refresh works automatically

### Technical (Updated Based on Phase 0)
- [ ] Container Runtime single-file deployment stable
- [ ] External API integration reliable with network rules
- [ ] Snowpark database integration working via `get_active_session()`
- [ ] Data processing efficient within single-file architecture
- [ ] Error handling comprehensive across all classes
- [ ] Performance acceptable (<5 seconds load time) with compute pool optimization

## Risk Mitigation

### Technical Risks (Updated Based on Phase 0)
- **Single-file architecture complexity**: ✅ MITIGATED - Use class-based organization within single file
- **Multi-file imports limitation**: ✅ RESOLVED - Single-file design prevents import issues  
- **Network access restrictions**: ✅ MITIGATED - Configure explicit network rules for API access
- **Song matching accuracy**: Implement fuzzy matching for artist/track names in Chartmetric data
- **Chartmetric data coverage**: Handle cases where songs aren't in the Chartmetric table
- **API rate limits**: Implement proper throttling and monitoring with validated connectivity
- **Data quality**: Validate and clean all scraped data
- **Song not found scenarios**: Implement graceful error handling and user feedback
- **Compute pool performance**: Monitor usage and optimize for Container Runtime environment
- **Package dependencies**: ✅ RESOLVED - All required packages validated as available

### Business Risks
- **Limited song coverage**: Test with diverse song types
- **TikTok API changes**: Build flexible parsing logic
- **User expectations**: Set clear POC limitations

## Resource Requirements

### Snowflake (Container Runtime Specific)
- Database storage for song and video data  
- **Compute Pool**: Required specification for Container Runtime apps
- Snowpark session integration via `get_active_session()`
- Task scheduling for daily refreshes using stored procedures
- Network rules configuration for external API access

### External APIs
- Apify account with sufficient credits
- Clockworks TikTok scraper access

### Development Time (Revised Single-File Approach)
- **Phase 0 (Exploration)**: 1 day - ✅ COMPLETED
- **Total estimated duration**: 5-7 days including completed Phase 0
- **MVP delivery**: After Phase 3 (4-5 days from start)
- **Full POC**: After Phase 4 (5-7 days from start)

**Timeline Benefits from Phase 0 Learnings**:
- Single-file architecture reduces integration complexity
- Pre-validated package availability eliminates setup time
- Confirmed API connectivity reduces debugging time
- Clear Container Runtime constraints enable focused development

## Success Metrics (Updated for Single-File Architecture)
- Working POC demonstrates all core features in single-file deployment
- Accurate TikTok data extraction and analysis via validated APIs
- Intuitive user interface using validated Streamlit features
- Reliable daily automation through Snowflake Tasks
- Successful Container Runtime deployment with compute pools
- Positive user feedback and performance metrics
- Clear path to production scaling

## Next Steps After POC
- User feedback collection and analysis
- Performance optimization for Container Runtime environment
- Feature expansion planning within single-file constraints
- Production deployment strategy with network rule finalization
- Business model validation and scaling considerations

## Key Lessons from Phase 0 Validation
1. **Architecture Constraint**: Container Runtime requires single-file design - embrace class-based organization
2. **Network Configuration**: External APIs work but need explicit network rules configuration
3. **Database Integration**: Snowpark `get_active_session()` pattern is the correct approach
4. **Package Ecosystem**: Core packages are available, focus on proven libraries
5. **Deployment Model**: Compute pools are mandatory for Container Runtime apps
6. **Development Approach**: Validation-first approach prevented major architectural pivots