# 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):
   ```yaml
   # docker-compose.yml
   environment:
     BACKUP_PATH: /srv/quality_recticel/backups
   volumes:
     - /srv/docker-test/backups:/srv/quality_recticel/backups
   ```

2. **Configuration File**:
   ```ini
   # 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:
```bash
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:
```json
{
  "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:
```bash
du -sh /srv/quality_app/backups
```

### Manual Cleanup
Remove old backups manually:
```bash
find /srv/quality_app/backups -name "*.sql" -mtime +30 -delete
```

### Backup Verification
Test restore in development environment:
```bash
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