Enhance Package Retention System and Implement Comprehensive Automated Testing

[naveensrinivasan]

Objective

Improve our security scanning system to maintain historical CVE data while optimizing storage for current versions. Expand and enhance automated tests to ensure robust functionality and data integrity.

Requirements

Data Retention

1. Configurable Version Retention:

   • Maintain complete scan data for the latest N versions (where N is configurable, default N=3).

2. Historical CVE Data:

   • Retain CVE count summaries for all historical scans, including those no longer fully stored.
   • Include counts for Critical, High, Medium, Low, and Total vulnerabilities.
   • Store timestamps with each historical entry.

3. Database Structure:

   • reports table: Contains all historical data, including full scans for latest N versions and CVE count summaries for older versions.
   • Other tables (e.g., scans, vulnerabilities): Maintain data only for the latest N versions.

4. Cleanup Mechanism:

   • Implement a system to remove outdated entries from non-reporting tables while preserving historical summaries.

Enhanced Automated Tests

1. Expanded Version Retention Test:

   • Modify TestDeletePackagesByNameExceptTags to test retention of N versions instead of just one.
   • Verify correct deletion of packages, scans, and vulnerabilities for versions outside the retention scope.

2. Historical Data Integrity Test:

   • Verify that historical CVE count summaries are retained for all versions, including those where full data has been deleted.

3. Configurable Retention Test:

   • Create tests that change the N value and verify the correct number of versions are retained.
   • Test with different sets of version tags to ensure flexibility.

4. Data Consistency Verification:

   • Add checks to ensure CVE counts in detailed scans match historical summaries.

5. Time-Based Retention Test:

   • Simulate multiple scans over time to verify correct long-term data management.
   • Ensure older data is properly summarized and cleaned up as new versions are added.

6. End-to-End (e2e) Test with SQLite:

   • Implement a comprehensive e2e test that simulates the entire package scanning, retention, and cleanup lifecycle.
   • Use an SQLite database to mimic a real-world scenario while keeping the test environment self-contained.
   • This test should cover:
     a. Initial database setup
     b. Multiple rounds of package scanning and data insertion
     c. Retention of N versions as per configuration
     d. Cleanup of older versions
     e. Verification of historical data retention
     f. Queries to retrieve both current and historical data

Additional Notes

• The e2e test should be designed to run quickly enough for regular CI/CD pipelines while still providing comprehensive coverage.
• Consider parameterizing the e2e test to allow for testing different retention configurations and edge cases.