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>
115 lines
4.0 KiB
SQL
115 lines
4.0 KiB
SQL
-- Migration 001 Rollback
|
|
-- Purpose: Revert schema enhancements to original state
|
|
-- Date: 2026-01-19
|
|
-- WARNING: This will remove CHECK constraints and audit_logs table
|
|
|
|
-- =============================================================================
|
|
-- ROLLBACK STEP 1: user_deposits - Remove CHECK constraint
|
|
-- =============================================================================
|
|
|
|
-- 1.1 Create backup table
|
|
CREATE TABLE user_deposits_backup AS SELECT * FROM user_deposits;
|
|
|
|
-- 1.2 Drop existing table
|
|
DROP TABLE user_deposits;
|
|
|
|
-- 1.3 Recreate table without CHECK constraint
|
|
CREATE TABLE user_deposits (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL UNIQUE,
|
|
balance INTEGER NOT NULL DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- 1.4 Restore all data
|
|
INSERT INTO user_deposits (id, user_id, balance, created_at, updated_at)
|
|
SELECT id, user_id, balance, created_at, updated_at
|
|
FROM user_deposits_backup;
|
|
|
|
-- 1.5 Drop backup table
|
|
DROP TABLE user_deposits_backup;
|
|
|
|
-- 1.6 Recreate index
|
|
CREATE INDEX IF NOT EXISTS idx_deposits_user ON user_deposits(user_id);
|
|
|
|
-- =============================================================================
|
|
-- ROLLBACK STEP 2: deposit_transactions - Remove length constraint
|
|
-- =============================================================================
|
|
|
|
-- 2.1 Create backup table
|
|
CREATE TABLE deposit_transactions_backup AS SELECT * FROM deposit_transactions;
|
|
|
|
-- 2.2 Drop existing table
|
|
DROP TABLE deposit_transactions;
|
|
|
|
-- 2.3 Recreate table without length constraint
|
|
CREATE TABLE deposit_transactions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
type TEXT NOT NULL CHECK(type IN ('deposit', 'withdrawal', 'refund')),
|
|
amount INTEGER NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'confirmed', 'rejected', 'cancelled')),
|
|
depositor_name TEXT,
|
|
description TEXT,
|
|
confirmed_at DATETIME,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- 2.4 Restore all data
|
|
INSERT INTO deposit_transactions (
|
|
id, user_id, type, amount, status, depositor_name, description, confirmed_at, created_at
|
|
)
|
|
SELECT
|
|
id, user_id, type, amount, status, depositor_name, description, confirmed_at, created_at
|
|
FROM deposit_transactions_backup;
|
|
|
|
-- 2.5 Drop backup table
|
|
DROP TABLE deposit_transactions_backup;
|
|
|
|
-- 2.6 Recreate indexes
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_user ON deposit_transactions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_status ON deposit_transactions(status, created_at DESC);
|
|
|
|
-- =============================================================================
|
|
-- ROLLBACK STEP 3: Remove audit_logs table
|
|
-- =============================================================================
|
|
|
|
-- 3.1 Drop indexes
|
|
DROP INDEX IF EXISTS idx_audit_logs_user_id;
|
|
DROP INDEX IF EXISTS idx_audit_logs_telegram_id;
|
|
DROP INDEX IF EXISTS idx_audit_logs_action;
|
|
DROP INDEX IF EXISTS idx_audit_logs_resource;
|
|
DROP INDEX IF EXISTS idx_audit_logs_created_at;
|
|
|
|
-- 3.2 Drop table
|
|
DROP TABLE IF EXISTS audit_logs;
|
|
|
|
-- =============================================================================
|
|
-- VERIFICATION QUERIES
|
|
-- =============================================================================
|
|
|
|
-- Count records in each table
|
|
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;
|
|
|
|
-- Verify audit_logs is gone
|
|
SELECT
|
|
CASE
|
|
WHEN COUNT(*) = 0 THEN 'audit_logs successfully removed'
|
|
ELSE 'WARNING: audit_logs still exists'
|
|
END as verification_result
|
|
FROM sqlite_master
|
|
WHERE type='table' AND name='audit_logs';
|
|
|
|
-- =============================================================================
|
|
-- ROLLBACK COMPLETE
|
|
-- =============================================================================
|
|
|
|
SELECT 'Migration 001 rollback completed successfully' as status, datetime('now') as timestamp;
|