04dcb57fae
데이터 무결성: - user_deposits.balance >= 0 CHECK 제약조건 - deposit_transactions.depositor_name 최대 50자 제한 - 음수 잔액 방지, 긴 이름 방지 감사 추적: - audit_logs 테이블 생성 - 모든 중요 작업 추적 (user_id, action, resource, details) - 인덱스 추가 (user_id, action, created_at) 프로덕션 안전: - 백업 → 재생성 → 복원 방식 - 롤백 스크립트 포함 - 데이터 유실 방지 로직 - 음수 잔액 데이터 감지 및 로그 마이그레이션 파일: - migrations/001_schema_enhancements.sql (5.5K) - migrations/001_rollback.sql (4.0K) - migrations/AUDIT_LOG_EXAMPLES.ts (11K) - migrations/TEST_RESULTS.md (8.0K) - migrations/README.md (2.8K) 문서: - SCHEMA_MIGRATION_GUIDE.md (13K) - 완전한 배포 가이드 - MIGRATION_SUMMARY.md (9.1K) - 요약 및 체크리스트 로컬 테스트 결과: - ✅ 마이그레이션 성공 (23 commands, <1초) - ✅ CHECK 제약조건 작동 (음수 잔액 거부) - ✅ 길이 제한 작동 (51자 이름 거부) - ✅ audit_logs 테이블 정상 - ✅ 데이터 보존 확인 (users:3, deposits:1, transactions:1) - ✅ 음수 잔액 데이터 감지 (user_id:3, balance:-500) 프로덕션 배포: - 로컬 테스트 완료, 프로덕션 준비 완료 - 배포 전 백업 필수 - 예상 소요 시간: <5분 Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
102 lines
2.8 KiB
Markdown
102 lines
2.8 KiB
Markdown
# 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
|