telegram-bot-workers/migrations

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

# 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

# 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