LedgerX

# Optimistic locking with version check
async def debit_account(account_id: str, amount: Decimal, version: int):
    async with db.transaction():
        # Acquire advisory lock for this account
        await db.execute(f"SELECT pg_advisory_xact_lock({hash(account_id)})")
        
        account = await db.fetchone(
            "SELECT * FROM accounts WHERE id = $1", account_id
        )
        
        if account.version != version:
            raise ConcurrencyError("Account modified by another transaction")
        
        if account.balance < amount:
            raise InsufficientFundsError(f"Balance: {account.balance}")
        
        # Create ledger entry (append-only)
        await db.execute("""
            INSERT INTO ledger_entries 
            (transaction_id, account_id, entry_type, amount, running_balance)
            VALUES ($1, $2, 'debit', $3, $4)
        """, tx_id, account_id, amount, account.balance - amount)

-- Prevent modifications to ledger entries
CREATE OR REPLACE FUNCTION prevent_ledger_modification()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'Ledger entries are immutable. Create a reversing entry instead.';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ledger_immutability
    BEFORE UPDATE OR DELETE ON ledger_entries
    FOR EACH ROW
    EXECUTE FUNCTION prevent_ledger_modification();

-- Reversing entry for corrections
CREATE OR REPLACE FUNCTION reverse_transaction(original_tx_id UUID)
RETURNS UUID AS $$
DECLARE
    reversal_id UUID := gen_random_uuid();
BEGIN
    INSERT INTO transactions (id, reference, description, reversed_by)
    SELECT reversal_id, 'REV-' || reference, 'Reversal of ' || description, original_tx_id
    FROM transactions WHERE id = original_tx_id;
    
    -- Create opposite entries
    INSERT INTO ledger_entries (transaction_id, account_id, entry_type, amount)
    SELECT reversal_id, account_id, 
           CASE WHEN entry_type = 'debit' THEN 'credit' ELSE 'debit' END,
           amount
    FROM ledger_entries WHERE transaction_id = original_tx_id;
    
    RETURN reversal_id;
END;
$$ LANGUAGE plpgsql;

# Daily reconciliation job
async def reconcile_accounts():
    accounts = await db.fetch("SELECT id FROM accounts WHERE is_active")
    
    for account in accounts:
        # Calculate balance from entries
        calculated = await db.fetchval("""
            SELECT COALESCE(
                SUM(CASE WHEN entry_type = 'credit' THEN amount ELSE -amount END), 0
            ) FROM ledger_entries WHERE account_id = $1
        """, account.id)
        
        # Get last running balance
        last_entry = await db.fetchone("""
            SELECT running_balance FROM ledger_entries 
            WHERE account_id = $1 ORDER BY created_at DESC LIMIT 1
        """, account.id)
        
        if calculated != last_entry.running_balance:
            await alert_discrepancy(account.id, calculated, last_entry.running_balance)
        
        # Create daily snapshot
        await db.execute("""
            INSERT INTO balance_snapshots (account_id, balance, snapshot_date)
            VALUES ($1, $2, CURRENT_DATE)
        """, account.id, calculated)