archiving-databases

# Database Archival System

This skill provides automated assistance for database archival system tasks.

## Prerequisites

Before using this skill, ensure:
- Database credentials with SELECT and DELETE permissions on source tables
- Access to destination storage (archive table or cloud storage credentials)
- Network connectivity to cloud storage services if using S3/Azure/GCS
- Backup of database before first archival run
- Understanding of data retention requirements and compliance policies
- Monitoring tools configured to track archival job success

## Instructions

### Step 1: Define Archival Criteria
1. Identify tables containing historical data for archival
2. Define age threshold for archival (e.g., records older than 1 year)
3. Determine additional criteria (status flags, record size, access frequency)
4. Calculate expected data volume to be archived
5. Document business requirements and compliance policies

### Step 2: Choose Archival Destination
1. Evaluate options: archive table in same database, separate archive database, or cold storage
2. For cloud storage: select S3, Azure Blob, or GCS based on infrastructure
3. Configure destination storage with appropriate security and access controls
4. Set up compression settings for storage efficiency
5. Define data format for archived records (CSV, Parquet, JSON)

### Step 3: Create Archive Schema
1. Design archive table schema matching source table structure
2. Add metadata columns (archived_at, source_table, archive_reason)
3. Create indexes on commonly queried archive columns
4. For cloud storage: define bucket structure and naming conventions
5. Test archive schema with sample data

### Step 4: Implement Archival Logic
1. Write SQL query to identify records meeting archival criteria
2. Create extraction script to export records from source tables
3. Implement transformation logic if archive format differs from source
4. Build verification queries to confirm data integrity after archival
5. Add transaction ha