Database Health Skill

# Database Health Skill

**Purpose**: Monitor the MySQL database and verify pipeline data is correctly ingested.

## When Claude Should Use This Skill

Use this skill when:
- User asks "how's the database?" or "check the database"
- User asks "did the data get into the database?" or "verify ingestion"
- After running a pipeline, to confirm data was ingested
- User wants to know game/player counts for a sport
- User mentions "missing data" or "data not showing up"
- Debugging ingestion issues
- User asks "what's in the database?"
- Verifying reference data (teams, players, seasons) exists
- Checking for data quality issues in the database

## What This Skill Does

1. **Connects** to MySQL database using credentials from `.env.local`
2. **Checks** game counts by sport (how many games in database)
3. **Checks** player counts by sport (how many players in database)
4. **Verifies** reference data exists (teams, seasons, positions)
5. **Detects** missing/orphaned records (games without teams, players without positions)
6. **Shows** recent ingestions (what was added today/this week)
7. **Displays** results in easy-to-read table format
8. **Explains** what the numbers mean in plain English

## Critical Constraints

**MUST follow these rules**:
- NO emoji (Windows environment - use ASCII like [OK], [FAIL], [WARN])
- Use MySQL connection from `.env.local` credentials
- Never modify database data (read-only queries)
- Display dates in ET timezone (user's timezone)
- Use Rich library for table display (already installed)

## How to Use This Skill

**Example 1**: User says "check the database"
- Claude runs: `python .claude/skills/db-health/scripts/check_db.py`
- Claude shows: Summary of games, players, teams by sport
- Claude explains: "Database looks healthy - you have X games and Y players"

**Example 2**: User says "did NFL data get ingested?"
- Claude runs: `python .claude/skills/db-health/scripts/check_db.py --sport nfl`
- Claude shows: NFL-specific counts and recent ingestions
- Claude explains: "Yes, NFL data is there. Added 10 games and 250 players today"

**Example 3**: User says "verify database after pipeline run"
- Claude runs: `python .claude/skills/db-health/scripts/check_db.py --date 2025-10-23`
- Claude shows: What was ingested on that specific date
- Claude explains: "Pipeline run completed successfully - all expected data present"

## Expected Outcome

After using this skill:
- User knows if database has data (or if it's empty)
- User knows if today's pipeline run worked (data got ingested)
- User knows if there are any data quality issues (missing teams, orphaned records)
- User understands what the numbers mean (in plain English)
- User feels confident the pipeline is working (or knows what's broken)

## Technical Details

The db-health scripts:
- `check_db.py` - Main health check (all sports overview)
- `check_sport.py` - Deep dive for specific sport
- `check_recent.py` - Show recent ingestions (last 24h/7d)

All scripts:
- Connect to MySQL using `pipelines/shared/db/base_ingestor.py` pattern
- Query key tables: games, players, teams, data_quality_log
- Use Rich for formatted table output
- Handle missing tables gracefully (show [MISSING] not crash)

## Success Criteria

Script is successful if:
- Connects to MySQL database successfully
- Queries database tables without errors
- Displays results in readable table format
- Shows accurate counts for games/players/teams
- Identifies any data quality issues (orphaned records, missing data)
- Explains findings in plain English
- User understands database health status