Quick Links

CLAUDE.md

Version: 3.2 | Updated: 2026-01-19

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

Quick Links

• Gross Margin Analysis
• Daily Cost Trends
• Bot Profitability Analysis

Core Principles

Workflow

1. Receive task → TodoList planning → 2. Show plan → User confirms → 3. Execute (no questions) → 4. Summary & review

4 Critical Blockers (Only Allowed Questions)

1. Missing credentials - Database passwords, API keys
2. Conflicting approaches - Cannot determine from codebase
3. Contradictory requirements - User requests conflict
4. Irreversible high-risk - Delete production data, force push

Self-Decide (No Questions)

File naming / Code style / Dependency versions / Test strategy / UI details → Follow existing conventions or best practices

Top 5 Error Patterns (Check Before Coding)

E001: Async Not Parallel | Severe | High Frequency

// Wrong: Sequential execution (13 times x 2s = 26s)
for (const term of searchTerms) {
  const results = await api.search(term);
  allResults.push(...results);
}

// Correct: Parallel execution (max 2s)
const searchPromises = searchTerms.map(term =>
  api.search(term)
    .then(results => ({ term, results, success: true }))
    .catch(error => ({ term, results: [], success: false, error: error.message }))
);
const searchResults = await Promise.all(searchPromises);

Self-check: Are independent async operations using Promise.all()?

E002: Polling Without Timeout | Severe | High Frequency

// Wrong: Infinite polling
scanPollInterval = setInterval(async () => {
  const data = await fetchStatus(scanId);
  if (data.status === 'completed') clearInterval(scanPollInterval);
}, 2000);

// Correct: With timeout
function pollStatus(scanId, maxAttempts = 30) {
  let attempts = 0;
  scanPollInterval = setInterval(async () => {
    attempts++;
    if (attempts > maxAttempts) {
      clearInterval(scanPollInterval);
      showError('Polling timeout');
      return;
    }
    try {
      const data = await fetchStatus(scanId);
      if (data.status === 'completed' || data.status === 'failed') {
        clearInterval(scanPollInterval);
        updateUI(data);
      }
    } catch (error) {
      clearInterval(scanPollInterval);
      showError(error.message);
    }
  }, 2000);
}

Self-check: Does polling have maxAttempts? Does failure/timeout call clearInterval?

E003: Error Not Re-thrown | Severe | Medium Frequency

// Wrong: Error swallowed
async function fetchUser(id) {
  try {
    return await fetch(`/api/users/${id}`).then(r => r.json());
  } catch (error) {
    console.error('Fetch failed:', error);
    // No throw, caller cannot detect failure
  }
}

// Correct: Re-throw
async function fetchUser(id) {
  try {
    return await fetch(`/api/users/${id}`).then(r => r.json());
  } catch (error) {
    console.error('Fetch failed:', error);
    throw new Error(`Cannot fetch user ${id}: ${error.message}`);
  }
}

Self-check: Does the catch block throw error?

E004: SQL Without CTE Pre-filtering | Medium | Medium Frequency

-- Wrong: Filter after JOIN, full table scan
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2026-01-01';

-- Correct: CTE pre-filter
WITH recent_orders AS (
  SELECT user_id, total
  FROM orders
  WHERE created_at > '2026-01-01'
)
SELECT u.name, ro.total
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id;

Self-check: Use CTE to pre-filter large tables? Avoid filtering after JOIN?

E005: Forgotten Resource Cleanup | Severe | Low Frequency

// Wrong: Only cleanup on success
scanPollInterval = setInterval(async () => {
  const data = await fetchStatus(scanId);
  if (data.status === 'completed') {
    clearInterval(scanPollInterval); // Only here
    updateUI(data);
  }
  // Leak on failure!
}, 2000);

// Correct: Cleanup on all exit paths
scanPollInterval = setInterval(async () => {
  try {
    const data = await fetchStatus(scanId);
    if (data.status === 'completed' || data.status === 'failed') {
      clearInterval(scanPollInterval);
      updateUI(data);
    }
  } catch (error) {
    clearInterval(scanPollInterval); // Also cleanup on error
    showError(error.message);
  }
}, 2000);

Self-check: All exit paths (success/failure/timeout) cleanup resources?

Core Methodology

Three-File Pattern (For Long Tasks)

task_plan.md     - Task planning and progress tracking (re-read at decision points!)
notes.md         - Research notes and discoveries
[deliverable].md - Final deliverable

Key mechanism: Re-read task_plan.md before each major decision point to refresh attention window and prevent goal drift.

Failure Tracking (Avoid Repeated Errors)

## Errors Encountered
### [Time] Error Type
**Error**: Specific error message
**Root Cause**: Root cause
**Solution**: Solution
**Learning**: Lessons learned

Phase Gating (Wait for Confirmation at Decision Points)

Phase 1: Requirements → [User confirms "ready"] → Phase 2: Design → [Confirm] → Phase 3: Implementation

Principle: Never proceed to next phase until user explicitly confirms.

Repository Overview

This is a data analysis and automation (DAA) repository for business intelligence, combining:

• Markdown-based analysis templates (executed via Claude Code or base44)
• Vercel serverless functions for scheduled data processing
• PostgreSQL database for storing analysis snapshots
• MCP (Model Context Protocol) integration for database access
• Slack notifications for automated reporting

Project Structure

/
├── skills/                   # Analysis templates (Markdown-based skills)
│   ├── cost-trend-by-user-type.md    # Daily cost trends by user type
│   ├── bot-margin-analysis.md        # Bot profitability analysis
│   ├── gross-margin-analysis.md      # Overall gross margin analysis
│   ├── inactive-email-domains.md     # Inactive domain analysis
│   └── ...
├── base44_prompt_mcphub.md   # MCP client setup for base44 runtime
└── functions/                # Vercel serverless functions
    ├── api/
    │   ├── cron/             # Scheduled jobs
    │   │   ├── sync-art-revenue.ts
    │   │   ├── sync-cost-snapshot.ts
    │   │   ├── daily-summary.ts
    │   │   └── weekly-analysis.ts
    │   └── hello.ts          # Example API endpoints
    ├── lib/
    │   ├── db/               # Database layer
    │   │   ├── schema.ts     # Drizzle ORM schemas
    │   │   └── index.ts      # DB client
    │   ├── mcp/              # MCP client utilities
    │   │   └── client.ts
    │   ├── slack.ts          # Slack API utilities
    │   ├── alerts.ts         # Alert logic
    │   ├── revenue.ts        # Revenue attribution models
    │   └── cost-snapshot.ts  # Cost snapshot logic
    ├── package.json
    ├── vercel.json           # Cron job configuration
    └── tsconfig.json

Development Commands

Functions Directory

cd functions

# Install dependencies
npm install

# Run tests
npm test

# Database operations (Drizzle ORM)
npm run db:push      # Push schema changes to database
npm run db:generate  # Generate migrations
npm run db:migrate   # Run migrations
npm run db:studio    # Open Drizzle Studio GUI

# Local development
vercel dev           # Run locally with Vercel CLI

# Production deployment
vercel --prod

Architecture

Analysis Templates (Markdown Skills)

The skills/*.md files are executable analysis templates. They follow a structured format:

1. Goal: What the analysis aims to accomplish
2. Parameters: Configurable inputs (dates, thresholds)
3. Data Sources: MySQL tables from my_shell_prod database
4. Step-by-step SQL queries: Detailed queries with comments
5. Data Transformation: JavaScript pseudocode for processing
6. Visualization: Chart generation using MCP chart tools

Execution Modes:

• Claude Code: Run directly with MCP server access to my_shell_prod via Bytebase
• base44: Deploy as interactive single-page apps using Deno runtime (see base44_prompt_mcphub.md)

Key Pattern: Analysis templates use MCP tools:

• mcp__mcphub__bytebase-execute_sql: Execute SQL queries
• mcp__mcphub__mcp-server-chart-*: Generate charts (area, line, bar, pie, etc.)

Serverless Functions Architecture

Cron Jobs (defined in vercel.json):

• sync-art-revenue: 16:00 UTC daily - Sync revenue attribution data
• sync-cost-snapshot: 16:05 UTC daily - Snapshot cost breakdown by user type
• daily-summary: 02:00 UTC daily - Generate daily summary report to Slack
• weekly-analysis: 02:00 UTC Monday - Weekly analysis report to Slack

Database Schema (lib/db/schema.ts):

• All tables use prefix daaf_ (data analysis and automation functions)
• botRevenueSnapshots: Daily bot-level revenue with 3 attribution models
• dailySummarySnapshots: Daily aggregated metrics
• costDailySnapshots: Daily cost breakdown by user type
• freeCostByBotSnapshots: Top 30 bots by free user cost

MCP Integration

The repository uses MCP (Model Context Protocol) to:

• Query my_shell_prod MySQL database via Bytebase MCP server
• Generate charts via chart MCP server
• Access Honeycomb, Statsig, Notion for extended analytics

Honeycomb Datasets:

• iOS dataset: test-serviceName

iOS Tracking Events (in Honeycomb test-serviceName dataset):

Common Event Properties: user_id, trace_id, time, slug_id, entry_point

MCP Client Setup (for base44):

• See base44_prompt_mcphub.md for Deno-based MCP client configuration
• Connects to MCP Hub at http://52.12.230.109:3000/mcp
• Requires SLACK_BOT_TOKEN and SLACK_CHANNEL_ID env vars

Key Business Logic

User Classification (6 Types)

Cost Calculation

• Cost unit: actual_energy_cost in cents, convert to USD by dividing by 100
• Task statuses: Include both done and cancel for cost (canceled tasks still incurred cost)
• For revenue attribution: Only use done tasks (completed usage drives payment decisions)

Revenue Attribution Models

Attribution Window

• Order window: start_date to end_date
• Task window: start_date - 7 days to end_date + 7 days
• Captures pre-payment trial usage and post-payment first usage
• Expected coverage: 70-80% of orders

Data Analysis Skills

Skills Overview

Quick Selection Guide

Analysis Workflow

Month start: Revenue & Subscription Analysis → Understand overall performance
  ├─ Revenue dropping → Bot Margin Analysis + Gross Margin Analysis
  ├─ Cost too high → Cost Trend by User Type + Main Site Energy Analysis
  └─ Specific bot anomaly → Bot Revenue/Cost Trend
Regular maintenance: Run Inactive Email Domain Analysis monthly → Optimize whitelist

Important Patterns

SQL Optimization

• Use CTEs to pre-filter by date ranges before JOINs
• Avoid repeated SUBSTRING_INDEX() calls in GROUP BY - compute once in CTE
• For bot margin analysis: SQL-based attribution (15-45s) vs app-layer (60-180s) = 3-10x faster

Temporary Email Domains

56 temporary email domains are hardcoded in analysis templates. If updating, modify in:

• skills/cost-trend-by-user-type.md
• skills/inactive-email-domains.md
• Any cron jobs that classify user types

Database Naming

• All analysis tables MUST use daaf_ prefix
• Example: daaf_bot_revenue_snapshots

Working with Analysis Templates

When modifying skills/*.md analysis templates:

1. SQL Queries: Queries are split into multiple steps for clarity and debugging
2. Date Parameters: Use placeholders like {start_date} and {end_date}
3. Chart Generation: Include complete chart configuration JSON with palette colors
4. Performance: Note optimization strategies (CTEs, pre-filtering, avoiding repeated calculations)

Core Data Tables

Environment Variables

Required in Vercel project settings:

# Database (Postgres for snapshots)
DATABASE_URL=postgresql://...

# Slack notifications
SLACK_BOT_TOKEN=xoxb-...
SLACK_CHANNEL_ID=C...

# Source database access (handled via MCP)
# No direct connection string needed

Testing

cd functions
npm test  # Runs vitest

Test files use .test.ts suffix.

Key Concepts

Typical Workflows

Data Analysis: bytebase query → chart generation → report writing
Debugging: honeycomb traces → bytebase slow query → root cause analysis
Payment: context7 docs → stripe MCP → /write-tests
Bot Analysis: @bot-margin-analysis.md query last 30 days
Cost Monitoring: @cost-trend-by-user-type.md show last 7 days

Ready for tasks