# Deployment Checklist: Secure Database Implementation

Use this checklist when deploying the secure database implementation to production.

## Pre-Deployment

### 1. Environment Preparation
- [ ] Generate encryption key: `openssl rand -hex 32`
- [ ] Generate JWT secret: `openssl rand -hex 32`
- [ ] Save keys securely (password manager, secrets vault, etc.)
- [ ] Add keys to environment configuration (docker-compose.yml or .env)
- [ ] Verify keys are 64 characters (hex format)

### 2. Backup Current System
- [ ] Backup current JSON files (users.json, user-sessions.json, affiliates.json)
- [ ] Document current user count
- [ ] Document current session count
- [ ] Create rollback plan
- [ ] Test backup restoration procedure

### 3. Testing in Staging
- [ ] Deploy to staging environment
- [ ] Verify database initialization
- [ ] Test user creation
- [ ] Test session management
- [ ] Test encryption/decryption
- [ ] Test migration (if applicable)
- [ ] Verify audit logging
- [ ] Test rollback to JSON mode

## Deployment

### Option A: New Deployment (No Existing Data)

```bash
# 1. Set environment variables
export DATABASE_ENCRYPTION_KEY=<your-64-char-hex-key>
export JWT_SECRET=<your-64-char-hex-key>

# 2. Deploy container
docker-compose up -d

# 3. Verify logs
docker logs shopify-ai-builder | grep "Database setup complete"

# Expected output:
# ✅ Database setup complete!
```

**Checklist:**
- [ ] Database created at `.data/shopify_ai.db`
- [ ] Encryption keys saved to `.data/.encryption_key` and `.data/.jwt_secret`
- [ ] All 10 tables created
- [ ] No errors in logs

### Option B: Migration from JSON Files

```bash
# 1. Keep JSON mode active initially
export USE_JSON_DATABASE=1
export DATABASE_ENCRYPTION_KEY=<your-64-char-hex-key>
export JWT_SECRET=<your-64-char-hex-key>

# 2. Deploy container
docker-compose up -d

# 3. Verify JSON mode is active
docker logs shopify-ai-builder | grep "JSON"
# Expected: "Running in JSON compatibility mode"

# 4. Run migration inside container
docker exec shopify-ai-builder node /opt/webchat/scripts/migrate-to-database.js

# 5. Verify migration results
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db "SELECT COUNT(*) FROM users;"
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db "SELECT COUNT(*) FROM sessions;"

# 6. Switch to database mode
export USE_JSON_DATABASE=0
docker-compose restart shopify-ai-builder

# 7. Verify database mode
docker logs shopify-ai-builder | grep -v "JSON"
```

**Checklist:**
- [ ] Migration backup created in `.data/migration_backup_*`
- [ ] All users migrated (verify count matches)
- [ ] All sessions migrated (verify count matches)
- [ ] All affiliates migrated (verify count matches)
- [ ] Database mode active (no "JSON" in logs)
- [ ] Application functioning normally

## Post-Deployment

### 1. Verification
- [ ] Database file exists and is accessible
- [ ] Encryption keys persisted
- [ ] Users can log in
- [ ] Sessions are created
- [ ] Audit log is recording events
- [ ] No errors in application logs

### 2. Smoke Tests
```bash
# Test database access
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db ".tables"

# Count records
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db "SELECT COUNT(*) FROM users;"
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db "SELECT COUNT(*) FROM sessions;"
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db "SELECT COUNT(*) FROM audit_log;"

# Check encryption keys
docker exec shopify-ai-builder test -f /home/web/data/.data/.encryption_key && echo "Encryption key exists"
docker exec shopify-ai-builder test -f /home/web/data/.data/.jwt_secret && echo "JWT secret exists"
```

**Checklist:**
- [ ] All tables exist
- [ ] Record counts match expectations
- [ ] Encryption keys exist
- [ ] Database size is reasonable

### 3. Functional Tests
- [ ] Create a new user
- [ ] Log in with user
- [ ] Verify session created in database
- [ ] Log out
- [ ] Verify session removed/expired
- [ ] Check audit log entries

### 4. Security Verification
```bash
# Check file permissions
docker exec shopify-ai-builder ls -l /home/web/data/.data/

# Verify database is encrypted (should see binary data)
docker exec shopify-ai-builder head -c 100 /home/web/data/.data/shopify_ai.db | od -c

# Check audit log
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db \
  "SELECT event_type, COUNT(*) FROM audit_log GROUP BY event_type;"
```

**Checklist:**
- [ ] Database file permissions are restrictive
- [ ] Encryption key file permissions are 600
- [ ] Database file is binary (not plain text)
- [ ] Audit log is capturing events

### 5. Performance Check
- [ ] Application response time normal
- [ ] Database query time < 1ms for typical operations
- [ ] WAL mode active: `PRAGMA journal_mode;` returns `wal`
- [ ] No database locks or conflicts
- [ ] Memory usage stable

### 6. Monitoring Setup
- [ ] Database size monitoring enabled
- [ ] Error log monitoring enabled
- [ ] Audit log review scheduled
- [ ] Backup schedule configured
- [ ] Alert thresholds set

## Rollback Procedure

If issues occur, follow this rollback procedure:

```bash
# 1. Stop the container
docker stop shopify-ai-builder

# 2. Set JSON mode
export USE_JSON_DATABASE=1

# 3. Verify JSON files exist
docker exec shopify-ai-builder ls -l /home/web/data/.data/*.json

# 4. If JSON files missing, restore from backup
docker cp backup/users.json shopify-ai-builder:/home/web/data/.data/
docker cp backup/user-sessions.json shopify-ai-builder:/home/web/data/.data/
docker cp backup/affiliates.json shopify-ai-builder:/home/web/data/.data/

# 5. Restart in JSON mode
docker-compose up -d

# 6. Verify JSON mode active
docker logs shopify-ai-builder | grep "JSON compatibility mode"
```

**Rollback Checklist:**
- [ ] JSON mode activated
- [ ] JSON files present
- [ ] Application functioning
- [ ] Users can log in
- [ ] No data loss confirmed

## Post-Rollback

If rollback was necessary:

- [ ] Document the issue encountered
- [ ] Review logs for error messages
- [ ] Test in staging environment again
- [ ] Fix identified issues
- [ ] Plan next deployment attempt

## Maintenance

### Daily
- [ ] Check application logs for errors
- [ ] Verify database is accessible
- [ ] Check disk space

### Weekly
- [ ] Review audit logs
- [ ] Check database size growth
- [ ] Verify backups are running
- [ ] Review performance metrics

### Monthly
- [ ] Rotate audit logs if needed
- [ ] Clean up expired sessions
- [ ] Clean up expired blacklist entries
- [ ] Review security events

### Quarterly
- [ ] Update dependencies
- [ ] Security audit
- [ ] Performance review
- [ ] Disaster recovery test

## Troubleshooting

### Database Not Found
```bash
# Check if database file exists
docker exec shopify-ai-builder ls -l /home/web/data/.data/

# Re-run initialization
docker exec shopify-ai-builder node /opt/webchat/scripts/init-database.js
```

### Encryption Error
```bash
# Verify key exists and is correct length
docker exec shopify-ai-builder cat /home/web/data/.data/.encryption_key | wc -c
# Should output 65 (64 chars + newline)

# Check environment variable
docker exec shopify-ai-builder printenv DATABASE_ENCRYPTION_KEY
```

### Migration Failed
```bash
# Check backup directory
docker exec shopify-ai-builder ls -l /home/web/data/.data/migration_backup_*/

# Restore from backup if needed
docker exec shopify-ai-builder cp -r /home/web/data/.data/migration_backup_*/* /home/web/data/.data/
```

### Performance Issues
```bash
# Check WAL mode
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db "PRAGMA journal_mode;"

# Check database size
docker exec shopify-ai-builder du -h /home/web/data/.data/shopify_ai.db

# Vacuum database if needed (offline)
docker stop shopify-ai-builder
docker exec shopify-ai-builder sqlite3 /home/web/data/.data/shopify_ai.db "VACUUM;"
docker start shopify-ai-builder
```

## Support Contacts

- **Development Team**: [contact info]
- **DevOps Team**: [contact info]
- **Security Team**: [contact info]
- **On-Call**: [contact info]

## Documentation References

- Implementation Guide: `chat/DATABASE_IMPLEMENTATION.md`
- Testing Guide: `chat/TESTING_GUIDE.md`
- Implementation Summary: `IMPLEMENTATION_COMPLETE_SUMMARY.md`
- Security Plan: `.opencode/plans/IMPLEMENTATION_PLAN_1.2_1.3.md`

---

**Last Updated**: 2026-02-09
**Version**: 1.0
**Status**: Production Ready ✅