Stats Tables Cleaner Bundle Laravel Package

Technical Evaluation

Architecture Fit

• Purpose Alignment: The bundle is a niche but critical solution for managing database bloat in stats/log tables, aligning well with systems tracking analytics, audit logs, or historical data.
• Doctrine ORM Dependency: Tightly coupled with Symfony/Doctrine, making it ideal for Laravel applications using Doctrine DBAL or Eloquent with Doctrine-like patterns (e.g., via doctrine/dbal or illuminate/database extensions).
• Annotation-Driven: Leverages PHP annotations (via doctrine/annotations), which may require Laravel’s vladimir-yuldashev/laravel-doctrine-orm or manual annotation parsing for native Laravel apps.

Integration Feasibility

• Laravel Compatibility:
  • Eloquent: Limited native support; annotations require workarounds (e.g., custom traits or doctrine/orm integration).
  • Doctrine DBAL: Feasible if using doctrine/dbal for raw queries (bundle’s core logic is DBAL-agnostic).
  • Symfony Bridge: Easier integration if the Laravel app uses Symfony components (e.g., symfony/console for CLI commands).
• Key Dependencies:
  • doctrine/annotations (v1.x for PHP 7.4+).
  • doctrine/dbal (for database abstraction).
  • symfony/console (for CLI commands; Laravel’s Artisan is compatible but may need wrapper).

Technical Risk

• Annotation Parsing: Laravel’s lack of native annotation support may require:
  • Custom annotation reader (e.g., phpdocumentor/reflection-docblock).
  • Migration to attributes (PHP 8+) with a polyfill for older versions.
• Batch Processing: Risk of locking tables or timeouts if batchSize is misconfigured (default: 500). Requires testing with production-like data volumes.
• Event System: The eventName parameter ties to Symfony’s event dispatcher. Laravel’s event system would need a bridge or mock implementation.
• No Laravel-Specific Docs: Undocumented edge cases (e.g., handling created_at vs. updated_at timestamps) may surface during integration.

Key Questions

1. Database Layer:
   • Is the app using Eloquent, Doctrine DBAL, or raw PDO? If Eloquent, how will annotations be parsed?
   • Are stats tables InnoDB (supports transactions) or another engine? Batch operations may need transaction handling.
2. Scheduling:
   • How is the cleanup currently scheduled (Cron, Laravel Task Scheduling)? The bundle’s command must fit this workflow.
3. Event Integration:
   • Is Symfony’s event system used elsewhere? If not, how will eventName be handled (e.g., ignored or mapped to Laravel events)?
4. Testing:
   • Are there existing tests for data cleanup logic? If not, how will edge cases (e.g., partial deletes, foreign keys) be validated?
5. Performance:
   • What’s the expected table size? For >1M rows, batchSize tuning and query optimization (e.g., WHERE clause indexing) are critical.

Integration Approach

Stack Fit

Migration Path

1. Assess Database Layer:

   • If using Eloquent, decide between:
     • Option A: Parse annotations manually (e.g., via a trait) and rewrite logic in Laravel.
     • Option B: Adopt doctrine/dbal for the stats tables to use the bundle natively.
   • If using raw PDO, wrap the bundle’s DBALConnection logic in a Laravel service.

2. Annotation Handling:

   • For PHP 8+: Replace annotations with attributes (e.g., #[\D3nysm\CleanOldData]).
   • For PHP <8: Use a library like voku/annotation-registry to parse annotations at runtime.

3. Command Integration:

   • Publish the bundle’s command as an Artisan command:

     // app/Console/Commands/CleanStatsTables.php
     use D3nysm\Bundle\StatsTablesCleaner\Command\CleanCommand;
     
     class CleanStatsTables extends CleanCommand {
         protected function configure() {
             $this->setName('stats:clean');
         }
     }
     

   • Register in app/Console/Kernel.php:

     protected $commands = [
         \App\Console\Commands\CleanStatsTables::class,
     ];
     

4. Event System:

   • Mock the Symfony event dispatcher or create a Laravel event listener that triggers after cleanup.

5. Scheduling:

   • Add to Laravel’s app/Console/Kernel.php:

     protected function schedule(Schedule $schedule) {
         $schedule->command('stats:clean')->monthly(); // Adjust interval
     }
     

Compatibility

• Doctrine ORM: Not directly compatible; requires DBAL or annotation parsing layer.
• Laravel Eloquent: Partial compatibility with custom logic (annotations won’t work out-of-the-box).
• Symfony Components: Low risk if using symfony/console or symfony/dependency-injection.

Sequencing

1. Phase 1: Proof-of-concept with a single stats table.
   • Test annotation parsing (or attribute migration).
   • Validate batch deletion logic.
2. Phase 2: Integrate event system (if needed).
3. Phase 3: Roll out to all stats tables with monitoring.
4. Phase 4: Optimize batchSize and scheduling based on performance data.

Operational Impact

Maintenance

• Pros:
  • Reduces manual SQL cleanup tasks.
  • Centralized configuration via annotations/attributes.
• Cons:
  • Custom annotation parsing adds maintenance overhead.
  • Bundle updates may require migration (e.g., if annotation syntax changes).
• Mitigation:
  • Document custom integration steps in the codebase.
  • Pin the bundle version in composer.json to avoid breaking changes.

Support

• Debugging:
  • Limited community support (0 stars/dependents). Issues may require reverse-engineering the bundle.
  • Log cleanup operations (e.g., ->log("Deleted {count} records")) for auditing.
• Rollback:
  • No built-in rollback; ensure backups are in place before running cleanup.
  • Consider soft-deletes (e.g., is_deleted flag) if data recovery is critical.

Scaling

• Performance:
  • Batch size (batchSize) must be tuned for large tables (start with 100–500 and monitor).
  • Add indexes on date columns if not present (e.g., ALTER TABLE stats ADD INDEX idx_date (date_column)).
• Concurrency:
  • Avoid running cleanup during peak traffic. Schedule during off-hours.
  • For distributed systems, coordinate cleanup across nodes to prevent data loss.

Failure Modes

Ramp-Up

• Developer Onboarding:
  • Document the custom integration steps (e.g., "To use this bundle, add the CleanOldData attribute to your model").
  • Provide a runbook for troubleshooting (e.g., "If cleanup fails, check the dateProp column exists").
• Testing:
  • Unit Tests: Mock the DBAL connection to test batch deletion logic.
  • Integration Tests: Test the Artisan command with a test database.
  • Load Tests: Simulate large tables to validate performance.
• Training:
  • Train ops teams on monitoring cleanup jobs (e.g., check stats:clean logs).
  • Educate developers on the batchSize parameter’s impact.