quality_app/documentation/BACKUP_SYSTEM.md

Database Backup System Documentation

Overview

The Quality Recticel application now includes a comprehensive database backup management system accessible from the Settings page for superadmin and admin users.

Features

1. Manual Backup

• Backup Now button creates an immediate full database backup
• Uses mysqldump to create complete SQL export
• Includes all tables, triggers, routines, and events
• Each backup is timestamped: backup_trasabilitate_YYYYMMDD_HHMMSS.sql

2. Scheduled Backups

Configure automated backups with:

• Enable/Disable: Toggle scheduled backups on/off
• Backup Time: Set time of day for automatic backup (default: 02:00)
• Frequency: Choose Daily, Weekly, or Monthly backups
• Retention Period: Automatically delete backups older than N days (default: 30 days)

3. Backup Management

• List Backups: View all available backup files with size and creation date
• Download: Download any backup file to your local computer
• Delete: Remove old or unnecessary backup files
• Restore: (Superadmin only) Restore database from a backup file

Configuration

Backup Path

The backup location can be configured in three ways (priority order):

1. Environment Variable (Docker):

   # docker-compose.yml
   environment:
     BACKUP_PATH: /srv/quality_recticel/backups
   volumes:
     - /srv/docker-test/backups:/srv/quality_recticel/backups
   

2. Configuration File:

   # py_app/instance/external_server.conf
   backup_path=/srv/quality_app/backups
   

3. Default Path: /srv/quality_app/backups

.env Configuration

Add to your .env file:

BACKUP_PATH=/srv/docker-test/backups

Usage

Access Backup Management

1. Login as superadmin or admin
2. Navigate to Settings page
3. Scroll to 💾 Database Backup Management card
4. The backup management interface is only visible to superadmin/admin users

Create Manual Backup

1. Click ⚡ Backup Now button
2. Wait for confirmation message
3. New backup appears in the list

Configure Scheduled Backups

1. Check Enable Scheduled Backups
2. Set desired backup time (24-hour format)
3. Select frequency (Daily/Weekly/Monthly)
4. Set retention period (days to keep backups)
5. Click 💾 Save Schedule

Download Backup

1. Locate backup in the list
2. Click ⬇️ Download button
3. File downloads to your computer

Delete Backup

1. Locate backup in the list
2. Click 🗑️ Delete button
3. Confirm deletion

Restore Backup (Superadmin Only)

⚠️ WARNING: Restore will replace current database!

1. This feature requires superadmin privileges
2. API endpoint: /api/backup/restore/<filename>
3. Use with extreme caution

Technical Details

Backup Module

Location: py_app/app/database_backup.py

Key Class: DatabaseBackupManager

Methods:

• create_backup(): Create new backup
• list_backups(): Get all backup files
• delete_backup(filename): Remove backup file
• restore_backup(filename): Restore from backup
• get_backup_schedule(): Get current schedule
• save_backup_schedule(schedule): Update schedule
• cleanup_old_backups(days): Remove old backups

API Endpoints

Endpoint	Method	Access	Description
/api/backup/create	POST	Admin+	Create new backup
/api/backup/list	GET	Admin+	List all backups
/api/backup/download/<filename>	GET	Admin+	Download backup file
/api/backup/delete/<filename>	DELETE	Admin+	Delete backup file
/api/backup/schedule	GET/POST	Admin+	Get/Set backup schedule
/api/backup/restore/<filename>	POST	Superadmin	Restore from backup

Backup File Format

• Format: SQL dump file (.sql)
• Compression: Not compressed (can be gzip manually if needed)
• Contents: Complete database with structure and data
• Metadata: Stored in backups_metadata.json

Schedule Storage

Schedule configuration stored in: {BACKUP_PATH}/backup_schedule.json

Example:

{
  "enabled": true,
  "time": "02:00",
  "frequency": "daily",
  "retention_days": 30
}

Security Considerations

1. Access Control: Backup features restricted to admin and superadmin users
2. Path Traversal Protection: Filenames validated to prevent directory traversal attacks
3. Credentials: Database credentials read from external_server.conf
4. Backup Location: Should be on different mount point than application for safety

Maintenance

Disk Space

Monitor backup directory size:

du -sh /srv/quality_app/backups

Manual Cleanup

Remove old backups manually:

find /srv/quality_app/backups -name "*.sql" -mtime +30 -delete

Backup Verification

Test restore in development environment:

mysql -u root -p trasabilitate < backup_trasabilitate_20251103_020000.sql

Troubleshooting

Backup Fails

• Check database credentials in external_server.conf
• Ensure mysqldump is installed
• Verify write permissions on backup directory
• Check disk space availability

Scheduled Backups Not Running

• TODO: Implement scheduled backup daemon/cron job
• Check backup schedule is enabled
• Verify time format is correct (HH:MM)

Cannot Download Backup

• Check backup file exists
• Verify file permissions
• Ensure adequate network bandwidth

Future Enhancements

Planned Features (Task 4)

• Implement APScheduler for automated scheduled backups
• Add backup to external storage (S3, FTP, etc.)
• Email notifications for backup success/failure
• Backup compression (gzip)
• Incremental backups
• Backup encryption
• Backup verification tool

Support

For issues or questions about the backup system:

1. Check application logs: /srv/quality_app/logs/error.log
2. Verify backup directory permissions
3. Test manual backup first before relying on scheduled backups
4. Keep at least 2 recent backups before deleting old ones

---

Created: November 3, 2025
Module: Database Backup Management
Version: 1.0.0