Skip to content

LEDGER.md

Latest commit

 

History

History
435 lines (354 loc) · 11.8 KB

LEDGER.md

File metadata and controls

435 lines (354 loc) · 11.8 KB

Ledger Database Schema

TigerBeetle ledger design and account structures.

Architecture: 01_ARCHITECTURE.md
Components: 02_COMPONENTS.md
Core DB: CORE.md

TigerBeetle Overview

TigerBeetle is a distributed financial accounting database optimized for high-volume payment systems.

Key Features:

  • Atomic transfers: Linked chains guarantee atomicity
  • No NULLs: All fields have values
  • 64-bit integers: Amounts in smallest currency unit (cents)
  • Immuable: Transfers cannot be modified after creation
  • Deterministic: Same operations always produce same results

Account Structure

Account ID Format (128-bit)

┌─────────────────────────────────────────────────────────────────────────┐
│                        TIGERBEETLE ACCOUNT ID                           │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  [tenant_id: 64 bits] [type: 8 bits] [currency: 24 bits] [resv: 32]     │
│                                                                         │
│  Bits 0-63:    Tenant UUID (first 8 bytes)                              │
│  Bits 64-71:   Account Type (0x01-0x06)                                 │
│  Bits 72-95:   Currency Code (ISO 4217 numeric)                         │
│  Bits 96-127:  Reserved / Flags                                         │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

Currency Codes (ISO 4217 Numeric)

Currency Code Numeric
IDR Indonesian Rupiah 360
USD US Dollar 840
EUR Euro 978
GBP British Pound 826
SEK Swedish Krona 752
DKK Danish Krone 208

Account Types

Code Type Purpose Maps To Bank
0x01 TENANT_WALLET Client funds per currency FBO sub-ledger
0x02 FEE_REVENUE Platform fees Kovra revenue account
0x03 FX_SETTLEMENT Internal FX positions (Internal only)
0x04 PENDING_INBOUND Funds being collected (Transit)
0x05 PENDING_OUTBOUND Funds being disbursed (Transit)
0x06 REGIONAL_SETTLEMENT Pre-funded liquidity Nostro accounts

Account Creation

-- Function to generate TigerBeetle account ID
CREATE OR REPLACE FUNCTION generate_tb_account_id(
    p_tenant_id UUID,
    p_account_type INTEGER,
    p_currency_char CHAR(3)
) RETURNS NUMERIC(39,0) AS $$
DECLARE
    v_currency_num INTEGER;
    v_high_bits BIGINT;
    v_low_bits BIGINT;
BEGIN
    -- Convert currency to numeric code
    v_currency_num := CASE p_currency_char
        WHEN 'IDR' THEN 360
        WHEN 'USD' THEN 840
        WHEN 'EUR' THEN 978
        WHEN 'GBP' THEN 826
        WHEN 'SEK' THEN 752
        WHEN 'DKK' THEN 208
        ELSE 999
    END;
    
    -- Build 128-bit ID
    -- High 64 bits: tenant_id
    v_high_bits := ('x' || substr(p_tenant_id::TEXT, 1, 16))::bit(64)::bigint;
    
    -- Low 64 bits: type (8) + currency (24) + reserved (32)
    v_low_bits := (p_account_type::bigint << 56) | (v_currency_num::bigint << 32);
    
    -- Combine into 128-bit numeric
    RETURN (v_high_bits::numeric << 64) | v_low_bits;
END;
$$ LANGUAGE plpgsql;

Transfer Chains

Same-Currency Transfer (Single Ledger)

Transfer 1 (linked):
  Debit:  TENANT_WALLET_EUR      €10,000
  Credit: PENDING_OUTBOUND_EUR   €10,000
  
Transfer 2 (linked, clears flags):
  Debit:  PENDING_OUTBOUND_EUR   €10,000
  Credit: REGIONAL_SETTLEMENT_EU €10,000

Flags: linked = true for both transfers
Result: Atomic - both succeed or both fail

Cross-Currency FX (Two Ledgers)

SOURCE CHAIN (EUR Ledger):
  Transfer 1 (linked):
    Debit:  TENANT_WALLET_EUR    €10,000
    Credit: PENDING_INBOUND_EUR  €10,000
    
  Transfer 2 (linked):
    Debit:  PENDING_INBOUND_EUR  €80
    Credit: FEE_REVENUE_EUR      €80
    
  Transfer 3 (linked, clears flags):
    Debit:  PENDING_INBOUND_EUR  €9,920
    Credit: FX_POSITION_EUR      €9,920

DEST CHAIN (IDR Ledger):
  Transfer 4:
    Debit:  FX_POSITION_IDR      Rp 171,120,000
    Credit: PENDING_OUTBOUND_IDR Rp 171,120,000
    
  Transfer 5 (clears flags):
    Debit:  PENDING_OUTBOUND_IDR Rp 171,120,000
    Credit: REGIONAL_SETTLEMENT  Rp 171,120,000

Coordination: Application-level (not atomic across ledgers)
Compensation: If Dest fails, reverse Source chain

Transfer Flags

Flag Value Purpose
linked 0x01 Part of atomic chain
pending 0x02 Awaiting completion
post_pending 0x04 Complete pending transfer
void_pending 0x08 Cancel pending transfer

Flag Combinations

// Linked chain (same-currency)
flags := FlagLinked  // 0x01

// Pending transfer (waiting for bank confirmation)
flags := FlagPending  // 0x02

// Complete pending transfer
flags := FlagPostPending  // 0x04

// Cancel pending transfer
flags := FlagVoidPending  // 0x08

Reconciliation Invariants

Daily Checks

-- FBO Balance Check
SELECT 
    currency,
    SUM(tb_balance) as tigerbeetle_sum,
    fbo_bank_statement as bank_sum,
    ABS(SUM(tb_balance) - fbo_bank_statement) as discrepancy
FROM (
    SELECT 
        (tb_account_id >> 32 & 0xFFFFFF) as currency_code,
        balance as tb_balance
    FROM tigerbeetle_accounts
    WHERE type = 0x01  -- TENANT_WALLET
) tb
JOIN currency_codes cc ON tb.currency_code = cc.numeric_code
JOIN daily_fbo_statements fbo ON cc.char_code = fbo.currency
GROUP BY currency, fbo_bank_statement;

-- Nostro Balance Check
SELECT 
    region,
    tb_balance as tigerbeetle_balance,
    nostro_statement as bank_balance
FROM (
    SELECT 
        CASE (tb_account_id >> 32 & 0xFFFFFF)
            WHEN 360 THEN 'ID'
            WHEN 978 THEN 'EU'
            WHEN 826 THEN 'UK'
        END as region,
        balance
    FROM tigerbeetle_accounts
    WHERE type = 0x06  -- REGIONAL_SETTLEMENT
) tb
JOIN daily_nostro_statements ns ON tb.region = ns.region;

TigerBeetle Schema (Simplified)

Accounts Table

-- TigerBeetle internal structure (simplified view)
CREATE VIEW tb_accounts AS
SELECT 
    id,
    user_data_128,      -- Custom data (our account metadata)
    user_data_64,       -- Additional metadata
    user_data_32,       -- Flags
    ledger,             -- Currency ledger (360, 840, 978, etc.)
    code,               -- Account code (chart of accounts)
    flags,              -- Account flags
    debits_pending,     -- Pending debits
    debits_posted,      -- Posted debits
    credits_pending,    -- Pending credits
    credits_posted,     -- Posted credits
    timestamp           -- Last update
FROM tigerbeetle.accounts;

Transfers Table

-- TigerBeetle internal structure (simplified view)
CREATE VIEW tb_transfers AS
SELECT 
    id,
    debit_account_id,
    credit_account_id,
    amount,
    pending_id,         -- If this completes a pending transfer
    user_data_128,      -- Custom metadata
    user_data_64,
    user_data_32,
    timeout,            -- Pending transfer timeout
    ledger,             -- Currency ledger
    code,               -- Transfer code
    flags,              -- Transfer flags (linked, pending, etc.)
    timestamp
FROM tigerbeetle.transfers;

Common Operations

Create Linked Chain

// Create atomic chain of transfers
chain := []Transfer{
    {
        DebitAccountID:  tenantWalletID,
        CreditAccountID: pendingInboundID,
        Amount:          10000 * 100,  // cents
        Ledger:          978,  // EUR
        Flags:           FlagLinked,
    },
    {
        DebitAccountID:  pendingInboundID,
        CreditAccountID: feeRevenueID,
        Amount:          80 * 100,
        Ledger:          978,
        Flags:           FlagLinked,
    },
    {
        DebitAccountID:  pendingInboundID,
        CreditAccountID: fxPositionID,
        Amount:          9920 * 100,
        Ledger:          978,
        Flags:           FlagLinked | FlagNone,  // Clears linked flag
    },
}

// Submit to TigerBeetle
results, err := tb.CreateTransfers(chain)
// All succeed or all fail (within same ledger)

Create Pending Transfer

// Hold funds pending external confirmation
pendingTransfer := Transfer{
    DebitAccountID:  tenantWalletID,
    CreditAccountID: pendingOutboundID,
    Amount:          amount,
    Ledger:          currency,
    Flags:           FlagPending,
    Timeout:         300 * 1000,  // 5 minutes in milliseconds
}

// When external confirms:
completion := Transfer{
    PendingID: pendingTransfer.ID,
    Flags:     FlagPostPending,
}

// Or if external fails:
cancellation := Transfer{
    PendingID: pendingTransfer.ID,
    Flags:     FlagVoidPending,
}

Query Balance

-- Calculate balance from TigerBeetle
SELECT 
    (credits_posted - debits_posted) as available_balance,
    (credits_pending - debits_pending) as pending_balance,
    (credits_posted + credits_pending - debits_posted - debits_pending) as total_balance
FROM tigerbeetle.accounts
WHERE id = :account_id;

Integration with PostgreSQL

Cached Balance Pattern

-- PostgreSQL stores cached balance (updated periodically)
CREATE TABLE wallet_balances (
    wallet_id           UUID PRIMARY KEY,
    tb_account_id       NUMERIC(39,0) NOT NULL UNIQUE,
    cached_balance      NUMERIC(20,2) NOT NULL DEFAULT 0,
    cached_pending      NUMERIC(20,2) NOT NULL DEFAULT 0,
    cached_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Reconciliation check
    last_reconciled_at  TIMESTAMPTZ,
    reconciliation_diff NUMERIC(20,2) DEFAULT 0
);

-- Update cache from TigerBeetle (periodic job)
UPDATE wallet_balances wb
SET 
    cached_balance = (credits_posted - debits_posted) / 100.0,
    cached_pending = (credits_pending - debits_pending) / 100.0,
    cached_at = NOW()
FROM tigerbeetle.accounts tb
WHERE wb.tb_account_id = tb.id;

Monitoring Queries

Account Health Check

-- Find accounts with pending transfers
SELECT 
    id,
    ledger,
    debits_pending,
    credits_pending,
    (credits_pending - debits_pending) as net_pending
FROM tigerbeetle.accounts
WHERE debits_pending > 0 OR credits_pending > 0;

Transfer Volume

-- Daily transfer volume by ledger
SELECT 
    ledger,
    COUNT(*) as transfer_count,
    SUM(amount) / 100.0 as total_amount
FROM tigerbeetle.transfers
WHERE timestamp > NOW() - INTERVAL '24 hours'
GROUP BY ledger;

Stuck Pending

-- Pending transfers past timeout
SELECT 
    t.id,
    t.pending_id,
    t.timeout,
    t.timestamp
FROM tigerbeetle.transfers t
WHERE t.flags & 2 = 2  -- Pending flag
  AND t.timestamp + (t.timeout || ' milliseconds')::INTERVAL < NOW();

Best Practices

  1. Always use linked chains for multi-step operations within same ledger
  2. Never modify transfers - create new ones to reverse/compensate
  3. Cache balances in PostgreSQL for reads, use TigerBeetle for writes
  4. Reconcile daily - FBO and Nostro must match bank statements
  5. Handle timeouts - Pending transfers need monitoring

Next: OPERATIONS.md for jobs and operations
Prev: COMPLIANCE.md for KYC/AML tables
Components: 02_COMPONENTS.md

Last Updated: 2026-02-15