Double-Entry Ledger: Immutable Schema & Concurrency

UPDATE accounts SET balance = balance - 1000000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000000 WHERE id = 'B';

// TigerBeetle Account Struct — exactly 128 bytes, CPU cache-line aligned
pub const Account = extern struct {
    id: u128,                 // 16 bytes: Unique identifier (UUIDv4/v7 or custom monotonic ID)
    debits_pending: u128,     // 16 bytes: Amount reserved in pending transfers
    debits_posted: u128,      // 16 bytes: Total debit fully committed
    credits_pending: u128,    // 16 bytes: Amount reserved on the credit side
    credits_posted: u128,     // 16 bytes: Total credit fully committed
    user_data_128: u128,      // 16 bytes: Custom metadata (e.g., customer_id)
    user_data_64: u64,        //  8 bytes: Custom metadata
    user_data_32: u32,        //  4 bytes: Custom metadata
    reserved: u32 = 0,        //  4 bytes: Padding to hit exactly 128 bytes
    ledger: u32,              //  4 bytes: Grouping accounts by currency / asset type
    code: u16,                //  2 bytes: Chart of Accounts code (e.g., 1001 = cash)
    flags: u16,               //  2 bytes: Business rules flags
    timestamp: u64,           //  8 bytes: Nanosecond timestamp (managed by the cluster)
};

// TigerBeetle Transfer Struct — 128 bytes, same alignment
pub const Transfer = extern struct {
    id: u128,                 // 16 bytes: Unique transfer ID
    debit_account_id: u128,   // 16 bytes: Account being debited
    credit_account_id: u128,  // 16 bytes: Account being credited
    amount: u128,             // 16 bytes: Asset amount to transfer
    pending_id: u128,         // 16 bytes: ID of the pending transfer (used in two-phase)
    user_data_128: u128,      // 16 bytes: Custom metadata
    user_data_64: u64,        //  8 bytes: Custom metadata
    user_data_32: u32,        //  4 bytes: Custom metadata
    timeout: u32 = 0,         //  4 bytes: Auto-void timeout in seconds
    ledger: u32,              //  4 bytes: Must match the ledger of both accounts
    code: u16,                //  2 bytes: Custom category code
    flags: u16,               //  2 bytes: Config flags (pending, post_pending, void_pending)
    timestamp: u64,           //  8 bytes: Nanosecond timestamp upon commit to log
};

-- Accounts Table: Defines accounts within the Chart of Accounts
CREATE TABLE accounts (
    id              UUID PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    currency        CHAR(3) NOT NULL,           -- ISO 4217: 'VND', 'USD', 'JPY'
    debit_balance   NUMERIC(18, 4) DEFAULT 0.0000 NOT NULL 
                    CHECK (debit_balance >= 0),
    credit_balance  NUMERIC(18, 4) DEFAULT 0.0000 NOT NULL 
                    CHECK (credit_balance >= 0),
    type            VARCHAR(20) NOT NULL 
                    CHECK (type IN ('ASSET', 'LIABILITY', 'EQUITY', 'REVENUE', 'EXPENSE')),
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Transactions Table: Header for each group of journal entries
CREATE TABLE transactions (
    id              UUID PRIMARY KEY,
    description     VARCHAR(255),
    posted_at       TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Entries Table: Individual Debit/Credit lines (the "legs" of a transaction)
CREATE TABLE entries (
    id              UUID PRIMARY KEY,
    transaction_id  UUID NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
    account_id      UUID NOT NULL REFERENCES accounts(id),
    amount          NUMERIC(18, 4) NOT NULL CHECK (amount <> 0),
    direction       VARCHAR(6) NOT NULL CHECK (direction IN ('DEBIT', 'CREDIT')),
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes to speed up balance lookups
CREATE INDEX idx_entries_account_id     ON entries(account_id);
CREATE INDEX idx_entries_transaction_id ON entries(transaction_id);

-- Trigger: Enforce balance invariant — total DEBIT must = total CREDIT in the same transaction
CREATE OR REPLACE FUNCTION verify_transaction_balance()
RETURNS TRIGGER AS $$
DECLARE
    balance_sum NUMERIC(18, 4);
BEGIN
    SELECT COALESCE(
        SUM(CASE WHEN direction = 'DEBIT' THEN amount ELSE -amount END),
        0
    )
    INTO balance_sum
    FROM entries
    WHERE transaction_id = NEW.transaction_id;

    IF balance_sum <> 0 THEN
        RAISE EXCEPTION 
            'Transaction unbalanced: SUM(DEBIT) - SUM(CREDIT) = %. Transaction ID: %',
            balance_sum, NEW.transaction_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_verify_balance
AFTER INSERT ON entries
FOR EACH ROW EXECUTE FUNCTION verify_transaction_balance();

BEGIN;

-- Lock both accounts in ID order to avoid deadlocks
-- Rule: ALWAYS lock the account with the smaller ID first
SELECT id, debit_balance, credit_balance
FROM accounts
WHERE id IN ('account-A', 'account-B')
ORDER BY id  -- Deterministic order — prevents deadlocks
FOR UPDATE;

-- Check if balance is sufficient
-- INSERT into transactions
-- INSERT into entries (Debit and Credit)
-- UPDATE account balances

COMMIT;

-- Detect any transaction where SUM(DEBIT) != SUM(CREDIT)
SELECT
    transaction_id,
    SUM(CASE WHEN direction = 'DEBIT' THEN amount ELSE -amount END) AS discrepancy
FROM entries
GROUP BY transaction_id
HAVING SUM(CASE WHEN direction = 'DEBIT' THEN amount ELSE -amount END) <> 0;

-- Expected result: 0 rows. If rows exist -> trigger P1 alert immediately.

// Run 100 concurrent goroutines to withdraw $10 from an account with a $100 balance
func TestConcurrentWithdrawal(t *testing.T) {
    const (
        numWorkers     = 100
        withdrawAmount = 10_000   // $10 in cents
        initialBalance = 100_000  // $100 in cents
    )
    
    var (
        successCount atomic.Int64
        wg           sync.WaitGroup
    )
    
    for i := 0; i < numWorkers; i++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            err := withdrawFunds("account-A", withdrawAmount)
            if err == nil {
                successCount.Add(1)
            }
        }()
    }
    wg.Wait()
    
    // Exactly 10 requests should succeed
    assert.Equal(t, int64(10), successCount.Load(),
        "Only 10 withdrawals permitted with a $100 balance")
    
    // No double-spend: final balance must be $0
    balance := getBalance("account-A")
    assert.Equal(t, int64(0), balance, "Balance after all funds withdrawn must be 0")
}

func reconcileAllTransactions(db *sql.DB) ([]UnbalancedTx, error) {
    query := `
        SELECT transaction_id, 
               SUM(CASE WHEN direction = 'DEBIT' THEN amount ELSE -amount END) AS discrepancy
        FROM entries
        GROUP BY transaction_id
        HAVING SUM(CASE WHEN direction = 'DEBIT' THEN amount ELSE -amount END) <> 0
    `
    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }
    // Parse rows and fire P1 alert if any discrepancy exists
    // ...
}