# Runbook: Database Backup and Recovery

## Overview

ClawAI uses 9 PostgreSQL databases and 1 MongoDB instance (3 databases). Each service owns its database. This runbook covers backup, restore, and migration rollback procedures.

## PostgreSQL Databases

| Database          | Service    | Contains                           |
| ----------------- | ---------- | ---------------------------------- |
| claw_auth         | auth       | Users, sessions, settings          |
| claw_chat         | chat       | Threads, messages, attachments     |
| claw_connectors   | connector  | Connectors, models, health events  |
| claw_routing      | routing    | Decisions, policies                |
| claw_memory       | memory     | Memory records, context packs      |
| claw_files        | file       | File metadata, chunks              |
| claw_ollama       | ollama     | Local models, pull jobs, catalog   |
| claw_images       | image      | Image generation records           |
| claw_file_gen     | file-gen   | File generation records            |

### Backup a Single Database

```bash
# Identify the postgres container
./scripts/claw.sh ps | grep postgres

# Backup using pg_dump
./scripts/claw.sh exec postgres-chat \
  pg_dump -U claw_chat_user claw_chat > backup_chat_$(date +%Y%m%d_%H%M%S).sql
```

### Backup All PostgreSQL Databases

```bash
#!/bin/bash
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="./backups/$TIMESTAMP"
mkdir -p "$BACKUP_DIR"

declare -A DBS=(
  ["postgres-auth"]="claw_auth:claw_auth_user"
  ["postgres-chat"]="claw_chat:claw_chat_user"
  ["postgres-connector"]="claw_connectors:claw_connectors_user"
  ["postgres-routing"]="claw_routing:claw_routing_user"
  ["postgres-memory"]="claw_memory:claw_memory_user"
  ["postgres-file"]="claw_files:claw_files_user"
  ["postgres-ollama"]="claw_ollama:claw_ollama_user"
  ["postgres-image"]="claw_images:claw_images_user"
  ["postgres-file-gen"]="claw_file_gen:claw_file_gen_user"
)

for container in "${!DBS[@]}"; do
  IFS=':' read -r db user <<< "${DBS[$container]}"
  echo "Backing up $db..."
  ./scripts/claw.sh exec -T "$container" \
    pg_dump -U "$user" "$db" > "$BACKUP_DIR/${db}.sql"
done
echo "Backups saved to $BACKUP_DIR"
```

### Restore a PostgreSQL Database

```bash
# Stop the service that uses the database
./scripts/claw.sh stop chat-service

# Drop and recreate the database
./scripts/claw.sh exec postgres-chat \
  psql -U claw_chat_user -d postgres -c "DROP DATABASE IF EXISTS claw_chat;"
./scripts/claw.sh exec postgres-chat \
  psql -U claw_chat_user -d postgres -c "CREATE DATABASE claw_chat OWNER claw_chat_user;"

# Restore from backup
./scripts/claw.sh exec -T postgres-chat \
  psql -U claw_chat_user claw_chat < backup_chat_20250101_120000.sql

# Restart the service
./scripts/claw.sh start chat-service
```

### Prisma Migration Rollback

If a migration caused issues:

```bash
# Check current migration status
./scripts/claw.sh exec chat-service \
  npx prisma migrate status

# List applied migrations
./scripts/claw.sh exec chat-service \
  npx prisma migrate status --schema=prisma/schema.prisma

# Mark a migration as rolled back (manual SQL required)
# 1. Write the reverse SQL
# 2. Execute it against the database
# 3. Delete the migration record from _prisma_migrations table:
./scripts/claw.sh exec postgres-chat \
  psql -U claw_chat_user claw_chat -c \
  "DELETE FROM _prisma_migrations WHERE migration_name = '20250101120000_problematic_migration';"

# 4. Delete the migration directory from prisma/migrations/
# 5. Restart the service
```

## MongoDB Databases

| Database  | Service      | Contains                    |
| --------- | ------------ | --------------------------- |
| claw_audit| audit        | Audit logs, usage ledger    |
| claw_logs | client-logs  | Frontend logs (TTL 30d)     |
| claw_logs | server-logs  | Backend logs (TTL 30d)      |

### Backup MongoDB

```bash
# Backup all MongoDB databases
./scripts/claw.sh exec mongodb \
  mongodump --username claw_user --password "$MONGO_PASSWORD" \
  --authenticationDatabase admin --out /tmp/mongodump

# Copy to host
./scripts/claw.sh cp \
  mongodb:/tmp/mongodump ./backups/mongodump_$(date +%Y%m%d)
```

### Restore MongoDB

```bash
# Copy backup to container
./scripts/claw.sh cp \
  ./backups/mongodump_20250101 mongodb:/tmp/mongodump

# Restore
./scripts/claw.sh exec mongodb \
  mongorestore --username claw_user --password "$MONGO_PASSWORD" \
  --authenticationDatabase admin /tmp/mongodump

# Restart affected services
./scripts/claw.sh restart audit-service client-logs-service server-logs-service
```

## Data Volume Management

### List Volumes

```bash
docker volume ls | grep claw
```

### Volume Backup (Complete)

```bash
# Backup a named volume
docker run --rm -v claw_postgres-chat-data:/data -v $(pwd)/backups:/backup \
  alpine tar czf /backup/postgres-chat-data.tar.gz -C /data .
```

### Volume Restore

```bash
# Restore a named volume
docker run --rm -v claw_postgres-chat-data:/data -v $(pwd)/backups:/backup \
  alpine sh -c "cd /data && tar xzf /backup/postgres-chat-data.tar.gz"
```

## Emergency: Complete Data Reset

If you need to start fresh (destroys all data):

```bash
# Stop everything
./scripts/claw.sh down

# Remove all volumes
./scripts/claw.sh down -v

# Start fresh
./scripts/claw.sh up -d

# Re-run seeds
./scripts/claw.sh exec ollama-service npx tsx prisma/seed-catalog.ts
```

## Prevention

- Schedule daily backups of all PostgreSQL databases
- MongoDB logs have a 30-day TTL and do not need regular backups
- Test restore procedures quarterly
- Never run `prisma migrate dev` in production -- use `prisma migrate deploy`
- Keep migration files in version control -- never delete applied migrations
