[BUG] Validator database CLI handler needs updates for new schema

[epappas]

[BUG] Validator database CLI handler needs updates for new schema

Bug Description

The validator's database CLI handler at crates/validator/src/cli/handlers/database.rs is using an outdated schema that doesn't reflect the current database structure. The handler is missing tables for GPU profiles, emission metrics, weight allocation history, and other new features that have been added to the validator.

Expected Behavior

The database handler should:

• Create and manage all current tables including new ones
• Show accurate statistics for all tables
• Handle migrations for new schema additions
• Support cleanup operations for all entity types

Actual Behavior

Current handler only manages basic tables:

• miners
• miner_executors
• verification_requests
• verification_logs
• rentals

Missing tables:

• miner_gpu_profiles
• emission_metrics
• weight_allocation_history
• miner_prover_results
• environment_validations
• challenge_results

Steps to Reproduce

1. Run validator database status
2. Notice missing tables in statistics output
3. Check actual SQLite database - contains tables not shown in status
4. Run migrations - doesn't create new tables
5. Cleanup command doesn't handle new entity types

Component

Validator

Environment

• OS: Linux
• Database: SQLite
• Validator version: Latest main branch

Checklist

• Update create_schema() function to include all current tables
  • Add miner_gpu_profiles table
  • Add emission_metrics table
  • Add weight_allocation_history table
  • Add miner_prover_results table
  • Add environment_validations table
  • Add challenge_results table
• Update table statistics in show_database_status()
  • Add queries for new tables
  • Show GPU profile distribution
  • Display emission metrics summary
• Update cleanup operations in cleanup_old_records()
  • Add cleanup for emission_metrics
  • Add cleanup for weight_allocation_history
  • Add cleanup for old GPU profiles
  • Handle cascade deletes properly
• Add new database commands
  • database export - Export data for analysis
  • database compact - Optimize database size
  • database validate - Check integrity and constraints
• Create proper migration system
  • Use sqlx migrations instead of manual SQL
  • Version migrations properly
  • Support rollback operations
• Add database metrics
  • Table sizes and row counts
  • Query performance stats
  • Disk usage information
• Update documentation
  • Document all tables and relationships
  • Add schema diagrams
  • Include migration guide

Additional Context

The database schema has evolved as new features were added (GPU profiling, emission calculations, etc.) but the CLI handler wasn't updated to match. This makes database management and debugging difficult.

Related Code

Current tables defined in:

• crates/validator/src/persistence/entities/
• crates/validator/migrations/

Priority

High - Database management is critical for validator operations and debugging