Focus Week Live Counter 🎯

A dramatic "Jack Bauer 24" styled live counter displaying combined Weekly Active Users (WAU) for VS Code + Code Reviews during the focus week (Jan 26 - Feb 1, 2026).

🎬 Features

• Dramatic 24-Style Design: Pure black background with glowing gold counter
• Real-Time Updates: Auto-refreshes every 60 seconds
• Large Display: 180px digital clock-style font optimized for office TVs
• Full-Screen Mode: Removes all UI chrome for maximum impact
• Error Handling: Graceful error display with retry logic
• Responsive: Works on desktop, tablet, and mobile devices

📊 What It Tracks

The counter displays the combined count of unique users who have:

• Used VS Code with LLM completions (from PostHog events)
• Created code reviews (from backend database)

During the focus week starting January 26, 2026.

🏗️ Architecture

┌─────────────────────────────────────────┐
│  Streamlit App (in Snowflake)          │
│  ├─ app.py (main application)          │
│  ├─ Jack Bauer 24 styling              │
│  └─ Auto-refresh logic                 │
└─────────────────────────────────────────┘
                  ↓
┌─────────────────────────────────────────┐
│  Snowflake Data Warehouse               │
│  ├─ staging_posthog.events              │
│  ├─ backend_prod.public tables          │
│  └─ dbt_prod.int_user_id mapping        │
└─────────────────────────────────────────┘

🚀 Deployment to Snowflake

Prerequisites

1. Snowflake Account with access to:

   • KILO_DW database
   • staging_posthog, backend_prod, and dbt_prod schemas
   • Required tables (see Permissions section)

2. Snowflake Role with:

   • CREATE STREAMLIT privilege
   • USAGE on database and schemas
   • SELECT on required tables

Step 1: Create Streamlit App in Snowflake

Option A: Using Snowflake UI (Recommended)

1. Log into Snowflake web interface
2. Navigate to Streamlit section in the left sidebar
3. Click + Streamlit App
4. Configure the app:
   • Name: focus_week_counter
   • Warehouse: Select your warehouse (e.g., COMPUTE_WH)
   • App location: Choose database and schema
5. Click Create

Option B: Using SQL

CREATE STREAMLIT focus_week_counter
  ROOT_LOCATION = '@<your_stage>'
  MAIN_FILE = 'app.py'
  QUERY_WAREHOUSE = '<your_warehouse>';

Step 2: Upload Application Files

1. In the Streamlit app editor, upload these files:

   • app.py (main application)
   • requirements.txt (dependencies)
   • environment.yml (Python environment)

2. Or use Snowflake stage:

-- Create stage if needed
CREATE STAGE IF NOT EXISTS streamlit_stage;

-- Upload files
PUT file://app.py @streamlit_stage AUTO_COMPRESS=FALSE OVERWRITE=TRUE;
PUT file://requirements.txt @streamlit_stage AUTO_COMPRESS=FALSE OVERWRITE=TRUE;
PUT file://environment.yml @streamlit_stage AUTO_COMPRESS=FALSE OVERWRITE=TRUE;

Step 3: Configure Permissions

Ensure your Snowflake role has access to required tables:

-- Grant database and schema usage
GRANT USAGE ON DATABASE KILO_DW TO ROLE <your_role>;
GRANT USAGE ON SCHEMA staging_posthog TO ROLE <your_role>;
GRANT USAGE ON SCHEMA backend_prod TO ROLE <your_role>;
GRANT USAGE ON SCHEMA dbt_prod TO ROLE <your_role>;

-- Grant table access
GRANT SELECT ON TABLE staging_posthog.events TO ROLE <your_role>;
GRANT SELECT ON TABLE backend_prod.public.kilocode_users TO ROLE <your_role>;
GRANT SELECT ON TABLE backend_prod.public.cloud_agent_code_reviews TO ROLE <your_role>;
GRANT SELECT ON TABLE KILO_DW.dbt_prod.int_user_id__to_distinct_id TO ROLE <your_role>;

-- Grant warehouse usage
GRANT USAGE ON WAREHOUSE <your_warehouse> TO ROLE <your_role>;

Step 4: Run the App

1. Click Run in the Streamlit editor
2. The app will start and display the counter
3. Share the URL with your team

Step 5: Display on Office TVs

1. Open the Streamlit app URL in a browser
2. Press F11 for full-screen mode
3. The app will auto-refresh every 60 seconds
4. All Streamlit UI chrome is hidden for clean display

📁 Project Structure

focus_week_live_counter/
├── app.py                    # Main Streamlit application
├── requirements.txt          # Python dependencies
├── environment.yml           # Snowflake environment config
├── README.md                 # This file
├── kilocounter.md           # Original specification
└── plans/                    # Planning documents
    ├── implementation-plan.md
    ├── architecture.md
    └── visual-design.md

🎨 Visual Design

Color Palette

• Background: #000000 (Pure Black)
• Counter: #FFD700 (Gold) with glowing effect
• Text: #FFFFFF (White)
• Borders: #FFD700 (Gold) with outer glow

Typography

• Counter: 180px Courier New (monospace)
• Title: 48px Arial Black (bold, uppercase)
• Subtitle: 24px Arial

Layout

┌─────────────────────────────────────────────────────┐
│                                                     │
│     VS CODE + CODE REVIEWS WEEKLY ACTIVE USERS      │
│                                                     │
│  ┌───────────────────────────────────────────────┐ │
│  │                                               │ │
│  │                   1,234                       │ │
│  │                                               │ │
│  └───────────────────────────────────────────────┘ │
│                                                     │
│         FOCUS WEEK: JAN 26 - FEB 1, 2026           │
│         LAST UPDATED: 10:35:42 UTC                 │
│                                                     │
└─────────────────────────────────────────────────────┘

🔍 How It Works

Data Query

The app executes a Snowflake query that:

1. Identifies VS Code users from PostHog events:

   • Filters for 'LLM Completion' events
   • Excludes bot traffic (Singapore IPs)
   • Maps PostHog distinct_id to user_id
   • Filters for dates >= 2026-01-26

2. Identifies Code Reviews users from backend:

   • Gets PR authors from code reviews table
   • Excludes internal users (@kilocode.ai)
   • Filters for dates >= 2026-01-26

3. Combines both sets using UNION

4. Counts distinct users across both features

Auto-Refresh Logic

# Check elapsed time since last refresh
elapsed = time.time() - st.session_state.last_refresh

# Refresh every 60 seconds
if elapsed >= 60:
    st.session_state.last_refresh = time.time()
    st.rerun()

Error Handling

• Catches Snowflake connection errors
• Displays user-friendly error messages
• Shows error in red box with same dramatic styling
• Continues auto-refresh to retry

🛠️ Local Development (Optional)

For local testing with Snowflake credentials:

1. Install dependencies:

pip install streamlit snowflake-connector-python

2. Create .env file with credentials:

SNOWFLAKE_ACCOUNT=your_account
SNOWFLAKE_USER=your_user
SNOWFLAKE_PASSWORD=your_password
SNOWFLAKE_ROLE=your_role
SNOWFLAKE_WAREHOUSE=your_warehouse

3. Modify app.py to use snowflake.connector instead of get_active_session()

4. Run locally:

streamlit run app.py

Note: For production deployment in Snowflake, use the native get_active_session() method (already implemented).

📊 Query Performance

• Expected execution time: 5-15 seconds
• Data volume: Processes millions of PostHog events
• Optimization: Uses CTEs and proper filtering
• Caching: Snowflake query result caching applies

🔒 Security

• Authentication: Handled by Snowflake (no credentials in code)
• Authorization: Uses Snowflake role-based access control
• Data Access: Read-only queries
• Network: Runs within Snowflake's secure environment

🐛 Troubleshooting

App shows "LOADING..." indefinitely

• Check Snowflake warehouse is running
• Verify role has SELECT permissions on all tables
• Check query execution in Snowflake worksheet

Error: "No data returned from query"

• Verify tables exist and have data
• Check date filter (>= 2026-01-26)
• Ensure joins are working correctly

Counter shows "---"

• Query returned NULL or no results
• Check data exists for the focus week period

App not auto-refreshing

• Check browser console for errors
• Verify Streamlit session state is working
• Try manual refresh (F5)

📝 Customization

Change Refresh Interval

Edit app.py:

# Change from 60 to desired seconds
if elapsed >= 60:  # Change this value

Change Color Scheme

Edit the CSS in app.py:

# Change gold (#FFD700) to your preferred color
color: #FFD700;  # Counter color
border: 3px solid #FFD700;  # Border color

Change Counter Size

Edit the CSS in app.py:

font-size: 180px;  # Change this value

📈 Future Enhancements

Potential additions (not in current scope):

• Historical trend chart
• Breakdown by VS Code vs Code Reviews
• Goal line or target indicator
• Slack notifications for milestones
• Multiple focus week support
• Export data functionality

🤝 Contributing

This is an internal tool for the focus week. For changes:

1. Update the planning documents in /plans
2. Test changes locally if possible
3. Deploy to Snowflake Streamlit
4. Verify on office displays

📄 License

Internal use only - Kilocode.ai

🙏 Acknowledgments

• Inspired by the "24" TV series countdown aesthetic
• Built with Streamlit and Snowflake
• Data from PostHog and internal backend

---

Focus Week: January 26 - February 1, 2026
Goal: Track and celebrate our combined VS Code + Code Reviews adoption! 🚀