# Database Migrations

This directory contains database migration scripts for the Telegram Bot Workers project.

## Files

| File | Purpose |
|------|---------|
| `001_schema_enhancements.sql` | Migration script - adds CHECK constraints and audit logging |
| `001_rollback.sql` | Rollback script - reverts migration 001 |
| `AUDIT_LOG_EXAMPLES.ts` | TypeScript examples for using audit logs |
| `TEST_RESULTS.md` | Local test results and verification |
| `README.md` | This file |

## Quick Start

### Local Testing

```bash
# Initialize local database
npm run db:init:local

# Add test data
wrangler d1 execute telegram-conversations --local \
  --command "INSERT INTO users (telegram_id, username) VALUES ('123', 'test')"

# Run migration
wrangler d1 execute telegram-conversations --local \
  --file migrations/001_schema_enhancements.sql

# Verify constraints work
wrangler d1 execute telegram-conversations --local \
  --command "INSERT INTO user_deposits (user_id, balance) VALUES (999, -1000)"
# Expected: CHECK constraint failed
```

### Production Deployment

**⚠️ MANDATORY: Read SCHEMA_MIGRATION_GUIDE.md first**

```bash
# 1. Backup database
wrangler d1 execute telegram-conversations \
  --command ".dump" > backup_$(date +%Y%m%d_%H%M%S).sql

# 2. Run migration
wrangler d1 execute telegram-conversations \
  --file migrations/001_schema_enhancements.sql

# 3. Verify success
wrangler d1 execute telegram-conversations \
  --command "SELECT 'Migration completed' as status, datetime('now') as timestamp"
```

## Migration 001: Schema Enhancements

**Date**: 2026-01-19

### Changes

1. **user_deposits**: Add `balance >= 0` CHECK constraint
2. **deposit_transactions**: Add `depositor_name` length <= 50 CHECK constraint
3. **audit_logs**: Create new table for operation tracking

### Benefits

- Prevent negative balances at database level
- Enforce depositor name length limit
- Track all critical operations for compliance and debugging
- Improved data integrity and auditability

### Risk Level

- **Low**: No breaking changes to application code
- **No downtime**: Migration completes in < 1 second for typical datasets
- **Reversible**: Rollback script available

### Files

- Migration: `001_schema_enhancements.sql`
- Rollback: `001_rollback.sql`
- Guide: `../SCHEMA_MIGRATION_GUIDE.md`
- Examples: `AUDIT_LOG_EXAMPLES.ts`
- Tests: `TEST_RESULTS.md`

## Documentation

For detailed information, see:

- **SCHEMA_MIGRATION_GUIDE.md**: Complete deployment guide
- **TEST_RESULTS.md**: Local test results and verification
- **AUDIT_LOG_EXAMPLES.ts**: Usage examples for audit logs

## Support

If issues occur during migration:

1. Check logs: `wrangler tail`
2. Review SCHEMA_MIGRATION_GUIDE.md troubleshooting section
3. Rollback if necessary: `001_rollback.sql`
4. Restore from backup if critical failure