# PFI Ledgering
## Overview

The ledger module contains our source of truth for all balances (customer stored balances as well as TBD internal treasury balances) and money movements impacting the balances. The core ledger is simply an append only table with debits and credits against balances.

Requirements:
1. Strong consistency
2. Multi currency
3. Append only entries

Use cases:
1. Track customer and treasury balances
a. With immutable transaction history
3. Real time balance checks
4. Data satisfies accounting and audit requirements
a. Fees and principals broken out, minimally

## Database Schemas

### `balances`
| field | description |
|:------------------ |:----------------------------------------------- |
| `id` | primary key |
| `balance_id` | unique immutable ID |
| `beneficiary` | customer id to whom the balance belongs, or TBD |
| `custodian` | who is custodying the funds |
| `currency_code` | currency of the balance |
| `available_amount` | total amount - pendings |
| `amount` | total amount |
| `created_at` | ISO8601 timestamp |
| `updated_at` | ISO8601 timestamp |

### `entries`
| field | description |
|:------------ |:----------------------------- |
| `id` | primary key |
| `entry_id` | unique immutable ID |
| `balance_id` | balance impacted by the entry |
| `amount` | debit/credit amount |
| `type` | PENDING, VOIDED, SETTLED |
| `created_at` | ISO8601 timestamp |

> [!NOTE]
> We can consider collapsing types into `pending`, `pending_void` (which is a cancelled pending), `pending_settle` (which is a posted/settled pending). This will reduce the number of entries by 1 per settled transaction vs having to ledger pending, voided, and settled.

### Tracking Fees

In order to keep our `entries` table super dumb, fee logic and association will be done via tables in the `treasury` and `exchanges` modules. These join individual ledger entries to higher level abstractions (`payments` and `transfers`).

The fees and attributes relevant for payments and transfers can be different, hence keeping that logic in the respective module.

#### `payments_entries`
| field | description |
|:------------ |:----------------------------- |
| `id` | primary key |
| `payment_id` | payment foreign key |
| `entry_id` | entry foreign key |
| `type` | CUSTOMER_FEE, PRINCIPLE, etc. |
| `created_at` | ISO8601 timestamp |

#### `transfers_entries`
| field | description |
|:------------- |:------------------------- |
| `id` | primary key |
| `transfer_id` | transfer foreign key |
| `entry_id` | entry foreign key |
| `type` | WIRE_FEE, PRINCIPLE, etc. |
| `created_at` | ISO8601 timestamp |

### Schema Relationships

erDiagram
    CUSTOMERS ||--|{ BALANCES : has
    BALANCES ||--o{ ENTRIES : has
    
    PAYMENTS ||--|{ PAYMENTS_ENTRIES : has
    ENTRIES ||--o|PAYMENTS_ENTRIES : "associated with"
    
    
    ENTRIES ||--o|TRANSFERS_ENTRIES : "associated with"
    TRANSFERS ||--|{ TRANSFERS_ENTRIES : has
    

## Verbs

### Internal

#### `CreateBalance`

##### Request
| field | description |
|:-------------- |:----------------------------------------------- |
| `beneficiary` | customer id to whom the balance belongs, or TBD |
| `custodian` | who is custodying the funds |
| `currencyCode` | currency of the balance |

##### Biz Logic
1. Insert a row into `balances` with the appropriate attributes
2. Return the balance

##### Response
| field | description |
|:--------- |:------------------- |
| `balance` | the created balance |

#### `CreateEntry`
Used by `exchanges` and `transfers` to ledger a debit or credit against a balance.

##### Request
| field | description |
|:----------- |:------------------------- |
| `balanceID` | balance to ledger against |
| `amount` | debit/credit amount |
| `type` | PENDING, SETTLED, VOIDED |

##### Biz Logic
1. Start DB transaction
2. Insert a row into `entries` for the specified amount and state
3. Update the balance's amount
4. Commit
5. Return the entry

##### Response
| field | description |
|:------- |:----------------- |
| `entry` | the created entry |

#### `GetBalances`
Returns the customer's balance amounts.

##### Request
| field | description |
|:------------- |:------------------------------------------------------------ |
| `beneficiary` | beneficiary for whom to fetch balances (customer ID, or TBD) |

##### Biz Logic
1. Query the `balances` table for the specified beneficiary.
2. Return the balances or error

##### Response
| field | description |
|:---------- |:-------------------------- |
| `balances` | a list of fetched balances |

#### `CheckBalanceAndReserve`
Used when processing a USD -> MXN RFQ to check that the customer has sufficient USD, we have sufficient MXN for the payout, and to reserve the funds.

> [!WARNING]
> TODO: **INCOMPLETE**
> Need to determine biz level requirements, likely needs to check and reserve against two balances at once

##### Request
| field | description |
|:----------- |:------------------------------ |
| `balanceID` | balance to perform check for |
| `amount` | amount that should be reserved |

##### Biz Logic
**Assuming sufficient balance**
1. Start DB transaction
2. Query balance A
3. Insert a row into `entries` that is a pending amount against balance A
4. Update balance A
5. Commit
6. Return the balance and entry

**Assuming insufficient balance**
1. Start DB transaction
2. Query balance A, exit
3. End txn
4. Return error

##### Response
| field | description |
|:--------- |:------------------- |
| `balance` | the updated balance |
| `entry` | the created entry |

## Examples
Timestamps and DB IDs omitted for brevity. Meaningful balance IDs to make reading entries easier. Should be TypeIDs for real.

### $10 USD -> MXN remittance with fee

#### 0. Starting balances

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- | ------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 100 | 100 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 200 | 200 |

#### 1. Customer sends RFQ for $10 USD -> MXN remittance, we payout from bankaya

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- | ----------------------- |:------ |:------- |
| e_1 | balance_CA_USD | -10 | pending |
| e_2 | balance_CA_USD | -1 | pending |
| e_3 | balance_TBD_BANKAYA_MXN | -165 | pending |

**`payments_entries`**
| payment_id | entry_id | type |
|:---------- |:-------- | --------- |
| p_1 | e_1 | principal |
| p_1 | e_2 | fee |
| p_1 | e_3 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- |:------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | **89** | 100 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | **35** | 200 |

#### 2. Customer sends Order

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- | ----------------------- |:------ |:------- |
| e_1 | balance_CA_USD | -10 | pending |
| e_2 | balance_CA_USD | -1 | pending |
| e_3 | balance_TBD_BANKAYA_MXN | -165 | pending |
| e_4 | balance_CA_USD | +10 | void |
| e_5 | balance_CA_USD | +1 | void |
| e_6 | balance_CA_USD | -10 | settle |
| e_7 | balance_CA_USD | -1 | settle |

**`payments_entries`**
| payment_id | entry_id | type |
|:------------ |:-------- | --------- |
| p_1 (payin) | e_1 | principal |
| p_1 | e_2 | fee |
| p_2 (payout) | e_3 | principal |
| p_1 | e_4 | principal |
| p_1 | e_5 | fee |
| p_1 | e_6 | fee |
| p_1 | e_7 | fee |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- |:------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 89 | **89** |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 35 | 200 |

#### 3. Payout succeeds

**`entries`**
| entry_id | balance_id | type | amount |
|:-------- |:----------------------- |:---- |:------- |
| e_1 | balance_CA_USD | -10 | pending |
| e_2 | balance_CA_USD | -1 | pending |
| e_3 | balance_TBD_BANKAYA_MXN | -165 | pending |
| e_4 | balance_CA_USD | +10 | void |
| e_5 | balance_CA_USD | +1 | void |
| e_6 | balance_CA_USD | -10 | settle |
| e_7 | balance_CA_USD | -1 | settle |
| e_8 | balance_TBD_BANKAYA_MXN | +165 | void |
| e_9 | balance_TBD_BANKAYA_MXN | -165 | settle |

**`payments_entries`**
| payment_id | entry_id | type |
|:------------ |:-------- | --------- |
| p_1 (payin) | e_1 | principal |
| p_1 | e_2 | fee |
| p_2 (payout) | e_3 | principal |
| p_1 | e_4 | principal |
| p_1 | e_5 | fee |
| p_1 | e_6 | fee |
| p_1 | e_7 | fee |
| p_2 | e_8 | principal |
| p_2 | e_9 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- |:------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 89 | 89 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 35 | **35** |

### $100 USD -> USDC treasury transfer

#### 0. Starting balances

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- | ------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 100 | 100 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

#### 1. Treasury wires $100 USD to Circle, assuming no fee

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- | ---------------- |:------ |:------- |
| e_1 | balance_TBD_USD | -100 | pending |
| e_2 | balance_TBD_USDC | +100 | pending |

**`transfers_entries`**
| transfer_id | entry_id | type |
|:----------- |:-------- | --------- |
| t_1 | e_1 | principal |
| t_1 | e_2 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- | ------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | **0** | 100 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

#### 2. Circle receives USD

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- | ---------------- |:------ |:------- |
| e_1 | balance_TBD_USD | -100 | pending |
| e_2 | balance_TBD_USDC | +100 | pending |
| e_3 | balance_TBD_USD | +100 | voided |
| e_4 | balance_TBD_USDC | -100 | voided |
| e_5 | balance_TBD_USD | -100 | settled |
| e_6 | balance_TBD_USDC | +100 | settled |

**`transfers_entries`**
| transfer_id | entry_id | type |
|:----------- |:-------- | --------- |
| t_1 | e_1 | principal |
| t_1 | e_2 | principal |
| t_1 | e_3 | principal |
| t_1 | e_4 | principal |
| t_1 | e_5 | principal |
| t_1 | e_6 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- |:------------- |:---------------- |:------- |
| balance_TBD_USD | TBD | TBD | USD | 0 | **0** |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | **100** | **100** |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

### $100 USDC -> MXN treasury transfer (Bitso)

#### 0. Starting balances

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- | ------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 100 | 100 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

#### 1. Treasury uses admin panel to send USDC to Bitso via Circle, assuming no fee

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- | --------------------- |:------ |:------- |
| e_1 | balance_TBD_USDC | -100 | pending |
| e_2 | balance_TBD_BITSO_MXN | +1700 | pending |

**`transfers_entries`**
| transfer_id | entry_id | type |
|:----------- |:-------- | --------- |
| t_1 | e_1 | principal |
| t_1 | e_2 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- |:------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | **0** | 100 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

#### 2. Circle send completes

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- |:--------------------- |:------ |:------- |
| e_1 | balance_TBD_USDC | -100 | pending |
| e_2 | balance_TBD_BITSO_MXN | +1700 | pending |
| e_3 | balance_TBD_USDC | +100 | voided |
| e_4 | balance_TBD_USDC | -100 | settled |

**`transfers_entries`**
| transfer_id | entry_id | type |
|:----------- |:-------- | --------- |
| t_1 | e_1 | principal |
| t_1 | e_2 | principal |
| t_1 | e_3 | principal |
| t_1 | e_4 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- |:------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | **0** |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

#### 3. Bitso receives USDC and auto converts to MXN

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- |:--------------------- |:------ |:------- |
| e_1 | balance_TBD_USDC | -100 | pending |
| e_2 | balance_TBD_BITSO_MXN | +1700 | pending |
| e_3 | balance_TBD_USDC | +100 | voided |
| e_4 | balance_TBD_USDC | -100 | settled |
| e_5 | balance_TBD_BITSO_MXN | -1700 | voided |
| e_6 | balance_TBD_BITSO_MXN | +1700 | pending |

**`transfers_entries`**
| transfer_id | entry_id | type |
|:----------- |:-------- | --------- |
| t_1 | e_1 | principal |
| t_1 | e_2 | principal |
| t_1 | e_3 | principal |
| t_1 | e_4 | principal |
| t_1 | e_5 | principal |
| t_1 | e_6 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- |:------------- |:---------------- |:-------- |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | **1700** | **1700** |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

### 1700 MXN -> MXN treasury transfer (Bitso -> Bankaya)

#### 0. Starting balances

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- | ------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 1700 | 1700 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

#### 1. Treasury uses admin panel to send MXN to Bankaya via Bitso

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- | ----------------------- |:------ |:------- |
| e_1 | balance_TBD_BITSO_MXN | -1700 | pending |
| e_2 | balance_TBD_BANKAYA_MXN | +1700 | pending |

**`transfers_entries`**
| transfer_id | entry_id | type |
|:----------- |:-------- | --------- |
| t_1 | e_1 | principal |
| t_1 | e_2 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- | ------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | **0** | 1700 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

#### 2. Bitso send completes

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- | ----------------------- |:------ |:------- |
| e_1 | balance_TBD_BITSO_MXN | -1700 | pending |
| e_2 | balance_TBD_BANKAYA_MXN | +1700 | pending |
| e_3 | balance_TBD_BITSO_MXN | +1700 | voided |
| e_4 | balance_TBD_BITSO_MXN | -1700 | settled |

**`transfers_entries`**
| transfer_id | entry_id | type |
|:----------- |:-------- | --------- |
| t_1 | e_1 | principal |
| t_1 | e_2 | principal |
| t_1 | e_3 | principal |
| t_1 | e_4 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- | ------------- |:---------------- |:------ |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | **0** |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | 0 | 0 |

#### 3. Bankaya receives MXN

**`entries`**
| entry_id | balance_id | amount | type |
|:-------- | ----------------------- |:------ |:------- |
| e_1 | balance_TBD_BITSO_MXN | -1700 | pending |
| e_2 | balance_TBD_BANKAYA_MXN | +1700 | pending |
| e_3 | balance_TBD_BITSO_MXN | +1700 | voided |
| e_4 | balance_TBD_BITSO_MXN | -1700 | settled |
| e_5 | balance_TBD_BANKAYA_MXN | -1700 | voided |
| e_6 | balance_TBD_BANKAYA_MXN | +1700 | pending |

**`transfers_entries`**
| transfer_id | entry_id | type |
|:----------- |:-------- | --------- |
| t_1 | e_1 | principal |
| t_1 | e_2 | principal |
| t_1 | e_3 | principal |
| t_1 | e_4 | principal |
| t_1 | e_5 | principal |
| t_1 | e_6 | principal |

**`balances`**
| balance_id | beneficiary | custodian | currency_code | available_amount | amount |
|:----------------------- |:---------------- |:--------- | ------------- |:---------------- |:-------- |
| balance_TBD_USD | TBD | TBD | USD | 0 | 0 |
| balance_CA_USD | customer_cashapp | TBD | USD | 0 | 0 |
| balance_TBD_USDC | TBD | CIRCLE | USDC | 0 | 0 |
| balance_TBD_BITSO_MXN | TBD | BITSO | MXN | 0 | 0 |
| balance_TBD_BANKAYA_MXN | TBD | BANKAYA | MXN | **1700** | **1700** |