# Database Migration Guide

This guide covers the database migration system for the Agent Hub plugin, including the automatic multi-step migration and recovery procedures.

## Table of Contents

1. [Overview](#overview)
2. [Automatic Migration Process](#automatic-migration-process)
3. [Recovery Scenarios](#recovery-scenarios)
4. [Multi-Webapp Scenarios](#multi-webapp-scenarios)
5. [Manual Recovery (Worst Case)](#manual-recovery-worst-case)

---

## Overview

The migration system uses Alembic with a **split migration strategy** that automatically runs when the plugin starts. The migration is divided into 4 sequential steps, each designed to be safe and reversible.

### Migration Steps

| Step | Revision | Description | Backward Compatible? |
|------|----------|-------------|---------------------|
| 1 | `33647ca7796c` | Add new columns | ✅ Yes - old plugin ignores new columns |
| 2 | `34647ca7796c` | Create new tables | ✅ Yes - old plugin ignores new tables |
| 3 | `35647ca7796c` | Schema alterations & data migration | ⚠️ Partial - downgrade available |
| 4 | `43647ca7796c` | Cleanup old artifacts | ⚠️ Partial - downgrade available |
| 5 | `53647ca7796d` | Memory/tool validation columns | ✅ Yes - simple column addition |

> **Note:** Revision `43647ca7796c` is preserved as Step 4 to maintain compatibility with existing development databases. Databases already at `43647ca7796c` will only run the memory migration (Step 5).

### Key Safety Features

- **Automatic backups** before migration (SQLite and MySQL)
- **Incremental steps** - each step is a separate transaction
- **Backward compatible early steps** - if Step 1-2 fail, just downgrade plugin
- **Proper downgrade functions** - each step can be reversed

---

## Automatic Migration Process

**No manual intervention required for normal operation.**

When you upgrade the plugin and restart the webapp:

1. **Backup is created automatically**
   - SQLite: `database.db.migration_backup.YYYYMMDD_HHMMSS`
   - MySQL: `database.migration_backup.YYYYMMDD_HHMMSS.sql`

2. **Migration steps run automatically**
   ```
   [Migration Step 1/4] Adding new columns to existing tables...
     Adding llm_id to conversations
     Adding agents_enabled to conversations
   [Migration Step 1/4] Complete - new columns added
   
   [Migration Step 2/4] Creating new tables...
     Creating derived_documents table
     Creating admin_settings table
   [Migration Step 2/4] Complete - new tables created
   
   [Migration Step 3/4] Schema alterations and data migration...
     Processing agents table...
     Processing conversations table...
   [Migration Step 3/4] Complete - schema alterations done
   
   [Migration Step 4/4] Cleaning up legacy artifacts...
     Dropping legacy schema_migrations table
   [Migration Step 4/4] Complete - migration finished!
   
   ============================================================
   MIGRATION COMPLETE
   ============================================================
   ```

3. **Backup is cleaned up on success**

---

## Recovery Scenarios

### Scenario 1: Migration Failed at Step 1 or 2

**These steps are fully backward compatible.** Old plugin versions will work.

**Recovery:**
1. Simply downgrade the plugin to the previous version
2. The extra columns/tables will be ignored by old code
3. When ready, upgrade again and migration will resume

### Scenario 2: Migration Failed at Step 3 or 4

**Recovery:**
1. Check the error message in the logs
2. Downgrade plugin version first
3. If old plugin doesn't work, run the downgrade:
   ```bash
   # Set environment variables
   export DATABASE_URL="sqlite:///path/to/database.db"
   export DB_FOLDER_PATH="/path/to/workload"
   
   # Run downgrade
   alembic downgrade base
   ```
4. Old plugin should now work

### Scenario 3: Database Corrupted / Unrecoverable

**Recovery from backup:**

For SQLite:
```bash
# Find the backup
ls -la database.db.migration_backup.*

# Restore it
cp database.db.migration_backup.YYYYMMDD_HHMMSS database.db
```

For MySQL:
```bash
mysql -u user -p database < database.migration_backup.YYYYMMDD_HHMMSS.sql
```

---

## Multi-Webapp Scenarios

When you have multiple webapps in the same DSS instance:

### Challenge

Each webapp has its own database that may be in a different state:
- Webapp A: Not yet migrated
- Webapp B: Partially migrated (failed at step 3)
- Webapp C: Fully migrated

### What Happens Automatically

When you upgrade the plugin:
1. Each webapp migrates **independently** when first accessed
2. If one webapp fails, others are not affected
3. Each webapp has its own backup

### Handling Partial Failures

If Webapp B failed during migration:

1. **Check its state** - Look at the logs to see which step failed
2. **Decide:**
   - If Step 1-2: Just restart, migration will skip completed steps
   - If Step 3-4: May need manual downgrade for that webapp only

### Rollback Strategy

To rollback ALL webapps to previous plugin version:

1. **Downgrade plugin version** (reinstall old plugin)
2. **For each webapp that completed migration:**
   - If only Step 1-2 completed: No action needed (backward compatible)
   - If Step 3-4 completed: Run downgrade for that webapp:
     ```bash
     export DATABASE_URL="sqlite:///path/to/webapp_x/database.db"
     alembic downgrade base
     ```

---

## Manual Recovery (Worst Case)

If all automatic mechanisms fail:

### Check Current State

```bash
# For SQLite
sqlite3 database.db ".tables"
sqlite3 database.db "SELECT * FROM alembic_version;"

# For MySQL
mysql -u user -p -e "SHOW TABLES;" database
mysql -u user -p -e "SELECT * FROM alembic_version;" database
```

### Force Reset Alembic Version

If stuck at a specific migration step:

```bash
# Clear version and re-run
sqlite3 database.db "DELETE FROM alembic_version;"
# or for MySQL:
mysql -u user -p -e "DELETE FROM alembic_version;" database

# Then restart webapp - migration will start fresh
```

### Restore from Backup

Always keep the backup file until you've verified the migration worked:

```bash
# List backups
ls -la *.migration_backup.*

# Restore
cp database.db.migration_backup.XXXXXXXX_XXXXXX database.db
```

---

## Best Practices

1. **Always have backups** - The system creates them automatically, but verify they exist
2. **Test in staging first** - Clone production database and test upgrade
3. **Check logs** - Migration progress is logged clearly
4. **Don't panic on failure** - Early steps are backward compatible
5. **One webapp at a time** - If concerned, access webapps one at a time after upgrade
