f5df0c0ffe
- Implement optimistic locking for deposit balance updates - Prevent race conditions in concurrent deposit requests - Add automatic retry with exponential backoff (max 3 attempts) - Add version column to user_deposits table - Improve type safety across codebase - Add explicit types for Namecheap API responses - Add typed function arguments (ManageDepositArgs, etc.) - Remove `any` types from deposit-agent and tool files - Add reconciliation job for balance integrity verification - Compare user_deposits.balance vs SUM(confirmed transactions) - Alert admin on discrepancy detection - Set up test environment with Vitest + Miniflare - Add 50+ test cases for deposit system - Add helper functions for test data creation - Update documentation - Add migration guide for version columns - Document optimistic locking patterns Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
29 lines
1.2 KiB
SQL
29 lines
1.2 KiB
SQL
-- Migration 002: Add version column for Optimistic Locking
|
|
-- Purpose: Enable concurrent transaction safety for deposit balance updates
|
|
-- Date: 2026-01-19
|
|
-- Reference: CLAUDE.md "Transaction Isolation & Optimistic Locking"
|
|
|
|
-- Problem:
|
|
-- D1 batch() is not a true transaction - partial failures can cause data inconsistencies
|
|
-- in financial operations (balance updates + transaction records)
|
|
|
|
-- Solution:
|
|
-- Optimistic Locking pattern with version column
|
|
-- - Version is incremented on every balance UPDATE
|
|
-- - UPDATE checks current version to detect concurrent modifications
|
|
-- - Automatic retry with exponential backoff (max 3 attempts)
|
|
|
|
-- Add version column to user_deposits
|
|
ALTER TABLE user_deposits ADD COLUMN version INTEGER NOT NULL DEFAULT 1;
|
|
|
|
-- Create index for efficient version checking
|
|
CREATE INDEX IF NOT EXISTS idx_deposits_user_version ON user_deposits(user_id, version);
|
|
|
|
-- Verification:
|
|
-- SELECT user_id, balance, version FROM user_deposits LIMIT 5;
|
|
|
|
-- Rollback instructions (manual execution required):
|
|
-- DROP INDEX IF EXISTS idx_deposits_user_version;
|
|
-- -- Note: SQLite doesn't support DROP COLUMN directly in older versions
|
|
-- -- If needed, recreate table without version column and copy data
|