telegram-bot-workers/SCHEMA_MIGRATION_GUIDE.md

Schema Migration Guide

Migration 001: Schema Enhancements Date: 2026-01-19 Author: Claude Code

Overview

This migration adds critical data integrity constraints and audit logging capabilities to the Telegram Bot database.

Changes Summary

Change	Purpose	Risk Level
user_deposits.balance CHECK constraint	Prevent negative balances	Medium
deposit_transactions.depositor_name length limit	Enforce 15 character max	Low
audit_logs table	Track all important operations	None (new table)

---

Migration Contents

1. Balance Integrity (user_deposits)

Goal: Ensure balance >= 0 at database level

Implementation:

CREATE TABLE user_deposits (
  ...
  balance INTEGER NOT NULL DEFAULT 0 CHECK (balance >= 0),
  ...
);

Impact:

• ✅ Prevents negative balances from invalid transactions
• ⚠️ Rejects existing records with negative balances (logs rejected records)
• 🔧 Requires table recreation (SQLite limitation)

Verification:

-- Should fail with CHECK constraint violation
INSERT INTO user_deposits (user_id, balance) VALUES (999999, -1000);

2. Depositor Name Length (deposit_transactions)

Goal: Limit depositor_name to 15 characters max

Implementation:

CREATE TABLE deposit_transactions (
  ...
  depositor_name TEXT CHECK (length(depositor_name) <= 15),
  ...
);

Impact:

• ✅ Prevents excessively long names
• 📏 Truncates existing names >15 chars (logs truncated records)
• 🔧 Requires table recreation (SQLite limitation)

Verification:

-- Should fail with CHECK constraint violation
INSERT INTO deposit_transactions (user_id, type, amount, depositor_name)
VALUES (1, 'deposit', 1000, 'ThisIsAVeryLongNameThatExceedsFiftyCharactersAndShouldBeRejected');

3. Audit Logging (audit_logs)

Goal: Track all critical operations for compliance and debugging

Schema:

CREATE TABLE audit_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  telegram_id TEXT,
  action TEXT NOT NULL,
  resource_type TEXT,
  resource_id INTEGER,
  details TEXT,
  ip_address TEXT,
  user_agent TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Use Cases:

• Deposit confirmations
• Domain registrations
• Balance changes
• Administrative actions

Example Usage:

// Log deposit confirmation
await env.DB.prepare(`
  INSERT INTO audit_logs (user_id, telegram_id, action, resource_type, resource_id, details)
  VALUES (?, ?, ?, ?, ?, ?)
`).bind(
  userId,
  telegramId,
  'deposit_confirmed',
  'deposit_transaction',
  transactionId,
  JSON.stringify({ amount: 5000, depositor: '홍길동' })
).run();

---

Pre-Migration Checklist

⚠️ CRITICAL: Complete these steps before running migration

1. Backup Production Database

# Export current data
wrangler d1 execute telegram-conversations \
  --command ".dump" > backup_$(date +%Y%m%d_%H%M%S).sql

# Verify backup file
ls -lh backup_*.sql

2. Review Current Data

# Check for negative balances (will be rejected)
wrangler d1 execute telegram-conversations \
  --command "SELECT * FROM user_deposits WHERE balance < 0"

# Check for long depositor names (will be truncated)
wrangler d1 execute telegram-conversations \
  --command "SELECT id, depositor_name, length(depositor_name) as len FROM deposit_transactions WHERE length(depositor_name) > 15"

3. Notify Users (if downtime expected)

• Estimated downtime: < 1 minute for typical dataset
• No user-facing impact (transparent migration)

---

Local Testing Procedure

MANDATORY: Test on local database first

Step 1: Initialize Local Test Database

# Create fresh local database
npm run db:init:local

Step 2: Populate Test Data

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

# Add test deposit account
wrangler d1 execute telegram-conversations --local \
  --command "INSERT INTO user_deposits (user_id, balance) VALUES (1, 10000)"

# Add test transaction
wrangler d1 execute telegram-conversations --local \
  --command "INSERT INTO deposit_transactions (user_id, type, amount, depositor_name, status) VALUES (1, 'deposit', 5000, '홍길동', 'confirmed')"

Step 3: Run Migration

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

# Expected output:
# ✅ Migration 001 completed successfully

Step 4: Verify Migration

# Check table structure
wrangler d1 execute telegram-conversations --local \
  --command "SELECT sql FROM sqlite_master WHERE type='table' AND name='user_deposits'"

# Verify CHECK constraint (should fail)
wrangler d1 execute telegram-conversations --local \
  --command "INSERT INTO user_deposits (user_id, balance) VALUES (999, -1000)"

# Expected: CHECK constraint failed: balance >= 0

# Verify audit_logs table exists
wrangler d1 execute telegram-conversations --local \
  --command "SELECT COUNT(*) FROM audit_logs"

Step 5: Test Rollback (Optional)

# Execute rollback
wrangler d1 execute telegram-conversations --local \
  --file migrations/001_rollback.sql

# Verify rollback
wrangler d1 execute telegram-conversations --local \
  --command "INSERT INTO user_deposits (user_id, balance) VALUES (999, -1000)"

# Should succeed (no CHECK constraint)

---

Production Deployment Procedure

⚠️ Only proceed after successful local testing

Pre-Deployment

1. Announce Maintenance Window (optional, <1 min downtime)
2. Backup Production Database (see Pre-Migration Checklist)
3. Review Migration SQL one final time

Deployment Steps

Step 1: Execute Migration

# Execute migration on production
wrangler d1 execute telegram-conversations \
  --file migrations/001_schema_enhancements.sql

# Monitor output for warnings

Step 2: Verify Migration Success

# Check record counts (should match pre-migration)
wrangler d1 execute telegram-conversations \
  --command "SELECT 'users' as table_name, COUNT(*) as count FROM users
             UNION ALL SELECT 'user_deposits', COUNT(*) FROM user_deposits
             UNION ALL SELECT 'deposit_transactions', COUNT(*) FROM deposit_transactions
             UNION ALL SELECT 'audit_logs', COUNT(*) FROM audit_logs"

# Verify CHECK constraints
wrangler d1 execute telegram-conversations \
  --command "INSERT INTO user_deposits (user_id, balance) VALUES (999999, -1000)"

# Expected: CHECK constraint failed

Step 3: Test Bot Functionality

# Send test message to bot
# Telegram: /start

# Check deposit function
# Telegram: 잔액

# Verify no errors in logs
wrangler tail

Step 4: Monitor for Issues

# Stream logs for 5 minutes
wrangler tail --format pretty

# Check for errors related to:
# - INSERT/UPDATE on user_deposits
# - INSERT/UPDATE on deposit_transactions
# - Any database constraint violations

Post-Deployment

1. Update Documentation (mark migration as completed)
2. Monitor Error Rates for 24 hours
3. Archive Backup (keep for 30 days minimum)

---

Rollback Procedure

Only use in case of critical failure

When to Rollback

• Migration fails mid-execution
• Data integrity issues detected
• Application errors due to constraints

Rollback Steps

# 1. Stop bot (if necessary)
# Update webhook to point to maintenance page

# 2. Execute rollback
wrangler d1 execute telegram-conversations \
  --file migrations/001_rollback.sql

# 3. Verify rollback
wrangler d1 execute telegram-conversations \
  --command "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='audit_logs'"

# Expected: 0 (audit_logs removed)

# 4. Restore from backup (if needed)
wrangler d1 execute telegram-conversations \
  --file backup_20260119_120000.sql

# 5. Resume bot operations
# Update webhook back to worker URL

---

Audit Log Usage Examples

1. Log Deposit Confirmation

// In deposit-agent.ts or index.ts
const auditLog = await env.DB.prepare(`
  INSERT INTO audit_logs (
    user_id, telegram_id, action, resource_type, resource_id, details
  ) VALUES (?, ?, ?, ?, ?, ?)
`).bind(
  userId,
  telegramId,
  'deposit_confirmed',
  'deposit_transaction',
  transactionId,
  JSON.stringify({
    amount: amount,
    depositor_name: depositorName,
    matched_bank_notification_id: bankNotificationId
  })
).run();

2. Log Domain Registration

// In domain-register.ts
await env.DB.prepare(`
  INSERT INTO audit_logs (
    user_id, telegram_id, action, resource_type, resource_id, details
  ) VALUES (?, ?, ?, ?, ?, ?)
`).bind(
  userId,
  telegramId,
  'domain_registered',
  'user_domains',
  domainId,
  JSON.stringify({
    domain: domain,
    price: price,
    balance_before: balanceBefore,
    balance_after: balanceAfter
  })
).run();

3. Query Audit Logs

// Get user's recent activity
const logs = await env.DB.prepare(`
  SELECT action, resource_type, details, created_at
  FROM audit_logs
  WHERE telegram_id = ?
  ORDER BY created_at DESC
  LIMIT 10
`).bind(telegramId).all();

// Get all deposit confirmations today
const today = await env.DB.prepare(`
  SELECT *
  FROM audit_logs
  WHERE action = 'deposit_confirmed'
    AND date(created_at) = date('now')
  ORDER BY created_at DESC
`).all();

// Get suspicious activity (multiple failed attempts)
const suspicious = await env.DB.prepare(`
  SELECT telegram_id, action, COUNT(*) as attempts
  FROM audit_logs
  WHERE action LIKE '%_failed'
    AND created_at > datetime('now', '-1 hour')
  GROUP BY telegram_id, action
  HAVING attempts > 5
`).all();

---

Troubleshooting

Issue: Migration Fails with "CHECK constraint failed"

Cause: Existing data violates new constraints

Solution:

# Find violating records
wrangler d1 execute telegram-conversations \
  --command "SELECT * FROM user_deposits WHERE balance < 0"

# Manually fix data before migration
wrangler d1 execute telegram-conversations \
  --command "UPDATE user_deposits SET balance = 0 WHERE balance < 0"

# Re-run migration

Issue: "Table already exists" Error

Cause: Previous migration attempt partially completed

Solution:

# Check current schema
wrangler d1 execute telegram-conversations \
  --command "SELECT name FROM sqlite_master WHERE type='table'"

# If audit_logs exists, drop it first
wrangler d1 execute telegram-conversations \
  --command "DROP TABLE IF EXISTS audit_logs"

# Re-run migration

Issue: Performance Degradation After Migration

Cause: Missing indexes after table recreation

Solution:

# Verify indexes exist
wrangler d1 execute telegram-conversations \
  --command "SELECT name FROM sqlite_master WHERE type='index'"

# Recreate missing indexes (already in migration script)
wrangler d1 execute telegram-conversations \
  --command "CREATE INDEX IF NOT EXISTS idx_deposits_user ON user_deposits(user_id)"

---

D1 Limitations & Workarounds

No Transaction Support

Issue: D1 doesn't support multi-statement transactions via wrangler CLI

Workaround:

• Migration script uses step-by-step approach with backup tables
• Each step is atomic (backup → drop → create → restore)
• If migration fails, manually restore from backup table

No ALTER TABLE ... ADD CHECK

Issue: SQLite/D1 doesn't support adding CHECK constraints to existing tables

Workaround:

• Recreate table with new constraints
• Use backup table to preserve data
• Restore data with validation

Constraint Naming

Issue: SQLite doesn't support named constraints in CHECK

Workaround:

• Use inline CHECK constraints
• Document constraint purpose in migration comments

---

Verification Checklist

After migration, verify:

• All user_deposits records have balance >= 0
• All deposit_transactions have depositor_name length <= 15
• audit_logs table exists with correct schema
• All indexes recreated successfully
• Record counts match pre-migration
• Bot responds to /start command
• Deposit system works (잔액 command)
• No errors in wrangler tail logs
• Backup file created and validated

---

Additional Resources

• D1 Documentation: https://developers.cloudflare.com/d1/
• SQLite CHECK Constraints: https://www.sqlite.org/lang_createtable.html#check_constraints
• Wrangler CLI Reference: https://developers.cloudflare.com/workers/wrangler/commands/

---

Contact & Support

Issues or Questions:

• Review troubleshooting section above
• Check wrangler tail logs for detailed errors
• Restore from backup if critical failure

Emergency Rollback: See "Rollback Procedure" section above