proffesor-for-testing__agentic-qe/docs/testing/migration-test-suite.md

Database Migration Test Suite

Overview

Comprehensive automated test suite for database migration and consolidation, ensuring migration safety, data integrity, and rollback capability.

Created: 2025-11-16 Test Files: 5 test suites, 1 execution script, 1 CI workflow Total Coverage: Migration functions, backup/restore, schema versioning, data integrity, rollback

---

Test Suite Components

1. Unit Tests

Database Migration Functions

File: /workspaces/agentic-qe-cf/tests/unit/database-migration.test.ts

Test Coverage:

• ✅ Checksum calculation and consistency
• ✅ File change detection
• ✅ Data preservation during migration
• ✅ Column schema verification
• ✅ Dry-run mode functionality
• ✅ Backup creation with timestamps
• ✅ Index preservation
• ✅ Error handling (missing DB, corrupted files)
• ✅ Disk space validation

Key Tests:

- should calculate consistent checksums
- should detect file changes
- should preserve all episodes records
- should preserve all patterns records
- should detect data corruption
- should verify table schemas
- should not create target database in dry-run mode
- should create backup with timestamp
- should handle corrupted database file

Schema Version Management

File: /workspaces/agentic-qe-cf/tests/unit/schema-version.test.ts

Test Coverage:

• ✅ Version table creation and structure
• ✅ Version tracking and history
• ✅ Schema upgrade sequences
• ✅ Rollback compatibility validation
• ✅ Migration safety checks
• ✅ Version metadata and descriptions

Key Tests:

- should create schema_version table
- should track multiple schema versions
- should get current schema version
- should apply schema upgrade from v1 to v2
- should apply multiple sequential upgrades
- should identify schema version for rollback
- should prevent duplicate version application
- should require sequential version application

2. Integration Tests

Backup and Restore System

File: /workspaces/agentic-qe-cf/tests/integration/backup-restore.test.ts

Test Coverage:

• ✅ Backup creation with checksums
• ✅ All tables included in backup
• ✅ Complete data preservation
• ✅ Corrupted backup detection
• ✅ Checksum file validation
• ✅ Database restoration from backup
• ✅ Integrity verification after restore
• ✅ Backup listing and management
• ✅ Old backup cleanup

Key Tests:

- should create backup with timestamp and checksum
- should include all tables in backup
- should preserve all data in backup
- should detect corrupted backup files
- should restore database from backup
- should verify checksum before restoration
- should maintain database integrity after restoration
- should remove old backups

Data Integrity

File: /workspaces/agentic-qe-cf/tests/integration/data-integrity.test.ts

Test Coverage:

• ✅ Complete data migration (100 episodes, 50 patterns)
• ✅ JSON data integrity
• ✅ Numeric precision preservation
• ✅ Table checksum verification
• ✅ Column data type preservation
• ✅ NOT NULL constraint validation
• ✅ Index preservation and functionality
• ✅ Query performance benchmarks
• ✅ Complex joins and aggregations
• ✅ Special character handling
• ✅ Edge cases (empty strings, long text)

Key Tests:

- should migrate all episodes records (100 records)
- should preserve JSON data integrity
- should preserve numeric precision
- should have matching checksums for identical data
- should preserve all indexes
- should use indexes for queries
- should perform indexed queries efficiently (<100ms for 100 queries)
- should handle complex joins efficiently (<50ms)
- should aggregate large datasets efficiently (<20ms)

Rollback Functionality

File: /workspaces/agentic-qe-cf/tests/integration/rollback.test.ts

Test Coverage:

• ✅ Schema restoration to original state
• ✅ Data restoration verification
• ✅ Table count restoration
• ✅ Zero data loss during rollback
• ✅ Checksum matching after rollback
• ✅ Post-rollback functionality (CRUD operations)
• ✅ Database integrity checks
• ✅ Transaction support
• ✅ Partial migration rollback
• ✅ Corruption recovery
• ✅ Multi-version rollback

Key Tests:

- should restore original schema
- should restore original data
- should preserve all episode records
- should have matching checksums after rollback
- should allow querying data after rollback
- should allow inserting new data
- should maintain database integrity
- should support transactions
- should rollback failed partial migration
- should restore from backup after corruption

---

Test Execution

Local Execution

Run All Migration Tests

./scripts/test-migration.sh

Run Individual Test Suites

# Unit tests
npm run test:unit -- --testPathPattern=database-migration
npm run test:unit -- --testPathPattern=schema-version

# Integration tests
npm run test:integration -- --testPathPattern=backup-restore
npm run test:integration -- --testPathPattern=data-integrity
npm run test:integration -- --testPathPattern=rollback

Run with Coverage

npm run test:unit -- --testPathPattern=database-migration --coverage
npm run test:integration -- --testPathPattern=data-integrity --coverage

CI/CD Execution

Workflow File: .github/workflows/test-migration.yml

Triggers:

• Push to main or testing-with-qe branches
• Pull requests to main
• Changes to migration-related files

Jobs:

1. migration-tests: Run all test suites on Node 18.x and 20.x
2. migration-safety-checks: Verify migration scripts and utilities
3. migration-performance: Benchmark migration performance
4. migration-documentation: Verify test documentation
5. summary: Aggregate results and report

Artifacts:

• Test results
• Coverage reports
• Performance benchmarks

---

Test Statistics

Unit Tests

Test Suite	Tests	Coverage
Database Migration	15+ tests	Checksums, data preservation, backups
Schema Version	20+ tests	Versioning, upgrades, rollback

Integration Tests

Test Suite	Tests	Coverage
Backup/Restore	12+ tests	End-to-end backup lifecycle
Data Integrity	25+ tests	100 episodes, 50 patterns, performance
Rollback	18+ tests	Complete rollback scenarios

Total: 90+ comprehensive tests

---

Performance Benchmarks

Query Performance (from data-integrity tests)

• ✅ Indexed queries: <100ms for 100 queries
• ✅ Complex joins: <50ms
• ✅ Aggregations: <20ms

Migration Performance (from CI benchmarks)

• ✅ Insert 10,000 records: <5 seconds
• ✅ Backup creation: <1 second for typical database
• ✅ Checksum calculation: <100ms

---

Success Criteria

All Tests Must Pass

✅ Unit Tests

• Checksum calculation works correctly
• Data preservation verified
• Schema validation passes
• Dry-run mode functional
• Error handling comprehensive

✅ Integration Tests

• Backup/restore cycle complete
• Data integrity maintained
• Rollback successful
• Post-migration system functional
• Performance benchmarks met

✅ CI/CD Requirements

• All tests pass in CI
• Code coverage >80% for migration code
• Tests run in <10 minutes
• Clear failure messages

---

File Locations

/workspaces/agentic-qe-cf/
├── tests/
│   ├── unit/
│   │   ├── database-migration.test.ts
│   │   └── schema-version.test.ts
│   └── integration/
│       ├── backup-restore.test.ts
│       ├── data-integrity.test.ts
│       └── rollback.test.ts
├── scripts/
│   └── test-migration.sh
├── .github/
│   └── workflows/
│       └── test-migration.yml
└── docs/
    └── testing/
        └── migration-test-suite.md (this file)

---

Usage Examples

Before Migration

# Run complete test suite to verify environment
./scripts/test-migration.sh

# Verify migration scripts exist
test -f scripts/migrate-learning-schema.ts
test -f scripts/migrate-patterns-table.ts

During Migration

# Run dry-run tests
npm run test:unit -- --testPathPattern=database-migration -t "dry-run"

# Verify data integrity
npm run test:integration -- --testPathPattern=data-integrity

After Migration

# Verify rollback capability
npm run test:integration -- --testPathPattern=rollback

# Check system functionality
npm run test:integration -- --testPathPattern=data-integrity -t "functional"

---

Troubleshooting

Test Failures

Checksum mismatch:

# Re-run with verbose output
npm run test:unit -- --testPathPattern=database-migration --verbose

Performance issues:

# Check database size
ls -lh .agentic-qe/agentdb.db

# Run performance benchmarks
npm run test:integration -- --testPathPattern=data-integrity -t "performance"

Rollback failures:

# Verify backup integrity
npm run test:integration -- --testPathPattern=backup-restore -t "verification"

---

Next Steps

After all tests pass:

1. ✅ Review test coverage report
2. ✅ Run migration in staging environment
3. ✅ Perform manual verification
4. ✅ Document any edge cases found
5. ✅ Update migration scripts if needed
6. ✅ Verify production readiness

---

Maintenance

Adding New Tests

1. Follow existing test structure
2. Use descriptive test names
3. Include cleanup in afterEach
4. Document edge cases
5. Update this documentation

Updating Tests

1. Maintain backward compatibility
2. Update version numbers appropriately
3. Run full suite after changes
4. Update CI workflow if needed

---

Contact

For questions or issues with migration tests:

• Review test output for specific failures
• Check CI logs for detailed error messages
• Refer to migration script documentation

---

Last Updated: 2025-11-16 Version: 1.0 Status: ✅ Complete and ready for use