proffesor-for-testing__agentic-qe/docs/database/backup-strategy.md

Database Backup Strategy

Overview

This document describes the comprehensive backup and restore system for the Agentic QE Fleet database infrastructure. The system ensures zero data loss during migrations, updates, and critical operations.

Components

1. Backup Scripts

/scripts/backup-databases.sh

Purpose: Create verified backups of all databases with checksums

Usage:

./scripts/backup-databases.sh [--compress] [--verify]

Options:

• --compress: Create compressed tar.gz archive of backups
• --no-verify: Skip checksum verification (not recommended)

Features:

• ✅ SHA-256 checksums for integrity verification
• ✅ Metadata tracking (timestamp, git branch, commit)
• ✅ Automatic cleanup (keeps last 10 backups by default)
• ✅ File size reporting
• ✅ Supports agentdb.db and all .agentic-qe/*.db files

Output:

.agentic-qe/backups/
├── agentdb.db.backup.20250116-120000
├── agentdb.db.backup.20250116-120000.sha256
├── backup-metadata.20250116-120000.json
├── qe-learning.db.backup.20250116-120000
├── qe-learning.db.backup.20250116-120000.sha256
└── databases-backup-20250116-120000.tar.gz (if --compress)

/scripts/restore-databases.sh

Purpose: Restore databases from backup with verification

Usage:

./scripts/restore-databases.sh <timestamp> [--verify] [--force]

Options:

• --no-verify: Skip checksum verification before restore
• --force: Skip confirmation prompt (for automation)

Safety Features:

• ⚠️ Creates safety backup of current state before restore
• ✅ Checksum verification before applying restore
• ✅ Interactive confirmation (unless --force)
• ✅ Detailed restore summary

Example:

# List available backups
./scripts/restore-databases.sh

# Restore specific backup
./scripts/restore-databases.sh 20250116-120000

# Force restore (no prompts)
./scripts/restore-databases.sh 20250116-120000 --force

/scripts/manage-backups.sh

Purpose: Manage, verify, and analyze backups

Commands:

# List all backups with metadata
./scripts/manage-backups.sh list

# Clean old backups (keep last N)
./scripts/manage-backups.sh clean 10

# Verify backup integrity
./scripts/manage-backups.sh verify 20250116-120000

# Compare backup with current state
./scripts/manage-backups.sh compare 20250116-120000

# Show backup details
./scripts/manage-backups.sh info 20250116-120000

# Show storage usage
./scripts/manage-backups.sh size

2. Programmatic API

src/scripts/backup-helper.ts

TypeScript helper for integration with migration scripts and application code.

Key Methods:

import { backupHelper } from './src/scripts/backup-helper';

// Create backup
const result = await backupHelper.createBackup({
  compress: true,
  verify: true
});

// Restore from backup
const restore = await backupHelper.restoreBackup(timestamp, {
  verify: true,
  force: true
});

// List backups
const backups = await backupHelper.listBackups();

// Verify integrity
const isValid = await backupHelper.verifyBackup(timestamp);

// Clean old backups
const removed = await backupHelper.cleanOldBackups(10);

// Safety backup before dangerous operation
const safety = await backupHelper.createSafetyBackup('migration');

3. Automated Backups

.github/workflows/backup-databases.yml

GitHub Actions workflow for automated daily backups.

Schedule: Daily at 2 AM UTC

Features:

• Automatic backup creation
• Upload to GitHub Artifacts (30-day retention)
• Backup verification
• Automatic cleanup of old backups
• Failure notifications (creates GitHub issue)
• Manual trigger support

Manual Trigger:

# Via GitHub UI: Actions > Daily Database Backup > Run workflow

# Via gh CLI:
gh workflow run backup-databases.yml

Backup Strategy

Retention Policy

Backup Type	Retention	Purpose
Daily automated	30 days	Regular snapshots
Manual backups	Until manually deleted	Important milestones
Safety backups	7 days	Pre-operation safety net
Compressed archives	90 days	Long-term storage

Storage Locations

1. Local: .agentic-qe/backups/ (development)
2. CI/CD: GitHub Artifacts (automated backups)
3. Production: External backup service (recommended)

Backup Frequency

• Automated: Daily (2 AM UTC)
• Before migration: Automatic via migration script
• Before major updates: Manual backup recommended
• Before dangerous operations: Automatic safety backup

Security

Checksum Verification

All backups use SHA-256 checksums for integrity verification:

# Create backup with checksum
sha256sum agentdb.db > backup.db.sha256

# Verify backup
sha256sum -c backup.db.sha256

Metadata Tracking

Each backup includes metadata for audit trail:

{
  "timestamp": "20250116-120000",
  "date": "2025-01-16T12:00:00Z",
  "hostname": "dev-machine",
  "user": "developer",
  "git_commit": "4a58796",
  "git_branch": "main",
  "compressed": true
}

Migration Integration

The backup system integrates with migration scripts:

// In migrate-to-agentdb.ts
import { backupHelper } from './src/scripts/backup-helper';

async function runMigration() {
  // 1. Create safety backup
  console.log('Creating pre-migration backup...');
  const backup = await backupHelper.createSafetyBackup('agentdb-migration');

  if (!backup.success) {
    console.error('Backup failed! Aborting migration.');
    process.exit(1);
  }

  // 2. Run migration
  try {
    await performMigration();
  } catch (error) {
    // 3. Restore on failure
    console.error('Migration failed! Restoring backup...');
    await backupHelper.restoreBackup(backup.timestamp, {
      force: true
    });
    throw error;
  }

  // 4. Verify migration success
  await verifyMigration();
}

Disaster Recovery

Recovery Procedures

1. Data Corruption Detected

# List recent backups
./scripts/manage-backups.sh list

# Verify specific backup
./scripts/manage-backups.sh verify 20250116-120000

# Restore
./scripts/restore-databases.sh 20250116-120000

2. Accidental Deletion

# Restore from most recent backup
latest=$(ls -1 .agentic-qe/backups/*.backup.* | \
  sed 's/.*backup\.//' | cut -d'.' -f1 | sort -r | head -1)
./scripts/restore-databases.sh $latest

3. Failed Migration

# Safety backup is automatically created
# Check restore instructions in migration output
./scripts/restore-databases.sh <safety-timestamp> --force

Recovery Time Objectives (RTO)

Scenario	Target RTO	Procedure
Single file corruption	< 5 minutes	Restore from backup
Complete database loss	< 15 minutes	Restore from latest backup
Failed migration	< 2 minutes	Automatic safety restore
Backup corruption	< 30 minutes	Restore from previous backup

Testing

Integration Tests

# Run backup/restore tests
npm run test:integration -- backup-restore.test.ts

Manual Testing

# 1. Create test database
sqlite3 test.db "CREATE TABLE test(id INTEGER)"

# 2. Create backup
./scripts/backup-databases.sh --verify

# 3. Modify database
sqlite3 test.db "DROP TABLE test"

# 4. Restore
./scripts/restore-databases.sh <timestamp> --force

# 5. Verify restoration
sqlite3 test.db "SELECT * FROM test"

Monitoring

Backup Health Checks

# Check backup storage
./scripts/manage-backups.sh size

# Verify all backups
./scripts/manage-backups.sh verify

# Check for old backups
./scripts/manage-backups.sh list

Alerts

GitHub Actions workflow creates issues for:

• Backup failures
• Verification failures
• Storage quota warnings

Best Practices

DO ✅

• Always create backup before migration
• Verify backups regularly
• Test restore procedures
• Monitor backup storage
• Use compression for long-term storage
• Keep multiple backup generations
• Document recovery procedures

DON'T ❌

• Skip backup verification
• Delete backups manually without checking
• Ignore backup failures
• Run migrations without backup
• Store backups only locally
• Exceed storage quotas
• Forget to test restore procedures

Troubleshooting

Issue: Backup Verification Failed

# Check file integrity
./scripts/manage-backups.sh verify <timestamp>

# Compare with current state
./scripts/manage-backups.sh compare <timestamp>

# Create new backup
./scripts/backup-databases.sh --verify

Issue: Insufficient Storage

# Check current usage
./scripts/manage-backups.sh size

# Clean old backups
./scripts/manage-backups.sh clean 5

# Remove compressed archives
rm .agentic-qe/backups/*.tar.gz

Issue: Restore Confirmation Timeout

# Use force flag
./scripts/restore-databases.sh <timestamp> --force

Automation

Pre-commit Hook

# .git/hooks/pre-push
#!/bin/bash
./scripts/backup-databases.sh --compress

Cron Job

# Daily backup at 2 AM
0 2 * * * /path/to/scripts/backup-databases.sh --compress --verify

CI/CD Integration

# In your CI/CD pipeline
- name: Create backup
  run: ./scripts/backup-databases.sh --compress

- name: Run tests
  run: npm test

- name: Restore on failure
  if: failure()
  run: ./scripts/restore-databases.sh $BACKUP_TIMESTAMP --force

Compliance

Data Retention

Backups comply with data retention policies:

• Development: 30 days
• Staging: 60 days
• Production: 90 days

Encryption

For production environments:

# Encrypt backup
gpg --encrypt --recipient admin@example.com backup.tar.gz

# Decrypt backup
gpg --decrypt backup.tar.gz.gpg > backup.tar.gz

References

• SQLite Backup API
• GitHub Artifacts Documentation
• Data Recovery Best Practices

Change Log

Date	Version	Changes
2025-01-16	1.0.0	Initial backup system implementation

Support

For issues or questions:

• GitHub Issues: agentic-qe-cf/issues
• Documentation: docs/database/