# Database Backups Directory

This directory contains automated PostgreSQL backups for the Ferrero Asset Tracking database.

## Directory Structure

```
backups/
├── dumps/          # Daily SQL dumps (pg_dump) - 7 day retention
├── basebackups/    # Weekly binary backups (pg_basebackup) - latest only
├── safety/         # Pre-restore safety backups
├── manual/         # Manual/ad-hoc backups
└── README.md       # This file
```

## Automated Backup Schedule

- **Daily:** 2:00 AM - SQL dump backup (7 days retained)
- **Weekly:** Sundays 3:00 AM - Full binary backup (latest only)
- **Health Check:** 8:00 AM - Verify backups are fresh

## Quick Commands

### Create Backup
```bash
# Daily backup
../database/backup.sh --daily

# Weekly backup
../database/backup.sh --weekly
```

### Check Backup Status
```bash
# Health check
../database/check_backups.sh

# List all backups
ls -lh dumps/
ls -lh basebackups/
```

### Restore Database
```bash
# List available backups
../database/restore.sh --list

# Restore from backup
../database/restore.sh dumps/ferrero_tracking_2025-11-11_02-00.sql.gz
```

## Backup File Naming

**Daily dumps:**
```
ferrero_tracking_YYYY-MM-DD_HH-MM.sql.gz
Example: ferrero_tracking_2025-11-11_02-00.sql.gz
```

**Weekly basebackups:**
```
ferrero_tracking_YYYY-MM-DD_HH-MM.tar.gz
Example: ferrero_tracking_2025-11-10_03-00.tar.gz
```

## Retention Policy

- **Daily:** Keep last 7 days (auto-cleanup)
- **Weekly:** Keep latest only (auto-cleanup)
- **Safety:** Manual cleanup after restore
- **Manual:** Manual cleanup as needed

## Expected Disk Usage

- Daily dumps: ~14 MB (7 files × ~2 MB)
- Weekly backup: ~30 MB (1 file)
- **Total: ~50 MB maximum**

## Security

⚠️ **Important:** Backup files contain sensitive database data.

**Permissions:**
```bash
chmod 700 backups           # Only owner can access
chmod 600 *.sql.gz          # Backup files read/write owner only
```

**NOT committed to Git:** This directory is in `.gitignore` and will not be pushed to repository.

## Monitoring

### Check Latest Backup Age

```bash
# Find latest daily backup
ls -lt dumps/*.sql.gz | head -1

# Check age in hours
stat -c %Y dumps/*.sql.gz | head -1 | xargs -I {} echo $(( ($(date +%s) - {}) / 3600 )) hours
```

### Verify Backup Worked

```bash
# Check backup log
tail -50 ../logs/backup.log

# Look for success markers
grep "✓" ../logs/backup.log | tail -5
```

## What's Included in Backups

### All Tables:
- `master_assets` - Master asset tracking
- `derivative_assets` - Localized versions
- `creativex_scores` - CreativeX quality scores
- `asset_events` - Audit trail
- `workflow_state` - Workflow tracking
- `campaign_status` - Campaign processing state

### All Data:
- Complete JSONB metadata
- All tracking IDs and relationships
- Campaign references
- Full CreativeX extraction data
- Timestamps and audit information

### Database Schema:
- Table structures
- Indexes
- Functions
- Triggers
- Constraints

## Restore Examples

### Restore Yesterday's Backup

```bash
# Find yesterday's backup
ls -lh dumps/ | grep $(date -d "yesterday" +%Y-%m-%d)

# Restore it
../database/restore.sh dumps/ferrero_tracking_2025-11-10_02-00.sql.gz
```

### Restore After Accidental Data Deletion

```bash
# Immediately create emergency backup of current state
docker exec ferrero-tracking-db pg_dump -U ferrero_user ferrero_tracking | \
  gzip > manual/emergency_before_restore_$(date +%Y-%m-%d_%H-%M).sql.gz

# Restore from last known good backup
../database/restore.sh dumps/ferrero_tracking_2025-11-11_02-00.sql.gz
```

## Documentation

For complete backup/restore procedures, see:
- **DATABASE_BACKUP_GUIDE.md** - Comprehensive guide
- **database/backup.sh** - Backup script source
- **database/restore.sh** - Restore script source
- **database/check_backups.sh** - Health check script

## Support

- **Backup logs:** `logs/backup.log`
- **Restore logs:** `logs/restore.log`
- **Health check logs:** `logs/backup_check.log`
- **Email notifications:** Sent to `ERROR_EMAIL` on failures

---

**Last Updated:** November 11, 2025
**Backup System Version:** 1.0
**Database:** ferrero_tracking (PostgreSQL 15)